avatar
文章
92
标签
37
分类
14
首页
时间轴
标签
分类
郝永亮的主页
首页
时间轴
标签
分类
SpringBoot2.X集成SpringDataJPA(二)
|框架
阅读量:

Spring-Data-JPA 概述

基于JPA再封装的一套框架，底层还是使用第三方ORM框架，默认使用Hibernate

环境搭建

创建项目

引入依赖[配置pom.xml]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
<?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>cn.itcast</groupId>
    <artifactId>jpa-day2</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <spring.version>5.0.2.RELEASE</spring.version>
        <hibernate.version>5.0.7.Final</hibernate.version>
        <slf4j.version>1.6.6</slf4j.version>
        <log4j.version>1.2.12</log4j.version>
        <c3p0.version>0.9.1.2</c3p0.version>
        <mysql.version>5.1.6</mysql.version>
    </properties>

    <dependencies>
        <!-- junit单元测试 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <!-- spring beg -->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.6.8</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!-- spring对orm框架的支持包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!-- spring end -->

        <!-- hibernate beg -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate.version}</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>${hibernate.version}</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>5.2.1.Final</version>
        </dependency>
        <!-- hibernate end -->

        <!-- c3p0 beg -->
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>${c3p0.version}</version>
        </dependency>
        <!-- c3p0 end -->

        <!-- log end -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>${log4j.version}</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>${slf4j.version}</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>${slf4j.version}</version>
        </dependency>
        <!-- log end -->


        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>

        <!-- spring data jpa 的坐标-->
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-jpa</artifactId>
            <version>1.9.0.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
        </dependency>

        <!-- el beg 使用spring data jpa 必须引入 -->
        <dependency>
            <groupId>javax.el</groupId>
            <artifactId>javax.el-api</artifactId>
            <version>2.2.4</version>
        </dependency>

        <dependency>
            <groupId>org.glassfish.web</groupId>
            <artifactId>javax.el</artifactId>
            <version>2.2.4</version>
        </dependency>
        <!-- el end -->
    </dependencies>

</project>

创建包

cn.itcast.service

cn.itcast.dao

cn.itcast.domain

创建applicationContext.xml

Spring整合Jpa就两步

​ 1.由spring创建entityManagerFactory

​ 2.使用标签jpa:repositories和spring进行整合

提示：该文件应该在java/main/resource目录下

注意修改数据库密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:jpa="http://www.springframework.org/schema/data/jpa" xmlns:task="http://www.springframework.org/schema/task"
       xsi:schemaLocation="
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
		http://www.springframework.org/schema/data/jpa
		http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">

    <!--S Spring配置[之前课程里的旧东西]-->
    <!--配置扫描包-->
    <context:component-scan base-package="cn.itcast" ></context:component-scan>
    <!--创建数据库连接池 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="user" value="root"></property>
        <property name="password" value="root"></property>
        <property name="jdbcUrl" value="jdbc:mysql:///jpa" ></property>
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
    </bean>
    <!--配置事务管理器-->
    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <!--这里不需要配置
			<property name="dataSource" ref="dataSource" />
			因为数据源已经配置在entityManagerFactory中了
		-->
        <property name="entityManagerFactory" ref="entityManagerFactoty"></property>
    </bean>

    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="save*" propagation="REQUIRED"/>
            <tx:method name="insert*" propagation="REQUIRED"/>
            <tx:method name="update*" propagation="REQUIRED"/>
            <tx:method name="delete*" propagation="REQUIRED"/>
            <tx:method name="get*" read-only="true"/>
            <tx:method name="find*" read-only="true"/>
            <tx:method name="*" propagation="REQUIRED"/>
        </tx:attributes>
    </tx:advice>

    <aop:config>
        <aop:pointcut id="pointcut" expression="execution(* cn.itcast.service.*.*(..))" />
        <aop:advisor advice-ref="txAdvice" pointcut-ref="pointcut" />
    </aop:config>
    <!--E Spring配置[之前课程里的旧东西]-->







    <!--S Spring整合JPA[今天讲的新配置]-->
    <!-- 1.创建entityManagerFactory对象交给spring容器管理-->
    <bean id="entityManagerFactoty" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <!--配置的扫描的包（实体类所在的包） -->
        <property name="packagesToScan" value="cn.itcast.domain" />
        <!-- jpa的实现厂家 -->
        <property name="persistenceProvider">
            <bean class="org.hibernate.jpa.HibernatePersistenceProvider"/>
        </property>

        <!--jpa的供应商适配器 -->
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <!--配置是否自动创建数据库表 -->
                <property name="generateDdl" value="false" />
                <!--指定数据库类型 -->
                <property name="database" value="MYSQL" />
                <!--数据库方言：支持的特有语法 -->
                <property name="databasePlatform" value="org.hibernate.dialect.MySQLDialect" />
                <!--是否显示sql -->
                <property name="showSql" value="true" />
            </bean>
        </property>

        <!--jpa的方言 ：高级的特性 -->
        <property name="jpaDialect" >
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
        </property>
        <property name="jpaProperties" >
            <props>
                <prop key="hibernate.hbm2ddl.auto">update</prop>
            </props>
        </property>
    </bean>

    <!--2.整合spring dataJpa-->
    <jpa:repositories base-package="cn.itcast.dao" transaction-manager-ref="transactionManager"
                      entity-manager-factory-ref="entityManagerFactoty" ></jpa:repositories>
    <!--E Spring整合JPA[今天讲的新配置]-->
