欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

mysql之動(dòng)態(tài)增添字段實(shí)現(xiàn)方式

 更新時(shí)間:2023年05月15日 15:01:01   作者:侖小杰  
這篇文章主要介紹了mysql之動(dòng)態(tài)增添字段實(shí)現(xiàn)方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

數(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ì)教程

    這篇文章主要介紹了Windows(x86,64bit)升級(jí)MySQL 5.7.17免安裝版的詳細(xì)教程,需要的朋友可以參考下
    2017-02-02
  • Mysql錯(cuò)誤1366 - Incorrect integer value解決方法

    Mysql錯(cuò)誤1366 - Incorrect integer value解決方法

    這篇文章主要介紹了Mysql錯(cuò)誤1366 - Incorrect integer value解決方法,本文通過修改字段默認(rèn)值解決,需要的朋友可以參考下
    2014-09-09
  • mysql獲取group by總記錄行數(shù)的方法

    mysql獲取group by總記錄行數(shù)的方法

    這篇文章主要介紹了mysql獲取group by總記錄行數(shù)的方法,主要通過group by SQL_CALC_FOUND_ROWS語句來實(shí)現(xiàn)該功能,具有一定的實(shí)用價(jià)值,需要的朋友可以參考下
    2014-10-10
  • mysql 8.0.20 winx64安裝配置方法圖文教程

    mysql 8.0.20 winx64安裝配置方法圖文教程

    這篇文章主要為大家詳細(xì)介紹了mysql 8.0.20 winx64安裝配置方法圖文教程,文中示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2020-05-05
  • MySQL里的found_row()與row_count()的解釋及用法

    MySQL里的found_row()與row_count()的解釋及用法

    MySQL中有兩個(gè)函數(shù)來計(jì)算上一條語句影響了多少行,不同于SqlServer/Oracle,不要因?yàn)榇朔矫娴牟町惗鸸δ軉栴}
    2013-02-02
  • mysql數(shù)據(jù)校驗(yàn)過程中的字符集問題處理

    mysql數(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
  • Mysql中強(qiáng)大的group?by語句解析

    Mysql中強(qiáng)大的group?by語句解析

    這篇文章主要介紹了Mysql中強(qiáng)大的group?by語句解析,GROUP?BY?語句根據(jù)一個(gè)或多個(gè)列對(duì)結(jié)果集進(jìn)行分組。在分組的列上我們可以使用?COUNT,?SUM,?AVG,等函數(shù),需要的朋友可以參考下
    2023-07-07
  • 數(shù)據(jù)庫實(shí)現(xiàn)行列轉(zhuǎn)換(mysql示例)

    數(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-12
  • Mysql中varchar長(zhǎng)度設(shè)置方法

    Mysql中varchar長(zhǎng)度設(shè)置方法

    這篇文章主要介紹了Mysql中varchar長(zhǎng)度設(shè)置方法的相關(guān)資料,本文還給大家?guī)砹藇alar類型的變化及char()和varchar()的區(qū)別介紹,非常不錯(cuò),具有參考借鑒價(jià)值,需要的朋友可以參考下
    2016-07-07
  • MySQL 5.5 range分區(qū)增加刪除處理的方法示例

    MySQL 5.5 range分區(qū)增加刪除處理的方法示例

    這篇文章主要給大家介紹了關(guān)于MySQL 5.5 range分區(qū)增加刪除處理的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來一起看看吧。
    2017-06-06

最新評(píng)論