Hibernate 原生sql查询

1，Person

package com.supan.bean;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
public class Person {
	private int id;
	private String name;
	private int age;
    private Set<Address> addresss = new HashSet<Address>();
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public Set<Address> getAddresss() {
		return addresss;
	}
	public void setAddresss(Set<Address> addresss) {
		this.addresss = addresss;
	}
}

2，Address类

package com.supan.bean;
public class Address {
	private int id;
	private String name;
	private String info;
    private String remark;
    private Person person;
	public Person getPerson() {
		return person;
	}
	public void setPerson(Person person) {
		this.person = person;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getInfo() {
		return info;
	}
	public void setInfo(String info) {
		this.info = info;
	}
	public String getRemark() {
		return remark;
	}
	public void setRemark(String remark) {
		this.remark = remark;
	}
}

3，Person映射文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-lazy="true" default-access="property" package="com.supan.bean" >
    <class name="Person" dynamic-update="true" dynamic-insert="true" table="myperson">
        <id name="id" column="id" type="integer">
            <generator class="native"/>
        </id>
        <property name="name" type="string" column="name" length="20"/>
        <property name="age" type="integer" column="age" length="3"/>
        <set name="addresss" inverse="true" cascade="all" >
             <key column="personid"/>
             <one-to-many class="Address"/>
        </set>
    </class>
</hibernate-mapping>

4，Address映射文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping default-lazy="true" default-access="property" package="com.supan.bean" >
    <class name="Address" dynamic-update="true" dynamic-insert="true" table="address">
        <id name="id" column="id" type="integer">
            <generator class="native"/>
        </id>
        <property name="name" type="string" column="NAME" length="20"/>
        <property name="info" type="string" column="INFO" length="30"/>
        <property name="remark" type="string" column="REMARK" length="30"/>
        <many-to-one name="person" class="Person" column="personid" cascade="all"></many-to-one>
    </class>
</hibernate-mapping>

5，原生sql的测试类。

	/**
	 * <测试Hibernate的原生态sql查询> <查询多列返回来的是Object数组的list>
	 * 
	 * @author chenchaoyang
	 * @since 2014-03-22
	 */
	public void testSql1() {
		Configuration config = new Configuration().configure();
		SessionFactory sf = config.buildSessionFactory();
		Session sess = sf.openSession();
		Transaction tx = (Transaction) sess.beginTransaction();
		/** 注意 myperson是数据库中的表的名字，不是实体类的名字 */
		String sql = "select * from myperson";
		/** 注意饭回来的list中装的是Object[]数组，不同于hql查询是出来list中装的是实体 */
		List<Object[]> result = (List<Object[]>) sess.createSQLQuery(sql)
				.list();
		for (Object[] temp : result) {
			for (int i = 0; i < temp.length; i++) {
				System.out.println(temp[i]);
			}
		}
		tx.commit();
		sess.close();
	}

	/**
	 * <测试Hibernate的原生态sql查询> <查询单列，返回的是单个的值而不是object数组>
	 * 
	 * @author chenchaoyang
	 * @since 2014-03-22
	 */
	public void testSql2() {
		Configuration config = new Configuration().configure();
		SessionFactory sf = config.buildSessionFactory();
		Session sess = sf.openSession();
		Transaction tx = (Transaction) sess.beginTransaction();
		/** 注意 myperson是数据库中的表的名字，不是实体类的名字 */
		String sql = "select p.id from myperson p";
		/** 注意饭回来的list中装的是Object[]数组，不同于hql查询是出来list中装的是实体 */
		List result = sess.createSQLQuery(sql).list();
		if (result != null && result.size() > 0) {
			for (int i = 0; i < result.size(); i++) {
				System.out.println(result.get(i));
			}
		}
		tx.commit();
		sess.close();
	}

