hibernat的查询语句

Hibernate常见的查询方式

1. NativeSQL是运用数据库本身提供的数据查询语言进行查询的，这种方式查询效率高，与数据库耦合性高依赖于具体的数据库。因为不同的数据库厂商提供的查询语句会存在某些细微从差别。
2. HQL通过Hibernate提供的查询语言进行查询。Hibernate  y lanague。
3. EJBQL(JPQL1.0)是EJB提供的查询语言
4. QBC(query by cretira)通过Cretira接口进行查询
5. QBE(query by Example)通过Example编程接口进行查询
6. 从功能强弱上排序：NativeSQL>HQL>EJBQ(JPQL1.0)>QBC(query by cretira)>QBE

HQL查询

1. Hibernat  Query  lanague,是hibernate专门用于查询数据的语句，有别于SQL,HQL更接近于面向对象的思维方式
2. 通过session调用session.createQuery(hql)进行查询

单表查询

1. 查询集合通过调用list()
2. 查询单个数据通过调用uniqueResult()
3. 条件查询通过setParameter(索引别名占位符，对应的数值
4. 排序查询setFirstResult(id数值)和setMaxResults(总条数)
5. 聚合函数和sql相同可以使
6. 用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);
		}
	
	}
	
}