hibernate是支持sql语句查询的,他称之为原生sql,因为他的写法并不是纯sql......
解决Hibernate原生SQL映射问题 在问答里和论坛中,经常看到有人问,怎样将使用本地SQL查询出来的结果映射为值对象的问题,这里就Hibernate中提供的方法做个结论。前提,
这里没有使用属性的延迟加载技术。 假设有个值对像,如下: Java代码 1. package test; 2. 3. public class Person { 4. private Long id; 5. private String name; 6. private Long age; 7. private Long phone; 8. private String address; 9. 10. public Person(Long id, String name, Long age, Long phone, String address) { 11. this.id = id; 12. this.name = name; 13. this.age = age; 14. this.phone = phone; 15. this.address = address; 16. } 17. 18. public Long getId() { 19. return id; 20. } 21. 22. public void setId(Long id) { 23. this.id = id; 24. } 25. 26. public String getName() { 27. return name; 28. } 29. 30. public void setName(String name) { 31. this.name = name; 32. } 33. 34. public Long getAge() { 35. return age; 36. } 37. 38. public void setAge(Long age) { 39. this.age = age; 40. } 41. 42. public Long getPhone() { 43. return phone; 44. } 45. 46. public void setPhone(Long phone) { 47. this.phone = phone; 48. } 49. 50. public String getAddress() { 51. return address; 52. } 53. 54. public void setAddress(String address) { 55. this.address = address; 56. } 57. } 58. 59. package test; 60. 61. public class Person { 62. private Long id; 63. private String name; 64. private Long age; 65. private Long phone; 66. private String address; 67. 68. public Person(Long id, String name, Long age, Long phone, String address) { 69. this.id = id; 70. this.name = name; 71. this.age = age; 72. this.phone = phone; 73. this.address = address; 74. } 75. 76. public Long getId() { 77. return id; 78. } 79. 80. public void setId(Long id) { 81. this.id = id; 82. } 83. 84. public String getName() { 85. return name; 86. } 87. 88. public void setName(String name) { 89. this.name = name; 90. } 91. 92. public Long getAge() { 93. return age; 94. } 95. 96. public void setAge(Long age) { 97. this.age = age; 98. } 99. 100. public Long getPhone() { 101. return phone; 102. } 103. 104. public void setPhone(Long phone) { 105. this.phone = phone; 106. } 107. 108. public String getAddress() { 109. return address; 110. } 111. 112. public void setAddress(String address) { 113. this.address = address; 114. } 115. } package test; public class Person { private Long id; private String name; private Long age; private Long phone; private String address; public Person(Long id, String name, Long age, Long phone, String address) { this.id = id; this.name = name; this.age = age; this.phone = phone; this.address = address; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Long getAge() { return age; } public void setAge(Long age) { this.age = age; } public Long getPhone() { return phone; } public void setPhone(Long phone) { this.phone = phone; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } } package test; public class Person { private Long id; private String name; private Long age; private Long phone; private String address; public Person(Long id, String name, Long age, Long phone, String address) { this.id = id; this.name = name; this.age = age; this.phone = phone; this.address = address; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Long getAge() { return age; } public void setAge(Long age) { this.age = age; } public Long getPhone() { return phone; } public void setPhone(Long phone) { this.phone = phone; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } } 如果查询全部五列记录的话,那么只要 Java代码 1. List list = getHibernateTemplate().loadAll(Person.class); 2. 3. List list = getHibernateTemplate().loadAll(Person.class); List list = getHibernateTemplate().loadAll(Person.class); List list = getHibernateTemplate().loadAll(Person.class); 如果只需要查询id,name,age三列记录呢?那么就要新增构造方法了, Java代码 1. public Person(Long id, String name, Long age) { 2. this.id = id; 3. this.name = name; 4. this.age = age; 5. } 6. 7. public Person(Long id, String name, Long age) { 8. this.id = id; 9. this.name = name; 10. this.age = age; 11. } public Person(Long id, String name, Long age) { this.id = id; this.name = name; this.age = age; } public Person(Long id, String name, Long age) { this.id = id; this.name = name; this.age = age; } 然后呢,就可以通过HQL来进行查询。 Java代码 1. List list = getHibernateTemplate().find("select new test.Person(id,name,age) from Person"); 2. 3. List list = getHibernateTemplate().find("select new test.Person(id,name,age) from Person"); List list = getHibernateTemplate().find("select new test.Person(id,name,age) from Person"); List list = getHibernateTemplate().find("select new test.Person(id,name,age) from Person"); 这个方法通常可以满足需要了,只是如果,只需要查询id,name,phone三列记录的话,还新增构造方法?不行了,会出现构造方法冲突了。有个办法: Java代码 1. List list = getSession().createQuery("select id,name,phone from person").addScalar("id",Hibernate.Long).addScalar("name").
addScalar("phone",Hibernate.Long).addEntity(Person.class); 2. 3. List list = getSession().createQuery("select id,name,phone from person").addScalar("id",Hibernate.Long).addScalar("name").
addScalar("phone",Hibernate.Long).addEntity(Person.class); List list = getSession().createQuery("select id,name,phone from person").addScalar("id",Hibernate.Long).addScalar("name").
addScalar("phone",Hibernate.Long).addEntity(Person.class); List list = getSession().createQuery("select id,name,phone from person").addScalar("id",Hibernate.Long).addScalar("name").
addScalar("phone",Hibernate.Long).addEntity(Person.class); 但是,这个只适用于存在Person实体的,如果Hibernate中没有进行Person映射的呢,系统中只存在一个JavaBean。 Java代码 1. List list = getSession().createSQLQuery("select id /"id/",name /"name/",phone /"phone/" from person") 2. .addScalar("id",Hibernate.Long).addScalar("name").addScalar("phone",Hibernate.Long) 3. .setResultTransformer(Transformers.aliasToBean(Person.class))); 4. 5. List list = getSession().createSQLQuery("select id /"id/",name /"name/",phone /"phone/" from person") 6. .addScalar("id",Hibernate.Long).addScalar("name").addScalar("phone",Hibernate.Long) 7. .setResultTransformer(Transformers.aliasToBean(Person.class))); List list = getSession().createSQLQuery("select id /"id/",name /"name/",phone /"phone/" from person") .addScalar("id",Hibernate.Long).addScalar("name").addScalar("phone",Hibernate.Long) .setResultTransformer(Transformers.aliasToBean(Person.class))); List list = getSession().createSQLQuery("select id /"id/",name /"name/",phone /"phone/" from person") .addScalar("id",Hibernate.Long).addScalar("name").addScalar("phone",Hibernate.Long) .setResultTransformer(Transformers.aliasToBean(Person.class))); 那么Hibernate就会自动将查出来的三列内容组装到VO对象中去,只是代码量有点大,而且名称都需要重新定义为小写的,在Oracle中查出来的列都默认为大写的
(不知道其它数据库怎么样) 这个办法就不依赖于构造方法了,只需要定义私有变量,设置getter/setter方法就行了。 不过如果更猛点的,根本就没有JavaBean对象可以填充怎么办,Hibernate可以将查出来的列组装到集合类中去。如Map。 Java代码 1. 2. List list = getSession().createSQLQuery("select * from person") 3. .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); 4. 5. List list = getSession().createSQLQuery("select * from person") 6. .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); List list = getSession().createSQLQuery("select * from person") .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); List list = getSession().createSQLQuery("select * from person") .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); 除了这个Transformers还可以将列转化为List。 Java代码 继承HibernaterDaoSupport 1. List list = getSession().createSQLQuery("select * from person") 2. .setResultTransformer(Transformers.T0_LIST); 3. 4. List list = getSession().createSQLQuery("select * from person") 5. .setResultTransformer(Transformers.T0_LIST); List list = getSession().createSQLQuery("select * from person") .setResultTransformer(Transformers.T0_LIST); List list = getSession().createSQLQuery("select * from person") .setResultTransformer(Transformers.T0_LIST); 到此,还可以通过继承Transformers将结果映射为其它对象,不累述了,基本功能够用了。
---------------------出现column not found---------------------
16.1.1. 标量查询(Scalar queries) 最基本的SQL查询就是获得一个标量(数值)的列表。 sess.createSQLQuery("SELECT * FROM CATS").list();sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();
它们都将返回一个Object数组(Object[])组成的List,数组每个元素都是CATS表的一个字段值。 Hibernate会使用ResultSetMetadata来判定返回的标量值的
实际顺序和类型。 如果要避免过多的使用ResultSetMetadata,或者只是为了更加明确的指名返回值,可以使用addScalar()。 sess.createSQLQuery("SELECT * FROM CATS") .addScalar("ID", Hibernate.LONG) .addScalar("NAME", Hibernate.STRING)
.addScalar("BIRTHDATE", Hibernate.DATE)这个查询指定了:
* SQL查询字符串
* 要返回的字段和类型
它仍然会返回Object数组,但是此时不再使用ResultSetMetdata,而是明确的将ID,NAME和BIRTHDATE按照 Long,String和Short类型从resultset中取出。
同时,也指明了就算query是使用*来查询的,可能获得超过列出的这三个字段,也 仅仅会返回这三个字段。 对全部或者部分的标量值不设置类型信息也是可以的。 sess.createSQLQuery("SELECT * FROM CATS") .addScalar("ID", Hibernate.LONG) .addScalar("NAME") .addScalar("BIRTHDATE")
基本上这和前面一个查询相同,只是此时使用ResultSetMetaData来决定NAME和 BIRTHDATE的类型,而ID的类型是明确指出的。 关于从ResultSetMetaData返回的java.sql.Types是如何映射到Hibernate类型,是由方言(Dialect)控制的。 假若某个指定的类型没有被映射,
或者不是你所预期的类型,你可以通过Dialet的registerHibernateType调用自行定义。 16.1.2. 实体查询(Entity queries) 上面的查询都是返回标量值的,也就是从resultset中返回的“裸”数据。下面展示如何通过addEntity()让原生查询返回实体对象。 sess.createSQLQuery("SELECT * FROM CATS").addEntity(Cat.class);sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").
addEntity(Cat.class);这个查询指定: * SQL查询字符串 * 要返回的实体 假设Cat被映射为拥有ID,NAME和BIRTHDATE三个字段的类,以上的两个查询都返回一个List,每个元素都是一个Cat实体。 假若实体在映射时有一个many-to-one的关联指向另外一个实体,在查询时必须也返回那个实体,否则会导致发生一个"column not found"的数据库错误。
这些附加的字段可以使用*标注来自动返回,但我们希望还是明确指明,看下面这个具有指向Dog的many-to-one的例 子: sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, DOG_ID FROM CATS").addEntity(Cat.class);这样cat.getDog()就能正常运作。