mysql 数据迁移java_使用jdk进行数据迁移(sqlite迁移mysql)
packagecom.wbg;importorg.omg.Messaging.SYNC_WITH_TRANSPORT;import java.sql.*;importjava.util.ArrayList;importjava.util.List;public classDBSQLite {//驅(qū)動(dòng)
static String DBDRIVERSQLITE="org.sqlite.JDBC";//文件絕對(duì)地址
static String DBURLSQLITE="B:\\lagou.db";staticConnection CONNSQLITE;staticPreparedStatement PSTATSQLITE;//驅(qū)動(dòng)
static String DBDRIVERMYSQL="com.mysql.jdbc.Driver";//數(shù)據(jù)庫(kù)
static String DBURLMYSQL="jdbc:mysql://localhost:3306/ii?rewriteBatchedStatements=true";//comoany是數(shù)據(jù)庫(kù)
staticConnection CONNMYSQL;staticPreparedStatement PSTATMYSQL;static{try{
Class.forName(DBDRIVERSQLITE);
CONNSQLITE= DriverManager.getConnection("jdbc:sqlite:"+DBURLSQLITE);
Class.forName(DBDRIVERMYSQL);
CONNMYSQL=DriverManager.getConnection(DBURLMYSQL,"root","123456");
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(SQLException e) {
e.printStackTrace();
}
}/*** 獲取數(shù)據(jù)庫(kù)中所有表*/
public static ListgetTable(){
System.out.println("開始獲取數(shù)據(jù)表");//數(shù)據(jù)庫(kù)
DatabaseMetaData md=null;
List list=new ArrayList<>();try{//獲取數(shù)據(jù)庫(kù)
md =CONNSQLITE.getMetaData();
}catch(SQLException e) {
e.printStackTrace();
}try{//進(jìn)行查詢表
ResultSet rs=md.getTables(null,null,null,null);if (rs!= null) {
list= new ArrayList();
}//往list添加查詢到的表
while(rs.next()){
list.add(rs.getString("TABLE_NAME"));
}
}catch(SQLException e) {
e.printStackTrace();
}
System.out.println("查詢數(shù)據(jù)表成功");returnlist;
}/*** 向mysql創(chuàng)建數(shù)據(jù)表*/
public static voidcreateTable(){
ResultSetMetaData rsmd= null;//獲取所有數(shù)據(jù)庫(kù)
List list=DBSQLite.getTable();for (int i = 0; i < list.size(); i++) {long srop=System.currentTimeMillis();
System.out.println("開始創(chuàng)建第" + (i + 1 )+ "數(shù)據(jù)表...");//查詢表
String sql = "select * from " +list.get(i);int size = 0;try{
PSTATSQLITE=CONNSQLITE.prepareStatement(sql);//獲取數(shù)據(jù)表
rsmd =PSTATSQLITE.getMetaData();//創(chuàng)建語句
String create = "create table " + list.get(i) + "(";//fh目的最后一個(gè)沒有逗號(hào)
String fh = "";//循環(huán)表中所有字段
size =rsmd.getColumnCount();for (int j = 0; j < size; j++) {//字段名 類型
create += fh + rsmd.getColumnName(j + 1) + " " + rsmd.getColumnTypeName(j + 1);//判斷長(zhǎng)度是否不為0 比如varchar(10) integer
if (rsmd.getPrecision(j + 1) != 0) {//不為0就給個(gè)長(zhǎng)度
create += "(" + rsmd.getPrecision(j + 1) + ")";
}
fh= ",";
}
create+= ");";
execute(create);
}catch(SQLException e) {
e.printStackTrace();
}
System.out.println("開始向(" + list.get(i) + ")數(shù)據(jù)表添加數(shù)據(jù)...");if(insertTable( list.get(i), size)){
System.out.println("第" + (i + 1 ) + "個(gè)數(shù)據(jù)表數(shù)據(jù)錄入完畢");
}else{
System.out.println("第" + (i + 1 ) + "個(gè)數(shù)據(jù)表數(shù)據(jù)錄入失敗");
}
}
}/*** 向mysql數(shù)據(jù)表錄入數(shù)據(jù)
* sql 查詢語句 size 字段長(zhǎng)度*/
public static boolean insertTable(String sql,intsize){
ResultSet rs=executeQuerySQLITE("select * from "+sql);try{
String fh="";
String sqladd="insert into "+sql+" values(";for (int i = 0; i < size; i++) {
sqladd+=(fh+"?");
fh=",";
}
sqladd+=")";//關(guān)閉自動(dòng)提交
CONNMYSQL.setAutoCommit(false);
PSTATMYSQL=CONNMYSQL.prepareStatement(sqladd);while(rs.next()){for (int i = 0; i < size; i++) {
PSTATMYSQL.setObject(i+1,rs.getObject(i+1));
}
PSTATMYSQL.addBatch();
}
PSTATMYSQL.executeBatch();//提交
CONNMYSQL.commit();//打開自動(dòng)提交
CONNMYSQL.setAutoCommit(true);
}catch(SQLException e) {
e.printStackTrace();
}return true;
}public staticResultSet executeQuerySQLITE(String sql){try{
PSTATSQLITE=CONNSQLITE.prepareStatement(sql);returnPSTATSQLITE.executeQuery();
}catch(SQLException e) {
e.printStackTrace();
}return null;
}public static voidexecute(String sql){try{
PSTATMYSQL=CONNMYSQL.prepareStatement(sql);
PSTATMYSQL.execute();
}catch(SQLException e) {
System.out.println("創(chuàng)建數(shù)據(jù)庫(kù)失敗");
}
}public static intexecuteUpdate(String sql ,Object[]in){try{
PSTATMYSQL=CONNMYSQL.prepareStatement(sql);for (int i = 0; i
PSTATMYSQL.setObject(i+1,in[i]);
}returnPSTATMYSQL.executeUpdate();
}catch(SQLException e) {
e.printStackTrace();
}return 0;
}public static voidmain(String[] args) {long start=System.currentTimeMillis();
System.out.println("開始遷移數(shù)據(jù)");
createTable();
System.out.println("遷移完畢,耗時(shí):"+(System.currentTimeMillis()-start)/1000);
}
}
總結(jié)
以上是生活随笔為你收集整理的mysql 数据迁移java_使用jdk进行数据迁移(sqlite迁移mysql)的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 主板上的"CLEAR CMOS"是什么意
- 下一篇： 桌面级3D打印机使用教程