深入研究Hibernate之六:原生SQL

当项目很大的时候，如果使用HQL，那么势必会对效率造成不小的影响，但是Hibernate对原生的SQL也提供了很好的支持：

package org.yanglg.test;

import java.util.Arrays;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.junit.Test;
import org.yanglg.model.Classroom;
import org.yanglg.model.Student;
import org.yanglg.model.StudentDTO;
import org.yanglg.utils.HibernateUtils;

@SuppressWarnings("unchecked")
public class SQLTest {
	@Test
	public void test1() {
		Session session = null;

		try {
			session = HibernateUtils.openSession();
			String sql = "select * from t_stu";
			List<Student> list = session.createSQLQuery(sql)
					.addEntity(Student.class).list();
			for (Student ele : list) {
				System.out.println(ele);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}

	}

	@Test
	public void test2() {
		Session session = null;

		try {
			session = HibernateUtils.openSession();
			String sql = "select stu.*,cla.* from t_stu stu left join t_cla cla "
					+ "on stu.c_id = cla.id where stu.id > 200";
			List<Object[]> list = session.createSQLQuery(sql)
					.addEntity(Student.class).addEntity(Classroom.class).list();
			for (Object[] ele : list) {
				System.out.println(Arrays.toString(ele));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}

	}

	@Test
	public void test3() {
		Session session = null;

		try {
			session = HibernateUtils.openSession();
			String sql = "select stu.id as sid,stu.name as sname,cla.name as cname,spe.name as spename"
					+ " from t_stu stu "
					+ "left join t_cla cla on (stu.c_id = cla.id) "
					+ "left join t_spe spe on (cla.spe_id = spe.id)";

			List<StudentDTO> list = session
					.createSQLQuery(sql)
					.setResultTransformer(
							Transformers.aliasToBean(StudentDTO.class))
					.setFirstResult(0).setMaxResults(10).list();
			for (StudentDTO ele : list) {
				System.out.println(ele);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}

	}
}

test1：原生的SQL直接映射成对象，不过需要设置一下对象(addEntity())

test2：Join出两个对象，设置两个实体

test3：采用DTO的形式，不过需要一个额外的DTO：

package org.yanglg.model;

public class StudentDTO {
	private int sid;
	private String sname;
	private String cname;
	private String spename;

	public int getSid() {
		return sid;
	}

	public void setSid(int sid) {
		this.sid = sid;
	}

	public String getSname() {
		return sname;
	}

	public void setSname(String sname) {
		this.sname = sname;
	}

	public String getCname() {
		return cname;
	}

	public void setCname(String cname) {
		this.cname = cname;
	}

	public String getSpename() {
		return spename;
	}

	public void setSpename(String spename) {
		this.spename = spename;
	}

	public StudentDTO() {
	}

	public StudentDTO(int sid, String sname, String cname, String spename) {
		super();
		this.sid = sid;
		this.sname = sname;
		this.cname = cname;
		this.spename = spename;
	}

}

BUT，此示例在MYSQL数据库下运行通过，但是在Oracle环境下却报一下异常：

org.hibernate.PropertyAccessException: IllegalArgumentException occurred while calling setter of org.yanglg.model.StudentDTO.SID

很诡异，最后百度半天，终于明白oracle会把字段的名字自动改为大写，所以找不到setter方法，试了网上大部分解决办法，都是不行，最后拿出终极武器：修改Hibernate源代码！

修改好的源码如下：

package org.hibernate.property;
import org.hibernate.PropertyNotFoundException;

public class ChainedPropertyAccessor implements PropertyAccessor {
 final PropertyAccessor[] chain;
 
 public ChainedPropertyAccessor(PropertyAccessor[] chain) {
  this.chain = chain;
 }
 
 public Getter getGetter(Class theClass, String propertyName)
   throws PropertyNotFoundException {
  Getter result = null;
  for (int i = 0; i < chain.length; i++) {
   PropertyAccessor candidate = chain[i];
   try { 
    result = candidate.getGetter(theClass, propertyName);
    return result;
   } catch (PropertyNotFoundException pnfe) {
      //添加修改
    try{  
    result = candidate.getGetter(theClass, propertyName.toLowerCase());
    return result;}
      catch(PropertyNotFoundException xx){
      
      }
   }
  }
  throw new PropertyNotFoundException("Could not find getter for " + propertyName + " on " + theClass);
 }
        
 public Setter getSetter(Class theClass, String propertyName)
   throws PropertyNotFoundException {
  Setter result = null;
  for (int i = 0; i < chain.length; i++) {
   PropertyAccessor candidate = chain[i];    
   try {
    result = candidate.getSetter(theClass, propertyName);
    return result;
   } catch (PropertyNotFoundException pnfe) {
 
     //添加修改
    try{
    result = candidate.getSetter(theClass, propertyName.toLowerCase());
    return result;}
      catch(PropertyNotFoundException xx){
      
      }
     
   }
  }
  throw new PropertyNotFoundException("Could not find setter for " + propertyName + " on " + theClass);
 }
}

编译生成classes.更新Jar包文件 ，搞定！