Hibernate之HQL查询小Demo

小灰灰 2022-06-07 12:22 355阅读 0赞

**代码:**

**\#数据模型层**

package com.sunline.entity;
    
    
    
    /**
     * User entity. @author MyEclipse Persistence Tools
     */
    
    public class User  implements java.io.Serializable {
    
    
        // Fields    
    
         private Integer id;
         private String userName;
         private String userPassword;
         private String userInfo;
    
    
        // Constructors
    
        /** default constructor */
        public User() {
        }
    
        
        /** full constructor */
        public User(String userName, String userPassword, String userInfo) {
            this.userName = userName;
            this.userPassword = userPassword;
            this.userInfo = userInfo;
        }
    
       
        // Property accessors
    
        public Integer getId() {
            return this.id;
        }
        
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getUserName() {
            return this.userName;
        }
        
        public void setUserName(String userName) {
            this.userName = userName;
        }
    
        public String getUserPassword() {
            return this.userPassword;
        }
        
        public void setUserPassword(String userPassword) {
            this.userPassword = userPassword;
        }
    
        public String getUserInfo() {
            return this.userInfo;
        }
        
        public void setUserInfo(String userInfo) {
            this.userInfo = userInfo;
        }
    
    }

<?xml version="1.0" encoding="utf-8"?>
    <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
    
    <hibernate-mapping>
        <class name="com.sunline.entity.User" table="user" catalog="association">
            <id name="id" type="java.lang.Integer">
                <column name="id" />
                <generator class="native"></generator>
            </id>
            <property name="userName" type="java.lang.String">
                <column name="user_name" length="200" not-null="true" />
            </property>
            <property name="userPassword" type="java.lang.String">
                <column name="user_password" length="50" not-null="true" />
            </property>
            <property name="userInfo" type="java.lang.String">
                <column name="user_info" length="60" not-null="true" />
            </property>
        </class>
    </hibernate-mapping>

**\#数据访问层**