	/**
	 * <测试Hibernate的原生态sql查询--实体查询.的addEntity方法>
	 * <addEntity方法：在查詢一个实体的全部属性的时候，用addEntity方法可以把 查询结构封装陈实体对象的list>
	 * 注意：使用原生sql的实体查询，必须查询出所有的字段，如果有many-to-one也必须列出这个外键列，否则会报错
	 * clumn not found，最简单的就是select *
	 * 下面的方法用select *
	 * @author chenchaoyang
	 * @since 2014-03-22
	 */
	@SuppressWarnings(value = "unchecked")
	public void testSql3() {
		Configuration config = new Configuration().configure();
		SessionFactory sf = config.buildSessionFactory();
		Session sess = sf.openSession();
		Transaction tx = (Transaction) sess.beginTransaction();
		/** 注意 myperson是数据库中的表的名字，不是实体类的名字 */
		String sql = "select * from address a";
		/** 注意饭回来的list中装的是Object[]数组，不同于hql查询是出来list中装的是实体 */
		List<Address> result = (List<Address>) sess.createSQLQuery(sql)
				.addEntity(Address.class).list();
		for (Address address : result) {
			System.out.println(address.getId());
			System.out.println(address.getName());
			System.out.println(address.getInfo());
			System.out.println(address.getRemark());
			System.out.println(address.getPerson().getName());
			System.out.println("********************");

		}
		tx.commit();
		sess.close();
	}
	/**
	 * 输出： Hibernate: select person0_.id as id0_0_, person0_.name as name0_0_,
	 * person0_.age as age0_0_ from myperson person0_ where person0_.id=? 陈超阳
	 ******************** 
	 * 15 坂田五里围 我目前居住的地方 附近的消费很低 陈超阳
	 ******************** 
	 * 17 佛山 男孩 目前已经不再那里居住了 丽娟
	 ******************** 
	 */
	
	
	/**
	 * <测试Hibernate的原生态sql查询,的addEntity方法>
	 * <addEntity方法：在查詢一个实体的全部属性的时候，用addEntity方法可以把 查询结构封装陈实体对象的list>
	 * 注意：使用原生sql的实体查询，必须查询出所有的字段，如果有many-to-one也必须列出这个外键列，否则会报错
	 * 下面的方法用select 全部字段名称，和上一个方法的作用相同
	 * @author chenchaoyang
	 * @since 2014-03-22
	 */
	@SuppressWarnings(value = "unchecked")
	public void testSql4() {
		Configuration config = new Configuration().configure();
		SessionFactory sf = config.buildSessionFactory();
		Session sess = sf.openSession();
		Transaction tx = (Transaction) sess.beginTransaction();
		/** 注意 myperson是数据库中的表的名字，不是实体类的名字 */
		String sql = "select a.id,a.name,a.info,a.remark,a.personid from address a";
		/** 注意饭回来的list中装的是Object[]数组，不同于hql查询是出来list中装的是实体 */
		List<Address> result = (List<Address>) sess.createSQLQuery(sql)
				.addEntity(Address.class).list();
		for (Address address : result) {
			System.out.println(address.getId());
			System.out.println(address.getName());
			System.out.println(address.getInfo());
			System.out.println(address.getRemark());
			System.out.println(address.getPerson().getName());
			System.out.println("********************");
		}
		tx.commit();
		sess.close();
	}
	
	
	/**
	 * <测试Hibernate的原生态sql查询同样可以传递参数>
	 * @author chenchaoyang
	 * @since 2014-03-22
	 */
	@SuppressWarnings(value = "unchecked")
	public void testSql5() {
		Configuration config = new Configuration().configure();
		SessionFactory sf = config.buildSessionFactory();
		Session sess = sf.openSession();
		Transaction tx = (Transaction) sess.beginTransaction();
		/** 注意参数的形式是  :参数名字 */
		String sql = "select a.id,a.name,a.info,a.remark,a.personid from address a where a.name = :name";
		/** 注意传递参数的形式*/
		List<Address> result = (List<Address>) sess.createSQLQuery(sql)
				.addEntity(Address.class).setString("name", "英国伦敦").list();
		for (Address address : result) {
			System.out.println(address.getId());
			System.out.println(address.getName());
			System.out.println(address.getInfo());
			System.out.println(address.getRemark());
			System.out.println(address.getPerson().getName());
			System.out.println("********************");
		}
		tx.commit();
		sess.close();
	}
	
	/**
	 * <测试Hibernate的原生态sql将查询结果映射成多个实体>
	 * @author chenchaoyang
	 * @since 2014-03-22
	 */
	@SuppressWarnings(value = "unchecked")
	public void testSql6() {
		Configuration config = new Configuration().configure();
		SessionFactory sf = config.buildSessionFactory();
		Session sess = sf.openSession();
		Transaction tx = (Transaction) sess.beginTransaction();
		/** 注意参数的形式是  :参数名字 */
		String sql = "select p.*,a.* from myperson p,address a where p.id = a.personid";
		/** 注意传递参数的形式*/
		List<Object[]> result = (List<Object[]>) sess.createSQLQuery(sql)
				.addEntity("p",Person.class)
				.addEntity("a",Address.class).list();
		for(Object[] temp : result)
		{
			Person tempPerson = (Person)temp[0];
			System.out.print(tempPerson.getId());
			System.out.print(tempPerson.getName());
			System.out.println(tempPerson.getAge());
			
			Address tempAddress = (Address)temp[1];
			System.out.print(tempAddress.getId());
			System.out.print(tempAddress.getName());
			System.out.println(tempAddress.getInfo());
		}
		tx.commit();
		sess.close();
	}