Java代码 MySql导出到SqlLite

package com.app.mysql2sqlite; import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.HashMap; import java.util.Map; import java.util.Vector; public class Mysql2Sqlite { private static Connection getMysqlConnection() { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/newappsystem", "root", "ciwong"); } catch (SQLException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } return conn; } private static Connection getSqliteConnection() { Connection conn = null; try { Class.forName("org.sqlite.JDBC"); conn = DriverManager.getConnection("jdbc:sqlite:D:\\newappstore.db"); } catch (SQLException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } return conn; } private static void closeDBConn(Connection connMysql, Connection connSqlite) throws SQLException { connMysql.close(); connSqlite.close(); } private static void showMysqlTables(Connection connMysql, Vector<String> tabs) throws SQLException { String strSql = "show tables "; PreparedStatement ps = connMysql.prepareStatement(strSql); ResultSet rs = ps.executeQuery(); while (rs.next()) { String tab = rs.getString(1); tabs.add(tab); } } private static void createSqliteTabs(Connection connMysql, Connection connSqlite, Vector<String> tabs) throws SQLException { for (String tab : tabs) { String strSql = "desc " + tab; PreparedStatement ps = connMysql.prepareStatement(strSql); ResultSet rs = ps.executeQuery(); int colCnt = 0; int keyCnt = 0; String priKeyStr = "primary key ("; String createTabDDL = ""; createTabDDL = "create table " + tab + " ("; while (rs.next()) { String Field = rs.getString(1); String Type = rs.getString(2); String Key = rs.getString(4); createTabDDL += (colCnt == 0 ? "" : ",") + Field + " " + (Type.startsWith("bit") ? "bool" : Type); if (Key.equals("PRI")) { priKeyStr += ((keyCnt == 0) ? "" : ",") + Field; keyCnt++; } colCnt++; } if (keyCnt > 0) { priKeyStr += ") "; createTabDDL += ", "+ priKeyStr; } createTabDDL += ") "; PreparedStatement psSqliteCreate = connSqlite.prepareStatement(createTabDDL); psSqliteCreate.executeUpdate(); } } private static void tranData(Connection connMysql, Connection connSqlite, Vector<String> tabs) throws SQLException { for (String tab : tabs) { String strSel = "select * from " + tab; PreparedStatement ps = connMysql.prepareStatement(strSel); PreparedStatement psInsert = null; ResultSet rs = ps.executeQuery(); System.out.println(strSel); boolean bFlag = false; int batchInsertCnt = 0; ResultSetMetaData rsmd = null; int colCnt = 0; Map<Integer, Integer> colTypeMap = new HashMap<Integer, Integer>(); while (rs.next()) { if (!bFlag) { rsmd = rs.getMetaData(); colCnt = rsmd.getColumnCount(); for (int i = 1; i <= colCnt; i++) colTypeMap.put(i, rsmd.getColumnType(i)); String strInsert = "insert into " + tab + getParamStr(colCnt); System.out.println(strInsert); psInsert = connSqlite.prepareStatement(strInsert); bFlag = true; } for (int i = 1; i <= colCnt; i++) { int colType = colTypeMap.get(i); switch (colType) { case -7: psInsert.setBoolean(i, rs.getBoolean(i)); break; case -1: psInsert.setString(i, rs.getString(i)); break; case 4: psInsert.setInt(i, rs.getInt(i)); break; case 8: psInsert.setDouble(i, rs.getDouble(i)); break; case 12: psInsert.setString(i, rs.getString(i)); break; case 93: psInsert.setDate(i, rs.getDate(i)); break; } } psInsert.addBatch(); batchInsertCnt++; if (batchInsertCnt % 100 == 0) { System.out.println("batchInsertCnt:" + batchInsertCnt); psInsert.executeBatch(); } } if (batchInsertCnt > 0) { psInsert.executeBatch(); System.out.println(tab + " total insert:" + batchInsertCnt + " rows."); } } } private static String getParamStr(int colCnt) { String paramStr = " values ("; for (int i = 0; i < colCnt; i++) { if (i==0) { paramStr += "?"; } else { paramStr += ",?"; } } paramStr += ") "; return paramStr; } private static void setSqliteAutoCommit(Connection connSqlite, boolean autoCommit) throws SQLException { connSqlite.setAutoCommit(autoCommit); } private static void sqliteCommit(Connection connSqlite) throws SQLException { connSqlite.commit(); } public static void main(String[] args) { Connection connMysql = getMysqlConnection(); if (connMysql == null) return; Connection connSqlite = getSqliteConnection(); if (connSqlite == null) return; // ÌáÉýÐÔÄÜinsertµÄ¹ý³ÌÖв»commit, µÈËùÓÐ±í¶¼²åÈëÍê³ÉÔÚͳһˢÅÌ try { setSqliteAutoCommit(connSqlite, false); } catch (SQLException e2) { e2.printStackTrace(); } Vector<String> tabs = new Vector<String>(); try { showMysqlTables(connMysql, tabs); createSqliteTabs(connMysql, connSqlite, tabs); tranData(connMysql, connSqlite, tabs); sqliteCommit(connSqlite); } catch (SQLException e1) { e1.printStackTrace(); } try { closeDBConn(connMysql, connSqlite); } catch (SQLException e) { e.printStackTrace(); } } }