hibernate使用原生SQL查询

以下是Demo测试Hibernate 原生SQL查询：

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import org.hibernate.Hibernate;

import org.hibernate.SQLQuery;

import org.hibernate.Session;

import org.hibernate.Transaction;

import org.hibernate.transform.Transformers;

import com.hibernate.bean.SqlQueryBean;

import com.hibernate.bean.User;

import com.hibernate.util.HibernateUtil;

public class SqlQueryTest {

 public static void main (String [] args){

  sqlQueryAddScalar();

 }

  static void sqlQueryAddEntity(){

     //获取Hibernate Session对象

     Session session = HibernateUtil.getSession();

     //开始事务

     Transaction tx = session.beginTransaction();

     //编写SQL语句

     String sqlString = "select * from user";

     //以SQL语句创建SQLQuery对象

     List l = session.createSQLQuery(sqlString)

                     //将查询ss实体关联的User类

                     .addEntity("ss",User.class)

                     //返回全部的记录集

                     .list();

     //遍历结果集

     Iterator it = l.iterator();

     while (it.hasNext()){

         //因为将查询结果与Student类关联，因此返回的是Student集合

         User s = (User)it.next();

         String a = s.getName();

         System.out.println(a);

     }

     //提交事务

     tx.commit();

     session.close();

 }

 static void sqlQueryAddScalar(){

  //返回Object[]的遍历

  Session session=HibernateUtil.getSession();

  Transaction transaction=session.beginTransaction();

  String sql="select * from user";

  SQLQuery s=(SQLQuery) session.createSQLQuery(sql); //.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)

  s.addScalar("id",Hibernate.INTEGER); //列名, 数据类型

  s.addScalar("name",Hibernate.STRING);

  List list=s.list();

  Object[] objects=(Object[])list.get(0);

  System.out.println(objects[0]);

  System.out.println(objects[1]);

  //用MAP

  SQLQuery ss=(SQLQuery) session.createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

  ss.addScalar("id",Hibernate.INTEGER);

  ss.addScalar("name",Hibernate.STRING);

  List list2=ss.list();

  Map map = (Map) list2.get(0);

  System.out.println(map.get("name"));

  System.out.println(map.get("id"));

  //用LIST

  SQLQuery s3=(SQLQuery) session.createSQLQuery(sql).setResultTransformer(Transformers.TO_LIST);

  s3.addScalar("id",Hibernate.INTEGER);

  s3.addScalar("name",Hibernate.STRING);

  List list3=s3.list();

  List list4=(List)list3.get(0);

  System.out.println(list4.get(0));

  System.out.println(list4.get(1));

  //用自定义的bean

  SQLQuery e=(SQLQuery) session.createSQLQuery(sql).setResultTransformer(Transformers.aliasToBean(SqlQueryBean.class));

  e.addScalar("id",Hibernate.INTEGER);

  e.addScalar("name",Hibernate.STRING);

  List r=e.list();

  SqlQueryBean sqb=(SqlQueryBean)r.get(0);

  System.out.println(sqb.getId());

  System.out.println(sqb.getName());

  }

}

分享