《数据库系统》课程之实验七 通过ODBC/JDBC转移异构数据库中数据

《数据库系统》课程之实验七 通过ODBC/JDBC转移异构数据库中数据

1 实验目的

​ 学会配置ODBC/JDBC数据源，熟悉使用ODBC/JDBC来进行数据库应用程序的设计，熟悉通过ODBC/JDBC接口访问异构数据库并对异构数据库进行操作。

2 实验平台和实验工具

实验平台：2个异构数据库（如MySQL数据库、SQL Sever数据库等ODBC/JDBC常支持的数据库）。

通过JAVA语言等编写访问数据库的应用程序。编程工具自选。

3 实验内容和要求

配置两个不同的数据源，使用ODBC/JDBC编写程序连接两个不同关系数据库管理系统的数据源，对异构数据库中的数据进行互相转移。如，将MySQL数据库的某个表中的数据转移到SQL Server数据库的表，将SQL Server中的数据转移至MySQL数据库。

认真填写实验报告，并且提交源程序，保证可正确编译和运行。

3.1 知识预备

​ 提前自行了解ODBC/JDBC的概念和使用流程。

3.2 实验要求

​ 给出配置两个不同的数据源的过程。提交应用程序源代码，并标识必要的注释，尽可能清楚明白地说明程序的功能，实现的方法，关键数据结构、变量、函数的定义。

4 实验过程

配置数据源

MySQL

​ 在实验一中已经对MySQL进行了配置

SQL Server

  • 下载镜像sql_server_2019_standard_x64_dvd_2bfe815a.iso，并解压打开，直接双击setup运行

    image-20220513104907484

  • 完成左侧的安装步骤

    image-20220513110329985

    image-20220513112757582

  • 安装并打开数据库管理工具，成功连接SQL Server2019服务器

    image-20220513212044408

    image-20220513212029841

image-20220513212128315

  • 后改用SQL Server身份认证，使用登录名和密码连接。

image-20220518095853266

关键代码及注释

展示编写的类

连接MySQL数据库

基于jdbc驱动进行连接

类：MysqlConnection

  • static初始化时从properties文件中获取属性，包括驱动名、URL、用户名、密码，然后通过Class.forName加载驱动。
  • public void connect()调用DriverManager.getConnection连接数据库。
  • public Connection getConnection()返回当前连接。
  • public void close()关闭数据库连接。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
public class MysqlConnection {
    private static String driverName;
    private static String url;
    private static String username;
    private static String password;
    private static Properties p = new Properties();
    public Connection conn;

