java hibernate用注解 mysql例子_Java进阶知识14 Hibernate查询语言（HQL），本文以hibernate注解版为例讲解...

1 packagecom.shore.test;2

3 importjava.util.ArrayList;4 importjava.util.List;5

6 importorg.hibernate.Query;7 importorg.hibernate.Session;8 importorg.hibernate.SessionFactory;9 importorg.hibernate.Transaction;10 importorg.hibernate.cfg.AnnotationConfiguration;11 importorg.junit.AfterClass;12 importorg.junit.BeforeClass;13 importorg.junit.Test;14

15 importcom.shore.model.Department;16 importcom.shore.model.Employee;17

18 /**

19 *@authorDSHORE/2019-9-1920 *21 */

22 public classMyTest {23 public static SessionFactory sessionFactory = null;24 public static Session session = null;25

26 @BeforeClass27 public static voidbuildSessionFactory() {28 sessionFactory = newAnnotationConfiguration().configure()29 .buildSessionFactory();30 }31

32 @AfterClass33 public static voidclose() {34 session.close();35 sessionFactory.close();36 }37

38 /**

39 * 单向多对一：多个员工 对 一个部门40 */

41 /*@Test42 public void testAdd() {//插入数据43 session = sessionFactory.openSession();44 Transaction transaction = session.beginTransaction();45 Department dept1 = new Department();46 Department dept2 = new Department();47 dept1.setName("总裁办");48 dept1.setDescription("秘书");49 dept2.setName("市场部");50 dept2.setDescription("经理");51

52 Employee em1 = new Employee();53 em1.setName("张三");54 em1.setSex(true);55 em1.setSalary(5999.00f);56 em1.setDepartment(dept1);57

58 Employee em2 = new Employee();59 em2.setName("李四");60 em2.setSex(true);61 em2.setSalary(5999.00f);62 em2.setDepartment(dept2);63

64 Employee em3 = new Employee();65 em3.setName("王五");66 em3.setSex(true);67 em3.setSalary(5999.00f);68 em3.setDepartment(dept1);69

70 Employee em4 = new Employee();71 em4.setName("赵六");72 em4.setSex(true);73 em4.setSalary(5999.00f);74 em4.setDepartment(dept1);75

76 Employee em5 = new Employee();77 em5.setName("田七");78 em5.setSex(true);79 em5.setSalary(5999.00f);80 em5.setDepartment(dept2);81

82 session.save(em1); // 先创建dept1并数据，后创建em1并插入数据83 session.save(em2); // 先创建dept2并数据，后插入em2的数据84 session.save(em3);85 session.save(em4);86 session.save(em5);87 transaction.commit();88 }*/

89

90 /**

91 * hql :1、查询全部列92 */

93 @SuppressWarnings("unchecked")94 @Test95 public voidtestListAllEmployee() {96 session =sessionFactory.openSession();97 Transaction transaction =session.beginTransaction();98 List list = new ArrayList();99 Query query = session.createQuery("select e from Employee e");100 //或者这样写：Query query = session.createQuery("from Employee"); 效果是一样的

101 list =query.list();102 transaction.commit();103

104 System.out.println("!!!!!!!!!!!!!!");105 if (list != null && list.size() > 0) {106 for (int j = 0; j < list.size(); j++) {107 System.out.println(j + ":" +list.get(j));108 }109 }110 /**testListAllEmployee()运行结果：111 * !!!!!!!!!!!!!!112 * 0:Employee [id=1, name=张三, sex=true, salary=5999.0, department=Department [id=1, name=总裁办, description=秘书]]113 * 1:Employee [id=2, name=李四, sex=true, salary=5999.0, department=Department [id=2, name=市场部, description=经理]]114 * 2:Employee [id=3, name=王五, sex=true, salary=5999.0, department=Department [id=1, name=总裁办, description=秘书]]115 * 3:Employee [id=4, name=赵六, sex=true, salary=5999.0, department=Department [id=1, name=总裁办, description=秘书]]116 * 4:Employee [id=5, name=田七, sex=true, salary=5999.0, department=Department [id=2, name=市场部, description=经理]]117 */

118 }119

120 /**

121 * hql: 2、查询指定的列122 */

123 @Test124 public voidtestListSubField() {125 session =sessionFactory.openSession();126 Transaction transaction =session.beginTransaction();127 Query query = session.createQuery("select name,sex,salary from Employee");128 System.out.println(query.list());129 transaction.commit();130 /**testListSubField()运行结果：131 * [[Ljava.lang.Object;@259a8416, [Ljava.lang.Object;@4355d3a3, [Ljava.lang.Object;@37b994de, [Ljava.lang.Object;@78dc9766, [Ljava.lang.Object;@5a57e787]132 */

133 }134

135 /**

136 * hql: 3、查询指定的列，自动封装为对象137 * 注意：必须要提供带参数构造器；必须在hql封装类前面要加上package名称138 */

139 @Test140 public voidtestListSubFieldsToObj() {141 session =sessionFactory.openSession();142 Transaction transaction =session.beginTransaction();143 Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) from Employee");144 System.out.println(query.list());145 transaction.commit();146 /**testListSubFieldsToObj()运行结果：147 * [SubEmployee [name=张三, sex=true, salary=5999.0], SubEmployee [name=李四, sex=true, salary=5999.0], SubEmployee [name=王五, sex=true, salary=5999.0], SubEmployee [name=赵六, sex=true, salary=5999.0], SubEmployee [name=田七, sex=true, salary=5999.0]]148 */

149 }150

151 /**

152 * hql: 4 、条件查询153 * 4.1、条件查询之占位符(？)154 */

