时间
select sysdate - to_date(CLAIM_TIME, ’yyyy - mm - dd hh24: mi: ss’) dt from
时间
select TO_CHAR(optime + 30, ’yyyy - mm - dd hh24: mi: ss’) FROM RWD_MERCHANT;
时间
select sysdate, TO_CHAR(sysdate + 30, 'yyyy-mm-dd HH24:MI:SS') from dual;
时间
SELECT CLAIM_TIME, SYSDATE - 3, CLAIM_TIME from RWD_MERCHANT WHERE CLAIM_TIME <= SYSDATE - 3;
时间
SELECT * from c_job WHERE TO_NUMBER(TO_CHARoptime, 'yyyyMM')) >= 201608 AND TO_NUMBER(TO_CHAR(optime, 'yyyyMM')) <= 201608;
级联
select c.ID, c.name, c.age, o.ID, o.order_number, o.customer_IDfrom Customer c inner join c.orders o group by c.age;
级联
Query query = session.createQuery(“from User user where user.name = : customername and user.age = : customerage”);
query.setString(“customername”, name);
query.setInteger(“customerage”, age);
级联
Query query = session.createQuery(“from User user where user.name = ? and user.age = ? ”);
query.setString(0, name);
query.setInteger(1, age);
级联
String hql = ”from User user where user.name = : customername”;
Query query = session.createQuery(hql);
query.setParameter(“customername”, name, Hibernate.STRING);
级联
Customer customer = new Customer();
customer.setName(“pansl”);
customer.setAge(80);
Query query = session.createQuery(“from Customer c where c.name = : name and c.age = : age”);
query.setProperties(customer);
级联
Customer customer = (Customer) session.load(Customer.class, ”1”);
Query query = session.createQuery(“from Order order where order.customer = : customer”);
query.setProperties(“customer”, customer);
List list = query.list();
//上面的代码会生成类似如下的SQL语句： Select * from order where customer_ID = ’1’;
//级联Restrictions.eq-- > equal, 等于
//Restrictions.allEq-- > 参数为Map对象, 使用key / value进行多个等于的比对, 相当于多个Restrictions.eq的效果
//Restrictions.gt-- > 大于
//Restrictions.ge-- >
//great - equal >= 大于等于Restrictions.lt-- > less - than, <
//小于Restrictions.le-- > less - equal <= 小于等于
//Restrictions.between-- >
//对应SQL的between子句Restrictions.like-- >
//对应SQL的LIKE子句Restrictions.in-- >
//对应SQL的in子句Restrictions.and-- > and
//关系Restrictions.or-- > or
//关系Restrictions.isNull-- >判断属性是否为空, 为空则返回trueRestrictions.isNotNull-- > 与isNull相反级联Session
session = HibernateSessionFactory.getSession();
Transaction tran = session.beginTransaction();
String[] names = {
"terry",
"larry",
"tom"
};
Criteria criteria = session.createCriteria(Customer.class);
criteria.add(Restrictions.in("name", names));
Listcustomers = criteria.list();
for (Customer cus: customers) {
System.out.println(cus);
}
级联
criteria.add(Restrictions.or(Restrictions.in("name", names), Restrictions.eq("age", 15)));
级联
Session session = getSessionFactory().getCurrentSession();
Criteria criteria = session.createCriteria(User.class);
级联
User user = new User();
user.setName("Jonh");
criteria.add(Example.create(user));
criteria.add(Restrictions.isNotNull("email"));
List list = criteria.list();
select i.*, u.* from ITEM i left outer join USERS u on i.SELLER_ID = u.USER_IDwhere i.ITEM_ID = ?
//在HQL中直接使用left join fetch， 直接查询出所有有的关联bids
collectionListallItems = session.createQuery("from Item i left join fetch i.bids").list();
Criteria方式， 效果同上ListallItems = session.createCriteria(Item.class).setFetchMode("bids", FetchMode.JOIN).list();
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
Item item = (Item) session.get(Item.class, new Long(1234));
Hibernate.initialize(item.getSeller());
//强制初始化seller proxy
tx.commit();
session.close();
processDetached(item.getSeller());
hibernate.format_sql
hibernate.use_sql_comments.
session.createCriteria(Item.class).createAlias("bids", "b")
.add(Restrictions.like("this.description", "%Foo%"))
.add(Restrictions.gt("b.amount", new BigDecimal(99)));
//返回的Item对象， bids集合属性被初始化
session.createCriteria(Item.class)
.setFetchMode("bids", FetchMode.INNER_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%"));
去重
List distinctResult = session.createCriteria(Item.class)
.setFetchMode("bids", FetchMode.JOIN)
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.list();
关联对象1
Criteria crit = session.createCriteria(Item.class)
.createAlias("bids", "b")
.createAlias("seller", "s")
.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List result = crit.list();
//List中是Map
for (Object aResult: result) {
Map map = (Map) aResult;
Item item = (Item) map.get(Criteria.ROOT_ALIAS);
Bid bid = (Bid) map.get("b");
User seller = (User) map.get("s");
}
关联对象2
Query q = session.createQuery("select i.id as itemId, i.description as desc, i.initialPrice as price from Item i");
q.setResultTransformer(Transformers.aliasToBean(ItemDTO.class));
//指定多个要查询的字段， Projections.property()
//此查询返回 a List of Object[]
session.createCriteria(Item.class)
.setProjection(Projections.projectionList()
.add(Projections.id())
.add(Projections.property("description"))
.add(Projections.property("initialPrice"))
);
//同上行等价的写法， 使用Property.forName()
session.createCriteria(Item.class)
.setProjection(Projections.projectionList()
.add(Property.forName("id"))
.add(Property.forName("description"))
.add(Property.forName("initialPrice"))
);
//根据u.id， u.username分组， 并统计bid的数量， 计算bid.amount的平均值
//返回 a collection of Object[]， 数组中有四个字段： user identifer, username, number of bids, average bid amount
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())
);
//同上一行等价的写法
session.createCriteria(Bid.class)
.createAlias("bidder", "u")
.setProjection(Projections.projectionList()
.add(Projections.groupProperty("u.id"))
.add(Projections.groupProperty("u.username"))
.add(Projections.count("id"))
.add(Projections.avg("amount"))
);
添加排序
session.createCriteria(Bid.class)
.createAlias("bidder", "u")
.setProjection(Projections.projectionList()
.add(Projections.groupProperty("u.id"))
.add(Projections.groupProperty("u.username").as("uname"))
.add(Projections.count("id"))
.add(Projections.avg("amount")))
.addOrder(Order.asc("uname"));
SQL嵌入式
String sqlFragment = "(select count(*) from ITEM i where i.ITEM_ID = ITEM_ID) as numOfItems";
session.createCriteria(Bid.class)
.createAlias("bidder", "u")
.setProjection(Projections.projectionList()
.add(Projections.groupProperty("u.id"))
.add(Projections.groupProperty("u.username"))
.add(Projections.count("id"))
.add(Projections.avg("amount) )
//添加SQL片断， 并指定字段名称及类型
.add(Projections.sqlProjection(sqlFragment, new String[] {
"numOfItems"
}, new Type[] {
Hibernate.LONG
})));
//SQL解析
SELECT u.USER_ID, u.USERNAME, count(BID_ID), avg(BID_AMOUNT),
(SELECT
count( * )
FROM
ITEM i WHERE i.ITEM_ID = ITEM_ID) AS numOfItems
FROM
BID INNER JOIN USERS u ON BIDDER_ID = u.USER_ID
GROUP BY
u.USER_ID, u.USERNAME
使用QBE
public List findUsersByExample(User u) {
Example exampleUser = Example.create(u)
//字符串类型查询时忽略大小写
.ignoreCase()
//字符串类型查询模糊
.enableLike(MatchMode.ANYWHERE)
//指定不包括的属性
.excludeProperty("password");
//默认所有的value - typed属性，
//不包括主键属性将用于查询匹配
return getSession().createCriteria(User.class).add(exampleUser).list();
}
QBE混合QBC
public List findUsersByExample(User u) {
Example exampleUser = Example.create(u)
.ignoreCase()
.enableLike(MatchMode.ANYWHERE);
return getSession().createCriteria(User.class)
.add(exampleUser)
.createCriteria("items")
//为items添加约束
.add(Restrictions.isNull("successfulBid")).list();
}
两个QBE
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();
}
