溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
今天小編給大家分享的是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;
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，然后就可以運行了。
使用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動態增添字段怎么實現就分享到這里了，希望以上內容可以對大家有一定的參考價值，可以學以致用。如果喜歡本篇文章，不妨把它分享出去讓更多的人看到。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
