Hibernate原生SQL

可以使用原生SQL来表达数据库查询，如果想利用数据库特有的功能，如查询提示或者Oracle中的CONNECT关键字。 Hibernate3.x允许使用手写SQL语句，包括存储过程，所有的创建，更新，删除和load操作。

应用程序将从会话创建一个原生SQL查询（Session接口上）createSQLQuery()方法：

 public SQLQuery createSQLQuery(String sqlString) throws HibernateException

当传递一个包含SQL查询到createSQLQuery()方法，可以将SQL结果与任何现有的Hibernate实体，联接，或者一个标量结果使用addEntity()方法，addJoin()，和addScalar()方法关联的字符串。

标量查询：

最基本的SQL查询是从一个或多个表中得到标量（数值）的列表。以下是语法使用原生SQL标量的值：

 String sql = "SELECT first_name, salary FROM EMPLOYEE"; SQLQuery query = session.createSQLQuery(sql); query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); List results = query.list();

实体的查询：

上面的查询都是返回标量值，也就是从resultset中返回的“裸”数据。以下是语法通过addEntity（）方法来从原生SQL查询获得实体对象作为一个整体。

 String sql = "SELECT * FROM EMPLOYEE"; SQLQuery query = session.createSQLQuery(sql); query.addEntity(Employee.class); List results = query.list();

命名SQL查询：

以下是语法通过addEntity（）方法来从原生SQL查询获得实体对象和使用命名SQL查询。

 String sql = "SELECT * FROM EMPLOYEE WHERE id = :employee_id"; SQLQuery query = session.createSQLQuery(sql); query.addEntity(Employee.class); query.setParameter("employee_id", 10); List results = query.list();

Native SQL 例子：

考虑下面的POJO类：

 public class Employee {    private int id;    private String firstName;     private String lastName;       private int salary;       public Employee() {}    public Employee(String fname, String lname, int salary) {       this.firstName = fname;       this.lastName = lname;       this.salary = salary;    }    public int getId() {       return id;    }    public void setId( int id ) {       this.id = id;    }    public String getFirstName() {       return firstName;    }    public void setFirstName( String first_name ) {       this.firstName = first_name;    }    public String getLastName() {       return lastName;    }    public void setLastName( String last_name ) {       this.lastName = last_name;    }    public int getSalary() {       return salary;    }    public void setSalary( int salary ) {       this.salary = salary;    } }

让我们创建下面的EMPLOYEE表来存储Employee对象：

 create table EMPLOYEE (    id INT NOT NULL auto_increment,    first_name VARCHAR(20) default NULL,    last_name  VARCHAR(20) default NULL,    salary     INT  default NULL,    PRIMARY KEY (id) );

以下将被映射文件。

 <?xml version="1.0" encoding="utf-8"?> <!DOCTYPE hibernate-mapping PUBLIC   "-//Hibernate/Hibernate Mapping DTD//EN"  "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">   <hibernate-mapping>    <class name="Employee" table="EMPLOYEE">       <meta attribute="class-description">          This class contains the employee detail.        </meta>       <id name="id" type="int" column="id">          <generator class="native"/>       </id>       <property name="firstName" column="first_name" type="string"/>       <property name="lastName" column="last_name" type="string"/>       <property name="salary" column="salary" type="int"/>    </class> </hibernate-mapping>

