了解hibernate中的native SQL查询

hibernate提供了很多种查询方式，也支持原始的SQL查询。

1.Scalar queries

The most basic SQL query is to get a list of scalars (values).

sess.createSQLQuery("SELECT * FROM CATS").list();
sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();

These will return a List of Object arrays (Object[]) with scalar values for each column in the CATS table. Hibernate will use ResultSetMetadata to deduce the actual order and types of the returned scalar values.

To avoid the overhead of using ResultSetMetadata, or simply to be more explicit in what is returned, one can use addScalar():

SQLQuery query = session.createSQLQuery(sql);
query.addScalar("id", IntegerType.INSTANCE)
		.addScalar("name", StringType.INSTANCE)
		.addScalar("age", IntegerType.INSTANCE);

也就是说：通过addScalar()显示地设置列的数据类型，能够避免hibernate自己使用ResultSetMetadata去检查数据库中列的实际类型，有一定的性能优势。不过这种方式一般没有啥用，我们虽然使用SQL，但还是希望返回值是对象，而不是Object[]。

 2.Entity queries

The above queries were all about returning scalar values, basically returning the "raw" values from the resultset. The following shows how to get entity objects from a native sql query via addEntity().

sess.createSQLQuery("SELECT * FROM CATS").addEntity(Cat.class);
sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").addEntity(Cat.class);

使用addEntity()，还可以设置表的别名：

@Test
public void test3() {
	Session session = sessionFactory.openSession();

	String sql = "select {c.*} from student {c}";

	SQLQuery query = session.createSQLQuery(sql);
	query.addEntity("c", Student.class);

	System.out.println(query.list());
}

如果只是单表查询，这种设置别名的写法意义不大。但是如果查询多张表，而且这些表中存在同名字段，那么这种写法就很有必要了，能够帮助hibernate读取正确的列。

@Test
public void testMultiEntities() {
	Session session = sessionFactory.openSession();

	String sql = "select c.*, s.* from t_association_college c, t_association_student s where c.cid=s.college_id";

	SQLQuery query = session.createSQLQuery(sql);
	query.addEntity(College.class).addEntity(Student.class);

	List<Object[]> results = (List<Object[]>) query.list();

	System.out.println("validate whether lazy load.");

	for (Object[] each : results) {
		System.out.println("college=" + (College) each[0]);
		System.out.println("student=" + (Student) each[1]);
	}
}

这种写法，如果college表和student表没有重名的列，没有什么问题。最好使用下面这种写法，能够避免列重名等影响。

@Test
public void testAvoidRepeatName() {
	Session session = sessionFactory.openSession();

	String sql = "select {c.*}, {s.*} from t_association_college c, t_association_student s where c.cid=s.college_id";

	SQLQuery query = session.createSQLQuery(sql);
	query.addEntity("c", College.class).addEntity("s", Student.class);

	List<Object[]> results = (List<Object[]>) query.list();

	System.out.println("validate whether lazy load.");

	for (Object[] each : results) {
		System.out.println("college=" + (College) each[0]);
		System.out.println("student=" + (Student) each[1]);
	}

	session.close();
}

3.Parameters

Native SQL queries support positional as well as named parameters:

Query query = sess.createSQLQuery("SELECT * FROM CATS WHERE NAME like ?").addEntity(Cat.class);
List pusList = query.setString(0, "Pus%").list();
     
query = sess.createSQLQuery("SELECT * FROM CATS WHERE NAME like :name").addEntity(Cat.class);
List pusList = query.setString("name", "Pus%").list();         

原始的JDBC占位符参数只能使用?，hibernate除了支持这种写法外，还支持命名参数。显然使用命名参数，可读性更好。

4.associations

College和Student配置了One-to-many单向关联，使用SQL一样可以关联查询：

@Test
public void testImmediateLoad() {
	Session session = sessionFactory.openSession();

	String sql = "select * from t_association_college c, t_association_student s where c.cid=s.college_id";

	SQLQuery query = session.createSQLQuery(sql);
	query.addEntity("c", College.class).addJoin("s", "c.allStudents");

	List<Object[]> results = (List<Object[]>) query.list();

	System.out.println("validate whether lazy load.");

	for (Object[] each : results) {
		System.out.println("college=" + (College) each[0]);
		System.out.println("student=" + (Student) each[1]);
	}

	session.close();
}

虽然关联集合set没有懒加载，但是只发出了一条SQL语句就完成了查询，这种方式挺好。如果采用下面这种做法，会多查很多次数据库。这是为了查询college关联的student。

@Test
public void testAvoidRepeatName() {
	Session session = sessionFactory.openSession();

	String sql = "select {c.*}, {s.*} from t_association_college c, t_association_student s where c.cid=s.college_id";

	SQLQuery query = session.createSQLQuery(sql);
	query.addEntity("c", College.class).addEntity("s", Student.class);

	List<Object[]> results = (List<Object[]>) query.list();

	System.out.println("validate whether lazy load.");

	for (Object[] each : results) {
		System.out.println("college=" + (College) each[0]);
		System.out.println("student=" + (Student) each[1]);
	}

	session.close();
}