欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL数据库,从一个数据库中复制指定表到另一个数据库中,案例

程序员文章站 2024-03-21 19:54:28
...
package com;

import java.sql.DriverManager;

import java.sql.*;

public class CopyMysqlDatabase {
	
	
	 /**
     * 利用创建表的语句和select语句解决数据迁移问题
     * @param dataBaseNameSource 源数据库名称
     * @param tableNameSource 源数据表名称
     * @param dataBaseNameTarget 目标数据库名称
     * @param tableNameTarget 目标数据表名称
     */
    public static void copyDataFromOneTable2AnotherWithSelectAndCreateSql(String dataBaseNameSource,String tableNameSource,
            String dataBaseNameTarget,String tableNameTarget){
    	Connection conn = null;
        Statement statement= null;
        try {
            conn = getConnection();
            statement = conn.createStatement();
            //列出库中所有表
            ResultSet rs = statement.executeQuery("show tables; ");
            while (rs.next()) {
            	//打印库中所有表名
            	System.out.println(rs.getString(1));
            }
            //创建数据库
           statement.execute("create database "+dataBaseNameTarget);
           statement.execute("use "+dataBaseNameTarget);

            //创建数据表
           statement.execute("create table "+tableNameTarget+" LIKE "+dataBaseNameSource+"."+tableNameSource);

            //导入数据
           String copySql = "insert "+tableNameTarget +" select * from "+dataBaseNameSource+"."+tableNameSource;
           statement.execute(copySql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 获取一个连接
     * @return
     */
    public static Connection getConnection(){
        try {
            String url = "jdbc:mysql://localhost:3306/test";
            // 数据库的用户名与密码,需要根据自己的设置
            final String username = "root";
            final String password = "test";
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url,username,password);
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 释放资源
     * @param conn
     * @param stmt
     * @param rs
     */
    public static void release(Connection conn,Statement stmt,ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs=null;
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            stmt = null;
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    public static void main(String[] args){ 
        System.out.println("---------------开始复制----------------");
        long startTime = System.currentTimeMillis();

        copyDataFromOneTable2AnotherWithSelectAndCreateSql("test","information","test02","information");

        long endTime = System.currentTimeMillis();
        System.out.println("---------------结束复制----------------");
        System.out.println("整个过程耗时:"+(endTime-startTime)+"ms");
    }

}