记录一下数据库实验中比较难的一个，实在是比较费脑筋。
任务：将一种数据类型从mysql上迁移到opengauss上 我在这里以视图为例
既然用JDBC连接，那么要导入mysql和opengauss的驱动
如下图所示，去官网上下载就可以了
下面为opengauss的连接 需要改的就是下面的ip地址（可以在你的数据库上用ifconfig查看），还有连接的具体数据库，还有你用户的名字和密码都需要根据自己的来改
//gauss connect static final String JDBC_DRIVER = "org.postgresql.Driver"; static final String gauss_URL = "jdbc:postgresql://ip address/DB"; static final String gauss_user = "name"; static final String gauss_pass = "password";
下面为mysql的连接， 同样一些基本信息需要填你自己的，值得一提的是最后一个驱动如果你的mysql是8.0以上则要加中间那个cj
static final String databaseName = "lab4";//数据库名 static final String mysql_user = "";//使用管理员账号启动 static final String mysql_pwd = " ";//数据库密码 static final String mysql_URL = "jdbc:mysql://localhost:3306/" + databaseName + "?useSSL=false&serverTimezone=UTC"; static final String mysql_DRIVER = "com.mysql.cj.jdbc.Driver";
下面为代码的关键 包括sql语句的定义 sql语句的执行，尤其要注意 思想是先从opengauss里提取出来数据，然后作为mysql语句的执行内容，一般会有字符串和变量不匹配的问题，要进行一下转义
String sql;//提取 sql = "SELECT table_name,table_schema,view_definition,check_option,is_updatable FROM information_schema.views where table_schema=\'lab4\' ;"; String g_sql; g_sql="select * from test"; ResultSet rs=gauss_stmt.executeQuery(g_sql); ResultSet resultset = mysql_stmt.executeQuery(sql); while (resultset.next()) { String view_def = resultset.getString("VIEW_DEFINITION"); view_def=view_def.replace("`",""); String a=" create view vi as %s;"; String b=String.format(a,view_def); System.out.println(b); gauss_stmt.execute(b)；
下面为完整代码
import java.sql.*; public class lab4 { //gauss connect static final String JDBC_DRIVER = "org.postgresql.Driver"; static final String gauss_URL = "jdbc:postgresql://192.168.176.154:5432/demo"; // 数据库的用户名与密码，需要根据自己的设置 static final String gauss_user = "dbuser"; static final String gauss_pass = "Gauss#3demo"; //mysql connect static final String databaseName = "lab4";//数据库名 static final String mysql_user = "root";//使用管理员账号启动 static final String mysql_pwd = "ljl050505 ";//数据库密码 static final String mysql_URL = "jdbc:mysql://localhost:3306/" + databaseName + "?useSSL=false&serverTimezone=UTC"; static final String mysql_DRIVER = "com.mysql.cj.jdbc.Driver"; public static void main(String[]args)throws Exception { Connection mysql_conn = null; Statement mysql_stmt = null; Connection gauss_conn = null; Statement gauss_stmt = null; try { Class.forName(mysql_DRIVER); System.out.println("connect mysql DB"); mysql_conn = DriverManager.getConnection(mysql_URL, mysql_user, mysql_pwd); mysql_stmt = mysql_conn.createStatement(); Class.forName(JDBC_DRIVER); System.out.println("connect opengauss"); gauss_conn = DriverManager.getConnection(gauss_URL, gauss_user, gauss_pass); gauss_stmt=gauss_conn.createStatement(); String sql;//提取 sql = "SELECT table_name,table_schema,view_definition,check_option,is_updatable FROM information_schema.views where table_schema=\'lab4\' ;"; String g_sql; g_sql="select * from test"; ResultSet rs=gauss_stmt.executeQuery(g_sql); ResultSet resultset = mysql_stmt.executeQuery(sql); while (resultset.next()) { String view_def = resultset.getString("VIEW_DEFINITION"); view_def=view_def.replace("`",""); String a=" create view vi as %s;"; String b=String.format(a,view_def); System.out.println(b); gauss_stmt.execute(b); } while(rs.next()){ String g_name=rs.getString("name"); String g_location=rs.getString("location"); System.out.println("name"+g_name); System.out.println("location"+g_location); } resultset.close(); mysql_stmt.close(); mysql_conn.close(); gauss_stmt.close(); gauss_conn.close(); } catch (SQLException e) { e.printStackTrace(); } catch (Exception e1) { e1.printStackTrace(); } finally { try { if (mysql_stmt != null) mysql_stmt.close(); } catch (SQLException e2) { } } try { if (mysql_conn != null) mysql_conn.close(); } catch (SQLException e3) { e3.printStackTrace(); } System.out.println("goodbye"); } }