155 @Test156 public voidtestListByConditions1() {157 session =sessionFactory.openSession();158 Transaction transaction =session.beginTransaction();159 Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) " +

160 "from Employee where department_id=?"); //用department_id或department都是可以的161 //query.setParameter(0, 1);//或者下面的setInteger(0, 1)方法也行

162 query.setInteger(0, 1); //类似于JDBC中的 PreparedStatement

163 System.out.println(query.list());164 transaction.commit();165 /**testListByConditions1()运行结果：166 * [SubEmployee [name=张三, sex=true, salary=5999.0], SubEmployee [name=王五, sex=true, salary=5999.0], SubEmployee [name=赵六, sex=true, salary=5999.0]]167 */

168 }169

170 /**

171 * hql: 4.2、条件查询之命名参数(:)172 */

173 @Test174 public voidtestListByConditions2() {175 session =sessionFactory.openSession();176 Transaction transaction =session.beginTransaction();177 Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) " +

178 "from Employee where department_id=:deptID");179 query.setParameter("deptID", 1);180 System.out.println(query.list());181 transaction.commit();182 /**testListByConditions2()运行结果：183 * [SubEmployee [name=张三, sex=true, salary=5999.0], SubEmployee [name=王五, sex=true, salary=5999.0], SubEmployee [name=赵六, sex=true, salary=5999.0]]184 */

185 }186

187 /**

188 * hql: 4.3、条件查询之范围查询189 * between 1 and 10 []闭区间 mysql 1<=x<=10190 */

191 @Test192 public voidtestListByConditions3() {193 session =sessionFactory.openSession();194 Transaction transaction =session.beginTransaction();195 Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) " +

196 "from Employee where department_id between :d1 and :d2");197 query.setParameter("d1", 1); //也可以用占位符？实现

198 query.setParameter("d2", 2);199 System.out.println(query.list());200 transaction.commit();201 /**testListByConditions3()运行结果：202 * [SubEmployee [name=张三, sex=true, salary=5999.0], SubEmployee [name=王五, sex=true, salary=5999.0], SubEmployee [name=赵六, sex=true, salary=5999.0]]203 */

204 }205

206 /**

207 * hql: 4.4、条件查询之模糊查询 like208 */

209 @Test210 public voidtestListByConditions4() {211 session =sessionFactory.openSession();212 Transaction transaction =session.beginTransaction();213 Query query = session.createQuery("select new com.shore.model.SubEmployee(name,sex,salary) " +

214 "from Employee where name like ?");215 query.setParameter(0, "%三%");216 System.out.println(query.list());217 transaction.commit();218 /**testListByConditions4()运行结果：219 * [SubEmployee [name=张三, sex=true, salary=5999.0]]220 */

221 }222

223 /**

224 * hql: 5、聚合函数统计225 */

226 @Test227 public voidtestListByCount() {228 session =sessionFactory.openSession();229 Transaction transaction =session.beginTransaction();230 Query query = session.createQuery("select count(*) " +

231 "from Employee where name like ?");232 query.setParameter(0, "%三%");233 System.out.println(query.list());234 transaction.commit();235 /**testListByCount()运行结果：236 * [1]237 */

238 }239

240 /**

241 * hql: 6、分组查询group by242 */

243 @Test244 public voidtestListByDept() {245 session =sessionFactory.openSession();246 Transaction transaction =session.beginTransaction();247 Query query = session.createQuery("select department,count(*) " +

248 "from Employee group by department_id");249 System.out.println(query.list());250 transaction.commit();251 /**testListByDept()运行结果：252 * [[Ljava.lang.Object;@3b35b1f3, [Ljava.lang.Object;@4235e6e3]253 */

254 }255

256 /**

257 * hql: 7、连接查询258 * 7.1、内连接259 */

260 @Test261 public voidtestListByInnerJoin() {262 session =sessionFactory.openSession();263 Transaction transaction =session.beginTransaction();264 Query query = session.createQuery("from Employee e inner join e.department");265 System.out.println(query.list());266 transaction.commit();267 /**

268 * 运行结果：269 * [[Ljava.lang.Object;@60c9630a, [Ljava.lang.Object;@4585572a, [Ljava.lang.Object;@351daa0e, [Ljava.lang.Object;@2e879860, [Ljava.lang.Object;@4824de7d]270 */

271 }272

273 /**

274 * 7.2、左连接275 */

276 @Test277 public voidtestListByLeftJoin() {278 session =sessionFactory.openSession();279 Transaction transaction =session.beginTransaction();280 Query query = session.createQuery("from Employee e left join e.department");281 System.out.println(query.list());282 transaction.commit();283 /**

284 * 运行结果：285 * [[Ljava.lang.Object;@5d15126e, [Ljava.lang.Object;@126d2380, [Ljava.lang.Object;@3b35b1f3, [Ljava.lang.Object;@4235e6e3, [Ljava.lang.Object;@60c9630a]286 */

287 }288

289 /**

290 * 7.3、右连接291 */

292 @Test293 public voidtestListByRightJoin() {294 session =sessionFactory.openSession();295 Transaction transaction =session.beginTransaction();296 Query query = session.createQuery("from Employee e right join e.department");297 System.out.println(query.list());298 transaction.commit();299 /**

300 * 运行结果：301 * [[Ljava.lang.Object;@4235e6e3, [Ljava.lang.Object;@60c9630a, [Ljava.lang.Object;@4585572a, [Ljava.lang.Object;@351daa0e, [Ljava.lang.Object;@2e879860]302 */

303 }304 }