springboot-mybatis-oracle学习笔记

oracle , , 评论

前言

最近公司的项目是基于 oracle 数据库的，这里记录下 springboot+mybatis+oracle 的踩坑过程。

开发前准备

环境参数

  • 开发工具：IDEA
  • 基础工具：Maven+JDK8
  • 所用技术：SpringBoot+Mybatis+Oracle
  • 数据库：MySQL5.7
  • SpringBoot版本：2.2.6.RELEASE
  • Mybatis版本: 2.1.2
  • Oracle: oracle-xe-11g

基于Docker搭建Oracle

docker-compose-oracle.yml

1
2
3
4
5
6
7
8
9
10
version: '2'
services:
  oracle:
    image: sath89/oracle-xe-11g
    container_name: oracle
    ports:
      - 1521:1521
      - 8082:8080
    volumes:
      - ./oracle/data:/u01/app/oracle

连接参数

  • Connection Type: Basic
  • Host: localhost
  • Port: 1521
  • Service Name: xe
  • Username: system
  • Password: oracle

创建 表空间

Navicat 创建表空间

脚本创建表空间

1
2
3
4
5
6
-- 表空间 OA
-- 表空间物理文件位置 /u01/app/oracle/oradata/XE/OA
-- 大小20M
-- 每次20M自动增大
-- 最大100M
CREATE TABLESPACE "OA" DATAFILE '/u01/app/oracle/oradata/XE/OA' SIZE 20 M AUTOEXTEND ON NEXT 20 M MAXSIZE 100 M

创建 用户 并绑定 角色

Navicat 创建用户

Navicat 绑定角色

脚本创建用户并绑定角色

1
2
3
4
5
CREATE USER "OA" IDENTIFIED BY "123456" DEFAULT TABLESPACE "OA" TEMPORARY TABLESPACE "TEMP";

GRANT "DBA" TO "root";

ALTER USER "root" DEFAULT ROLE "DBA"

设计Entity

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
public class Account {

    // 主键
    private Integer id;
    // 用户名(唯一)
    private String realName;
    // 工号(递增)
    private Integer jobNumber;
    // 创建时间
    private Date createTime;

    public Account() {
    }

    public Account(String realName, Integer jobNumber) {
        this.realName = realName;
        this.jobNumber = jobNumber;
        this.createTime = new Date();
    }

    public Integer getId() {
        return id;
    }

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

    public String getRealName() {
        return realName;
    }

    public void setRealName(String realName) {
        this.realName = realName;
    }

    public Integer getJobNumber() {
        return jobNumber;
    }

    public void setJobNumber(Integer jobNumber) {
        this.jobNumber = jobNumber;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", realName='" + realName + '\'' +
                ", jobNumber=" + jobNumber +
                ", createTime=" + createTime +
                '}';
    }
}

创建数据库

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
-- ID 主键、自增
-- USER_NAME 唯一索引
-- JOB_NUMBER 递增
CREATE TABLE "OA"."ACCOUNT" (
"ID" NUMBER(18, 0) NOT NULL ,
"REAL_NAME" VARCHAR2(128) NOT NULL ,
"JOB_NUMBER" NUMBER(18,0) DEFAULT 0 NOT NULL ,
"CREATE_TIME" TIMESTAMP(6) NULL ,
PRIMARY KEY ("ID")
);

-- 字段说明
COMMENT ON table ACCOUNT IS '账户表';
COMMENT ON COLUMN "OA"."ACCOUNT"."ID" IS '主键';
COMMENT ON COLUMN "OA"."ACCOUNT"."REAL_NAME" IS '用户名';
COMMENT ON COLUMN "OA"."ACCOUNT"."JOB_NUMBER" IS '工号';
COMMENT ON COLUMN "OA"."ACCOUNT"."CREATE_TIME" IS '创建时间';

-- 创建唯一索引
CREATE UNIQUE INDEX INDEX_REAL_NAME on ACCOUNT(REAL_NAME);

-- 创建ID递增序列
CREATE SEQUENCE ACCOUNT_ID_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;

-- 创建触发器实现主键自增
CREATE OR REPLACE TRIGGER ACCOUNT_TRG BEFORE INSERT ON ACCOUNT FOR EACH ROW 
BEGIN
    SELECT ACCOUNT_ID_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

-- 查看序列
SELECT * FROM user_sequences;
-- 查看触发器
SELECT * FROM user_triggers;

代码实践

配置文件

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
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>cn.idea360</groupId>
    <artifactId>idc-oracle</artifactId>
    <version>0.0.1</version>
    <name>idc-oracle</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>

        <dependency>
            <groupId>com.oracle.ojdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

