多线程 批处理 数据导入工具 Java

由于在工作中经常需要进行数据的导入…..

调用

package com.lb.impl.qikandaohang.changecls;import java.sql.ResultSet;import java.sql.SQLException;import org.apache.commons.lang3.StringUtils;import com.lb.template.bantchexecute.QueryAndUpdateOneTemplateV2;import com.lb.util.Database;public class ChageImpactFactor extends QueryAndUpdateOneTemplateV2 {    public ChageImpactFactor(Database srcDb, Database descDb) {        super(srcDb, descDb);    }    public static void main(String[] args) {        Database srcDb = new Database("sa", "123456", "jdbc:sqlserver://192.168.2.44:1433;DatabaseName=Ixuehsu", com.lb.util.Constants.SQLSERVER_DRIVER);        Database desc = new Database("sa", "123456", "jdbc:sqlserver://192.168.2.203:1433;DatabaseName=DB_wulixi", com.lb.util.Constants.SQLSERVER_DRIVER);        String querySql = "SELECT ISSN, [封面], [综合影响因子] FROM [期刊封面]";        QueryAndUpdateOneTemplateV2 template = new ChageImpactFactor(srcDb, desc);        template.execute(querySql);    }    @Override    public String getOprationSql(ResultSet resultSet) throws SQLException {        String issn = resultSet.getString("ISSN").trim();        String cover = getCover(resultSet.getString("封面"));        String impactfactory = getImfactory(resultSet.getString("综合影响因子"));        if (StringUtils.isNotBlank(issn)) {            String updateSql = "UPDATE wlx_journal SET cover = '" + cover + "', impact_factory = '" + impactfactory + "' WHERE ISSN LIKE '%" + issn + "%';";            return updateSql;        }        return null;    }    private String getImfactory(String string) {        if (StringUtils.isNotBlank(string)) {            return string;        } else {            return "0.0";        }    }    private static String getCover(String string) {        try {            String url = string.substring(string.indexOf("/") + 1, string.indexOf("."));            return url;        } catch (Exception e) {            return "";        }    }}

