hibernate mysql查询_hibernate 数据库查询

最新推荐文章于 2024-04-18 03:43:49 发布

weixin_39914938

最新推荐文章于 2024-04-18 03:43:49 发布

阅读量253

点赞数

文章标签： hibernate mysql查询

本文链接：https://blog.csdn.net/weixin_39914938/article/details/113475378

版权

hibernate提供了多种方式进行数据库数据的查询

HQL查询

代码如下

1 public classHibernateTest {2 public static voidmain(String[] args) {3 Session session =HibernateFactory.currentSession();4 Transaction tx =session.beginTransaction();5

6 Query query = session.createQuery("from Student student where student.name like :name order by student.id asc");7 query.setParameter("name", "student%");8 //实现分页

9 query.setFirstResult(0);10 query.setMaxResults(2);11 List students =query.list();12 for(Student student : students) {13 System.out.println(student.getName());14 }15

16 tx.commit();17 HibernateFactory.closeSession();18 }19 }

如果结果是单一的对象而不是集合，可以使用query. uniqueResult()

1 public classHibernateTest {2 public static voidmain(String[] args) {3 Session session =HibernateFactory.currentSession();4 Transaction tx =session.beginTransaction();5

6 Query query = session.createQuery("select count(*) from Student");7 Long count =(Long)query.uniqueResult();8

9 tx.commit();10 HibernateFactory.closeSession();11 }12 }

也可以直接访问对象中的对象属性(String hql = "from Student where grade.name= 'grade1'"；) 这样相当于两张表的联合查询

如果一次查询多个对象，可以使用以下方式

1 public classHibernateTest {2 public static voidmain(String[] args) {3

4 Session session =HibernateFactory.currentSession();5 Transaction tx =session.beginTransaction();6

7 Query query = session.createQuery("from Student s,Grade g where s.id=g.id and s.name like :name");8 query.setParameter("name", "student%");9 List list =query.list();10 for(int i = 0;i < list.size();i++) {11 Object[] obj =(Object[]) list.get(i);12 Student student = (Student) obj[0];13 Grade grade = (Grade)obj[1];14 System.out.println(student.getName() + "|" +grade.getName());15 }16

17 tx.commit();18 HibernateFactory.closeSession();19 }20 }

Criteria方式查询

代码如下

1 public classHibernateTest {2 public static voidmain(String[] args) {3 Session session =HibernateFactory.currentSession();4 Transaction tx =session.beginTransaction();5

6 Criteria criteria = session.createCriteria(Student.class);7 criteria.add(Restrictions.like("name", "student%"));8

9 criteria.setFirstResult(0);10 criteria.setMaxResults(2);11 criteria.addOrder(Order.desc("name"));12

13 List students =criteria.list();14 for(Student student : students) {15 System.out.println(student.getName());16 }17

18 tx.commit();19 HibernateFactory.closeSession();20 }21 }

使用criteria方式查询有以下三个步骤

1.使用Session实例的createCriteria()方法创建Criteria对象

2.使用工具类Restrictions的方法为Criteria对象设置查询条件，Order工具类的方法设置排序方式，Projections工具类的方法进行统计和分组

3.使用Criteria对象的list()方法进行查询并返回结果

例子查询

例子查询将一个对象的非空属性作为查询条件进行查询，代码如下

1 public classHibernateTest {2 public static voidmain(String[] args) {3 //建立一个例子对象，它的非空属性作为删选条件

4 Student studentExample = newStudent();5 studentExample.setName("student1");6

7 Session session =HibernateFactory.currentSession();8 Transaction tx =session.beginTransaction();9

10 Criteria criteria = session.createCriteria(Student.class);11 criteria.add(Example.create(studentExample));12

13 List students =criteria.list();14 for(Student student : students) {15 System.out.println(student.getName());16 }17

18 tx.commit();19 HibernateFactory.closeSession();20 }21 }

离线查询

离线查询建立一个DetachedCriteria对象，将查询的条件等指定好，然后在session.beginTransaction()后将这个对象传入，代码如下

1 public classHibernateTest {2 public static voidmain(String[] args) {3 DetachedCriteria dc = DetachedCriteria.forClass(Student.class);4 dc.add(Restrictions.like("name", "student%"));5

6 Session session =HibernateFactory.currentSession();7 Transaction tx =session.beginTransaction();8

9 Criteria criteria =dc.getExecutableCriteria(session);10 List students =criteria.list();11 for(Student student : students) {12 System.out.println(student.getName());13 }14

15 tx.commit();16 HibernateFactory.closeSession();17 }18 }

