hibernate高级查询

条件查询

session.createCriteria(Item.class);
session.createCriteria(java.lang.Object.class);

 

session.createCriteria(User.class).addOrder( Order.asc("lastname") ).addOrder( Order.asc("firstname") );

 不必再session开启的条件下创建查询

DetachedCriteria crit =
DetachedCriteria.forClass(User.class).addOrder( Order.asc("lastname") ).addOrder( Order.asc("firstname") );
List result = crit.getExecutableCriteria(session).list();

限制

Criterion emailEq = Restrictions.eq("email", "foo@hibernate.org");
Criteria crit = session.createCriteria(User.class);
crit.add(emailEq);
User user = (User) crit.uniqueResult();

 

User user =
(User) session.createCriteria(User.class).add(Restrictions.eq("email", "foo@hibernate.org")).uniqueResult();

 

session.createCriteria(User.class).add( Property.forName("email").eq("foo@hibernate.org") );

 

session.createCriteria(User.class).add( Restrictions.eq("homeAddress.street", "Foo"));

 比较表达式

Criterion restriction =
Restrictions.between("amount",new BigDecimal(100),new BigDecimal(200) );
session.createCriteria(Bid.class).add(restriction);
session.createCriteria(Bid.class).add( Restrictions.gt("amount", new BigDecimal(100) ) );
String[] emails = { "foo@hibernate.org", "bar@hibernate.org" };
session.createCriteria(User.class).add( Restrictions.in("email", emails) );

 

session.createCriteria(User.class).add( Restrictions.isNull("email") );
session.createCriteria(User.class).add( Restrictions.isNotNull("email") );
session.createCriteria(Item.class).add( Restrictions.isEmpty("bids"));
session.createCriteria(Item.class).add( Restrictions.sizeGt("bids", 3));

 

session.createCriteria(User.class).add( Restrictions.eqProperty("firstname", "username") );

 字符串匹配

session.createCriteria(User.class).add( Restrictions.like("username", "G%") );
session.createCriteria(User.class).add( Restrictions.like("username", "G", MatchMode.START) );

 

session.createCriteria(User.class).add( Restrictions.eq("username", "foo").ignoreCase() );

 组合

session.createCriteria(User.class).add( Restrictions.like("firstname", "G%") ).add( Restrictions.like("lastname", "K%") );

 

session.createCriteria(User.class)
.add(
Restrictions.or(
Restrictions.and(
Restrictions.like("firstname", "G%"),
Restrictions.like("lastname", "K%")
),
Restrictions.in("email", emails)
)
);

 添加任意SQL

session.createCriteria(User.class)
.add( Restrictions.sqlRestriction(
"length({alias}.PASSWORD) < ?",
5,
Hibernate.INTEGER
)
);

 

session.createCriteria(Item.class)
.add( Restrictions.sqlRestriction(
"'100' > all" +
" ( select b.AMOUNT from BID b" +
" where b.ITEM_ID = {alias}.ITEM_ID )"
)
);

 子查询

DetachedCriteria subquery =
DetachedCriteria.forClass(Item.class, "i");
subquery.add( Restrictions.eqProperty("i.seller.id", "u.id"))
.add( Restrictions.isNotNull("i.successfulBid") )
.setProjection( Property.forName("i.id").count() );
Criteria criteria = session.createCriteria(User.class, "u")
.add( Subqueries.lt(10, subquery) );

 级联和动态抓取

Criteria itemCriteria = session.createCriteria(Item.class);
itemCriteria.add(
Restrictions.like("description",
"Foo",
MatchMode.ANYWHERE)
);
Criteria bidCriteria = itemCriteria.createCriteria("bids");
bidCriteria.add( Restrictions.gt( "amount", new BigDecimal(99) ) );
List result = itemCriteria.list();

 

List result =
session.createCriteria(Item.class)
.add( Restrictions.like("description",
"Foo",
MatchMode.ANYWHERE)
)
.createCriteria("bids")
.add( Restrictions.gt("amount", new BigDecimal(99) ) )
.list();

 通过条件查询动态抓取

session.createCriteria(Item.class)
.setFetchMode("bids", FetchMode.JOIN)
.add( Restrictions.like("description", "%Foo%") );

 

session.createCriteria(Item.class)
.createAlias("bids", "b", CriteriaSpecification.INNER_JOIN)
.setFetchMode("b", FetchMode.JOIN)
.add( Restrictions.like("description", "%Foo%") );

 投影

session.createCriteria(Item.class)
.add( Restrictions.gt("endDate", new Date()) )
.setProjection( Projections.id() );

 

session.createCriteria(Item.class)
.setProjection( Projections.projectionList()
.add( Projections.id() )
.add( Projections.property("description") )
.add( Projections.property("initialPrice") )
);

 

session.createCriteria(Item.class)
.setProjection( Projections.projectionList()
.add( Property.forName("id") )
.add( Property.forName("description") )
.add( Property.forName("initialPrice") )
);

 

