驱动连接规范
JDBC驱动规范
JDBC 驱动版本要求
| HALO版本 | JDBC 版本 |
|---|---|
| HALO 14 Oracle 模式 | halojdbc-jdk1.8 |
| HALO 14 PG 模式 | PG 官方版本 |
HALO官方Oracle模式版本Jar驱动：
halojdbc-jdk1.8.jar
POSTGRESQL官方版本Jar驱动（支持PG 8.2及以上版本）：
postgresql-42.6.0.jar
JDBC连接串规范
Halo Oracle模式连接方式：
spring.datasource.url=jdbc:halo://{IP}:{1921}/{DatabaseName}? clobAsText=true&binaryTransfer=true&blobAsBytea=true
clobAsText=true 兼容oracleclob类型
blobAsBytea=true 兼容oracleblob类型
binaryTransfer=true 开启日期二进制输出
Halo PG模式连接方式：
spring.datasource.url=jdbc:postgresql://{IP}:{1921}/{DatabaseName}
连接池规范
Hikari 连接池配置规范
# 连接池配置
maximumPoolSize=20
minimumIdle=5
connectionTimeout=30000
idleTimeout=600000
# 有效性检测
connectionTestQuery=SELECT 1
validationTimeout=5000
# 连接泄漏处理
leakDetectionThreshold=60000
Druid 连接池配置规范
# 连接池配置
initialSize=5
maxActive=20
minIdle=5
maxWait=60000
# 有效性检测
validationQuery=SELECT 1
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
# 连接泄漏处理
removeAbandoned=true
removeAbandonedTimeout=1800
logAbandoned=true
# 监控和统计
statSqlMaxSize=2048
slowSqlMillis=5000
filters=stat,log4j
Java-Demo
import java.sql.*;
public class HaloCRUDExample {
private static final String URL = "jdbc:halo://10.16.6.187:1922/test?clobAsText=true&blobAsBytea=true&dataBaseTypeName=halo";
private static final String USERNAME = "test";
private static final String PASSWORD = "test";
public static void main(String[] args) {
try {
// 连接到数据库
Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// 插入数据
insertData(conn, "John Doe", "john.doe@example.com");
// 查询数据
selectData(conn);
// 更新数据
updateData(conn, "John Doe", "new_email@example.com");
// 删除数据
deleteData(conn, "John Doe");
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void insertData(Connection conn, String name, String email) throws SQLException {
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, name);
pstmt.setString(2, email);
int rowsInserted = pstmt.executeUpdate();
if (rowsInserted > 0) {
System.out.println("A new user was inserted successfully!");
}
}
}
private static void selectData(Connection conn) throws SQLException {
String sql = "SELECT * FROM users";
try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
System.out.println("Name: " + rs.getString("name") + ", Email: " + rs.getString("email"));
}
}
}
private static void updateData(Connection conn, String name, String newEmail) throws SQLException {
String sql = "UPDATE users SET email = ? WHERE name = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, newEmail);
pstmt.setString(2, name);
int rowsUpdated = pstmt.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("An existing user was updated successfully!");
}
}
}
private static void deleteData(Connection conn, String name) throws SQLException {
String sql = "DELETE FROM users WHERE name = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, name);
int rowsDeleted = pstmt.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("An existing user was deleted successfully!");
}
}
}
}
.Net-Demo
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Security;
using System.Runtime.InteropServices;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Npgsql.Internal;
using Npgsql.PostgresTypes;
using Npgsql.Util;
using NpgsqlTypes;
using NUnit.Framework;
using static Npgsql.Tests.TestUtil;
using System.Text.RegularExpressions;
namespace Npgsql.Tests;
public class ConHaloProcTests
{
private static NpgsqlConnection conn = new NpgsqlConnection();
[SetUp]
public void Setup()
{
Console.WriteLine("----------------test begin----------------");
var cs = "Host=10.16.6.187;Port=1922;Username=test;Password=test;Database=test";
conn.ConnectionString = cs;
conn.Open();
}
[TearDown]
public void TearDown()
{
conn.Close();
Console.WriteLine("----------------test end----------------");
}
[Test]
public void InsertData(){
using (var cmd = new NpgsqlCommand("INSERT INTO users (name, email) VALUES (@name, @email)", conn))
{
cmd.Parameters.AddWithValue("name", "John Doe");
cmd.Parameters.AddWithValue("email", "john.doe@example.com");
cmd.ExecuteNonQuery();
}
Console.WriteLine("Data inserted successfully!");
}
[Test]
public void SelectData(){
using (var cmd = new NpgsqlCommand("SELECT name, email FROM users", conn))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(\$"Name: {reader.GetString(0)}, Email: {reader.GetString(1)}");
}
}
}
[Test]
public void UpdateData(){
using (var cmd = new NpgsqlCommand("UPDATE users SET email = @newEmail WHERE name = @name", conn))
{
cmd.Parameters.AddWithValue("name", "John Doe");
cmd.Parameters.AddWithValue("newEmail", "new_email@example.com");
cmd.ExecuteNonQuery();
}
Console.WriteLine("Data updated successfully!");
}
[Test]
public void DeleteData(){
using (var cmd = new NpgsqlCommand("DELETE FROM users WHERE name = @name", conn))
{
cmd.Parameters.AddWithValue("name", "John Doe");
cmd.ExecuteNonQuery();
}
Console.WriteLine("Data deleted successfully!");
}
}