1.执行sql，返回一个结果集，适用查询一个字段并返回一条记录
public Long findSeqTbTest() { String sql = "select SEQ_TB_TEST.nextval from dual"; SQLQuery query = this.getSession().createSQLQuery(sql); String str = query.uniqueResult().toString(); return Long.valueOf(str); } //获取表中最小的id String sql = "select min(a.employeeid) from Emplyees a"; Long id = (Long) session.createQuery(sql).uniqueResult(); //获取数据库时间mysql String sql = "select now() from dual"; String time = session.createSQLQuery(sql).uniqueResult().toString();
2.删除、更新等操作，这里参数是从0开始的
public void deleteTbTest(Long id) { String hql = "DELETE FROM TbTest WHERE ID = ?"; this.getSession().createQuery(hql).setLong(0, id).executeUpdate(); } public void updateTbTest(Date date, boolean flag) { String sql = "update tb_test set t_name=? where t_status!=1 and t_date" + (flag ? "<?" : "=?"); SQLQuery query = this.getSession().createSQLQuery(sql); query.setString(0,flag ? "hello": "hi").setDate(1, date).executeUpdate(); } 3.执行sql，查询单表中多条数据
//尽量避免适用"*"
String sql = "select * from employee e where e.valid=1 and not exists (select employeeid from attendance a where a.employeeid=e.employeeid and a.date = ?)";
SQLQuery query = getSession().createSQLQuery(sql);
query.addEntity(Employee.class).setDate(0, day);
List<Employee> retList = query.list();
4.查询多表
String hql = "select new map(dept.deptID as deptID,dept.depNo as deptNo,dept.deptName as deptName,emp.empName as empName,emp.empID as empID,emp.empAge as age,emp.empNo as empNo) from Department dept ,Employee emp where dept.depID = emp.depID and emp.empName = ? and emp.sex = ?"; return getHibernateTemplate().find(hql, new Object[] { name, sex }); 每个字段都保存在map中（key是字段名，value是此字段的值如：[{empID=1,empName=leona,...},...]）
5.查询多表
String sql = "select dept.deptID as deptID,dept.depNo as deptNo,dept.deptName as deptName,emp.empName as empName,emp.empID as empID,emp.empAge as age,emp.empNo as empNo,emp.birthday as birthday from Employee emp LEFT JOIN Department dept on dept.depID = emp.depID where empName = ?"; return (List<EmpBean>) this.getSession() .createSQLQuery(sql) .addScalar("deptID", Hibernate.STRING) .addScalar("deptNo", Hibernate.STRING) .addScalar("deptName", Hibernate.STRING) .addScalar("empName", Hibernate.STRING) .addScalar("empID", Hibernate.STRING) .addScalar("age", Hibernate.LONG) .addScalar("birthday", Hibernate.DATE) .addScalar("empNo", Hibernate.STRING) .setString(0, empName) // 将结果集映射为EmpBean对象 .setResultTransformer( Transformers.aliasToBean(EmpBean.class)) .list(); String hql = "from Attendance att where att.employeeid = ? and att.date =? "; List<Attendance> list = this.getHibernateTemplate().find(hql, new Object[] { employeeid, workDay }); if (null != list && !list.isEmpty()) { return list.get(0); } String queryString = "FROM Attendance a WHERE a.employeeid=? AND DATE_FORMAT(a.date,'%Y-%m')=DATE_FORMAT(?,'%Y-%m') ORDER BY a.teamname"; Query queryObject = getSession(). createQuery(queryString); queryObject.setParameter(0, id); queryObject.setParameter(1, date); return queryObject.list();
Session session = getSession(); session.clear(); getSession().saveOrUpdate(transientInstance);
startBatch()的用法
public class LocalDaoImpl extends SqlMapClientDaoSupport implements LocalDao { public void insertBuNaTaxBatLst(final PaginatedList list) { getSqlMapClientTemplate().execute(new SqlMapClientCallback() { public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException { executor.startBatch(); // do some iBatis operations here for(int i=0,count=list.size();i<count;i++) { executor.insert("insertBuNaTaxBatLst", list.get(i)); if (i % 50 == 0) { System.out.println("----" + i);//没有意义只为测试 } } executor.executeBatch(); return null; } }); } 黑色头发:http://heisetoufa.iteye.com/