package com.sunline.dao;
    
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.annotation.Resource;
    
    import org.hibernate.HibernateException;
    import org.hibernate.Query;
    import org.hibernate.Session;
    import org.hibernate.SessionFactory;
    import org.hibernate.Transaction;
    import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
    import org.springframework.stereotype.Repository;
    
    import com.sunline.entity.User;
    import com.sunline.sessionFactory.HibernateSessionFactory;
    
    @Repository(value="userDao")
    public class UserDao extends HibernateDaoSupport {
        private Session session;
        private Transaction tx;
        HibernateSessionFactory getSession;
        
    	/*
    	 * 使用注解必须添加以下方式
    	 */
        @Resource  
        public void setSessionFacotry(SessionFactory sessionFacotry) {  
            super.setSessionFactory(sessionFacotry);  
        }  
        
        /*
         * 查询所有用户
         */
        public List<User> findAll(){
        	getSession = new HibernateSessionFactory();
        	session = getSession.getSession();
        	String hql = "from User";
        	Query query = session.createQuery(hql);
        	List<User> list = query.list();
        	session.close();
    		return list;
        }
        
        /*
         * 添加用户
         */
        public void AddUser(User user){
        	getSession = new HibernateSessionFactory();
        	session = getSession.getSession();
        	try {
        		tx = session.beginTransaction();
    			System.out.println("成功添加用户!");
    			session.save(user);
    			tx.commit();
    			session.close();
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			System.out.println("添加用户失败!");
    			e.printStackTrace();
    		}
        }
        
        /*
         * 通过id取得某个用户信息
         */
        public User findById(int id){
        	getSession = new HibernateSessionFactory();
        	session = getSession.getSession();
        	User user = null;
    		try {
    			String hql = "from User where id=:id";
    			Query query = session.createQuery(hql);
    			query.setParameter("id", id);
    			user = (User) query.uniqueResult();
    			System.out.println("查询用户信息成功!");
    			session.close();
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			System.out.println("查询用户信息失败!");
    			e.printStackTrace();
    		}
    		return user;
        }
        
        /*
         * 修改用户信息(个人觉得这种方式不灵活,固定要修改整个对象)
         */
        public void UpdateUserInfo(User user){
        	getSession = new HibernateSessionFactory();
        	session = getSession.getSession();
        	tx = session.beginTransaction();
        	try {
    			session.update(user);
    			System.out.println("成功修改数据!");
    			tx.commit();
    			session.close();
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			System.out.println("修改数据失败!");
    			e.printStackTrace();
    		}
        }
        
        /*
         * 删除用户信息
         */
        public void deleteUser(int id){
        	getSession = new HibernateSessionFactory();
        	session = getSession.getSession();
        	tx = session.beginTransaction();
        	try {
    			String  hql = "delete from User where id=:id";
    			Query query = session.createQuery(hql);
    			query.setParameter("id", id);
    			query.executeUpdate();
    			System.out.println("成功删除数据!");
    			tx.commit();
    			session.close();
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			System.out.println("删除数据失败!");
    			e.printStackTrace();
    		}
        }
        
        /*
         * 获取用户信息总数
         */
        public int getUserCount(){
        	getSession = new HibernateSessionFactory();
        	session = getSession.getSession();
        	int count = 0;
        	try {
    			String hql = "select count(u) from User u";
    			Query query = session.createQuery(hql);
    			Number num = (Number) query.uniqueResult();
    			count = num.intValue();
    			System.out.println("成功获取数据!");
    			session.close();
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			System.out.println("获取数据失败!");
    			e.printStackTrace();
    		}
    		return count;
        }
    
        /*
         * 获取分页信息
         */
        public List<User> getUserPageList(int pageNum, int pageSize){
        	getSession = new HibernateSessionFactory();
        	session = getSession.getSession();
        	List<User> list = new ArrayList<User>();
        	try {
    			String hql = "from User";
    			Query query = session.createQuery(hql);
    			query.setFirstResult((pageNum-1)*pageSize); 
    			query.setMaxResults(pageSize);
    			list =query.list();
    			System.out.println("成功获取数据!");
    			session.close();
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			System.out.println("获取数据失败!");
    			e.printStackTrace();
    		}
    		return list;
        }
        
        /*
         * 通过输入用户名和密码获取用户信息,用于用户登录
         */
        public User Login(String user_name,String user_password){
        	getSession = new HibernateSessionFactory();
        	session = getSession.getSession();
        	User user = null;
        	try {
    			String hql = "from User where userName=? and userPassword=?";
    			Query query = session.createQuery(hql);
    			query.setString(0, user_name);
    			query.setString(1, user_password);
    			user = (User) query.uniqueResult();
    			System.out.println("成功获取用户登录数据!");
    			session.close();
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			System.out.println("获取用户登录数据失败!");
    			e.printStackTrace();
    		}
        	
    		return user;
        }
        
        /*
         * 模糊查询
         */
        public List<User> findByMoHu(String user_name, String user_password){
        	getSession = new HibernateSessionFactory();
        	session = getSession.getSession();
        	List<User> list = new ArrayList<User>();
        	try {
    			 String hql = "FROM User u WHERE u.userName=? AND u.userPassword like ?";
    			 Query query = session.createQuery(hql);
    			 String password = "%"+user_password+"%";
    			 query.setString(0, user_name);
    			 query.setString(1, password);
    			 list = query.list();
    			 System.out.println("模糊查询到数据!");
    			 session.close();
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			System.out.println("查询数据失败!");
    			e.printStackTrace();
    		}
    		return list;
        }
        
        /*
         * 查询指定字段
         */
        public List<Object []> findByEle(){
        	getSession = new HibernateSessionFactory();
        	session = getSession.getSession();
        	List<Object []> list = new ArrayList<Object[]>();
        	try {
    			String hql = "select userName,userPassword from User";
    			Query query = session.createQuery(hql);
    			list = query.list();
    			System.out.println("成功查询到数据!");
    			session.close();
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			System.out.println("查询数据失败!");
    			e.printStackTrace();
    		}
    		return list;
        }
        
        /*
         * 使用Map进行封装查询
         */
        public User findByMap(String userName,String UserPassword){
        	getSession = new HibernateSessionFactory();
        	session = getSession.getSession();
        	Map<String, String> map = new HashMap<String, String>();
        	map.put("userName", userName);
        	map.put("userPassword", UserPassword);
        	User user = null;
        	try {
    			String hql = "from User where userName = :userName  and userPassword = :userPassword";
    			Query query = session.createQuery(hql);
    			query.setParameter("userName", map.get("userName"));
    		    query.setParameter("userPassword", map.get("userPassword"));
    		    user = (User) query.uniqueResult();
    		    System.out.println("成功查询到数据!");
    			session.close();
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			System.out.println("查询数据失败!");
    			e.printStackTrace();
    		}
    		return user;
        }
        
        /*
         * 使用 in来设置基于列表的查询(设置查询区间)
         */
        public List<User> findByIn(String name){
        	getSession = new HibernateSessionFactory();
        	session = getSession.getSession();
        	List<User> list = new ArrayList<User>();
        	try {
    			 String hql = "from User u where u.userName like ? and u.id in ( :id )";
    			 Query query = session.createQuery(hql);
    			 String Name = "%"+name+"%";
    			 query.setString(0, Name);
    			 query.setParameterList("id", new Integer[] {5,6,7});   //显示出id号为以上的用户
    			 list = query.list();
    			 System.out.println("查询到区间数据!");
    			 session.close();
    		} catch (Exception e) {
    			// TODO Auto-generated catch block
    			System.out.println("查询区间数据失败!");
    			e.printStackTrace();
    		}
    		return list;
        }
    }

