2023.4.13每日总结

完成使用jdbc实现使用excel批量插入数据到数据库

package wangzhan;

import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
 
 
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
 
public class ReadExcelFile {
 
    public static String TAG="EXCELToSQL";
 
    private static final String mSQLDriver = "com.mysql.jdbc.Driver";
 
    private static final String UserName="root";
    private static final String PassWord="qq2665410029";
    private static final String Url="jdbc:mysql://localhost:3306/pddabc?useSSL=false";
    private static Connection conSQL;
    private static String excelpath="C:\\Users\\王磊\\Desktop\\test.xls";
    private static Sheet sheet0=null;
    private static String val_property=null;
    private static String val_intfname=null;
 
 
    public static void main(String[] args){
 
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {
                    //加载驱动
                    Class.forName(mSQLDriver);
 
                    //建立连接
                    conSQL = DriverManager.getConnection(Url,UserName,PassWord);
                    
                    String classpath = this.getClass().getResource("/").getPath().replaceFirst("/", "");
                    classpath = URLDecoder.decode(classpath,"utf-8");
                    System.out.println(classpath);
                    String webappRoot = classpath.replaceAll("build/classes/", "");
                    System.out.println(webappRoot);
                    String fileName = webappRoot + "src/main/webapp/test.xls";
                    System.out.println(fileName);
                    excelpath=fileName;
                    sheet0 = readSheet(excelpath,0);
                    int rows=sheet0.getRows();
                    for (int h=0; h<rows; h++){
                        val_property=readRow(sheet0,h)[0];
                        val_intfname=readRow(sheet0,h)[1];
 
                        String sql="INSERT Read_My_Excel(property,intfname) VALUES('"+ val_property +
                                "','"+ val_intfname + "');";
                        //创建statement对象，用来执行SQL语句
                        Statement st = conSQL.createStatement();
 
 
                        st.executeUpdate(sql);
                    }
 
                }catch (Exception e){
                    e.printStackTrace();
                }
 
 
 
            }
        }).start();
 
    }
 
//由于我的EXCEL中有好几张表，因此本方法实现的是获取Sheet对象
    public static Sheet readSheet(String filepath, int sheetcount){
        Sheet sheet=null;
 
        if ( null != filepath && !"".equals(filepath.trim())){
            Workbook workbook= null;
            InputStream inputStream = null;
            try {
                inputStream= new FileInputStream(filepath);
                workbook = Workbook.getWorkbook(inputStream);
                if (null == workbook){
                    return null;
                }
                sheet = workbook.getSheet(sheetcount);
 
                if (null == sheet){
                    return null;
                }else{
                    return sheet;
                }
 
 
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                if (null != inputStream){
                    try{
                        inputStream.close();
                    }catch (Exception e){
                        e.printStackTrace();
                    }
 
                }
            }
 
        }
        return sheet;
 
    }
//对一张表实现对第j条记录的读取，并存为数组结构
    public static String[] readRow(Sheet sheet, int j){
        int k = 0;
        String[] array= new String[2];
        int sheet_row = sheet.getRows();
        for( k=0; k<sheet_row; k++ ){
            if(j == k){
                Cell[] cells=sheet.getRow(j);
                array[0]=cells[0].getContents();
                array[1]=cells[1].getContents();
                break;
            }
        }
        return array;
 
    }
}