</beans>

快速入门

创建表cst_customer

提示：day01已经创建过了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE `jpa`;
DROP TABLE IF EXISTS `cst_customer`;

CREATE TABLE `cst_customer` (
  `cust_id` bigint(32) NOT NULL AUTO_INCREMENT COMMENT '客户编号(主键)',
  `cust_name` varchar(32) NOT NULL COMMENT '客户名称(公司名称)',
  `cust_source` varchar(32) DEFAULT NULL COMMENT '客户信息来源',
  `cust_industry` varchar(32) DEFAULT NULL COMMENT '客户所属行业',
  `cust_level` varchar(32) DEFAULT NULL COMMENT '客户级别',
  `cust_address` varchar(128) DEFAULT NULL COMMENT '客户联系地址',
  `cust_phone` varchar(64) DEFAULT NULL COMMENT '客户联系电话',
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `cst_customer`(`cust_id`,`cust_name`,`cust_source`,`cust_industry`,`cust_level`,`cust_address`,`cust_phone`) values (1,'黑马程序员',NULL,'教育',NULL,NULL,NULL),(2,'传智播客',NULL,'教育',NULL,NULL,NULL),(3,'酷丁鱼',NULL,'教育',NULL,NULL,NULL),(4,'传智汇',NULL,'教育',NULL,NULL,NULL),(5,'传智专修学院',NULL,'教育',NULL,NULL,NULL),(6,'谷歌',NULL,'教育',NULL,NULL,NULL);

创建实体类Customer

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
package cn.itcast.domain;
import javax.persistence.*;

public class Customer {
    private Long custId; //客户的主键
    private String custName;//客户名称
    private String custSource;//客户来源
    private String custLevel;//客户级别
    private String custIndustry;//客户所属行业
    private String custPhone;//客户的联系方式
    private String custAddress;//客户地址
    public Long getCustId() {
        return custId;
    }
    public void setCustId(Long custId) {
        this.custId = custId;
    }
    public String getCustName() {
        return custName;
    }
    public void setCustName(String custName) {
        this.custName = custName;
    }
    public String getCustSource() {
        return custSource;
    }
    public void setCustSource(String custSource) {
        this.custSource = custSource;
    }
    public String getCustLevel() {
        return custLevel;
    }
    public void setCustLevel(String custLevel) {
        this.custLevel = custLevel;
    }
    public String getCustIndustry() {
        return custIndustry;
    }
    public void setCustIndustry(String custIndustry) {
        this.custIndustry = custIndustry;
    }

    public String getCustPhone() {
        return custPhone;
    }

    public void setCustPhone(String custPhone) {
        this.custPhone = custPhone;
    }

    public String getCustAddress() {
        return custAddress;
    }

    public void setCustAddress(String custAddress) {
        this.custAddress = custAddress;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "custId=" + custId +
                ", custName='" + custName + '\'' +
                ", custSource='" + custSource + '\'' +
                ", custLevel='" + custLevel + '\'' +
                ", custIndustry='" + custIndustry + '\'' +
                ", custPhone='" + custPhone + '\'' +
                ", custAddress='" + custAddress + '\'' +
                '}';
    }
}

通过注解配置映射关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
package cn.itcast.domain;

import javax.persistence.*;

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

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "cust_id")
    private Long custId; //客户的主键

    @Column(name = "cust_name")
    private String custName;//客户名称

    @Column(name="cust_source")
    private String custSource;//客户来源

    @Column(name="cust_level")
    private String custLevel;//客户级别

    @Column(name="cust_industry")
    private String custIndustry;//客户所属行业

    @Column(name="cust_phone")
    private String custPhone;//客户的联系方式

    @Column(name="cust_address")
    private String custAddress;//客户地址

    public Long getCustId() {
        return custId;
    }

    public void setCustId(Long custId) {
        this.custId = custId;
    }

    public String getCustName() {
        return custName;
    }

    public void setCustName(String custName) {
        this.custName = custName;
    }

    public String getCustSource() {
        return custSource;
    }

    public void setCustSource(String custSource) {
        this.custSource = custSource;
    }

    public String getCustLevel() {
        return custLevel;
    }

    public void setCustLevel(String custLevel) {
        this.custLevel = custLevel;
    }

    public String getCustIndustry() {
        return custIndustry;
    }

    public void setCustIndustry(String custIndustry) {
        this.custIndustry = custIndustry;
    }

    public String getCustPhone() {
        return custPhone;
    }

    public void setCustPhone(String custPhone) {
        this.custPhone = custPhone;
    }

    public String getCustAddress() {
        return custAddress;
    }

    public void setCustAddress(String custAddress) {
        this.custAddress = custAddress;
    }

    @Override
    public String toString() {
        return "Customer{" +
                "custId=" + custId +
                ", custName='" + custName + '\'' +
                ", custSource='" + custSource + '\'' +
                ", custLevel='" + custLevel + '\'' +
                ", custIndustry='" + custIndustry + '\'' +
                ", custPhone='" + custPhone + '\'' +
                ", custAddress='" + custAddress + '\'' +
                '}';
    }
}

