1、创建表
@Test
public void testAdd() {
Session session = HibernateUtils.getSession();
Transaction ts = session.beginTransaction();
try {
for (int i = 0; i <1000; i++) {
User user = new User();
if (i % 2 == 0) {
user.setUserName("周志强");
user.setGender(1);
user.setBirthday(new Date(1997-1990,06-1,07));
user.setAddress("天水");
} else {
user.setUserName("张豆豆");
user.setGender(0);
user.setBirthday(new Date(2002-1990,06-1,03));
user.setAddress("重庆");
}
if (i%10 ==0) {
session.flush();
}
session.flush();
session.save(user);
}
ts.commit();
} catch (Exception e) {
e.printStackTrace();
ts.rollback();
}finally {
HibernateUtils.closeResource(session);
}
}
2、hql语句
2.1hql基本查询
hql语句中所以表名和字段名名
类名 属性名
2.1.1(查询表中所有属性) String hql = “from User”;
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
String hql = "from User";
Query query = session.createQuery(hql);
List<User> userList = query.list();
for (User user:userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
2.1.2(查询表中单个属性)String hql = “select u.userName from User u”;
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
String hql = "select u.userName from User u";
Query query = session.createQuery(hql);
List<String> nameList = query.list();
for (String name:nameList) {
System.out.println(name);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
2.1.3(查询表中多个属性) String hql = “select u.userName,u.gender from User u”;
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
String hql = "select u.userName,u.gender from User u";
Query query = session.createQuery(hql);
List<Object[]> arrayList = query.list();
for (Object[] array :arrayList) {
System.out.println("姓名:"+array[0]+":性别:"+array[1]);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
2.1.4(hql分页查询重点)
//设置开始行号
//从前台传递过来的是页码的pageNo 公式(pageNo-1)*pageSize
query.setFirstResult(0);
//设置每页的数量(pageSize)
query.setMaxResults(10);
翻页计算公式(每页的开始行号): (pageNo-1)*pageSize
sql语句:select *from 表 limit (pageNo-1)*pageSize ,pageSize
该sql语句第一个参数开始行号,第二个参数每页的记录数
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
String hql = "from User";
Query query = session.createQuery(hql);
//设置开始行号
//从前台传递过来的是页码的pageNo 公式(pageNo-1)*pageSize
query.setFirstResult(0);
//设置每页的数量(pageSize)
query.setMaxResults(10);
List<User> userList = query.list();
for (User user:userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
2.1.5(限定查询:有条件的查询) String hql = “select u.userName from User u where u.gender=?”;
//设置第一个缺少的参数 和jdbc不同 预编译索引 是从0开始的不是从1开始的 切记 切记
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
String hql = "select u.userName from User u where u.gender=?";
Query query = session.createQuery(hql);
//设置第一个缺少的参数 和jdbc不同 参数是从0开始的不是从1开始的
query.setParameter(0, 0);
List<String> userList = query.list();
for (String user:userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
2.1.6(统计查询)String hql = “select count(u.userId) from User u”;
Object o = query.uniqueResult();
String hql = “select max(u.userId) from User u”;
String hql = “select min(u.userId) from User u”;
String hql = “select sum(u.userId) from User u”;
String hql = “select avg(u.userId) from User u”;
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
String hql = "select count(u.userId) from User u";
Query query = session.createQuery(hql);
Object o = query.uniqueResult();
System.out.println(o);
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
2.1.7(分组统计查询)
String hql = “select avg(u.salary),u.gender from User u group by u.gender having avg(u.salary)>6000”;
having:条件筛选
sql语句一定写对 程序出错75% 可能是sql语句写错 先检查sql语句。
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
String hql = "select avg(u.salary),u.gender from User u group by u.gender having avg(u.salary)>6000";
Query query = session.createQuery(hql);
List<Object[]> arrayList =query.list();
for (Object[] array:arrayList) {
System.out.println("性别" + array[1] + ":平均薪水:" + array[0]);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
运行结果:
性别0::平均薪水:5500.0
性别1:平均薪水:9990.0
2.1.8(投影查询)
先创建一个接收查询结果的bean(必须给定它的有参构造器才能用它来接收sql执行的结果
package model;
public class EmpBean {
private double avgSalary;
private Integer gender;
public EmpBean(double avgSalary, Integer gender) {
this.avgSalary = avgSalary;
this.gender = gender;
}
public double getAvgSalary() {
return avgSalary;
}
public void setAvgSalary(Integer avgSalary) {
this.avgSalary = avgSalary;
}
@Override
public String toString() {
return "EmpBean{" +
"avgSalary=" + avgSalary +
", gender=" + gender +
'}';
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
}
//我们可以创建一个业务bean,在bean中提供有参数的构造器来返回sql执行的值。
创建这个对象的语法在select 后直接new 这个对象,
也就是创建有参构造器对象区别是里面的实参是填入sql执行后的值。
顺序必须和构造器里的顺序一致,不然会报错。
查询后的返回结果就是这个对象,相当于用sql语句new出了这个对象。
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
//我们可以创建一个业务bean,在bean中提供有参数的构造器来返回sql执行的值。创建这个对象的语法在select 后直接new 这个对象
String hql="select new model.EmpBean(avg(u.salary),u.gender) from User u group by gender";
Query query = session.createQuery(hql);
List<EmpBean> empBeans =query.list();
for (EmpBean bean:empBeans) {
System.out.println("性别" + bean.getGender() + ":平均薪水:" + bean.getAvgSalary());
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
}
使用投影查询的好处是避免使用数组进行接收,数据更加集中。遍历也更加方便。一般都用这种而不用普通的分组查询。
2.1.9(排序查询)
String hql = "select u from User u order by u.salary asc"; 按照工资升序排列
asc:升序
desc:降序
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
String hql = "select u from User u order by u.salary asc";
Query query = session.createQuery(hql);
List<User> userList = query.list();
for (User user : userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
2.1.9(模糊查询 重点)
String hql = "select u from User u where u.userName like '%张%'"
模糊语句用 ’‘ 括在里面
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
String hql = "select u from User u where u.userName like '%张%'";
Query query = session.createQuery(hql);
List<User> userList = query.list();
for (User user : userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
String hql = "select u from User u where u.userName like '%志_'"; 查询倒数第二个是志的数据
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
String hql = "select u from User u where u.userName like '%志_'";
Query query = session.createQuery(hql);
List<User> userList = query.list();
for (User user : userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
2.10 在配置文件中写sql语句 (推荐使用)
名字是固定的 参数设置最好使用 :名字 这样的格式 尽量不要使用 问号 ? query写在class下面 不要写在class里面
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="model">
<class name="model.User" table="t_user">
<id name="userId" column="user_id">
<generator class="native"/>
</id>
<property name="userName" column="user_name"/>
<property name="gender"/>
<property name="birthday"/>
<property name="address"/>
<property name="salary"/>
</class>
<query name="getUserAll">
<![CDATA[
from User u where u.salary>:salary
]]>
</query>
</hibernate-mapping>
测试类
Query query = session.getNamedQuery("getUserAll"); //获得sql语句的
query.setParameter("salary", 5000);//设置参数
List<User> userList = query.list();//执行
都用的同一个query
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
Query query = session.getNamedQuery("getUserAll");
query.setParameter("salary", 5000);
List<User> userList = query.list();
for (User user : userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
3、QBC查询
3.1(QBC基本查询)
//创建qbc查询接口的实现类
Criteria criteria = session.createCriteria(User.class);
//查询User类对象的列表
List<User> userList = criteria.list();
3.1.1 基本查询(查询全量数据)
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
//创建qbc查询接口的实现类
Criteria criteria = session.createCriteria(User.class);
//查询User类对象的列表
List<User> userList = criteria.list();
for (User user : userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
3.1.2 基本查询(查询单个属性)
//创建qbc查询接口的实现类
Criteria c = session.createCriteria(User.class);
//设置要具体查询的列
c.setProjection(Projections.property("userName"));
List<String> nameList = c.list();
也可以分步骤写
Criteria c = session.createCriteria(User.class);
PropertyProjection userName = Projections.property("userName");
c.setProjection(userName);
List<String> nameList = c.list();
不过建议连在一起写
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
//创建qbc查询接口的实现类
Criteria c = session.createCriteria(User.class);
//设置要具体查询的列
c.setProjection(Projections.property("userName"));
List<String> nameList = c.list();
for (String name : nameList) {
System.out.println(name);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
3.1.2 基本查询(查询多个属性)
Criteria c = session.createCriteria(User.class);
//创建要查询的具体的属性
PropertyProjection userName = Projections.property("userName");
PropertyProjection gender = Projections.property("gender");
//创建要查询的列的集合
ProjectionList projectionList = Projections.projectionList();
//把要查询的列加入到集合中
projectionList.add(userName);
projectionList.add(gender);
//把要查询的属性集合 设置到projection中
c.setProjection(projectionList);
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
Criteria c = session.createCriteria(User.class);
//创建要查询的具体的属性
PropertyProjection userName = Projections.property("userName");
PropertyProjection gender = Projections.property("gender");
//创建要查询的列的集合
ProjectionList projectionList = Projections.projectionList();
//把要查询的列加入到集合中
projectionList.add(userName);
projectionList.add(gender);
//把要查询的属性集合 设置到projection中
c.setProjection(projectionList);
List<Object[]> objectsList = c.list();
for (Object[] objArray : objectsList) {
System.out.println("姓名:"+objArray[0]+":性别:"+objArray[1]);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
3.2(QBC分组统计查询)
3.2.1QBC查询(函数使用 Count函数是统计某一属性有多少行)
//设置要查询的项
//查询总数的函数
Projection rowCount = Projections.rowCount();
//查询平均值的函数
AggregateProjection salary = Projections.avg("salary");
//查询总和的函数
AggregateProjection salary = Projections.sum("salary");
//查询最大值的函数
AggregateProjection salary = Projections.max("salary");
//查询最小值的函数
AggregateProjection salary = Projections.min("salary");
c.setProjection(rowCount);
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
Criteria c = session.createCriteria(User.class);
AggregateProjection salary = Projections.count("gender");
AggregateProjection salary1 = Projections.avg("salary");
AggregateProjection salary2 = Projections.sum("salary");
AggregateProjection salary3 = Projections.max("salary");
AggregateProjection salary4 = Projections.min("salary");
c.setProjection(salary);
Object o = c.uniqueResult();
System.out.println(o);
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
3.2.2QBC查询(分组统计查询:通过性别分组查询出最高薪水)
Criteria c = session.createCriteria(User.class);
ProjectionList pL = Projections.projectionList();
//设置要查询的属性
PropertyProjection gender = Projections.property("gender");
AggregateProjection salary = Projections.max("salary");
//根据性别进行分组
PropertyProjection gender1 = Projections.groupProperty("gender");
pL.add(gender);
pL.add(salary);
pL.add(gender1);
c.setProjection(pL);
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
Criteria c = session.createCriteria(User.class);
ProjectionList pL = Projections.projectionList();
//设置要查询的属性
PropertyProjection gender = Projections.property("gender");
AggregateProjection salary = Projections.max("salary");
//根据性别进行分组
PropertyProjection gender1 = Projections.groupProperty("gender");
pL.add(gender);
pL.add(salary);
pL.add(gender1);
c.setProjection(pL);
List<Object[]> objectsList = c.list();
for (Object[] objects : objectsList) {
System.out.println("性别:" + objects[0] + ":最高薪水:" + objects[1]);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
3.4(QBC 限定排序分页查询)
//分组排序
SELECT gender,salary FROM t_user GROUP BY gender ORDER BY salary ASC;
//分页查询
SELECT *FROM t_user LIMIT 100,10;
//limit后第一个参数开始id,第二个参数每页有多少个
3.4.1(排序查询)
//设置排序字段·
c.addOrder(Order.desc("salary"));
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
Criteria c = session.createCriteria(User.class);
//设置要查询的属性
PropertyProjection username = Projections.property("userName");
PropertyProjection gender = Projections.property("gender");
ProjectionList pL = Projections.projectionList();
pL.add(username);
pL.add(gender);
c.setProjection(pL);
//设置排序字段·
c.addOrder(Order.desc("salary"));
List<Object[]> objectsList = c.list();
for (Object[] objects : objectsList) {
System.out.println("姓名:" + objects[0] + ":性别" + objects[1]);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
3.4.2(分页查询)
//设置分页查询
c.setFirstResult(1);
c.setMaxResults(10);
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
Criteria c = session.createCriteria(User.class);
//设置要查询的属性
PropertyProjection username = Projections.property("userName");
PropertyProjection gender = Projections.property("gender");
ProjectionList pL = Projections.projectionList();
pL.add(username);
pL.add(gender);
c.setProjection(pL);
//设置分页查询
c.setFirstResult(1);
c.setMaxResults(10);
List<Object[]> objectsList = c.list();
for (Object[] objects : objectsList) {
System.out.println("姓名:" + objects[0] + ":性别" + objects[1]);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
3.4.2(限定查询)
//创建QBC查询接口
Criteria c = session.createCriteria(User.class);
//创建查询条件
SimpleExpression eq = Restrictions.eq("userName", "周志强");
//把查询条件设置给QBC查询接口
c.add(eq);
List<User> userList = c.list();
eq:等于
gt:大于
lt:小于
ge:大于等于
le:小于等于
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
Criteria c = session.createCriteria(User.class);
SimpleExpression id = Restrictions.eq("userId", 1);
c.add(id);
List<User> userList = c.list();
for (User user : userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
3.4.3(限定查询关于in的用法)
//原始sql语句
SELECT *FROM t_user u WHERE u.user_id IN(1,2,3);
//创建QBC查询接口
Criteria c = session.createCriteria(User.class);
Integer[] userIds=new Integer[]{1,2,3,4};
//设置in
Criterion userId = Restrictions.in("userId", userIds);
//将查询条件加入到查询接口中
c.add(userId);
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
//创建QBC查询接口
Criteria c = session.createCriteria(User.class);
Integer[] userIds=new Integer[]{1,2,3,4};
//设置in
Criterion userId = Restrictions.in("userId", userIds);
//将查询条件加入到查询接口中
c.add(userId);
List<User> userList = c.list();
for (User user : userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
3.4.4(限定查询关于and 和 or的用法)
//原始sql语句
SELECT *FROM t_user u WHERE u.salary>=5000 AND u.gender=0;
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
//创建QBC查询接口
Criteria c = session.createCriteria(User.class);
Integer[] userIds=new Integer[]{1,2,3,4};
//设置查询条件工资>=5000 and 性别=0
LogicalExpression and = Restrictions.and(Restrictions.ge("salary", 5000), Restrictions.eq("gender", 0));
//将查询条件加入到查询接口中
c.add(and);
List<User> userList = c.list();
for (User user : userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
3.5(QBC 模糊查询)
//原始sql语句
SELECT *from t_user u WHERE u.user_name like '%志_';
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
//创建QBC查询接口
Criteria c = session.createCriteria(User.class);
Integer[] userIds=new Integer[]{1,2,3,4};
//设置查询条件 姓名倒数第二个是志的人
SimpleExpression userName = Restrictions.like("userName", "%志_");
//将查询条件加入到查询接口中
c.add(userName);
List<User> userList = c.list();
for (User user : userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}
4、hibernate中也可以使用普通sql
String sql="select count(*) from t_user";
SQLQuery query = session.createSQLQuery(sql);
Object o = query.uniqueResult();
System.out.println(o);
@Test
public void testQuery() {
Session session = HibernateUtils.getSession();
try {
String sql="select count(*) from t_user";
SQLQuery query = session.createSQLQuery(sql);
Object o = query.uniqueResult();
System.out.println(o);
} catch (Exception e) {
e.printStackTrace();
}finally {
HibernateUtils.closeResource(session);
}
}