Kylin JDBC 驱动

2017年12月29日 0 条评论 1.67k 次阅读 0 人点赞

身份认证

基于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进行查询

Java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/**
     * 使用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

Java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/**
     * 使用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等 。

Java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/**
     * 展示元数据信息
     *
     * @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);
        }
    }

完整版代码

Java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
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

Vim
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
<?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>

 

© 著作权归作者所有

勇敢，和生活的艰难无关。

文章评论（0）