application.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
server:
  port: 9090
spring:
  profiles:
    active: local

mybatis:
  type-aliases-package: cn.idea360.oracle.model
  configuration:
    map-underscore-to-camel-case: true
    default-fetch-size: 100
    default-statement-timeout: 30
  mapper-locations: mapper/*.xml

logging:
  level:
    root: info

application-local.yml

1
2
3
4
5
6
spring:
  datasource:
    driver-class-name: oracle.jdbc.driver.OracleDriver
    url: jdbc:oracle:thin:@127.0.0.1:1521:XE
    username: oa
    password: 123456

mybatis-config.xml

1
2
3
4
5
6
7
8
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeAliases>
        <typeAlias alias="account" type="cn.idea360.oracle.model.Account"/>
    </typeAliases>
</configuration>

Java代码

mapper包扫描配置

1
2
3
4
5
6
7
8
9
@MapperScan("cn.idea360.oracle.dao")
@SpringBootApplication
public class OracleApp {

    public static void main(String[] args) {
        SpringApplication.run(OracleApp.class, args);
    }

}

分页

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
/**
 * Mapper进一步实现可以基于拦截器实现
 * @param <T>
 */
@Data
public class Page<T> {

    /**
     * 查询数据列表
     */
    private List<T> records = Collections.emptyList();
    /**
     * 总数
     */
    private long total = 0;
    /**
     * 每页显示条数，默认 10
     */
    private long size = 10;
    /**
     * 当前页
     */
    private long current = 1;
    /**
     * KEY/VALUE 条件
     */
    private Map<Object, Object> condition;

    /**
     * oracle分页： start
     */
    private Integer startIndex = 1;
    /**
     * oracle分页： end
     */
    private Integer endIndex = 10;

    public Page() {
    }

    public Page(long current, long size) {
        this(current, size, 0);
    }

    public Page(long current, long size, long total) {
        if (current > 1) {
            this.current = current;
        }
        this.size = size;
        this.total = total;
    }

    /**
     * 计算当前分页偏移量
     */
    public long offset() {
        return getCurrent() > 0 ? (getCurrent() - 1) * getSize() : 0;
    }

    /**
     * 当前分页总页数
     */
    public long getPages() {
        if (getSize() == 0) {
            return 0L;
        }
        long pages = getTotal() / getSize();
        if (getTotal() % getSize() != 0) {
            pages++;
        }
        return pages;
    }

    /**
     * oracle分页开始
     * @return
     */
    public long getStartIndex() {
        return (getCurrent()-1)*size+1;
    }

    /**
     * oracle分页结束
     * @return
     */
    public long getEndIndex() {
        return getCurrent()*size;
    }

}

mapper定义

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
public interface AccountMapper {

    /**
     * 插入单条数据
     * @param account
     * @return 返回主键id
     */
    int insert(Account account);

    /**
     * 批量插入list
     * @param data
     */
    void insertBatch(@Param("coll") Collection<Account> data);

    /**
     * 更新数据
     * @param account
     * @return
     */
    int updateIgnoreNullById(@Param("et") Account account);

    /**
     * 删除数据
     * @param id
     * @return
     */
    int removeById(@Param("id") Integer id);

    /**
     * 根据id查询数据
     * @param id
     * @return
     */
    Account selectById(@Param("id") Integer id);

    /**
     * 根据其他字段查询数据
     * @param columnMap
     * @return
     */
    Account selectByMap(@Param("cm") Map<String, Object> columnMap);

    /**
     * 根据id数组批量查询数据
     * @param idArray
     * @return
     */
    List<Account> selectByIds(@Param("coll") Integer[] idArray);

    /**
     * 根据分页参数查询数据
     * @param page
     * @return
     */
    List<Account> selectPage(@Param("page") Page page);

    /**
     * 查询所有
     * @return
     */
    List<Account> listAll();

}

