一、通过数据库信息访问第三方数据库
本例子是从一张存储数据库信息表中查出第三方数据库信息，再进行访问的
1、 对数据库密码的加密、解密类
public class SecretHelper { public byte[] random(int len) { byte[] buf = new byte[len]; random.nextBytes(buf); return buf; } public byte[] password(byte[] plain) throws GeneralSecurityException { Mac mac = macKeysetHandle.getPrimitive(Mac.class); return mac.computeMac(plain); } public boolean verify(byte[] cipher, byte[] plain) { try { Mac mac = macKeysetHandle.getPrimitive(Mac.class); mac.verifyMac(cipher, plain); return true; } catch (GeneralSecurityException e) { logger.error("fail on verify password ", e); } return false; } public byte[] encrypt(byte[] plain, byte[] salt) throws GeneralSecurityException { Aead aead = aesKeysetHandle.getPrimitive(Aead.class); return aead.encrypt(plain, salt); } public byte[] decrypt(byte[] cipher, byte[] salt) throws GeneralSecurityException { Aead aead = aesKeysetHandle.getPrimitive(Aead.class); return aead.decrypt(cipher, salt); } @PostConstruct void init() throws IOException, GeneralSecurityException { random = new Random(); final var aes = new File("aes.json"); if (aes.exists()) { logger.info("load aes key from {}", aes); aesKeysetHandle = CleartextKeysetHandle.read(JsonKeysetReader.withFile(aes)); } else { aesKeysetHandle = KeysetHandle.generateNew( AeadKeyTemplates.AES256_GCM); logger.info("generate aes key file {}", aes); CleartextKeysetHandle.write(aesKeysetHandle, JsonKeysetWriter.withFile(aes)); } final var mac = new File("mac.json"); if (mac.exists()) { logger.info("load mac key from {}", mac); macKeysetHandle = CleartextKeysetHandle.read(JsonKeysetReader.withFile(mac)); } else { macKeysetHandle = KeysetHandle.generateNew( MacKeyTemplates.HMAC_SHA256_256BITTAG); logger.info("generate mac key file {}", mac); CleartextKeysetHandle.write(macKeysetHandle, JsonKeysetWriter.withFile(mac)); } } private KeysetHandle aesKeysetHandle; private KeysetHandle macKeysetHandle; private Random random; }
2、第三方数据库信息类
public class Fwqsz implements Serializable { @Override public String toString() { return "(" + user + "@" + host + "/" + sid + ")"; } public HikariDataSource open(String password) { return open(user, password); } public HikariDataSource open(String user, String password) { final var url = url(); logger.info("连接 {}@{}", user, url); var ds = new HikariDataSource(); ds.setJdbcUrl(url); ds.setUsername(user); ds.setPassword(password); return ds; } public String url() { return String.format("jdbc:oracle:thin:@%s:1521:%s", host, sid); } public void test(String password) throws SQLException { try ( var conn = DriverManager.getConnection(url(), user, password); var stmt = conn.createStatement(); var rs = stmt.executeQuery("select CURRENT_TIMESTAMP from dual")) { if (rs.next()) { logger.info("test oracle server: timestamp {}", rs.getString(1)); } } } @Id @GeneratedValue(strategy = GenerationType.AUTO) private int id; @Column(length = 255, nullable = false) private String host; @Column(name = "`user`", length = 20, nullable = false) private String user; @Lob @JsonIgnore @Column(nullable = false) private byte[] salt; @Lob @JsonIgnore @Column(name = "`password`", nullable = false) private byte[] password; @Column(length = 20, nullable = false) private String sid; @Nationalized @Column(length = 100) private String yt; @Type(type = "yes_no") @Column(nullable = false) private boolean enable; @Version private int version; private Date updatedAt; private Date createdAt; //使用时需要实现get、set方法 }
数据库建表时，salt和password的类型为type="blob"
新增一条第三方数据库信息时，密码需要加密
@Transactional(rollbackFor = Exception.class) @Override public void add(CreateFwqsz form) throws GeneralSecurityException { Fwqsz it = new Fwqsz(); var now = new Date(); it.setHost(form.getHost()); it.setUser(form.getUser()); it.setSalt(secretHelper.random(32)); it.setPassword(secretHelper.encrypt(form.getPassword().getBytes(), it.getSalt())); it.setSid(form.getSid()); it.setYt(form.getYt()); it.setEnable(form.getEnable()); it.setUpdatedAt(now); it.setCreatedAt(now); fwqszRepository.save(it); }
3、访问第三方数据库。先对密码进行解密，new Object[]{}里面的参数根据前面sql语句？的顺序传入
try (var ds = fwqsz.open(new String(secretHelper.decrypt(fwqsz.getPassword(), fwqsz.getSalt())))) { var open = new JdbcTemplate(ds); List<YecxKm> yecxKmList = = open.query("SELECT SERVERIP,SACC_CODE_C,IYEAR,SCORPCODE,SACC_NAME_C,ILEVEL,NOPEN_BAL FROM SUBJECT_C WHERE SERVERIP=? AND SCORPCODE=? AND IYEAR=? AND (SACC_CODE_C=? OR SACC_CODE_C LIKE ? ) ", new Object[]{corptype, scorpcode, year, saccCodeC, saccCodeC + "-%"}, new QjyecxMapper()); } catch (GeneralSecurityException e) { logger.error("{}", e.getMessage()); throw e; }
注意：
因为链接是 ds 负责管理的，要么做成单例 给所有的beans用，要么用完就释放。把ds放try()内（用完之后自动关闭数据库连接,上面例子为try语法）,否则放finally内close。
4、建一个QjyecxMapper类
public class QjyecxMapper implements RowMapper<YecxKm> { @Override public YecxKm mapRow(ResultSet rs, int rowNum) throws SQLException { var it = new YecxKm(); it.setServerip(rs.getString("SERVERIP")); it.setScorpcode(rs.getString("SCORPCODE")); it.setSaccCodeC(rs.getString("SACC_CODE_C")); it.setSaccNameC(rs.getString("SACC_NAME_C")); it.setIyear(rs.getString("IYEAR")); it.setIlevel(rs.getString("ILEVEL")); it.setNopenBal(rs.getString("NOPEN_BAL")); return it; } }
二、JdbcTemplate的in语法参数解决办法
上面的JdbcTemplate解决不了in语法，得使用NamedParameterJdbcTemplate
官网文档例子连接：https://www.technicalkeeda.com/spring-tutorials/spring-jdbctemplate-in-clause-example
例子：
var jdbcTemplate = new NamedParameterJdbcTemplate(ds); HashMap<String, Object> params = new HashMap<>(); params.put("corptype", corptype); params.put("scorpcode", scorpcode); params.put("iyear", iyear); params.put("sdocids", list); List<Pzmxzb> pzmxzbList=open.query("SELECT SDOC_ID,SDOC_NO,SDOC_TYPE,SENTRIEDBY,SPERIODCODE,DDOC_DATE FROM GL_DOC_M WHERE SERVERIP=:corptype AND SCORPCODE=:scorpcode AND IYEAR=:iyear AND SDOC_ID IN (:sdocids)", params, new PzmxzbMapper());
list为集合，比如List<String>字符串集合
注意：oracle的in语法的集合长度不能超过1000，否则会报错，其实超过200就不合理了