load/get方式

Student student = (Student) session.get(Student.class, 1);

Student student = (Student) session.load(Student.class, 1);

两者区别

1.get()采用立即加载方式,而load()采用延迟加载;get()方法执行的时候,会立即向数据库发出查询语句, 而load()方法返回的是一个代理(此代理中只有一个id属性),只有等真正使用该对象属性的时候,才会发出sql语句

2.如果数据库中没有对应的记录,get()方法返回的是null.而load()方法出现异常ObjectNotFoundException

命名查询

1.配置hbm文件，将sql或者hql语句写入hbm，并给与一个唯一标示这个查询语句的name

1 <?xml version="1.0" encoding="UTF-8"?>

2 "-//Hibernate/Hibernate Mapping DTD 3.0//EN"4 "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

6 ...

10 from Student where name like :name11 ]]>

16 select s from Student s where name like ?17 ]]>

2.通过name在代码中调用事先写好的查询语句

1 public classHibernateTest {2 public static voidmain(String[] args) {3

4 Session session =HibernateFactory.currentSession();5 Transaction tx =session.beginTransaction();6

7 Query query = session.getNamedQuery("hqlquery");8 query.setParameter("name", "student%");9 List students =query.list();10 for(Student student : students) {11 System.out.println(student.getName());12 }13

14 Query sqlquery = session.getNamedQuery("sqlquery");15 sqlquery.setParameter(0, "student%");16 List students2 =query.list();17 for(Student student : students2) {18 System.out.println(student.getName());19 }20

22 tx.commit();23 HibernateFactory.closeSession();24 }25 }

SQL查询

hibernate支持原生的sql查询语句，具体的代码如下

返回单表的查询

1 public classHibernateTest {2 public static voidmain(String[] args) {3 Session session =HibernateFactory.currentSession();4 Transaction tx =session.beginTransaction();5

6 List students = session.createSQLQuery("select * from student").addEntity(Student.class).list();7 //或者采用以下语句8 //List students = session.createSQLQuery("select s.* from student as s").addEntity("s",Student.class).list();

10 for(Student student : students) {11 System.out.println(student.getName());12 }13

14 tx.commit();15 HibernateFactory.closeSession();16 }17 }

返回多个表的查询

1 public classHibernateTest {2 public static voidmain(String[] args) {3 Session session =HibernateFactory.currentSession();4 Transaction tx =session.beginTransaction();5

6 SQLQuery query = session.createSQLQuery("select s.*,g.* from student as s,grade as g where s.gradeid=g.id");7 query.addEntity("s",Student.class).addEntity("g",Grade.class);8

9 List list =query.list();10 //list的每一条数据都由Object数组组成，数组的每个约束对应一个对象

11 for (int i = 0; i < list.size(); i++) {12 Object[] obj =(Object[]) list.get(i);13 Student student = (Student) obj[0];14 Grade grade = (Grade) obj[1];15 }16

17 tx.commit();18 HibernateFactory.closeSession();19 }20 }

使用jdbc

1 public classHibernateTest {2 public static voidmain(String[] args) {3

4 Session session =HibernateFactory.currentSession();5 Transaction tx =session.beginTransaction();6 session.doWork(newWork() {7 public void execute(Connection connection) throwsSQLException {8 //这里已经得到connection了，可以继续你的JDBC代码。

9 PreparedStatement ps = connection.prepareStatement("insert into student(id,name) values(?,?)");10 for(int i = 0;i < 500;i++) {11 ps.setInt(1,i);12 ps.setString(2,"student" +i);13 ps.addBatch(); //添加到批处理命令中

14 }15 ps.executeBatch(); //执行批处理命令16 //注意不要close了这个connection。

17 }18 });19

20 //事务提交后才会执行插入操作

21 tx.commit();22 HibernateFactory.closeSession();23 }24 }

使用原生的jdbc操作，hibernate4之前可以用session.connection()获取Connection对象，Hibernate4之后这个方法已经废弃

Query.iterator与Query.list的比较

iterator的使用方法如下

1 public classHibernateTest {2 public static voidmain(String[] args) {3

4 Session session =HibernateFactory.currentSession();5 Transaction tx =session.beginTransaction();6

7 Query query = session.createQuery("from Student");8 Iterator iterator =query.iterate();9 while(iterator.hasNext()) {10 Student student =(Student)iterator.next();11 System.out.println(student.getName());12 }13

14 tx.commit();15 HibernateFactory.closeSession();16 }17 }

