mysql之动态增添字段实现方式

数据库

--用户表
CREATE TABLE `users`  (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `account` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'E10ADC3949BA59ABBE56E057F20F883E',
 `propertyId` int(11) NOT NULL DEFAULT -1,
 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
--属性表
CREATE TABLE `property`  (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `uid` int(11) NOT NULL,
 `key` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
 `value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

mybatis逆向工程

1.使用idea新建maven项目，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>top.changelife</groupId>
   <artifactId>mybatis-generator</artifactId>
   <version>1.0-SNAPSHOT</version>
   <dependencies>
       <dependency>
           <groupId>org.mybatis.generator</groupId>
           <artifactId>mybatis-generator-core</artifactId>
           <version>1.3.6</version>
       </dependency>
       <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <version>5.1.35</version>
       </dependency>
   </dependencies>
   <build>
       <plugins>
           <plugin>
               <groupId>org.mybatis.generator</groupId>
               <artifactId>mybatis-generator-maven-plugin</artifactId>
               <version>1.3.6</version>
               <configuration>
                   <verbose>true</verbose>
                   <overwrite>true</overwrite>
               </configuration>
           </plugin>
       </plugins>
   </build>
</project>

2.在src/main/resource目录下新建geoneratorConfig.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>
   <classPathEntry
           location="C:/Users/35152/.m2/repository/mysql/mysql-connector-java/5.1.35/mysql-connector-java-5.1.35.jar"/>
   <context id="mysqlTables">
       <plugin type="org.mybatis.generator.plugins.SerializablePlugin"></plugin>
       <plugin type="org.mybatis.generator.plugins.EqualsHashCodePlugin"></plugin>
       <plugin type="org.mybatis.generator.plugins.ToStringPlugin"></plugin>
       <commentGenerator>
           <!-- 是否去除自动生成的注释 true：是 ： false:否 -->
           <property name="suppressAllComments" value="true"/>
       </commentGenerator>
       <!--数据库连接的信息：驱动类、连接地址、用户名、密码 -->
       <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                       connectionURL="jdbc:mysql://localhost:3306/test" userId="root"
                       password="123456">
       </jdbcConnection>
       <!-- 默认false，把JDBC DECIMAL 和 NUMERIC 类型解析为 Integer，为 true时把JDBC DECIMAL 和
           NUMERIC 类型解析为java.math.BigDecimal -->
       <javaTypeResolver>
           <property name="forceBigDecimals" value="false"/>
       </javaTypeResolver>
       <!-- targetProject:生成PO类的位置 -->
       <javaModelGenerator targetPackage="top.changelife.dynamicproperty.model"
                           targetProject="./src/main/java">
           <!-- enableSubPackages:是否让schema作为包的后缀 -->
           <property name="enableSubPackages" value="false"/>
           <!-- 从数据库返回的值被清理前后的空格 -->
           <property name="trimStrings" value="true"/>
       </javaModelGenerator>
       <!-- targetProject:mapper映射文件生成的位置 -->
       <sqlMapGenerator targetPackage="top.changelife.dynamicproperty.mapper"
                        targetProject="./src/main/java">
           <!-- enableSubPackages:是否让schema作为包的后缀 -->
           <property name="enableSubPackages" value="false"/>
       </sqlMapGenerator>
       <!-- targetPackage：mapper接口生成的位置 -->
       <javaClientGenerator type="XMLMAPPER"
                            targetPackage="top.changelife.dynamicproperty.dao"
                            targetProject="./src/main/java">
           <!-- enableSubPackages:是否让schema作为包的后缀 -->
           <property name="enableSubPackages" value="false"/>
       </javaClientGenerator>
       <!-- 指定数据库表 -->
       <table tableName="users" domainObjectName="Users" schema="public" enableCountByExample="false"
              enableDeleteByExample="false" enableUpdateByExample="false"
              enableSelectByExample="false" selectByExampleQueryId="false"></table>
   </context>
</generatorConfiguration>

这里需要重点注意的不是数据库的连接信息的填写，这个用过jdbc的你想必是没有问题的，重点要关注的是classPathEntry，不要以为在pom里面配置了连接mysql的jar包就万事大吉，这里一定要指定你电脑上jar包所在的绝对地址才行。

3.指定运行方式

工具栏Run&ndash;>Edit Configurations&ndash;>+&ndash;>Maven

Command line : mybatis-generator:generate -e

设置完成后点OK，然后就可以运行了。

新建springboot项目

使用idea新建springboot项目 File&ndash;>New&ndash;>Project&ndash;>Spring Initializr&hellip;&hellip;这里比较简单，就不细说了。

在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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
   <modelVersion>4.0.0</modelVersion>
   <groupId>top.changelife</groupId>
   <artifactId>dynamic-property</artifactId>
   <version>0.0.1-SNAPSHOT</version>
   <packaging>jar</packaging>
   <name>dynamic-property</name>
   <description>mysql实现动态属性配置</description>
   <parent>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-parent</artifactId>
       <version>2.0.3.RELEASE</version>
       <relativePath/>
   </parent>
   <properties>
       <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
       <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
       <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-test</artifactId>
           <scope>test</scope>
       </dependency>
       <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <version>5.1.35</version>
       </dependency>
       <dependency>
           <groupId>org.mybatis.spring.boot</groupId>
           <artifactId>mybatis-spring-boot-starter</artifactId>
           <version>1.3.0</version>
       </dependency>
   </dependencies>
   <build>
       <plugins>
           <plugin>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-maven-plugin</artifactId>
           </plugin>
       </plugins>
   </build>
</project>

我这里使用mybatis连接数据库，需要在application.properties中进行配置：

spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?username=root
spring.datasource.username=root
spring.datasource.password=1314
mybatis.mapper-locations=classpath:mapper/*Mapper.xml
mybatis.config-location=classpath:mapper/config/sqlMapperConfig.xml

程序目录结构如下：

下面陆续贴出相关代码，如对springboot和mybatis不甚了解，可查阅相关资料。

sqlMapperConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
       PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
       "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
   <typeAliases>
       <typeAlias alias="users" type="top.changelife.dynamicproperty.model.Users"/>
       <typeAlias alias="property" type="top.changelife.dynamicproperty.model.Property"/>
   </typeAliases>
</configuration>

PropertyMapper.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="top.changelife.dynamicproperty.dao.PropertyMapper">
   <insert id="insert" keyProperty="id" useGeneratedKeys="true" parameterType="java.util.List">
       insert into property (uid, property.key,property.value) values
       <foreach collection="list" item="property" separator=",">
           (#{property.uid,jdbcType=INTEGER},
           #{property.key,jdbcType=VARCHAR}, #{property.value,jdbcType=VARCHAR})
       </foreach>
   </insert>
</mapper>

UsersMapper.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="top.changelife.dynamicproperty.dao.UsersMapper">
   <resultMap id="UserResultMap" type="users">
       <id property="id" column="id"/>
       <result column="account" jdbcType="VARCHAR" property="account"/>
       <result column="password" jdbcType="VARCHAR" property="password"/>
       <result column="propertyId" jdbcType="INTEGER" property="propertyId"/>
       <collection property="list" ofType="property">
           <id column="property_id" jdbcType="INTEGER" property="id"/>
           <result column="uid" jdbcType="INTEGER" property="uid"/>
           <result column="key" jdbcType="VARCHAR" property="key"/>
           <result column="value" jdbcType="VARCHAR" property="value"/>
       </collection>
   </resultMap>
   <select id="selectAll" resultMap="UserResultMap">
       SELECT
        u.id AS id,u.account AS account,u.password AS PASSWORD,u.propertyId as propertyId,
        p.id AS property_id,p.uid as uid,p.key AS 'key',p.value AS 'value'
        FROM users u,property p WHERE u.propertyid = p.uid
   </select>
   <insert id="insert" keyProperty="id" useGeneratedKeys="true" parameterType="users">
   insert into users (account, password, propertyId)
   values (#{account,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, #{propertyId,jdbcType=INTEGER})
 </insert>
</mapper>

Users

package top.changelife.dynamicproperty.model;
import java.io.Serializable;
import java.util.List;
public class Users implements Serializable {
   private Integer id;
   private String account;
   private String password;
   private Integer propertyId;
   private List<Property> list;
   private static final long serialVersionUID = 1L;
   public Integer getId() {
       return id;
   }
   public void setId(Integer id) {
       this.id = id;
   }
   public String getAccount() {
       return account;
   }
   public void setAccount(String account) {
       this.account = account == null ? null : account.trim();
   }
   public String getPassword() {
       return password;
   }
   public void setPassword(String password) {
       this.password = password == null ? null : password.trim();
   }
   public Integer getPropertyId() {
       return propertyId;
   }
   public void setPropertyId(Integer propertyId) {
       this.propertyId = propertyId;
   }
   public List<Property> getList() {
       return list;
   }
   public void setList(List<Property> list) {
       this.list = list;
   }
   @Override
   public boolean equals(Object that) {
       if (this == that) {
           return true;
       }
       if (that == null) {
           return false;
       }
       if (getClass() != that.getClass()) {
           return false;
       }
       Users other = (Users) that;
       return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))
               && (this.getAccount() == null ? other.getAccount() == null : this.getAccount().equals(other.getAccount()))
               && (this.getPassword() == null ? other.getPassword() == null : this.getPassword().equals(other.getPassword()))
               && (this.getPropertyId() == null ? other.getPropertyId() == null : this.getPropertyId().equals(other.getPropertyId()));
   }
   @Override
   public int hashCode() {
       final int prime = 31;
       int result = 1;
       result = prime * result + ((getId() == null) ? 0 : getId().hashCode());
       result = prime * result + ((getAccount() == null) ? 0 : getAccount().hashCode());
       result = prime * result + ((getPassword() == null) ? 0 : getPassword().hashCode());
       result = prime * result + ((getPropertyId() == null) ? 0 : getPropertyId().hashCode());
       return result;
   }
   @Override
   public String toString() {
       StringBuilder sb = new StringBuilder();
       sb.append(getClass().getSimpleName());
       sb.append(" [");
       sb.append("Hash = ").append(hashCode());
       sb.append(", id=").append(id);
       sb.append(", account=").append(account);
       sb.append(", password=").append(password);
       sb.append(", propertyid=").append(propertyId);
       sb.append(", list=").append(list);
       sb.append(", serialVersionUID=").append(serialVersionUID);
       sb.append("]");
       return sb.toString();
   }
}

Property

package top.changelife.dynamicproperty.model;
import java.io.Serializable;
public class Property implements Serializable {
   private Integer id;
   private Integer uid;
   private String key;
   private String value;
   private static final long serialVersionUID = 1L;
   public Integer getId() {
       return id;
   }
   public void setId(Integer id) {
       this.id = id;
   }
   public Integer getUid() {
       return uid;
   }
   public void setUid(Integer uid) {
       this.uid = uid;
   }
   public String getKey() {
       return key;
   }
   public void setKey(String key) {
       this.key = key == null ? null : key.trim();
   }
   public String getValue() {
       return value;
   }
   public void setValue(String value) {
       this.value = value == null ? null : value.trim();
   }
   @Override
   public boolean equals(Object that) {
       if (this == that) {
           return true;
       }
       if (that == null) {
           return false;
       }
       if (getClass() != that.getClass()) {
           return false;
       }
       Property other = (Property) that;
       return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))
               && (this.getUid() == null ? other.getUid() == null : this.getUid().equals(other.getUid()))
               && (this.getKey() == null ? other.getKey() == null : this.getKey().equals(other.getKey()))
               && (this.getValue() == null ? other.getValue() == null : this.getValue().equals(other.getValue()));
   }
   @Override
   public int hashCode() {
       final int prime = 31;
       int result = 1;
       result = prime * result + ((getId() == null) ? 0 : getId().hashCode());
       result = prime * result + ((getUid() == null) ? 0 : getUid().hashCode());
       result = prime * result + ((getKey() == null) ? 0 : getKey().hashCode());
       result = prime * result + ((getValue() == null) ? 0 : getValue().hashCode());
       return result;
   }
   @Override
   public String toString() {
       StringBuilder sb = new StringBuilder();
       sb.append(getClass().getSimpleName());
       sb.append(" [");
       sb.append("Hash = ").append(hashCode());
       sb.append(", id=").append(id);
       sb.append(", uid=").append(uid);
       sb.append(", key=").append(key);
       sb.append(", value=").append(value);
       sb.append(", serialVersionUID=").append(serialVersionUID);
       sb.append("]");
       return sb.toString();
   }
}

UserController

package top.changelife.dynamicproperty.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;
import top.changelife.dynamicproperty.dao.PropertyMapper;
import top.changelife.dynamicproperty.dao.UsersMapper;
import top.changelife.dynamicproperty.model.Property;
import top.changelife.dynamicproperty.model.Users;
import java.util.List;
@RestController
public class UserController {
   @Autowired
   UsersMapper usersMapper;
   @Autowired
   PropertyMapper propertyMapper;
   @GetMapping("/users")
   public Object selectAllUsers() {
       return usersMapper.selectAll();
   }
   @PostMapping("/users")
   public Object insertUsers(@RequestBody Users user) {
       List<Property> list = user.getList();
//        System.out.println(list);
       propertyMapper.insert(list);
       usersMapper.insert(user);
       return user;
   }
}

代码就这么多，下面启动项目进行测试，我这里使用Postman进行接口测试。

前段可以随意增添list中的属性个数，达到动态增添字段的效果。

这里做得比较简单，实际使用中可以另建一张表，用来存储必备的字段，每次新增的时候都将必备的字段取出来让用户填写，然后其他的再自定义。

遇到的问题

在写这个demo以前，思路是很清晰的，没想到还是遇到不少的问题，首先就是application.properties中配置数据库出错，spring.datasource.username写错了，导致数据库连接获取不到，报错却为Access denied for user ''@'localhost'，找了很久才发现原来是自己粗心导致。

还有就是无论何时，定义了带参数的构造函数，一定要将无参构造函数写上，免得后期出错。

来源：https://blog.csdn.net/m0_37659871/article/details/80840124