美文网首页
Mybatis+Oracle12c+Springboot 201

Mybatis+Oracle12c+Springboot 201

作者: 代码届的泥石流sc | 来源:发表于2019-03-29 13:43 被阅读0次

    概述

    Lz之前在项目使用的是hibernate+springMVC+mysql。现在的公司计划使用的mybatisPlus+oracle+springboot.虽然这些官网上的api挺多挺详细的,但自己在学习的过程中还是遇到一些坑。所以想整理一下项目,加深记忆,方便以后查阅。文笔有限,多多包涵。

    工具

      安装oracle 12c
      jdk1.8
      IntelliJ IDEA 
    

    代码

    主要实现:
    1、generator 自动生成代码:contorller、entiy、servcie、*mapper.xml等文件
    2、oracle数据库的自增 序列、触发器
    3、利用Postman实现简单的CRUD

    ps:省略oracle等工具的安装
    闲话少讲,干活:
    1、首先在IDEA中创建springboot项目

    1.png
    2.png
    3.png
    最后Finshed即可。
    项目的pom.xml,所有工具依赖的包都是201903最新的,直接去maven仓库搜索加上的
    附上Maven仓库依赖查询的地址:https://mvnrepository.com/
    <?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>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.1.3.RELEASE</version>
            <relativePath/> <!-- lookup parent from repository -->
        </parent>
        <groupId>com.dv</groupId>
        <artifactId>mpdemo</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>mpdemo</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.springframework.boot</groupId>
                <artifactId>spring-boot-devtools</artifactId>
                <optional>true</optional>
                <scope>true</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>
            </dependency>
    
            <dependency>
                <groupId>com.oracle</groupId>
                <artifactId>ojdbc7_g</artifactId>
                <version>12.1.0.1.0</version>
            </dependency>
    
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.1.0</version>
            </dependency>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-generator</artifactId>
                <version>3.1.0</version>
            </dependency>
    
            <!-- velocity 模板引擎, 默认 -->
            <dependency>
                <groupId>org.apache.velocity</groupId>
                <artifactId>velocity-engine-core</artifactId>
                <version>2.0</version>
            </dependency>
    
            <!-- freemarker 模板引擎 -->
            <dependency>
                <groupId>org.freemarker</groupId>
                <artifactId>freemarker</artifactId>
                <version>2.3.28</version>
            </dependency>
    
    
            <!-- beetl 模板引擎 -->
            <dependency>
                <groupId>com.ibeetl</groupId>
                <artifactId>beetl</artifactId>
                <version>2.9.9</version>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                    <configuration>
                        <!--fork:如果没有该项配置,整个devtools不会起作用-->
                        <fork>true</fork>
                    </configuration>
                </plugin>
            </plugins>
        </build>
    
    </project>
    
    
    微信截图_20190329153618.png

    其中oracle的版本是12c,需要把oracle驱动手动加载到本地Maven仓库中
    (1)到oracle安装目录找到相应jdk的驱动:

    image.png

    (2)把驱动放到本地maven仓库,注意这是我的安装路径和版本,读者只需相应的替换对应的文件即可

    mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc7_g -Dversion=12.1.0.1.0
    -Dfile=E:\app\teemo\product\12.1.0\dbhome_1\jdbc\lib\ojdbc7_g.jar -Dpackaging=jar -DgeneratePom=true
    

    ps:关于maven本地仓库的配置,请自行百度。IDEA中点击file->setting->Maven 设置你对应的路径即可


    4.png

    application.properties文件

    spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
    spring.datasource.username=c##ly_dv
    spring.datasource.password=root
    spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
    #这是更改tomcat默认端口
    server.port=8989
    
    mybatis-plus.mapper-locations=classpath:/mapper/*Mapper.xml
    mybatis-plus.type-aliases-package = com.dv.mpdemo.mapper
    

    2、代码自动生成器generator 这部分mybatisplus官网api讲的详细的,读者可自行查看mybatisplus官网

    这是我结合根据自己项目的路径运用的MysqlGenerator ,以及一些注释

    package com.dv.mpdemo;
    
    import com.baomidou.mybatisplus.core.exceptions.MybatisPlusException;
    import com.baomidou.mybatisplus.core.toolkit.StringPool;
    import com.baomidou.mybatisplus.core.toolkit.StringUtils;
    import com.baomidou.mybatisplus.generator.AutoGenerator;
    import com.baomidou.mybatisplus.generator.InjectionConfig;
    import com.baomidou.mybatisplus.generator.config.*;
    import com.baomidou.mybatisplus.generator.config.po.TableInfo;
    import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
    import com.baomidou.mybatisplus.generator.engine.VelocityTemplateEngine;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Scanner;
    
    /**
     * <p>
     * mysql 代码生成器演示例子
     * </p>
     *
     * @author dv
     * @since 2019-03-28
     */
    public class MysqlGenerator {
    
        /**
         * <p>
         * 读取控制台内容
         * </p>
         */
        public static String scanner(String tip) {
            Scanner scanner = new Scanner(System.in);
            StringBuilder help = new StringBuilder();
            help.append("请输入" + tip + ":");
            System.out.println(help.toString());
            if (scanner.hasNext()) {
                String ipt = scanner.next();
                if (StringUtils.isNotEmpty(ipt)) {
                    return ipt;
                }
            }
            throw new MybatisPlusException("请输入正确的" + tip + "!");
        }
    
        /**
         * RUN THIS
         */
        public static void main(String[] args) {
            // 代码生成器
            AutoGenerator mpg = new AutoGenerator();
    
            // 全局配置
            GlobalConfig gc = new GlobalConfig();
            String projectPath = System.getProperty("user.dir");
            System.out.println(projectPath);
            gc.setOutputDir(projectPath + "/src/main/java");
            gc.setAuthor("dv");
            gc.setOpen(false);  //生成文件后是否打开目录
            gc.setBaseResultMap(true);  // XML ResultMap
            gc.setFileOverride(true);// 是否覆盖文件
            gc.setActiveRecord(true);// 开启 activeRecord 模式
            gc.setEnableCache(false);// XML 二级缓存
    
            mpg.setGlobalConfig(gc);
    
            // 数据源配置
            DataSourceConfig dsc = new DataSourceConfig();
            dsc.setUrl("jdbc:oracle:thin:@localhost:1521:orcl");
            // dsc.setSchemaName("public");
            dsc.setDriverName("oracle.jdbc.driver.OracleDriver");
            dsc.setUsername("c##ly_dv");
            dsc.setPassword("root");
            mpg.setDataSource(dsc);
    
            // 包配置
            PackageConfig pc = new PackageConfig();
    //        pc.setModuleName(scanner("模块名"));
            pc.setParent("com.dv.mpdemo");
            mpg.setPackageInfo(pc);
    
            // 如果模板引擎是 freemarker
    //        String templatePath = "/templates/mapper.xml.ftl";
            // 如果模板引擎是 velocity
             String templatePath = "/templates/mapper.xml.vm";
    
            // 自定义配置
            InjectionConfig cfg = new InjectionConfig() {
                @Override
                public void initMap() {
                    // to do nothing
                }
            };
            List<FileOutConfig> focList = new ArrayList<>();
            focList.add(new FileOutConfig(templatePath) {
                @Override
                public String outputFile(TableInfo tableInfo) {
                    // 自定义输入文件名称
                    return projectPath + "/src/main/resources/mapper/"
                            + "/" + tableInfo.getEntityName() + "Mapper" + StringPool.DOT_XML;
                }
            });
            cfg.setFileOutConfigList(focList);
            mpg.setCfg(cfg);
            mpg.setTemplate(new TemplateConfig().setXml(null));
    
            // 策略配置
            StrategyConfig strategy = new StrategyConfig();
            strategy.setNaming(NamingStrategy.underline_to_camel);
            strategy.setColumnNaming(NamingStrategy.underline_to_camel);
    //        strategy.setSuperEntityClass("com.baomidou.mybatisplus.samples.generator.common.BaseEntity");
            strategy.setEntityLombokModel(true);
    //        strategy.setSuperControllerClass("com.baomidou.mybatisplus.samples.generator.common.BaseController");
            strategy.setInclude(scanner("表名"));
            strategy.setSuperEntityColumns("id");
    //        strategy.set
            strategy.setControllerMappingHyphenStyle(true);
            strategy.setTablePrefix(pc.getModuleName() + "_");
            mpg.setStrategy(strategy);
            // 选择 freemarker 引擎需要指定如下加,注意 pom 依赖必须有!
            mpg.setTemplateEngine(new VelocityTemplateEngine());  //对应的模板引擎 80
            mpg.execute();
        }
    
    }
    
    

    首先在oracle中创建一张表:t_test

    create table t_test(  
      id number(10)   primary key,  
      name varchar2(20) not null
    ); 
    

    创建序列和触发器 实现插入数据的时候id的自增:

    --序列
    CREATE SEQUENCE  "C##LY_DV"."SEQ_TTEST_DI"  
    MINVALUE 1 
    MAXVALUE 333 
    INCREMENT BY 1 
    START WITH 1 
    CACHE 20 ORDER  NOCYCLE  NOPARTITION ;
    
    --触发器
    create or replace 
    trigger TIR_TTEST_ID 
    BEFORE INSERT ON T_TEST 
    FOR EACH ROW 
    BEGIN
     SELECT "SEQ_TTEST_DI".nextval INTO :new.ID FROM dual;
    END;
    

    运行MysqlGenerator:手动输入表名回车即可


    8.png

    最后生成的项目目录


    7.png
    直接上TTestContonller.java
    package com.dv.mpdemo.controller;
    
    
    import com.dv.mpdemo.entity.TTest;
    import com.dv.mpdemo.mapper.TCouseMapper;
    import com.dv.mpdemo.mapper.TTestMapper;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.ResponseBody;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * <p>
     *  前端控制器
     * </p>
     *
     * @author dv
     * @since 2019-03-28
     */
    @Controller
    @RequestMapping("/t-test")
    public class TTestController {
    
        @Autowired
        private TTestMapper tTestMapper;
    
        @Autowired
        private TCouseMapper tCouseMapper;
    
        @ResponseBody
        @RequestMapping(value="/getTtestById")
        public TTest getTtestById(HttpServletRequest request, HttpServletResponse response){
            List<TTest> ls = new ArrayList<TTest>();
            String id = request.getParameter("id");
            TTest tt = tTestMapper.getTtestById(Long.parseLong(id));
            System.out.println("你好getTtestById");
            return tt;
        }
    
        @ResponseBody
        @RequestMapping(value="/getAlls")
        public List<TTest> getAlls(HttpServletRequest request, HttpServletResponse response){
            List<TTest> ls = new ArrayList<TTest>();
            ls = tTestMapper.getAlls();
    //        System.out.println("你好getAlls");
            return ls;
        }
    
    
        @ResponseBody
        @RequestMapping(value="/insertT")
        public boolean insertT(HttpServletRequest request, HttpServletResponse response){
            List<TTest> ls = new ArrayList<TTest>();
    
            String id = request.getParameter("id");
            String name = request.getParameter("name");
            boolean flag = false;
            try{
                tTestMapper.insertT(Long.parseLong(id),name);
                flag = true;
            }catch (Exception e ){
                e.printStackTrace();
            }
    //        System.out.println("你好insertT");
            return flag;
        }
    
        @ResponseBody
        @RequestMapping(value="/insertTNotId")
        public boolean insertTNotId(HttpServletRequest request, HttpServletResponse response){
            List<TTest> ls = new ArrayList<TTest>();
    
    //      String id = request.getParameter("id");
            String name = request.getParameter("name");
            boolean flag = false;
            try{
                tTestMapper.insertTNotId(name);
                flag = true;
            }catch (Exception e ){
                e.printStackTrace();
            }
    //        System.out.println("你好insertT");
            return flag;
        }
    
        @ResponseBody
        @RequestMapping(value="/deleteById")
        public boolean deleteById(HttpServletRequest request, HttpServletResponse response){
            List<TTest> ls = new ArrayList<TTest>();
    
            String id = request.getParameter("id");
    //        String name = request.getParameter("name");
            boolean flag = false;
            try{
                tTestMapper.deleteById_T(Long.parseLong(id));
                flag = true;
            }catch (Exception e ){
                e.printStackTrace();
            }
    //        System.out.println("你好delete");
            return flag;
        }
    
        @ResponseBody
        @RequestMapping(value="/updateById_T")
        public boolean updateById_T(HttpServletRequest request, HttpServletResponse response){
    //        List<TTest> ls = new ArrayList<TTest>();
    
            String id = request.getParameter("id");
            String name = request.getParameter("name");
            boolean flag = false;
            try{
                tTestMapper.updateById_T(Long.parseLong(id),name);
                flag = true;
            }catch (Exception e ){
    
                e.printStackTrace();
            }
    //        System.out.println("你好delete");
            return flag;
        }
    
    }
    

    TTestMapper.java

    package com.dv.mpdemo.mapper;
    
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import com.dv.mpdemo.entity.TTest;
    import org.apache.ibatis.annotations.Param;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    
    /**
     * <p>
     *  Mapper 接口
     * </p>
     *
     * @author dv
     * @since 2019-03-28
     */
    public interface TTestMapper extends BaseMapper<TTest> {
    
        public List<TTest> getAlls();
    
        public TTest getTtestById(Long id);
    
        public void insertT(@Param("id") Long id , @Param("name") String name);
    
        public void insertTNotId(@Param("name") String name);
    
        public void deleteById_T(@Param("id") Long id);
    
        public void updateById_T(@Param("id") Long id,@Param("name") String name);
    
        @Select("select * from T_TEST")   //注解的方式
        public List<TTest> getAllByZhuJie();
    
    }
    
    

    Entity:TTest.java

    package com.dv.mpdemo.entity;
    
    import com.baomidou.mybatisplus.annotation.TableName;
    import com.baomidou.mybatisplus.annotation.TableId;
    import com.baomidou.mybatisplus.annotation.TableField;
    import java.io.Serializable;
    import lombok.Data;
    import lombok.EqualsAndHashCode;
    import lombok.experimental.Accessors;
    
    /**
     * <p>
     * 
     * </p>
     *
     * @author dv
     * @since 2019-03-28
     */
    @Data
    @EqualsAndHashCode(callSuper = false)
    @Accessors(chain = true)
    @TableName("T_TEST")
    public class TTest implements Serializable {
    
        private static final long serialVersionUID = 1L;
    
        @TableId("ID")
        private Long id;
    
        @TableField("NAME")
        private String name;
    
    
    }
    
    

    TTestMapper.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.dv.mpdemo.mapper.TTestMapper">
    
        <!-- 通用查询映射结果 -->
        <resultMap id="BaseResultMap" type="com.dv.mpdemo.entity.TTest">
            <id column="ID" property="id" />
            <result column="NAME" property="name" />
        </resultMap>
    
        <select id="getTtestById" resultType="com.dv.mpdemo.entity.TTest" parameterType="Long">
            select * from T_TEST where ID = #{0}
        </select>
    
    
        <select id="getAlls" resultMap="BaseResultMap">
            select * from T_TEST
        </select>
    
        <insert id="insertT" >
            insert into T_TEST(ID,NAME) values(#{id},#{name})
        </insert>
    
        <insert id="insertTNotId" >
            insert into T_TEST(NAME) values(#{name})
        </insert>
    
        <delete id="deleteById_T" parameterType="Long">
            delete from T_TEST where id = #{id}
         </delete>
    
        <update id="updateById_T">
    
            update T_TEST SET NAME=#{name} where ID=#{id}
        </update>
    </mapper>
    
    

    运行MpdemoApplication.java 注意加上注解@MapperScan("com.dv.mpdemo.mapper")
    自动扫面mapper文件

    package com.dv.mpdemo;
    
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    @MapperScan("com.dv.mpdemo.mapper")
    public class MpdemoApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(MpdemoApplication.class, args);
        }
    
    }
    

    运用Postman 发送请求结果:


    10.png
    12.png

    ps:这里只贴出了其中的一个查询和插入,有兴趣的读者可以根据自己具体的项目去测试CRUD
    LZ自己是都测试过了,就不把所有贴出来,已经算是很详细了,哈哈。

    补充两点:1、IDEA需要安装lombok插件 ,实体才能正常使用set get方法
    File->setting->Plugins 搜索lombox install即可


    11.png

    2、oracle自增应用实体插入的方式 问题:以下方式每次id自增都是+2,
    我自己实现的时候:可以删除oracle里面对应的触发器,实现id+1
    有张表T_COUSE test:
    实体中:
    加入注解@KeySequence("SEQ_T_COUSE")里面是对应的序列名
    id->type=IdType.INPUT
    贴出部分代码

            TCouse tc = new TCouse();
            tc.setCousename("数学");
            tc.setCousetype("1");
            tCouseMapper.insert(tc);
    
    /**
     * @author dv
     * @since 2019-03-29
     */
    @Data
    @EqualsAndHashCode(callSuper = false)
    @Accessors(chain = true)
    @TableName("T_COUSE")
    @KeySequence("SEQ_T_COUSE")
    public class TCouse extends Model<TCouse> {
    
    
        @TableId(value = "COUSEID" ,type = IdType.INPUT)
        private Long couseid;
    
         ...
    }
    
    

    至此整个项目完成
    第一次在简书中写文章,写的不好,请多包涵
    有兴趣的朋友可以一起交流
    ly_dv,一个小菜鸟。

    相关文章

      网友评论

          本文标题:Mybatis+Oracle12c+Springboot 201

          本文链接:https://www.haomeiwen.com/subject/xvjlbqtx.html