java申请提交多个表单数据 java多表增删查

转载

mob64ca140b0bc8 2024-03-02 09:17:23

文章标签 java申请提交多个表单数据 java mysql spring sql 文章分类 Java 后端开发

主要思路就是拼接建表语句，需要注意的是数值类型以及日期类型拼接方式。
直接亮代码

1、连接数据库

package com.jss.wx.config;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

@Slf4j
@Component
@Data
public class CreateTable {

    private static CreateTable createTable;
    private static String driverClassName;
    private static String url;
    private static String username;
    private static String password;

    /**
     * 初始化注入的 service
     */
    @PostConstruct
    public void init() {
        createTable = this;
//        createTable.sysMeterService = this.sysMeterService;
    }


    @Value("${spring.datasource.driver-class-name}")
    public void setDriverClassName(String driverClassName) {
        CreateTable.driverClassName = driverClassName;
    }

    @Value("${spring.datasource.url}")
    public void setUrl(String url) {
        CreateTable.url = url;
    }

    @Value("${spring.datasource.username}")
    public void setUsername(String username) {
        CreateTable.username = username;
    }

    @Value("${spring.datasource.password}")
    public void setPassword(String password) {
        CreateTable.password = password;
    }


    /**
     * 连接MySQL数据库
     */
    public static Map<String, Object> connectMySQL() {
        Connection connection = null;
        Statement statement = null;
        Map<String, Object> map = new HashMap<>();
        try {
            // 连接数据库
            Class.forName(driverClassName);
            // 获取数据库连接
            connection = DriverManager.getConnection(url, username, password);
            // 根据连接获取可执行Statement
            statement = connection.createStatement();

            map.put("connection", connection);
            map.put("statement", statement);
            return map;
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("CreateTable 类中，连接数据库错误！");
        }
        return null;
    }
}

2、实体类

package com.jss.wx.entity;

import lombok.Builder;
import lombok.Data;
import java.util.List;

@Data
@Builder
public class SysMeter {

    private String tableName;
    private List<SysMeterAttached> meterInfo;
    private String sql;
}

package com.jss.wx.entity;

import lombok.Builder;
import lombok.Data;

@Data
@Builder
public class SysMeterAttached {
    private String fieldName;
    private String fieldLength;
    private String fieldType;
    private String primaryKey;
    private String isNull;
    private String fieldRemark;
    private String decimalPoint;
}

3、具体方法

package com.jss.wx.service;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.jss.wx.dao.TestDao;
import com.jss.wx.entity.SysMeter;
import com.jss.wx.entity.SysMeterAttached;
import org.springframework.stereotype.Service;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import static com.jss.wx.config.CreateTable.connectMySQL;

/**
 * @author shenJiang
 * @description:TODO
 * @date 2022/11/2214:25
 * @Version 1.0
 */

@Service
public class TestCreateTableServiceImpl extends ServiceImpl<TestDao, SysMeter> {


    /*String*/
    public static final String TYPE_STRING = "String";

    /*Integer*/
    public static final String TYPE_INTEGER = "Integer";

    /*Double*/
    public static final String TYPE_DOUBLE = "Double";

    /*Date*/
    public static final String TYPE_Date = "Date";

    /*BigDecimal*/
    public static final String TYPE_BigDecimal = "BigDecimal";

    /*text*/
    public static final String TYPE_text = "text";


    /**
     * 执行SQL语句
     *
     * @param
     * @return
     */
    public String executeSQL(SysMeter sysMeter) throws SQLException, IOException {
        Connection conn = null;
        Statement state = null;
        try {
            Map<String, Object> map = connectMySQL();

            if (map.get("connection") == null || map.get("statement") == null) {
                return "获取数据库连接失败...";
            }
            // 获取数据库连接
            conn = (Connection) map.get("connection");
            // 获取SQL执行环境
            state = (Statement) map.get("statement");
            // 获取数据库表名


            ResultSet tables = conn.getMetaData().getTables(null, null, sysMeter.getTableName(), null);
//            // 是否存在表,如果表存在，删除表在进行重新创建？
            if (tables.next()) {
//
                String copy = sysMeter.getTableName() + "copy";
                sysMeter.setTableName(copy);

            }
            String sql = createSQL(sysMeter);
            System.out.println("--------------创建表sql开始---------------");

//          创建表

            System.out.println(sql);
            if (0 == state.executeUpdate(sql)) {
                System.out.println("成功创建表！");
            } else {
                System.out.println("创建表失败！");
            }
            System.out.println("--------------创建表sql结束---------------");


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            state.close();
            conn.close();
        }
        return "表" + "同步成功";

    }


