Hibernate常见的查询方式
- NativeSQL是运用数据库本身提供的数据查询语言进行查询的,这种方式查询效率高,与数据库耦合性高依赖于具体的数据库。因为不同的数据库厂商提供的查询语句会存在某些细微从差别。
- HQL通过Hibernate提供的查询语言进行查询。Hibernate y lanague。
- EJBQL(JPQL1.0)是EJB提供的查询语言
- QBC(query by cretira)通过Cretira接口进行查询
- QBE(query by Example)通过Example编程接口进行查询
- 从功能强弱上排序:NativeSQL>HQL>EJBQ(JPQL1.0)>QBC(query by cretira)>QBE
HQL查询
- Hibernat Query lanague,是hibernate专门用于查询数据的语句,有别于SQL,HQL更接近于面向对象的思维方式
- 通过session调用session.createQuery(hql)进行查询
单表查询
- 查询集合通过调用list()
- 查询单个数据通过调用uniqueResult()
- 条件查询通过setParameter(索引别名占位符,对应的数值
- 排序查询setFirstResult(id数值)和setMaxResults(总条数)
- 聚合函数和sql相同可以使
- 用sum(id),count(*),max(id),avg(id)建议使用Number类型进行接收操作
绑定参数
占位符?使用?index在hql语句中代替具体参数,(从0开始)
别名,格式"属性= :别名"
public void getUserWithNameAndPwd(){
Query query = session.createQuery("from User where name=?0 or password=?1");
//param1:?的索引
//param2:?对应的具体数值
query.setParameter(0, "admin");
query.setParameter(1, "123");
List<User> list = query.list();
for (User user : list) {
System.out.println(user);
}
}
/*********************************************************************************************************************************************************/
public void getUserWithNameAndPwd2(){
Query query = session.createQuery("from User where name = :name or password = :pwd");
query.setParameter("name", "admin");
query.setParameter("pwd", "123");
List<User> list = query.list();
for (User user : list) {
System.out.println(user);
}
}
查询对象中某些属性,可以使用两种方法
//第一种方法
String hql ="select id,name from User";
List<Object[]> list = session.createQuery(hql).list();
for(Object[] o : list){
System.out.printlhn(Arrays.toString(o));
}
//第二种方法
/**
可以使用select new User(id,name) from User 需要在User中创建相同类型的构造方法,注意如果创建了0构造方法,注意如果创建了构造方法,那么必须在创建空参的构造方法
*/
String hql ="select new User(id,name) from User";
List<User> list = session.createQuery(hql).list();
System.out.println(list);
package com.zhiyou.test;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import com.zhiyou.pojo.User;
public class Demo {
public static void main(String[] args) {
// TODO Auto-generated method stub
//1.加载配置文件,获取配置信息
//configure():默认加载src下的hibernate.cfg.xml
Configuration config = new Configuration().configure();
//2.构建sessionFactory
SessionFactory factory = config.buildSessionFactory();
//3.生成session对象
Session session = factory.openSession();
//4.开启事务
Transaction transaction = session.beginTransaction();
//数据操作
//添加一条记录
/* User user = new User();
user.setName("abc");
user.setPassword("333");
user.setAge(20);
//添加
session.save(user);*/
//修改记录
/* User user = new User();
user.setId(1);
user.setName("admin");
user.setPassword("123456");
user.setAge(30);
session.update(user);
*/
//刪除
/* User user = new User();
user.setId(4);
session.delete(user);*/
//查询
/*User user = session.get(User.class, 1);
System.out.println(user);*/
User user = session.get(User.class, 1);
user.setName("李四");
//清除session缓存数据
//session.clear();
//5.提交事务
transaction.commit();
//6.关闭资源
session.close();
factory.close();
}
}
/* hibernate缓存
* 一级缓存:session级别的缓存
* 二级缓存:sessionFactory级别的缓存
*
*/
package com.zhiyou.test;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import com.zhiyou.pojo.User;
public class Demo1 {
public static void main(String[] args) {
// TODO Auto-generated method stub
Configuration cofig = new Configuration().configure();
SessionFactory factory = cofig.buildSessionFactory();
Session session = factory.openSession();
Transaction transaction = session.beginTransaction();
//数据处理
/*User user = new User();
user.setName("admin");
user.setPassword("123456");
user.setAge(30);
session.save(user);*/
User user = session.load(User.class,1);
System.out.println(user);
transaction.commit();
session.close();
Session session2 = factory.openSession();
Transaction transaction2 = session2.beginTransaction();
User user2 = session2.load(User.class, 1);
System.out.println("user2:"+user2);
transaction2.commit();
session2.close();
factory.close();
}
}
package com.zhiyou.test;
import java.util.Arrays;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.zhiyou.pojo.User;
public class Demo2 {
SessionFactory factory;
Session session;
Transaction transaction;
@Before
public void before(){
Configuration config = new Configuration().configure();
factory = config.buildSessionFactory();
session = factory.openSession();
transaction = session.beginTransaction();
}
@After
public void close(){
transaction.commit();
session.close();
factory.close();
}
//获取所有数据
//select * from user
public void getAll(){
Query query = session.createQuery("from com.zhiyou.pojo.User");
List<User> list = query.list();
for (User user : list) {
System.out.println(user);
}
}
//select * from user where id = 2
@Test
public void getOne(){
User user = (User) session.createQuery("from User where id = 2 ").uniqueResult();
System.out.println(user);
}
//根据用户名查询
public void getUserByName(){
Query query = session.createQuery("from User where name = ?0 ");
//替换参数
query.setParameter(0, "zhangsan");
User user = (User) query.uniqueResult();
System.out.println(user);
}
//select * from user where name='admin' or password='123'
public void getUserWithNameAndPwd(){
Query query = session.createQuery("from User where name=?0 or password=?1");
//param1:?的索引
//param2:?对应的具体数值
query.setParameter(0, "admin");
query.setParameter(1, "123");
List<User> list = query.list();
for (User user : list) {
System.out.println(user);
}
}
public void getUserWithNameAndPwd2(){
Query query = session.createQuery("from User where name = :name or password = :pwd");
query.setParameter("name", "admin");
query.setParameter("pwd", "123");
List<User> list = query.list();
for (User user : list) {
System.out.println(user);
}
}
//select * from user order by u_age desc
public void sortUser(){
List<User> list = session.createQuery("from User order by age desc").list();
for (User user : list) {
System.out.println(user);
}
}
//select count(id) from user
public void count(){
Number count = (Number) session.createQuery("select count(*) from User").uniqueResult();
System.out.println(count);
}
//根据名称修改密码
public void update(){
session.createQuery("update User set password=:pwd where name=:name").
setParameter("pwd", "123").setParameter("name", "admin").executeUpdate();
}
public void delete(){
session.createQuery("delete from User where name=:name").
setParameter("name", "admin").executeUpdate();
}
//查询id,name
public void query1(){
List<Object[]> list = session.createQuery("select id,name from User").list();
for (Object[] objects : list) {
System.out.println(Arrays.toString(objects));
}
}
@Test
public void query2(){
List<User> list = session.createQuery("select new User(id,name) from User").list();
for (User user : list) {
System.out.println(user);
}
}
}
package com.zhiyou.test;
import java.util.Arrays;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import com.zhiyou.pojo.Room;
import com.zhiyou.pojo.Student;
import com.zhiyou.pojo.Teacher;
public class Demo3 {
SessionFactory factory;
Session session;
Transaction transaction;
@Before
public void before(){
Configuration config = new Configuration().configure();
factory = config.buildSessionFactory();
session = factory.openSession();
transaction = session.beginTransaction();
}
@After
public void close(){
transaction.commit();
session.close();
factory.close();
}
public void addStudent(){
Student s1 = new Student();
s1.setName("小白");
s1.setPhone("132121212");
session.save(s1);
}
//张三 101教室
public void addTeacher(){
Room room = new Room();
room.setName("102教室");
Teacher teacher = new Teacher();
teacher.setName("李四");
teacher.setRoom(room);
session.save(room);
session.save(teacher);
}
public void queryOne(){
Teacher teacher = session.get(Teacher.class, 2);
System.out.println(teacher);
}
//
public void queryAllTeachers(){
List<Object[]> list = session.createQuery("from Teacher t left join t.room").list();
for (Object[] objects : list) {
System.out.println(Arrays.toString(objects));
}
}
//from Room r left join fetch r.teacher
public void queryAllTeachers2(){
List<Teacher> list = session.createQuery("from Teacher t left join fetch t.room").list();
for (Teacher teacher : list) {
System.out.println(teacher);
}
}
public void addStudents(){
Room room = new Room();
room.setName("201教室");
Student s1 = new Student();
s1.setName("小黑");
s1.setRoom(room);
Student s2 = new Student();
s2.setName("小明");
s2.setRoom(room);
/*room.getStudents().add(s1);
room.getStudents().add(s2);*/
session.save(room);
session.save(s1);
session.save(s2);
}
//查询所有学生信息
public void getAllStudents(){
List<Student> list = session.createQuery("from Student s left join fetch s.room").list();
for (Student student : list) {
System.out.println(student);
}
}
//查询所有教室
public void getAllRooms(){
List<Room> list = session.createQuery("select distinct r from Room r left join fetch r.students").list();
for (Room room : list) {
System.out.println(room);
}
}
@Test
public void getAllStudents2(){
List<Student> list = session.createQuery("from Student s left join fetch s.courses").list();
for (Student student : list) {
System.out.println(student);
}
}
}