创建CustomerDao接口

1
2
3
4
5
6
7
8
9
10
11
12
13
package cn.itcast.dao;

import cn.itcast.domain.Customer;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

/**
 *  JpaRepository<操作的实体类, 实体类中主键的类型> 接口中提供了简单操作数据库的方法
 *  JpaSpecificationExecutorr<操作的是实体类>接口中提供了复杂方法，比如多条件和分页查询
 */
public interface CustomerDao extends JpaRepository<Customer,Long>,
													JpaSpecificationExecutor<Customer>{
}

编写测试类对接口进行测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
package cn.itcast.dao;

import cn.itcast.domain.Customer;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

/**
 * @RunWith注解： 就是一个运行器，SpringJUnit4ClassRunner.class意思是让测试运行于spring环境
 * @ContextConfiguration注解： Spring整合junit4时需要引入的spring的配置文件
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")

public class CustomerTest {
    @Autowired
    private CustomerDao customerDao;

    @Test
    public void testFindOne(){
        Customer customer = customerDao.findOne(1L);
        System.out.println(customer);
    }
}

运行结果

可能出现的问题

问题1：javax.xml.bind.JAXBException

原因是使用的JDK1.9需要引入依赖

1
2
3
4
5
<dependency>
    <groupId>javax.xml.bind</groupId>
    <artifactId>jaxb-api</artifactId>
    <version>2.3.0</version>
</dependency>

问题2：org.springframework.transaction.CannotCreateTransactionException

可能是applicationContext.xml中数据库账号密码配置错了

基本操作

增加

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Test
public void testAdd(){
    /*
    注意：不需要设置ID，是添加，如果设置了ID，则是更新，所以能设置主键
     */
    Customer customer = new Customer();
    customer.setCustName("李四");//设置顾客姓名
    customer.setCustAddress("山西太原");//设置顾客的住址
    customer.setCustPhone("1390000000");//设置顾客的电话

    //添加成功后会返回添加进去的那个对象，所以当前对象中包含ID
    Customer cust = customerDao.save(customer);
    System.out.println(cust);
}

