Java 集成kylin

怼烎@ 2022-05-21 09:19 189阅读 0赞

1、创建maven工程，添加kylin和c3p0依赖

<dependency>		
     <groupId>org.apache.kylin</groupId>
     <artifactId>kylin-jdbc</artifactId>
     <version>2.2.0</version>
 </dependency>
 
 <dependency>
     <groupId>c3p0</groupId>
     <artifactId>c3p0</artifactId>
     <version>0.9.1.2</version>
 </dependency>

2、编写代码

（1）创建连接池

package com.ldy.kylin;
 
 import java.beans.PropertyVetoException;
 import java.sql.Connection;
 import java.sql.SQLException;
 
 import com.mchange.v2.c3p0.ComboPooledDataSource;
 
 /**
 * @类名: KylinConnPool 
 * @描述: Kylin的jdbc连接池 
 * @创建者: lidongyang 
 * @修改时间: 2018年7月3日 下午4:03:59 
 */
 public class KylinJdbcConnPool {
 	public static final String url = "jdbc:kylin://192.168.2.20:7070/ldy_test";
 	public static final String username = "ldy";
 	public static final String password = "123456";
 
 	private static final KylinJdbcConnPool instance = new KylinJdbcConnPool();
 	private static ComboPooledDataSource comboPooledDataSource;
 
 	static {
 		try {
 			try {
 				// Class.forName("com.mysql.jdbc.Driver");
 				Class.forName("org.apache.kylin.jdbc.Driver");
 			} catch (ClassNotFoundException e) {
 				e.printStackTrace();
 			}
 			comboPooledDataSource = new ComboPooledDataSource();
 			comboPooledDataSource.setDriverClass("org.apache.kylin.jdbc.Driver");
 			comboPooledDataSource.setJdbcUrl(url);
 			comboPooledDataSource.setUser(username);
 			comboPooledDataSource.setPassword(password);
 			// 下面是设置连接池的一配置
 			comboPooledDataSource.setMaxPoolSize(20);
 			comboPooledDataSource.setMinPoolSize(5);
 		} catch (PropertyVetoException e) {
 			e.printStackTrace();
 		}
 	}
 
 	public synchronized Connection getConnection() {
 		Connection connection = null;
 		try {
 			connection = comboPooledDataSource.getConnection();
 		} catch (SQLException e) {
 			e.printStackTrace();
 		}
 
 		return connection;
 	}
 
 	public static KylinJdbcConnPool getInstance() {
 		return instance;
 	}
 }

（2）编写JDBC帮助类

package com.ldy.kylin;
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 
 import org.apache.commons.lang.StringUtils;
 
 /**
 * @类名: KylinHelper 
 * @描述: kylin操作数据库帮助类 
 * @创建者: lidongyang 
 * @修改时间: 2018年7月3日 下午4:38:51 
 */
 public class KylinJdbcHelper {
 
 	/**
 	 * @方法名: executeQuery 
 	 * @描述: 执行能够返回结果集的查询语句 
 	 * @创建者: lidongyang 
 	 * @修改时间: 2018年7月3日 下午3:57:03 
 	 * @param connection
 	 * @param sql
 	 * @param params
 	 * @return
 	 */
 	public static ResultSet executeQuery(Connection connection, String sql, Object... params) {
 		if (StringUtils.isBlank(sql)) {
 			System.out.println("sql语句不为空");
 			return null;
 		}
 		ResultSet rs = null;
 		PreparedStatement ps = null;
 		System.out.println("sql--> " + sql);
 		try {
 			ps = connection.prepareStatement(sql);
 			if(null != params) {
 				for (int i = 0; i < params.length; i++) {
 					ps.setObject(i+1, params[i]);
 				}
 			}
 			rs = ps.executeQuery();
 		} catch (SQLException e) {
 			e.printStackTrace();
 		}
 		return rs;
 	}
 
 	/**
 	 * @方法名: executeUpdate 
 	 * @描述: 执行update、insert、delete等语句，返回值为受影响的行数 
 	 * @创建者: lidongyang 
 	 * @修改时间: 2018年7月3日 下午3:58:20 
 	 * @param connection
 	 * @param sql
 	 * @param params
 	 * @return
 	 */
 	public static int executeUpdate(Connection connection, String sql, Object... params) {
 		int resCount = 0;
 		if (StringUtils.isBlank(sql)) {
 			System.out.println("sql语句不能为空");
 			return resCount;
 		}
 		PreparedStatement ps = null;
 		System.out.println("sql--> " + sql);
 		try {
 			ps = connection.prepareStatement(sql);
 			if(null != params) {
 				for (int i = 0; i < params.length; i++) {
 					ps.setObject(i+1, params[i]);
 				}
 			}
 			resCount = ps.executeUpdate();
 		} catch (SQLException e) {
 			e.printStackTrace();
 		} finally {
 		}
 		return resCount;
 
 	}
 
 	/**
 	 * @方法名: closeConnection 
 	 * @描述: 关闭连接 
 	 * @创建者: lidongyang 
 	 * @修改时间: 2018年7月3日 下午4:21:28 
 	 * @param connection
 	 */
 	public static void closeConnection(Connection connection) {
 		try {
 			connection.close();
 		} catch (SQLException e) {
 			e.printStackTrace();
 		}
 	}
 }

（3）编写demo

package com.ldy.kylin;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.util.ArrayList;
    import java.util.List;
    
    public class KylinJdbcDemo {
    	
        public static void main(String[] args) {
            Connection connection = KylinJdbcConnPool.getInstance().getConnection();
            ResultSet rs = null;
            String sql = "select dt,sum(new_user_num)"
    		+ " from ldy_test_table"
    		+ " where dt > ?"
    		+ " group by dt"
    		+ " order by dt"
    		+ " limit 10 offset 0";
            try {
                rs = KylinJdbcHelper.executeQuery(connection, sql, 20180501);
                while (rs.next()) {
            	System.out.println(rs.getString(1));
                }
    
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                KylinJdbcHelper.closeConnection(connection);
            }
        }
    }

4、查看结果

![70][]

[70]: /images/20220521/04bd2581ef1f4f43baebc5ee1040519a.png