package com.lb.template.bantchexecute;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.concurrent.ArrayBlockingQueue;import java.util.concurrent.BlockingQueue;import java.util.concurrent.atomic.AtomicInteger;import org.apache.commons.lang3.StringUtils;import com.lb.util.DBManager;import com.lb.util.Database;public abstract class QueryAndUpdateOneTemplateV2 {    private DBManager srcDbMg;    private DBManager descDbMg;    private static AtomicInteger totalCount = new AtomicInteger();    /**     * 一次执行多少条 sql     */    private static final int SQL_BUFFER_COUNT = 1;    /**     * 最大线程数     */    private static final int MAX_THREAD_COUNT = 1;    private static final String END_FLAG = "_END_";    private BlockingQueue<String> sqlsQueue = new ArrayBlockingQueue<>(10000);    public QueryAndUpdateOneTemplateV2(Database srcDb, Database descDb) {        this.srcDbMg = new DBManager(srcDb.getUserName(), srcDb.getPassword(), srcDb.getUrl(), srcDb.getDriver());        this.descDbMg = new DBManager(descDb.getUserName(), descDb.getPassword(), descDb.getUrl(), descDb.getDriver());    }    public void execute(String querySql) {        long start = System.currentTimeMillis();        Reader reader = new Reader(querySql);        reader.start();        for (int i = 0; i < MAX_THREAD_COUNT; i++) {            Writer writer = new Writer();            writer.start();        }        long end = System.currentTimeMillis();        // System.out.println("[修改完成], 共修改:" + totalCount.get() + ",耗时：" + (end - start) + ",毫秒");    }    /**     * sql 读取者     * @author admin     *     */    private class Reader extends Thread{        private String querySql;        public Reader(String querySql) {            this.querySql = querySql;        }        @Override        public void run() {            System.out.println("读取线程启动....");            Connection srcConn = srcDbMg.getConnection();            int queryCount = 0;            if (StringUtils.isBlank(querySql)) {                System.out.println("查询语句不为空");                return;            }            try {                PreparedStatement pstm = srcConn.prepareStatement(querySql);                ResultSet resultSet = pstm.executeQuery();                System.out.println("读取线程 开始读取...");                while (resultSet.next()) {                    addOneSqlToBuffer(resultSet, sqlsQueue);                    queryCount += 1;                }                sqlsQueue.put(END_FLAG);                System.out.println("读取完成, 共生成sql:" + queryCount + "次");            } catch (Exception e) {                e.printStackTrace();            }        }    }    /**     * sql 写入者     *     * @author admin     *     */    private class Writer extends Thread{        private List<String> sqlBuffer = new ArrayList<String>();        private int executeCount;        @Override        public void run() {            System.out.println("写入线程:" + Thread.currentThread().getName() + "启动...");            int count = 0;            while(true) {                try {                    String sql = sqlsQueue.take();                    if (StringUtils.isNotBlank(sql) && sql.equals(END_FLAG)) {                        sqlsQueue.put(END_FLAG);                        break;                    }                    sqlBuffer.add(sql);                    count += 1;                    if (count >= SQL_BUFFER_COUNT) {                        long start1 = System.currentTimeMillis();                        descDbMg.executes(sqlBuffer);                        long end1 = System.currentTimeMillis();                        executeCount += SQL_BUFFER_COUNT;                        System.out.println(Thread.currentThread().getName() + "已修改:" + executeCount + ", 耗时：" + (end1 - start1));                        totalCount.addAndGet(SQL_BUFFER_COUNT);                        sqlBuffer.clear();                        count = 0;                    }                } catch (InterruptedException e) {                    e.printStackTrace();                    sqlBuffer.clear();                    count = 0;                    continue;                }            }            if (sqlBuffer.size() != 0) {                descDbMg.executes(sqlBuffer);                executeCount = executeCount + sqlBuffer.size();            }            System.out.println(Thread.currentThread().getName() + "[修改完成], 共修改:" + executeCount);        }    }    private void addOneSqlToBuffer(ResultSet resultSet, BlockingQueue<String> sqlsQueue) throws SQLException {        String sql = getOprationSql(resultSet);        if (StringUtils.isNotBlank(sql)) {            try {                sqlsQueue.put(sql);            } catch (InterruptedException e) {                e.printStackTrace();            }        }    }    /**     * 通过 查询结果集 生成 操作语句     *     * @param resultSet     * @return     * @throws SQLException     */    public abstract String getOprationSql(ResultSet resultSet) throws SQLException;}

---

package com.lb.util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.List;import java.util.Map;public class DBManager {    private String userName;    private String password;    private String url;    private String driver;    public DBManager(String userName, String password, String url, String driver) {        this.userName = userName;        this.password = password;        this.url = url;        this.driver = driver;    }    public Connection getConnection() {        Connection conn = null;        try {            Class.forName(driver);            conn = DriverManager.getConnection(url, userName, password);        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        }        return conn;    }    public void close(ResultSet rs, Statement statement, Connection conn) {        if (rs != null) {            try {                rs.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (statement != null) {            try {                statement.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (conn != null) {            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }    public void close(ResultSet rs, PreparedStatement ps, Connection conn) {        if (rs != null) {            try {                rs.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (ps != null) {            try {                ps.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if (conn != null) {            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }    /**     * 批量执行多条 sqls, 一条失败全部回滚     */    public void executes(List<String> sqls) {        Connection conn = null;        Statement statement = null;        conn = getConnection();        // 禁用自动提交        try {            statement = conn.createStatement();            conn.setAutoCommit(false);            for (String sql : sqls) {                statement.addBatch(sql);            }            statement.executeBatch();            // 提交事务            conn.commit();            conn.setAutoCommit(true);        } catch (SQLException e) {            for (String sql : sqls) {                System.out.println(sql);            }            System.out.println("出错了");            e.printStackTrace();            if (conn != null) {                // 事务回滚                try {                    conn.rollback();                } catch (SQLException e1) {                    e1.printStackTrace();                }            }        } finally {            close(null, statement, conn);        }    }    /**     * 执行多条 一个模板的多条sql语句, 一条失败全部回滚 (测试没通过)     *     * String : sql     * Object[] : params     */    public void batchExecuteByTempalte(String sqlTemplate, List<Object[]> params) throws RuntimeException{        Connection conn = null;        PreparedStatement ps = null;        conn = getConnection();        // 禁用自动提交        try {            ps = conn.prepareStatement(sqlTemplate);            conn.setAutoCommit(false);            int paramSize = params.size();            Object[] param = null;            for (int i = 0; i < paramSize; i++) {                param = params.get(i);                for (int j = 0; j < param.length; j++) {                    ps.setObject(j + 1, param[j]);                }                ps.addBatch();            }            ps.executeBatch();            conn.commit();            conn.setAutoCommit(true);        } catch (SQLException e) {            if (conn != null) {                // 事务回滚                try {                    conn.rollback();                    throw new RuntimeException();                } catch (SQLException e1) {                    e1.printStackTrace();                }            }        } finally {            close(null, ps, conn);        }    }    /**     * 执行多条 sqls, 一条失败全部回滚     * sql 不可重复     * String : sql     * Object[] : params     */    public void executeWithParams(Map<String, Object[]> sqlMap) throws RuntimeException{        Connection conn = null;        PreparedStatement ps = null;        conn = getConnection();        // 禁用自动提交        try {            conn.setAutoCommit(false);            for (Map.Entry<String, Object[]> entry : sqlMap.entrySet()) {                String sql = entry.getKey();                ps = conn.prepareStatement(sql);                Object[] params = entry.getValue();                for (int i = 0; i < params.length; i++) {                    ps.setObject(i + 1, params[i]);                }                ps.execute();            }            // 提交事务            conn.commit();        } catch (SQLException e) {            if (conn != null) {                // 事务回滚                try {                    conn.rollback();                    throw new RuntimeException();                } catch (SQLException e1) {                    e1.printStackTrace();                }            }        } finally {            close(null, ps, conn);        }    }    /**     * Map<Object[] : params     * String: String     *     * @param sqlMaps     * @throws RuntimeException     */    public void executeWithParamsNew(Map<Object[], String> sqlMap) throws RuntimeException{        Connection conn = null;        PreparedStatement ps = null;        conn = getConnection();        String errorSql = "";        // 禁用自动提交        try {            conn.setAutoCommit(false);            for (Map.Entry<Object[], String> entry : sqlMap.entrySet()) {                String sql =  entry.getValue();                errorSql = sql;                ps = conn.prepareStatement(sql);                Object[] params = entry.getKey();                for (int i = 0; i < params.length; i++) {                    ps.setObject(i + 1, params[i]);                }                ps.execute();            }            // 提交事务            conn.commit();        } catch (SQLException e) {            e.printStackTrace();            System.out.println("error sql is:" + errorSql);            if (conn != null) {                // 事务回滚                try {                    conn.rollback();                    throw new RuntimeException();                } catch (SQLException e1) {                    e1.printStackTrace();                }            }        } finally {            close(null, ps, conn);        }    }    /**     * 适合单表     *     * Map<Object[] : params     * String: String     *     * @param sqlMaps     * @throws RuntimeException     */    public void bantchExecuteOneTable(Map<Object[], String> sqlMap) throws RuntimeException{        Connection conn = null;        PreparedStatement ps = null;        conn = getConnection();        // 禁用自动提交        try {            conn.setAutoCommit(false);            for (Map.Entry<Object[], String> entry : sqlMap.entrySet()) {                String sql =  entry.getValue();                ps = conn.prepareStatement(sql);                Object[] params = entry.getKey();                for (int i = 0; i < params.length; i++) {                    ps.setObject(i + 1, params[i]);                }                ps.addBatch();            }            ps.executeBatch();            // 提交事务            conn.commit();            conn.setAutoCommit(true);        } catch (SQLException e) {            e.printStackTrace();            if (conn != null) {                // 事务回滚                try {                    conn.rollback();                    throw new RuntimeException();                } catch (SQLException e1) {                    e1.printStackTrace();                }            }        } finally {            close(null, ps, conn);        }    }}

---

package com.lb.util;/** * 数据库对象 * * @author admin * */public class Database {    private String userName;    private String password;    private String url;    private String driver;    public Database() {}    public Database(String userName, String password, String url, String driver) {        super();        this.userName = userName;        this.password = password;        this.url = url;        this.driver = driver;    }    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;    }    public String getUrl() {        return url;    }    public void setUrl(String url) {        this.url = url;    }    public String getDriver() {        return driver;    }    public void setDriver(String driver) {        this.driver = driver;    }}