iterator方式查询产生的sql语句如下

1 Hibernate: select student0_.id as col_0_0_ fromstudent student0_2

3 Hibernate: select student0_.id as id1_1_0_, student0_.name as name2_1_0_, student0_.gradeid as gradeid3_1_0_ from student student0_ where student0_.id=?4

5 Hibernate: select student0_.id as id1_1_0_, student0_.name as name2_1_0_, student0_.gradeid as gradeid3_1_0_ from student student0_ where student0_.id=?6

7 Hibernate: select student0_.id as id1_1_0_, student0_.name as name2_1_0_, student0_.gradeid as gradeid3_1_0_ from student student0_ where student0_.id=?8

9 Hibernate: select student0_.id as id1_1_0_, student0_.name as name2_1_0_, student0_.gradeid as gradeid3_1_0_ from student student0_ where student0_.id=?10

11 Hibernate: select student0_.id as id1_1_0_, student0_.name as name2_1_0_, student0_.gradeid as gradeid3_1_0_ from student student0_ where student0_.id=?12

13 Hibernate: select student0_.id as id1_1_0_, student0_.name as name2_1_0_, student0_.gradeid as gradeid3_1_0_ from student student0_ where student0_.id=?14

15 Hibernate: select student0_.id as id1_1_0_, student0_.name as name2_1_0_, student0_.gradeid as gradeid3_1_0_ from student student0_ where student0_.id=?16

17 Hibernate: select student0_.id as id1_1_0_, student0_.name as name2_1_0_, student0_.gradeid as gradeid3_1_0_ from student student0_ where student0_.id=?

通过list方式查询产生的sql语句如下

1 Hibernate: select student0_.id as id1_1_, student0_.name as name2_1_, student0_.gradeid as gradeid3_1_ from student student0_

两者相比较，list()只发一条语句将符合条件的数据全部查出，而iterator()却现将id查出来，然后根据id再将符合条件的数据查出，这就构成了N＋1的问题；既然list更高效，为什么hibernate还将iterator存在呢？

执行以下程序

1 public classHibernateTest {2 public static voidmain(String[] args) {3

4 Session session =HibernateFactory.currentSession();5 Transaction tx =session.beginTransaction();6 String hql = "from Student";7

8 Query listQuery =session.createQuery(hql);9 List list =listQuery.list();10 for(Student student : list) {11 System.out.println(student.getName());12 }13

14 Query iteratorQuery =session.createQuery(hql);15 Iterator iterator =iteratorQuery.iterate();16 while(iterator.hasNext()) {17 Student student =(Student)iterator.next();18 System.out.println(student.getName());19 }20

21 tx.commit();22 HibernateFactory.closeSession();23 }24 }

最终产生的sql语句如下

1 Hibernate: select student0_.id as id1_1_, student0_.name as name2_1_, student0_.gradeid as gradeid3_1_ fromstudent student0_2 Hibernate: select student0_.id as col_0_0_ from student student0_

将list()和iterator()放在一起使用，这时的iterator只执行了一条SQL，原因在于hibernate的缓存机制

list()方法将执行Select SQL从数据库中获取所有符合满足条件的记录并构造相应的实体对象，实体对象构建完毕后，就将其纳入缓存；

这样等到iterator()执行时，首先会执行一条SQL来查询符合条件数据的id，随即，iterator方法首先在本地缓存内根据id查找对应的实体对象是否存在，如果缓存中已经存在对应的数据，则直接以此数据对象作为查询结果；如果没有找到，则再次执行Select语句获得对应数据库中的表记录(如果iterator在数据库中查到并构建了完整的数据对象，也会将其纳入缓存中);

list()方法无法读取缓存，但它可以写入缓存，在上面这个实例中，list()将读取的数据放入缓存中，iterator()直接可以用于是出现了以上的结果；

如果目标数据只读或者读取相当频繁，可以使用iterator()来减少性能上的消耗；

weixin_39914938

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
hibernate mysql查询_hibernate 数据库查询

hibernate提供了多种方式进行数据库数据的查询HQL查询代码如下1 public classHibernateTest {2 public static voidmain(String[] args) {3 Session session =HibernateFactory.currentSession();4 Transaction tx =sess...
复制链接

扫一扫