Hibernate实战——条件查询 分手后的思念是犯贱 2021-07-25 01:06 451阅读 0赞 **一 配置** <?xml version="1.0" encoding="GBK"?> <!-- 指定Hibernate配置文件的DTD信息 --> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> <!-- hibernate-configuration是配置文件的根元素 --> <hibernate-configuration> <session-factory> <!-- 指定连接数据库所用的驱动 --> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <!-- 指定连接数据库的url,其中hibernate是本应用连接的数据库名 --> <property name="connection.url">jdbc:mysql://localhost/hibernate</property> <!-- 指定连接数据库的用户名 --> <property name="connection.username">root</property> <!-- 指定连接数据库的密码 --> <property name="connection.password">32147</property> <!-- 指定连接池里最大连接数 --> <property name="hibernate.c3p0.max_size">200</property> <!-- 指定连接池里最小连接数 --> <property name="hibernate.c3p0.min_size">2</property> <!-- 指定连接池里连接的超时时长 --> <property name="hibernate.c3p0.timeout">5000</property> <!-- 指定连接池里最大缓存多少个Statement对象 --> <property name="hibernate.c3p0.max_statements">100</property> <property name="hibernate.c3p0.idle_test_period">3000</property> <property name="hibernate.c3p0.acquire_increment">2</property> <property name="hibernate.c3p0.validate">true</property> <!-- 指定数据库方言 --> <property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property> <!-- 根据需要自动创建数据库 --> <property name="hbm2ddl.auto">update</property> <!-- 显示Hibernate持久化操作所生成的SQL --> <property name="show_sql">true</property> <!-- 将SQL脚本进行格式化后再输出 --> <property name="hibernate.format_sql">true</property> <!-- 罗列所有持久化类的类名 --> <mapping class="org.crazyit.app.domain.Enrolment"/> <mapping class="org.crazyit.app.domain.Student"/> <mapping class="org.crazyit.app.domain.Course"/> </session-factory> </hibernate-configuration> **二 PO** Student package org.crazyit.app.domain; import java.util.*; import javax.persistence.*; @Entity @Table(name="student_inf") public class Student { // 代表学生学号的成员变量,将作为标识属性 @Id @Column(name="student_id") private Integer studentNumber; // 代表学生姓名的成员变量 private String name; // 该学生的所有选课记录对应的关联实体 @OneToMany(targetEntity=Enrolment.class , mappedBy="student" , cascade=CascadeType.REMOVE) private Set<Enrolment> enrolments = new HashSet<>(); // 无参数的构造器 public Student() { } // 初始化全部成员变量的构造器 public Student(Integer studentNumber , String name) { this.studentNumber = studentNumber; this.name = name; } // studentNumber的setter和getter方法 public void setStudentNumber(Integer studentNumber) { this.studentNumber = studentNumber; } public Integer getStudentNumber() { return this.studentNumber; } // name的setter和getter方法 public void setName(String name) { this.name = name; } public String getName() { return this.name; } // enrolments的setter和getter方法 public void setEnrolments(Set<Enrolment> enrolments) { this.enrolments = enrolments; } public Set<Enrolment> getEnrolments() { return this.enrolments; } } Enrolment package org.crazyit.app.domain; import javax.persistence.*; @Entity @Table(name = "enrolment_inf") public class Enrolment { // 定义标识属性 @Id @Column(name = "enrolment_id") @GeneratedValue(strategy = GenerationType.IDENTITY) private Integer enrolmentId; // 定义选课记录所属的学年 private int year; // 定义选课记录所属的学期 private int semester; // 定义选课记录关联的学生实体 @ManyToOne(targetEntity = Student.class) @JoinColumn(name = "student_id") private Student student; // 定义选课记录关联的课程实体 @ManyToOne(targetEntity = Course.class) @JoinColumn(name = "course_code") private Course course; // enrolmentId的setter和getter方法 public void setEnrolmentId(Integer enrolmentId) { this.enrolmentId = enrolmentId; } public Integer getEnrolmentId() { return this.enrolmentId; } // year的setter和getter方法 public void setYear(int year) { this.year = year; } public int getYear() { return this.year; } // semester的setter和getter方法 public void setSemester(int semester) { this.semester = semester; } public int getSemester() { return this.semester; } // student的setter和getter方法 public void setStudent(Student student) { this.student = student; } public Student getStudent() { return this.student; } // course的setter和getter方法 public void setCourse(Course course) { this.course = course; } public Course getCourse() { return this.course; } } Course package org.crazyit.app.domain; import javax.persistence.*; @Entity @Table(name="course_inf") public class Course { // 代表课程编号的成员变量,将作为标识属性 @Id @Column(name="course_code") private String courseCode; // 代表课程名成员变量 private String name; // 无参数的构造器 public Course() { } // 初始化全部成员变量的构造器 public Course(String courseCode , String name) { this.courseCode = courseCode; this.name = name; } // courseCode的setter和getter方法 public void setCourseCode(String courseCode) { this.courseCode = courseCode; } public String getCourseCode() { return this.courseCode; } // name的setter和getter方法 public void setName(String name) { this.name = name; } public String getName() { return this.name; } } **三 测试** 1 工具类 package lee; import org.hibernate.*; import org.hibernate.cfg.*; import org.hibernate.service.*; import org.hibernate.boot.registry.*; public class HibernateUtil { public static final SessionFactory sessionFactory; static { try { // 使用默认的hibernate.cfg.xml配置文件创建Configuration实例 Configuration cfg = new Configuration() .configure(); // 以Configuration实例来创建SessionFactory实例 ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder() .applySettings(cfg.getProperties()).build(); sessionFactory = cfg.buildSessionFactory(serviceRegistry); } catch (Throwable ex) { System.err.println("Initial SessionFactory creation failed." + ex); throw new ExceptionInInitializerError(ex); } } // ThreadLocal可以隔离多个线程的数据共享,因此不再需要对线程同步 public static final ThreadLocal<Session> session = new ThreadLocal<Session>(); public static Session currentSession() throws HibernateException { Session s = session.get(); // 如果该线程还没有Session,则创建一个新的Session if (s == null) { s = sessionFactory.openSession(); // 将获得的Session变量存储在ThreadLocal变量session里 session.set(s); } return s; } public static void closeSession() throws HibernateException { Session s = session.get(); if (s != null) s.close(); session.set(null); } } 2 测试类 package lee; import org.hibernate.*; import org.hibernate.criterion.Criterion; import org.hibernate.criterion.Restrictions; import org.crazyit.app.domain.*; import java.util.*; public class CriteriaTest { public static void main(String[] args) { CriteriaTest criteriaTest = new CriteriaTest(); criteriaTest.query(); criteriaTest.queryWithJoin(); criteriaTest.queryWithFecth(); HibernateUtil.sessionFactory.close(); } private void query() { // 打开Session和事务 Session session = HibernateUtil.currentSession(); Transaction tx = session.beginTransaction(); // 使用createCriteria开始条件查询 List list = session.createCriteria(Student.class) // 根据Student的属性进行过滤数据 .add(Restrictions.gt("name", "a")).list(); System.out.println("=====简单条件查询获取所有学生记录====="); for (Object obj : list) { Student s = (Student) obj; System.out.println(s.getName()); Set<Enrolment> enrolments = s.getEnrolments(); System.out.println("=====获取-" + s.getName() + "-的选课记录====="); for (Enrolment e : enrolments) { System.out.println(e.getCourse().getName()); } } tx.commit(); HibernateUtil.closeSession(); } // 示范根据关联实体的属性过滤数据 private void queryWithJoin() { // 打开Session和事务 Session session = HibernateUtil.currentSession(); Transaction tx = session.beginTransaction(); // 使用createCriteria开始条件查询 List list = session.createCriteria(Student.class) // 此处增加限制条件必须是Student实体存在的属性 .add(Restrictions.gt("studentNumber", 20050231)) // 如果要增加对Student的关联类的属性的限制 // 则必须重新createCriteria() // 如果此关联属性是集合,则只要集合里任意一个对象的属性满足下面条件即可 // .createCriteria("enrolments") // .add( Restrictions.gt("semester" , 2)) .createAlias("enrolments", "en").add(Restrictions.gt("en.semester", 2)).list(); System.out.println("=====关联条件查询获取所有学生记录====="); for (Object obj : list) { Student s = (Student) obj; System.out.println(s.getName()); Set<Enrolment> enrolments = s.getEnrolments(); System.out.println("=====获取-" + s.getName() + "-的选课记录====="); for (Enrolment e : enrolments) { System.out.println(e.getCourse().getName()); } } tx.commit(); HibernateUtil.closeSession(); } // 示范FetchMode的用法 private void queryWithFecth() { // 打开Session和事务 Session session = HibernateUtil.currentSession(); Transaction tx = session.beginTransaction(); // 使用createCriteria开始条件查询 List list = session.createCriteria(Student.class) // 此处增加限制条件必须是Student实体存在的属性 .add(Restrictions.gt("studentNumber", 20050231)).setFetchMode("enrolments", FetchMode.JOIN).list(); tx.commit(); HibernateUtil.closeSession(); // ① System.out.println("=====启用预初始化的条件查询获取所有学生记录====="); for (Object obj : list) { Student s = (Student) obj; System.out.println(s.getName()); // Session关闭后访问Student的关联实体 Set<Enrolment> enrolments = s.getEnrolments(); // ② System.out.println(enrolments); } } } **四 数据脚本** drop database if exists hibernate; create database hibernate; use hibernate; CREATE TABLE course_inf ( course_code varchar(255) NOT NULL, name varchar(255) default NULL, PRIMARY KEY (course_code) ); INSERT INTO course_inf VALUES ('001','疯狂Java讲义'), ('002','轻量级Java EE企业应用实战'), ('003','疯狂Android讲义'); CREATE TABLE student_inf ( student_id int NOT NULL, name varchar(255) NOT NULL, PRIMARY KEY (student_id) ); INSERT INTO student_inf VALUES (20050231,'孙悟空'), (20050232,'猪八戒'), (20050233,'牛魔王'); CREATE TABLE enrolment_inf ( enrolment_id int(11) NOT NULL auto_increment, semester int(11) NOT NULL, year int(11) NOT NULL, student_id int default NULL, course_code varchar(255) default NULL, PRIMARY KEY (enrolment_id), KEY FKEB4882C47EDE09EE (course_code), KEY FKEB4882C4109881D8 (student_id), FOREIGN KEY (student_id) REFERENCES student_inf (student_id), FOREIGN KEY (course_code) REFERENCES course_inf (course_code) ); INSERT INTO enrolment_inf VALUES (1,3,2005,20050232,'001'), (2,2,2005,20050232,'003'), (3,2,2005,20050233,'002'), (4,3,2005,20050233,'003'), (5,1,2005,20050231,'002'); ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70][] **五 测试** Hibernate: select this_.student_id as student_1_2_0_, this_.name as name2_2_0_ from student_inf this_ where this_.name>? =====1 简单条件查询获取所有学生记录===== 孙悟空 =====获取-孙悟空-的选课记录===== Hibernate: select enrolments0_.student_id as student_5_2_0_, enrolments0_.enrolment_id as enrolmen1_1_0_, enrolments0_.enrolment_id as enrolmen1_1_1_, enrolments0_.course_code as course_c4_1_1_, enrolments0_.semester as semester2_1_1_, enrolments0_.student_id as student_5_1_1_, enrolments0_.year as year3_1_1_, course1_.course_code as course_c1_0_2_, course1_.name as name2_0_2_ from enrolment_inf enrolments0_ left outer join course_inf course1_ on enrolments0_.course_code=course1_.course_code where enrolments0_.student_id=? 轻量级Java EE企业应用实战 猪八戒 =====获取-猪八戒-的选课记录===== Hibernate: select enrolments0_.student_id as student_5_2_0_, enrolments0_.enrolment_id as enrolmen1_1_0_, enrolments0_.enrolment_id as enrolmen1_1_1_, enrolments0_.course_code as course_c4_1_1_, enrolments0_.semester as semester2_1_1_, enrolments0_.student_id as student_5_1_1_, enrolments0_.year as year3_1_1_, course1_.course_code as course_c1_0_2_, course1_.name as name2_0_2_ from enrolment_inf enrolments0_ left outer join course_inf course1_ on enrolments0_.course_code=course1_.course_code where enrolments0_.student_id=? 疯狂Java讲义 疯狂Android讲义 牛魔王 =====获取-牛魔王-的选课记录===== Hibernate: select enrolments0_.student_id as student_5_2_0_, enrolments0_.enrolment_id as enrolmen1_1_0_, enrolments0_.enrolment_id as enrolmen1_1_1_, enrolments0_.course_code as course_c4_1_1_, enrolments0_.semester as semester2_1_1_, enrolments0_.student_id as student_5_1_1_, enrolments0_.year as year3_1_1_, course1_.course_code as course_c1_0_2_, course1_.name as name2_0_2_ from enrolment_inf enrolments0_ left outer join course_inf course1_ on enrolments0_.course_code=course1_.course_code where enrolments0_.student_id=? 轻量级Java EE企业应用实战 疯狂Android讲义 Hibernate: select this_.student_id as student_1_2_3_, this_.name as name2_2_3_, en1_.enrolment_id as enrolmen1_1_0_, en1_.course_code as course_c4_1_0_, en1_.semester as semester2_1_0_, en1_.student_id as student_5_1_0_, en1_.year as year3_1_0_, course4_.course_code as course_c1_0_1_, course4_.name as name2_0_1_, student5_.student_id as student_1_2_2_, student5_.name as name2_2_2_ from student_inf this_ inner join enrolment_inf en1_ on this_.student_id=en1_.student_id left outer join course_inf course4_ on en1_.course_code=course4_.course_code left outer join student_inf student5_ on en1_.student_id=student5_.student_id where this_.student_id>? and en1_.semester>? =====2 关联条件查询获取所有学生记录===== 猪八戒 =====获取-猪八戒-的选课记录===== Hibernate: select enrolments0_.student_id as student_5_2_0_, enrolments0_.enrolment_id as enrolmen1_1_0_, enrolments0_.enrolment_id as enrolmen1_1_1_, enrolments0_.course_code as course_c4_1_1_, enrolments0_.semester as semester2_1_1_, enrolments0_.student_id as student_5_1_1_, enrolments0_.year as year3_1_1_, course1_.course_code as course_c1_0_2_, course1_.name as name2_0_2_ from enrolment_inf enrolments0_ left outer join course_inf course1_ on enrolments0_.course_code=course1_.course_code where enrolments0_.student_id=? 疯狂Android讲义 疯狂Java讲义 牛魔王 =====获取-牛魔王-的选课记录===== Hibernate: select enrolments0_.student_id as student_5_2_0_, enrolments0_.enrolment_id as enrolmen1_1_0_, enrolments0_.enrolment_id as enrolmen1_1_1_, enrolments0_.course_code as course_c4_1_1_, enrolments0_.semester as semester2_1_1_, enrolments0_.student_id as student_5_1_1_, enrolments0_.year as year3_1_1_, course1_.course_code as course_c1_0_2_, course1_.name as name2_0_2_ from enrolment_inf enrolments0_ left outer join course_inf course1_ on enrolments0_.course_code=course1_.course_code where enrolments0_.student_id=? 轻量级Java EE企业应用实战 疯狂Android讲义 Hibernate: select this_.student_id as student_1_2_2_, this_.name as name2_2_2_, enrolments2_.student_id as student_5_2_4_, enrolments2_.enrolment_id as enrolmen1_1_4_, enrolments2_.enrolment_id as enrolmen1_1_0_, enrolments2_.course_code as course_c4_1_0_, enrolments2_.semester as semester2_1_0_, enrolments2_.student_id as student_5_1_0_, enrolments2_.year as year3_1_0_, course3_.course_code as course_c1_0_1_, course3_.name as name2_0_1_ from student_inf this_ left outer join enrolment_inf enrolments2_ on this_.student_id=enrolments2_.student_id left outer join course_inf course3_ on enrolments2_.course_code=course3_.course_code where this_.student_id>? =====3 启用预初始化的条件查询获取所有学生记录===== 猪八戒 [org.crazyit.app.domain.Enrolment@736caf7a, org.crazyit.app.domain.Enrolment@3da05287] 猪八戒 [org.crazyit.app.domain.Enrolment@736caf7a, org.crazyit.app.domain.Enrolment@3da05287] 牛魔王 [org.crazyit.app.domain.Enrolment@497486b3, org.crazyit.app.domain.Enrolment@6127a7e] 牛魔王 [org.crazyit.app.domain.Enrolment@497486b3, org.crazyit.app.domain.Enrolment@6127a7e] [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70]: /images/20210724/bd8af4f9c22249e2b57cfef4539c721f.png
还没有评论,来说两句吧...