处理了时间类型
package changeDataBase;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
public class MySQLToSQLiteMigration {
private static final String MYSQL_URL = "jdbc:mysql://localhost:3306/datebaseName";
private static final String MYSQL_USER = "user";
private static final String MYSQL_PASSWORD = "123456";
private static final String SQLITE_URL = "jdbc:sqlite:D:/database/datebaseName.sqlite";
public static void main(String[] args) {
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException var2) {
System.err.println("SQLite JDBC driver not found!");
var2.printStackTrace();
return;
}
List<String> tableNames = getTableNamesFromMySQL();
migrateTablesToSQLite(tableNames);
}
private static List<String> getTableNamesFromMySQL() {
ArrayList<String> tableNames = new ArrayList<>();
try (Connection conn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD)) {
DatabaseMetaData metaData = conn.getMetaData();
ResultSet rs = metaData.getTables(null, null, "%", new String[]{"TABLE"});
while (rs.next()) {
tableNames.add(rs.getString("TABLE_NAME"));
}
} catch (SQLException var6) {
var6.printStackTrace();
}
return tableNames;
}
private static void migrateTablesToSQLite(List<String> tableNames) {
try (Connection mysqlConn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD);
Connection sqliteConn = DriverManager.getConnection(SQLITE_URL)) {
Iterator<String> var3 = tableNames.iterator();
while (var3.hasNext()) {
String tableName = var3.next();
System.out.println("Migrating table: " + tableName);
migrateTable(mysqlConn, sqliteConn, tableName);
}
} catch (SQLException var9) {
var9.printStackTrace();
}
}
private static void migrateTable(Connection mysqlConn, Connection sqliteConn, String tableName) throws SQLException {
String selectSql = "SELECT * FROM " + tableName;
try (Statement stmt = mysqlConn.createStatement();
ResultSet rs = stmt.executeQuery(selectSql)) {
String createTableSql = getCreateTableSql(mysqlConn, tableName);
try (Statement sqliteStmt = sqliteConn.createStatement()) {
sqliteStmt.execute(createTableSql);
}
String insertSql = getInsertSql(rs.getMetaData(), tableName);
try (PreparedStatement sqlitePstmt = sqliteConn.prepareStatement(insertSql)) {
while (rs.next()) {
for (int i = 1; i <= rs.getMetaData().getColumnCount(); ++i) {
String columnName = rs.getMetaData().getColumnName(i);
String columnType = rs.getMetaData().getColumnTypeName(i);
// 特殊处理时间字段
if ("DATETIME".equalsIgnoreCase(columnType) || "TIMESTAMP".equalsIgnoreCase(columnType)) {
Object value = rs.getObject(i);
if (value instanceof java.sql.Timestamp) {
// 将 Timestamp 转换为标准日期时间格式
java.sql.Timestamp timestamp = (java.sql.Timestamp) value;
sqlitePstmt.setString(i, timestamp.toString());
} else {
sqlitePstmt.setObject(i, value);
}
} else {
sqlitePstmt.setObject(i, rs.getObject(i));
}
}
sqlitePstmt.addBatch();
}
sqlitePstmt.executeBatch();
}
}
}
private static String getCreateTableSql(Connection mysqlConn, String tableName) throws SQLException {
DatabaseMetaData metaData = mysqlConn.getMetaData();
ResultSet rs = metaData.getColumns(null, null, tableName, "%");
StringBuilder sb = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
sb.append(tableName).append(" (");
List<String> columns = new ArrayList<>();
String primaryKey = null;
while (rs.next()) {
String columnName = rs.getString("COLUMN_NAME");
String columnType = getSQLiteType(rs.getString("TYPE_NAME"));
columns.add(columnName + " " + columnType);
}
// 获取主键信息
String primaryKeySql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND CONSTRAINT_NAME = 'PRIMARY'";
try (PreparedStatement pstmt = mysqlConn.prepareStatement(primaryKeySql)) {
pstmt.setString(1, "datebaseName");
pstmt.setString(2, tableName);
try (ResultSet pkRs = pstmt.executeQuery()) {
if (pkRs.next()) {
primaryKey = pkRs.getString("COLUMN_NAME");
}
}
}
sb.append(String.join(", ", columns));
if (primaryKey != null) {
sb.append(", PRIMARY KEY (").append(primaryKey).append(")");
}
sb.append(");");
return sb.toString();
}
private static String getInsertSql(ResultSetMetaData metaData, String tableName) throws SQLException {
StringBuilder sb = new StringBuilder("INSERT INTO ").append(tableName).append(" (");
for (int i = 1; i <= metaData.getColumnCount(); ++i) {
sb.append(metaData.getColumnName(i));
if (i < metaData.getColumnCount()) {
sb.append(", ");
}
}
sb.append(") VALUES (");
for (int i = 1; i <= metaData.getColumnCount(); ++i) {
sb.append("?");
if (i < metaData.getColumnCount()) {
sb.append(", ");
}
}
sb.append(");");
return sb.toString();
}
private static String getSQLiteType(String mysqlType) {
switch (mysqlType.toUpperCase()) {
case "INT":
case "INTEGER":
return "INTEGER";
case "VARCHAR":
case "CHAR":
return "TEXT";
case "DATE":
return "DATE";
case "DATETIME":
case "TIMESTAMP":
return "DATETIME";
case "DECIMAL":
return "NUMERIC";
case "FLOAT":
case "DOUBLE":
return "REAL";
default:
return "TEXT";
}
}
}
package javaBean;
/**
* @author
* @date 2025/7/4 13:33
* @desc 时间类型转换
*/
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class DateUtil {
private static final SimpleDateFormat DEFAULT_DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private static final SimpleDateFormat DEFAULT_DAY_FORMAT = new SimpleDateFormat("yyyy-MM-dd");
public static Date parseDate(String dateStr) {
if (dateStr == null || dateStr.trim().isEmpty()) {
return null; // 如果输入字符串为 null 或空，直接返回 null
}
try {
return DEFAULT_DATE_FORMAT.parse(dateStr);
} catch (ParseException e) {
e.printStackTrace();
return null; // 如果解析失败，返回 null
}
}
public static String formatDate(Date date) {
if (date == null) {
return null; // 如果日期为 null，返回 null
}
return DEFAULT_DATE_FORMAT.format(date);
}
public static String formatDateDay(Date date) {
if (date == null) {
return null; // 如果日期为 null，返回 null
}
return DEFAULT_DAY_FORMAT.format(date);
}
public static Date parseDay(String dateStr) {
if (dateStr == null || dateStr.trim().isEmpty()) {
return null; // 如果输入字符串为 null 或空，直接返回 null
}
try {
return DEFAULT_DAY_FORMAT.parse(dateStr);
} catch (ParseException e) {
e.printStackTrace();
return null; // 如果解析失败，返回 null
}
}
}