目录
身份认证
基于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 驱动