AccountMapper.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
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="cn.idea360.oracle.dao.AccountMapper">

    <!--resultType是直接表示返回类型的(对应着我们的model对象中的实体)，而resultMap则是对外部ResultMap的引用(提前定义了db和model之间的隐射key-value关系)-->
    <resultMap id="accountMap" type="account">
        <id column="id" property="id" />
        <result column="real_name" property="realName"/>
        <result column="job_number" property="jobNumber" />
        <result column="create_time" property="createTime" />
    </resultMap>

    <!--插入单条数据，自增主键通过序列和触发器实现-->
    <insert id="insert"  parameterType="account" useGeneratedKeys="true">
        insert into account (real_name, job_number, create_time)
        values
            (#{realName}, #{jobNumber}, #{createTime})
        <selectKey keyProperty="id" resultType="java.lang.Integer" order="AFTER">
            select ACCOUNT_ID_SEQ.CURRVAL from dual
        </selectKey>
    </insert>

    <!--通过list批量插入数据-->
    <insert id="insertBatch" parameterType="java.util.List">
        insert into account (real_name, job_number, create_time)
        <foreach collection="coll" item="item"  index="index" separator="UNION ALL">
            select
            #{item.realName}, #{item.jobNumber}, #{item.createTime}
            from dual
        </foreach>
    </insert>

    <!--更新数据-->
    <update id="updateIgnoreNullById">
        update account
        <set>
            <if test="et.realName != null">
                real_name = #{et.realName},
            </if>
            <if test="et.jobNumber != null">
                job_number = #{et.jobNumber},
            </if>
        </set>
        where id = #{et.id}
    </update>

    <!--根据主键id移除数据-->
    <delete id="removeById">
        delete from account where id = #{id}
    </delete>

    <!--根据主键id查询数据-->
    <select id="selectById" parameterType="integer" resultMap="accountMap">
        select * from account where id = #{id}
    </select>

    <!--根据其他字段查询数据-->
    <select id="selectByMap" resultMap="accountMap">
        select * from account
        <where>
            <if test="cm != null and cm.realName != null">
                and real_name = #{cm.realName}
            </if>
        </where>
    </select>

    <!--根据id数组查询数据-->
    <select id="selectByIds" resultMap="accountMap">
        select * from account where id in
        <foreach collection="coll" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <!--根据分页参数查询数据-->
    <select id="selectPage" resultMap="accountMap">
        SELECT
        T2.*
        FROM
        ( SELECT T1.*, ROWNUM RN FROM ( SELECT a.* FROM account a
        <where>
            <if test="page != null and page.condition != null and page.condition.keyword != null">
                AND a.real_name LIKE '%' ||  #{page.condition.keyword} || '%'
            </if>
        </where>
        ORDER BY id DESC
        ) T1 ) T2
        <where>
            <if test="page != null and page.startIndex != null">
                and RN <![CDATA[ >= ]]> #{page.startIndex}
            </if>
            <if test="page != null and page.endIndex != null">
                AND RN <![CDATA[ <= ]]> #{page.endIndex}
            </if>
        </where>
    </select>
    <select id="listAll" resultType="cn.idea360.oracle.model.Account">
        select * from account
    </select>

</mapper>

单元测试

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
@SpringBootTest
class AccountMapperTest {

    @Autowired
    private AccountMapper accountMapper;

    @Test
    public void insert() {
        Account account = new Account("admin", 1);
        accountMapper.insert(account);
        System.out.println(account.getId());
    }

    @Test
    public void insertBatch() {
        List<Account> data = new ArrayList<>();
        for (int i=0; i<3; i++) {
            Account account = new Account("test" + i, i+2);
            data.add(account);
        }
        accountMapper.insertBatch(data);
    }

    @Test
    public void updateIgnoreNullById() {
        Account account = new Account("admin0", 1);
        account.setId(1);
        accountMapper.updateIgnoreNullById(account);
    }

    @Test
    public void removeById() {
        accountMapper.removeById(4);
    }

    @Test
    public void selectById() {
        Account account = accountMapper.selectById(4);
        System.out.println(account.toString());
    }

    @Test
    public void selectByMap() {
        Map<String, Object> params = new HashMap<>();
        params.put("realName", "admin");
        Account account = accountMapper.selectByMap(params);
        System.out.println(account);
    }

    @Test
    public void selectByIds() {
        Integer[] ids = {1, 2};
        List<Account> accounts = accountMapper.selectByIds(ids);
        System.out.println(accounts);
    }

    @Test
    public void selectPage() {
        Page<Account> page = new Page<>(1, 10);
        HashMap<Object, Object> condition = new HashMap<>();
        condition.put("keyword", "ad");
        page.setCondition(condition);
        List<Account> accounts = accountMapper.selectPage(page);
        page.setRecords(accounts);
        System.out.println(page);
    }

    @Test
    public void listAll() {
        List<Account> accounts = accountMapper.listAll();
        System.out.println(accounts);
    }
}

最后

oracle 在自增主键、分页、模糊查询、批量操作上和 mysql 略有不同，上边的例子里基本都演示到了。初次接触 oracle, 如有错误还请指出, 共同进步。同时，希望大家关注公众号【当我遇上你】, 您的支持就是我最大的动力。

当我遇上你

勿在浮沙筑高楼