    static {
        try {
            InputStream inputStream = new FileInputStream("src/main/resources/config.properties");
            p.load(inputStream);
            // 从properties文件中获取属性
            driverName = p.getProperty("Mysql_driverName");
            url = p.getProperty("Mysql_URL");
            username = p.getProperty("Mysql_username");
            password = p.getProperty("Mysql_password");
            // 加载驱动
            Class.forName(driverName);
            inputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 数据库连接
     */
    public void connect() {
        try {
            System.out.println("正在连接MySQL数据库");
            this.conn = DriverManager.getConnection(url, username, password);
            System.out.println("连接MySQL数据库成功");
        } catch (Exception e) {
            System.out.println("连接MySQL数据库失败");
            e.printStackTrace();
        }
    }

    /**
     * 获取当前数据库连接
     */
    public Connection getConnection() {
        return this.conn;
    }

    /**
     * 关闭数据库连接
     */
    public void close() {
        try {
            if (this.conn != null) this.conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

连接SQL Server数据库

类：SqlServerConnection

与连接MySQL基本一致，基于jdbc驱动使用用户名和密码进行连接。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
public class SqlServerConnection {
    private static Properties p = new Properties();
    private Connection conn = null;
    private static String driverName;
    private static String url;
    private static String username;
    private static String password;

    static {
        try {
            InputStream inputStream = new FileInputStream("src/main/resources/config.properties");
            p.load(inputStream);
            // 从properties文件中获取属性
            driverName = p.getProperty("SqlServer_driverName");
            url = p.getProperty("SqlServer_URL");
            username=p.getProperty("SqlServer_username");
            password=p.getProperty(("SqlServer_password"));
            //加载驱动
            Class.forName(driverName);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 数据库连接
     */
    public void connect() {
        System.out.println("正在连接SQLServer数据库");
        try {
            this.conn = DriverManager.getConnection(url,username,password);
            System.out.println("连接SQLServer数据库成功");
        } catch (Exception e) {
            System.out.println("连接SQLServer数据库失败");
            e.printStackTrace();
        }
    }

    /**
     * 获取当前数据库连接
     */
    public Connection getConnection() {
        return this.conn;
    }

    /**
     * 关闭数据库连接
     */
    public void close() {
        try {
            if (this.conn != null) this.conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

由 SQL Server 转移数据至 MySQL

类：SqlServerToMysql

  • public static void setConnSQLserver(Connection connSQLserver)：传参分别设置SQL Server的连接。
  • public static void setConnMysql(Connection connMysql)：传参分别设置MySQL的连接。

  • public static List< String > getTable()：对连接调用getMetaData()获取数据库的元数据md，对于md调用getTables(null, "dbo", null, null)获得数据库中的表信息，若该表在使用者指定要转移的表中，则将表的信息存入list返回。

  • public static boolean insertTable(String table,int size)：使用Preparedstatement定义预处理对象，执行sql语句，完成向Mysql数据库单个表插入元组的操作。

  • public static void convert()：调用getTable()和insertTable(String table,int size)，也同样执行sql语句，遍历原表中所有字段来加入建表语句，以完成在Mysql数据库中建表并转移数据。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
public class SqlServerToMysql {
    static Connection connSQLserver = null;
    static Connection connMysql = null;

    public static void setConnSQLserver(Connection connSQLserver) {
        //设置连接
        SqlServerToMysql.connSQLserver = connSQLserver;
    }

    public static void setConnMysql(Connection connMysql) {
        //设置连接
        SqlServerToMysql.connMysql = connMysql;
    }

    static PreparedStatement pstatSqlServer;
    static PreparedStatement pstatMySql;

    /**
     * 获取SqlServer中的表
     */
    public static List<String> getTable(List<String> TableList) {
        System.out.println("开始获取SqlServer数据库的表");
        DatabaseMetaData md = null;
        List<String> list = new ArrayList<>();
        try {
            //获取数据库元数据
            md = connSQLserver.getMetaData();
            //查询获得表
            ResultSet rs = md.getTables(null, "dbo", null, null);
            if (rs != null) {
                list = new ArrayList<String>();
            }
            //往list添加查询到的表
            while (rs.next()) {
                // 存在需要转移的表 / 为空：转移所有表
                if(TableList.contains(rs.getString("TABLE_NAME"))||TableList.isEmpty())
                    list.add(rs.getString("TABLE_NAME"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("成功获取SqlServer数据库的表");
        return list;
    }

    /**
     * 用于向Mysql数据库单个表插入元组
     * table  表名  size 字段个数
     */
    public static boolean insertTable(String table,int size){
        try {
            pstatSqlServer=connSQLserver.prepareStatement("select * from "+table);
            ResultSet rs=pstatSqlServer.executeQuery();
            //分隔符
            String separator="";
            //插入元组的sql语句
            String insertSql="insert into "+table+" values(";
            for (int i = 0; i < size; i++) {
                insertSql+=(separator+"?");
                separator=",";
            }
            insertSql+=")";
            //关闭自动提交，使插入字段的语句为一个事务组
            connMysql.setAutoCommit(false);
            //定义预编译的sql语句对象
            pstatMySql=connMysql.prepareStatement(insertSql);

            while (rs.next()){
                //将字段值填入insertSql语句
                for (int i = 0; i < size; i++) {
                    pstatMySql.setObject(i+1,rs.getObject(i+1));
                }
                //加入批处理队列
                pstatMySql.addBatch();
            }
            //批量更新sql语句
            pstatMySql.executeBatch();
            //提交
            connMysql.commit();
            //恢复自动提交
            connMysql.setAutoCommit(true);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return true;
    }
    /**
     * 建表并转移数据
     */
    public static void convert(List<String> TableList){
        //结果集元数据
        ResultSetMetaData rsmd = null;
        //获取SqlServer数据库的所有表
        List<String> list=getTable(TableList);
        System.out.println(list.size());
        for (int i = 0; i < list.size(); i++) {
            System.out.println("开始转移第" + (i + 1 )+ "数据表...");
            //查询表
            String sql = "select * from " + list.get(i);
            int size = 0;
            try {
                pstatSqlServer = connSQLserver.prepareStatement(sql);
                //获取数据表
                rsmd = pstatSqlServer.getMetaData();
                //创建表的语句
                String createSql = "create table " + list.get(i) + "(";
                //sql语句中字段名的分隔符
                String separator = "";
                //遍历表中所有字段
                size = rsmd.getColumnCount();
                for (int j = 0; j < size; j++) {
                    //字段名 类型
                    createSql += separator + rsmd.getColumnName(j + 1) + "  " + rsmd.getColumnTypeName(j + 1);
                    //判断类型是否规定长度
                    if (rsmd.getPrecision(j + 1) != 0) {
                        //不为0则规定长度
                        createSql += "(" + rsmd.getPrecision(j + 1) + ")";
                    }
                    separator = ",";
                }
                createSql += ");";
                //执行建表语句
                pstatMySql=connMysql.prepareStatement(createSql);
                pstatMySql.execute();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            System.out.println("开始向(" + list.get(i) + ")数据表添加数据...");
            // 添加数据到建好的表中
            if (insertTable( list.get(i), size)){
                System.out.println("第" + (i + 1 ) + "个数据表数据转移成功");
            }
            else {
                System.out.println("第" + (i + 1 ) + "个数据表数据转移失败");
            }
        }
    }
}

由 MySQL 转移数据至 SQL Server

类：MysqlToSqlServer

与SQL Server 转移数据至 MySQL 大同小异。不同之处在于：

  • 查询获得表的语句为：ResultSet rs = md.getTables(null, null, null, new String[]{"TABLE"}),这要求在创建jdbc连接的URL后面接上nullCatalogMeansCurrent=true，否则会返回所有数据库表的信息，而不是指定的数据库的表。
  • 判断字段类型是否规定长度，注意MySQL中默认INT类型长度为10，而SQL Server中为0，故对于INT型字段要添加过滤条件，往SQL Server数据库插入INT类型的数据时不得指定长度。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
public class MysqlToSqlServer {
    static Connection connSQLserver = null;
    static Connection connMysql = null;

    public static void setConnSQLserver(Connection connSQLserver) {
        MysqlToSqlServer.connSQLserver = connSQLserver;
    }

    public static void setConnMysql(Connection connMysql) {
        MysqlToSqlServer.connMysql = connMysql;
    }

    static PreparedStatement pstatSqlServer;
    static PreparedStatement pstatMySql;

    /**
     * 获取MySQL中的表
     */
    public static List<String> getTable(List<String> TableList) {
        System.out.println("开始获取MySQL数据库的表");
        DatabaseMetaData md = null;
        List<String> list = new ArrayList<>();
        try {
            //获取数据库元数据
            md = connMysql.getMetaData();
            //查询获得表
            ResultSet rs = md.getTables(null, null, null, new String[]{"TABLE"});
            if (rs != null) {
                list = new ArrayList<String>();
            }
            //往list添加查询到的表
            while (rs.next()) {
                // 存在需要转移的表 / 为空：转移所有表
                if(TableList.contains(rs.getString("TABLE_NAME"))||TableList.isEmpty())
                    list.add(rs.getString("TABLE_NAME"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("成功获取MySQL数据库的表");
        return list;
    }

    /**
     * 用于向Sql Server数据库单个表插入元组
     * table  表名  size 字段个数
     */
    public static boolean insertTable(String table,int size){
        try {
            pstatMySql=connMysql.prepareStatement("select * from "+table);
            ResultSet rs=pstatMySql.executeQuery();
            //分隔符
            String separator="";
            //插入元组的sql语句
            String insertSql="insert into "+table+" values(";
            for (int i = 0; i < size; i++) {
                insertSql+=(separator+"?");
                separator=",";
            }
            insertSql+=")";
            //关闭自动提交，使插入字段的语句为一个事务组
            connSQLserver.setAutoCommit(false);
            //定义预编译的sql语句对象
            pstatSqlServer=connSQLserver.prepareStatement(insertSql);

            while (rs.next()){
                //将字段值填入insertSql语句
                for (int i = 0; i < size; i++) {
                    pstatSqlServer.setObject(i+1,rs.getObject(i+1));
                }
                //加入批处理队列
                pstatSqlServer.addBatch();
            }
            //批量更新sql语句
            pstatSqlServer.executeBatch();
            //提交
            connSQLserver.commit();
            //恢复自动提交
            connSQLserver.setAutoCommit(true);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return true;
    }
    /**
     * 建表并转移数据
     */
    public static void convert(List<String> TableList){
        //结果集元数据
        ResultSetMetaData rsmd = null;
        //获取MySQL数据库的所有表
        List<String> list=getTable(TableList);
        System.out.println(list.size());
        for (int i = 0; i < list.size(); i++) {
            System.out.println("开始转移第" + (i + 1 )+ "数据表...");
            //查询表
            String sql = "select * from " + list.get(i);
            int size = 0;
            try {
                pstatMySql = connMysql.prepareStatement(sql);
                //获取数据表
                rsmd = pstatMySql.getMetaData();
                //创建表的语句
                String createSql = "create table " + list.get(i) + "(";
                //sql语句中字段名的分隔符
                String separator = "";
                //遍历表中所有字段
                size = rsmd.getColumnCount();
                for (int j = 0; j < size; j++) {
                    //字段名 类型
                    createSql += separator + rsmd.getColumnName(j + 1) + "  " + rsmd.getColumnTypeName(j + 1);
                    //判断类型是否规定长度;注意默认INT类型长度为10
                    if (rsmd.getPrecision(j + 1) != 0&&rsmd.getColumnTypeName(j + 1)!="INT") {
                        //不为0或INT则规定长度
                        createSql += "(" + rsmd.getPrecision(j + 1) + ")";
                    }
                    separator = ",";
                }
                createSql += ");";
                //执行建表语句
                pstatSqlServer=connSQLserver.prepareStatement(createSql);
                pstatSqlServer.execute();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            System.out.println("开始向(" + list.get(i) + ")数据表添加数据...");
            // 添加数据到建好的表中
            if (insertTable( list.get(i), size)){
                System.out.println("第" + (i + 1 ) + "个数据表数据转移成功");
            }
            else {
                System.out.println("第" + (i + 1 ) + "个数据表数据转移失败");
            }
        }
    }
}

主函数类

可与使用者交互，由使用者选择功能，并指定源数据库和目的数据库，动态修改连接数据库的配置文件config.properties成功与指定的数据库连接。然后由使用者可以指定需要转移的表，或者选择转移所有表，程序将执行convert()函数转移数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
public class main {
    /**
     * 根据输入的数据库名，更改properties文件的值
     */
    public static void modifyConfig(String SqlServer_URL, String Mysql_URL) {
        try {
            String filePath = "src/main/resources/config.properties";
            Properties properties = new Properties();
            properties.load(new FileInputStream(filePath));
            properties.setProperty("SqlServer_URL", SqlServer_URL);
            properties.setProperty("Mysql_URL", Mysql_URL);
            properties.store(new FileOutputStream(filePath), "");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 主函数
     */
    public static void main(String[] args) {
        try {
            System.out.println(System.getProperty("java.library.path"));
            Scanner in = new Scanner(System.in);
            System.out.println("请选择功能：");
            System.out.println("1.从SqlServer转移数据至MySQL");
            System.out.println("2.从MySQL转移数据至SqlServer");
            System.out.println("其它: 退出程序");
            //操作
            int op = in.nextInt();
            if(op!=1&&op!=2)
                return;
            System.out.println("请输入源数据库名：");
            String src = in.next();
            System.out.println("请输入目的数据库名：");
            String des = in.next();
            List<String> TableList = new ArrayList<>();
            System.out.println("请要转移的表名：(以回车分隔，输入#结束; 若只输入#，则转移所有表)");
            String TableName;
            do
            {
              TableName= in.next();
              // 若不是终止符,则加入
              if(!TableName.equals("#"))
              {
                  TableList.add(TableName);
              }
              else
              {
                  break;
              }
            }while(true);
            if (op == 1) {
                String SqlServer_URL = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=" + src;
                String Mysql_URL = "jdbc:mysql://localhost:3306/" + des;
                //修改配置
                modifyConfig(SqlServer_URL, Mysql_URL);
                //连接数据库
                SqlServerConnection SqlServerConn = new SqlServerConnection();
                MysqlConnection MysqlConn = new MysqlConnection();
                SqlServerConn.connect();
                MysqlConn.connect();
                SqlServerToMysql.setConnSQLserver(SqlServerConn.getConnection());
                SqlServerToMysql.setConnMysql(MysqlConn.getConnection());
                System.out.println("开始转移数据");
                //计时
                long start = System.currentTimeMillis();
                //转移数据库
                SqlServerToMysql.convert(TableList);
                System.out.println("迁移完毕,耗时:"+(System.currentTimeMillis()-start)+"ms");
            }
            else if (op==2){
                String SqlServer_URL = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=" + des;
                //nullCatalogMeansCurrent=true 使getTable使返回指定数据库的表的信息
                String Mysql_URL = "jdbc:mysql://localhost:3306/" + src+"?nullCatalogMeansCurrent=true";
                //修改配置
                modifyConfig(SqlServer_URL, Mysql_URL);
                //连接数据库
                SqlServerConnection SqlServerConn = new SqlServerConnection();
                MysqlConnection MysqlConn = new MysqlConnection();
                SqlServerConn.connect();
                MysqlConn.connect();
                MysqlToSqlServer.setConnSQLserver(SqlServerConn.getConnection());
                MysqlToSqlServer.setConnMysql(MysqlConn.getConnection());
                System.out.println("开始转移数据");
                //计时
                long start = System.currentTimeMillis();
                //转移数据库
                MysqlToSqlServer.convert(TableList);
                System.out.println("迁移完毕,耗时:"+(System.currentTimeMillis()-start)+"ms");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

5 实验效果

由 SQL Server 转移数据至 MySQL

转移指定的表

  • SQL Server数据库的test中有表student,course

    image-20220521184525491 image-20220521184627654 image-20220521184654364

  • 指定转移course表，则执行程序：

    image-20220521194512964

  • MySQL产生这一个表：

    image-20220521184457197 image-20220521184509328

转移所有表

  • SQL Server数据库的test中有表student,course

    image-20220521184525491 image-20220521184627654 image-20220521184654364

  • 执行程序：

    image-20220521193707466

  • MySQL产生这两个表：

    image-20220521185003182 image-20220521184927043 image-20220521184943789

由 MySQL 转移数据至 SQL Server

转移指定的表

  • MySQL数据库的test中有表student,teacher

  • 指定转移student表，则执行程序：

    image-20220521194249290

  • SQL Server产生这一个表：

    image-20220521183307710image-20220521183249389

转移所有表

  • MySQL数据库的test中有表student,teacher

    image-20220521182414504 image-20220521182446725 image-20220521182510012

  • 执行程序：

    image-20220521194119166

  • SQL Server产生这两个表：

    image-20220521182911972image-20220521182936258 image-20220521183004198

6 实验心得

​ 这次实验让我进一步熟练了如何通过JDBC连接MySQL数据库并进行操作，而是首次接触SQL Server，刚开始使用Windows身份认证配置连接，结果接连出错，后来改用SQL Server身份认证，通过登录名和密码连接数据库从而成功在Java中配置数据源。

​ 而在转移数据部分代码的编写中，我学会了如何使用getMetaData()获取当前连接数据库的元数据，并通过getTable()获取数据表，这里要格外注意在SQL Server和MySQL获取表信息的区别。既SQL Server查询获得表的语句getTables(null, "dbo", null, null)为，MySQL的为：md.getTables(null, null, null, new String[]{"TABLE"})。对于Mysql，需要在连接URL后面接上nullCatalogMeansCurrent=true，否则会返回所有数据库表的信息，而不是指定的数据库的表。

​ 此次我对预编译SQL语句的对象prepareStatement的使用更加熟练，并学会如何对一组SQL语句事务进行批处理，即对于一个连接通过setAutoCommit(false)关闭自动提交，对于预编译对象，通过addBatch()将填充后SQL语句加入批处理队列，使用executeBatch()批量更新sql语句。最后对于连接，通过commit()将这组SQL事务提交，一次性执行。这样批量处理sql数据提高了数据库执行SQL语句的效率，解决了数据库处理速度快与sql语句一次次传输需要时间的冲突

​ 在主函数中，我增加了与用户的交互模块，使使用者可以选择需要的转移数据功能，直接修改配置文件指定源数据库和目的数据库，使用列表保存用户需要转移的表，不将转移数据局限于固定的数据库和固定的表中中，即灵活地实现转移异构数据库中数据。

数据库系统课程实验
数据库系统实验

本博客所有文章除特别声明外，均为博客作者本人编写整理，转载请联系作者！