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

吾爱主题阅读：229 2023-05-16 15:43:00 评论：0

数据库

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 --用户表 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内容如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 <? 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文件，内容如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 <? 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 >  < property name = "suppressAllComments" value = "true" /> </ commentGenerator >  < jdbcConnection driverClass = "com.mysql.jdbc.Driver" connectionURL = "jdbc:mysql://localhost:3306/test" userId = "root" password = "123456" > </ jdbcConnection >  < javaTypeResolver > < property name = "forceBigDecimals" value = "false" /> </ javaTypeResolver >  < javaModelGenerator targetPackage = "top.changelife.dynamicproperty.model" targetProject = "./src/main/java" >  < property name = "enableSubPackages" value = "false" />  < property name = "trimStrings" value = "true" /> </ javaModelGenerator >  < sqlMapGenerator targetPackage = "top.changelife.dynamicproperty.mapper" targetProject = "./src/main/java" >  < property name = "enableSubPackages" value = "false" /> </ sqlMapGenerator >  < javaClientGenerator type = "XMLMAPPER" targetPackage = "top.changelife.dynamicproperty.dao" targetProject = "./src/main/java" >  < 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–>Edit Configurations–>+–>Maven

Command line : mybatis-generator:generate -e

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

新建springboot项目

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

在pom.xml中引入相关依赖：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 <? 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中进行配置：

1 2 3 4 5 6 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

1 2 3 4 5 6 7 8 9 10 <? 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

1 2 3 4 5 6 7 8 9 10 11 <? 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 <? 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 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'，找了很久才发现原来是自己粗心导致。

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