Kylin JDBC 驱动

身份认证

基于Apache Kylin身份认证的restful服务支持以下参数：

• user : username
• password : password
• ssl: true/false，默认为 false，如果设置为true那么所有的服务都会遵循https协议。

URL连接格式

jdbc:kylin://<hostname>:<port>/<kylin_project_name>

• 如果 “ssl” = true, 那么连接端口”port” 应该是Kylin服务器的HTTPS端口；
• 如果 端口”port”没有指定，driver将会使用默认端口： HTTP协议端口号为 80，HTTPS协议端口号为 443；
• 必须指定Kylin Server上的工程名”kylin_project_name”，并确保该工程下的Model和Cube可用。

通过Statement进行查询

 /**
 * 使用Statement进行查询
 *
 * @param connection JDBC Connection
 */
private static void queryWithStatement(Connection connection) {
    Statement state = null;
    try {
        state = connection.createStatement();
        ResultSet resultSet = state.executeQuery("SELECT * FROM kylin_country LIMIT 15");
        showQueryResult(resultSet);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        closeStatement(state);
    }
}

通过PreparedStatement进行查询

目前prepared statement支持的占位符参数类型：

• setString
• setInt
• setShort
• setLong
• setFloat
• setDouble
• setBoolean
• setByte
• setDate
• setTime
• setTimestamp

/**
     * 使用PrepareStatement进行查询
     *
     * @param connection JDBC Connection
     */
    private static void queryWithPrepareStatement(Connection connection) {
        PreparedStatement pstm = null;
        try {
            pstm = connection.prepareStatement("SELECT * FROM kylin_country WHERE country=?");
            pstm.setString(1, "PS");
            ResultSet resultSet = pstm.executeQuery();
            showQueryResult(resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeStatement(pstm);
        }
    }

获取ResultSet 结果集metadata

Kylin JDBC驱动提供了相关方法以获取元数据信息，可以借助sql模式过滤器(比如%)列出catalog, schema, table 和column等 。

/**
     * 展示元数据信息
     *
     * @param connection Kylin JDBC Connection
     */
    private static void showMetaData(Connection connection) {
        ResultSet tables = null;
        try {
            tables = connection.getMetaData().getTables(null, null, "%", null);
            while (tables.next()) {
                for (int i = 0; i < 10; i++) {
                    System.out.println(tables.getString(i + 1));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResultSet(tables);
        }
    }

完整版代码

public class KylinJDBC {
    public static void main(String[] args) {
        Driver driver = null;
        Connection connection = null;
        try {
            driver = (Driver) Class.forName("org.apache.kylin.jdbc.Driver").newInstance();
            Properties info = new Properties();
            info.put("user", "ADMIN");
            info.put("password", "KYLIN");
            connection = driver.connect("jdbc:kylin://ip:7070/learn_kylin", info);

            queryWithStatement(connection);

            queryWithPrepareStatement(connection);

            showMetaData(connection);

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(connection);
        }
    }

    /**
     * 使用Statement进行查询
     *
     * @param connection JDBC Connection
     */
    private static void queryWithStatement(Connection connection) {
        Statement state = null;
        try {
            state = connection.createStatement();
            ResultSet resultSet = state.executeQuery("SELECT * FROM kylin_country LIMIT 15");
            showQueryResult(resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeStatement(state);
        }
    }

    /**
     * 使用PrepareStatement进行查询
     *
     * @param connection JDBC Connection
     */
    private static void queryWithPrepareStatement(Connection connection) {
        PreparedStatement pstm = null;
        try {
            pstm = connection.prepareStatement("SELECT * FROM kylin_country WHERE country=?");
            pstm.setString(1, "PS");
            ResultSet resultSet = pstm.executeQuery();
            showQueryResult(resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeStatement(pstm);
        }
    }

    /**
     * 展示元数据信息
     *
     * @param connection Kylin JDBC Connection
     */
    private static void showMetaData(Connection connection) {
        ResultSet tables = null;
        try {
            tables = connection.getMetaData().getTables(null, null, "%", null);
            while (tables.next()) {
                for (int i = 0; i < 10; i++) {
                    System.out.println(tables.getString(i + 1));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResultSet(tables);
        }
    }

    /**
     * 查询结果ResultSet进行格式化显示
     *
     * @param resultSet Kylin JDBC 查询结果
     */
    private static void showQueryResult(ResultSet resultSet) {
        ResultSetMetaData metaData = null;
        try {
            metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                System.out.print(metaData.getColumnLabel(i + 1) + "\t");
            }
            System.out.println();
            while (resultSet.next()) {
                for (int i = 0; i < columnCount; i++) {
                    System.out.print(resultSet.getObject(i + 1) + "\t");
                }
                System.out.println();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResultSet(resultSet);
        }
    }

    /**
     * 关闭JDBC连接
     *
     * @param connection JDBC Connection
     */
    private static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭Statement
     *
     * @param statement JDBC JDBC Connection Statement
     */
    private static void closeStatement(Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 关闭ResultSet
     *
     * @param resultSet Kylin JDBC 查询结果集
     */
    private static void closeResultSet(ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.w3sun.kylin.jdbc</groupId>
    <artifactId>jdbc-test</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <kylin.version>2.0.0</kylin.version>
        <encoding>UTF-8</encoding>
    </properties>

    <dependencies>
        <!-- 导入Kylin JDBC的依赖 -->
        <dependency>
            <groupId>org.apache.kylin</groupId>
            <artifactId>kylin-jdbc</artifactId>
            <version>${kylin.version}</version>
        </dependency>

    <build>
        <pluginManagement>
            <plugins>
                <!-- 编译scala的插件 -->
                <plugin>
                    <groupId>net.alchim31.maven</groupId>
                    <artifactId>scala-maven-plugin</artifactId>
                    <version>3.2.2</version>
                </plugin>
                <!-- 编译java的插件 -->
                <plugin>
                    <groupId>org.apache.maven.plugins</groupId>
                    <artifactId>maven-compiler-plugin</artifactId>
                    <version>3.5.1</version>
                </plugin>
            </plugins>
        </pluginManagement>
        <plugins>
            <plugin>
                <groupId>net.alchim31.maven</groupId>
                <artifactId>scala-maven-plugin</artifactId>
                <executions>
                    <execution>
                        <id>scala-compile-first</id>
                        <phase>process-resources</phase>
                        <goals>
                            <goal>add-source</goal>
                            <goal>compile</goal>
                        </goals>
                    </execution>
                    <execution>
                        <id>scala-test-compile</id>
                        <phase>process-test-resources</phase>
                        <goals>
                            <goal>testCompile</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <executions>
                    <execution>
                        <phase>compile</phase>
                        <goals>
                            <goal>compile</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>


            <!-- 打jar插件 -->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>2.4.3</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <filters>
                                <filter>
                                    <artifact>*:*</artifact>
                                    <excludes>
                                        <exclude>META-INF/*.SF</exclude>
                                        <exclude>META-INF/*.DSA</exclude>
                                        <exclude>META-INF/*.RSA</exclude>
                                    </excludes>
                                </filter>
                            </filters>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

转载请注明：雪后西塘 » Kylin JDBC 驱动