Web 开发
【分享】hibernate执行原生，指定返回实体类型
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区但用SQLQuery执行原生sql的返回结果集，我们不好控制字段，返回结果集是object[]数组类型，取数时不是那么方便，如果返回的是list<object>,这时候取数就方便多了，直接循环取数就好，不需要字段类型转换之类的操作，其实很简单
StringBuffer hql = new StringBuffer( " select dt.alarm_num as alarmNum," +
"dt.alarm_date as alarmDate ," +
"dt.database_sign as databaseSign," +
"dt.alarm_info as alarmInfo," +
"dt.alarm_des as alarmDes ," +
"dt.alarm_type as alarmType ");
hql.append(" from databasealarm dt ");
hql.append(" left join monitor_log lg ");
hql.append(" on dt.alarm_num = lg.log_id ");
hql.append(" left join config_alarm cfg ");
hql.append(" on lg.monitor_id=cfg.monitor_id ");
hql.append(" left join dim_monitor_type tp ");
hql.append(" on cfg.monitor_type_id=tp.monitor_type_id ");
hql.append(" where 1=1 ");
if(databasealarmVO!=null){
if(databasealarmVO.getStartAlarmDate()!=null){
System.out.println(sdf.format(databasealarmVO.getStartAlarmDate()));
// hql.append(" and dt.alarm_date >= DATE('").append(sdf.format(databasealarmVO.getStartAlarmDate())).append("') ");
hql.append(" and dt.alarm_date >=to_date('").append(sdf.format(databasealarmVO.getStartAlarmDate())).append("','yyyy-mm-dd hh24:mi:ss') ");
}
if(databasealarmVO.getEndAlarmDate()!=null){
// hql.append(" and dt.alarm_date <=DATE('").append(sdf.format(databasealarmVO.getEndAlarmDate())).append("') ");
hql.append(" and dt.alarm_date <=to_date('").append(sdf.format(databasealarmVO.getEndAlarmDate())).append("','yyyy-mm-dd hh24:mi:ss') ");
}
if(databasealarmVO.getMonitorTypeId()!=null){
hql.append(" and tp.monitor_type_id= ").append(databasealarmVO.getMonitorTypeId()).append(" ");
}
if(databasealarmVO.getMonitorId()!=null){
hql.append(" and cfg.monitor_id= ").append(databasealarmVO.getMonitorId()).append(" ");
}
}
hql.append("order by dt.alarm_date desc ");上面这是原生sql语句，我们只要在createSQLQuery的时候加上Transformers.aliasToBean(XXX.class)，就是将返回结果进行封装，XXX.class 就是你要封装的类，如下：
Query query = session.createSQLQuery( hql.toString())
.addScalar("alarmNum", Hibernate.STRING)
.addScalar("alarmDate",Hibernate.TIMESTAMP)
.addScalar("databaseSign", Hibernate.STRING)
.addScalar("alarmInfo", Hibernate.STRING)
.addScalar("alarmDes", Hibernate.STRING)
.addScalar("alarmType", Hibernate.STRING)
.setResultTransformer(Transformers.aliasToBean(DatabasealarmVO.class));
query.setFirstResult(pageList.getFirst());
query.setMaxResults(pageList.getObjectsPerPage());
List list = query.list();addScalar 是显式指定返回数据的类型，这里可以指定你返回字段的类型
还有就是，在select 后面不能直接用*，得在select 后面列出你需要的列和xxx.class中的属性对应
今天在项目中无意中应用到，在这分享给大家
相关推荐
hibernate3.6 文档（pdf 格式）
截至 2011-08-29 官方公布的最新 hibernate 文档，此为英文版，配套中文版下载地址：http://download.csdn.net/source/3557584。 详细信息： 版本：3.6.3.Final，作者：Gavin King, Christian Bauer, Max Rydahl Andersen,Emmanuel Bernard, Steve Ebersole, and Hardy Ferentschik。 大小 2.1MB，pdf 格式。 内容预览： Preface ............................................................................................................................. xi 1. Tutorial ........................................................................................................................ 1 1.1. Part 1 - The first Hibernate Application ................................................................ 1 1.1.1. Setup ...................................................................................................... 1 1.1.2. The first class .......................................................................................... 3 1.1.3. The mapping file ...................................................................................... 4 1.1.4. Hibernate configuration ............................................................................. 7 1.1.5. Building with Maven ................................................................................. 9 1.1.6. Startup and helpers .................................................................................. 9 1.1.7. Loading and storing objects .................................................................... 10 1.2. Part 2 - Mapping associations ........................................................................... 13 1.2.1. Mapping the Person class ...................................................................... 13 1.2.2. A unidirectional Set-based association ..................................................... 14 1.2.3. Working the association .......................................................................... 15 1.2.4. Collection of values ................................................................................ 17 1.2.5. Bi-directional associations ....................................................................... 19 1.2.6. Working bi-directional links ..................................................................... 19 1.3. Part 3 - The EventManager web application ....................................................... 20 1.3.1. Writing the basic servlet ......................................................................... 20 1.3.2. Processing and rendering ....................................................................... 22 1.3.3. Deploying and testing ............................................................................. 23 1.4. Summary .......................................................................................................... 24 2. Architecture ............................................................................................................... 25 2.1. Overview .......................................................................................................... 25 2.1.1. Minimal architecture ............................................................................... 25 2.1.2. Comprehensive architecture .................................................................... 26 2.1.3. Basic APIs ............................................................................................. 27 2.2. JMX Integration ................................................................................................ 28 2.3. Contextual sessions .......................................................................................... 28 3. Configuration ............................................................................................................. 31 3.1. Programmatic configuration ............................................................................... 31 3.2. Obtaining a SessionFactory ............................................................................... 32 3.3. JDBC connections ............................................................................................ 32 3.4. Optional configuration properties ........................................................................ 34 3.4.1. SQL Dialects .......................................................................................... 42 3.4.2. Outer Join Fetching ................................................................................ 43 3.4.3. Binary Streams ...................................................................................... 43 3.4.4. Second-level and query cache ................................................................ 43 3.4.5. Query Language Substitution .................................................................. 43 3.4.6. Hibernate statistics ................................................................................. 44 3.5. Logging ............................................................................................................ 44 3.6. Implementing a NamingStrategy ........................................................................ 45 3.7. Implementing a PersisterClassProvider .............................................................. 45 3.8. XML configuration file ........................................................................................ 46 HIBERNATE - Relational Persis... iv 3.9. Java EE Application Server integration ............................................................... 47 3.9.1. Transaction strategy configuration ........................................................... 47 3.9.2. JNDI-bound SessionFactory .................................................................... 49 3.9.3. Current Session context management with JTA ........................................ 49 3.9.4. JMX deployment .................................................................................... 50 4. Persistent Classes ..................................................................................................... 53 4.1. A simple POJO example ................................................................................... 53 4.1.1. Implement a no-argument constructor ...................................................... 54 4.1.2. Provide an identifier property .................................................................. 55 4.1.3. Prefer non-final classes (semi-optional) ................................................... 55 4.1.4. Declare accessors and mutators for persistent fields (optional) .................. 56 4.2. Implementing inheritance ................................................................................... 56 4.3. Implementing equals() and hashCode() .............................................................. 57 4.4. Dynamic models ............................................................................................... 58 4.5. Tuplizers .......................................................................................................... 60 4.6. EntityNameResolvers ........................................................................................ 61 5. Basic O/R Mapping .................................................................................................... 65 5.1. Mapping declaration .......................................................................................... 65 5.1.1. Entity ..................................................................................................... 68 5.1.2. Identifiers ............................................................................................... 73 5.1.3. Optimistic locking properties (optional) ..................................................... 91 5.1.4. Property ................................................................................................. 94 5.1.5. Embedded objects (aka components) .................................................... 103 5.1.6. Inheritance strategy .............................................................................. 106 5.1.7. Mapping one to one and one to many associations ................................. 117 5.1.8. Natural-id ............................................................................................. 126 5.1.9. Any ...................................................................................................... 127 5.1.10. Properties .......................................................................................... 129 5.1.11. Some hbm.xml specificities ................................................................. 130 5.2. Hibernate types ............................................................................................... 134 5.2.1. Entities and values ............................................................................... 134 5.2.2. Basic value types ................................................................................. 135 5.2.3. Custom value types .............................................................................. 137 5.3. Mapping a class more than once ..................................................................... 138 5.4. SQL quoted identifiers ..................................................................................... 139 5.5. Generated properties ....................................................................................... 139 5.6. Column transformers: read and write expressions ............................................. 140 5.7. Auxiliary database objects ............................................................................... 141 6. Types ....................................................................................................................... 143 6.1. Value types .................................................................................................... 143 6.1.1. Basic value types ................................................................................. 143 6.1.2. Composite types .................................................................................. 149 6.1.3. Collection types .................................................................................... 149 6.2. Entity types ..................................................................................................... 150 v 6.3. Significance of type categories ......................................................................... 150 6.4. Custom types .................................................................................................. 150 6.4.1. Custom types using org.hibernate.type.Type .......................................... 150 6.4.2. Custom types using org.hibernate.usertype.UserType ............................. 152 6.4.3. Custom types using org.hibernate.usertype.CompositeUserType ............. 153 6.5. Type registry ................................................................................................... 155 7. Collection mapping .................................................................................................. 157 7.1. Persistent collections ....................................................................................... 157 7.2. How to map collections ................................................................................... 158 7.2.1. Collection foreign keys .......................................................................... 162 7.2.2. Indexed collections ............................................................................... 162 7.2.3. Collections of basic types and embeddable objects ................................. 168 7.3. Advanced collection mappings ......................................................................... 170 7.3.1. Sorted collections ................................................................................. 170 7.3.2. Bidirectional associations ...................................................................... 171 7.3.3. Bidirectional associations with indexed collections .................................. 176 7.3.4. Ternary associations ............................................................................. 177 7.3.5. Using an <idbag> ................................................................................. 178 7.4. Collection examples ........................................................................................ 179 8. Association Mappings ............................................................................................. 185 8.1. Introduction ..................................................................................................... 185 8.2. Unidirectional associations ............................................................................... 185 8.2.1. Many-to-one ......................................................................................... 185 8.2.2. One-to-one ........................................................................................... 185 8.2.3. One-to-many ........................................................................................ 186 8.3. Unidirectional associations with join tables ........................................................ 187 8.3.1. One-to-many ........................................................................................ 187 8.3.2. Many-to-one ......................................................................................... 188 8.3.3. One-to-one ........................................................................................... 188 8.3.4. Many-to-many ...................................................................................... 189 8.4. Bidirectional associations ................................................................................. 190 8.4.1. one-to-many / many-to-one ................................................................... 190 8.4.2. One-to-one ........................................................................................... 191 8.5. Bidirectional associations with join tables .......................................................... 192 8.5.1. one-to-many / many-to-one ................................................................... 192 8.5.2. one to one ........................................................................................... 193 8.5.3. Many-to-many ...................................................................................... 193 8.6. More complex association mappings ................................................................ 194 9. Component Mapping ................................................................................................ 197 9.1. Dependent objects .......................................................................................... 197 9.2. Collections of dependent objects ...................................................................... 199 9.3. Components as Map indices ............................................................................ 200 9.4. Components as composite identifiers ............................................................... 200 9.5. Dynamic components ...................................................................................... 202 HIBERNATE - Relational Persis... vi 10. Inheritance mapping .............................................................................................. 205 10.1. The three strategies ...................................................................................... 205 10.1.1. Table per class hierarchy .................................................................... 205 10.1.2. Table per subclass ............................................................................. 206 10.1.3. Table per subclass: using a discriminator ............................................. 206 10.1.4. Mixing table per class hierarchy with table per subclass ........................ 207 10.1.5. Table per concrete class ..................................................................... 208 10.1.6. Table per concrete class using implicit polymorphism ............................ 209 10.1.7. Mixing implicit polymorphism with other inheritance mappings ................ 210 10.2. Limitations .................................................................................................... 210 11. Working with objects ............................................................................................. 213 11.1. Hibernate object states .................................................................................. 213 11.2. Making objects persistent .............................................................................. 213 11.3. Loading an object .......................................................................................... 214 11.4. Querying ....................................................................................................... 216 11.4.1. Executing queries ............................................................................... 216 11.4.2. Filtering collections ............................................................................. 220 11.4.3. Criteria queries ................................................................................... 221 11.4.4. Queries in native SQL ........................................................................ 221 11.5. Modifying persistent objects ........................................................................... 222 11.6. Modifying detached objects ............................................................................ 222 11.7. Automatic state detection ............................................................................... 223 11.8. Deleting persistent objects ............................................................................. 224 11.9. Replicating object between two different datastores ......................................... 225 11.10. Flushing the Session ................................................................................... 225 11.11. Transitive persistence .................................................................................. 226 11.12. Using metadata ........................................................................................... 229 12. Read-only entities .................................................................................................. 231 12.1. Making persistent entities read-only ................................................................ 231 12.1.1. Entities of immutable classes .............................................................. 232 12.1.2. Loading persistent entities as read-only ............................................... 232 12.1.3. Loading read-only entities from an HQL query/criteria ........................... 233 12.1.4. Making a persistent entity read-only ..................................................... 234 12.2. Read-only affect on property type ................................................................... 235 12.2.1. Simple properties ................................................................................ 236 12.2.2. Unidirectional associations .................................................................. 237 12.2.3. Bidirectional associations .................................................................... 238 13. Transactions and Concurrency .............................................................................. 241 13.1. Session and transaction scopes ..................................................................... 241 13.1.1. Unit of work ....................................................................................... 241 13.1.2. Long conversations ............................................................................. 242 13.1.3. Considering object identity .................................................................. 243 13.1.4. Common issues .................................................................................. 244 13.2. Database transaction demarcation .................................................................. 245 vii 13.2.1. Non-managed environment ................................................................. 246 13.2.2. Using JTA .......................................................................................... 247 13.2.3. Exception handling ............................................................................. 248 13.2.4. Transaction timeout ............................................................................ 249 13.3. Optimistic concurrency control ........................................................................ 250 13.3.1. Application version checking ............................................................... 250 13.3.2. Extended session and automatic versioning ......................................... 251 13.3.3. Detached objects and automatic versioning .......................................... 252 13.3.4. Customizing automatic versioning ........................................................ 252 13.4. Pessimistic locking ........................................................................................ 253 13.5. Connection release modes ............................................................................ 254 14. Interceptors and events ......................................................................................... 257 14.1. Interceptors ................................................................................................... 257 14.2. Event system ................................................................................................ 259 14.3. Hibernate declarative security ........................................................................ 260 15. Batch processing ................................................................................................... 263 15.1. Batch inserts ................................................................................................. 263 15.2. Batch updates ............................................................................................... 264 15.3. The StatelessSession interface ...................................................................... 264 15.4. DML-style operations ..................................................................................... 265 16. HQL: The Hibernate Query Language .................................................................... 269 16.1. Case Sensitivity ............................................................................................ 269 16.2. The from clause ............................................................................................ 269 16.3. Associations and joins ................................................................................... 270 16.4. Forms of join syntax ...................................................................................... 271 16.5. Referring to identifier property ........................................................................ 272 16.6. The select clause .......................................................................................... 272 16.7. Aggregate functions ....................................................................................... 274 16.8. Polymorphic queries ...................................................................................... 274 16.9. The where clause .......................................................................................... 275 16.10. Expressions ................................................................................................ 277 16.11. The order by clause .................................................................................... 281 16.12. The group by clause .................................................................................... 281 16.13. Subqueries .................................................................................................. 282 16.14. HQL examples ............................................................................................ 283 16.15. Bulk update and delete ................................................................................ 285 16.16. Tips & Tricks ............................................................................................... 285 16.17. Components ................................................................................................ 286 16.18. Row value constructor syntax ....................................................................... 287 17. Criteria Queries ...................................................................................................... 289 17.1. Creating a Criteria instance ........................................................................... 289 17.2. Narrowing the result set ................................................................................. 289 17.3. Ordering the results ....................................................................................... 290 17.4. Associations .................................................................................................. 291 HIBERNATE - Relational Persis... viii 17.5. Dynamic association fetching ......................................................................... 292 17.6. Example queries ........................................................................................... 292 17.7. Projections, aggregation and grouping ............................................................ 293 17.8. Detached queries and subqueries .................................................................. 295 17.9. Queries by natural identifier ........................................................................... 295 18. Native SQL ............................................................................................................. 297 18.1. Using a SQLQuery ........................................................................................ 297 18.1.1. Scalar queries .................................................................................... 297 18.1.2. Entity queries ..................................................................................... 298 18.1.3. Handling associations and collections .................................................. 298 18.1.4. Returning multiple entities ................................................................... 299 18.1.5. Returning non-managed entities .......................................................... 301 18.1.6. Handling inheritance ........................................................................... 301 18.1.7. Parameters ........................................................................................ 301 18.2. Named SQL queries ...................................................................................... 302 18.2.1. Using return-property to explicitly specify column/alias names ................ 308 18.2.2. Using stored procedures for querying ................................................... 309 18.3. Custom SQL for create, update and delete ..................................................... 310 18.4. Custom SQL for loading ................................................................................ 313 19. Filtering data .......................................................................................................... 315 19.1. Hibernate filters ............................................................................................. 315 20. XML Mapping ......................................................................................................... 319 20.1. Working with XML data ................................................................................. 319 20.1.1. Specifying XML and class mapping together ........................................ 319 20.1.2. Specifying only an XML mapping ......................................................... 320 20.2. XML mapping metadata ................................................................................. 320 20.3. Manipulating XML data .................................................................................. 322 21. Improving performance .......................................................................................... 325 21.1. Fetching strategies ........................................................................................ 325 21.1.1. Working with lazy associations ............................................................ 326 21.1.2. Tuning fetch strategies ........................................................................ 326 21.1.3. Single-ended association proxies ......................................................... 327 21.1.4. Initializing collections and proxies ........................................................ 329 21.1.5. Using batch fetching ........................................................................... 331 21.1.6. Using subselect fetching ..................................................................... 331 21.1.7. Fetch profiles ..................................................................................... 332 21.1.8. Using lazy property fetching ................................................................ 334 21.2. The Second Level Cache .............................................................................. 334 21.2.1. Cache mappings ................................................................................ 335 21.2.2. Strategy: read only ............................................................................. 338 21.2.3. Strategy: read/write ............................................................................. 338 21.2.4. Strategy: nonstrict read/write ............................................................... 338 21.2.5. Strategy: transactional ........................................................................ 338 21.2.6. Cache-provider/concurrency-strategy compatibility ................................ 338 ix 21.3. Managing the caches .................................................................................... 339 21.4. The Query Cache .......................................................................................... 340 21.4.1. Enabling query caching ....................................................................... 341 21.4.2. Query cache regions .......................................................................... 342 21.5. Understanding Collection performance ............................................................ 342 21.5.1. Taxonomy .......................................................................................... 342 21.5.2. Lists, maps, idbags and sets are the most efficient collections to update ... 343 21.5.3. Bags and lists are the most efficient inverse collections ......................... 343 21.5.4. One shot delete .................................................................................. 344 21.6. Monitoring performance ................................................................................. 344 21.6.1. Monitoring a SessionFactory ............................................................... 344 21.6.2. Metrics ............................................................................................... 345 22. Toolset Guide ......................................................................................................... 347 22.1. Automatic schema generation ........................................................................ 347 22.1.1. Customizing the schema ..................................................................... 347 22.1.2. Running the tool ................................................................................. 350 22.1.3. Properties .......................................................................................... 351 22.1.4. Using Ant ........................................................................................... 351 22.1.5. Incremental schema updates ............................................................... 352 22.1.6. Using Ant for incremental schema updates ........................................... 352 22.1.7. Schema validation .............................................................................. 353 22.1.8. Using Ant for schema validation .......................................................... 353 23. Additional modules ................................................................................................ 355 23.1. Bean Validation ............................................................................................. 355 23.1.1. Adding Bean Validation ....................................................................... 355 23.1.2. Configuration ...................................................................................... 355 23.1.3. Catching violations .............................................................................. 357 23.1.4. Database schema ............................................................................... 357 23.2. Hibernate Search .......................................................................................... 358 23.2.1. Description ......................................................................................... 358 23.2.2. Integration with Hibernate Annotations ................................................. 358 24. Example: Parent/Child ............................................................................................ 359 24.1. A note about collections ................................................................................ 359 24.2. Bidirectional one-to-many ............................................................................... 359 24.3. Cascading life cycle ...................................................................................... 361 24.4. Cascades and unsaved-value ........................................................................ 362 24.5. Conclusion .................................................................................................... 363 25. Example: Weblog Application ................................................................................ 365 25.1. Persistent Classes ......................................................................................... 365 25.2. Hibernate Mappings ...................................................................................... 366 25.3. Hibernate Code ............................................................................................. 368 26. Example: Various Mappings .................................................................................. 373 26.1. Employer/Employee ....................................................................................... 373 26.2. Author/Work .................................................................................................. 375 HIBERNATE - Relational Persis... x 26.3. Customer/Order/Product ................................................................................ 377 26.4. Miscellaneous example mappings .................................................................. 379 26.4.1. "Typed" one-to-one association ........................................................... 379 26.4.2. Composite key example ...................................................................... 379 26.4.3. Many-to-many with shared composite key attribute ............................... 381 26.4.4. Content based discrimination ............................................................... 382 26.4.5. Associations on alternate keys ............................................................ 383 27. Best Practices ........................................................................................................ 385 28. Database Portability Considerations ...................................................................... 389 28.1. Portability Basics ........................................................................................... 389 28.2. Dialect .......................................................................................................... 389 28.3. Dialect resolution ........................................................................................... 389 28.4. Identifier generation ....................................................................................... 390 28.5. Database functions ........................................................................................ 391 28.6. Type mappings ............................................................................................. 391 References .................................................................................................................... 3
HQL入门与实战：面向对象的Hibernate查询语言
HQL（Hibernate Query Language）是一种面向Java实体类的ORM查询语言，其核心原理是将开发者对领域对象的操作自动翻译为适配不同数据库的原生SQL。相比传统SQL，HQL提供编译期类型安全、数据库无关性及延迟加载集成等技术价值，显著降低DAO层维护成本。它适用于以CRUD为主、强调可维护性的企业级Java应用，尤其在Spring Boot + JPA技术栈中承担着统一数据访问语义的关键角色。本文围绕HQL语法规范、命名参数绑定、JOIN FETCH优化及常见性能陷阱展开，覆盖从基础
Hibernate一对多映射
Hibernate级联映射前言关联1、概念2、关联的分类注意要点级联映射总结 前言 今天为大家分享的是Hibernate的映射，主要讲的是一对多的映射， hibernate的其他知识点之前就有提及过，以下是博客链接： 1、hibernate的初步介绍与学习 2、hibernate的入门 关联 1、概念 关联指的是类之间的引用关系。 如果类A与类B关联，那么被引用的类B将被定义为类A的属性。 public class A{ private B b = new B;
Java根据实体创建Mysql数据库表
根据实体创建数据库表，资源共享出来，希望有人可以用到，抽出时间多陪陪您的家人，恋人，程序员真的很辛苦...
Hibernate一对多关联双向关联代码实现分享
Hibernate一对多关联双向关联代码实现分享,大家参考使用吧