最后，我们将创建应用程序类的main（）方法来运行，我们将使用原生SQL查询的应用程序：

 import java.util.*;    import org.hibernate.HibernateException;  import org.hibernate.Session;  import org.hibernate.Transaction; import org.hibernate.SessionFactory; import org.hibernate.SQLQuery; import org.hibernate.Criteria; import org.hibernate.Hibernate; import org.hibernate.cfg.Configuration;  public class ManageEmployee {    private static SessionFactory factory;     public static void main(String[] args) {       try{          factory = new Configuration().configure().buildSessionFactory();       }catch (Throwable ex) {           System.err.println("Failed to create sessionFactory object." + ex);          throw new ExceptionInInitializerError(ex);        }       ManageEmployee ME = new ManageEmployee();        /* Add few employee records in database */       Integer empID1 = ME.addEmployee("Zara", "Ali", 2000);       Integer empID2 = ME.addEmployee("Daisy", "Das", 5000);       Integer empID3 = ME.addEmployee("John", "Paul", 5000);       Integer empID4 = ME.addEmployee("Mohd", "Yasee", 3000);        /* List down employees and their salary using Scalar Query */       ME.listEmployeesScalar();        /* List down complete employees information using Entity Query */       ME.listEmployeesEntity();    }    /* Method to CREATE an employee in the database */    public Integer addEmployee(String fname, String lname, int salary){       Session session = factory.openSession();       Transaction tx = null;       Integer employeeID = null;       try{          tx = session.beginTransaction();          Employee employee = new Employee(fname, lname, salary);          employeeID = (Integer) session.save(employee);           tx.commit();       }catch (HibernateException e) {          if (tx!=null) tx.rollback();          e.printStackTrace();        }finally {          session.close();        }       return employeeID;    }     /* Method to  READ all the employees using Scalar Query */    public void listEmployeesScalar( ){       Session session = factory.openSession();       Transaction tx = null;       try{          tx = session.beginTransaction();          String sql = "SELECT first_name, salary FROM EMPLOYEE";          SQLQuery query = session.createSQLQuery(sql);          query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);          List data = query.list();           for(Object object : data)          {             Map row = (Map)object;             System.out.print("First Name: " + row.get("first_name"));              System.out.println(", Salary: " + row.get("salary"));           }          tx.commit();       }catch (HibernateException e) {          if (tx!=null) tx.rollback();          e.printStackTrace();        }finally {          session.close();        }    }     /* Method to  READ all the employees using Entity Query */    public void listEmployeesEntity( ){       Session session = factory.openSession();       Transaction tx = null;       try{          tx = session.beginTransaction();          String sql = "SELECT * FROM EMPLOYEE";          SQLQuery query = session.createSQLQuery(sql);          query.addEntity(Employee.class);          List employees = query.list();           for (Iterator iterator =                             employees.iterator(); iterator.hasNext();){             Employee employee = (Employee) iterator.next();              System.out.print("First Name: " + employee.getFirstName());              System.out.print("  Last Name: " + employee.getLastName());              System.out.println("  Salary: " + employee.getSalary());           }          tx.commit();       }catch (HibernateException e) {          if (tx!=null) tx.rollback();          e.printStackTrace();        }finally {          session.close();        }    } }

编译和执行：

下面是步骤来编译并运行上述应用程序。请确保在进行的编译和执行之前，适当地设置PATH和CLASSPATH。

• 创建hibernate.cfg.xml配置文件中配置章节解释。

• 创建Employee.hbm.xml映射文件，如上图所示。

• 创建Employee.java源文件，如上图所示，并编译它。

• 创建ManageEmployee.java源文件，如上图所示，并编译它。

• 执行ManageEmployee二进制文件来运行程序。

会得到以下结果，并记录将在EMPLOYEE表中创建。

 $java ManageEmployee .......VARIOUS LOG MESSAGES WILL DISPLAY HERE........  First Name: Zara, Salary: 2000 First Name: Daisy, Salary: 5000 First Name: John, Salary: 5000 First Name: Mohd, Salary: 3000 First Name: Zara  Last Name: Ali  Salary: 2000 First Name: Daisy  Last Name: Das  Salary: 5000 First Name: John  Last Name: Paul  Salary: 5000 First Name: Mohd  Last Name: Yasee  Salary: 3000

如果检查EMPLOYEE表，它应该记录下已：

 mysql> select * from EMPLOYEE; +----+------------+-----------+--------+ | id | first_name | last_name | salary | +----+------------+-----------+--------+ | 26 | Zara       | Ali       |   2000 | | 27 | Daisy      | Das       |   5000 | | 28 | John       | Paul      |   5000 | | 29 | Mohd       | Yasee     |   3000 | +----+------------+-----------+--------+ 4 rows in set (0.00 sec)  mysql>