11、JPA-JPQL

/**
 * JPQL，Java Persistence Query Language 的简称。是一种和 SQL 类似的中间性和对象化查询语言，它最终会被编译成针对不同底层数据库的 SQL 查询，从而屏蔽不同数据库的差异。
 * JPQL 语言的语句可以是 select 语句、update 语句或delete语句，它们都通过 Query 接口封装执行
 * Query接口封装了执行数据库查询的相关方法。调用 EntityManager 的 createQuery、create NamedQuery 及 createNativeQuery 方法可以获得查询对象，进而调用 Query 接口的相关方法来执行查询操作
 *
 * int executeUpdate()
 * 用于执行update或delete语句
 *
 * List getResultList()
 * 用于执行select语句并返回结果集实体列表
 *
 * Object getSingleResult()
 * 用于执行只返回单个结果实体的select语句
 *
 * Query setFirstResult(int startPosition)
 * 用于设置从哪个实体记录开始返回查询结果
 *
 * Query setMaxResults(int maxResult)
 * 用于设置返回结果实体的最大数。与setFirstResult结合使用可实现分页查询
 *
 * Query setFlushMode(FlushModeType flushMode)
 * 设置查询对象的Flush模式。参数可以取2个枚举值：FlushModeType.AUTO 为自动更新数据库记录，FlushMode Type.COMMIT 为直到提交事务时才更新数据库记录
 *
 * setHint(String hintName, Object value)
 * 设置与查询对象相关的特定供应商参数或提示信息。参数名及其取值需要参考特定 JPA 实现库提供商的文档。如果第二个参数无效将抛出IllegalArgumentException异常
 *
 * setParameter(int position, Object value)
 * 为查询语句的指定位置参数赋值。Position 指定参数序号，value 为赋给参数的值
 *
 * setParameter(int position, Date d, TemporalType type)
 * 为查询语句的指定位置参数赋 Date 值。Position 指定参数序号，value 为赋给参数的值，temporalType 取 TemporalType 的枚举常量，包括 DATE、TIME 及 TIMESTAMP 三个，用于将 Java 的 Date 型值临时转换为数据库支持的日期时间类型（java.sql.Date、java.sql.Time及java.sql.Timestamp）
 *
 * setParameter(int position, Calendar c, TemporalType type)
 * 为查询语句的指定位置参数赋 Calenda r值。position 指定参数序号，value 为赋给参数的值，temporalType 的含义及取舍同前
 *
 * setParameter(String name, Object value)
 * 为查询语句的指定名称参数赋值
 *
 * setParameter(String name, Date d, TemporalType type)
 * 为查询语句的指定名称参数赋 Date 值。用法同前
 *
 * setParameter(String name, Calendar c, TemporalType type)
 * 为查询语句的指定名称参数设置Calendar值。name为参数名，其它同前。该方法调用时如果参数位置或参数名不正确，或者所赋的参数值类型不匹配，将抛出 IllegalArgumentException 异常
 */

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com</groupId>
    <artifactId>jpa</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.14</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.4.1.Final</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-jpamodelgen</artifactId>
            <version>5.4.1.Final</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-ehcache</artifactId>
            <version>5.4.1.Final</version>
        </dependency>
        <dependency>
            <groupId>org.ehcache</groupId>
            <artifactId>ehcache</artifactId>
            <version>3.6.3</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-nop</artifactId>
            <version>1.7.25</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <!-- 指定jdk -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

