mysql之動(dòng)態(tài)增添字段實(shí)現(xiàn)方式
數(shù)據(jù)庫
--用戶表 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項(xiàng)目,pom內(nèi)容如下:
<?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文件,內(nèi)容如下:
<?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> <!-- 是否去除自動(dòng)生成的注釋 true:是 : false:否 --> <property name="suppressAllComments" value="true"/> </commentGenerator> <!--數(shù)據(jù)庫連接的信息:驅(qū)動(dòng)類、連接地址、用戶名、密碼 --> <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/test" userId="root" password="123456"> </jdbcConnection> <!-- 默認(rèn)false,把JDBC DECIMAL 和 NUMERIC 類型解析為 Integer,為 true時(shí)把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"/> <!-- 從數(shù)據(jù)庫返回的值被清理前后的空格 --> <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> <!-- 指定數(shù)據(jù)庫表 --> <table tableName="users" domainObjectName="Users" schema="public" enableCountByExample="false" enableDeleteByExample="false" enableUpdateByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table> </context> </generatorConfiguration>
這里需要重點(diǎn)注意的不是數(shù)據(jù)庫的連接信息的填寫,這個(gè)用過jdbc的你想必是沒有問題的,重點(diǎn)要關(guān)注的是classPathEntry
,不要以為在pom里面配置了連接mysql的jar包就萬事大吉,這里一定要指定你電腦上jar包所在的絕對(duì)地址才行。
3.指定運(yùn)行方式
工具欄Run
–>Edit Configurations
–>+
–>Maven
Command line : mybatis-generator:generate -e
設(shè)置完成后點(diǎn)OK
,然后就可以運(yùn)行了。
新建springboot項(xiàng)目
使用idea新建springboot項(xiàng)目 File
–>New
–>Project
–>Spring Initializr
……這里比較簡(jiǎn)單,就不細(xì)說了。
在pom.xml中引入相關(guān)依賴:
<?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實(shí)現(xiàn)動(dòng)態(tài)屬性配置</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連接數(shù)據(jù)庫,需要在application.properties中進(jìn)行配置:
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
程序目錄結(jié)構(gòu)如下:
下面陸續(xù)貼出相關(guān)代碼,如對(duì)springboot和mybatis不甚了解,可查閱相關(guān)資料。
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; } }
代碼就這么多,下面啟動(dòng)項(xiàng)目進(jìn)行測(cè)試,我這里使用Postman進(jìn)行接口測(cè)試。
前段可以隨意增添list中的屬性個(gè)數(shù),達(dá)到動(dòng)態(tài)增添字段的效果。
這里做得比較簡(jiǎn)單,實(shí)際使用中可以另建一張表,用來存儲(chǔ)必備的字段,每次新增的時(shí)候都將必備的字段取出來讓用戶填寫,然后其他的再自定義。
遇到的問題
在寫這個(gè)demo以前,思路是很清晰的,沒想到還是遇到不少的問題,首先就是application.properties中配置數(shù)據(jù)庫出錯(cuò),spring.datasource.username
寫錯(cuò)了,導(dǎo)致數(shù)據(jù)庫連接獲取不到,報(bào)錯(cuò)卻為Access denied for user ''@'localhost'
,找了很久才發(fā)現(xiàn)原來是自己粗心導(dǎo)致。
還有就是無論何時(shí),定義了帶參數(shù)的構(gòu)造函數(shù),一定要將無參構(gòu)造函數(shù)寫上,免得后期出錯(cuò)。
總結(jié)
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
Windows(x86,64bit)升級(jí)MySQL 5.7.17免安裝版的詳細(xì)教程
這篇文章主要介紹了Windows(x86,64bit)升級(jí)MySQL 5.7.17免安裝版的詳細(xì)教程,需要的朋友可以參考下2017-02-02Mysql錯(cuò)誤1366 - Incorrect integer value解決方法
這篇文章主要介紹了Mysql錯(cuò)誤1366 - Incorrect integer value解決方法,本文通過修改字段默認(rèn)值解決,需要的朋友可以參考下2014-09-09MySQL里的found_row()與row_count()的解釋及用法
MySQL中有兩個(gè)函數(shù)來計(jì)算上一條語句影響了多少行,不同于SqlServer/Oracle,不要因?yàn)榇朔矫娴牟町惗鸸δ軉栴}2013-02-02mysql數(shù)據(jù)校驗(yàn)過程中的字符集問題處理
在日常應(yīng)用中,我們經(jīng)常會(huì)遇到在不同的字符集的數(shù)據(jù)庫直接進(jìn)行數(shù)據(jù)的導(dǎo)入導(dǎo)出操作,針對(duì)這個(gè)問題,我們來進(jìn)行討論下2014-05-05數(shù)據(jù)庫實(shí)現(xiàn)行列轉(zhuǎn)換(mysql示例)
最近突然玩起了sql語句,想著想著便給自己出了一道題目:“行列轉(zhuǎn)換”。起初瞎折騰了不少時(shí)間也上網(wǎng)參考了一些博文,不過大多數(shù)是采用oracle數(shù)據(jù)庫當(dāng)中的一些便捷函數(shù)進(jìn)行處理,比如”pivot”。那么,在Mysql環(huán)境下如何處理?下面通過這篇文章我們來一起看看吧。2016-12-12Mysql中varchar長(zhǎng)度設(shè)置方法
這篇文章主要介紹了Mysql中varchar長(zhǎng)度設(shè)置方法的相關(guān)資料,本文還給大家?guī)砹藇alar類型的變化及char()和varchar()的區(qū)別介紹,非常不錯(cuò),具有參考借鑒價(jià)值,需要的朋友可以參考下2016-07-07MySQL 5.5 range分區(qū)增加刪除處理的方法示例
這篇文章主要給大家介紹了關(guān)于MySQL 5.5 range分區(qū)增加刪除處理的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來一起看看吧。2017-06-06