Java

    在linux服务器端源代码目录下执行build.sh，获得驱动jar包postgresql.jar，包位置在源代码目录下。从发布包中获取，包名为openGauss-xxxx-操作系统版本号-64bit-Jdbc.tar.gz。

    驱动包与PostgreSQL保持兼容，其中类名、类结构与PostgreSQL驱动完全一致，曾经运行于PostgreSQL的应用程序可以直接移植到当前系统使用。

    在创建数据库连接之前，需要加载数据库驱动类，驱动类不同包位置不同。openGauss jdbc的驱动为“org.opengauss.Driver”，其中url前缀为“jdbc:opengauss”。

    连接数据库

    在连接数据库之前先要添加连接数据库的主机地址（加粗有下划线内容）添加到pg_hba.conf（此文件在安装目录下的datanode文件夹下）中，格式如下所示：

    JDBC提供了三个方法，用于创建数据库连接。

    语句执行常用的方式为通过Statement和PreparedStatement两种方式：

    • Statement：

      • execute：返回值是boolean类型，执行查询语句看不到返回值。
      • executeQuery：返回值是ResultSet类型，通常用于查询，使用方法为在里面直接写SQL语句。

    • PreparedStatement：

      包含Statement两个方法，但使用不太一样，要先在conn.prepareStatement()中写入语句，之后根据需要再执行execute或executeQuery，这两个方法里面入参为空。如果SQL语句中有变量，建议使用PreparedStatement进行操作可以有效防止SQL注入，使用?替换掉变量，之后使用setObject方法对其赋值。

    • DriverManager.getConnection(String url);

      此连接方法需要在url上面拼接上用户名密码。

      2. import java.sql.DriverManager;
      3. import java.sql.PreparedStatement;
      4. import java.sql.ResultSet;
      5. import java.sql.SQLException;
      6. import java.sql.Statement;
      7. import java.util.Properties;
      9. public class JdbcConn {
      10.   public static void main(String[] args) {
      11.         getConnect();
      12.   }
      13.   public static Connection getConnect() {
      14.         String driver = "org.opengauss.Driver";
      15.         String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=myPassWord";
      16.         Properties info = new Properties();
      17.         Connection conn = null;
      18.         try {
      19.             Class.forName(driver);
      20.         } catch (Exception var9) {
      21.             var9.printStackTrace();
      22.             return null;
      23.         }
      24.         try {
      25.             conn = DriverManager.getConnection(sourceURL);
      26.             System.out.println("连接成功！");
      27.             return conn;
      28.         } catch (Exception var8) {
      29.             var8.printStackTrace();
      30.             return null;
      31.         }
      32.     }
      33. }

    • DriverManager.getConnection(String url, Properties info);

      此方法的用户名密码等参数均在Properties 对象的实例通过setProperty添加。

      1. import java.sql.Connection;
      2. import java.sql.DriverManager;
      3. import java.sql.PreparedStatement;
      4. import java.sql.ResultSet;
      5. import java.sql.SQLException;
      6. import java.sql.Statement;
      7. import java.util.Properties;
      9. public class JdbcConn {
      10.   public static void main(String[] args) {
      11.         getConnect();
      12.   }
      13.  public static Connection getConnect() {
      14.         String driver = "org.opengauss.Driver";
      15.         String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test";
      16.         Properties info = new Properties();
      17.         info.setProperty("user","myuser");
      18.         info.setProperty("password","myPassWord");
      19.         Connection conn = null;
      20.         try {
      21.             Class.forName(driver);
      22.         } catch (Exception var9) {
      23.             var9.printStackTrace();
      24.             return null;
      25.         }
      26.         try {
      27.             conn = DriverManager.getConnection(sourceURL, info);
      28.             System.out.println("连接成功！");
      29.             return conn;
      30.         } catch (Exception var8) {
      31.             var8.printStackTrace();
      32.             return null;
      33.         }
      34.     }
      35. }

    • DriverManager.getConnection(String url, String user, String password);

      此方法需要将用户名和密码作为变量输入。

      1. import java.sql.Connection;
      2. import java.sql.DriverManager;
      3. import java.sql.PreparedStatement;
      4. import java.sql.ResultSet;
      5. import java.sql.SQLException;
      6. import java.sql.Statement;
      7. import java.util.Properties;
      9. public class JdbcConn {
      10.   public static void main(String[] args) {
      11.         getConnect();
      12.   }
      13.  public static Connection getConnect() {
      14.         String driver = "org.opengauss.Driver";
      15.         String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test";
      16.         String username="myuser";
      17.         String passwd="myPassWord";
      18.         Connection conn = null;
      19.         try {
      20.             Class.forName(driver);
      21.         } catch (Exception var9) {
      22.             var9.printStackTrace();
      23.             return null;
      24.         }
      25.         try {
      26.             conn = DriverManager.getConnection(sourceURL, username, passwd);
      27.             System.out.println("连接成功！");
      28.             return conn;
      29.         } catch (Exception var8) {
      30.             var8.printStackTrace();
      31.             return null;
      32.         }
      33.     }
      34. }

    • 通过statement执行创表操作。

      1. import java.sql.Connection;
      2. import java.sql.DriverManager;
      3. import java.sql.PreparedStatement;
      4. import java.sql.ResultSet;
      5. import java.sql.SQLException;
      6. import java.sql.Statement;
      7. import java.util.Properties;
      9. public class JdbcConn {
      10.   public static void main(String[] args) throws SQLException {
      11.         Connection conn = getConnect();
      12.         Statement statement = conn.createStatement();
      13.         statement.execute("create table test_table (id int,name varchar (10))");
      14.     }
      15.     public static Connection getConnect() {
      16.         String driver = "org.opengauss.Driver";
      17.         String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=myPassWord";
      18.         Properties info = new Properties();
      19.         Connection conn = null;
      20.         try {
      21.             Class.forName(driver);
      22.             var9.printStackTrace();
      23.             return null;
      24.         }
      25.         try {
      26.             conn = DriverManager.getConnection(sourceURL);
      27.             System.out.println("连接成功！");
      28.             return conn;
      29.         } catch (Exception var8) {
      30.             var8.printStackTrace();
      31.             return null;
      33.     }
      34. }

    • 通过PreparedStatement执行创表操作。

    插入操作

    • 使用Statement执行插入操作，后面可以跟随查询语句检查插入是否生效。

      1. public static void main(String[] args) throws SQLException {
      2.         Connection conn = getConnect();
      3.         Statement statement = conn.createStatement();
      4.         statement.execute("insert into test (id,name) values (2,'zhangsan')");
      5.         PreparedStatement preparedStatement=conn.prepareStatement("select * from test;");
      6.         ResultSet resultSet=preparedStatement.executeQuery();
      7.         while (resultSet.next()){
      8.             System.out.println(resultSet.getObject("id")+"  "+
      9.                     resultSet.getObject("name"));
      10.         }
      11.         conn.close();
      12.     }
      13.     public static Connection getConnect() {
      14.         String driver = "org.opengauss.Driver";
      15.         String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=myPassWord";
      16.         Properties info = new Properties();
      17.         Connection conn = null;
      18.         try {
      19.             Class.forName(driver);
      20.         } catch (Exception var9) {
      21.             var9.printStackTrace();
      22.             return null;
      23.         }
      24.         try {
      25.             conn = DriverManager.getConnection(sourceURL);
      26.             System.out.println("连接成功！");
      27.             return conn;
      28.         } catch (Exception var8) {
      29.             var8.printStackTrace();
      30.             return null;
      31.         }
      32.     }

    • 使用PreparedStatement 执行插入操作，后面可以跟随查询语句检查插入是否生效。

      1. import java.sql.Connection;
      2. import java.sql.DriverManager;
      3. import java.sql.PreparedStatement;
      4. import java.sql.ResultSet;
      5. import java.sql.SQLException;
      6. import java.sql.Statement;
      7. import java.util.Properties;
      9. public class JdbcConn {
      10.     public static void main(String[] args) throws SQLException {
      11.         Connection conn = getConnect();
      12.         Statement statement = conn.createStatement();
      13.         PreparedStatement preparedStatement=conn.prepareStatement("insert into test (id,name) values (3,'zhaoliu')");
      14.         preparedStatement.execute();
      15.         preparedStatement=conn.prepareStatement("select * from test;");
      16.         ResultSet resultSet=preparedStatement.executeQuery();
      17.         while (resultSet.next()){
      18.             System.out.println(resultSet.getObject("id")+"  "+
      19.                     resultSet.getObject("name"));
      20.         }
      21.         conn.close();
      22.     }
      23.     public static Connection getConnect() {
      24.         String driver = "org.opengauss.Driver";
      25.         String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=myPassWord";
      26.         Properties info = new Properties();
      27.         Connection conn = null;
      28.         try {
      29.             Class.forName(driver);
      30.         } catch (Exception var9) {
      31.             var9.printStackTrace();
      32.             return null;
      33.         }
      34.         try {
      35.             conn = DriverManager.getConnection(sourceURL);
      36.             System.out.println("连接成功！");
      37.             return conn;
      38.         } catch (Exception var8) {
      39.             var8.printStackTrace();
      40.             return null;
      41.         }
      42.     }
      43. }

    • 使用prepareStatement执行查询操作可以使用预编译，动态的添加参数也可以执行普通的SQL语句。

      1. public static void main(String[] args) throws SQLException {
      2.         Connection conn = getConnect();
      3.     PreparedStatement preparedStatement=conn.prepareStatement("select * from test where id=?;");
      4.         preparedStatement.setObject(1,1);
      5.         ResultSet resultSet=preparedStatement.executeQuery();
      6.         while (resultSet.next()){
      7.             System.out.println(resultSet.getObject("id")+"  "+
      8.                     resultSet.getObject("name"));
      9.         }
      10.         conn.close();
      11.     }
      12.     public static Connection getConnect() {
      13.         String driver = "org.opengauss.Driver";
      14.         String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=myPassWord";
      15.         Properties info = new Properties();
      16.         Connection conn = null;
      17.         try {
      18.             Class.forName(driver);
      19.         } catch (Exception var9) {
      20.             var9.printStackTrace();
      21.             return null;
      22.         }
      23.         try {
      24.             conn = DriverManager.getConnection(sourceURL);
      25.             System.out.println("连接成功！");
      26.             return conn;
      27.         } catch (Exception var8) {
      28.             var8.printStackTrace();
      29.             return null;
      30.         }
      31.     }

    • 使用Statement执行查询语句。

    更新操作

    • 使用Statement执行更新操作，然后执行查询语句判断更新是否成功。

      1. public static void main(String[] args) throws SQLException {
      2.         Connection conn = getConnect();
      3.         Statement statement = conn.createStatement();
      4.         statement.execute("update  test set name='wangwu' where id=1");
      5.         PreparedStatement preparedStatement=conn.prepareStatement("select * from test");
      6.         ResultSet resultSet=preparedStatement.executeQuery();
      7.         while (resultSet.next()){
      8.             System.out.println(resultSet.getObject("id")+"  "+
      9.                     resultSet.getObject("name"));
      10.         }
      11.         conn.close();
      12.     }
      13.     public static Connection getConnect() {
      14.         String driver = "org.opengauss.Driver";
      15.         String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=myPassWord";
      16.         Properties info = new Properties();
      17.         Connection conn = null;
      18.             Class.forName(driver);
      19.         } catch (Exception var9) {
      20.             var9.printStackTrace();
      21.             return null;
      22.         }
      23.         try {
      25.             System.out.println("连接成功！");
      26.             return conn;
      27.         } catch (Exception var8) {
      28.             var8.printStackTrace();
      29.             return null;
      30.         }
      31.     }

    • 使用PreparedStatement执行更新操作，然后执行查询语句判断更新是否成功。

      1. import java.sql.Connection;
      2. import java.sql.DriverManager;
      3. import java.sql.PreparedStatement;
      4. import java.sql.ResultSet;
      5. import java.sql.SQLException;
      6. import java.sql.Statement;
      7. import java.util.Properties;
      9. public class JdbcConn {
      10.     public static void main(String[] args) throws SQLException {
      11.         Connection conn = getConnect();
      12.         Statement statement = conn.createStatement();
      13.         PreparedStatement preparedStatement=conn.prepareStatement("update  test set name='wangwu' where id=1");
      14.         preparedStatement.execute();
      15.         preparedStatement=conn.prepareStatement("select * from test;");
      16.         ResultSet resultSet=preparedStatement.executeQuery();
      17.         while (resultSet.next()){
      18.             System.out.println(resultSet.getObject("id")+"  "+
      19.                     resultSet.getObject("name"));
      20.         }
      21.         conn.close();
      22.     }
      23.     public static Connection getConnect() {
      24.         String driver = "org.opengauss.Driver";
      25.         String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=myPassWord";
      26.         Properties info = new Properties();
      27.         Connection conn = null;
      28.         try {
      29.             Class.forName(driver);
      30.         } catch (Exception var9) {
      31.             var9.printStackTrace();
      32.             return null;
      33.         }
      34.         try {
      35.             conn = DriverManager.getConnection(sourceURL);
      36.             System.out.println("连接成功！");
      37.             return conn;
      38.         } catch (Exception var8) {
      39.             var8.printStackTrace();
      40.             return null;
      41.         }
      42.     }
      43. }

    • 使用Statement进行删除，然后执行查询语句判断删除是否成功。

      1. import java.sql.Connection;
      2. import java.sql.DriverManager;
      3. import java.sql.PreparedStatement;
      4. import java.sql.ResultSet;
      5. import java.sql.SQLException;
      6. import java.sql.Statement;
      7. import java.util.Properties;
      9. public class JdbcConn {
      10.     public static void main(String[] args) throws SQLException {
      11.         Connection conn = getConnect();
      12.         Statement statement = conn.createStatement();
      13.         statement.execute("delete from test where id =4");
      14.         PreparedStatement preparedStatement=conn.prepareStatement("select * from test;");
      15.         ResultSet resultSet=preparedStatement.executeQuery();
      16.         while (resultSet.next()){
      17.             System.out.println(resultSet.getObject("id")+"  "+
      18.                     resultSet.getObject("name"));
      19.         }
      20.         conn.close();
      21.     }
      22.     public static Connection getConnect() {
      23.         String driver = "org.opengauss.Driver";
      24.         String sourceURL = "jdbc:opengauss://10.244.50.251:33700/test_l?user=ltest&password=tiange1999?";
      25.         Properties info = new Properties();
      26.         Connection conn = null;
      27.         try {
      28.             Class.forName(driver);
      29.         } catch (Exception var9) {
      30.             var9.printStackTrace();
      31.             return null;
      32.         }
      33.         try {
      34.             conn = DriverManager.getConnection(sourceURL);
      35.             System.out.println("连接成功！");
      36.             return conn;
      37.         } catch (Exception var8) {
      38.             var8.printStackTrace();
      39.             return null;
      40.         }
      41.     }
      42. }
      1. import java.sql.Connection;
      2. import java.sql.DriverManager;
      3. import java.sql.PreparedStatement;
      4. import java.sql.ResultSet;
      5. import java.sql.SQLException;
      6. import java.sql.Statement;
      7. import java.util.Properties;
      9. public class JdbcConn {
      10.     public static void main(String[] args) throws SQLException {
      11.         Connection conn = getConnect();
      12.         Statement statement = conn.createStatement();
      13.         PreparedStatement preparedStatement=conn.prepareStatement("delete from test where id =2");
      14.         preparedStatement.execute();
      15.         preparedStatement=conn.prepareStatement("select * from test;");
      16.         ResultSet resultSet=preparedStatement.executeQuery();
      17.         while (resultSet.next()){
      18.             System.out.println(resultSet.getObject("id")+"  "+
      19.                     resultSet.getObject("name"));
      20.         }
      21.         conn.close();
      22.     }
      23.     public static Connection getConnect() {
      24.         String driver = "org.opengauss.Driver";
      25.         String sourceURL = "jdbc:opengauss://10.244.50.251:33700/test_l?user=ltest&password=tiange1999?";
      26.         Properties info = new Properties();
      27.         Connection conn = null;
      28.         try {
      29.             Class.forName(driver);
      30.         } catch (Exception var9) {
      31.             var9.printStackTrace();
      32.             return null;
      33.         }
      34.         try {
      35.             conn = DriverManager.getConnection(sourceURL);
      36.             System.out.println("连接成功！");
      37.             return conn;
      38.         } catch (Exception var8) {
      39.             var8.printStackTrace();
      40.             return null;
      41.         }
      42. }