Hibernate的N+1条SQL查询问题-------Iterate

雨点打透心脏的1/2处 2022-06-11 05:58 183阅读 0赞

hibernate使用session.createQuery(hql)查询数据的时候，有两种查询方式：

1、一种是只查询一次，将所有要查询的数据都查询出来，后面直接取数据就可以了；

获取方式：session.createQuery(hql).list()。

一次性加载的数据多，效率低，用于例如商品信息展示。适用于展示所有信息。

2、另一种是先查询一次，将主键查询出来，后面需要数据的时候，根据主键一条一条的取数据。

获取方式：session.createQuery(hql).iterate()。

用于新闻列表展示。当需要查看新闻内容时，在去根据主键查询具体内容数据。

适用于仅仅展示某一条或几条信息。

下面用一个例子来演示一下

新建一个java项目，项目结构如下：

![Center][]

实体类Book代码：

package com.myeclipse.pojo;
    
    import java.util.Date;
    public class Book {
    	
    	private int id;
    	private String author;
    	private String name;
    	private double price;
    	private Date pubDate;
    	
    	public int getId() {
    		return id;
    	}
    	public void setId(int id) {
    		this.id = id;
    	}
    	public String getAuthor() {
    		return author;
    	}
    	public void setAuthor(String author) {
    		this.author = author;
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public double getPrice() {
    		return price;
    	}
    	public void setPrice(double price) {
    		this.price = price;
    	}
    	public Date getPubDate() {
    		return pubDate;
    	}
    	public void setPubDate(Date pubDate) {
    		this.pubDate = pubDate;
    	}
    	@Override
    	public String toString() {
    		return "Book [id=" + id + ", author=" + author + ", name=" + name
    				+ ", price=" + price + ", pubDate=" + pubDate + "]";
    	}
    	
    	
    }

Book.hbm.xml代码：

<?xml version="1.0" encoding="utf-8"?>
    <!DOCTYPE hibernate-mapping PUBLIC 
    	"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    	"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
    
    <hibernate-mapping package="com.myeclipse.pojo">
    	<class name="Book" table="t_book" lazy="false">
    		<id name="id">
    			<generator class="identity" />
    		</id>
    		<property name="author" />
    		<property name="name" column="book_name" />
    		<property name="price" />
    		<property name="pubDate" />
    	</class>
    
    </hibernate-mapping>

HibernateUtil代码：

package com.robert.util;
    
    import org.hibernate.Session;
    import org.hibernate.SessionFactory;
    import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
    import org.hibernate.cfg.Configuration;
    
    /**
     * hibernate工具类
     */
    public class HibernateUtil {
    
    	private static Configuration cfg = null;
    	private static SessionFactory factory = null;
    	private static Session session = null ;
    	
    	static {
    		init();
    	}
    
    	/**
    	 * 初始化获得Configuration和SessionFacroty对象
    	 */
    	public static void init() {
    		cfg = new Configuration().configure();
    		factory = cfg.buildSessionFactory(new StandardServiceRegistryBuilder()
    				.applySettings(cfg.getProperties()).build());
    	}
    
    	/**
    	 * 获得Session对象
    	 * @return
    	 */
    	public static Session getSession() {
    		if (factory != null){
    			return session = factory.openSession();
    		}
    		
    
    		init();
    		return session = factory.openSession();
    	}
    	
    	/**
    	 * 关闭Session
    	 */
    	public static void closeSession() {
    		if(session!=null && session.isOpen())
    			session.close();
    	}
    
    }

hibernate.cfg.xml代码：

<!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>
    	
    	<property name="connection.driver_class">
    		com.mysql.jdbc.Driver
    	</property>
    	<property name="connection.url">jdbc:mysql:///hibernate4</property>
    	<property name="connection.username">root</property>
    	<property name="connection.password">root</property>
    	
    	<property name="hibernate.dialect">
    		org.hibernate.dialect.MySQL5Dialect
    	</property>
    	
    	<property name="show_sql">true</property>
    	
    	<property name="format_sql">true</property>
    	
    	<property name="hbm2ddl.auto">update</property>
    	
    	<mapping resource="com/myeclipse/pojo/Book.hbm.xml" />
    
    </session-factory>
    </hibernate-configuration>

HibernateTest测试类代码：

package com.ghibernate.test;
    
    import java.util.Date;
    import java.util.Iterator;
    import java.util.List;
    
    import org.hibernate.Query;
    import org.hibernate.Session;
    import org.hibernate.Transaction;
    import org.hibernate.cfg.Configuration;
    import org.hibernate.tool.hbm2ddl.SchemaExport;
    import org.junit.Test;
    
    import com.myeclipse.pojo.Book;
    import com.robert.util.HibernateUtil;
    
    public class HibernateTest {
    
    	@Test
    	public void testCreateDB() {
    		Configuration cfg = new Configuration().configure();
    		SchemaExport se = new SchemaExport(cfg);
    		// 第一个参数：是否生成ddl脚本
    		// 第二个参数：是否执行到数据库中
    		se.create(true, true);
    	}
    
    	@Test
    	public void testSave() {
    
    		Session session = HibernateUtil.getSession();
    		Transaction tx = session.beginTransaction();
    
    		Book book = new Book();
    		book.setName("读者");
    		book.setPrice(5.6);
    		book.setAuthor("众人");
    		book.setPubDate(new Date());
    
    		Book book1 = new Book();
    		book1.setName("傲慢与偏见");
    		book1.setPrice(80.0);
    		book1.setAuthor("简.奥斯汀");
    		book1.setPubDate(new Date());
    
    		Book book2 = new Book();
    		book2.setName("中国历史");
    		book2.setPrice(30.0);
    		book2.setAuthor("人民出版社");
    		book2.setPubDate(new Date());
    
    		Book book3 = new Book();
    		book3.setName("翩眇之旅");
    		book3.setPrice(70.0);
    		book3.setAuthor("萧鼎");
    		book3.setPubDate(new Date());
    
    		Book book4 = new Book();
    		book4.setName("蓝血人");
    		book4.setPrice(60.0);
    		book4.setAuthor("卫斯理");
    		book4.setPubDate(new Date());
    
    		Book book5 = new Book();
    		book5.setName("我的大学");
    		book5.setPrice(60.5);
    		book5.setAuthor("高尔基");
    		book5.setPubDate(new Date());
    
    		session.save(book);
    		session.save(book1);
    		session.save(book2);
    		session.save(book3);
    		session.save(book4);
    		session.save(book5);
    
    		tx.commit();
    		HibernateUtil.closeSession();
    
    	}
    
    	@Test
    	public void testIterator() {
    		Session session = HibernateUtil.getSession();
    		Transaction tx = session.beginTransaction();
    		List<Book> list = session.createQuery("from Book").list() ;
    		for(Book book : list) {
    			System.out.println("书名："+book.getName());
    		}
    		tx.commit();
    		HibernateUtil.closeSession();
    		
    		System.out.println("-----------------上面是list方式-------------------------");
    		System.out.println("-----------------下面是iterator方式---------------------");
    		
    
    		session = HibernateUtil.getSession();
    		tx = session.beginTransaction();
    		Iterator<Book> iter = session.createQuery("from Book").iterate() ;
    		for (; iter.hasNext();) {
    			Book book = (Book) iter.next();
    			System.out.println("iterator遍历的书名："+book.getName());
    		}
    		tx.commit();
    		HibernateUtil.closeSession();
    
    	}
    
    
    
    }

先运行testCreateDB()，重新生成数据库表；

执行testSave(),保存数据；

执行testIterator()方法，生成的sql语句如下：

Hibernate: 
        select
            book0_.id as id1_0_,
            book0_.author as author2_0_,
            book0_.book_name as book_nam3_0_,
            book0_.price as price4_0_,
            book0_.pubDate as pubDate5_0_ 
        from
            t_book book0_
    书名：读者
    书名：傲慢与偏见
    书名：中国历史
    书名：翩眇之旅
    书名：蓝血人
    书名：我的大学
    -----------------上面是list方式-------------------------
    -----------------下面是iterator方式---------------------
    Hibernate: 
        select
            book0_.id as col_0_0_ 
        from
            t_book book0_
    Hibernate: 
        select
            book0_.id as id1_0_0_,
            book0_.author as author2_0_0_,
            book0_.book_name as book_nam3_0_0_,
            book0_.price as price4_0_0_,
            book0_.pubDate as pubDate5_0_0_ 
        from
            t_book book0_ 
        where
            book0_.id=?
    iterator遍历的书名：读者
    Hibernate: 
        select
            book0_.id as id1_0_0_,
            book0_.author as author2_0_0_,
            book0_.book_name as book_nam3_0_0_,
            book0_.price as price4_0_0_,
            book0_.pubDate as pubDate5_0_0_ 
        from
            t_book book0_ 
        where
            book0_.id=?
    iterator遍历的书名：傲慢与偏见
    Hibernate: 
        select
            book0_.id as id1_0_0_,
            book0_.author as author2_0_0_,
            book0_.book_name as book_nam3_0_0_,
            book0_.price as price4_0_0_,
            book0_.pubDate as pubDate5_0_0_ 
        from
            t_book book0_ 
        where
            book0_.id=?
    iterator遍历的书名：中国历史
    Hibernate: 
        select
            book0_.id as id1_0_0_,
            book0_.author as author2_0_0_,
            book0_.book_name as book_nam3_0_0_,
            book0_.price as price4_0_0_,
            book0_.pubDate as pubDate5_0_0_ 
        from
            t_book book0_ 
        where
            book0_.id=?
    iterator遍历的书名：翩眇之旅
    Hibernate: 
        select
            book0_.id as id1_0_0_,
            book0_.author as author2_0_0_,
            book0_.book_name as book_nam3_0_0_,
            book0_.price as price4_0_0_,
            book0_.pubDate as pubDate5_0_0_ 
        from
            t_book book0_ 
        where
            book0_.id=?
    iterator遍历的书名：蓝血人
    Hibernate: 
        select
            book0_.id as id1_0_0_,
            book0_.author as author2_0_0_,
            book0_.book_name as book_nam3_0_0_,
            book0_.price as price4_0_0_,
            book0_.pubDate as pubDate5_0_0_ 
        from
            t_book book0_ 
        where
            book0_.id=?
    iterator遍历的书名：我的大学

[Center]: /images/20220611/3a7a733987024ee89edd80e0d9572808.png