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=PI
pi.jdbc.driver=com.osisoft.jdbc.Driver
pi.jdbc.url=jdbc:pioledb://localhost/Data Source=PIAddress; Integrated Security=SSPI
#pool settings
jdbc.pool.init=1
jdbc.pool.minIdle=20
jdbc.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);
            }
        }   
    }
}