Java连接ClickHouse实现数据库基本增删查
引言
ClickHose作为一个新的列式数据库,其在实时数仓中作为结果存储的数据库。对于Java程序员来说,我们需要对于ClickHouse进行数据的增删查改,进而支持后续的处理业务。我使用面向对象的思想,实现Java操作ClickHouse的增删改,希望对于学习和使用ClickHouse的你有些帮助。
一、依赖导入
<dependency><groupId>ru.yandex.clickhouse</groupId><artifactId>clickhouse-jdbc</artifactId><version>0.1.52</version></dependency>
二、连接实体类
public class ConnEntiy { // 驱动器 String driverName; // 连接对象地址 String url; // 用户 String user; // 密码 String password; public ConnEntiy() { } public ConnEntiy(String driverName, String url, String user, String password) { this.driverName = driverName; this.url = url; this.user = user; this.password = password; } public String getDriverName() { return driverName; } public void setDriverName(String driverName) { this.driverName = driverName; } public String getUrl() { return url; } public void setUrl(String url) { this.url = url; } public String getUser() { return user; } public void setUser(String user) { this.user = user; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; }}
三、接口类
public interface Utils { public Connection connection(ConnEntiy connEntiy); public void close(AutoCloseable... closes); public boolean insert(Connection connection,String sql,String...params); public boolean delete(Connection connection,String sql,String...params); public ResultSet QueryResultSet(Connection connection,String sql,String...params);}
四、接口实现类
public class ClickHouseUtils implements Utils{ @Override public Connection connection(ConnEntiy connEntiy) { Connection conn=null; try { Class.forName(connEntiy.getDriverName()); conn= DriverManager.getConnection(connEntiy.getUrl(),connEntiy.getUser(),connEntiy.getPassword()); }catch(Exception e){ System.out.println("connection fail ,please check your entities"); } return conn; } @Override public void close(AutoCloseable... closes) { for (AutoCloseable close : closes) { if (close!=null) { try { close.close(); } catch (Exception e) { e.printStackTrace(); }finally { close=null; } } } } @Override public boolean insert(Connection connection, String sql, String... params) { boolean b =false; ClickHousePreparedStatement pst=null; if (connection==null) { System.out.println("connection is empty"); System.exit(-1); } try { pst= (ClickHousePreparedStatement) connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { pst.setObject(i+1,params[i]); } b= pst.execute(); } catch (SQLException throwables) { throwables.printStackTrace(); }finally{ close(pst,connection); } return b; } @Override public boolean delete(Connection connection, String sql, String... params) { boolean b =false; ClickHousePreparedStatement pst=null; if (connection==null) { System.out.println("connection is empty"); System.exit(-1); } try { pst= (ClickHousePreparedStatement) connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { pst.setObject(i+1,params[i]); } b= pst.execute(); } catch (SQLException throwables) { throwables.printStackTrace(); }finally{ close(pst,connection); } return b; } @Override public ResultSet QueryResultSet(Connection connection, String sql, String... params) { ResultSet rst=null; ClickHousePreparedStatement pst=null; if (connection==null) { System.out.println("connection is empty"); System.exit(-1); } try { pst= (ClickHousePreparedStatement) connection.prepareStatement(sql); for (int i = 0; i < params.length; i++) { pst.setObject(i+1,params[i]); } rst = pst.executeQuery(); } catch (SQLException throwables) { throwables.printStackTrace(); }finally{ close(rst,pst,connection); } return rst; }}
五、测试类
public class test { public static void main(String[] args) { String driverName="ru.yandex.clickhouse.ClickHouseDriver"; String url="jdbc:clickhouse://192.168.50.100:9000/test"; String user="root"; String password="root"; String sql="select * from test;"; String[] params={}; ConnEntiy connEntiy = new ConnEntiy(driverName, url, user, password); Utils utils = new ClickHouseUtils(); Connection conn = utils.connection(connEntiy); utils.insert(conn,sql,params); }}