通过java导入csv文件到oracle（mysql）数据库

<pre name="code" class="java">上一篇文章讲了如何利用java将oracle（mysql）数据库中的数据导出到csv文件，这篇文章我们将把上篇文章中的csv文件导入到数据库中。

本程序能解决的问题是将csv文件导入数据库，可以将数据库中原记录删除后导入csv文件中的记录，也可以通过建立临时表（原表以"_tmp"结尾）将csv文件中的数据合并到目标表中。

需要注意的是：

csv文件中的日期格式在dateutil.java文件中调整，如果日期格式不对将会设置为计算机起始时间。

csv文件中要有表头，如果没有表头需要人为添加表头（添加表头的代码没有上传）；

csv文件可以以逗号结束，也可以不以逗号结束；

本程序的测试方法用到了<span style="font-family: Arial, Helvetica, sans-serif;">guessTableName</span>方法，需要根据个人情况调整。

本程序中用到了将一些特殊字符串替换，可以自己屏蔽掉，不屏蔽也应该不影响。

入库比导出相对麻烦些了。

package com.blog.laishaobin.db;import java.sql.Connection;import java.util.ArrayList;import java.util.List;import org.apache.commons.io.FilenameUtils;import com.blog.laishaobin.form.CSVImportForm;public class BlogCSVToTable {public static void main(String[] args) {List<String> lstable = new ArrayList<String>();List<CSVImportForm> csvFormList = new ArrayList<CSVImportForm>();lstable.add("C:\\Users\\John\\workspace\\blog\\T_TEERIE_FLOW_RELATE_20150523.csv");lstable.add("C:\\Users\\John\\workspace\\blog\\T_TEERIE_IDENTIFY_20150523.csv");for (String s : lstable) {CSVImportForm form = new CSVImportForm(s, CSVImportForm.INSERTTYPE, guessTableName(s));csvFormList.add(form);}try {new BlogCSVToTable(csvFormList);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}//根据文件路径获取表名（根据个人情况修改本函数）private static String guessTableName(String localFilePath) {String tableName = null;tableName = FilenameUtils.getBaseName(localFilePath);tableName = tableName.substring(0, tableName.length() - 9);return tableName;}public BlogCSVToTable(List<CSVImportForm> csvFormList) throws Exception {for(CSVImportForm c : csvFormList) {try {if (c.isInsert()) {Connection conn = new DBConnections(DBConnections.URL, DBConnections.USERNAME, DBConnections.PASSWORD).getConn();CSVLoader csvLoader = new CSVLoader(conn);csvLoader.setSeprator(c.getSeprator());csvLoader.loadCSV(c.getLocalFilePath(), c.getTableName(), true);} else if (c.isUpdate()) {Connection conn = new DBConnections(DBConnections.URL, DBConnections.USERNAME, DBConnections.PASSWORD).getConn();CSVLoader csvLoader = new CSVLoader(conn);csvLoader.setSeprator(c.getSeprator());csvLoader.loadCSV(c.getLocalFilePath(), getTempTable(c.getTableName()), true);conn = new DBConnections(DBConnections.URL, DBConnections.USERNAME, DBConnections.PASSWORD).getConn();new MergeInto(getTempTable(c.getTableName()), c.getTableName(), "SYS_SEQ_NO", null, null, conn);}}catch (Exception e) {e.printStackTrace();throw new Exception(e.getMessage());}}}private String getTempTable(String tableName) {return tableName.toUpperCase() + "_TMP";}}

package com.blog.laishaobin.form;public class CSVImportForm {public static final String INSERTTYPE = "01";public static final String UPDATETYPE = "02";//文件路径String localFilePath;//文件分隔符char seprator = ',';//入库类型String sqlType;//数据库表名String tableName;/** * @param localFilePath * 本地文件路径 * @param sqlType * INSERTTYPE  or   UPDATETYPE * @param tableName * 数据库表名 */public CSVImportForm(String localFilePath,String sqlType, String tableName) {this.localFilePath = localFilePath;this.sqlType = sqlType;this.tableName = tableName;}/** * @param localFilePath * 本地文件路径 * @param sqlType * INSERTTYPE or UPDATETYPE * @param tableName * 数据库表名 * @param seprator * 文件分割符 */public CSVImportForm(String localFilePath,String sqlType, String tableName, char seprator) {this.localFilePath = localFilePath;this.sqlType = sqlType;this.tableName = tableName;this.seprator = seprator;}public char getSeprator() {return seprator;}public void setSeprator(char seprator) {this.seprator = seprator;}public String getTableName() {return tableName;}public void setTableName(String tableName) {this.tableName = tableName;}public String getLocalFilePath() {return localFilePath;}public void setLocalFilePath(String localFilePath) {this.localFilePath = localFilePath;}public String getSqlType() {return sqlType;}public void setSqlType(String sqlType) {this.sqlType = sqlType;}public boolean isInsert() {return sqlType.equals(INSERTTYPE);}public boolean isUpdate() {return sqlType.equals(UPDATETYPE);}}

package com.blog.laishaobin.db;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/** * @author John * */public class MergeInto {private String from;private String to;private String joinField;private String insertCondition;private String updteCondition;private Connection conn;/** * @param from * 源表 * @param to * 目标表 * @param joinField * join字段 * @param insertCondition * insert的条件，可以为null， 不带where * @param updateCondition * update的条件， 可以为null， 不带where * @throws Exception  */public MergeInto(String from, String to, String joinField, String insertCondition, String updateCondition, Connection conn) throws Exception {this.from = from;this.to = to;this.joinField = joinField;if (insertCondition != null) {this.insertCondition = " where " + insertCondition + " ";} elsethis.insertCondition = " ";if (updateCondition != null) {this.updteCondition = " where " + updateCondition + " ";} else {this.updteCondition = " ";}this.conn = conn;merge();}private void merge() throws Exception {Statement stmt = conn.createStatement();String sql = "merge into " + to;sql += " using " + from;sql += " on (" + to + "." + joinField + " = " + from + "." + joinField + ") ";sql += "when matched then update set " + generateSetSql(to, from, conn, joinField);sql += updteCondition;sql += "when not matched then " + generateInsertSql(to, from, conn);sql += insertCondition;System.out.println(sql);stmt.executeQuery(sql);stmt.close();conn.close();}private String generateInsertSql(String to, String from, Connection conn) throws SQLException {Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);String[] fields = getTableTitle(to, stmt);String resultStr = "insert (";for (int i = 0; i < fields.length; i++) {resultStr += (to + "." + fields[i]);if (i != fields.length - 1) {resultStr += ",";} else {resultStr += ")";}}resultStr += " values(";for (int i = 0; i < fields.length; i++) {resultStr += (from + "." + fields[i]);if (i != fields.length - 1) {resultStr += ",";} elseresultStr += ")";}return resultStr;}/** * @param tableName * 数据库表名 * @param conn * 数据库连接 * @return 表名title的数组 * @throws SQLException */public static String[] getTableTitle(String tableName, Statement stmt) throws SQLException {String sql = "select column_name from user_tab_columns where table_name = '" + tableName.toUpperCase() + "'";System.out.println(sql);ResultSet rs = stmt.executeQuery(sql);int count = 0;while (rs.next()) {count++;}System.out.println(count);rs.first();String[] array;array = new String[count];int i = 0;do {array[i] = rs.getString(1);i++;} while (rs.next());stmt.close();return array;}private String generateSetSql(String to, String from, Connection conn, String exclusiveField) throws SQLException {Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);String[] fields = getTableTitle(to, stmt);String resultStr = "";for (int i = 0; i < fields.length; i++) {if (!fields[i].equals(exclusiveField)){resultStr += mergeTwoStringWithSetFormat(to, from, fields[i]);if (i != fields.length -1)resultStr += " , ";}}return resultStr;}private String mergeTwoStringWithSetFormat(String alias1, String alias2, String field) {return (alias1 + "." + field + "=" + alias2 + "." + field + " ");}public String getFrom() {return from;}public void setFrom(String from) {this.from = from;}public String getTo() {return to;}public void setTo(String to) {this.to = to;}public String getJoinField() {return joinField;}public void setJoinField(String joinField) {this.joinField = joinField;}public String getInsertCondition() {return insertCondition;}public void setInsertCondition(String insertCondition) {this.insertCondition = insertCondition;}public String getUpdteCondition() {return updteCondition;}public void setUpdteCondition(String updteCondition) {this.updteCondition = updteCondition;}}

package com.blog.laishaobin.db; import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List; public class DateUtil {     // List of all date formats that we want to parse.    // Add your own format here.    private static List<SimpleDateFormat>            dateFormats = new ArrayList<SimpleDateFormat>() {/** *  */private static final long serialVersionUID = 1L;{            add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"));            add(new SimpleDateFormat("yyyyMMdd"));            add(new SimpleDateFormat("yyyy-MM-dd"));        }    };     /**     * Convert String with various formats into java.util.Date     *      * @param input     *            Date as a string     * @return java.util.Date object if input string is parsed      *          successfully else returns null     */    public static Date convertToDate(String input) {        Date date = null;        if(null == input) {            return null;        }        for (SimpleDateFormat format : dateFormats) {            try {                format.setLenient(false);                date = format.parse(input);            } catch (ParseException e) {                //Shhh.. try other formats            }            if (date != null) {                break;            }        }         return date;    }}

package com.blog.laishaobin.db;import java.sql.Connection;import java.sql.DriverManager;public class DBConnections {public static final String URL = "jdbc:oracle:thin:@192.168.1.179:1521:orcl";public static final String USERNAME = "blog";public static final String PASSWORD = "laishaobin";private String url;private String username;private String password;public DBConnections(String url, String username, String password) {this.url = url;this.username = username;this.password = password;}public Connection getConn() throws Exception {Connection conn = null;try {Class.forName("oracle.jdbc.driver.OracleDriver");            conn = DriverManager.getConnection(url, username, password);            conn.setAutoCommit(false);} catch (Exception e) {e.printStackTrace();throw new Exception(e.getMessage());}        return conn;}public String getUrl() {return url;}public void setUrl(String url) {this.url = url;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}}

package com.blog.laishaobin.db; import java.io.FileNotFoundException;import java.io.FileReader;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.Statement;import java.sql.Types;import java.util.ArrayList;import java.util.Arrays;import java.util.Date; import java.util.List;import org.apache.commons.lang3.StringUtils;import com.opencsv.CSVReader;  /** *  * @author viralpatel.net *  */public class CSVLoader {     private static final        String SQL_INSERT = "INSERT INTO ${table}(${keys}) VALUES(${values})";    private static final String TABLE_REGEX = "\\$\\{table\\}";    private static final String KEYS_REGEX = "\\$\\{keys\\}";    private static final String VALUES_REGEX = "\\$\\{values\\}";     private Connection connection;    private char seprator;     /**     * Public constructor to build CSVLoader object with     * Connection details. The connection is closed on success     * or failure.     * @param connection     */    public CSVLoader(Connection connection) {        this.connection = connection;        //Set default separator        this.seprator = ',';    }         /**     * Parse CSV file using OpenCSV library and load in      * given database table.      * @param csvFile Input CSV file     * @param tableName Database table name to import data     * @param truncateBeforeLoad Truncate the table before inserting      *          new records.     * @throws Exception     */    public void loadCSV(String csvFile, String tableName,            boolean truncateBeforeLoad) throws Exception {         CSVReader csvReader = null;        if(null == this.connection) {            throw new Exception("Not a valid connection.");        }        try {                         csvReader = new CSVReader(new FileReader(csvFile), this.seprator);         } catch (Exception e) {            e.printStackTrace();            throw new Exception("Error occured while executing file. "                    + e.getMessage());        }         String[] headerRow = csvReader.readNext(); //        if (null == headerRow) {//            throw new FileNotFoundException(//                    "No columns defined in given CSV file." +//                    "Please check the CSV file format.");//        }        if (null == headerRow) {        return;        }                int realLength = headerRow.length;                if (headerRow[realLength - 1].isEmpty())        realLength--;                String[] newHeaderRow = new String[realLength];                for (int i = 0; i < realLength; i++) {        newHeaderRow[i] = headerRow[i];        }                        Statement stmt = connection.createStatement();        ResultSet rs = stmt.executeQuery("select * from " + tableName);        ResultSetMetaData rsmd = rs.getMetaData();        int[] types = new int[rsmd.getColumnCount()];        for (int i = 0; i < rsmd.getColumnCount(); i++) {        types[i] = rsmd.getColumnType(i+1);        }        stmt.close();                if (types.length != realLength) {        throw new Exception("表结构和文件中的不一致！ 表：" + tableName.toUpperCase());        }                String questionmarks = StringUtils.repeat("?,", newHeaderRow.length);        questionmarks = (String) questionmarks.subSequence(0, questionmarks                .length() - 1);         String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);        query = query                .replaceFirst(KEYS_REGEX, StringUtils.join(newHeaderRow, ","));        query = query.replaceFirst(VALUES_REGEX, questionmarks);         String[] nextLine;        Connection con = null;        PreparedStatement ps = null;        try {            con = this.connection;            con.setAutoCommit(false);            ps = con.prepareStatement(query);             if(truncateBeforeLoad) {                //delete data from table before loading csv                con.createStatement().execute("DELETE FROM " + tableName);            }             final int batchSize = 1000;            int count = 0;            Date date = null;            while ((nextLine = csvReader.readNext()) != null) {                 if (null != nextLine && nextLine.length >= realLength) {                    int index = 1;                    for (String string : nextLine) {                    if (index <= realLength) {                    if (types[index-1] == Types.TIMESTAMP) {                    date = DateUtil.convertToDate(string);                        if (null != date) {                            ps.setTimestamp(index++, new java.sql.Timestamp(date                                    .getTime()));                        } else {                        //日期转换失败时使用最初时间                        ps.setTimestamp(index++, new java.sql.Timestamp(0));                        }                    } else {                    string = string.replaceAll("&%&", ",");    //这里进行了特殊字符串替换，因为我导出是进行了替换                    string = string.replaceAll("&#&", "\n");                    ps.setString(index++, string);                    }                    }                    }                    ps.addBatch();                }                if (++count % batchSize == 0) {                    ps.executeBatch();                }            }            ps.executeBatch(); // insert remaining records            con.commit();        } catch (Exception e) {            con.rollback();            e.printStackTrace();            throw new Exception(                    "Error occured while loading data from file to database."                            + e.getMessage());        } finally {            if (null != ps)                ps.close();            if (null != con)                con.close();             csvReader.close();        }    }     public char getSeprator() {        return seprator;    }     public void setSeprator(char seprator) {        this.seprator = seprator;    } }