1.(Hibernate with a connection pool in a non-managed environment)
Using hibernate.properties for C3P0 connection pool settings:
hibernate.connection.driver_class = org.postgresql.Driver
hibernate.connection.url = jdbc:postgresql://localhost/auctiondb
hibernate.connection.username = auctionuser
hibernate.connection.password = secret
hibernate.dialect = net.sf.hibernate.dialect.PostgreSQLDialect
hibernate.c3p0.min_size=5
hibernate.c3p0.max_size=20
hibernate.c3p0.timeout=300
hibernate.c3p0.max_statements=50
hibernate.c3p0.idle_test_period=3000
2.(Hibernate in a managed environment with an application server)
hibernate.connection.datasource = java:/comp/env/jdbc/AuctionDB
hibernate.transaction.factory_class = \
net.sf.hibernate.transaction.JTATransactionFactory
hibernate.transaction.manager_lookup_class = \
net.sf.hibernate.transaction.JBossTransactionManagerLookup
hibernate.dialect = net.sf.hibernate.dialect.PostgreSQLDialect
结合spring 后就不是这样了，可以右spring 控制transaction & datasource &...
HQL
The SQL functions any, some, all, exists, in are supported when passed the element or index set of a collection
(elements and indices functions) or the result of a subquery (see below).
select mother from Cat as mother, Cat as kit
where kit in elements(foo.kittens)
select p from NameList list, Person p
where p.name = some elements(list.names)
from Cat cat where exists elements(cat.kittens)
from Player p where 3 > all elements(p.scores)
from Show show where 'fizard' in indices(show.acts)
/*
List cookies = getHibernateTemplate().find(
"from UserCookie c where c.username=? and c.cookieId=?",
new Object[]{cookie.getUsername(), cookie.getCookieId()});
*/
*************************************
使用元数据(meta-data)
Cat fritz = ......;
ClassMetadata catMeta = sessionfactory.getClassMetadata(Cat.class);
Object[] propertyValues = catMeta.getPropertyValues(fritz);
String[] propertyNames = catMeta.getPropertyNames();
Type[] propertyTypes = catMeta.getPropertyTypes();
// get a Map of all properties which are not collections or associations
Map namedValues = new HashMap();
for ( int i=0; i<propertyNames.length; i++ ) {
if ( !propertyTypes[i].isEntityType() && !propertyTypes[i].isCollectionType() ) {
namedValues.put( propertyNames[i], propertyValues[i] );
}
}
*************************************
Chapter 14. Batch processing
A naive approach to inserting 100 000 rows in the database using Hibernate might look like this:
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
for ( int i=0; i<100000; i++ ) {
Customer customer = new Customer(.....);
session.save(customer);
}
tx.commit();
session.close();
This would fall over with an OutOfMemoryException somewhere around the 50 000th row. That's because Hibernate
caches all the newly inserted Customer instances in the session-level cache.
In this chapter we'll show you how to avoid this problem. First, however, if you are doing batch processing, it is
absolutely critical that you enable the use of JDBC batching, if you intend to achieve reasonable performance.
Set the JDBC batch size to a reasonable number (say, 10-50):
hibernate.jdbc.batch_size 20
You also might like to do this kind of work in a process where interaction with the second-level cache is completely
disabled:
hibernate.cache.use_second_level_cache false
14.1. Batch inserts
When making new objects persistent, you must flush() and then clear() the session regularly, to control the
size of the first-level cache.
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
for ( int i=0; i<100000; i++ ) {
Customer customer = new Customer(.....);
session.save(customer);
if ( i % 20 == 0 ) { //20, same as the JDBC batch size
//flush a batch of inserts and release memory:
session.flush();
session.clear();
}
}
tx.commit();
session.close();
14.2. Batch updates
For retrieving and updating data the same ideas apply. In addition, you need to use scroll() to take advantage
of server-side cursors for queries that return many rows of data.
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
ScrollableResults customers = session.getNamedQuery("GetCustomers")
.setCacheMode(CacheMode.IGNORE)
.scroll(ScrollMode.FORWARD_ONLY);
int count=0;
while ( customers.next() ) {
Customer customer = (Customer) customers.get(0);
customer.updateStuff(...);
if ( ++count % 20 == 0 ) {
//flush a batch of updates and release memory:
session.flush();
session.clear();
}
}
tx.commit();
session.close();
=========================================
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
String hqlUpdate = "update Customer set name = :newName where name = :oldName";
int updatedEntities = s.createQuery( hqlUpdate )
.setString( "newName", newName )
.setString( "oldName", oldName )
.executeUpdate();
tx.commit();
session.close();
To execute an HQL DELETE, use the same Query.executeUpdate() method (the method is named for those familiar
with JDBC's PreparedStatement.executeUpdate()):
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
String hqlDelete = "delete Customer where name = :oldName";
int deletedEntities = s.createQuery( hqlDelete )
.setString( "oldName", oldName )
.executeUpdate();
tx.commit();
session.close();
/***************以下是关于查询的HQL 来字 Hibernate in Action 一书****************/
*
List results =
session.createQuery("from User u order by u.name asc")
.setFirstResult(0)
.setMaxResults(10)
.list();
*
Bid maxBid =
(Bid) session.createQuery("from Bid b order by b.amount desc")
.setMaxResults(1)
.uniqueResult();
*
String queryString = "from Item item "
+ "where item.description like :searchString "
+ "and item.date > :minDate";
List result = session.createQuery(queryString)
.setString("searchString", searchString)
.setDate("minDate", minDate)
.list();
使用name sql 比使用 ？ 在某种情况下教好，原因在于可以不用setString(0,string);用0来决定位置。 所以位置可以任意。而且考虑有种情况，SQL语句里有多个相同的:somestring 就可以用一个setString("somestring",string);
*
A particularly useful method is setEntity(), which lets you bind a persistent
entity:
session.createQuery("from Item item where item.seller = :seller")
.setEntity("seller", seller)
.list();
*
可以不考虑参数类型
String queryString = "from Item item "
+ "where item.seller=:seller and "
+ "item.description like :desc";
session.createQuery(queryString)
.setParameter( "seller", seller, Hibernate.entity(User.class) )
.setParameter( "desc", description, Hibernate.STRING )
.list();
*
可以使用自定义类型
This even works for custom user-defined types like MonetaryAmount:
Query q =
session.createQuery("from Bid bid where bid.amount > :amount");
q.setParameter( "amount",
givenAmount,
Hibernate.custom(MonetaryAmountUserType.class) );
List result = q.list();
*
支持运算符号
from Bid bid where ( bid.amount / 0.71 ) - 100.0 > 0.0
支持函数 from User u where lower(u.email) = mailto:%27foo@hibernate.org%27
字符串连接符号 ||
from User user
where ( user.firstname || ' ' || user.lastname ) like 'G% K%'
from User u order by u.lastname asc, u.firstname asc
*
from Item item
left join fetch item.bids
where item.description like '%gc%'
This query returns all items with a description that contains the string gc, and all
their bids, in a single select.
*
If you fetch a collection, Hibernate doesn’t return a distinct result list.
so you can
distinctResults = new HashSet(resultList);.
*
from Item item
join item.bids bid
where item.description like '%gc%'
and bid.amount > 100
This query assigns the alias item to the class Item and the alias bid to the joined
Item’s bids. We then use both aliases to express our restriction criteria in the
where clause.
The resulting SQL is as follows:
select I.DESCRIPTION, I.CREATED, I.SUCCESSFUL_BID,
B.BID_ID, B.AMOUNT, B.ITEM_ID, B.CREATED
from ITEM I
inner join BID B on I.ITEM_ID = B.ITEM_ID
where I.DESCRIPTION like '%gc%'
and B.AMOUNT > 100
*
Query q = session.createQuery("from Item item join item.bids bid");
Iterator pairs = q.list().iterator();
while ( pairs.hasNext() ) {
Object[] pair = (Object[]) pairs.next();
Item item = (Item) pair[0];
Bid bid = (Bid) pair[1];
}
Instead of a List of Items, this query returns a List of Object[] arrays. At index 0
is the Item, and at index 1 is the Bid. A particular Item may appear multiple times,
once for each associated Bid.
This is all different from the case of a query with an eager fetch join. //不返回bids集合
如果不想返回 Bid表的列，可以这样写
select item
from Item item
join item.bids bid
where item.description like '%gc%'
and bid.amount > 100
*
*
from Item i, User u
where i.seller = u and u.username = 'steve'
与以下等效：
from Item i join i.seller u
where u.username = 'steve'
推荐这样使用
from Item i, User u
where i.seller.id = u.id and u.username = 'steve'
*
再一次示例
Iterator i = session.createQuery(
"select item.id, item.description, bid.amount " +
"from Item item join item.bids bid " +
"where bid.amount > 100"
)
.list()
.iterator();
while ( i.hasNext() ) {
Object[] row = (Object[]) i.next();
Long id = (Long) row[0];
String description = (String) row[1];
BigDecimal amount = (BigDecimal) row[2];
// ... show values in a report screen
}
*
动态实例话
Iterator i = session.createQuery(
"select new ItemRow( item.id, item.description, bid.amount ) " +
"from Item item join item.bids bid " +
"where bid.amount > 100"
)
.list()
.iterator();
while ( i.hasNext() ) {
ItemRow row = (ItemRow) i.next();
// Do something
}
*
select count(distinct item.description) from Item item
select min(bid.amount), max(bid.amount) from Bid bid where bid.item.id = 1
*
select user.lastname, count(user)
from User user
group by user.lastname
having user.lastname like 'A%'
*
List results =
session.createQuery("from Bid b where b.item = :item " +
"order by b.amount asc")
.setEntity("item", item)
.list();
This query works perfectly, since the association between bids and items is bidirectional
and each Bid knows its Item. Imagine that this association was unidirectional:
Item has a collection of Bids, but there is no inverse association from Bid to Item.
We could try the following query:
String query = "select bid from Item item join item.bids bid "
+ "where item = :item order by bid.amount asc";
List results = session.createQuery(query)
.setEntity("item", item)
.list();
虽然可以，但是效率不高（the query is inefficient)
*
List results =
session.createFilter( user.getBids(),
"where this.created > :oneWeekAgo" )
.setTimestamp("oneWeekAgo", oneWeekAgo)
.list();
Again, this query does not initialize the bids collection of the User.
//再次提醒，此查询没有初始化全部用户的bids集合!!
*
For example, the following query returns items where all bids are less than 100:
from Item item where 100 > all ( select b.amount from item.bids b )
The next query returns all items with bids greater than 100:
from Item item where 100 < any ( select b.amount from item.bids b )
This query returns items with a bid of exactly 100:
from Item item where 100 = some ( select b.amount from item.bids b )
So does this one:
from Item item where 100 in ( select b.amount from item.bids b )
*
List list = session.createQuery("from Category c " +
"where :item in elements(c.items)")
.setEntity("item", item)
.list();
The query returns all categories to which the item belongs and is equivalent to the
following HQL, where the subquery is more explicit:
List results = session.createQuery("from Category c " +
"where :item in (from c.items)")
.setEntity("item", item)
.list();
Along with elements(), HQL provides indices(), maxelement(), minelement(),
maxindex(), minindex(), and size(), each of which is equivalent to a certain correlated
subquery against the passed collection.
*
--------------------查询优化------------------------------
**
Solving the n+1 selects problem
在 lasy="true"的情况下
Iterator items = session.createCriteria(Item.class)
.add( Expression.eq("item.seller", user) )
.list()
.iterator();
This query returns a list of items, where each collection of bids is an uninitialized
collection wrapper. Suppose that we now wish to find the maximum bid for each
item. The following code would be one way to do this:
List maxAmounts = new ArrayList();
while (items.hasNext()) {
Item item = (Item) items.next();
BigDecimal maxAmount = new BigDecimal("0");
for ( Iterator b = item.getBids().iterator(); b.hasNext(); ) {
Bid bid = (Bid) b.next();
if ( bid.getAmount().compareTo(maxAmount) == 1 )
maxAmount = bid.getAmount();
}
maxAmounts.add( new MaxAmount( item.getId(), maxAmount ) );
}
解决办法
1。
2。String query = "select MaxAmount( item.id, max(bid.amount) )"
+ " from Item item join item.bids bid"
+ " where item.seller = :user group by item.id";
List maxAmounts = session.createQuery(query)
.setEntity("user", user)
.list();
3。
List results = session.createCriteria(Item.class)
.add( Expression.eq("item.seller", user) )
.setFetchMode("bids", FetchMode.EAGER)
.list();//运行时动态决定的fetch
**
Using iterate() queries
query.interate();
iterate()首先查询满足条件的ID，然后先在缓存里查询是否有对应的记录，没有，再查询数据库。
**
cache query
设置：hibernate.cache.use_query_cache true
在查询中使用
Query categoryByName =
session.createQuery("from Category c where c.name = :name");
categoryByName.setString("name", categoryName);
categoryByName.setCacheable(true);
/**
cascade="delete"级联删除的时候，传给delete(Object obj)必须是一个完整的对象，这样他才能级联删除。否则只删除本身
例如delete(new Object(objID);
**/
关联抓取：
<set name="ctRoles" table="user_role" lazy="true"
inverse="false">
<key>
<column name="USER_ID" />
</key>
<many-to-many class="model.CtRole"
column="ROLE_ID" fetch="select"/> <!-- 这里设置了关联查询使用select，当获得了一个user，循环遍历他的role，获取role的信息，则会采用根据ID select 关联表的方式，查询关联对象 并且全局设置中 hibernate.default_batch_fetch_size 的属性会影响到这里查询的次数 (使用id in 查询-->
</set>
例如 ：
Query query = session.createQuery("from User u where u.userId=18");
User user = (User)query.uniqueResult();
Iterator it = set.iterator();
for(;it.hasNext();){
CtRole role = (CtRole)it.next();
System.out.println(i + ":" + role.getName());
}
会导致以下查询：
Hibernate: select user0_.USER_ID as USER1_36_, user0_.USERNAME as USERNAME36_ from user user0_ where user0_.USER_ID=18
Hibernate: select roles0_.USER_ID as USER2_0_, roles0_.ROLE_ID as ROLE1_0_ from user_role roles0_ where roles0_.USER_ID=?
Hibernate: select role0_.ROLE_ID as ROLE1_34_0_, role0_.NAME as NAME34_0_ from role role0_ where role0_.ROLE_ID in (?, ?, ?, ?)
Hibernate: select role0_.ROLE_ID as ROLE1_34_0_, role0_.NAME as NAME34_0_ from role role0_ where role0_.ROLE_ID in (?, ?, ?, ?)
多对多关联添加对象：
user.getRoles.add(role); //user 一方的inverse="false" 则，只要调用user这方的save就可以保存 //user 和 role 之间的关联 inverse="true" 的一方没有办法保存
session.save(user);
但是这种做法不是最佳实践，因为多对多保存的时候会事先全表查询一次中间表，如果没有这个关联，则会去insert一条记录，这个全表查询在数据量大的时候比较耗内存，要慎用。最佳实践是 分解成两个1对多关联，这也是官方文档里最佳实践部分所推荐的做法。
