hibernate 原生态sql 别名 查询 取值

//查询的sql语句
StringBuffer sql = new StringBuffer(" select a.address as address ,a.zzrs as zzrs ,SUBSTRING (convert(varchar (20),(a.zzrs*1.0/b.zs*100)),1,4)+'%' as bfb  from ");
			sql.append(" (select address ,count(address) as zzrs  from History group by  address) a ");
			sql.append(" ,(select count(*) as zs from History) b order by a.zzrs desc ");


//查询方法
public Pagination getPaginactionHql_(final String sql,final String countSql, final int pageSize,
			final int pageNumber) {
		return (Pagination) this.getHibernateTemplate().execute(
				new HibernateCallback() {
					// 返回Paginaction
					int totalCount = ((Integer) queryObjectCount(countSql))
							.intValue();
					Pagination pagination = new Pagination(pageSize,
							pageNumber, totalCount);

					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Query query = session.createSQLQuery(sql).
						addScalar("address", new org.hibernate.type.StringType()).
						addScalar("zzrs", new org.hibernate.type.StringType()).
						addScalar("bfb", new org.hibernate.type.StringType());
						    
						// 最大记录数
						query.setMaxResults(pageSize);
						// 偏移量
						query.setFirstResult(pageSize * (pageNumber - 1));
						List list = query.list();
						if (list.size() > 0) {
							pagination.setList(list);
							return pagination;
						} else {
							return null;
						}

					}
				});
	}


//取值
for(Iterator<Object[]> a = this.pagination.getList().iterator();a.hasNext();){
			Object[] test = a.next();
			System.out.println((String)test[0]+"  "+(String)test[1]+"   "+(String)test[2]);
		}
        
		System.out.println("*******************************");
		
		Object[] objs = this.pagination.getList().toArray();
		for(Object o :objs){
			Object[] oo = (Object[]) o;
			System.out.println(oo[0]+"   "+oo[1]+"       "+oo[2]);
		}

*****************************************************************************************

在问答里和论坛中，经常看到有人问，怎样将使用本地SQL查询出来的结果映射为值对象的问题，这里就Hibernate中提供的方法做个结论。前提，这里没有使用属性的延迟加载技术。

    假设有个值对像，如下：

    package test;  
      
    public class Person {  
        private Long id;  
        private String name;  
        private Long age;  
        private Long phone;  
        private String address;  
      
        public Person(Long id, String name, Long age, Long phone, String address) {  
            this.id = id;  
            this.name = name;  
            this.age = age;  
            this.phone = phone;  
            this.address = address;  
        }  
      
        public Long getId() {  
            return id;  
        }  
      
        public void setId(Long id) {  
            this.id = id;  
        }  
      
        public String getName() {  
            return name;  
        }  
      
        public void setName(String name) {  
            this.name = name;  
        }  
      
        public Long getAge() {  
            return age;  
        }  
      
        public void setAge(Long age) {  
            this.age = age;  
        }  
      
        public Long getPhone() {  
            return phone;  
        }  
      
        public void setPhone(Long phone) {  
            this.phone = phone;  
        }  
      
        public String getAddress() {  
            return address;  
        }  
      
        public void setAddress(String address) {  
            this.address = address;  
        }  
    }  

 

如果查询全部五列记录的话，那么只要

List list = getHibernateTemplate().loadAll(Person.class);

   

如果只需要查询id,name,age三列记录呢？那么就要新增构造方法了，

    public  Person(Long id, String name, Long age) {  
        this .id = id;  
        this .name = name;  
        this .age = age;  
    }  

 

然后呢，就可以通过HQL来进行查询。

List list = getHibernateTemplate().find( "select new test.Person(id,name,age) from Person" );

 

 这个方法通常可以满足需要了，只是如果，只需要查询id,name,phone三列记录的话，还新增构造方法？不行了，会出现构造方法冲突了。有个办法：

 List list = getSession().createQuery( "select id,name,phone from person" )  .addScalar("id" ,Hibernate.Long).addScalar( "name" ).addScalar( "phone" ,Hibernate.Long)  .addEntity(Person.class );

 

但是，这个只适用于存在Person实体的，如果Hibernate中没有进行Person映射的呢，系统中只存在一个JavaBean。

 List list = getSession().createSQLQuery( "select id /" id/ ",name /" name/ ",phone /" phone/ " from person" )  
                    .addScalar("id" ,Hibernate.Long).addScalar( "name" ).addScalar( "phone" ,Hibernate.Long)  
                    .setResultTransformer(Transformers.aliasToBean(Person.class ))); 

 

 

  那么Hibernate就会自动将查出来的三列内容组装到VO对象中去，只是代码量有点大，而且名称都需要重新定义为小写的，在Oracle中查出来的列都默认为大写的（不知道其它数据库怎么样）

    这个办法就不依赖于构造方法了，只需要定义私有变量，设置getter/setter方法就行了。

    不过如果更猛点的，根本就没有JavaBean对象可以填充怎么办，Hibernate可以将查出来的列组装到集合类中去。如Map。

 

    List list = getSession().createSQLQuery( "select * from person" )  
                        .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  

 

 除了这个Transformers还可以将列转化为List。

    List list = getSession().createSQLQuery( "select * from person" )  
                        .setResultTransformer(Transformers.T0_LIST);  

 

  

以下参考：主要是【SQLQuery】用法

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());
  }