转：Hibernate Query examples (HQL) 示例
Hibernate created a new language named Hibernate Query Language (HQL), the syntax is quite similar to database SQL language. The main difference between is HQL uses class name instead of table name, and property names instead of column name.
HQL is extremely simple to learn and use, and the code is always self-explanatory.
1. HQL Select Query Example
Retrieve a stock data where stock code is “7277″.
Query query = session.createQuery("from Stock where stockCode = :code "); query.setParameter("code", "7277"); List list = query.list();
Query query = session.createQuery("from Stock where stockCode = '7277' "); List list = query.list();
2. HQL Update Query Example
Update a stock name to “DIALOG1″ where stock code is “7277″.
Query query = session.createQuery("update Stock set stockName = :stockName" + " where stockCode = :stockCode"); query.setParameter("stockName", "DIALOG1"); query.setParameter("stockCode", "7277"); int result = query.executeUpdate();
Query query = session.createQuery("update Stock set stockName = 'DIALOG2'" + " where stockCode = '7277'"); int result = query.executeUpdate();
3. HQL Delete Query Example
Delete a stock where stock code is “7277″.
Query query = session.createQuery("delete Stock where stockCode = :stockCode"); query.setParameter("stockCode", "7277"); int result = query.executeUpdate();
Query query = session.createQuery("delete Stock where stockCode = '7277'"); int result = query.executeUpdate();
4. HQL Insert Query Example
In HQL, only the INSERT INTO … SELECT … is supported; there is no INSERT INTO … VALUES. HQL only support insert from another table. For example
"insert into Object (id, name) select oo.id, oo.name from OtherObject oo";
Insert a stock record from another backup_stock table. This can also called bulk-insert statement.
Query query = session.createQuery("insert into Stock(stock_code, stock_name)" + "select stock_code, stock_name from backup_stock"); int result = query.executeUpdate();
The query.executeUpdate() will return how many number of record has been inserted, updated or deleted.
Reference
http://docs.jboss.org/hibernate/core/3.3/reference/en/html/objectstate.html
- 10.1. Hibernate object states
- 10.2. Making objects persistent
- 10.3. Loading an object
- 10.4. Querying
- 10.5. Modifying persistent objects
- 10.6. Modifying detached objects
- 10.7. Automatic state detection
- 10.8. Deleting persistent objects
- 10.9. Replicating object between two different datastores
- 10.10. Flushing the Session
- 10.11. Transitive persistence
- 10.12. Using metadata
Hibernate is a full object/relational mapping solution that not only shields the developer from the details of the underlying database management system, but also offers state management of objects. This is, contrary to the management of SQL statements in common JDBC/SQL persistence layers, a natural object-oriented view of persistence in Java applications.
In other words, Hibernate application developers should always think about the state of their objects, and not necessarily about the execution of SQL statements. This part is taken care of by Hibernate and is only relevant for the application developer when tuning the performance of the system.
DomesticCat fritz = new DomesticCat(); fritz.setColor(Color.GINGER); fritz.setSex('M'); fritz.setName("Fritz"); Long generatedId = (Long) sess.save(fritz);
Alternatively, you can assign the identifier using an overloaded version of save().
DomesticCat pk = new DomesticCat(); pk.setColor(Color.TABBY); pk.setSex('F'); pk.setName("PK"); pk.setKittens( new HashSet() ); pk.addKitten(fritz); sess.save( pk, new Long(1234) );
Cat fritz = (Cat) sess.load(Cat.class, generatedId);
// you need to wrap primitive identifiers long id = 1234; DomesticCat pk = (DomesticCat) sess.load( DomesticCat.class, new Long(id) );
Alternatively, you can load state into a given instance:
Cat cat = new DomesticCat(); // load pk's state into cat sess.load( cat, new Long(pkId) ); Set kittens = cat.getKittens();
Cat cat = (Cat) sess.get(Cat.class, id); if (cat==null) { cat = new Cat(); sess.save(cat, id); } return cat;
Cat cat = (Cat) sess.get(Cat.class, id, LockMode.UPGRADE);
sess.save(cat); sess.flush(); //force the SQL INSERT sess.refresh(cat); //re-read the state (after the trigger executes)
How much does Hibernate load from the database and how many SQL SELECTs will it use? This depends on the fetching strategy. This is explained in Section 19.1, “Fetching strategies”.
List cats = session.createQuery( "from Cat as cat where cat.birthdate < ?") .setDate(0, date) .list(); List mothers = session.createQuery( "select mother from Cat as cat join cat.mother as mother where cat.name = ?") .setString(0, name) .list(); List kittens = session.createQuery( "from Cat as cat where cat.mother = ?") .setEntity(0, pk) .list(); Cat mother = (Cat) session.createQuery( "select cat.mother from Cat as cat where cat = ?") .setEntity(0, izi) .uniqueResult();]] Query mothersWithKittens = (Cat) session.createQuery( "select mother from Cat as mother left join fetch mother.kittens"); Set uniqueMothers = new HashSet(mothersWithKittens.list());
Hibernate queries sometimes return tuples of objects. Each tuple is returned as an array:
Iterator kittensAndMothers = sess.createQuery( "select kitten, mother from Cat kitten join kitten.mother mother") .list() .iterator(); while ( kittensAndMothers.hasNext() ) { Object[] tuple = (Object[]) kittensAndMothers.next(); Cat kitten = (Cat) tuple[0]; Cat mother = (Cat) tuple[1]; .... }
//named parameter (preferred) Query q = sess.createQuery("from DomesticCat cat where cat.name = :name"); q.setString("name", "Fritz"); Iterator cats = q.iterate();
//positional parameter Query q = sess.createQuery("from DomesticCat cat where cat.name = ?"); q.setString(0, "Izi"); Iterator cats = q.iterate();
//named parameter list List names = new ArrayList(); names.add("Izi"); names.add("Fritz"); Query q = sess.createQuery("from DomesticCat cat where cat.name in (:namesList)"); q.setParameterList("namesList", names); List cats = q.list();
Query q = sess.createQuery("from DomesticCat cat"); q.setFirstResult(20); q.setMaxResults(10); List cats = q.list();
Hibernate knows how to translate this limit query into the native SQL of your DBMS.
<query name="ByNameAndMaximumWeight"><![CDATA[ from eg.DomesticCat as cat where cat.name = ? and cat.weight > ? ] ]></query>
Parameter binding and executing is done programatically:
Query q = sess.getNamedQuery("ByNameAndMaximumWeight"); q.setString(0, name); q.setInt(1, minWeight); List cats = q.list();
Collection blackKittens = session.createFilter( pk.getKittens(), "where this.color = ?") .setParameter( Color.BLACK, Hibernate.custom(ColorUserType.class) ) .list() );
Collection blackKittenMates = session.createFilter( pk.getKittens(), "select this.mate where this.color = eg.Color.BLACK.intValue") .list();
Even an empty filter query is useful, e.g. to load a subset of elements in a large collection:
Collection tenKittens = session.createFilter( mother.getKittens(), "") .setFirstResult(0).setMaxResults(10) .list();
Criteria crit = session.createCriteria(Cat.class); crit.add( Restrictions.eq( "color", eg.Color.BLACK ) ); crit.setMaxResults(10); List cats = crit.list();
The Criteria and the associated Example API are discussed in more detail in Chapter 15, Criteria Queries.
List cats = session.createSQLQuery("SELECT {cat.*} FROM CAT {cat} WHERE ROWNUM<10") .addEntity("cat", Cat.class) .list();
List cats = session.createSQLQuery( "SELECT {cat}.ID AS {cat.id}, {cat}.SEX AS {cat.sex}, " + "{cat}.MATE AS {cat.mate}, {cat}.SUBCLASS AS {cat.class}, ... " + "FROM CAT {cat} WHERE ROWNUM<10") .addEntity("cat", Cat.class) .list()
SQL queries can contain named and positional parameters, just like Hibernate queries. More information about native SQL queries in Hibernate can be found in Chapter 16, Native SQL.
DomesticCat cat = (DomesticCat) sess.load( Cat.class, new Long(69) ); cat.setName("PK"); sess.flush(); // changes to cat are automatically detected and persisted
Important
Hibernate does not offer its own API for direct execution of UPDATE or DELETE statements. Hibernate is a state management service, you do not have to think in statements to use it. JDBC is a perfect API for executing SQL statements, you can get a JDBC Connection at any time by calling session.connection(). Furthermore, the notion of mass operations conflicts with object/relational mapping for online transaction processing-oriented applications. Future versions of Hibernate can, however, provide special mass operation functions. SeeChapter 13, Batch processing for some possible batch operation tricks.
// in the first session Cat cat = (Cat) firstSession.load(Cat.class, catId); Cat potentialMate = new Cat(); firstSession.save(potentialMate); // in a higher layer of the application cat.setMate(potentialMate); // later, in a new session secondSession.update(cat); // update cat secondSession.update(mate); // update mate
The application should individually update() detached instances that are reachable from the given detached instance only if it wants their state to be updated. This can be automated using transitive persistence. See Section 10.11, “Transitive persistence” for more information.
The lock() method also allows an application to reassociate an object with a new session. However, the detached instance has to be unmodified.
//just reassociate: sess.lock(fritz, LockMode.NONE); //do a version check, then reassociate: sess.lock(izi, LockMode.READ); //do a version check, using SELECT ... FOR UPDATE, then reassociate: sess.lock(pk, LockMode.UPGRADE);
Note that lock() can be used with various LockModes. See the API documentation and the chapter on transaction handling for more information. Reattachment is not the only usecase for lock().
Other models for long units of work are discussed in Section 11.3, “Optimistic concurrency control”.
// in the first session Cat cat = (Cat) firstSession.load(Cat.class, catID); // in a higher tier of the application Cat mate = new Cat(); cat.setMate(mate); // later, in a new session secondSession.saveOrUpdate(cat); // update existing state (cat has a non-null id) secondSession.saveOrUpdate(mate); // save the new instance (mate has a null id)
Usually update() or saveOrUpdate() are used in the following scenario:
saveOrUpdate() does the following:
//retrieve a cat from one database Session session1 = factory1.openSession(); Transaction tx1 = session1.beginTransaction(); Cat cat = session1.get(Cat.class, catId); tx1.commit(); session1.close(); //reconcile with a second database Session session2 = factory2.openSession(); Transaction tx2 = session2.beginTransaction(); session2.replicate(cat, ReplicationMode.LATEST_VERSION); tx2.commit(); session2.close();
The SQL statements are issued in the following order:
An exception is that objects using native ID generation are inserted when they are saved.
It is possible to change the default behavior so that flush occurs less frequently. The FlushMode class defines three different modes: only flush at commit time when the Hibernate Transaction API is used, flush automatically using the explained routine, or never flush unless flush() is called explicitly. The last mode is useful for long running units of work, where a Session is kept open and disconnected for a long time (see Section 11.3.2, “Extended session and automatic versioning”).
sess = sf.openSession();
Transaction tx = sess.beginTransaction();
sess.setFlushMode(FlushMode.COMMIT); // allow queries to return stale state
Cat izi = (Cat) sess.load(Cat.class, id);
izi.setName(iznizi);
// might return stale data
sess.find("from Cat as cat left outer join cat.kittens kitten");
// change to izi is not flushed!
...
tx.commit(); // flush occurs
sess.close();
During flush, an exception might occur (e.g. if a DML operation violates a constraint). Since handling exceptions involves some understanding of Hibernate's transactional behavior, we discuss it inChapter 11, Transactions and Concurrency.
<one-to-one name="person" cascade="persist"/>
Cascade styles my be combined:
<one-to-one name="person" cascade="persist,delete,lock"/>
