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>