本文正在参加「技术专题19期 漫谈数据库技术」活动

---

在本文中，我们将学习如何使用Java JDBC驱动程序与PostgreSQL数据库进行交互。JDBC是Java的核心API，它为兼容SQL的数据库 (尤其是PostgreSQL) 提供标准接口。

本文使用的emoji🦄🧚‍♀️🧚🏻‍♀️🧚🏻‍♂️

🧚‍♀️PostgreSQL JDBC连接数据库:

JDBC连接数据库的语法如下

jdbc:postgresql://<database_host>:<port>/<database_name>

• database_host

数据库服务的地址

• port

数据库服务的端口

• database_name

要连接的数据库库名

以下是我链接数据库的示例:

jdbc:postgresql://localhost:5432/postgres

为了使其更简单，我们可以定义用于存储连接url、user和password的App类的字符串:

private final String url = "jdbc:postgresql://localhost:5432/postgres";
private final String user = "postgres";
private final String password = "112233";

要建立与PostgreSQL数据库服务器的连接，我们可以调用DriverManager的 getConnection 方法。此方法返回Connection 对象。

以下是使用connect()方法连接到PostgreSQL数据库服务器并返回Connection 对象。

完整代码如下所示

    @Test
    void connect() {
        String url = "jdbc:postgresql://localhost/postgres?user=postgres&password=112233";
        try {
            Connection conn = DriverManager.getConnection(url);
            System.out.println("链接PG成功！");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }

执行结果如下所示：

🧚‍♀️如何使用JDBC调用PostgreSQL存储函数

总结: 在本节中，我们将学习如何使用JDBC调用PostgreSQL存储函数。

PostgreSQL JDBC驱动程序完全支持PostgreSQL存储函数。

我将展示如何使用JDBC调用内置存储函数以及用户定义的存储函数

🧚🏻‍♀️调用内置存储函数

我们将调用内置字符串函数substring()替换字符串中的指定字符。

使用jdbc调用substring()函数，可以按照以下步骤操作:

1. 建立数据库连接。
2. 创建CallableStatement对象并注册OUT参数。
3. 执行函数调用并获取返回的结果。

以下是代码示例：

    /**
     * 调用内置函数demo
     *
     * @param str 是一个字符串数据类型，是char、varchar、text等。
     * @param startPosition 是一个整数，指定要提取子字符串的位置。如果 startPosition 等于零，子字符串从字符串的第一个字符开始。startPosition 只能是正数。（尽管在其他数据库系统中，如MySQL子字符串函数可以接受 startPosition 是负数。）
     * @param length 是一个正整数，用于确定要从字符串中提取的字符数量，开头为 startPosition。如果 startPosition 和length之和大于string中的字符数，substring函数返回从开始位置。length参数是可选的。如果省略length参数，substring函数将返回从start_position开始的整个字符串。
     * @return
     */
    public String strSubstring(String str,Integer startPosition,Integer length) {
        String result = null;
        try (Connection conn = this.connect(); CallableStatement properCase = conn.prepareCall("{ ? = call substring (?,?,?) }")) {
            properCase.registerOutParameter(1, Types.VARCHAR);  //对于输出参数，必须注册为out参数类型，并且指定参数的数据类型，通过Types类：标识sql的常量的类
            properCase.setString(2, str);
            properCase.setInt(3, startPosition);
            properCase.setInt(4, length);
            properCase.execute();
            result = properCase.getString(1);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return result;
    }

一下是截取字符串36999前三位的代码示例：

@SpringBootTest
class JavapostgrestestApplicationTests {

    private final String url = "jdbc:p6spy:postgresql://localhost:5432/postgres";
    private final String user = "postgres";
    private final String password = "112233";

    @Test
    void testBuiltinFunction() {
        String strSubstring = strSubstring("36999",0,4) ;
        System.out.println(strSubstring);
    }


    /**
     * 连接PostgreSql数据库
     *
     * @return Connection
     * @throws java.sql.SQLException
     */
    public Connection connect() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }


    /**
     * 调用内置函数demo
     *
     * @param str 是一个字符串数据类型，是char、varchar、text等。
     * @param startPosition 是一个整数，指定要提取子字符串的位置。如果 startPosition 等于零，子字符串从字符串的第一个字符开始。startPosition 只能是正数。（尽管在其他数据库系统中，如MySQL子字符串函数可以接受 startPosition 是负数。）
     * @param length 是一个正整数，用于确定要从字符串中提取的字符数量，开头为 startPosition。如果 startPosition 和length之和大于string中的字符数，substring函数返回从开始位置。length参数是可选的。如果省略length参数，substring函数将返回从start_position开始的整个字符串。
     * @return
     */
    public String strSubstring(String str,Integer startPosition,Integer length) {
        String result = null;
        try (Connection conn = this.connect(); CallableStatement properCase = conn.prepareCall("{ ? = call substring (?,?,?) }")) {
            properCase.registerOutParameter(1, Types.VARCHAR);  //对于输出参数，必须注册为out参数类型，并且指定参数的数据类型，通过Types类：标识sql的常量的类
            properCase.setString(2, str);
            properCase.setInt(3, startPosition);
            properCase.setInt(4, length);
            properCase.execute();
            result = properCase.getString(1);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return result;
    }

}

程序执行如下所示：

🧚🏻‍♀️调用用户定义的存储函数

首先，我们需要创建数据库pro_rank表，并且插入一些数据，如下所示：

DROP TABLE IF EXISTS pro_rank;
CREATE TABLE pro_rank (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  team VARCHAR(25) NOT NULL,
  line VARCHAR(20) NOT NULL,
  rank INT NOT NULL
);


INSERT INTO 
    pro_rank(name, team, line,rank)
VALUES
    ('369','JDG','Top',3699),
		('Kanavi','JDG','Jug',3700),
		('Yagao','JDG','Mid',3666),
		('Hope','JDG','Adc',2222),
		('Missing','JDG','Sup',3333),
		('Bin','BLG','Top',3500),
		('Ning','NULL','Jug',0),
		('Cream','OMG','Mid',2200),
		('Light','LNG','Adc',2500),
		('Hang','WBG','Sup',-2000);

第二步，我们需要创建自定义函数get_pros，它用来查询pro_rank表中的name和team字段，查询条件为team，sql如下所示：

CREATE 
	OR REPLACE FUNCTION get_pros ( p_pattern VARCHAR ) RETURNS TABLE ( pro_name VARCHAR, pro_team VARCHAR ) LANGUAGE plpgsql AS $$ BEGIN
		RETURN query 
	SELECT 
		pro_rank.name,pro_rank.team 
	FROM
		pro_rank 
	WHERE
		pro_rank.team LIKE '%' ||  p_pattern || '%';

END;$$

get_pros方法接受一个参数:

p_pattern战队名称。

完整代码如下：

    @Test
    void testUserDefinedFunction() {
        getPros("JDG") ;
    }

    /**
     * 调用自定义函数get_pros
     * @param pattern
     */
    public void getPros(String pattern) {

        String SQL = "SELECT * FROM get_pros (?)";
        try (Connection conn = this.connect();
             PreparedStatement pstmt = conn.prepareStatement(SQL))
        {
            pstmt.setString(1,pattern);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                System.out.println(String.format("%s %s",
                        rs.getString("pro_name"),
                        rs.getString("pro_team")));
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

1. 首先，连接到postgre数据库。
2. 接下来，使用调用get_pros存储函数的查询创建PreparedStatement对象。
3. 然后，使用setString方法将参数传递给语句。
4. 之后，执行语句
5. 最后，处理结果集并输出选手数据。

代码执行如下：

未完待续..既然看到这里了，不妨来个大大的点赞吧