Для того, чтобы формировать запросы для базы данных (далее – БД), при этом испольуя все возможности БД, мы можем использовать нативный SQL.
Таким обращом наше приложение создаст нативный SQL – запрос, испоьзуя метод createSQLQuery() интерфейса Session, который выглядит следующим образом:
public SQLQuery createSQLQuery (String sqlString) throws HibernateException;
После того, как мы передаём методу createSQLQuery() строку (String), содержащую SQL – запрос, мы можем связать результат этого запроса с сохраняемым объектом (persistent object).
Для понимания того, как это работате на практике, рассмотрим пример простого приложения.
Пример:
Исходный код проекта можно скачать по ЭТОЙ ССЫЛКЕ.
Шаг 1. Создадим таблицу в нашей БД
HIBRNATE_DEVELOPERS
CREATE TABLE HIBERNATE_DEVELOPERS (
id INT NOT NULL auto_increment,
FIRST_NAME VARCHAR(50) default NULL,
LAST_NAME VARCHAR(50) default NULL,
SPECIALTY VARCHAR(50) default NULL,
EXPERIENCE INT default NULL,
SALARY INT default NULL,
PRIMARY KEY (id)
);
Шаг 2. Создадим POJO – класс
Developer.java
package net.proselyte.hibernate.criteria;
public class Developer {
private int id;
private String firstName;
private String lastName;
private String specialty;
private int experience;
private int salary;
/**
* Default Constructor
*/
public Developer() {
}
/**
* Plain constructor
*/
public Developer(String firstName, String lastName, String specialty, int experience, int salary) {
this.firstName = firstName;
this.lastName = lastName;
this.specialty = specialty;
this.experience = experience;
this.salary = salary;
}
/**
* Getters and Setters
*/
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getSpecialty() {
return specialty;
}
public void setSpecialty(String specialty) {
this.specialty = specialty;
}
public int getExperience() {
return experience;
}
public void setExperience(int experience) {
this.experience = experience;
}
public int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
/**
* toString method (optional)
*/
@Override
public String toString() {
return "id: " + id +
"\nFirst Name: " + firstName +
"\nLast Name: " + lastName +
"\nSpecialty: " + specialty +
"\nExperience: " + experience +
"\nSalary: " + salary + "\n";
}
}
Шаг 3. Создаём конфигурационные файлы
hibernate.cfg.xml
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration SYSTEM
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.dialect">
org.hibernate.dialect.MySQLDialect
</property>
<property name="hibernate.connection.driver_class">
com.mysql.jdbc.Driver
</property>
<!-- Assume PROSELYTE_TUTORIAL is the database name -->
<property name="hibernate.connection.url">
jdbc:mysql://localhost/ИМЯ_ВАШЕЙ_БД
</property>
<property name="hibernate.connection.username">
ВАШЕ_ИМЯ_ПОЛЬЗОВАТЕЛЯ
</property>
<property name="hibernate.connection.password">
ВАШ_ПАРОЛЬ
</property>
<!-- List of XML mapping files -->
<mapping resource="Developer.hbm.xml"/>
</session-factory>
</hibernate-configuration>
Developer.hbm.xml
<?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="net.proselyte.hibernate.nativesql.Developer" table="HIBERNATE_DEVELOPERS">
<meta attribute="class-description">
This class contains developer details.
</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="specialty" column="SPECIALTY" type="string"/>
<property name="experience" column="EXPERIENCE" type="int"/>
<property name="salary" column="SALARY" type="int"/>
</class>
</hibernate-mapping>
Шаг 4. Создаём класс DeveloperRunner.java
DeveloperRunner.java
package net.proselyte.hibernate.nativesql;
import org.hibernate.*;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Projections;
import java.util.List;
import java.util.Map;
public class DeveloperRunner {
private static SessionFactory sessionFactory;
public static void main(String[] args) {
sessionFactory = new Configuration().configure().buildSessionFactory();
DeveloperRunner developerRunner = new DeveloperRunner();
System.out.println("Adding developer's records to the database...");
Integer developerId1 = developerRunner.addDeveloper("Proselyte", "Developer", "Java Developer", 3, 2000);
Integer developerId2 = developerRunner.addDeveloper("First", "Developer", "C++ Developer", 10, 5000);
Integer developerId3 = developerRunner.addDeveloper("Second", "Developer", "C# Developer", 5, 4000);
Integer developerId4 = developerRunner.addDeveloper("Third", "Developer", "PHP Developer", 1, 1000);
System.out.println("List of Developers using Entity Query:");
developerRunner.listDevelopers();
System.out.println("List of Developers using Scalar Query:");
developerRunner.listDevelopersScalar();
sessionFactory.close();
}
public Integer addDeveloper(String firstName, String lastName, String specialty, int experience, int salary) {
Session session = sessionFactory.openSession();
Transaction transaction = null;
Integer developerId = null;
transaction = session.beginTransaction();
Developer developer = new Developer(firstName, lastName, specialty, experience, salary);
developerId = (Integer) session.save(developer);
transaction.commit();
session.close();
return developerId;
}
public void listDevelopers() {
Session session = sessionFactory.openSession();
Transaction transaction = null;
transaction = session.beginTransaction();
SQLQuery sqlQuery = session.createSQLQuery("SELECT * FROM HIBERNATE_DEVELOPERS");
sqlQuery.addEntity(Developer.class);
List developers = sqlQuery.list();
for (Developer developer : developers) {
System.out.println("=======================");
System.out.println(developer);
System.out.println("=======================");
}
transaction.commit();
session.close();
}
public void listDevelopersScalar() {
Session session = sessionFactory.openSession();
Transaction transaction = null;
transaction = session.beginTransaction();
SQLQuery sqlQuery = session.createSQLQuery("SELECT * FROM HIBERNATE_DEVELOPERS");
sqlQuery.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List developers = sqlQuery.list();
for (Object developer : developers) {
Map row = (Map) developer;
System.out.println("=======================");
System.out.println("id: " + row.get("id"));
System.out.println("First Name: " + row.get("FIRST_NAME"));
System.out.println("Last Name: " + row.get("LAST_NAME"));
System.out.println("Specialty: " + row.get("SPECIALTY"));
System.out.println("Experience: " + row.get("EXPERIENCE"));
System.out.println("Salary: " + row.get("SALARY"));
System.out.println("=======================");
}
transaction.commit();
session.close();
}
public void totalSalary() {
Session session = sessionFactory.openSession();
Transaction transaction = null;
transaction = session.beginTransaction();
Criteria criteria = session.createCriteria(Developer.class);
criteria.setProjection(Projections.sum("salary"));
List totalSalary = criteria.list();
System.out.println("Total salary of all developers: " + totalSalary.get(0));
transaction.commit();
session.close();
}
}
Если всё было сделано правильно, в результате работы программы мы получим, примерно, следующий результат:
/usr/lib/jvm/java-8-oracle/bin/java -Didea.launcher.port=7541 -Didea.launcher.bin.path=/home/proselyte/Programming/Soft/IntellijIdea/bin -Dfile.encoding=UTF-8 -classpath /usr/lib/jvm/java-8-oracle/jre/lib/management-agent.jar:/usr/lib/jvm/java-8-oracle/jre/lib/plugin.jar:/usr/lib/jvm/java-8-oracle/jre/lib/rt.jar:/usr/lib/jvm/java-8-oracle/jre/lib/jsse.jar:/usr/lib/jvm/java-8-oracle/jre/lib/charsets.jar:/usr/lib/jvm/java-8-oracle/jre/lib/jce.jar:/usr/lib/jvm/java-8-oracle/jre/lib/resources.jar:/usr/lib/jvm/java-8-oracle/jre/lib/deploy.jar:/usr/lib/jvm/java-8-oracle/jre/lib/jfxswt.jar:/usr/lib/jvm/java-8-oracle/jre/lib/javaws.jar:/usr/lib/jvm/java-8-oracle/jre/lib/jfr.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/dnsns.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/sunpkcs11.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/sunec.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/sunjce_provider.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/jaccess.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/nashorn.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/localedata.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/zipfs.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/cldrdata.jar:/usr/lib/jvm/java-8-oracle/jre/lib/ext/jfxrt.jar:/home/proselyte/Programming/IdeaProjects/ProselyteTutorials/Hibernate/target/classes:/home/proselyte/.m2/repository/org/springframework/spring-core/4.1.1.RELEASE/spring-core-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/commons-logging/commons-logging/1.1.3/commons-logging-1.1.3.jar:/home/proselyte/.m2/repository/org/springframework/spring-web/4.1.1.RELEASE/spring-web-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/spring-aop/4.1.1.RELEASE/spring-aop-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/aopalliance/aopalliance/1.0/aopalliance-1.0.jar:/home/proselyte/.m2/repository/org/springframework/spring-beans/4.1.1.RELEASE/spring-beans-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/spring-context/4.1.1.RELEASE/spring-context-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/javax/servlet/servlet-api/2.5/servlet-api-2.5.jar:/home/proselyte/.m2/repository/org/springframework/spring-webmvc/4.1.1.RELEASE/spring-webmvc-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/spring-expression/4.1.1.RELEASE/spring-expression-4.1.1.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/integration/spring-integration-file/4.2.1.RELEASE/spring-integration-file-4.2.1.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/integration/spring-integration-core/4.2.1.RELEASE/spring-integration-core-4.2.1.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/spring-messaging/4.2.2.RELEASE/spring-messaging-4.2.2.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/retry/spring-retry/1.1.2.RELEASE/spring-retry-1.1.2.RELEASE.jar:/home/proselyte/.m2/repository/org/springframework/spring-tx/4.2.2.RELEASE/spring-tx-4.2.2.RELEASE.jar:/home/proselyte/.m2/repository/commons-io/commons-io/2.4/commons-io-2.4.jar:/home/proselyte/.m2/repository/org/hibernate/hibernate-core/5.1.0.Final/hibernate-core-5.1.0.Final.jar:/home/proselyte/.m2/repository/org/jboss/logging/jboss-logging/3.3.0.Final/jboss-logging-3.3.0.Final.jar:/home/proselyte/.m2/repository/org/hibernate/javax/persistence/hibernate-jpa-2.1-api/1.0.0.Final/hibernate-jpa-2.1-api-1.0.0.Final.jar:/home/proselyte/.m2/repository/org/javassist/javassist/3.20.0-GA/javassist-3.20.0-GA.jar:/home/proselyte/.m2/repository/antlr/antlr/2.7.7/antlr-2.7.7.jar:/home/proselyte/.m2/repository/org/apache/geronimo/specs/geronimo-jta_1.1_spec/1.1.1/geronimo-jta_1.1_spec-1.1.1.jar:/home/proselyte/.m2/repository/org/jboss/jandex/2.0.0.Final/jandex-2.0.0.Final.jar:/home/proselyte/.m2/repository/com/fasterxml/classmate/1.3.0/classmate-1.3.0.jar:/home/proselyte/.m2/repository/dom4j/dom4j/1.6.1/dom4j-1.6.1.jar:/home/proselyte/.m2/repository/xml-apis/xml-apis/1.0.b2/xml-apis-1.0.b2.jar:/home/proselyte/.m2/repository/org/hibernate/common/hibernate-commons-annotations/5.0.1.Final/hibernate-commons-annotations-5.0.1.Final.jar:/home/proselyte/.m2/repository/javassist/javassist/3.12.1.GA/javassist-3.12.1.GA.jar:/home/proselyte/.m2/repository/mysql/mysql-connector-java/5.1.38/mysql-connector-java-5.1.38.jar:/home/proselyte/Programming/Soft/IntellijIdea/lib/idea_rt.jar com.intellij.rt.execution.application.AppMain net.proselyte.hibernate.nativesql.DeveloperRunner
Feb 23, 2016 9:44:03 PM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {5.1.0.Final}
Feb 23, 2016 9:44:03 PM org.hibernate.cfg.Environment
INFO: HHH000206: hibernate.properties not found
Feb 23, 2016 9:44:03 PM org.hibernate.cfg.Environment buildBytecodeProvider
INFO: HHH000021: Bytecode provider name : javassist
Feb 23, 2016 9:44:04 PM org.hibernate.annotations.common.reflection.java.JavaReflectionManager
INFO: HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
Feb 23, 2016 9:44:05 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH10001002: Using Hibernate built-in connection pool (not for production use!)
Feb 23, 2016 9:44:05 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001005: using driver [com.mysql.jdbc.Driver] at URL [jdbc:mysql://localhost/PROSELYTE_TUTORIAL]
Feb 23, 2016 9:44:05 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001001: Connection properties: {user=root, password=****}
Feb 23, 2016 9:44:05 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001003: Autocommit mode: false
Feb 23, 2016 9:44:05 PM org.hibernate.engine.jdbc.connections.internal.PooledConnections
INFO: HHH000115: Hibernate connection pool size: 20 (min=1)
Tue Feb 23 21:44:05 EET 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Feb 23, 2016 9:44:05 PM org.hibernate.dialect.Dialect
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect
Adding developer's records to the database...
List of Developers using Entity Query:
=======================
id: 69
First Name: Proselyte
Last Name: Developer
Specialty: Java Developer
Experience: 3
Salary: 2000
=======================
=======================
id: 70
First Name: First
Last Name: Developer
Specialty: C++ Developer
Experience: 10
Salary: 5000
=======================
=======================
id: 71
First Name: Second
Last Name: Developer
Specialty: C# Developer
Experience: 5
Salary: 4000
=======================
=======================
id: 72
First Name: Third
Last Name: Developer
Specialty: PHP Developer
Experience: 1
Salary: 1000
=======================
List of Developers using Scalar Query:
=======================
id: 69
First Name: Proselyte
Last Name: Developer
Specialty: Java Developer
Experience: 3
Salary: 2000
=======================
=======================
id: 70
First Name: First
Last Name: Developer
Specialty: C++ Developer
Experience: 10
Salary: 5000
=======================
=======================
id: 71
First Name: Second
Last Name: Developer
Specialty: C# Developer
Experience: 5
Salary: 4000
=======================
=======================
id: 72
First Name: Third
Last Name: Developer
Specialty: PHP Developer
Experience: 1
Salary: 1000
=======================
Feb 23, 2016 9:44:06 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl stop
INFO: HHH10001008: Cleaning up connection pool [jdbc:mysql://localhost/PROSELYTE_TUTORIAL]
Наша таблица HIBERNATE_DEVELOPERS будет иметь такой вид:
+----+------------+-----------+----------------+------------+--------+
| id | FIRST_NAME | LAST_NAME | SPECIALTY | EXPERIENCE | SALARY |
+----+------------+-----------+----------------+------------+--------+
| 65 | Proselyte | Developer | Java Developer | 3 | 2000 |
| 66 | First | Developer | C++ Developer | 10 | 5000 |
| 67 | Second | Developer | C# Developer | 5 | 4000 |
| 68 | Third | Developer | PHP Developer | 1 | 1000 |
+----+------------+-----------+----------------+------------+--------+
В этой статье мы изучили основы использования нативного SQL и рассмотрели пример простого приложения.