persistence.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd"
             version="2.2">
    <persistence-unit name="jpaname" transaction-type="RESOURCE_LOCAL">

        <!-- 配置使用什么 ORM 产品来作为 JPA 的实现 -->
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>

        <!-- 实体类 -->
        <class>com.jpa.yingshe.Customer</class>

        <!--
        配置二级缓存的策略
        ALL：所有的实体类都被缓存
        NONE：所有的实体类都不被缓存.
        ENABLE_SELECTIVE：标识 @Cacheable(true) 注解的实体类将被缓存
        DISABLE_SELECTIVE：缓存除标识 @Cacheable(false) 以外的所有实体类
        UNSPECIFIED：默认值，JPA 产品默认值将被使用
        -->
        <shared-cache-mode>ENABLE_SELECTIVE</shared-cache-mode>

        <properties>
            <!-- 数据库信息 -->
            <property name="javax.persistence.jdbc.driver" value="com.mysql.cj.jdbc.Driver"/>
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://xxx.xxx.xxx.xxx/jpa?useSSL=false"/>
            <property name="javax.persistence.jdbc.user" value="xxx"/>
            <property name="javax.persistence.jdbc.password" value="xxx"/>

            <!-- 配置 hibernate 属性 -->
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.hbm2ddl.auto" value="update"/>

            <!-- 二级缓存相关 -->
            <property name="hibernate.cache.use_second_level_cache" value="true"/>
            <property name="hibernate.cache.region.factory_class" value="org.hibernate.cache.ehcache.internal.EhcacheRegionFactory"/>
            <property name="hibernate.cache.use_query_cache" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

ehcache.xml

<ehcache>
    <diskStore path="./target/tmp"/>

    <defaultCache
        maxElementsInMemory="10000"
        eternal="false"
        timeToIdleSeconds="120"
        timeToLiveSeconds="120"
        overflowToDisk="true"
        />

    <cache name="sampleCache1"
        maxElementsInMemory="10000"
        eternal="false"
        timeToIdleSeconds="300"
        timeToLiveSeconds="600"
        overflowToDisk="true"
        />

    <cache name="sampleCache2"
        maxElementsInMemory="1000"
        eternal="true"
        timeToIdleSeconds="0"
        timeToLiveSeconds="0"
        overflowToDisk="false"
        /> -->
</ehcache>

实体类

Customer

package com.jpa.yingshe;

import javax.persistence.*;

@Cacheable
@Table(name = "JPA_CUTOMERS")
@Entity
public class Customer {

    private Integer id;
    private String lastName;

    private String email;

    public Customer() {
    }

    public Customer(String lastName) {
        this.lastName = lastName;
    }

    @GeneratedValue(strategy = GenerationType.AUTO)
    @Id
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @Column(name = "LAST_NAME", length = 50, nullable = false)
    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

测试

添加几条数据

package jpa.test;

import com.jpa.yingshe.Customer;
import org.hibernate.jpa.QueryHints;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import javax.persistence.*;
import java.util.List;

public class JPAJPQLTest {
    private EntityManagerFactory entityManagerFactory;
    private EntityManager entityManager;
    private EntityTransaction transaction;

    @Before
    public void init() {
        entityManagerFactory = Persistence.createEntityManagerFactory("jpaname");
        entityManager = entityManagerFactory.createEntityManager();
        transaction = entityManager.getTransaction();
        transaction.begin();
    }

    @After
    public void destroy() {
        transaction.commit();
        entityManager.close();
        entityManagerFactory.close();
    }

    @Test
    public void testPersist() {
        Customer c1 = new Customer();
        c1.setEmail("vfdgsdfs");
        c1.setLastName("131waw");

        entityManager.persist(c1);
    }
}

单个查询

@Test
public void testHelloJPQL() {
    String jpql = "FROM Customer c WHERE c.id >= ?0";
    Query query = entityManager.createQuery(jpql);

    query.setParameter(0, 1);
    List<Customer> customers = query.getResultList();
    System.out.println(customers.size());
}

部分属性查询

// 若只查询部分属性, 则将返回 Object[] 类型的结果. 或者 Object[] 类型的 List
// 也可以在实体类中创建对应的构造器, 然后再 JPQL 语句中利用对应的构造器返回实体类的对象
@Test
public void testPartlyProperties() {
    String jpql = "SELECT new Customer(c.lastName) FROM Customer c WHERE c.id > ?0";
    List result = entityManager.createQuery(jpql).setParameter(0, 40).getResultList();

    System.out.println(result.size());
    System.out.println(result.get(0));
}

标记查询 @NamedQuery

@NamedQuery(name="testNamedQuery", query="FROM Customer c WHERE c.id = ?0")
@Cacheable
@Table(name = "JPA_CUTOMERS")
@Entity
public class Customer {