**\#业务逻辑层**

package com.sunline.biz;
    
    import java.util.List;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.stereotype.Service;
    
    import com.sunline.dao.UserDao;
    import com.sunline.entity.User;
    
    @Service(value="userBiz")
    public class UserBiz {
    	@Autowired
    	@Qualifier("userDao")            //使用@Qualifier注解来说明使用哪一个实现类
        UserDao userDao;
    	
    	/*
    	 * 查询所有用户
    	 */
    	public List<User> findAll(){
    		return userDao.findAll();
    	}
    	
        /*
         * 添加用户
         */
        public void AddUser(User user){
        	userDao.AddUser(user);
        }
        
        /*
         * 通过id取得某个用户信息
         */
        public User findById(int id){
        	return userDao.findById(id);
        }
        
        /*
         * 修改用户信息(个人觉得这种方式不灵活)
         */
        public void UpdateUserInfo(User user){
        	userDao.UpdateUserInfo(user);
        }
        
        /*
         * 删除用户信息
         */
        public void deleteUser(int id){
        	userDao.deleteUser(id);
        }
        
        /*
         * 获取用户信息总数
         */
        public int getUserCount(){
        	return userDao.getUserCount();
        }
        
        /*
         * 获取分页信息
         */
        public List<User> getUserPageList(int pageNum, int pageSize){
        	return userDao.getUserPageList(pageNum, pageSize);
        }
        
        /*
         * 通过输入用户名和密码获取用户信息,用于用户登录
         */
        public User Login(String user_name,String user_password){
        	return userDao.Login(user_name, user_password);
        }
        
        /*
         * 模糊查询
         */
        public List<User> findByMoHu(String user_name, String user_password){
        	return userDao.findByMoHu(user_name, user_password);
        }
        
        /*
         * 查询指定字段
         */
        public List<Object []> findByEle(){
        	return userDao.findByEle();
        }
        
        /*
         * 使用Map进行封装查询
         */
        public User findByMap(String userName,String UserPassword){
        	return userDao.findByMap(userName, UserPassword);
        }
        
        /*
         * 使用 in来设置基于列表的查询(设置查询区间)
         */
        public List<User> findByIn(String name){
        	return userDao.findByIn(name);
        }
    }

**\#测试层**

