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(); } } } }