session.createCriteria(Item.class)
.setProjection( Projections.projectionList()
.add( Projections.id().as("itemId") )
.add( Projections.property("description")
.as("itemDescription") )
.add( Projections.property("initialPrice")
.as("itemInitialPrice") )
).setResultTransformer(
new AliasToBeanResultTransformer(ItemPriceSummary.class)
);

 统计分组

session.createCriteria(Item.class).setProjection( Projections.rowCount() );

 

session.createCriteria(Bid.class)
.createAlias("bidder", "u")
.setProjection( Projections.projectionList()
.add( Property.forName("u.id").group() )
.add( Property.forName("u.username").group() )
.add( Property.forName("id").count())
.add( Property.forName("amount").avg() )
);

 

示例查询

public List findUsers(String firstname,
String lastname) {
StringBuffer queryString = new StringBuffer();
boolean conditionFound = false;
if (firstname != null) {
queryString.append("lower(u.firstname) like :firstname ");
conditionFound=true;
}
if (lastname != null) {
if (conditionFound) queryString.append("and ");
queryString.append("lower(u.lastname) like :lastname ");
conditionFound=true;
}
String fromClause = conditionFound ?
"from User u where " :
"from User u ";
queryString.insert(0, fromClause).append("order by u.username");
Query query = getSession()
.createQuery( queryString.toString() );
if (firstname != null)
query.setString( "firstName",
'%' + firstname.toLowerCase() + '%' );
if (lastname != null)
query.setString( "lastName",
'%' + lastname.toLowerCase() + '%' );
return query.list();
}

 

public List findUsers(String firstname,
String lastname) {
Criteria crit = getSession().createCriteria(User.class);
if (firstname != null) {
crit.add( Restrictions.ilike("firstname",firstname,
MatchMode.ANYWHERE) );
}
if (lastname != null) {
crit.add( Restrictions.ilike("lastname",
lastname,
MatchMode.ANYWHERE) );
}
crit.addOrder( Order.asc("username") );
return crit.list();
}

 

public List findUsersByExample(User u) throws {
Example exampleUser =
Example.create(u)
.ignoreCase()
.enableLike(MatchMode.ANYWHERE)
.excludeProperty("password");
return getSession().createCriteria(User.class)
.add(exampleUser)
.list();
}

 

public List findUsersByExample(User u){
Example exampleUser =
Example.create(u)
.ignoreCase()
.enableLike(MatchMode.ANYWHERE);
return getSession().createCriteria(User.class)
.add( exampleUser )
.createCriteria("items")
.add( Restrictions.isNull("successfulBid") )
.list();
}

 

public List findUsersByExample(User u, Item i) {
Example exampleUser =
Example.create(u).ignoreCase().enableLike(MatchMode.ANYWHERE);
Example exampleItem =
Example.create(i).ignoreCase().enableLike(MatchMode.ANYWHERE);
return getSession().createCriteria(User.class)
.add( exampleUser )
.createCriteria("items")
.add( exampleItem )
.list();
}

  原生查询

List result = session.createSQLQuery("select * from CATEGORY")
.addEntity(Category.class).list();

 

session.createSQLQuery("select {i.*} from ITEM i" +
" join USERS u on i.SELLER_ID = u.USER_ID" +
" where u.USERNAME = :uname")
.addEntity("i", Item.class)
.setParameter("uname", "johndoe");

 

session.createSQLQuery("select {i.*}, {u.*} from ITEM i" +
" join USERS u on i.SELLER_ID = u.USER_ID" +
" where u.USERNAME = :uname")
.addEntity("i", Item.class)
.addJoin("u", "i.seller")
.setParameter("uname", "johndoe");

 

session.createSQLQuery("select u.FIRSTNAME as fname from USERS u")
.addScalar("fname");

 

Properties params = new Properties();
params.put("enumClassname", "auction.model.Rating");
session.createSQLQuery(
"select c.RATING as rating from COMMENTS c" +
" where c.FROM_USER_ID = :uid"
)
.addScalar("rating",
Hibernate.custom(StringEnumUserType.class, params) )
.setParameter("uid", new Long(123));

 

session.createSQLQuery(
"select {i.*}, u.FIRSTNAME as fname from ITEM i" +
" join USERS u on i.SELLER_ID = u.USER_ID" +
" where u.USERNAME = :uname"
)
.addEntity("i", Item.class)
.addScalar("fname")
.setParameter("uname", "johndoe");

 过滤集合

List filteredCollection =session.createFilter( item.getBids(),"order by this.created asc" ).list();

 

List filteredCollection =session.createFilter( item.getBids(), "" ).list();

 

List filteredCollection =session.createFilter( item.getBids(), "" )
.setFirstResult(50).setMaxResults(100).list();

 

String filterString ="select other from Category other where this.name = other.name";
List result =session.createFilter( cat.getChildCategories(), filterString ).list();

 

List result =session.createFilter( item.getBids(),"select this.bidder" ).list();

 

List result =session.createFilter(item.getBids(),"select elements(this.bidder.bids)").list();

 

List result =session.createFilter( user.getBids(),
"where this.created > :oneWeekAgo" ).setTimestamp("oneWeekAgo", oneWeekAgo).list();