springboot-mybatis-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
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
创建表空间
脚本创建表空间
-- 表空间 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
绑定角色
脚本创建用户并绑定角色
CREATE USER "OA" IDENTIFIED BY "123456" DEFAULT TABLESPACE "OA" TEMPORARY TABLESPACE "TEMP";
GRANT "DBA" TO "root";
ALTER USER "root" DEFAULT ROLE "DBA"
设计 Entity
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 +
'}';
}
}
创建数据库
-- 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
<?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
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
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
<?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 包扫描配置
@MapperScan("cn.idea360.oracle.dao")
@SpringBootApplication
public class OracleApp {
public static void main(String[] args) {
SpringApplication.run(OracleApp.class, args);
}
}
分页
/**
* 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 定义
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
<?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>
单元测试
@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
, 如有错误还请指出, 共同进步。同时,希望大家关注公众号【当我遇上你】, 您的支持就是我最大的动力。