    public static boolean existsTable(String tableName) throws SQLException {
        Connection conn = null;
        Statement state = null;
        // 获取数据库连接和SQL执行环境
        try {
            Map<String, Object> map = connectMySQL();
            if (map.get("connection") == null || map.get("statement") == null) {
                System.out.println("数据库连接失败");
                return false;
            }
            // 获取数据库连接
            conn = (Connection) map.get("connection");
            // 获取SQL执行环境
            state = (Statement) map.get("statement");
            // 获取数据库表名
            ResultSet tables = conn.getMetaData().getTables(null, null, tableName, null);
            if (tables.next()) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("验证数据库表名是否能存在错误！");
            return true;
        } finally {
            state.close();
            conn.close();
        }
        return false;
    }

    /**
     * 创建生成表的SQL
     *
     * @param sysMeter
     * @return
     */
    public static String createSQL(SysMeter sysMeter) {
        // 创建主键集合
        List<String> priKeyList = new ArrayList<String>();
        // 创建 StringBuffer 拼接sql
        StringBuffer sb = new StringBuffer();
        //我么要执行创建表的DDl语句
        sb.append("CREATE TABLE `" + sysMeter.getTableName() + "` (\n");
        List<SysMeterAttached> meterInfo = sysMeter.getMeterInfo();

        for (int i = 0; i < meterInfo.size(); i++) {
            // 当前条数据
            SysMeterAttached sma = meterInfo.get(i);
            // 判断数据类型
            String fieldType = judgeDataType(sma.getFieldType());
            sb.append("" + sma.getFieldName() + "");
            if ("double".equals(fieldType)) {
                // 特殊处理 `age` double(23,0) DEFAULT NULL COMMENT '年龄',
                sb.append(" " + fieldType + "(" + sma.getFieldLength() + "," + sma.getDecimalPoint() + ") ");
            } else if ("decimal".equals(fieldType)) {
                sb.append(" " + fieldType + "(" + sma.getFieldLength() + "," + sma.getDecimalPoint() + ") ");
            } else if ("datetime".equals(fieldType)) {
                /*如果是datetime类型，不添加FieldLength*/
                sb.append(" " + fieldType + " ");
            } else {
                sb.append(" " + fieldType + "(" + sma.getFieldLength() + ") ");
            }


            // 判断是否为主键 - 等于1是主键
            if ("1".equals(sma.getPrimaryKey())) {
                // 字段名称放进去
                priKeyList.add(sma.getFieldName());
                // 判断是否允许为空 等于1是允许为空; 只有不为空的时候，需要设置
                if (!"1".equals(sma.getIsNull())) {
                    sb.append("NOT NULL COMMENT '" + sma.getFieldRemark() + "',\n");
                }
                // 如果到了最后一条，并且只有一个主键时
                if (i >= meterInfo.size() - 1 && priKeyList.size() == 1) {
                    sb.append("PRIMARY KEY (`" + priKeyList.get(0) + "`)");
                    sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");

                } else if (i >= meterInfo.size() - 1 && priKeyList.size() > 1) {
                    // 最后一条，并且存在多个主键时
                    sb.append("PRIMARY KEY (");
                    // 遍历主键集合
                    for (int j = 0; j < priKeyList.size(); j++) {
                        // 最后一个时
                        if (j == priKeyList.size() - 1) {
                            sb.append("`" + priKeyList.get(j) + "`) USING BTREE \n");
                        } else {
                            sb.append("`" + priKeyList.get(j) + "`,");
                        }
                    }
                    sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
                }
                // 非主键，直接判断是否允许为空
            } else {
                // 存在主键，并且为最后一个了
                if (priKeyList.size() > 0 && i >= meterInfo.size() - 1) {
                    // 判断是否为空 if是可以为空
                    if ("1".equals(sma.getIsNull())) {
                        sb.append("DEFAULT NULL COMMENT '" + sma.getFieldRemark() + "',\n");
                    } else {
                        sb.append("NOT NULL COMMENT '" + sma.getFieldRemark() + "',\n");
                    }
                    // 表示只有一个主键
                    if (priKeyList.size() == 1) {
                        sb.append("PRIMARY KEY (`" + priKeyList.get(0) + "`)\n");
                        sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
                    } else {
                        // 最后一条，并且存在多个主键时
                        sb.append("PRIMARY KEY (");
                        // 遍历主键集合
                        for (int j = 0; j < priKeyList.size(); j++) {
                            // 最后一个时
                            if (j == priKeyList.size() - 1) {
                                sb.append("`" + priKeyList.get(j) + "`) USING BTREE \n");
                            } else {
                                sb.append("`" + priKeyList.get(j) + "`,");
                            }
                        }
                        sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
                    }
                } else {
                    // 没有就追加 判断是否为空
                    if ("1".equals(sma.getIsNull())) {
                        sb.append("DEFAULT NULL COMMENT '" + sma.getFieldRemark() + "',\n");
                    } else {
                        sb.append("NOT NULL COMMENT '" + sma.getFieldRemark() + "',\n");
                    }
                }
            }
        }
        return sb.toString();
    }

