//HQL查询 // auto-import要设置true，如果是false，写HQL时要指定类的全名 //查询全部列 Query query = session.createQuery("from Dept"); System.out.println(query.list()); //查询指定列 Query query = session.createQuery("select d.depId,d.depName from Dept d"); System.out.println(query.list()); //查询指定列，并封装为对象，必须提供带参数的构造器 Query query = session.createQuery("select new Dept(d.depId,d.depName) from Dept d"); System.out.println(query.list()); //条件查询 一个条件、多个条件、and or between 模糊查询 Query query = session.createQuery("from Dept d where depName=?"); query.setString(0,"人事部"); query.setParameter(0,"人事部"); //条件查询，命名参数 Query query = session.createQuery("from Dept d where depId =:myid and depName=:name"); query.setParameter("myid",1); query.setParameter("name","人事部"); //条件查询，between and查询 Query query = session.createQuery("from Dept d where depId between ? and ?"); query.setParameter(0,1); query.setParameter(1,5); //模糊查询 Query query = session.createQuery("from Dept d where depName like ?"); query.setParameter(0,"%部%"); System.out.println(query.list()); //集合函数统计，不支持count(1)，uniqueResult()返回结果的第一行第一列中的值 Query query = session.createQuery("select count(*) from Dept"); System.out.println(query.uniqueResult()); //分组查询，查询employ表，统计每个部门的人数 Query query = session.createQuery("select e.dept, count(*) from Employee e group by e.dept"); //统计部门人数大于1的部门 Query query = session.createQuery("select e.dept, count(*) from Employee e group by e.dept having count(*) >1"); System.out.println(query.list()); //连接查询，内连接、左外连接、右外连接 //内连接，映射已经配置好了关系，关联的时候直接写对象的属性即可 //返回员工和部门组成的对象数组，写在前面的元素在结果集中也在前面 // SELECT e.empName, e.salary, d.deptName FROM t_employee e INNER JOIN t_dept d ON d.depId = e.dept_id Query query = session.createQuery("select e.empName,e.salary,e.dept.depName from Employee e inner join e.dept"); List<Object[]> list = query.list(); for(int i=0;i<list.size();i++){ Object[] obj = list.get(i); Employee employee = (Employee) obj[0]; Dept dept = (Dept) obj[1]; System.out.println(employee); System.out.println(dept); } System.out.println(query.list()); //左外连接 // SELECT e.empName, e.salary, d.deptName FROM t_employee e LEFT JOIN t_dept d ON d.depId = e.dept_id Query query = session.createQuery(" from Employee e left join e.dept"); List<Object[]> list = query.list(); for(int i=0;i<list.size();i++){ Object[] obj = list.get(i); Employee employee = (Employee) obj[0]; Dept dept = (Dept) obj[1]; System.out.println(employee); System.out.println(dept); }//迫切内连接，将右表的对象填充到左表当中 Query query = session.createQuery("from Dept d inner join fetch d.emps");
将HQL查询语句放到映射文件中：
<query name="getDept"> from Dept d where depName=? </query> <query name="getDept2"> <![CDATA[ from Dept d where depId < ? ]]> </query>
使用查询语句
Query query = session.getNamedQuery("getDept");
query.setParameter(0,"人事部");
Query query = session.getNamedQuery("getDept2");
query.setParameter(0, 4);
System.out.println(query.list());
使用c3p0连接池
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <!-- 通常，一个session-factory节点代表一个数据库 --> <session-factory> <!-- 1. 数据库连接配置 --> <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> <property name="hibernate.connection.url">jdbc:mysql:///test</property> <property name="hibernate.connection.username">root</property> <property name="hibernate.connection.password">juaner767</property> <!-- 【连接池配置】 --> <!-- 数据库方法配置， hibernate在运行的时候，会根据不同的方言生成符合当前数据库语法的sql --> <property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property> <!-- 2. 其他相关配置 --> <!-- 2.1 显示hibernate在运行时候执行的sql语句 --> <property name="hibernate.show_sql">true</property> <!-- 2.2 格式化sql <property name="hibernate.format_sql">true</property> --> <!-- 2.3 自动建表 --> <property name="hibernate.hbm2ddl.auto">update</property> <!-- 配置连接驱动管理类 --> <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property> <!-- 配置连接池参数信息 --> <property name="hibernate.c3p0.min_size">2</property> <property name="hibernate.c3p0.max_size">4</property> <property name="hibernate.c3p0.timeout">5000</property> <property name="hibernate.c3p0.max_statements">10</property> <property name="hibernate.c3p0.idle_test_period">30000</property> <property name="hibernate.c3p0.acquire_increment">2</property> <!-- 3. 加载所有映射 --> <mapping resource="com/juaner/hibernate/address/User.hbm.xml"/> <!--<mapping resource="com/juaner/hibernate/department/Dept.hbm.xml"/>--> <!--<mapping resource="com/juaner/hibernate/department/Employee.hbm.xml"/>--> <!--<mapping resource="com/juaner/hibernate/project/Developer.hbm.xml"/>--> <!--<mapping resource="com/juaner/hibernate/project/Project.hbm.xml"/>--> <!--<mapping resource="com/juaner/hibernate/extendMap/Animal.hbm.xml"/>--> </session-factory> </hibernate-configuration>