运行结果

删除

1
2
3
4
@Test
public void testDelete(){
    customerDao.delete(9L);
}

运行结果

修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
@Test
   public void testUpdate(){
       /*
       注意：不需要设置ID，是添加，如果设置了ID，则是更新，所以能设置主键
        */
       Customer customer = new Customer();
       customer.setCustId(8L);
       customer.setCustName("李四");//设置顾客姓名
       customer.setCustAddress("北京");//设置顾客的住址
       customer.setCustPhone("1390000001");//设置顾客的电话

       //添加成功后会返回添加进去的那个对象，所以当前对象中包含ID
       Customer cust = customerDao.save(customer);
       System.out.println(cust);
   }

运行结果

查询

根据ID查询

1
2
3
4
5
@Test
   public void testFindOne(){
       Customer customer = customerDao.findOne(1L);
       System.out.println(customer);
   }

运行结果

查询所有

1
2
3
4
5
6
7
8
9
@Test
   public void testFindAll(){
       List<Customer> customers = customerDao.findAll();

       //遍历查询结果
       for (Customer customer : customers) {
           System.out.println(customer);
       }
   }

运行结果

查询总记录数

1
2
3
4
5
@Test
public void testCount(){
    long count = customerDao.count();
    System.out.println("一共有"+count+"条记录");
}

运行结果

根据ID查询是否有记录

1
2
3
4
5
@Test
   public void testExists(){
       boolean exists = customerDao.exists(1L);
       System.out.println(exists?"存在":"不存在");
   }

运行结果

findOne和getOne的区别

立刻查询findOne

1
2
3
4
5
@Test
public void testFindOne(){
    Customer customer = customerDao.findOne(1L);
    //注意获取到对象以后并没有操作该对象
}

运行结果

延迟查询 getOne

1
2
3
4
5
6
@Test
@Transactional
   public void testGetOne(){
       Customer one = customerDao.getOne(1L);
       //注意获取到对象后并没有操作对象
   }

运行结果

区别

立刻查询：无论是否操作对象都会执行SQL

延迟查询：如果不操作对象不会执行SQL

使用JPQL语句操作数据库

好处

可以使用类似SQL的语句进行自定义查询

注意事项

需要注意的是查询的是实体类和属性，不是表名和字段

使用方式

CustomDao接口代码

在接口的方法上使用@Query注解

1
2
3
4
5
6
7
8
9
10
11
12
13
/**
 *  JpaRepository<操作的实体类, 实体类中主键的类型> 接口中提供了简单操作数据库的方法
 *  JpaSpecificationExecutorr<操作的是实体类>接口中提供了复杂方法，比如多条件和分页查询
 */
public interface CustomerDao extends JpaRepository<Customer,Long>,JpaSpecificationExecutor<Customer>{
    /**
     * 通过客户名查询客户信息
     * @param name 客户姓名
     * @return
     */
    @Query(value = "from Customer where custName = ?")
    public Customer findByCustomerName(String name);
}

测试代码

1
2
3
4
5
@Test
   public void testFindByCustomerName(){
       Customer lisi = customerDao.findByCustomerName("李四");
       System.out.println(lisi);
   }

红线报错

from和?那里可能会报 from unexpected错误，不用理会，这是因为我们的JPQL语句书写不标准,当然也可以书写标准的HPQL语句，后续会讲

多占位符赋值(解决红线警告)

需求: 根据Id和姓名赋值

占位符的第一中写法

1
2
@Query(value = "from Customer where custName=? and custId=?")
public Customer findByCustomerNameAndId(String name, Long id);

占位符的第二种写法

索引必须从1开始

?后面的数字表使用第几个形式参数的值

1
2
 @Query(value = "from Customer where custId=?2 and custName=?1 ")
public Customer findByCustomerNameAndId(String name, Long id);

占位符的第三种写法

1
2
@Query(value = "from Customer where custId=:custId and custName=:custName ")
public Customer findByCustomerNameAndId(@Param("custName") String name, @Param("custId") Long id);

完全符合规范的姿势

