工具 mysqlToSqlite

package util;
import java.sql.*;

public class MysqlToSqlite {
    public void importData(String tableName, String sqlitePath) throws SQLException {
        Connection mysqlconn = null;
        Statement statement = null;
        ResultSet rs = null;
        Connection sqliteconn = null;
        PreparedStatement preStatement = null;
        try {
            //MySQL数据库、
            mysqlconn = getMysqlconn();
            statement =mysqlconn.createStatement();
            rs = statement.executeQuery("select * from "+tableName);

            //结果集获取到的长度,即表有多少字段
            int size = rs.getMetaData().getColumnCount();
            //拼接插入语句
            StringBuffer sbf =new StringBuffer();
            sbf.append("insert into "+tableName+" values (");
            String link ="";
            for (int i = 0; i <size ; i++) {
                sbf.append(link).append("?");
                link=",";
            }
            sbf.append(")");
            //连接Sqlite数据库，清空表数据
            sqliteconn = getIteconn(sqlitePath);
            preStatement = sqliteconn.prepareStatement("delete from "+tableName);
            preStatement.executeUpdate();

            preStatement = sqliteconn.prepareStatement(sbf.toString());

            //取出结果集并向SQLite数据库插入数据 (使用批处理 )
            //完成条数
            int count =0;
            int num=0;
            //取消事务
            sqliteconn.setAutoCommit(false);
            long start = System.currentTimeMillis();
            while (rs.next()) {
                ++count;
                for (int i=1;i<= size;i++) {
                    preStatement.setObject(i, rs.getObject(i));
                }

                //将预先语句存储起来，这里还没有向数据库插入
                preStatement.addBatch();
                //当count 到达 200条时 向数据库提交
                if (count % 200 ==0 ){
                    ++num;
                    preStatement.executeBatch();
                }
            }
            //防止有数据未提交
            preStatement.executeBatch();
            //提交事务
            sqliteconn.commit();
            System.out.println("完成 "+count+" 条数据,耗时:"+(System.currentTimeMillis()-start)/1000.0+"s");
        } catch (SQLException e) {
            e.printStackTrace();
            sqliteconn.rollback();
        } finally {
            //关闭资源
            close(sqliteconn,preStatement,null);
            close(mysqlconn,statement,rs);
        }

    }

    private Connection getIteconn(String sqlitePath){
        try {
            Class.forName("org.sqlite.JDBC");
            return DriverManager.getConnection("jdbc:sqlite:" + sqlitePath);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    private Connection getMysqlconn(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            return DriverManager.getConnection("url","userName","password");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public  void  close(Connection conn,Statement stmt,ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}