读取excel文件，并拼接sql到txt文件，java直接调取数据库工具类

之前写过的工具类，愣是找不到了，重新写了一遍，哈卖批的费劲。

package com.example.demo;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author liheping
 * @Description //TODO
 * @Date 2022/2/8 15:33
 **/
public class Heool {

    public static void main(String[] args) throws Exception {
        String filePath = "C:\\Users\\Desktop\\aa.xlsx";
        XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath));
        XSSFSheet sheet = wookbook.getSheet("Sheet1");
        //获取到Excel文件中的所有行数
        int rows = sheet.getPhysicalNumberOfRows();
        //遍历行
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        for (int i = 1; i < rows; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row != null) {
                Map<String, Object> map = new HashMap<String, Object>();
                int cells = row.getPhysicalNumberOfCells();
                XSSFCell nameCell =row.getCell(0);
                String varName = getValue(nameCell);
                System.out.print("品种名称:"+varName+"  ");

                XSSFCell nameCell1 =row.getCell(1);
                String varType = getValue(nameCell1);
                System.out.print("产品类型:"+varType+"  ");

                XSSFCell nameCell2 =row.getCell(2);
                String femaleParent = getValue(nameCell2);
                System.out.print("母本名称:"+femaleParent+"  ");
                XSSFCell nameCell3 =row.getCell(3);
                String maleParent = getValue(nameCell3);
                System.out.print("父本名称:"+maleParent+"  ");
                XSSFCell nameCell5 =row.getCell(5);
                String varietyCode = getValue(nameCell5);
                System.out.print("品种编号:"+varietyCode+"  ");
                XSSFCell nameCell6 =row.getCell(6);
                String femaleParentCode = getValue(nameCell6);
                System.out.print("母本编号:"+femaleParentCode+"  ");
                XSSFCell nameCell7 =row.getCell(7);
                String maleParentCode = getValue(nameCell7);
                System.out.print("父本编号:"+maleParentCode+"  ");
                XSSFCell nameCell9 =row.getCell(9);
                String custcode = getValue(nameCell9);
                System.out.print("合作单位:"+custcode+"  ");
                XSSFCell nameCell11 =row.getCell(11);
                String mature = getValue(nameCell11);
                System.out.println("熟期:"+mature);
                String sql = "insert into t_crm_variety_info (cust_code,var_name,var_type,female_parent,male_parent,variety_code," +
                        "female_parent_code,male_parent_code,mature,create_user,create_org,create_time) VALUES ('"+custcode+"','"+varName+"','"+varType+"','"+femaleParent+"','"+maleParent+"'," +
                    "'"+varietyCode+"','"+femaleParentCode+"','"+maleParentCode+"','"+mature+"','superAdmin','dbnbc','2022-02-09 08:55:54');";
                writeTxtCount("E:\\下载\\aa.txt", sql);

            }
        }

    }

    private static String getValue(XSSFCell xSSFCell) {

        if (null == xSSFCell) {

            return "";

        }
        if (xSSFCell.getCellType() == xSSFCell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(xSSFCell.getBooleanCellValue());
        } else if (xSSFCell.getCellType() == xSSFCell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值
            return String.valueOf(xSSFCell.getNumericCellValue());
        }else if(xSSFCell.getStringCellValue().equals("/")){
            return "";
        }
        else {
            // 返回字符串类型的值
            return String.valueOf(xSSFCell.getStringCellValue());
        }
    }
    private static boolean writeTxtCount(String path,String content) {
        BufferedWriter bufferedWriter = null;
        try {
                bufferedWriter = new BufferedWriter(new FileWriter(path,true));
                bufferedWriter.write(content+ "\r\n");//分行写
//            bufferedWriter.write(content);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                bufferedWriter.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
}

一个java直接调取数据库的工具类，懒得搞那些框架了，直接干

import java.sql.*;

/**
 * @Author liheping
 * @Description //TODO
 * @Date 2022/2/9 11:21
 **/
public class JDBCUtil_new {
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/****?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT&nullCatalogMeansCurrent=true";
    private static final String USER = "root";
    private static final String PASSWORD = "root";
    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn=DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void closeAll(ResultSet rs,PreparedStatement stem,Connection conn) {
        try {
            if(rs!=null) {
                rs.close();
                rs=null;
            }
            if(stem!=null) {
                stem.close();
                stem=null;
            }
            if(conn!=null) {
                conn.close();
                conn=null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static ResultSet executeselect(String sql ) throws SQLException{
        ResultSet rs;
        Connection conn = null;
        PreparedStatement ps = null;
        conn = getConnection();
        try {
            ps=conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                if(rs.getString("cust_code").equals("")){
                    System.out.println("aaa");
                }
                System.out.println(rs.getString("cust_code"));
            }
        } catch (SQLException e) {
            throw e;
        }finally {
            closeAll(null, ps, conn);
        }
        return rs;
    }
}

使用的时候直接

public static void main(String[] args) throws IOException, SQLException {
String sql = "select cust_code from t_crm_customer_info WHERE full_name='"+companyname+"'";

ResultSet r=JDBCUtil_new.executeselect(sql);
}