package com.sunline.test;
    
    import java.util.List;
    
    import org.springframework.context.ApplicationContext;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    
    import com.sunline.biz.UserBiz;
    import com.sunline.entity.User;
    
    public class Test {
    
    	/**
    	 * @param args
    	 */
    	public static void main(String[] args) {
    		// TODO Auto-generated method stub
    	    ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
    	    UserBiz userBiz = (UserBiz) ctx.getBean("userBiz");
    	    
           /*
            * 1.查询所有用户
            */
    	    List<User> list = userBiz.findAll();
    	    for(User user : list){
    	    	System.out.println("你的个人信息为: "+user.getUserName()+" "+user.getUserPassword()+" "+user.getUserInfo());
    	    }
    	    
    	    /*
    	     * 2.添加用户
    	     */
    	    User user = new User();
    	    user.setUserName("晓林");
    	    user.setUserPassword("123456");
    	    user.setUserInfo("是一个Java工程师");
    	    userBiz.AddUser(user);
    	    
    	    /*
    	     * 3.通过id取得某个用户信息
    	     */
    	    User user = userBiz.findById(2);
    	    System.out.println("你的个人信息为: "+user.getUserName()+" "+user.getUserPassword()+" "+user.getUserInfo());
    	    
    	    /*
    	     * 4.修改用户信息
    	     */
    	    User user = new User();
    	    user.setId(2);
    	    user.setUserName("杨旭");
    	    user.setUserPassword("123456");
    	    user.setUserInfo("山东大学学生");
    	    userBiz.UpdateUserInfo(user);
    	    
    	    /*
    	     * 5.删除用户信息
    	     */
    	    userBiz.deleteUser(1);
    	    
    	    /*
    	     * 6.获取用户信息总数
    	     */
    	    int count = userBiz.getUserCount();
    	    System.out.println("一共有"+count+"位用户");
    	    
    	    /*
    	     * 7.获取分页信息
    	     */
    	    List<User> list = userBiz.getUserPageList(1, 1);
    	    for(User user : list){
        	    System.out.println("你的个人信息为: "+user.getUserName()+" "+user.getUserPassword()+" "+user.getUserInfo());
            }
    	    
    	    /*
    	     * 8.通过输入用户名和密码获取用户信息,用于模拟用户登录
    	     */
    	    String user_name = "杨旭";
    	    String user_password = "123456";
    	    User user = userBiz.Login(user_name, user_password);
    	    System.out.println("你的个人信息为: "+user.getUserName()+" "+user.getUserPassword()+" "+user.getUserInfo());
    	    
    	    /*
    	     * 9.模糊查询
    	     */
    	    String user_name = "杨旭";
    	    String user_password = "123";
    	    List<User> list = userBiz.findByMoHu(user_name, user_password);
    	    for(User user : list){
        	  System.out.println("你的个人信息为: "+user.getUserName()+" "+user.getUserPassword()+" "+user.getUserInfo());
            }
    	    
    	    /*
    	     * 10.查询指定字段
    	     */
    	    List<Object []> list = userBiz.findByEle();
    	    for(Object [] object : list){
    	    	String name = (String) object[0];
    	    	String password = (String) object[1];
    	    	 System.out.println("你的个人信息为--->用户名:"+name + ",密码:" +password);  
    	    }
    	    
    	    /*
    	     * 11.使用Map进行封装查询
    	     */
    	    String userName = "杨旭";
    	    String UserPassword = "123456";
    	    User user = userBiz.findByMap(userName, UserPassword);
    	    System.out.println("你的个人信息为: "+user.getUserName()+" "+user.getUserPassword()+" "+user.getUserInfo());
    	    
    	    /*
    	     * 12.使用 in来设置基于列表的查询(设置查询区间)
    	     */
    	    String user_name = "晓";
    	    List<User> list = userBiz.findByIn(user_name);
    	    for(User user : list){
        	  System.out.println("你的个人信息为: "+user.getUserName()+" "+user.getUserPassword()+" "+user.getUserInfo());
            }
    	}
    
    }