Hibernate查询(Query Language)
查询方式:
1. NativeSQL (Mysql, oracle, sql server等)>HQL>EJBQL
2. QL应该与导航关系结合, 共同为查询提供服务
category->topic: one to more:
package com.bjsxt.hibernate; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; @Entity public class Category { private int id; private String name; @Id @GeneratedValue public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } }
Topic->msg: one to more:
package com.bjsxt.hibernate; import java.util.Date; import javax.persistence.Entity; import javax.persistence.FetchType; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.ManyToOne; @Entity public class Topic { private int id; private String title; private Category category; //private Category category2; private Date createDate; public Date getCreateDate() { return createDate; } public void setCreateDate(Date createDate) { this.createDate = createDate; } @ManyToOne(fetch=FetchType.LAZY) public Category getCategory() { return category; } public void setCategory(Category category) { this.category = category; } @Id @GeneratedValue public int getId() { return id; } public void setId(int id) { this.id = id; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } }
msg.java;
package com.bjsxt.hibernate; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.ManyToOne; @Entity public class Msg { private int id; private String cont; private Topic topic; @ManyToOne public Topic getTopic() { return topic; } public void setTopic(Topic topic) { this.topic = topic; } @Id @GeneratedValue public int getId() { return id; } public void setId(int id) { this.id = id; } public String getCont() { return cont; } public void setCont(String cont) { this.cont = cont; } }
hibernate.cfg.xml:
<?xml version='1.0' encoding='utf-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/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://localhost/hibernate</property> <property name="connection.username">root</property> <property name="connection.password">linda0213</property> <property name="dialect">org.hibernate.dialect.MySQLDialect</property> <!-- <property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property> <property name="connection.url">jdbc:oracle:thin:@localhost:1521:SXT</property> <property name="connection.username">scott</property> <property name="connection.password">tiger</property> <property name="dialect">org.hibernate.dialect.OracleDialect</property> --> <!-- JDBC connection pool (use the built-in) --> <property name="connection.pool_size">1</property> <!-- Enable Hibernate's automatic session context management --> <property name="current_session_context_class">thread</property> <!-- Disable the second-level cache --> <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property> <!-- Echo all executed SQL to stdout --> <property name="show_sql">true</property> <property name="format_sql">true</property> <!-- Drop and re-create the database schema on startup <property name="hbm2ddl.auto">update</property> --> <!-- <mapping resource="com/bjsxt/hibernate/Group.hbm.xml"/> <mapping resource="com/bjsxt/hibernate/User.hbm.xml"/> --> <mapping class="com.bjsxt.hibernate.Category"/> <mapping class="com.bjsxt.hibernate.Msg"/> <mapping class="com.bjsxt.hibernate.Topic"/> </session-factory> </hibernate-configuration>
test文件:
package com.bjsxt.hibernate; import java.util.Date; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.cfg.AnnotationConfiguration; import org.hibernate.tool.hbm2ddl.SchemaExport; import org.junit.AfterClass; import org.junit.BeforeClass; import org.junit.Test; public class HibernateQLTest { private static SessionFactory sf; @BeforeClass public static void beforeClass() { sf = new AnnotationConfiguration().configure().buildSessionFactory(); } @AfterClass public static void afterClass() { sf.close(); } @Test public void testSchemaExport() { new SchemaExport(new AnnotationConfiguration().configure()).create(false, true); } @Test public void testSave() { Session session = sf.openSession(); session.beginTransaction(); for(int i=0; i<10; i++) { Category c = new Category(); c.setName("c" + i); session.save(c); } for(int i=0; i<10; i++) { Category c = new Category(); c.setId(1); Topic t = new Topic(); t.setCategory(c); t.setTitle("t" + i); t.setCreateDate(new Date()); session.save(t); } for(int i=0; i<10; i++) { Topic t = new Topic(); t.setId(1); Msg m = new Msg(); m.setCont("m" + i); m.setTopic(t); session.save(m); } session.getTransaction().commit(); session.close(); } @Test public void testHQL_01() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("from Category"); List<Category> categories = (List<Category>)q.list(); for(Category c : categories) { System.out.println(c.getId()+" "+c.getName()); } session.getTransaction().commit(); session.close(); } @Test public void testHQL_02() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("from Category c where c.name > 'c5'"); List<Category> categories = (List<Category>)q.list(); for(Category c : categories) { System.out.println(c.getName()); } session.getTransaction().commit(); session.close(); } @Test public void testHQL_03() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("from Category c order by c.name desc"); List<Category> categories = (List<Category>)q.list(); for(Category c : categories) { System.out.println(c.getName()); } session.getTransaction().commit(); session.close(); } @Test public void testHQL_04() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("select distinct c from Category c order by c.name desc"); List<Category> categories = (List<Category>)q.list(); for(Category c : categories) { System.out.println(c.getName()); } session.getTransaction().commit(); session.close(); } @Test public void testHQL_05() { Session session = sf.openSession(); session.beginTransaction(); /*Query q = session.createQuery("from Category c where c.id > :min and c.id < :max"); //q.setParameter("min", 2); //q.setParameter("max", 8); q.setInteger("min", 2); q.setInteger("max", 8);*/ Query q = session.createQuery("from Category c where c.id > :min and c.id < :max") .setInteger("min", 2) .setInteger("max", 8); List<Category> categories = (List<Category>)q.list(); for(Category c : categories) { System.out.println(c.getId() + "-" + c.getName()); } session.getTransaction().commit(); session.close(); } @Test public void testHQL_06() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("from Category c where c.id > ? and c.id < ?"); q.setParameter(0, 2) .setParameter(1, 8); // q.setParameter(1, 8); List<Category> categories = (List<Category>)q.list(); for(Category c : categories) { System.out.println(c.getId() + "-" + c.getName()); } session.getTransaction().commit(); session.close(); } //分页 @Test public void testHQL_07() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("from Category c order by c.name desc"); q.setMaxResults(4); q.setFirstResult(2); List<Category> categories = (List<Category>)q.list(); for(Category c : categories) { System.out.println(c.getId() + "-" + c.getName()); } session.getTransaction().commit(); session.close(); } @Test public void testHQL_08() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("select c.id, c.name from Category c order by c.name desc"); List<Object[]> categories = (List<Object[]>)q.list(); for(Object[] o : categories) { System.out.println(o[0] + "-" + o[1]); } session.getTransaction().commit(); session.close(); } //Topic 设定fetch type为lazy后, 将不会产生第二条sql语句. @Test public void testHQL_09() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("from Topic t where t.category.id = 1"); List<Topic> topics = (List<Topic>)q.list(); for(Topic t : topics) { System.out.println(t.getTitle()); //System.out.println(t.getCategory().getName()); } session.getTransaction().commit(); session.close(); } //�趨fetch type Ϊlazy�����еڶ���sql��� @Test public void testHQL_10() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("from Topic t where t.category.id = 1"); List<Topic> topics = (List<Topic>)q.list(); for(Topic t : topics) { System.out.println(t.getTitle()); } session.getTransaction().commit(); session.close(); } @Test public void testHQL_11() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("from Msg m where m.topic.category.id = 1"); for(Object o : q.list()) { Msg m = (Msg)o; System.out.println(m.getCont()); } session.getTransaction().commit(); session.close(); } //了解即可 //VO Value Object //DTO data transfer object @Test public void testHQL_12() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("select new com.bjsxt.hibernate.MsgInfo(m.id, m.cont, m.topic.title, m.topic.category.name) from Msg"); for(Object o : q.list()) { MsgInfo m = (MsgInfo)o; System.out.println(m.getCont()); } session.getTransaction().commit(); session.close(); } //动手测试left right join //为什么不能直接写Category名, 而必须写t.category //因为有可能存在多个成员变量(同一个类), 需要指明用哪一个成员变量的连接条件做连接 @Test public void testHQL_13() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("select t.title, c.name from Topic t join t.category c "); //join Category c for(Object o : q.list()) { Object[] m = (Object[])o; System.out.println(m[0] + "-" + m[1]); } session.getTransaction().commit(); session.close(); } //学习使用uniqueResult @Test public void testHQL_14() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("from Msg m where m = :MsgToSearch "); //不重要 Msg m = new Msg(); m.setId(1); q.setParameter("MsgToSearch", m); Msg mResult = (Msg)q.uniqueResult(); System.out.println(mResult.getCont()); session.getTransaction().commit(); session.close(); } @Test public void testHQL_15() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("select count(*) from Msg m"); long count = (Long)q.uniqueResult(); System.out.println(count); session.getTransaction().commit(); session.close(); } @Test public void testHQL_16() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("select max(m.id), min(m.id), avg(m.id), sum(m.id) from Msg m"); Object[] o = (Object[])q.uniqueResult(); System.out.println(o[0] + "-" + o[1] + "-" + o[2] + "-" + o[3]); session.getTransaction().commit(); session.close(); } @Test public void testHQL_17() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("from Msg m where m.id between 3 and 5"); for(Object o : q.list()) { Msg m = (Msg)o; System.out.println(m.getId() + "-" + m.getCont()); } session.getTransaction().commit(); session.close(); } @Test public void testHQL_18() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("from Msg m where m.id in (3,4, 5)"); for(Object o : q.list()) { Msg m = (Msg)o; System.out.println(m.getId() + "-" + m.getCont()); } session.getTransaction().commit(); session.close(); } //is null �� is not null @Test public void testHQL_19() { Session session = sf.openSession(); session.beginTransaction(); Query q = session.createQuery("from Msg m where m.cont is not null"); for(Object o : q.list()) { Msg m = (Msg)o; System.out.println(m.getId() + "-" + m.getCont()); } session.getTransaction().commit(); session.close(); } public static void main(String[] args) { beforeClass(); } }