• springboot+mybatis+mysql 利用mybatis自动生成sql语句


    工具和环境

    idea,mysql,JDK1.8

    效果图如下

    结构图如下

    java

     resources

    sql文件

    /*
    Navicat MySQL Data Transfer
    
    Source Server         : localhost_3306
    Source Server Version : 50562
    Source Host           : localhost:3306
    Source Database       : miaosha
    
    Target Server Type    : MYSQL
    Target Server Version : 50562
    File Encoding         : 65001
    
    Date: 2020-07-06 20:34:49
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for user_info
    -- ----------------------------
    DROP TABLE IF EXISTS `user_info`;
    CREATE TABLE `user_info` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(64) NOT NULL DEFAULT '',
      `gender` int(11) NOT NULL DEFAULT '0' COMMENT '//1代表男性,2代表女性',
      `age` int(11) NOT NULL,
      `telphone` varchar(0) NOT NULL DEFAULT '',
      `register_mode` varchar(0) NOT NULL DEFAULT '' COMMENT '//byphone,bywechat,byalipay',
      `third_party_id` varchar(64) NOT NULL DEFAULT '',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    
    -- ----------------------------
    -- Records of user_info
    -- ----------------------------
    INSERT INTO `user_info` VALUES ('1', '王王', '1', '12', '', '', '');
    INSERT INTO `user_info` VALUES ('2', 'ww', '0', '21', '', '', '');
    
    -- ----------------------------
    -- Table structure for user_password
    -- ----------------------------
    DROP TABLE IF EXISTS `user_password`;
    CREATE TABLE `user_password` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `encrpt_password` varchar(128) NOT NULL DEFAULT '',
      `user_id` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    
    -- ----------------------------
    -- Records of user_password
    -- ----------------------------

    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.3.1.RELEASE</version>
            <relativePath/> <!-- lookup parent from repository -->
        </parent>
        <groupId>com.example</groupId>
        <artifactId>demo</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>miaosha</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-starter-thymeleaf</artifactId>
                <version>2.1.0.RELEASE</version>
            </dependency>
            <!--热部署-->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-devtools</artifactId>
                <optional>true</optional>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.38</version>
            </dependency>
            <!--连接池-->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.10</version>
            </dependency>
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.5</version>
            </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>
                <plugin>
                    <groupId>org.mybatis.generator</groupId>
                    <artifactId>mybatis-generator-maven-plugin</artifactId>
                    <version>1.3.5</version>
                    <dependencies>
                        <dependency>
                            <groupId>mysql</groupId>
                            <artifactId>mysql-connector-java</artifactId>
                            <version>5.1.39</version>
                        </dependency>
                        <dependency>
                            <groupId>org.mybatis.generator</groupId>
                            <artifactId>mybatis-generator-core</artifactId>
                            <version>1.3.5</version>
                        </dependency>
                    </dependencies>
                    <executions>
                        <execution>
                            <id>Generate MyBatis Artifacts</id>
                            <phase>package</phase>
                            <goals>
                                <goal>generate</goal>
                            </goals>
                        </execution>
                    </executions>
                    <configuration>
                        <!--允许移动生成的文件 -->
                        <verbose>true</verbose>
                        <!-- 是否覆盖 -->
                        <overwrite>true</overwrite>
                        <!-- 自动生成的配置 -->
                        <configurationFile>
                            src/main/resources/mybatis-generator.xml
                        </configurationFile>
                    </configuration>
                </plugin>
            </plugins>
        </build>
       
    </project>

    自动生成映射关系的mybatis-generator.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE generatorConfiguration
            PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
            "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
    <generatorConfiguration>
        <context id="DB2Tables" targetRuntime="MyBatis3">
            <commentGenerator>
                <property name="suppressDate"  value="true"/>
                <property name="suppressAllComments" value="true"/>
            </commentGenerator>
            <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://127.0.0.1:3306/miaosha" userId="root" password="magicimage"></jdbcConnection>
            <javaTypeResolver>
                <property name="forceBigDecimals" value="false"/>
            </javaTypeResolver>
            <!-- 生成dataObject的包名和位置 -->
            <javaModelGenerator targetPackage="com.miaoshaproject.dataobject" targetProject="src/main/java">
                <property name="enableSbuPackages" value="true"/>
                <property name="trimStrings" value="true"/>
            </javaModelGenerator>
            <!-- 映射文件生成所在的目录为每一个数据库的表生成对应的SqlMap文件 -->
            <sqlMapGenerator targetPackage="mapping" targetProject="src/main/resources">
                <property name="enableSubPackages" value="true"/>
            </sqlMapGenerator>
            <!-- 生成DAO的包名和位置 -->
            <javaClientGenerator type="XMLMAPPER" targetPackage="com.miaoshaproject.dao" targetProject="src/main/java">
                <property name="enableSubPackages" value="true"/>
            </javaClientGenerator>
            <!--tableName是数据库中表的名字,domainObjectName是实体的名字-->
            <table tableName="user_info" domainObjectName="UserDO" enableCountByExample="false"
                   enableUpdateByExample="false" enableDeleteByExample="false"
                   enableSelectByExample="false" selectByExampleQueryId="false"></table>
            <table tableName="user_password" domainObjectName="UserPasswordDO" enableCountByExample="false"
                   enableUpdateByExample="false" enableDeleteByExample="false"
                   enableSelectByExample="false" selectByExampleQueryId="false"></table>
        </context>
    </generatorConfiguration>

    application.properties文件

    server.port=8090
    
    #数据库
    spring.datasouce.name=miaosha
    spring.datasource.url=jdbc:mysql://127.0.0.1:3306/miaosha?useUnicode=true&characterEncoding=utf-8
    spring.datasource.username=root
    spring.datasource.password=magicimage
    
    #druid
    spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
    spring.datasource.driverClassName=com.mysql.jdbc.Driver
    
    #mybatis
    mybatis.mapper-locations=classpath:mapping/*.xml
    mybatis.type-aliases-package=com.miaoshaproject.dataobject
    
    #模板
    spring.thymeleaf.cache=false
    spring.thymeleaf.encoding=utf-8
    spring.thymeleaf.mode=HTML5
    spring.thymeleaf.prefix=classpath:/templates/
    spring.thymeleaf.suffix=.html
    
    #指定某些文件不进行监听,即不会进行热加载
    spring.devtools.restart.exclude=application.properties
    spring.devtools.restart.enabled=true   
    spring.devtools.restart.additional-paths=src/main/java 

    MiaoshaApplication

    @SpringBootApplication(scanBasePackages={"com.miaoshaproject.*"})
    @MapperScan("com.miaoshaproject.dao")
    public class MiaoshaApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(MiaoshaApplication.class, args);
        }
    }

    controller

    @Controller
    @RequestMapping("/")
    public class Forward {
        @RequestMapping("/index")
        public String index() {
            return "/index";
        }
    }


    @RestController
    public class UserController {
    
        @Autowired
        private UserService userService;
    
        @RequestMapping("/getUser")
         public UserDO getName(Integer id) {
              UserDO user = userService.getName(id);
                  System.out.println(user.getName());
                  return user;
            }
    }

    service

    public interface  UserService {
    
        UserDO getName(Integer id);
    }

    serviceImpl

    @Service
    public class UserServiceImpl implements UserService {
    
        @Autowired
        private UserDOMapper userDOMapper;
    
        @Override
        public UserDO getName(Integer id) {
            return userDOMapper.selectByPrimaryKey(id);
        }
    }

    dao

    public interface UserDOMapper {
        int deleteByPrimaryKey(Integer id);
    
        int insert(UserDO record);
    
        int insertSelective(UserDO record);
    
        UserDO selectByPrimaryKey(Integer id);
    
        int updateByPrimaryKeySelective(UserDO record);
    
        int updateByPrimaryKey(UserDO record);
    }

    dataobject

    package com.miaoshaproject.dataobject;
    
    public class UserDO {
        private Integer id;
    
        private String name;
    
        private Integer gender;
    
        private Integer age;
    
        private String telphone;
    
        private String registerMode;
    
        private String thirdPartyId;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name == null ? null : name.trim();
        }
    
        public Integer getGender() {
            return gender;
        }
    
        public void setGender(Integer gender) {
            this.gender = gender;
        }
    
        public Integer getAge() {
            return age;
        }
    
        public void setAge(Integer age) {
            this.age = age;
        }
    
        public String getTelphone() {
            return telphone;
        }
    
        public void setTelphone(String telphone) {
            this.telphone = telphone == null ? null : telphone.trim();
        }
    
        public String getRegisterMode() {
            return registerMode;
        }
    
        public void setRegisterMode(String registerMode) {
            this.registerMode = registerMode == null ? null : registerMode.trim();
        }
    
        public String getThirdPartyId() {
            return thirdPartyId;
        }
    
        public void setThirdPartyId(String thirdPartyId) {
            this.thirdPartyId = thirdPartyId == null ? null : thirdPartyId.trim();
        }
    }

    mapping

    <?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.miaoshaproject.dao.UserDOMapper">
      <resultMap id="BaseResultMap" type="com.miaoshaproject.dataobject.UserDO">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="gender" jdbcType="INTEGER" property="gender" />
        <result column="age" jdbcType="INTEGER" property="age" />
        <result column="telphone" jdbcType="VARCHAR" property="telphone" />
        <result column="register_mode" jdbcType="VARCHAR" property="registerMode" />
        <result column="third_party_id" jdbcType="VARCHAR" property="thirdPartyId" />
      </resultMap>
      <sql id="Base_Column_List">
        id, name, gender, age, telphone, register_mode, third_party_id
      </sql>
      <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select 
        <include refid="Base_Column_List" />
        from user_info
        where id = #{id,jdbcType=INTEGER}
      </select>
      <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete from user_info
        where id = #{id,jdbcType=INTEGER}
      </delete>
      <insert id="insert" parameterType="com.miaoshaproject.dataobject.UserDO">
        insert into user_info (id, name, gender, 
          age, telphone, register_mode, 
          third_party_id)
        values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{gender,jdbcType=INTEGER}, 
          #{age,jdbcType=INTEGER}, #{telphone,jdbcType=VARCHAR}, #{registerMode,jdbcType=VARCHAR}, 
          #{thirdPartyId,jdbcType=VARCHAR})
      </insert>
      <insert id="insertSelective" parameterType="com.miaoshaproject.dataobject.UserDO">
        insert into user_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
          <if test="id != null">
            id,
          </if>
          <if test="name != null">
            name,
          </if>
          <if test="gender != null">
            gender,
          </if>
          <if test="age != null">
            age,
          </if>
          <if test="telphone != null">
            telphone,
          </if>
          <if test="registerMode != null">
            register_mode,
          </if>
          <if test="thirdPartyId != null">
            third_party_id,
          </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
          <if test="id != null">
            #{id,jdbcType=INTEGER},
          </if>
          <if test="name != null">
            #{name,jdbcType=VARCHAR},
          </if>
          <if test="gender != null">
            #{gender,jdbcType=INTEGER},
          </if>
          <if test="age != null">
            #{age,jdbcType=INTEGER},
          </if>
          <if test="telphone != null">
            #{telphone,jdbcType=VARCHAR},
          </if>
          <if test="registerMode != null">
            #{registerMode,jdbcType=VARCHAR},
          </if>
          <if test="thirdPartyId != null">
            #{thirdPartyId,jdbcType=VARCHAR},
          </if>
        </trim>
      </insert>
      <update id="updateByPrimaryKeySelective" parameterType="com.miaoshaproject.dataobject.UserDO">
        update user_info
        <set>
          <if test="name != null">
            name = #{name,jdbcType=VARCHAR},
          </if>
          <if test="gender != null">
            gender = #{gender,jdbcType=INTEGER},
          </if>
          <if test="age != null">
            age = #{age,jdbcType=INTEGER},
          </if>
          <if test="telphone != null">
            telphone = #{telphone,jdbcType=VARCHAR},
          </if>
          <if test="registerMode != null">
            register_mode = #{registerMode,jdbcType=VARCHAR},
          </if>
          <if test="thirdPartyId != null">
            third_party_id = #{thirdPartyId,jdbcType=VARCHAR},
          </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
      </update>
      <update id="updateByPrimaryKey" parameterType="com.miaoshaproject.dataobject.UserDO">
        update user_info
        set name = #{name,jdbcType=VARCHAR},
          gender = #{gender,jdbcType=INTEGER},
          age = #{age,jdbcType=INTEGER},
          telphone = #{telphone,jdbcType=VARCHAR},
          register_mode = #{registerMode,jdbcType=VARCHAR},
          third_party_id = #{thirdPartyId,jdbcType=VARCHAR}
        where id = #{id,jdbcType=INTEGER}
      </update>
    </mapper>

    css

    p,label{
            color: green;
         }

    js

    $(document).ready(function(){
        $("button#aa").click(function(){
            //var jsonObj = {id: $("#id").val(),name:"abcd",age:"123"};
            $.ajax({
                async : false,
                url: "/getUser",
                data:{
                    id:$("#id").val()
                },
                //data:JSON.stringify(jsonObj),  传入json数据
                type: "POST",
                dataType:"json",//返回数据类型
                //headers:{"Content-Type":"application/json"},  传入json数据时需设置请求头
                success:function(data){
                    alert(data.name);
                },
                error:function(err){
                    alert('连接失败');
                }
            });
        });
    });

    html

    <!DOCTYPE HTML>
    <html xmlns="http://www.w3.org/1999/xhtml"
          xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="utf-8"/>
        <title>测试网站t</title>
        <link th:href="@{/css/index.css}" rel="stylesheet" type="text/css"/>
        <script type="text/javascript" src="https://cdn.staticfile.org/jquery/1.10.2/jquery.min.js"></script>
        <script type="text/javascript" th:src="@{/js/index.js}"></script>
    </head>
    <body>
    <p>
        <label class="name">t用户id:</label>
        <input placeholder="输入id" type="text" class="text-box" id="id"/>
        <button id="aa">按钮</button>
    </p>
    </body>
    </html>
  • 相关阅读:
    HTML5 与 CSS3 jQuery部分知识总结【转】
    JS中检测数据类型的几种方式及优缺点【转】
    PHP 小数点保留两位【转】
    ES6中的const命令【转】
    bootstrap学习笔记【转】
    让一个元素垂直水平居中的三种方法【转】
    JavaScript笔试必备语句【转】
    网站优化——前端部分【转】
    移动App服务端架构设计
    IE6浏览器常见的bug及其修复方法
  • 原文地址:https://www.cnblogs.com/wangxue1314/p/13257354.html
Copyright © 2020-2023  润新知