    private Integer id;

// createNamedQuery 适用于在实体类前使用 @NamedQuery 标记的查询语句
@Test
public void testNamedQuery() {
    Query query = entityManager.createNamedQuery("testNamedQuery").setParameter(0, 1);
    Customer customer = (Customer) query.getSingleResult();

    System.out.println(customer);
}

本地 SQL 查询

// createNativeQuery 适用于本地 SQL
@Test
public void testNativeQuery() {
    String sql = "SELECT email FROM JPA_CUTOMERS WHERE id = ?1";
    Query query = entityManager.createNativeQuery(sql).setParameter(1, 1);

    Object result = query.getSingleResult();
    System.out.println(result);
}

缓存查询

// 不使用 hibernate 的查询缓存
@Test
public void testQueryCache() {
    String jpql = "FROM Customer c WHERE c.id > ?1";
    Query query = entityManager.createQuery(jpql);

    query.setParameter(1, 1);
    List<Customer> customers = query.getResultList();
    System.out.println(customers.size());

    query = entityManager.createQuery(jpql);

    query.setParameter(1, 1);
    customers = query.getResultList();
    System.out.println(customers.size());
}

开启缓存后查询，10、JPA-二级缓存

// 使用 hibernate 的查询缓存，setHint(QueryHints.HINT_CACHEABLE, true)
@Test
public void testQueryCache() {
    String jpql = "FROM Customer c WHERE c.id > ?1";
    Query query = entityManager.createQuery(jpql).setHint(QueryHints.HINT_CACHEABLE, true);

    query.setParameter(1, 1);
    List<Customer> customers = query.getResultList();
    System.out.println(customers.size());

    query = entityManager.createQuery(jpql).setHint(QueryHints.HINT_CACHEABLE, true);

    query.setParameter(1, 1);
    customers = query.getResultList();
    System.out.println(customers.size());
}

OrderBy

@Test
public void testOrderBy() {
    String jpql = "FROM Customer c WHERE c.id > ?1 ORDER BY c.lastName DESC";
    Query query = entityManager.createQuery(jpql).setHint(QueryHints.HINT_CACHEABLE, true);

    query.setParameter(1, 1);
    List<Customer> customers = query.getResultList();
    System.out.println(customers.size());
}

GroupBy

//查询 order 数量大于 2 的那些 Customer
@Test
public void testGroupBy() {
    String jpql = "SELECT c.email FROM Customer c GROUP BY c.email HAVING count(c.id) >= 2";
    List<Customer> customers = entityManager.createQuery(jpql).getResultList();

    System.out.println(customers);
}

关联查询

修改实体类 Customer

private Set<Order> orders;

//使用 @OneToMany 来映射 1-n 的关联关系//注意: 若在 1 的一端的 @OneToMany 中使用 mappedBy 属性, 则 @OneToMany 端就不能再使用 @JoinColumn 属性了
@OneToMany(mappedBy = "customer")
public Set<Order> getOrders() {
    return orders;
}

public void setOrders(Set<Order> orders) {
    this.orders = orders;
}

添加实体类 Order

package com.jpa.yingshe;

import javax.persistence.*;

@Table(name = "JPA_ORDERS")
@Entity
public class Order {

    private Integer id;
    private String orderName;

    private Customer customer;

    @GeneratedValue
    @Id
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    @Column(name = "ORDER_NAME")
    public String getOrderName() {
        return orderName;
    }

    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }

    // 映射单向 n-1 的关联关系
    // 使用 @ManyToOne 来映射多对一的关联关系
    // 使用 @JoinColumn 来映射外键
    @JoinColumn(name = "CUSTOMER_ID")
    @ManyToOne()
    public Customer getCustomer() {
        return customer;
    }

