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>

巴特西

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

工具和环境

效果图如下

结构图如下

sql文件

pom.xml

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

application.properties文件

MiaoshaApplication

controller

service

serviceImpl

dao

dataobject

mapping

css

js

html

最新文章

热门文章