java操作mysql数据库

java操作mysql数据库

package com.executemysql;

import java.sql.*;
import java.util.ArrayList;
import java.util.UUID;
import com.executemysql.tools.TimeStamp;

public class InsertPurchaseorderplanstores {

    public static void main(String[] args) throws SQLException {
        /*
            提供一个订货计划id，插入所有供应商商品的门店订货数（目前是pre环境DT-01店）
            这里的store_id是写死的,如果供应商商品的这个门店不存在或者不可订货的话，那么插入的这条数据就是垃圾数据
            1.getSuppliers：先根据订货计划id查询可订货供应商商品
            2.setSupplierModel：设置供应商商品的订货类型
            3.insertStores：根据查询到的供应商数据插入门店数据（订货数）
         */

        String purchase_order_plan_id = "96f18ea0-cd29-4490-b832-f558c9fc12e3";
        try {
            ResultSet suppliers = getSuppliers(getConnection(), purchase_order_plan_id);
            setSupplierModel(getConnection(), purchase_order_plan_id, 20);
            insertStores(getConnection(), purchase_order_plan_id, suppliers);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            System.out.println("数据库连接失败");
        } finally {
            getConnection().close();
        }
    }

    public static Connection getConnection() {// 数据库连接
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");// 加载Mysql数据驱动
            conn = DriverManager.getConnection(
                    "*****");// 创建数据连接
            return conn;
        } catch (Exception e) {
            System.out.println(e);
            System.out.println("数据库连接失败");
        }
        return conn;
    }

    public static ResultSet getSuppliers(Connection conn, String purchase_order_plan_id) throws SQLException {
        String suppliers_select_sql = "select * from pupu_oms.purchaseorderplansuppliers where purchase_order_plan_id = '" + purchase_order_plan_id + "'";
        Statement stmt = conn.createStatement();
        return stmt.executeQuery(suppliers_select_sql);
    }

    public static void setSupplierModel(Connection conn, String purchase_order_plan_id, int Model) throws SQLException {
        String suppliers_update_sql = "update pupu_oms.purchaseorderplansuppliers set supply_model = " + Model + " where purchase_order_plan_id = '" + purchase_order_plan_id + "'";
        Statement stmt = conn.createStatement();
        stmt.execute(suppliers_update_sql);
        System.out.println("修改供应商订货类型为：" + Model + "，修改成功");
    }

    public static void setCityOrderCreatTime(Connection conn, ResultSet supplierRow, long time_create) throws SQLException {
        ArrayList<String> supplierIds = new ArrayList<>();
        time_create = TimeStamp.getTimeStampAddSelf(time_create);
        while (supplierRow.next()) {
            String purchaseSupplierId = supplierRow.getString(1);
            supplierIds.add(purchaseSupplierId);
        }
        for (int i = 0; i < supplierIds.size(); i++) {
            String suppliers_update_sql = "update pupu_oms.purchaseorderplansuppliers set time_create = " + time_create + " where id = '" + supplierIds.get(i) + "'";
            Statement stmt = conn.createStatement();
            stmt.execute(suppliers_update_sql);
            System.out.println("修改订货计划供应商id:" + supplierIds.get(i) + "的time_create为:" + time_create);
        }
    }

    public static void insertStores(Connection conn, String purchase_order_plan_id, ResultSet supplierRow) throws SQLException {
        String stores_insert_sql = "INSERT INTO pupu_oms.purchaseorderplanstores (id,purchase_order_plan_id,purchase_order_plan_supplier_id,product_id,supplier_id,supplier_product_id,store_id,store_product_id,count_plan_pur,count_plan_sale,sum_cost,time_create,time_update,user_id_create,user_id_update,has_deleted,count_card_plan) VALUES ";
        Statement stmt = conn.createStatement();
        ArrayList<String> purchaseSupplierIds = new ArrayList<>();
        ArrayList<String> productIds = new ArrayList<>();
        ArrayList<String> supplierIds = new ArrayList<>();
        ArrayList<String> supplierProductIds = new ArrayList<>();
        while (supplierRow.next()) { //获取每一行的数据
            String purchaseSupplierId = supplierRow.getString(1);
            String productId = supplierRow.getString(4);
            String supplierId = supplierRow.getString(5);
            String supplierProductId = supplierRow.getString(6);
            purchaseSupplierIds.add(purchaseSupplierId);
            productIds.add(productId);
            supplierIds.add(supplierId);
            supplierProductIds.add(supplierProductId);
        }
        for (int i = 0; i < purchaseSupplierIds.size(); i++) {
            String stores_insert_sql_value = "('" + UUID.randomUUID().toString() + "','" + purchase_order_plan_id + "','" + purchaseSupplierIds.get(i) + "','" + productIds.get(i) + "','" + supplierIds.get(i) + "','" + supplierProductIds.get(i) + "','c1d01085-d22d-40f8-890e-15de56da9ca0','xxxxxxxxxxxxxx',10,10,12000,1655457780780,1655457780780,'d1d52c6e-0090-4d2c-8c97-46f581d738a0','d1d52c6e-0090-4d2c-8c97-46f581d738a0',0,0),";
            stores_insert_sql += stores_insert_sql_value;
//            stmt.execute(stores_insert_sql); //单条插入,也可以如上拼成一条sql插入
            System.out.println("------门店表第" + (i + 1) + "条数据插入成功------");
        }
        stores_insert_sql = stores_insert_sql.substring(0, stores_insert_sql.length() - 1);
        stmt.execute(stores_insert_sql); //拼成一条sql插入
        System.out.println("========门店表共插入" + purchaseSupplierIds.size() + "条数据========");
    }
}