SpringBoot+Mybatis+Oracle 项目搭建
项目地址
后端工程BondSales
Angular前端工程BondSalesWeb
SpringBoot
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://maven.apache.org/POM/4.0.0"
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>org.example</groupId>
<artifactId>SpringBootOracleDemo</artifactId>
<version>1.0-SNAPSHOT</version>
<!-- 常用的包依赖可以省去version标签 -->
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.6.RELEASE</version>
</parent>
<dependencies>
<!-- 链接Oracle数据库 oracle ojdbc不免费,需要手动引入jar包 -->
<!-- https://mvnrepository.com/artifact/oracle/ojdbc6 -->
<dependency>
<groupId>oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<!-- SpringBoot web -->
<!-- 构建Web,包含RESTful风格框架SpringMVC和默认的嵌入式容器Tomcat -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- jdbc -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 集成mybatis -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
<!-- json tool -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.44</version>
</dependency>
<!-- 使用json处理工具 -->
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>1.9.13</version>
</dependency>
<!-- 使用模板引擎 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.0</version>
</dependency>
</dependencies>
</project>
application.yml
spring:
datasource:
url: jdbc:oracle:thin:@192.168.0.103:1521:orcl
username: hr
password: hr
driver-class-name: oracle.jdbc.driver.OracleDriver
continue-on-error: true
hikari:
connection-timeout: 10000
thymeleaf:
prefix: classpath:/templates/
suffix: .html
logging:
level:
com.xkcoding: debug
com.xkcoding.orm.mybatis.MapperAndPage.mapper: trace
mybatis:
configuration:
# 下划线转驼峰
map-underscore-to-camel-case: true
mapper-locations: classpath:mappers/*.xml
type-aliases-package: com.group4.backend.entity
文件目录结构
开发流程
- 创建Emloyee实体类
根据数据库内容填写属性名称,增加getter、setter等方法,其中NUMBER对应BigDecimal,VARCHAR对应String,DATE对应java.sql.Date - 创建EmployeeDAO接口
- 创建EmployeeDAOMapper
一般Mapper.xml存于resource/mappers中
- 拼接查询语句
<resultMap type="DemoBond" id="_bond">
<id property="id" column="ID"/>
<result property="bondName" column="BONDS_NAME"/>
<result property="saleName" column="SALES_NAME"/>
<result property="amount" column="AMOUNT"/>
<result property="createdAt" column="CREATED_AT"/>
<result property="updatedAt" column="UPDATED_AT"/>
</resultMap>
<select id="queryBond" resultMap="_bond">
select * from DEMO_BONDS_SALES_RECORD
<where>
<if test="bondName != null and bondName!='' ">
"AND" BONDS_NAME like CONCAT(#{bondName},'%')
</if>
<if test="saleName != null and saleName!='' ">
and SALES_NAME like CONCAT(#{saleName},'%')
</if>
<if test="createdAt != null">
and CREATED_AT like CONCAT(#{createdAt},'%')
</if>
<if test="amount != null and amount!='' ">
and AMOUNT like CONCAT(#{amount},'%')
</if>
</where>
</select>
- 查询日期并转换格式
<resultMap id="createdAtMap" type="java.util.Map">
<result column="name" property="key" jdbcType="DATE"/>
<result column="amount" property="value" jdbcType="VARCHAR"/>
</resultMap>
<select id="orderByCreated" resultMap="createdAtMap" parameterType="DemoBond">
select TO_CHAR(CREATED_AT, 'yyyy-MM-dd') as name, SUM(AMOUNT) as amount
from DEMO_BONDS_SALES_RECORD
GROUP BY CREATED_AT
ORDER BY CREATED_AT
</select>
- 批量插入
<insert id="importBonds" parameterType="java.util.List" useGeneratedKeys="false">
INSERT ALL
<foreach item="demoBond" index="index" collection="list">
INTO DEMO_BONDS_SALES_RECORD
(
ID,
BONDS_NAME,
SALES_NAME,
AMOUNT,
CREATED_AT,
UPDATED_AT
) VALUES
(
null,
#{demoBond.bondName},
#{demoBond.saleName},
#{demoBond.amount},
#{demoBond.createdAt},
null
)
</foreach>
SELECT 1 FROM DUAL
</insert>
- 创建EmployeeService和实现类EmployeeServiceImpl
- 编写Controller
-
跨域问题
-
接收参数
接收参数的方式有两种一种通过@RequestParam传递参数,指定每个参数的类型,一种通过@RequestBody传递对象或字符串。 -
json处理
-