hibernate 执行原生sql,select返回string,long(1)

2401_84815303

于 2024-05-14 20:40:11 发布

阅读量758

点赞数 19

分类专栏：程序员文章标签： hibernate sql windows

本文链接：https://blog.youkuaiyun.com/2401_84815303/article/details/138869306

版权

程序员专栏收录该内容

112 篇文章

订阅专栏

Java代码

public void deleteTbTest(Long id) {
String hql = “DELETE FROM TbTest WHERE ID = ?”;
this.getSession().createQuery(hql).setLong(0, id).executeUpdate();
}

Java代码

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 retList = query.list();

4.查询多表

Java代码

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.查询多表

Java代码

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) 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();

Java代码

String hql = "from Attendance att where att.employeeid = ? and att.date =? ";
List list = this.getHibernateTemplate().find(hql,
new Object[] { employeeid, workDay });
if (null != list && !list.isEmpty()) {
return list.get(0);
}

Java代码

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();

Java代码