    /**
     * 删除表SQL
     *
     * @param
     * @return
     */
    public String dropTable(SysMeter sysMeter) {
        Connection conn = null;
        Statement state = null;
        try {
            Map<String, Object> map = connectMySQL();

            if (map.get("connection") == null || map.get("statement") == null) {
                return "获取数据库连接失败...";
            }
            // 获取数据库连接
            conn = (Connection) map.get("connection");
            // 获取SQL执行环境
            state = (Statement) map.get("statement");
            // 获取数据库表名


            ResultSet tables = conn.getMetaData().getTables(null, null, sysMeter.getTableName(), null);
            // 是否存在表
            if (tables.next()) {
                System.out.println("--------------删除表sql开始---------------");
                String sql = "DROP TABLE " + sysMeter.getTableName() + "";
                state.executeUpdate(sql);
                System.out.println("表：" + sysMeter.getTableName());
                System.out.println("--------------删除表sql结束---------------");
                return "表：" + sysMeter.getTableName() + "删除成功";
            } else {
                return "表不存在";
            }
        } catch (Exception e) {
            e.printStackTrace();
            return "--------------删除表sql失败---------------";

        }
    }

    public String executeQuery(SysMeter sysMeter) {
        Connection conn = null;
        Statement state = null;
        try {
            Map<String, Object> map = connectMySQL();

            if (map.get("connection") == null || map.get("statement") == null) {
                return "获取数据库连接失败...";
            }
            // 获取数据库连接
            conn = (Connection) map.get("connection");
            // 获取SQL执行环境
            state = (Statement) map.get("statement");
            // 获取数据库表名
            String sql = sysMeter.getSql();
            ResultSet result = state.executeQuery(sql);

            /*如果还有数据*/
            StringBuffer stringBuffer = new StringBuffer();
            while (result.next()) {
                ResultSetMetaData metaData = result.getMetaData();  //获取列集
                int columnCount = metaData.getColumnCount(); //获取列的数量
                for (int i = 0; i < columnCount; i++) { //循环列

                    String columnName = metaData.getColumnName(i + 1); //通过序号获取列名,起始值为1
                    String columnValue = result.getString(columnName);  //通过列名获取值.如果列值为空,columnValue为null,不是字符型
                    stringBuffer.append(columnName).append(":").append(columnValue).append("、");
                }
                stringBuffer.append("\n");
            }

            return stringBuffer.toString();
        } catch (SQLException e) {
            e.printStackTrace();
            return "--------------查询失败---------------";
        }
    }

    /**
     * 判断类型
     * varchar
     * int
     * double
     * datetime
     * decimal
     * text
     *
     * @param type
     * @return
     */
    private static String judgeDataType(String type) {
        switch (type) {
            case TYPE_STRING:
                return "varchar";
            case TYPE_INTEGER:
                return "int";
            case TYPE_DOUBLE:
                return "double";
            case TYPE_Date:
                return "datetime";
            case TYPE_BigDecimal:
                return "decimal";
            case TYPE_text:
                return "text";
            default:
                return "varchar";
        }
    }
}

写在最后，目前只实现了增删查，改的功能没有实现，如果有大佬实现了，可以贴在评论区，拜谢。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。