Hibernate中使用SQL查询数据

在Hibernate中也可以使用标准SQL查询数据，格式如下：
SQLQuery sqlQuery = session.createSQLQuery("SQL语句");

hibernate.cfg.xml:

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<!-- Generated by MyEclipse Hibernate Tools. -->
<hibernate-configuration>

<session-factory>
<property name="connection.username">scott</property>
<property name="connection.url">jdbc:oracle:thin:@127.0.0.1:1521:MGC</property>
<property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
<property name="myeclipse.connection.profile">MGC</property>
<property name="connection.password">tiger</property>
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>

<property name="show_sql">true</property>

<mapping resource="cn/edu/ahau/mgc/hibernate/pojo/Student.hbm.xml" />
<mapping resource="cn/edu/ahau/mgc/hibernate/pojo/Classes.hbm.xml" />

</session-factory>

</hibernate-configuration>


Student.java:

package cn.edu.ahau.mgc.hibernate.pojo;

public class Student {

private long id;
private String name;
private Classes classes;

public Classes getClasses() {
return classes;
}

public void setClasses(Classes classes) {
this.classes = classes;
}

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


Classes.java:

package cn.edu.ahau.mgc.hibernate.pojo;

import java.util.Set;

public class Classes {

private long id;
private String name;
private Set<Student> students;

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 Set<Student> getStudents() {
return students;
}

public void setStudents(Set<Student> students) {
this.students = students;
}
}


Student.hbm.xml:

<?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">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="cn.edu.ahau.mgc.hibernate.pojo.Classes" table="HQL_CLASSES" schema="SCOTT">
<id name="id" type="java.lang.Long">
<column name="ID" precision="22" scale="0" />
<generator class="native" />
</id>
<property name="name" type="java.lang.String">
<column name="NAME" length="20" not-null="true" />
</property>
<set name="students" inverse="true">
<key column="CLASSID" />
<one-to-many class="cn.edu.ahau.mgc.hibernate.pojo.Student"/>
</set>
</class>
</hibernate-mapping>


Classes.hbm.xml:

<?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">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="cn.edu.ahau.mgc.hibernate.pojo.Student" table="HQL_STUDENT" schema="SCOTT">
<id name="id" type="java.lang.Long">
<column name="ID" precision="22" scale="0" />
<generator class="native" />
</id>
<property name="name" type="java.lang.String">
<column name="NAME" length="20" not-null="true" />
</property>

<many-to-one name="classes" column="CLASSID" />
</class>
</hibernate-mapping>


HibernateSessionFactory.java:

package cn.edu.ahau.mgc.hibernate.many2one.factory;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.cfg.Configuration;

/**
* Configures and provides access to Hibernate sessions, tied to the
* current thread of execution. Follows the Thread Local Session
* pattern, see {@link http://hibernate.org/42.html }.
*/
public class HibernateSessionFactory {

/**
* Location of hibernate.cfg.xml file.
* Location should be on the classpath as Hibernate uses
* #resourceAsStream style lookup for its configuration file.
* The default classpath location of the hibernate config file is
* in the default package. Use #setConfigFile() to update
* the location of the configuration file for the current session.
*/
private static String CONFIG_FILE_LOCATION = "/hibernate.cfg.xml";
private static final ThreadLocal<Session> threadLocal = new ThreadLocal<Session>();
private static Configuration configuration = new Configuration();
private static org.hibernate.SessionFactory sessionFactory;
private static String configFile = CONFIG_FILE_LOCATION;

static {
try {
configuration.configure(configFile);
sessionFactory = configuration.buildSessionFactory();
} catch (Exception e) {
System.err
.println("%%%% Error Creating SessionFactory %%%%");
e.printStackTrace();
}
}
private HibernateSessionFactory() {
}

/**
* Returns the ThreadLocal Session instance. Lazy initialize
* the <code>SessionFactory</code> if needed.
*
* @return Session
* @throws HibernateException
*/
public static Session getSession() throws HibernateException {
Session session = (Session) threadLocal.get();

if (session == null || !session.isOpen()) {
if (sessionFactory == null) {
rebuildSessionFactory();
}
session = (sessionFactory != null) ? sessionFactory.openSession()
: null;
threadLocal.set(session);
}

return session;
}

/**
* Rebuild hibernate session factory
*
*/
public static void rebuildSessionFactory() {
try {
configuration.configure(configFile);
sessionFactory = configuration.buildSessionFactory();
} catch (Exception e) {
System.err
.println("%%%% Error Creating SessionFactory %%%%");
e.printStackTrace();
}
}

/**
* Close the single hibernate session instance.
*
* @throws HibernateException
*/
public static void closeSession() throws HibernateException {
Session session = (Session) threadLocal.get();
threadLocal.set(null);

if (session != null) {
session.close();
}
}

/**
* return session factory
*
*/
public static org.hibernate.SessionFactory getSessionFactory() {
return sessionFactory;
}

/**
* return session factory
*
* session factory will be rebuilded in the next call
*/
public static void setConfigFile(String configFile) {
HibernateSessionFactory.configFile = configFile;
sessionFactory = null;
}

/**
* return hibernate configuration
*
*/
public static Configuration getConfiguration() {
return configuration;
}

}


ExportToDBCreate.java:

package cn.edu.ahau.mgc.hibernate.export;

import org.hibernate.cfg.Configuration;
import org.hibernate.tool.hbm2ddl.SchemaExport;

public class ExportToDBCreate {

public static void main(String[] args) {
Configuration cfg = new Configuration().configure();
SchemaExport export = new SchemaExport(cfg);
export.create(true, true);
}

}


InitData.java:

package cn.edu.ahau.mgc.hibernate.export;

import org.hibernate.Session;

import cn.edu.ahau.mgc.hibernate.factory.HibernateSessionFactory;
import cn.edu.ahau.mgc.hibernate.pojo.Classes;
import cn.edu.ahau.mgc.hibernate.pojo.Student;


public class InitData {


public static void main(String[] args) {
Session session = HibernateSessionFactory.getSession();
session.beginTransaction();

for (int i = 0; i < 3; i++) {
Classes classes = new Classes();
classes.setName("Class_" + i);
session.save(classes);

for (int j = 0; j < 3; j++) {
Student student = new Student();
student.setName("Class_" + i + "-Student_" + j);
student.setClasses(classes);
session.save(student);
}
}

for (int i = 0; i < 3; i++) {
Classes classes = new Classes();
classes.setName("NoStudent-Class_" + i);
session.save(classes);
}

for (int i = 0; i < 3; i++) {
Student student = new Student();
student.setName("NoClass-Student_" + i);
session.save(student);
}


session.getTransaction().commit();
HibernateSessionFactory.closeSession();
}

}


TestQueryBySQL.java:

package cn.edu.ahau.mgc.hibernate.pojo;


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

import junit.framework.TestCase;

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

import cn.edu.ahau.mgc.hibernate.factory.HibernateSessionFactory;

public class TestQueryBySQL extends TestCase {

public void testSQL() {
Session session = null;
try {
session = HibernateSessionFactory.getSession();
SQLQuery sqlQuery = session.createSQLQuery("select * from HQL_STUDENT");
List<Object[]> students = sqlQuery.list();
System.out.println("--TEST1:");
for (Iterator<Object[]> iterator = students.iterator(); iterator.hasNext();) {
Object[] student = iterator.next();
System.out.println("----id:" + student[0] + ";name:" + student[1]);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
HibernateSessionFactory.closeSession();
}
}

}