hibernate之动态生成SQL

 

动态sql生成

 

默认情况下，hibernate在启动时给每个持久化类创建SQL语句。这些语句是用来读取单个行，删除一行等的简单创建，读取，更新和删除。

hibernate在启动时如何创建UPDATE语句？毕竟，这时候还不知道要更新的列。答案是生成的SQL语句更新所有的列，并且如果特定列的值没有被修改，这个语句就会把它设置为它的旧值。

在有些情况下，例如包含几百列的一个遗留表，在该表中，即使最简单的操作(假使只有一个列需要更新)的SQL语句也将很大，必须关闭这个启动时的SQL生成，并切换到运行时生成的动态语句。

<class>映射元素中有两个属性可以禁用启动时CRUD SQL的生成：

<class name="Item" dynamic-insert="true" dynamic-update="true">

...

</class>

dynamic-update="true" : 动态生成update-SQL语句，只update被修改的字段。

dynamic-insert="true" : 动态生成insert-SQL语句，只包含非空值的字段。

 

---

 

pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>hibernateTest</groupId><artifactId>hibernateTest</artifactId><version>1.0-SNAPSHOT</version><packaging>jar</packaging><name>hibernateTest</name><url>http://maven.apache.org</url><dependencies><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>3.8.1</version><scope>test</scope></dependency><dependency><groupId>org.hibernate</groupId><artifactId>hibernate-core</artifactId><version>3.3.1.GA</version></dependency><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-nop</artifactId><version>1.5.2</version></dependency><dependency><groupId>javassist</groupId><artifactId>javassist</artifactId><version>3.4.GA</version></dependency><dependency><groupId>org.hibernate</groupId><artifactId>hibernate-proxool</artifactId><version>3.3.1.GA</version></dependency><dependency><groupId>com.oracle</groupId><artifactId>ojdbc14</artifactId><version>10.2.0.3.0</version><scope>runtime</scope></dependency></dependencies><build><finalName>hibernateTest</finalName><resources><resource><directory>src/main/resources</directory></resource><resource><directory>src/main/java</directory><excludes><exclude>**/*.java</exclude></excludes></resource></resources><plugins><plugin><artifactId>maven-compiler-plugin</artifactId><configuration><source>1.6</source><target>1.6</target><encoding>UTF-8</encoding></configuration></plugin></plugins></build></project> 

 

resources/proxool.xml:

<?xml version="1.0" encoding="UTF-8"?><something-else-entirely><proxool><!-- 连接池的别名 --><alias>proxool</alias><!-- proxool只能管理由自己产生的连接 --><driver-url>jdbc:oracle:thin:@localhost:1521:XE</driver-url><!--JDBC驱动程序--><driver-class>oracle.jdbc.driver.OracleDriver</driver-class><!-- 用户名与密码 --><driver-properties><property name="user" value="system" /><property name="password" value="password" /></driver-properties><!-- 允许最大连接数 ,默认是15,这里设置为20--><maximum-connection-count>20</maximum-connection-count><!-- 最小连接数 ,默认是5,其实可以不用声明它--><minimum-connection-count>5</minimum-connection-count><!-- 测试连接的sql语句 --><house-keeping-test-sql>select sysdate from dual</house-keeping-test-sql></proxool></something-else-entirely> 

 

resources/hibernate.cfg.xml:

<?xml version="1.0" encoding="utf-8"?><!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"><hibernate-configuration><session-factory name="sessionFactory"><property name="hibernate.connection.provider_class">org.hibernate.connection.ProxoolConnectionProvider</property><property name="hibernate.proxool.pool_alias">proxool</property><property name="hibernate.proxool.xml">proxool.xml</property><!-- 输出sql --><property name="hibernate.show_sql">true</property><!-- 格式化sql --><property name="hibernate.format_sql">true</property><!--指定数据库适配器 --><property name="dialect"> org.hibernate.dialect.OracleDialect </property><!-- 映射文件 --><mapping resource="hibernateTest/Student.hbm.xml" /></session-factory></hibernate-configuration> 

 

HibernateTest/Student.java:

package hibernateTest;public class Student {private int id;private String name;private String address;private int age;public int getId() {return id;}//主键由数据库提供的，这里最好设置为private@SuppressWarnings("unused")private void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}} 

 

HibernateTest/Student.hbm.xml:

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"><hibernate-mapping><class name="hibernateTest.Student" table="STUDENT" dynamic-insert="true" dynamic-update="true"><id name="id" column="ID" type="int"><generator class="sequence"><!-- seq_student就是表student的主键自增的sequence --><param name="sequence">seq_student</param></generator></id><property name="name" column="name" type="string"/><property name="address" column="address" type="string"/><property name="age" column="age" type="int"/></class></hibernate-mapping> 

 

util/HibernateUtil.java:

package util;import org.hibernate.SessionFactory;import org.hibernate.cfg.Configuration;public class HibernateUtil {private static SessionFactory sessionFactory;static{try {sessionFactory = new Configuration().configure().buildSessionFactory();} catch (Throwable e) {throw new ExceptionInInitializerError(e);}}public static SessionFactory getSessionFactory(){return sessionFactory;}public static void shutdown(){getSessionFactory().close();}} 

 

util/StudentManager.java:

package util;import hibernateTest.Student;import org.hibernate.Session;import org.hibernate.Transaction;public class StudentManager {public static void main(String[] args) {Student s = new Student();s.setName("刘德华");s.setAge(45);//打开一个普通的sessionSession session = HibernateUtil.getSessionFactory().openSession();Transaction transaction1 = session.beginTransaction();session.save(s);s.setAddress("湖北");transaction1.commit();session.close();}} 

 

打印的sql为：

Hibernate: select seq_student.nextval from dualHibernate: insert into STUDENT (name, age, ID) values (?, ?, ?)----这里已经没有了addressHibernate: update STUDENT set address=? -----这里也只更新了address where ID=?