Hibernate查询语句

1：criteria查询

package com.itany.hibernate;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Expression;

import com.itany.util.HibernateUtil;

import junit.framework.TestCase;

public class CriteriaQuery extends TestCase{

public void test(){
Session session=null;
try{
session=HibernateUtil.getsSession();
Criteria criteria=session.createCriteria(Student.class);
criteria.add(Expression.like("name", "%0%"));
List students=criteria.list();
for(Object o:students){
Student s=(Student) o;
System.out.println("姓名："+s.getName());
System.out.println("班级："+s.getClasses().getName());
}
}catch (Exception e) {
}finally{
HibernateUtil.close(session);
}
}
}

2：简单属性查询

package com.itany.hibernate;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Expression;

import com.itany.util.HibernateUtil;

import junit.framework.TestCase;

public class SimplePropertyQuery extends TestCase{

//单一属性查询
public void testQuery() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

List students=session.createQuery("select name from Student").list();
for(Iterator it=students.iterator();it.hasNext();){
String name=(String) it.next();
System.out.println("姓名："+name);
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
//多个属性查询
public void testQuery2() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

List students=session.createQuery("select name,id from Student").list();
for(Iterator it=students.iterator();it.hasNext();){
Object[] obj=(Object[]) it.next();
System.out.println("学号："+obj[1]+",姓名："+obj[0]);
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
//多个属性查询（直接返回实体对象）
public void testQuery3() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

List students=session.createQuery("select new Student(id,name) from Student").list();
for(Iterator it=students.iterator();it.hasNext();){
Student s=(Student) it.next();
System.out.println("学号："+s.getId()+", 姓名："+s.getName());
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
}

3：简单对象查询

package com.itany.hibernate;

import java.util.Iterator;
import java.util.List;
import junit.framework.TestCase;
import org.hibernate.Session;
import com.itany.util.HibernateUtil;

public class SimpleObjectQuery extends TestCase{

//在hibernate中
public void testQuery() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

//List students=session.createQuery("from Student").list();
//在HQL中没有select * 要想使用可以使用别名代替
List students=session.createQuery("select s from Student s").list();
for(Iterator it=students.iterator();it.hasNext();){
Student s=(Student) it.next();
System.out.println("学号："+s.getId()+" , 姓名："+s.getName());
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
//使用iterate遍历的N+1的问题
//1：首先查询所有对象的id
//N：首先根据id在缓存中查询，如果不存在匹配的数据，再在数据库中根据id查询
public void testQuery2() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

Iterator it=session.createQuery("from Student").iterate();
while(it.hasNext()){
Student s=(Student) it.next();
System.out.println("学号："+s.getId()+" , 姓名："+s.getName());
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
//list与Iterator的区别
//list每次调用都发出sql语句，并将结果写入缓存中，但它不会读取缓存数据
//Iterator开始会出现N+1的问题，但是Iterator会利用缓存数据，当缓存中有数据的时候，它的效率会比list高
public void testQuery3() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

//List students=session.createQuery("from Student").list();
//在HQL中没有select * 要想使用可以使用别名代替
List students=session.createQuery("select s from Student s").list();
for(Iterator it=students.iterator();it.hasNext();){
Student s=(Student) it.next();
System.out.println("学号："+s.getId()+" , 姓名："+s.getName());
}
System.out.println("Iterator+***********");
Iterator it=session.createQuery("from Student").iterate();
while(it.hasNext()){
Student s=(Student) it.next();
System.out.println("学号："+s.getId()+" , 姓名："+s.getName());
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
public void testQuery4() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

System.out.println("Iterator1+***********");
Iterator it=session.createQuery("from Student").iterate();
while(it.hasNext()){
Student s=(Student) it.next();
System.out.println("学号："+s.getId()+" , 姓名："+s.getName());
}
System.out.println("Iterator1+***********");
Iterator it1=session.createQuery("from Student").iterate();
while(it1.hasNext()){
Student s=(Student) it1.next();
System.out.println("学号："+s.getId()+" , 姓名："+s.getName());
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
}

4：简单条件查询

package com.itany.hibernate;

import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

import com.itany.util.HibernateUtil;

import junit.framework.TestCase;

public class SimpleConditionQuery extends TestCase{

//不建议使用，容易被注入
public void testQuery1() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

List students=session.createQuery("select new Student(s.id,s.name) " +
"from Student s where s.name like ‘%1%‘").list();
for(Iterator it=students.iterator();it.hasNext();){
Student s=(Student) it.next();
System.out.println("学号："+s.getId()+" , 姓名："+s.getName());
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}


public void testQuery2() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

/*Query query=session.createQuery("select new Student(s.id,s.name) " +
"from Student s where s.name like ?");
query.setParameter(0, "%1%");
List students=query.list();*/
//链式调用
List students=session.createQuery
("select new Student(s.id,s.name) " +
"from Student s where s.name like ?")
.setParameter(0, "%1%").list();
for(Iterator it=students.iterator();it.hasNext();){
Student s=(Student) it.next();
System.out.println("学号："+s.getId()+" , 姓名："+s.getName());
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
//推荐使用参数名来传递参数
public void testQuery3() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

Query query=session.createQuery
("select new Student(s.id,s.name) " +
"from Student s where s.name like :name");
query.setParameter("name", "%1%");
List students=query.list();

for(Iterator it=students.iterator();it.hasNext();){
Student s=(Student) it.next();
System.out.println("学号："+s.getId()+" , 姓名："+s.getName());
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
//多条件查询
public void testQuery4() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

Query query=session.createQuery
("select new Student(s.id,s.name) " +
"from Student s where s.name like :name " +
"and s.id=:id");
query.setParameter("name", "%1%");
query.setParameter("id", 2);
List students=query.list();

for(Iterator it=students.iterator();it.hasNext();){
Student s=(Student) it.next();
System.out.println("学号："+s.getId()+" , 姓名："+s.getName());
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
//in条件
public void testQuery5() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

Query query=session.createQuery
("select new Student(s.id,s.name) " +
"from Student s where s.id in(:id)");
query.setParameterList("id",new Object[]{1,2,3});
List students=query.list();

for(Iterator it=students.iterator();it.hasNext();){
Student s=(Student) it.next();
System.out.println("学号："+s.getId()+" , 姓名："+s.getName());
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
//date_format()(mysql的专用函数)
//查询某个时间的数据
public void testQuery6() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

Query query=session.createQuery
("select s.id,s.name,s.createTime " +
"from Student s " +
"where date_format(s.createTime,‘%Y-%m‘)=:createTime");
query.setParameter("createTime","2015-03");
List students=query.list();

for(Iterator it=students.iterator();it.hasNext();){
Object[] obj=(Object[]) it.next();
System.out.println("学号："+obj[0]+",姓名："+obj[1]+","+obj[2]);
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
public void testQuery7() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Query query=session.createQuery
("select s.id,s.name,s.createTime " +
"from Student s " +
"where s.createTime between :createTime1 " +
"and :createTime2");
query.setParameter("createTime1",sdf.parse("2015-01-10 00:00:00"));
query.setParameter("createTime2",sdf.parse("2015-03-23 23:59:59"));
List students=query.list();

for(Iterator it=students.iterator();it.hasNext();){
Object[] obj=(Object[]) it.next();
System.out.println("学号："+obj[0]+",姓名："+obj[1]+","+obj[2]);
}
//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
}

5：sql原生查询语句

package com.itany.hibernate;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Session;

import com.itany.util.HibernateUtil;

import junit.framework.TestCase;

public class SQLQuery extends TestCase {

public void testQuery1() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

List students=session.createSQLQuery("select * from t_student").list();
for(Iterator it=students.iterator();it.hasNext();){
Object[] obj=(Object[]) it.next();
System.out.println("学号："+obj[0]+",姓名："+obj[1]+","+obj[2]);
}

//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
}

6：分页查询

package com.itany.hibernate;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Session;

import com.itany.util.HibernateUtil;

import junit.framework.TestCase;

public class PageQuery extends TestCase{

public void testQuery1() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

List students=session.createQuery("from Student")
.setFirstResult(1)
.setMaxResults(3)
.list();
System.out.println(students.size());

//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
}

7：对象导航查询

package com.itany.hibernate;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Session;

import com.itany.util.HibernateUtil;

import junit.framework.TestCase;

public class ObjectNavQuery extends TestCase{

public void testQuery1() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

List students=session.createQuery
("select s.name " +
"from Student s " +
"where s.classes.name like ‘%1%‘").list();
System.out.println(students.size());
for(Iterator it=students.iterator();it.hasNext();){
Object[] obj=(Object[]) it.next();
System.out.println("学号："+obj[0]);
}

//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
}

8：连接查询

package com.itany.hibernate;

import java.util.Iterator;
import java.util.List;

import org.hibernate.Session;

import com.itany.util.HibernateUtil;

import junit.framework.TestCase;

public class JoinQuery extends TestCase{
//内连接:关联条件用表的外键自动关联
public void testQuery1() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

List students=session.createQuery
("select s.name,c.name " +
"from Student s " +
"inner join s.classes c").list();
System.out.println(students.size());
for(Iterator it=students.iterator();it.hasNext();){
Object[] obj=(Object[]) it.next();
System.out.println(""+obj[0]+" , "+obj[1]);
}

//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
//左外连接
public void testQuery2() {
Session session=null;
try{
//通过session工厂创建session
session=HibernateUtil.getsSession();
//开启事物
session.beginTransaction();

List students=session.createQuery
("select s.name,c.name " +
"from Student s " +
"left join s.classes c").list();
System.out.println(students.size());
for(Iterator it=students.iterator();it.hasNext();){
Object[] obj=(Object[]) it.next();
System.out.println(""+obj[0]+" , "+obj[1]);
}

//提交事物
session.getTransaction().commit();
}catch (Exception e) {
//回滚事物
session.getTransaction().rollback();
}finally{
HibernateUtil.close(session);
}
}
}