在本系列的第一部分中，我们讨论了如何安装和创建一个简单的类，我们运行了一个 SELECT 并返回一行和一列的格式化文本。现在是扩展并查看如何返回多个元组的时候了。
这里有一点免责声明；我不会对 Java 代码做太多评论，因为这不是一个 Java 教程。此处的示例仅用于教育目的，并非旨在具有高性能或用于生产！
返回表结构
第一个示例将展示我们如何从 PL/Java 函数中选择和返回一个表。我们将在这里继续使用表“ customer ”，探测 SQL 将是：
SELECT * FROM customer LIMIT 10;
我将在此处创建一个名为“ CustomerResultSet ”的新 Java 类，初始代码为：
package com.percona.blog.pljava;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.postgresql.pljava.ResultSetHandle;
public class CustomerResultSet implements ResultSetHandle {
private Connection conn;
private PreparedStatement stmt;
private final String m_url = "jdbc:default:connection";
private final String sql = "select * FROM customer LIMIT 10";
public CustomerResultSet() throws SQLException {
conn = DriverManager.getConnection(m_url);
stmt = conn.prepareStatement(sql);
}
@Override
public void close() throws SQLException {
stmt.close();
conn.close();
}
@Override
public ResultSet getResultSet() throws SQLException {
return stmt.executeQuery();
}
public static ResultSetHandle getCustomerPayments() throws SQLException {
return new CustomerResultSet();
}
}
请注意，我们正在实现 PL/Java 提供的 org.postgresql.pljava.ResultSetHandle接口。我们需要它是因为我们正在返回一个复杂的对象，并且当我们不需要操作返回的元组时，ResultSetHandle 接口是合适的。
现在我们正在使用 PL/Java 对象，我们需要告诉编译器在哪里可以找到这些引用，对于这里的第一个示例，我们需要 pljava-api jar，在我的例子中恰好是 pljava-api-1.6.2.jar . 如果你记得我编译的第一篇文章，我在这里使用的 PL/Java 和我的 JAR 文件位于“ ~/pljava-1_6_2/pljava-api/target/pljava-api-1.6.2.jar ”，编译命令为：
javac -cp "~/pljava-1_6_2/pljava-api/target/pljava-api-1.6.2.jar" com/percona/blog/pljava/CustomerResultSet.java jar -c -f /app/pg12/lib/pljavaPart2.jar com/percona/blog/pljava/CustomerResultSet.class
创建新的 JAR 文件后，我可以将其安装到 Postgres 中并创建函数“ getCustomerLimit10() ”：
SELECT sqlj.install_jar( 'file:///app/pg12/lib/pljavaPart2.jar', 'pljavaPart2', true );
SELECT sqlj.set_classpath( 'public', 'pljavaPart2' );
CREATE OR REPLACE FUNCTION getCustomerLimit10() RETURNS SETOF customer AS 'com.percona.blog.pljava.CustomerResultSet.getCustomerLimit10' LANGUAGE java;
函数调用的结果是：
test=# SELECT * FROM getCustomerLimit10();
customer_id | store_id | first_name | last_name | email | address_id | activebool | create_date | last_update | active
-------------+----------+------------+-----------+-------------------------------------+------------+------------+-------------+---------------------+--------
1 | 1 | MARY | SMITH | MARY.SMITH@sakilacustomer.org | 5 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
2 | 1 | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | 6 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
3 | 1 | LINDA | WILLIAMS | LINDA.WILLIAMS@sakilacustomer.org | 7 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
4 | 2 | BARBARA | JONES | BARBARA.JONES@sakilacustomer.org | 8 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
5 | 1 | ELIZABETH | BROWN | ELIZABETH.BROWN@sakilacustomer.org | 9 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
6 | 2 | JENNIFER | DAVIS | JENNIFER.DAVIS@sakilacustomer.org | 10 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
7 | 1 | MARIA | MILLER | MARIA.MILLER@sakilacustomer.org | 11 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
8 | 2 | SUSAN | WILSON | SUSAN.WILSON@sakilacustomer.org | 12 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
9 | 2 | MARGARET | MOORE | MARGARET.MOORE@sakilacustomer.org | 13 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
10 | 1 | DOROTHY | TAYLOR | DOROTHY.TAYLOR@sakilacustomer.org | 14 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
(10 rows)
test=#
返回前处理结果
返回一个普通 SQL 的结果有它的用法，比如可见性/权限控制，但我们通常需要在返回之前操作查询的结果，为此我们可以实现接口“ org.postgresql.pljava.ResultSetProvider ”。
在下面的示例中，我将实现一个简单的方法来使用哈希函数匿名化敏感数据。我还将创建一个帮助程序类来处理散列和加密函数，以保持 CustomerResultSet 类的清洁：
/**
* Crypto helper class that will contain all hashing and cryptographic functions
*/
package com.percona.blog.pljava;
import java.nio.charset.StandardCharsets;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
public class Crypto {
MessageDigest digest;
public Crypto() throws NoSuchAlgorithmException {
digest = MessageDigest.getInstance("SHA-256");
}
public String bytesToHex(byte[] hash) {
StringBuilder hexString = new StringBuilder(2 * hash.length);
for (int i = 0; i < hash.length; i++) {
String hex = Integer.toHexString(0xff & hash[i]);
if (hex.length() == 1) {
hexString.append('0');
}
hexString.append(hex);
}
return hexString.toString();
}
public String encode(String data, int min, int max) {
double salt = Math.random();
int sbstring = (int) ((Math.random() * ((max - min) + 1)) + min);
return bytesToHex(digest.digest((data + salt).getBytes(StandardCharsets.UTF_8))).substring(0, sbstring);
}
}
/**
* CustomerHash class
*/
package com.percona.blog.pljava;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.postgresql.pljava.ResultSetProvider;
public class CustomerHash implements ResultSetProvider {
private final Connection conn;
private final PreparedStatement stmt;
private final ResultSet rs;
private final Crypto crypto;
private final String m_url = "jdbc:default:connection";
public CustomerHash(int id) throws SQLException, NoSuchAlgorithmException {
String query;
crypto = new Crypto();
query = "SELECT * FROM customer WHERE customer_id = ?";
conn = DriverManager.getConnection(m_url);
stmt = conn.prepareStatement(query);
stmt.setInt(1, id);
rs = stmt.executeQuery();
}
@Override
public void close() throws SQLException {
stmt.close();
conn.close();
}
@Override
public boolean assignRowValues(ResultSet receiver, int currentRow) throws SQLException {
if (!rs.next())
return false;
try {
receiver.updateInt(1, rs.getInt("customer_id"));
receiver.updateInt(2, rs.getInt("store_id"));
receiver.updateString(3, crypto.encode(rs.getString("first_name"), 5, 45));
receiver.updateString(4, crypto.encode(rs.getString("last_name"), 5, 45));
receiver.updateString(5, crypto.encode(rs.getString("email"), 5, 41) + "@mail.com");
receiver.updateInt(6, rs.getInt("address_id"));
receiver.updateBoolean(7, rs.getBoolean("activebool"));
receiver.updateDate(8, rs.getDate("create_date"));
receiver.updateTimestamp(9, rs.getTimestamp("last_update"));
receiver.updateInt(10, rs.getInt("active"));
} catch (Exception e) {
Logger.getAnonymousLogger().log(Level.parse("SEVERE"), e.getMessage());
}
return true;
}
public static ResultSetProvider getCustomerAnonymized(int id) throws SQLException, NoSuchAlgorithmException {
return new CustomerHash(id);
}
}
类的数量在增加，所以与其一一提及，让我们只使用“ .java ”来构建类和“ .class ”来创建jar：
javac -cp "~/pljava-1_6_2/build/pljava-api-1.6.2.jar" com/percona/blog/pljava/*.java
jar -c -f /app/pg12/lib/pljavaPart2.jar com/percona/blog/pljava/*.class
请记住，每次更改 JAR 文件时，我们还需要将其重新加载到 Postgres 中。检查下一个示例，您将看到我正在重新加载 JAR 文件，创建并测试我们的新函数/方法：
test=# SELECT sqlj.replace_jar( 'file:///app/pg12/lib/pljavaPart2.jar', 'pljavaPart2', true );
replace_jar
-------------
(1 row)
test=# CREATE OR REPLACE FUNCTION getCustomerAnonymized(int) RETURNS SETOF customer AS 'com.percona.blog.pljava.CustomerHash.getCustomerAnonymized' LANGUAGE java;
CREATE FUNCTION
test=# SELECT * FROM getCustomerAnonymized(9);
customer_id | store_id | first_name | last_name | email | address_id | activebool | create_date | last_update | ac
tive
-------------+----------+---------------------+-------------------------------------+-----------------------------------------+------------+------------+-------------+---------------------+---
-----
9 | 2 | 72e2616ef0075e81929 | 3559c00ee546ae0062460c8faa4f24960f1 | 24854ed40ed42b57f077cb1cfaf916@mail.com | 13 | t | 2006-02-14 | 2006-02-15 09:57:20 |
1
(1 row)
test=#
伟大的！我们现在有了一种匿名数据的方法！
触发器
第二部分的最后一个主题是关于“触发器”，为了让它更有趣，我们将创建一个触发器来加密我们表的敏感数据。在前面的例子中使用散列函数的匿名化很好，但是如果我们对数据库进行了未经授权的访问会发生什么？数据以纯文本格式保存！
为了使这个示例尽可能小，我不会费心保护密钥，因为我们将在本系列的第三部分使用 Java 访问外部资源并使用 Vault 来保护我们的密钥，所以请继续关注！
好的，我们需要做的第一件事是创建加密/解密数据所需的一对密钥。我将使用“ OpenSSL ”创建它们并将它们存储到名为“keys”的表中！
openssl genrsa -out keypair.pem 2048 openssl pkcs8 -topk8 -nocrypt -in keypair.pem -outform PEM -out private.pem openssl rsa -in keypair.pem -outform PEM -pubout -out public.pem
现在我们有了密钥，我们需要清理密钥文件以从私钥和公钥中删除页眉和页脚数据，并删除所有断行，否则我们的 Java 代码会报错：
echo -n "CREATE TABLE keys(id int primary key, priv varchar not null, pub varchar not null); INSERT INTO keys VALUES(1, '" > keys.sql
cat private.pem | sed '1d;$d' | sed ':a;N;$!ba;s/\n//g' | tr -d '\n' >> keys.sql
echo -n "', '" >> keys.sql
cat public.pem | sed '1d;$d' | sed ':a;N;$!ba;s/\n//g' | tr -d '\n' >> keys.sql
echo -n "');" >> keys.sql
psql test < keys.sql
消毒后看起来像这样：
CREATE TABLE keys(id int primary key, priv varchar not null, pub varchar not null); INSERT INTO keys VALUES(1, 'MIIEvAIBADANBgkqhkiG9w0BAQEFAASCBKYwggSiAgEAAoIBAQCiAA4BE64JZpXwIGfsUanyL//drIcFZ1cmiCW6zWOxc6nL8AQ33MPyQup8g/ociJFGn/eEEYOvRMV2pVNo3qB3VQU4WHRWkq22x7mRfuhHmAnAJA3dic5fiJ1aCQgo7tEqlGPc0WqL+jMUXh6Wmktq1kDZagUGJRorw0f5Iaj60PycbGtgKaKDc4VHepkN1jl0rhpJBzjBehuvB88LLXJ/cHsMOp3q569jLsHtqymCA2wP68ldtfKtOowPW9togIUmgWY0Z2lWlefrlzmT2g3L/oYbPUxCmptOAMFD8NajdA518ohZAC8SPfUsD4CwL89oPrMZlX4RkTuc5UvBHiKrAgMBAAECggEAcJl5ImZ7YS1cqjrcAPYCGcQjJAD3GFpryOx4zQ5VbNHoA0ggpnNb/tdkBIf3ID4MO/qUH8fMr9YtKfpfr1SOVGNT7YYN1t68v36zDN4YtSqIHHTy7jkKqHxcYmhEs67K072wa5tjY0fUmSOSPzufj/K7wGJge5TuS9y/+fnbafkdfW/yz3X2YXL6T/jfjqI4h+P7Nhh5hlpD1KZfEWTAY5B5tBoLc4xaTIB8FTLclVWw3CAW8h60EwUAkyxCSbrP2I1FCrWsV6hJGy8U+hUQJUpyDdum9ZC1oAVewRrCkSH0ZP1XaQifDZoRv/1N7cCbQqaLJaVk4rzVOEv0DoCEAQKBgQDOMPMm2ioEredCx0hfmHWWayGA5as7VPDSzv1QH3g4AdjZFf2YxctXGNJNMpfqVvFiQCWxp9NpjUPpODRbmR2J+7tYgx3B445zDeXdBH2JTKhUgNTHjL6DrM6FTI3yaSsSJ77L0mDcFQ42nfWtfqkZd5lYfwiVC0eL86bp408+YQKBgQDJIks6RqVlDbHerIIqT1ToN+CQ+BCjx/Z6sk4BFIKBB8sU8VyVkZlvQpFHvT06oE/1NbyiQ3nVufGrm0kwMqx7MXGiA670E1Q+Q/mQ12uRByUlgd+LW4mp1Y6tln1lpP5pVqUOC/jtnXYQmEReU4Ye24E4AZhFU23J+oYoh3XEiwKBgEJFaWFrbWXjnxjPhGt1TRXziOks6ERBoMWg0boW40TdEx1y+/dGW3y69ZzqTfl7yEmT5ImdL04VoWYsMmfeZqgayLRCMCZJRVeld+P5tX+Tq+a9Iaahjfo0aIxfdqAbPUSwkZphG9Cg09iqHHSO6TrOPfM7oT6GSZCp11QFQ0sBAoGAeABi+8D8mx8hmWY5Pv8X/HiiHjwyyVTbpPbO/Wv8NPmuW69per9k2PHRdgjdCCZvrjBCfFlfznljS+yZLQ1+xP2J+4zRDESgBYpO0vED94JY0lj7Q8z4hICq4Lyh0kwvki+kyI2yFirVLy950wFoSu7R2NVywSH2pgQ3mOTBCeMCgYBL5KIRf1qwsCYaCggPls4pWKMjfxxO915h26/aaniEYaTNnhXRSRwkVOWoGHoUKfrqQdrvj/y5lgezn7mZM0CvnB6ZkGwDXxpcIYUnhR1Lnp3HNSqfigg+WjQASVCKuq3YUri3p+KQkrpED/O3B4FJW2Q4IReEuREEsKNkeH96ew==', 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAogAOAROuCWaV8CBn7FGp8i//3ayHBWdXJoglus1jsXOpy/AEN9zD8kLqfIP6HIiRRp/3hBGDr0TFdqVTaN6gd1UFOFh0VpKttse5kX7oR5gJwCQN3YnOX4idWgkIKO7RKpRj3NFqi/ozFF4elppLatZA2WoFBiUaK8NH+SGo+tD8nGxrYCmig3OFR3qZDdY5dK4aSQc4wXobrwfPCy1yf3B7DDqd6uevYy7B7aspggNsD+vJXbXyrTqMD1vbaICFJoFmNGdpVpXn65c5k9oNy/6GGz1MQpqbTgDBQ/DWo3QOdfKIWQAvEj31LA+AsC/PaD6zGZV+EZE7nOVLwR4iqwIDAQAB');
完成填充表格后，我们应该有一个包含私钥和公钥的漂亮表格。现在是创建我们的 Java 类的时候了。我将为加密函数重用“ Crypto ”类并创建一个新类来添加我们的触发器函数。我只会在此处添加 Crypto 类的相关部分，但您可以在我的GitHub 页面此处中找到此处描述的代码， 包括发布时的第一部分（和第三部分）。让我们来看看代码：
/**
* This is the relevant part of the Crypto class that will encrypt and decrypt our data using the certificates we generated above.
*/
public PublicKey getPublicKey(String base64PublicKey) {
PublicKey publicKey = null;
try {
X509EncodedKeySpec keySpec = new X509EncodedKeySpec(Base64.getDecoder().decode(base64PublicKey.getBytes()));
KeyFactory keyFactory = KeyFactory.getInstance("RSA");
publicKey = keyFactory.generatePublic(keySpec);
return publicKey;
} catch (NoSuchAlgorithmException e) {
e.printStackTrace();
} catch (InvalidKeySpecException e) {
e.printStackTrace();
}
return publicKey;
}
public PrivateKey getPrivateKey(String base64PrivateKey) {
PrivateKey privateKey = null;
PKCS8EncodedKeySpec keySpec = new PKCS8EncodedKeySpec(Base64.getDecoder().decode(base64PrivateKey.getBytes()));
KeyFactory keyFactory = null;
try {
keyFactory = KeyFactory.getInstance("RSA");
} catch (NoSuchAlgorithmException e) {
e.printStackTrace();
}
try {
privateKey = keyFactory.generatePrivate(keySpec);
} catch (InvalidKeySpecException e) {
e.printStackTrace();
}
return privateKey;
}
public String encrypt(String data, PublicKey publicKey) throws BadPaddingException, IllegalBlockSizeException,
InvalidKeyException, NoSuchPaddingException, NoSuchAlgorithmException {
Cipher cipher = Cipher.getInstance("RSA/ECB/PKCS1Padding");
cipher.init(Cipher.ENCRYPT_MODE, publicKey);
return Base64.getEncoder().encodeToString(cipher.doFinal(data.getBytes()));
}
public String decrypt(String data, PrivateKey privateKey) throws NoSuchPaddingException,
NoSuchAlgorithmException, InvalidKeyException, BadPaddingException, IllegalBlockSizeException {
Cipher cipher = Cipher.getInstance("RSA/ECB/PKCS1Padding");
cipher.init(Cipher.DECRYPT_MODE, privateKey);
return new String(cipher.doFinal(Base64.getDecoder().decode(data)));
}
现在我们可以用两个函数来实现这个类——当我们需要选择数据时，加密的触发函数和解密的函数：
package com.percona.blog.pljava;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.security.PrivateKey;
import java.security.PublicKey;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.crypto.BadPaddingException;
import javax.crypto.IllegalBlockSizeException;
import javax.crypto.NoSuchPaddingException;
import org.postgresql.pljava.ResultSetProvider;
import org.postgresql.pljava.TriggerData;
public class CustomerCrypto implements ResultSetProvider {
private final String m_url = "jdbc:default:connection";
private final Connection conn;
private PreparedStatement stmt;
private ResultSet rs;
//
private PrivateKey privateKey;
private PublicKey publicKey;
public CustomerCrypto() throws SQLException, NoSuchAlgorithmException {
String query;
query = "SELECT * FROM keys WHERE id = 1";
conn = DriverManager.getConnection(m_url);
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
if (!rs.next())
throw new SQLException("Keys not found!");
privateKey = Crypto.getPrivateKey(rs.getString("priv"));
publicKey = Crypto.getPublicKey(rs.getString("pub"));
}
public void processQuery(int id) throws SQLException, NoSuchAlgorithmException {
String query;
query = "SELECT * FROM customer WHERE customer_id = ?";
stmt = conn.prepareStatement(query);
stmt.setInt(1, id);
rs = stmt.executeQuery();
}
@Override
public void close() throws SQLException {
stmt.close();
conn.close();
}
public static int getLineNumber() {
return Thread.currentThread().getStackTrace()[2].getLineNumber();
}
@Override
public boolean assignRowValues(ResultSet receiver, int currentRow) throws SQLException {
if (!rs.next())
return false;
try {
receiver.updateInt(1, rs.getInt("customer_id"));
receiver.updateInt(2, rs.getInt("store_id"));
receiver.updateString(3, Crypto.decrypt(rs.getString("first_name"), this.privateKey));
receiver.updateString(4, Crypto.decrypt(rs.getString("last_name"), this.privateKey));
receiver.updateString(5, Crypto.decrypt(rs.getString("email"), this.privateKey));
receiver.updateInt(6, rs.getInt("address_id"));
receiver.updateBoolean(7, rs.getBoolean("activebool"));
receiver.updateDate(8, rs.getDate("create_date"));
receiver.updateTimestamp(9, rs.getTimestamp("last_update"));
receiver.updateInt(10, rs.getInt("active"));
} catch (Exception e) {
Logger.getAnonymousLogger().log(Level.parse("SEVERE"), e.getMessage());
}
return true;
}
private void encryptData(TriggerData td) throws InvalidKeyException, BadPaddingException, IllegalBlockSizeException, NoSuchPaddingException, NoSuchAlgorithmException, SQLException {
ResultSet _new = td.getNew();
_new.updateString("first_name", Crypto.encrypt(_new.getString("first_name"), this.publicKey));
_new.updateString("last_name", Crypto.encrypt(_new.getString("last_name"), this.publicKey));
_new.updateString("email", Crypto.encrypt(_new.getString("email"), this.publicKey));
}
public static void customerBeforeInsertUpdate(TriggerData td) throws SQLException, InvalidKeyException, BadPaddingException, IllegalBlockSizeException, NoSuchPaddingException, NoSuchAlgorithmException {
CustomerCrypto ret = new CustomerCrypto();
ret.encryptData(td);
}
public static ResultSetProvider getCustomerCrypto(int id) throws SQLException, NoSuchAlgorithmException {
CustomerCrypto ret = new CustomerCrypto();
ret.processQuery(id);
return ret;
}
}
上面代码的相关部分是“ customerBeforeInsertUpdate ”和“ encryptData ”方法，前者是数据库将访问的静态方法。Postgres 上的 PL/Java 期望找到一个带有“ void (TriggerData) ”签名的静态方法。它将调用“CustomerCrypto”对象的“encryptData”方法来完成这项工作。“encryptData”方法将从通过“TriggerData”对象传递的“ NEW ”指针恢复结果集，然后更改值以加密数据。我们需要在“ BEFORE ”事件中调用触发器，因为我们需要在它被持久化之前对其进行加密。
另一个重要的方法是“ getCustomerCrypto ”。我们需要能够解密数据，这种方法将对我们有所帮助。在这里，我们使用与前面示例中使用的技术相同的技术，其中我们实现了“ ResultSetProvider ”接口并在返回结果集之前操作数据。仔细查看“ assignRowValues ”方法，您会发现我们正在使用“ Crypto.decrypt ”方法解密那里的数据！
好的，是时候编译代码并检查它是否真的有效：
javac -cp "/v01/proj/percona/blog/pljava/pljava-1_6_2/build/pljava-api-1.6.2.jar" com/percona/blog/pljava/*.java
jar -c -f /app/pg12/lib/pljavaPart2.jar com/percona/blog/pljava/*.class
并创建数据库对象：
SELECT sqlj.replace_jar( 'file:///app/pg12/lib/pljavaPart2.jar', 'pljavaPart2', true );
CREATE FUNCTION customerBeforeInsertUpdate()
RETURNS trigger
AS 'com.percona.blog.pljava.CustomerCrypto.customerBeforeInsertUpdate'
LANGUAGE java;
CREATE TRIGGER tg_customerBeforeInsertUpdate
BEFORE INSERT ON customer
FOR EACH ROW
EXECUTE PROCEDURE customerBeforeInsertUpdate();
在这一点上，我们的数据还没有加密，但我们可以通过 noop 更新来完成，触发器将发挥它的魔力：
test=# SELECT * FROM customer LIMIT 3;
customer_id | store_id | first_name | last_name | email | address_id | activebool | create_date | last_update | active
-------------+----------+------------+-----------+-------------------------------------+------------+------------+-------------+---------------------+--------
1 | 1 | MARY | SMITH | MARY.SMITH@sakilacustomer.org | 5 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
2 | 1 | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | 6 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
3 | 1 | LINDA | WILLIAMS | LINDA.WILLIAMS@sakilacustomer.org | 7 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
(3 rows)
test=# UPDATE customer SET first_name = first_name, last_name = last_name, email = email;
UPDATE 599
test=# SELECT * FROM customer LIMIT 3;
customer_id | store_id |
first_name |
last_name
|
email
| address_id | activebool | create_date | last_update | active
-------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------+------------+------------+-------------+----------------------------+--------
3 | 1 | DT8oXb0VQvtFSIOv61zZfmUrpjWWGoeyl8D8tQl7naCLT31WlJn3U+uILYqUedSOdDSO17QdQKwChWG+DrcvYEih9RYyEPR2ja9deN4cn+vwHt/v09HDfmwrsJUt0UgP/fp78hCxkJDAV50KkMUsA23aeH5HRn9nCHOH0P
AcuId+7acCgwvU9YP8Sx2KVeVnLaBrzpeRLrsmAczQLUAXilXfdFC8uT2APBfwx1So2eCe+kSOsjcu1yTwlsa95Dnfu/N++Zm1D4knKUrAuNm5svTHjIz+B4HKXFMPz/Yk7KYF6ThB6OshIomyRvSEtKu5torfdwAvT3tsgP2DLWiKgQ== | H0YRoi10z36
tnNSXpBs/oYfMQRbAhfUYLIcE885Dhxmy2mbuhecCCqPcye5/++MhUwmEQG2pBgfsqWHLOnAgbqjaG3O0reipVysYK7cMysX1w5RVINsyD5H3vCqgnHESfdRuhW3b00InkR2qCtBYX1QJ1tKJZz89D2AOjoTq5jTum00vcLT06h6ZxVh1RKLNAuGpY9qN57m
/9a4JZuff9poYjw2PPQ6kTxhtbFl3bw+B3sJUcLFuFMYUoAAHsVETQRAerH1ncG9Uxi+xQjUtTVBqZdjvED+eydetH7vsnjBuYDtXD9XAn14qmALx5NfvwpU5jfpMOPOM4xP1BRVA2Q== | DpWBfhhii4LRPxZ9XJy8xoNne+qm051wD5Gd9AMHc+oIhx/B
ln6H+lcAM3625rKN1Vw/lG6VkQo2EnoZz/bhFtULvAOAUiBxerBDbYe0lYWqI50NxnFJbkexMkjDSiuoglh3ilRBn6Z+WGLc7FfEprOd1+tULW2gcwLI68uoEhfSY7INQZuGXfOUMAM4roB2fWvEfylL1ShbiGTRjX7KGXQbXLJtm7xel8J2VhdCecXzxzY2
Mtnu3EXGNpFy9atTXxE/fI0C5AX/u2FDZiOHz9xV7sB3atcqAeXwJB0smpBnPbwI3BN+ptzsWnhyFNNS+ol4QayMDgFhi/tp2+lCAQ== | 7 | t | 2006-02-14 | 2021-08-08 19:10:29.337653 | 1
4 | 2 | jo3zKr6lJ5zMN5f3/BPgENhs9CdzDu7F/uFxAf6uO9MAKc7X+++ipk/OBbvvA8vpaJ7DTgph9LshRvpYIqPMwS6IubkScDOSRDoLBNW9z2oMF3dB46R86LK0pTEVVrGaddjnPzaAEh7Uwzy3LncC1y7pJqGKW1b3RGUE8n
4SgstMo/4tRNUe/AUcPn9FXkCoc5jFvn8gPwVoTRKwhYu0oTco2gNKZs1rmFdmkmobGaMjZuhWyWG2PO1oXIVPkpgILmD42yAMDxWkS4DVvgJChXJRukjBzfOitsQwHapjqPqf/q3mfBaQzNUwROcSfGBe6KlI5yfjWU309KRKaCYWNQ== | MMhPovG/N3k
Xjou6kS9V7QtEvlA5NS8/n62KVRVVGEnsh5bhwEhBZxlK72AQv8e4niATWPEiJJU6i7Z08NkU5FWNIvuWwlGTdEEW+kK7XQXib6cNAdnmo4RH72SWhDxEp3tMwwoZif2932H8WDEbNdP6bCP69ekBA7Z+nGtXaeh+H9BAaLf1e6XunBj2YN7zs4sFWB2Kxs2
IugLWd9g9677BWzUeJIzpJfVLro4HYlzASh9AMKb8wPRU0LlEpxtqUdejj7IY5M1hVhDTCCLSQjSqJscqzG1pYQ04W7KNdGwWxJPMjvyPC2K4H+HQuW0IWVjvFpyYd/5q1eIQX+vjdw== | oF4nyIjtVtWuydg6QiSg3BDadWe48nhbBEZSLrR5GVigA768
E3n1npN6sdstzG7bRVnphtfwIZwQ3MUFURWCbUCe0VqioNgVXFhiRvr3DAw2AH64ss/h65B2U5whAygnq4kiy5JvPD0z0omtfs9913QeoO+ilvPVLEc0q3n0jD9ZQlkNVfHSytx1NY86gWnESquTVhkVQ55QDV8GY70YLX9V6nU7ldu+zpNLmf2+rfpxqbRC
i16jnHGDcTT7CKeq+AxbiJDeaaAmSPpxTZsrX4sXFW4rpNtSmOyuyHZziy8rkN8xSpyhvrmxjC7EYe4bn6L/+hay108Wn0BSFYe2ow== | 8 | t | 2006-02-14 | 2021-08-08 19:10:29.337653 | 1
<...>
(3 rows)
test=#
太棒了，我们的数据被加密了！类的“解密”部分怎么样？让我们来看看：
test=# CREATE OR REPLACE FUNCTION getCustomerCrypto(int) RETURNS SETOF customer AS 'com.percona.blog.pljava.CustomerCrypto.getCustomerCrypto' LANGUAGE java;
CREATE FUNCTION
test=# SELECT * FROM getCustomerCrypto(10);
customer_id | store_id | first_name | last_name | email | address_id | activebool | create_date | last_update | active
-------------+----------+------------+-----------+-----------------------------------+------------+------------+-------------+---------------------+--------
10 | 1 | DOROTHY | TAYLOR | DOROTHY.TAYLOR@sakilacustomer.org | 14 | t | 2006-02-14 | 2006-02-15 09:57:20 | 1
(1 row)
test=#
像魅力一样工作！在这里，我们完成了第二部分，此时，我们能够查询和操作数据库中的对象。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
