Java使用JDBC操作PI数据库的完整指南

一、环境准备

1.1 驱动配置

<!-- Maven依赖配置 -->
<dependency>
    <groupId>com.osisoft</groupId>
    <artifactId>pi-jdbc</artifactId>
    <version>4.0.0</version>
</dependency>

1.2 连接参数

参数	示例值	说明
URL	jdbc:pi://192.168.1.100	PI服务器地址
用户名	admin	PI管理员账号
密码	pi@1234	认证密码
数据库名	PI_ARCHIVE	默认归档数据库

二、基础操作流程

2.1 连接建立与关闭

// 加载驱动
Class.forName("com.osisoft.jdbc.Driver");

// 建立连接
String url = "jdbc:pi://192.168.1.100/PI_ARCHIVE";
Properties props = new Properties();
props.setProperty("user", "admin");
props.setProperty("password", "pi@1234");

try (Connection conn = DriverManager.getConnection(url, props)) {
    System.out.println("PI数据库连接成功");
} catch (SQLException e) {
    System.err.println("连接失败: " + e.getMessage());
}

三、数据查询操作

3.1 基础查询

String sql = "SELECT tag, time, value FROM piarchive..picomp "
           + "WHERE tag = 'SINUSOID' AND time > '*-1d'";

try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {
    
    while (rs.next()) {
        String tag = rs.getString("tag");
        Timestamp time = rs.getTimestamp("time");
        double value = rs.getDouble("value");
        System.out.printf("%s\t%s\t%.2f%n", tag, time, value);
    }
}

3.2 参数化查询

String tagName = "TEMP_001";
String query = "SELECT time, value FROM piarchive..picomp "
            + "WHERE tag = ? AND time BETWEEN ? AND ?";

try (PreparedStatement pstmt = conn.prepareStatement(query)) {
    pstmt.setString(1, tagName);
    pstmt.setTimestamp(2, Timestamp.valueOf("2023-01-01 00:00:00"));
    pstmt.setTimestamp(3, Timestamp.valueOf("2023-01-02 23:59:59"));
    
    ResultSet rs = pstmt.executeQuery();
    // 处理结果集...
}

四、数据写入操作

4.1 单点数据写入

String insertSQL = "INSERT INTO piarchive..picomp (tag, time, value) "
                + "VALUES (?, ?, ?)";

try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
    pstmt.setString(1, "NEW_TAG");
    pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
    pstmt.setDouble(3, 42.5);
    
    int affectedRows = pstmt.executeUpdate();
    System.out.println("写入记录数: " + affectedRows);
}

4.2 批量写入优化

String batchSQL = "INSERT INTO piarchive..picomp (tag, time, value) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(batchSQL)) {
    
    // 模拟1000条数据
    for (int i = 0; i < 1000; i++) {
        pstmt.setString(1, "BATCH_TAG_" + i%10);
        pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis() - i*60000));
        pstmt.setDouble(3, Math.random() * 100);
        pstmt.addBatch();
        
        if (i % 100 == 0) {
            pstmt.executeBatch(); // 每100条执行一次
        }
    }
    pstmt.executeBatch(); // 执行剩余批次
}

五、PI特有功能实现

5.1 时间范围查询

String timeRangeSQL = "SELECT * FROM piarchive..picomp "
                   + "WHERE tag = 'FLOW_001' AND time > '*-2h'";
// PI特有语法：'*-2h'表示当前时间前推2小时

5.2 聚合函数使用

String aggSQL = "SELECT tag, MIN(value), MAX(value), AVG(value) "
             + "FROM piarchive..picomp "
             + "WHERE tag IN ('TEMP_001', 'PRESS_002') "
             + "AND time > '*-1d' GROUP BY tag";

六、异常处理机制

6.1 完整异常处理

try {
    // 数据库操作代码...
} catch (SQLException e) {
    System.err.println("SQL错误: " + e.getMessage());
    System.err.println("错误代码: " + e.getErrorCode());
    System.err.println("SQL状态: " + e.getSQLState());
} catch (Exception e) {
    System.err.println("系统错误: " + e.getMessage());
} finally {
    // 确保资源释放
}

