原生 SQL

    当你通过一个包含 SQL 查询的 createsqlquery() 方法的字符串时，你可以将 SQL 的结果与现有的 Hibernate 实体，一个连接，或一个标量结果分别使用 addEntity(), addJoin(), 和 addScalar() 方法进行关联。

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

    2. SQLQuery query = session.createSQLQuery(sql);
    3. query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
    4. List results = query.list();

    以上的查询都是关于返回标量值的查询，只是基础性地返回结果集中的“原始”值。以下是从原生 SQL 查询中通过 addEntity() 方法获取实体对象整体的语法： 

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

    考虑下面的 POJO 类： 

    1. public class Employee {
    2.    private int id;
    3.    private String firstName; 
    4.    private String lastName;   
    5.    private int salary;  
    7.    public Employee() {}
    8.    public Employee(String fname, String lname, int salary) {
    9.       this.firstName = fname;
    10.       this.lastName = lname;
    11.       this.salary = salary;
    12.    }
    13.    public int getId() {
    14.       return id;
    15.    }
    16.    public void setId( int id ) {
    17.       this.id = id;
    18.    }
    19.    public String getFirstName() {
    20.       return firstName;
    21.    }
    22.    public void setFirstName( String first_name ) {
    23.       this.firstName = first_name;
    24.    }
    25.    public String getLastName() {
    26.       return lastName;
    27.    }
    28.    public void setLastName( String last_name ) {
    29.       this.lastName = last_name;
    30.    }
    31.    public int getSalary() {
    32.       return salary;
    33.    }
    34.    public void setSalary( int salary ) {
    35.       this.salary = salary;
    36.    }
    37. }

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

    1. create table EMPLOYEE (
    2.    id INT NOT NULL auto_increment,
    3.    first_name VARCHAR(20) default NULL,
    4.    last_name  VARCHAR(20) default NULL,
    5.    salary     INT  default NULL,
    6.    PRIMARY KEY (id)
    7. );

    以下是映射文件： 

    1. import java.util.*; 
    3. import org.hibernate.Session; 
    5. import org.hibernate.SessionFactory;
    6. import org.hibernate.SQLQuery;
    7. import org.hibernate.Criteria;
    8. import org.hibernate.Hibernate;
    9. import org.hibernate.cfg.Configuration;
    11. public class ManageEmployee {
    12.    private static SessionFactory factory; 
    13.    public static void main(String[] args) {
    14.       try{
    15.          factory = new Configuration().configure().buildSessionFactory();
    16.       }catch (Throwable ex) { 
    17.          System.err.println("Failed to create sessionFactory object." + ex);
    18.          throw new ExceptionInInitializerError(ex); 
    19.       }
    20.       ManageEmployee ME = new ManageEmployee();
    22.       /* Add few employee records in database */
    23.       Integer empID1 = ME.addEmployee("Zara", "Ali", 2000);
    24.       Integer empID2 = ME.addEmployee("Daisy", "Das", 5000);
    25.       Integer empID3 = ME.addEmployee("John", "Paul", 5000);
    26.       Integer empID4 = ME.addEmployee("Mohd", "Yasee", 3000);
    28.       /* List down employees and their salary using Scalar Query */
    29.       ME.listEmployeesScalar();
    31.       /* List down complete employees information using Entity Query */
    32.       ME.listEmployeesEntity();
    33.    }
    34.    /* Method to CREATE an employee in the database */
    35.    public Integer addEmployee(String fname, String lname, int salary){
    36.       Session session = factory.openSession();
    37.       Transaction tx = null;
    38.       Integer employeeID = null;
    39.       try{
    40.          tx = session.beginTransaction();
    41.          Employee employee = new Employee(fname, lname, salary);
    42.          employeeID = (Integer) session.save(employee); 
    43.          tx.commit();
    44.       }catch (HibernateException e) {
    45.          if (tx!=null) tx.rollback();
    46.          e.printStackTrace(); 
    47.       }finally {
    48.          session.close(); 
    49.       }
    50.       return employeeID;
    51.    }
    53.    /* Method to  READ all the employees using Scalar Query */
    54.    public void listEmployeesScalar( ){
    55.       Session session = factory.openSession();
    56.       Transaction tx = null;
    57.       try{
    58.          tx = session.beginTransaction();
    59.          String sql = "SELECT first_name, salary FROM EMPLOYEE";
    61.          query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
    63.          for(Object object : data)
    64.          {
    65.             Map row = (Map)object;
    66.             System.out.print("First Name: " + row.get("first_name")); 
    67.             System.out.println(", Salary: " + row.get("salary")); 
    68.          }
    69.          tx.commit();
    70.       }catch (HibernateException e) {
    71.          if (tx!=null) tx.rollback();
    72.          e.printStackTrace(); 
    73.       }finally {
    74.          session.close(); 
    75.       }
    76.    }
    78.    /* Method to  READ all the employees using Entity Query */
    79.    public void listEmployeesEntity( ){
    80.       Session session = factory.openSession();
    81.       Transaction tx = null;
    82.       try{
    83.          tx = session.beginTransaction();
    84.          String sql = "SELECT * FROM EMPLOYEE";
    85.          SQLQuery query = session.createSQLQuery(sql);
    86.          query.addEntity(Employee.class);
    87.          List employees = query.list();
    89.          for (Iterator iterator = 
    90.                            employees.iterator(); iterator.hasNext();){
    91.             Employee employee = (Employee) iterator.next(); 
    92.             System.out.print("First Name: " + employee.getFirstName()); 
    93.             System.out.print("  Last Name: " + employee.getLastName()); 
    94.             System.out.println("  Salary: " + employee.getSalary()); 
    95.          }
    96.          tx.commit();
    97.       }catch (HibernateException e) {
    98.          if (tx!=null) tx.rollback();
    99.          e.printStackTrace(); 
    100.       }finally {
    101.          session.close(); 
    102.       }
    103.    }
    104. }

    这是编译并运行上述应用程序的步骤。确保你有适当的 PATH 和 CLASSPATH，然后执行编译程序。

    • 按照在配置一章讲述的方法创建 hibernate.cfg.xml 配置文件。
    • 如上述所示创建 employee.hbm.xml 映射文件。
    • 如上述所示创建 employee.java 源文件并编译。
    • 如上述所示创建 manageemployee.java 源文件并编译。
    • 执行 manageemployee 二进制代码运行程序。

    你会得到下面的结果，并且记录将会在 EMPLOYEE 表创建。 

    1. $java ManageEmployee
    2. .......VARIOUS LOG MESSAGES WILL DISPLAY HERE........
    4. First Name: Zara, Salary: 2000
    5. First Name: Daisy, Salary: 5000
    6. First Name: John, Salary: 5000
    7. First Name: Mohd, Salary: 3000
    8. First Name: Zara  Last Name: Ali  Salary: 2000
    9. First Name: Daisy  Last Name: Das  Salary: 5000
    10. First Name: John  Last Name: Paul  Salary: 5000

    如果你检查你的 EMPLOYEE 表，它应该有以下记录：