springboot使用mybatis连接oracle简单使用

建表

create table public_memo(
   ids varchar2(32) not null,
   title varchar2(255) not null,
   contents clob not null,
   address varchar(255) not null,
   longitude number(13,10) not null,
   latitudenumber(13,10) not null,
   created_date date,
   updated_date date,
   status varchar2(4) not null
)

　　字段加注释

comment on table   public_memo is '备忘录';
  
comment on column public_memo.ids is '主键id';
  
comment on column public_memo.title is '标题';
  
comment on column public_memo.contents is '内容';
  
comment on column public_memo.address is '地址';
  
comment on column public_memo.longitude is '经度';
  
comment on column public_memo.latitudeis '纬度';
  
comment on column public_memo.created_date is '创建时间';
  
comment on column public_memo.updated_date is '修改时间';
  
comment on column public_memo.status is '状态';

　　数据准备

insert into public_memo(ids,title,contents,address,longitude,latitude，created_date,updated_date,status)
values (sys_guid(),'1','23','浦东大道',12.1234,13.2345,sysdate,sysdate,'1');
  
insert into public_memo(ids,title,contents,address,longitude,latitude，created_date,updated_date,status)
values (sys_guid(),'2','24','浦东大道001',12.1234,13.2345,sysdate,sysdate,'2');
  
  
insert into public_memo(ids,title,contents,address,longitude,latitude，created_date,updated_date,status)
values (sys_guid(),'3','25','浦东大道002',12.1234,13.2345,sysdate,sysdate,'3');

　　pom文件

<?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>com.java</groupId>
    <artifactId>test-study</artifactId>
    <version>1.0-SNAPSHOT</version>
 
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.1.RELEASE</version>
        <relativePath/>
    </parent>
 
    <dependencies>
        <!--tomcat容器-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--lombok依赖-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.16</version>
        </dependency>
        <!--引入junit单元测试依赖-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!--判断空的用法  -->
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>
 
        <!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 -->
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>12.2.0.1</version>
        </dependency>
        <!--springboot整合mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>
 
 
    </dependencies>
 
 
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
        <finalName>study</finalName>
    </build>
 
 
</project>

　　配置文件配置数据库

server.port=2001
 
 
logging.level.com.java.test=debug
logging.level.web=debug
spring.devtools.add-properties=false
 
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521/orcl
spring.datasource.username=test
spring.datasource.password=test
mybatis.mapper-locations=classpath:mapping/*.xml

　　实体类

package com.java.test.bean;
 
import lombok.Data;
 
import java.util.Date;
 
/**
 * @Description:
 * @Author: Yourheart
 * @Create: 2022/10/26 15:49
 */
@Data
public class PublicMemoDO {
 
    private String ids;
    /**
     * 标题
     */
    private String title;
    /**
     * 内容
     */
    private String contents;
    /**
     * 地址
     */
    private String address;
    /**
     * 经度
     */
    private double longitude;
    /**
     * 纬度
     */
    private double latitude;
    /**
     * 创建时间
     */
    private Date createdDate;
    /**
     * 修改时间
     */
    private  Date updatedDate;
    /**
     * 状态
     */
    private String status;
}

　　dao层

package com.java.test.mapper;
 
import com.java.test.bean.PublicMemoDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
 
import java.util.List;
import java.util.Map;
 
/**
 * @Description:
 * @Author: Yourheart
 * @Create: 2022/10/26 15:17
 */
@Mapper
public interface PublicMemoMapper {
 
 
    @Select("select  *  from  PUBLIC_MEMO")
    List<Map<String,Object>> getList();
 
    List<PublicMemoDO> listPublicMemos();
}

　　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="com.java.test.mapper.PublicMemoMapper">
 
     <resultMap id="publicMemoMap" type="com.java.test.bean.PublicMemoDO">
         <result column="CREATED_DATE" javaType="DATE" property="createdDate"/>
         <result column="UPDATED_DATE" javaType="DATE" property="updatedDate"/>
     </resultMap>
 
 
    <select id="listPublicMemos" resultMap="publicMemoMap">
        select  tt.title,tt.address,tt.created_date,tt.updated_date  from  public_memo tt  order by tt.updated_date desc
    </select>
 
</mapper>

　　测试类

package com.java.test;
 
import com.java.test.bean.PublicMemoDO;
import com.java.test.mapper.PublicMemoMapper;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
 
import java.util.List;
 
/**
 * @Description:
 * @Author: Yourheart
 * @Create: 2022/10/26 15:19
 */
@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class TestApplicationTest {
 
 
    @Autowired
    private PublicMemoMapper publicMemoMapper;
 
    @Test
    public void test(){
        //List<Map<String, Object>> list = publicMemoMapper.getList();
        List<PublicMemoDO> memoList = publicMemoMapper.listPublicMemos();
        memoList.stream().forEach(a->{
            log.info(a.toString());
        });
 
    }
}

　结果打印

mybatis连接oracle注意

oracle插入语法和mysql不太一样

单条插入的时候，例如

1	`insert into test(id) values('12');在oracle数据库中执行不会报错`

但是mybatis的xml文件需要去掉分号，不然会报错

oracle没有批量插入的语法,因此采用

<insert id="add"  parameterType="java.util.List">
    insert into test
    (id)
    <foreach collection="list" item="item" index="index" open="(" close=")" separator=" UNION ALL ">
        SELECT #{item.id,jdbcType=VARCHAR}  FROM dual
    </foreach>
</insert>