6.2 连接池配置

// 使用HikariCP连接池
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:pi://192.168.1.100/PI_ARCHIVE");
config.setUsername("admin");
config.setPassword("pi@1234");
config.setMaximumPoolSize(10);

try (HikariDataSource ds = new HikariDataSource(config);
     Connection conn = ds.getConnection()) {
    // 使用连接...
}

七、性能优化技巧

7.1 查询优化建议

优化措施	效果提升	实现难度
使用PreparedStatement	30-50%↑	低
合理设置FetchSize	20-40%↑	中
避免SELECT *	40-70%↑	低
使用PI时间表达式	50-80%↑	中

7.2 最佳实践代码

// 高效查询示例
String optimizedSQL = "SELECT tag, time, value FROM piarchive..picomp "
                   + "WHERE tag = ? AND time > '*-6h' "
                   + "ORDER BY time DESC";

try (PreparedStatement pstmt = conn.prepareStatement(optimizedSQL,
        ResultSet.TYPE_FORWARD_ONLY,
        ResultSet.CONCUR_READ_ONLY)) {
    
    pstmt.setString(1, "CRITICAL_TAG");
    pstmt.setFetchSize(1000); // 批量获取
    
    ResultSet rs = pstmt.executeQuery();
    // 处理结果...
}

八、元数据操作

8.1 获取标签列表

String metaSQL = "SELECT DISTINCT tag FROM piarchive..picomp "
              + "WHERE tag LIKE 'TEMP_%'";

try (Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(metaSQL)) {
    
    List<String> tags = new ArrayList<>();
    while (rs.next()) {
        tags.add(rs.getString(1));
    }
    System.out.println("温度标签: " + tags);
}

8.2 表结构查询

DatabaseMetaData meta = conn.getMetaData();
try (ResultSet tables = meta.getTables(null, "piarchive", "%", new String[]{"TABLE"})) {
    while (tables.next()) {
        System.out.println("表名: " + tables.getString("TABLE_NAME"));
    }
}

九、事务管理

9.1 基本事务控制

try {
    conn.setAutoCommit(false);
    
    // 执行多个操作
    updateTagValue(conn, "TAG_001", 25.5);
    insertNewTag(conn, "TAG_NEW");
    
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    System.err.println("事务回滚: " + e.getMessage());
} finally {
    conn.setAutoCommit(true);
}

9.2 保存点使用

Savepoint sp1 = conn.setSavepoint("SAVEPOINT_1");
try {
    // 部分操作...
    Savepoint sp2 = conn.setSavepoint();
    
    // 更多操作...
    conn.releaseSavepoint(sp2);
} catch (SQLException e) {
    conn.rollback(sp1);
}

十、完整工具类示例

10.1 PI数据库工具类

public class PIJdbcUtil {
    private static HikariDataSource dataSource;
    
    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:pi://192.168.1.100/PI_ARCHIVE");
        config.setUsername("admin");
        config.setPassword("pi@1234");
        config.setMaximumPoolSize(15);
        dataSource = new HikariDataSource(config);
    }
    
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    
    public static List<Map<String, Object>> query(String sql, Object... params) {
        List<Map<String, Object>> result = new ArrayList<>();
        
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            
            ResultSet rs = pstmt.executeQuery();
            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount();
            
            while (rs.next()) {
                Map<String, Object> row = new LinkedHashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    row.put(meta.getColumnLabel(i), rs.getObject(i));
                }
                result.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
    
    public static int update(String sql, Object... params) {
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        }
    }
}

10.2 使用示例

// 查询示例
List<Map<String, Object>> data = PIJdbcUtil.query(
    "SELECT * FROM piarchive..picomp WHERE tag = ? AND time > ?",
    "PRESS_001", Timestamp.valueOf("2023-01-01 00:00:00")
);

// 更新示例
int rows = PIJdbcUtil.update(
    "INSERT INTO piarchive..picomp (tag, time, value) VALUES (?, ?, ?)",
    "NEW_TAG", new Timestamp(System.currentTimeMillis()), 99.9
);

关键注意事项：