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);
    }
}