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

文件目录结构

开发流程

1. 创建Emloyee实体类
   根据数据库内容填写属性名称，增加getter、setter等方法，其中NUMBER对应BigDecimal，VARCHAR对应String，DATE对应java.sql.Date
2. 创建EmployeeDAO接口
3. 创建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>

4. 创建EmployeeService和实现类EmployeeServiceImpl
5. 编写Controller

   • 跨域问题

   • 接收参数
     接收参数的方式有两种一种通过@RequestParam传递参数，指定每个参数的类型，一种通过@RequestBody传递对象或字符串。

   • json处理

MyBatis

Oracle

Tips