我的书签
添加书签
移除书签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方法对其赋值。
创建数据库连接
说明： 下面示例中黑体加下划线的字段需要替换成用户自己的信息，其中：
- 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;
- public class JdbcConn {
- public static void main(String[] args) {
- getConnect();
- }
- public static Connection getConnect() {
- String driver = "org.opengauss.Driver";
- String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=xxxxxx";
- Properties info = new Properties();
- Connection conn = null;
- try {
- Class.forName(driver);
- } catch (Exception var9) {
- var9.printStackTrace();
- return null;
- }
- try {
- conn = DriverManager.getConnection(sourceURL);
- System.out.println("连接成功！");
- return conn;
- } catch (Exception var8) {
- var8.printStackTrace();
- return null;
- }
- }
- } ```
- 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;
- public class JdbcConn {
- public static void main(String[] args) {
- getConnect();
- }
- public static Connection getConnect() {
- String driver = "org.opengauss.Driver";
- String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test";
- Properties info = new Properties();
- info.setProperty("user","myuser");
- info.setProperty("password","xxxxxx");
- Connection conn = null;
- try {
- Class.forName(driver);
- } catch (Exception var9) {
- var9.printStackTrace();
- return null;
- }
- try {
- conn = DriverManager.getConnection(sourceURL, info);
- System.out.println("连接成功！");
- return conn;
- } catch (Exception var8) {
- var8.printStackTrace();
- return null;
- }
- }
- } ```
- 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;
- public class JdbcConn {
- public static void main(String[] args) {
- getConnect();
- }
- public static Connection getConnect() {
- String driver = "org.opengauss.Driver";
- String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test";
- String username="myuser";
- String passwd="xxxxxx";
- Connection conn = null;
- try {
- Class.forName(driver);
- } catch (Exception var9) {
- var9.printStackTrace();
- return null;
- }
- try {
- conn = DriverManager.getConnection(sourceURL, username, passwd);
- System.out.println("连接成功！");
- return conn;
- } catch (Exception var8) {
- var8.printStackTrace();
- return null;
- }
- }
- } ```
创建表
- 通过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;
- public class JdbcConn {
- public static void main(String[] args) throws SQLException {
- Connection conn = getConnect();
- Statement statement = conn.createStatement();
- statement.execute("create table test_table (id int,name varchar (10))");
- }
- public static Connection getConnect() {
- String driver = "org.opengauss.Driver";
- String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=xxxxxx";
- Properties info = new Properties();
- Connection conn = null;
- try {
- Class.forName(driver);
- } catch (Exception var9) {
- var9.printStackTrace();
- return null;
- }
- try {
- conn = DriverManager.getConnection(sourceURL);
- System.out.println("连接成功！");
- return conn;
- } catch (Exception var8) {
- var8.printStackTrace();
- return null;
- }
- }
- } ```
- 通过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;
- public class JdbcConn {
- public static void main(String[] args) throws SQLException {
- Connection conn = getConnect();
- PreparedStatement preparedStatement=conn.prepareStatement("create table test (id int,name varchar (10))");
- preparedStatement.execute();
- }
- public static Connection getConnect() {
- String driver = "org.opengauss.Driver";
- String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=xxxxxx";
- Properties info = new Properties();
- Connection conn = null;
- try {
- Class.forName(driver);
- } catch (Exception var9) {
- var9.printStackTrace();
- return null;
- }
- try {
- conn = DriverManager.getConnection(sourceURL);
- System.out.println("连接成功！");
- return conn;
- } catch (Exception var8) {
- var8.printStackTrace();
- return null;
- }
- }
- } ```
插入操作
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=xxxxxx";
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;
- public class JdbcConn {
- public static void main(String[] args) throws SQLException {
- Connection conn = getConnect();
- Statement statement = conn.createStatement();
- PreparedStatement preparedStatement=conn.prepareStatement("insert into test (id,name) values (3,'zhaoliu')");
- preparedStatement.execute();
- preparedStatement=conn.prepareStatement("select * from test;");
- ResultSet resultSet=preparedStatement.executeQuery();
- while (resultSet.next()){
- System.out.println(resultSet.getObject("id")+" "+
- resultSet.getObject("name"));
- }
- conn.close();
- }
- public static Connection getConnect() {
- String driver = "org.opengauss.Driver";
- String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=xxxxxx";
- Properties info = new Properties();
- Connection conn = null;
- try {
- Class.forName(driver);
- } catch (Exception var9) {
- var9.printStackTrace();
- return null;
- }
- try {
- conn = DriverManager.getConnection(sourceURL);
- System.out.println("连接成功！");
- return conn;
- } catch (Exception var8) {
- var8.printStackTrace();
- return null;
- }
- }
- } ```
SELECT操作
- 使用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=xxxxxx";
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=xxxxxx";
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. }
更新操作
- 使用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=xxxxxx";
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;
- public class JdbcConn {
- public static void main(String[] args) throws SQLException {
- Connection conn = getConnect();
- Statement statement = conn.createStatement();
- PreparedStatement preparedStatement=conn.prepareStatement("update test set name='wangwu' where id=1");
- preparedStatement.execute();
- preparedStatement=conn.prepareStatement("select * from test;");
- ResultSet resultSet=preparedStatement.executeQuery();
- while (resultSet.next()){
- System.out.println(resultSet.getObject("id")+" "+
- resultSet.getObject("name"));
- }
- conn.close();
- }
- public static Connection getConnect() {
- String driver = "org.opengauss.Driver";
- String sourceURL = "jdbc:opengauss://127.0.0.1:8080/test?user=myuser&password=xxxxxx";
- Properties info = new Properties();
- Connection conn = null;
- try {
- Class.forName(driver);
- } catch (Exception var9) {
- var9.printStackTrace();
- return null;
- }
- try {
- conn = DriverManager.getConnection(sourceURL);
- System.out.println("连接成功！");
- return conn;
- } catch (Exception var8) {
- var8.printStackTrace();
- return null;
- }
- }
- } ```
删除操作
- 使用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;
- public class JdbcConn {
- public static void main(String[] args) throws SQLException {
- Connection conn = getConnect();
- Statement statement = conn.createStatement();
- statement.execute("delete from test where id =4");
- PreparedStatement preparedStatement=conn.prepareStatement("select * from test;");
- ResultSet resultSet=preparedStatement.executeQuery();
- while (resultSet.next()){
- System.out.println(resultSet.getObject("id")+" "+
- resultSet.getObject("name"));
- }
- conn.close();
- }
- public static Connection getConnect() {
- String driver = "org.opengauss.Driver";
- String sourceURL = "jdbc:opengauss://10.244.50.251:33700/test_l?user=ltest&password=xxxxxx?";
- Properties info = new Properties();
- Connection conn = null;
- try {
- Class.forName(driver);
- } catch (Exception var9) {
- var9.printStackTrace();
- return null;
- }
- try {
- conn = DriverManager.getConnection(sourceURL);
- System.out.println("连接成功！");
- return conn;
- } catch (Exception var8) {
- var8.printStackTrace();
- return null;
- }
- }
- } ```
- 使用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;
- public class JdbcConn {
- public static void main(String[] args) throws SQLException {
- Connection conn = getConnect();
- Statement statement = conn.createStatement();
- PreparedStatement preparedStatement=conn.prepareStatement("delete from test where id =2");
- preparedStatement.execute();
- preparedStatement=conn.prepareStatement("select * from test;");
- ResultSet resultSet=preparedStatement.executeQuery();
- while (resultSet.next()){
- System.out.println(resultSet.getObject("id")+" "+
- resultSet.getObject("name"));
- }
- conn.close();
- }
- public static Connection getConnect() {
- String driver = "org.opengauss.Driver";
- String sourceURL = "jdbc:opengauss://10.244.50.251:33700/test_l?user=ltest&password=xxxxxx?";
- Properties info = new Properties();
- Connection conn = null;
- try {
- Class.forName(driver);
- } catch (Exception var9) {
- var9.printStackTrace();
- return null;
- }
- try {
- conn = DriverManager.getConnection(sourceURL);
- System.out.println("连接成功！");
- return conn;
- } catch (Exception var8) {
- var8.printStackTrace();
- return null;
- }
- }
- } ```
