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