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動態增添字段如何實現" 就介紹到此。希望多多支持碩編程。