    public void setCustomer(Customer customer) {
        this.customer = customer;
    }
}

JPQL插入中文数据乱码，修改下连接SQL的语句，xml中一些特殊字符需转义

persistence.xml

<!-- &amp; & 和 -->
<!-- &lt; < 小于号 -->
<!-- &gt; > 大于号 -->
<!-- &apos; ' 单引号 -->
<!-- &quot; " 双引号 -->
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://xxx.xxx.xxx.xxx/jpa?useSSL=false&amp;characterEncoding=utf-8"/>

添加一些数据

@Test
public void testPersist() {
    Customer c1 = new Customer();
    c1.setEmail("cbcv");
    c1.setLastName("ert");

    Order order = new Order();
    order.setCustomer(c1);
    order.setOrderName("cbcv");

    Order order1 = new Order();
    order1.setCustomer(c1);
    order1.setOrderName("34543");

    Order order2 = new Order();
    order2.setCustomer(c1);
    order2.setOrderName("mbndr");

    entityManager.persist(c1);

    entityManager.persist(order);
    entityManager.persist(order1);
    entityManager.persist(order2);
}

先看不使用关联时的查询

@Test
public void testLeftOuterJoinFetch(){
    String jpql = "FROM Customer c WHERE c.id = ?1";

    Customer customer = (Customer) entityManager.createQuery(jpql).setParameter(1, 14).getSingleResult();
    System.out.println(customer.getLastName());
    System.out.println(customer.getOrders().size());
}

使用关联查询

// JPQL 的关联查询同 HQL 的关联查询
@Test
public void testLeftOuterJoinFetch(){
    String jpql = "FROM Customer c LEFT OUTER JOIN FETCH c.orders WHERE c.id = ?1";

    Customer customer = (Customer) entityManager.createQuery(jpql).setParameter(1, 14).getSingleResult();
    System.out.println(customer.getLastName());
    System.out.println(customer.getOrders().size());
}

不使用 FETCH 的关联查询会返回一个 Object 集合

@Test
public void testLeftOuterJoinFetch(){
    String jpql = "FROM Customer c LEFT OUTER JOIN c.orders WHERE c.id = ?1";

    List<Object[]> result = entityManager.createQuery(jpql).setParameter(1, 14).getResultList();
    System.out.println(result);
}

子查询

// JPQL 子查询
@Test
public void testSubQuery(){
    //查询所有 Customer 的 lastName 为 ert 的 Order
    String jpql = "SELECT o FROM Order o WHERE o.customer = (SELECT c FROM Customer c WHERE c.lastName = ?1)";

    Query query = entityManager.createQuery(jpql).setParameter(1, "ert");
    List<Order> orders = query.getResultList();
    System.out.println(orders.size());
}

内建函数

//JPQL 内建函数
@Test
public void testJpqlFunction(){
    String jpql = "SELECT upper(c.email) FROM Customer c";

    List<String> emails = entityManager.createQuery(jpql).getResultList();
    System.out.println(emails);
}

/**
 * JPQL 提供了一些内建函数，包括字符串处理函数、算术函数和日期函数。
 *
 * 常用字符串处理函数：
 * concat(String s1, String s2)：字符串合并/连接函数。
 * substring(String s, int start, int length)：取字串函数。
 * trim([leading|trailing|both,] [char c,] String s)：从字符串中去掉首/尾指定的字符或空格。
 * lower(String s)：将字符串转换成小写形式。
 * upper(String s)：将字符串转换成大写形式。
 * length(String s)：求字符串的长度。
 * locate(String s1, String s2[, int start])：从第一个字符串中查找第二个字符串(子串)出现的位置。若未找到则返回0。
 *
 * 常用算术函数有：
 * abs、mod、sqrt、size 等。Size 用于求集合的元素个数。
 *
 * 日期函数主要有三个：
 * current_date、current_time、current_timestamp，它们不需要参数，返回服务器上的当前日期、时间和时戳。
 */

UPDATE 和 DELETE

@Test
public void testExecuteUpdate(){
    String jpql = "UPDATE Customer c SET c.lastName = ?1 WHERE c.id = ?2";
    Query query = entityManager.createQuery(jpql).setParameter(1, "YYY").setParameter(2, 18);

    query.executeUpdate();
}

// 有主外键要先删除外键
@Test
public void testExecuteDelete(){
    String jpql = "DELETE Order o WHERE o.id = ?2";
    Query query = entityManager.createQuery(jpql).setParameter(2, 21);

    query.executeUpdate();
}

---

http://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#hql

https://www.jianshu.com/p/4a4410075bab