Java导入csv文件到mysql

package CSV;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.nio.charset.Charset;
import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.SQLException;

import com.csvreader.CsvReader;

import CSV.DBUtil;


public class csv1 {
    public static void main(String[] args) throws IOException, SQLException {

        String path="D:\\data\\school.csv";
        csv(path);
    }
    public static boolean csv(String path) throws IOException, SQLException {
        Connection conn = DBUtil.getConn();
        PreparedStatement state = null;
        int a=0;
        boolean f = false;
        CsvReader reader=new CsvReader(path,',',Charset.forName("gbk"));
        String name=new BufferedReader(new FileReader(new File(path))).readLine();
        System.out.println(name);
        //name就是表格列的名称
        reader.readHeaders();
        int len=reader.getHeaders().length;
        System.out.println(len);
        try {
        //len表示的是有几个列
            while(reader.readRecord()){  //整个while就是为了组装成为 插入语句的形式
                state = conn.prepareStatement("insert into school("+name+") values( ?, ?, ?)");
                for(int i=1;i<len+1;i++){
                    state.setString(i,reader.get(i-1) );
                }
                a=state.executeUpdate();
                if (a > 0) {
                    f = true;
                }
                }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(state, conn);
        }

    
        return f;
    }
}
package db;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;

/**
 * 数据库连接工具
 * @author Hu
 *
 */
public class DBUtil {
    
    public static String db_url = "jdbc:mysql://localhost:3306/javaweb?useSSL=false";
    public static String db_user = "root";
    public static String db_pass = "199126";
    
    public static Connection getConn () {
        Connection conn = null;
        
        try {
            Class.forName("com.mysql.jdbc.Driver");//加载驱动
            conn = DriverManager.getConnection(db_url, db_user, db_pass);
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return conn;
    }
    
    /**
     * 关闭连接
     * @param state
     * @param conn
     */
    public static void close (PreparedStatement state, Connection conn) {
        if (state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    public static void close (ResultSet rs, PreparedStatement state, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        if (state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

 

posted @ 2020-10-09 16:46  张利杰j  阅读(900)  评论(0编辑  收藏  举报