Java

JDBC(Java Database Connectivity,Java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问接口,应用程序可基于它操作数据。openGauss库提供了对JDBC 4.0特性的支持,需要使用JDK1.8版本编译程序代码,不支持JDBC桥接ODBC方式。

在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文件夹下)中,格式如下所示:

  1. host all all 127.0.0.1/32 sha256

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

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

  • Statement:

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

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

示例:

Java - 图1 说明: 下面示例中黑体加下划线的字段需要替换成用户自己的信息,其中:

  • 127.0.0.1:数据库所在的主机地址。
  • 8080:数据库连接的端口。
  • test:连接的数据库名称。如果数据库不存在会报错,使用前请先确认该数据库是否存在。
  • myuser:连接数据库的用户名。
  • myPassWord:连接数据库用户的密码。
  • DriverManager.getConnection(String url);

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

    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;
    8. public class JdbcConn {
    9. public static void main(String[] args) {
    10. getConnect();
    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. }
    32. }
  • 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;
    8. public class JdbcConn {
    9. public static void main(String[] args) {
    10. getConnect();
    11. }
    12. public static Connection getConnect() {
    13. String driver = "org.opengauss.Driver";
    14. String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test";
    15. Properties info = new Properties();
    16. info.setProperty("user","myuser");
    17. info.setProperty("password","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, info);
    27. System.out.println("连接成功!");
    28. return conn;
    29. } catch (Exception var8) {
    30. var8.printStackTrace();
    31. return null;
    32. }
    33. }
    34. }
  • 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;
    8. public class JdbcConn {
    9. public static void main(String[] args) {
    10. getConnect();
    11. }
    12. public static Connection getConnect() {
    13. String driver = "org.opengauss.Driver";
    14. String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test";
    15. String username="myuser";
    16. String passwd="myPassWord";
    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, username, passwd);
    26. System.out.println("连接成功!");
    27. return conn;
    28. } catch (Exception var8) {
    29. var8.printStackTrace();
    30. return null;
    31. }
    32. }
    33. }

创建表

  • 通过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;
    8. public class JdbcConn {
    9. public static void main(String[] args) throws SQLException {
    10. Connection conn = getConnect();
    11. Statement statement = conn.createStatement();
    12. statement.execute("create table test_table (id int,name varchar (10))");
    13. }
    14. public static Connection getConnect() {
    15. String driver = "org.opengauss.Driver";
    16. String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=myPassWord";
    17. Properties info = new Properties();
    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);
    27. System.out.println("连接成功!");
    28. return conn;
    29. } catch (Exception var8) {
    30. var8.printStackTrace();
    31. return null;
    32. }
    33. }
    34. }
  • 通过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;
    8. public class JdbcConn {
    9. public static void main(String[] args) throws SQLException {
    10. Connection conn = getConnect();
    11. PreparedStatement preparedStatement=conn.prepareStatement("create table test (id int,name varchar (10))");
    12. preparedStatement.execute();
    13. }
    14. public static Connection getConnect() {
    15. String driver = "org.opengauss.Driver";
    16. String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=myPassWord";
    17. Properties info = new Properties();
    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);
    27. System.out.println("连接成功!");
    28. return conn;
    29. } catch (Exception var8) {
    30. var8.printStackTrace();
    31. return null;
    32. }
    33. }
    34. }

插入操作

Java - 图2 说明: 插入操作执行前需要确保执行了创建表操作,表名为test,表结构如下。

  1. create table test (id int,name varchar (10));
  • 使用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;
    8. public class JdbcConn {
    9. public static void main(String[] args) throws SQLException {
    10. Connection conn = getConnect();
    11. Statement statement = conn.createStatement();
    12. PreparedStatement preparedStatement=conn.prepareStatement("insert into test (id,name) values (3,'zhaoliu')");
    13. preparedStatement.execute();
    14. 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://127.0.0.1:8080/test?user=myuser&password=myPassWord";
    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. }

SELECT操作

Java - 图3 说明: 请先完成创建表插入操作

  • 使用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执行查询语句。

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

更新操作

Java - 图4 说明: 请先完成创建表插入操作

  • 使用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. 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;
    8. public class JdbcConn {
    9. public static void main(String[] args) throws SQLException {
    10. Connection conn = getConnect();
    11. Statement statement = conn.createStatement();
    12. PreparedStatement preparedStatement=conn.prepareStatement("update test set name='wangwu' where id=1");
    13. preparedStatement.execute();
    14. 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://127.0.0.1:8080/test?user=myuser&password=myPassWord";
    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. }

删除操作

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