1
2
@Query(value = "select c from Customer c where c.custId=:custId and c.custName=:custName ")
public Customer findByCustomerNameAndId(@Param("custName") String name, @Param("custId") Long id);

根据客户名查询

接口

1
2
@Query(value = "from Customer where custName = ?")
public Customer findByCustomerName(String name);

测试代码

1
2
3
4
5
@Test
   public void testFindByCustomerName(){
       Customer lisi = customerDao.findByCustomerName("李四");
       System.out.println(lisi);
   }

运行结果

更新或者删除

CustomDao接口中的代码

1
2
3
@Modifying
@Query(value = "update Customer  set custPhone=:custPhone where custId=:custId")
public void updatePhoneById(@Param("custId") Long id,@Param("custPhone")String phone);

测试代码

1
2
3
4
5
6
@Test
@Transactional
@Rollback(false)
public void testUpdatePhone(){
	customerDao.updatePhoneById(7L,"1388887777");
}

可能出现的问题

问题1：Not supported for DML operations

原因是因为接口上没有添加@Modifying 注解

问题2：Executing an update/delete query; nested exception is javax.persistence

原因是测试类没有开启事务，需要加@org.springframework.transaction.annotation.Transactional 注解

问题3：数据库没有更新成功

测试方法默认执行完后要事务回滚，修改为提交，在测试类添加@Rollback(false)注解

使用原生SQL操作数据库

使用原生SQL查询全部

接口代码

需要在@Query注解中指定属性navtiveQuery=true 表示使用的是原生SQL

这里SQL语句我没有写 select * … ，框架认为是不标准的写法要通过表的别名.*操作

1
2
@Query(value = "select * from cst_customer ", nativeQuery = true)
public List<Customer> selectAll();

###测试代码

1
2
3
4
5
6
7
8
@Test
   public void testFindAllBySql(){
       List<Customer> customers = customerDao.selectAll();

       for (Customer customer : customers) {
           System.out.println(customer);
       }
   }

运行结果

使用原生SQL模糊查询

接口代码

1
2
3
4
5
6
7
8
/*
    这里如果占位符使用的是? 那么?两边不能使用%，如果使用的是?位置 或者 :变量名 这种占位符则可以使用%
    select * from cst_customer c where cust_name LIKE %?%           错误的写法
    select * from cst_customer c where cust_name LIKE %?1%          正确的写法
    select * from cst_customer c where cust_name LIKE %:cust_name%  正确的写法
*/
@Query(value = "select * from cst_customer c where cust_name LIKE ?", nativeQuery = true)
public List<Customer> selectByCustomerName( String name);//

测试代码

1
2
3
4
5
6
7
8
9
@Test
public void testFindByNameWithSQL(){
    //如果SQL语句中已经使用%，那么这里传参的时候就不要再添加%号了
    List<Customer> customers = customerDao.selectByCustomerName("传智");

    for (Customer customer : customers) {
    	System.out.println(customer);
    }
}

运行结果

使用原生SQL修改或删除

接口代码

1
2
3
4
5
6
/**
  * 通过SQL语句更新客户名
*/
@Modifying
@Query(value = "update cst_customer set cust_name = :cust_name where cust_id=:cust_id", nativeQuery = true)
public void updateCustomerNameByIdWithSQL(@Param("cust_name") String name,@Param("cust_id") Long id);

测试代码

1
2
3
4
5
6
@Test
   @Transactional
   @Rollback(false)
   public void testUpdateWithSQL(){
       customerDao.updateCustomerNameByIdWithSQL("传智播客客",2L);
   }

运行结果

自定义简单查询

概述

自定义的简单查询就是根据方法名来自动生成 SQL，主要的语法是 findXXBy、readAXXBy、queryXXBy、countXXBy、getXXBy 后面跟属性名称：

1
User findByUsername(String userName);

也可以加一些关键字 And、Or：

1
User findByUsernameOrAddress(String username, String address);

修改、删除、统计也是类似语法：

1
2
Long deleteById(Long id);
Long countByUsername(String userName)

基本上 SQL 体系中的关键词都可以使用，如 LIKE、IgnoreCase、OrderBy。

