mysql动态增添字段怎么实现
mysql动态增添字段怎么实现
本文讲解"mysql动态增添字段如何实现"，希望能够解决相关问题。
目录- 数据库
- mybatis逆向工程
- 新建springboot项目
- 遇到的问题
- 总结
数据库
--用户表 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–>edit configurations–>+–>maven
command line : mybatis-generator:generate -e
设置完成后点ok，然后就可以运行了。
新建springboot项目
使用idea新建springboot项目 file–>new–>project–>spring initializr……这里比较简单，就不细说了。
在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'，找了很久才发现原来是自己粗心导致。
还有就是无论何时，定义了带参数的构造函数，一定要将无参构造函数写上，免得后期出错。
关于 "mysql动态增添字段如何实现" 就介绍到此。希望多多支持硕编程。
学习MySQL