Java 访问PI 数据库：（2）JDBC访问PI

技术说明

1. 采用 Maven 集成

   • 从 PI JDBC Driver 安装后的目录下取出 PIHOME\JDBC\PIJDBCDriver.jar。将其放在项目的lib 下，引入项目中
     

     <!-- 引入本地jar包 -->    <dependency>        <groupId>com.osisoft</groupId>        <artifactId>PIJDBCDriver</artifactId>        <version>1.5.17051.1</version>        <scope>system</scope>        <systemPath>${project.basedir}/src/main/webapp/WEB-INF/lib/PIJDBCDriver.jar</systemPath>    </dependency>

2. 使用 C3P0 数据源配置信息

   • conf.properties

pi.jdbc.type=PIpi.jdbc.driver=com.osisoft.jdbc.Driverpi.jdbc.url=jdbc:pioledb://localhost/Data Source=PIAddress; Integrated Security=SSPI#pool settingsjdbc.pool.init=1jdbc.pool.minIdle=20jdbc.pool.maxActive=50

• spring-context.xml 中的配置

<!-- C3P0连接池配置 -->    <bean id="dataSource_PI" class="com.mchange.v2.c3p0.ComboPooledDataSource">        <property name="driverClass" value="${pi.jdbc.driver}" />        <property name="jdbcUrl" value="${pi.jdbc.url}" />        <property name="user" value="" />        <property name="initialPoolSize" value="${jdbc.pool.init}" />        <property name="minPoolSize" value="${jdbc.pool.minIdle}" />        <property name="maxPoolSize" value="${jdbc.pool.maxActive}" />        <property name="maxIdleTime" value="60000" />    </bean>

1. 用常用JDBC访问数据库的方式即可查询PI 数据库

package com.bkc.test;import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Properties;public class GetBAMetaData{    public static void main(String[] args)    {        // arguments: java GetBAMetaData dasName [trustedConnection protocolOrder logLevel]        // example: java GetBAMetaData myDas yes nettcp:5462 2        String dasName = "localhost";        String dataSourceName = "192.168.1.32";        String isTrustedConnection = "Yes";        String protocolOrder = "Https/Soap:5461,NetTcp:5462";        String logLevel = "0";        System.out.println("\nArguments:");        System.out.println("\tData Access Server Name: " + dasName);        System.out.println("\tUse trusted connection?: " + isTrustedConnection);        System.out.println("\tProtocol order: " + protocolOrder);        System.out.println("\tLog level: " + logLevel);        Connection connection = null;        String url = "";        String driverClassName = "com.osisoft.jdbc.Driver";        ResultSet resultSet = null;        Properties properties = new Properties();        //url = "jdbc:piintegrator://" + dasName;        //url = "jdbc:pioledb://" + dasName + "/Data Source=" + dataSourceName + "; Integrated Security=SSPI";        url = "jdbc:pioledb://" + dasName + "/Data Source=" + dataSourceName + "; Integrated Security=SSPI";        System.out.println(url);        properties.put("TrustedConnection", isTrustedConnection);        properties.put("ProtocolOrder", protocolOrder);        properties.put("LogConsole", "True");        properties.put("LogLevel", logLevel);        try        {            Class.forName(driverClassName).newInstance();            connection = DriverManager.getConnection(url, properties);              DatabaseMetaData metaData = connection.getMetaData();            System.out.println(metaData.getDriverName() + " " + metaData.getDriverVersion());             System.out.println(metaData.getDatabaseProductName());            System.out.println(metaData.getDatabaseProductVersion() + "\n");            // get all tables and views            String tTypes[] = new String[] {"TABLE", "VIEW"};            resultSet = metaData.getTables(null, null, "%", tTypes);            if(!resultSet.first())                throw new Exception ("No rows in the result set.");            // we are just interested in those 4 columns            System.out.print("Catalog || ");            System.out.print("Schema || ");            System.out.print("Table || ");            System.out.println("Table Type\n");            // read the data            while (!resultSet.isAfterLast()) {                System.out.print(resultSet.getString("TABLE_CAT") + " || ");                System.out.print(resultSet.getString("TABLE_SCHEM") + " || ");                System.out.print(resultSet.getString("TABLE_NAME") + " || ");                System.out.println(resultSet.getString("TABLE_TYPE"));                resultSet.next();            }        }        catch (Exception ex)        {            System.err.println(ex);        }        finally        {            try {                if(resultSet != null) resultSet.close();                if(connection != null) connection.close();            } catch (SQLException ex) {                System.err.println(ex);            }        }       }}