1
2
3
List<User> findByAddressLike(String address);
User findByAddressIgnoreCase(String address);
List<User> findByAddressOrderByBalanceDesc(String address);

方法命名规范

Keyword Sample JPQL snippet
And findByLastnameAndFirstname … where x.lastname = ?1 and x.firstname = ?2
Or findByLastnameOrFirstname … where x.lastname = ?1 or x.firstname = ?2
Is,Equals findByFirstnameIs,findByFirstnameEquals … where x.firstname = ?1
Between findByStartDateBetween … where x.startDate between ?1 and ?2
LessThan findByAgeLessThan … where x.age < ?1
LessThanEqual findByAgeLessThanEqual … where x.age <= ?1
GreaterThan findByAgeGreaterThan … where x.age > ?1
GreaterThanEqual findByAgeGreaterThanEqual … where x.age >= ?1
After findByStartDateAfter … where x.startDate > ?1
Before findByStartDateBefore … where x.startDate < ?1
IsNull findByAgeIsNull … where x.age is null
IsNotNull,NotNull findByAge(Is)NotNull … where x.age not null
Like findByFirstnameLike … where x.firstname like ?1
NotLike findByFirstnameNotLike … where x.firstname not like ?1
StartingWith findByFirstnameStartingWith … where x.firstname like ?1 (parameter bound with appended %)
EndingWith findByFirstnameEndingWith … where x.firstname like ?1 (parameter bound with prepended %)
Containing findByFirstnameContaining … where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy findByAgeOrderByLastnameDesc … where x.age = ?1 order by x.lastname desc
Not findByLastnameNot … where x.lastname <> ?1
In findByAgeIn(Collection ages) … where x.age in ?1
NotIn findByAgeNotIn(Collection age) … where x.age not in ?1
TRUE findByActiveTrue() … where x.active = true
FALSE findByActiveFalse() … where x.active = false
IgnoreCase findByFirstnameIgnoreCase … where UPPER(x.firstame) = UPPER(?1)

##6.2基本查询

方法命名格式

属性名首字母要大写

查询一个实体类：findBy属性名1And属性名2

查询多个实体类：findAllBy属性名1And属性名2

接口代码

1
2
3
4
5
6
/**
    * 生成的SQL select * from cst_customer where cust_name = ?
    * @param custName
    * @return
    */
   public Customer findByCustName(String custName);

测试代码

1
2
3
4
5
@Test
   public void testFindByCustName(){
       Customer customer = customerDao.findByCustName("传智汇");
       System.out.println(customer);
   }

运行结果

模糊查询

方法命名格式

属性名首字母要大写

查询一个实体类：findBy属性名1And属性名2+Like

查询多个实体类：findAllBy属性名1And属性名2+Like

表数据

接口代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/**
     * 单个条件模糊查询
     * 格式 属性名后加Like， findAllBy属性名Like()
     * @param name
     * @return
     */
    public List<Customer> findAllByCustNameLike(String name);
    /**
     * 使用多个条件模糊查询
     * 格式 属性名后加Like就好，findAllBy属性名1LikeAnd属性名2Like
     * @param phone
     * @param name
     * @return
     select * from 表名 where custphone like ? or custName like
     */
    //select * from 表名 where cust_industry like ? or cust_name liek ?
    public List<Customer> findAllByCustIndustryLikeOrCustNameLike(String industry, String name);

测试代码

1
2
3
4
5
6
7
8
@Test
public void testLike(){
    List<Customer> customers = customerDao.findAllByCustNameLike("%传智%");
    System.out.println(customers.size());

    List<Customer> li = dao.findAllByCustIndustryLikeOrCustNameLike("教%", "传智%");
    System.out.println(li.size());
}

运行结果

文章作者: 微信：hao_yongliang
文章链接: https://haoyongliang.gitee.io/2019/07/13/SpringBoot/SpringBoot2.X%E9%9B%86%E6%88%90SpringDataJPA(%E4%BA%8C)/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 郝永亮的主页！
SpringDataJPA
打赏
  • 微信
    微信
  • 支付寶
    支付寶
相关推荐
2019-07-13
SpringBoot2.X集成SpringDataJPA(一)
2019-07-13
SpringDataJPA-动态查询和多表查询(三)
评论