clickhouse jdbc 增删改查

提示：本文利用MergeTree作为引擎
pom依赖

		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-slf4j-impl</artifactId>
			<version>2.11.0</version>
		</dependency>
		<dependency>
			<groupId>ru.yandex.clickhouse</groupId>
			<artifactId>clickhouse-jdbc</artifactId>
			<version>0.2.6</version>
		</dependency>

获取连接（示例）：

    public void getConn() throws SQLException, ClassNotFoundException{
    	String address = "jdbc:clickhouse://127.0.0.1:8123/default";
    	Class.forName("ru.yandex.clickhouse.ClickHouseDriver");
        connection = DriverManager.getConnection(address);

    }

建表（示例）：

    public void creatTable() throws SQLException{
    	String sql = "create table IF NOT EXISTS t_skip("
    			+ "id String,"
    			+ "website String,"
    			+ "code String,"
    			+ "createtime datetime"
//    			+ ","   //索引部分
//    			+ "index ix_id id type minmax granularity 5," 
//    			+ "index ix_length(length(id)*8) type set(2) granularity 5,"
//    			+ "index ix_id_code (id,code) type ngrambf_v1(3,256,2,0) granularity 5,"
//    			+ "index ix_token id type tokenbf_v1(256,2,0) granularity 5"
    			+ ")"
    			+ "engine=MergeTree() "
    			+ "partition by toYYYYMM(createtime) "
    			+ "order by id primary key id" ; 
    	PreparedStatement ps = connection.prepareStatement(sql);
    	ps.execute();
    	ps.close();
    }

单条新增（示例）：

    public void insert() throws SQLException, ParseException{
    	String sql = "INSERT INTO t_skip (id,website,code,createtime) VALUES (?,?,?,?)";
    	PreparedStatement ps = connection.prepareStatement(sql);
    	ps.setString(1, "1");
    	ps.setString(2, "2");
    	ps.setString(3, "3");
    	ps.setTimestamp(4, LocalDateTime.of("2022-07-01 11:11:11").toTimestamp() ); //时间戳转换
    	ps.execute();
    	ps.close();
    }

多条新增（示例）仅需几百毫秒：

public void insert100000() {
    	System.out.println(LocalDateTime.now());
    	String sql = "INSERT INTO t_skip (id,website,code,createtime) VALUES (?,?,?,?)";
        PreparedStatement prepareStatement = null;
        try {
            connection.setAutoCommit(false);
            prepareStatement = connection.prepareStatement(sql);
            for(int i=0;i<100000;i++) {
            	prepareStatement.setString(1, "1");
            	prepareStatement.setString(2, "2");
            	prepareStatement.setString(3, "3");
            	prepareStatement.setTimestamp(4, LocalDateTime.of("2022-06-02 11:11:11").toTimestamp() );
                prepareStatement.addBatch();
            }
            prepareStatement.executeBatch();
            connection.commit();
        } catch (Exception e) {
           e.printStackTrace();
        } finally {
            if (prepareStatement != null) {
                try {
                    prepareStatement.close();
                } catch (SQLException e) {
                }
            }
        }
        System.out.println(LocalDateTime.now());
    }

删除（示例）：

    public void delete() throws SQLException{
    	String sql = "ALTER TABLE t_skip DELETE WHERE id = ?";
    	PreparedStatement ps = connection.prepareStatement(sql);
    	ps.setString(1, "1111111111");
    	ps.execute();
    	ps.close();
    }

分区删除（示例）：

	public void deleteByPARTITION() throws SQLException{
    	String sql = "ALTER TABLE t_skip DROP PARTITION '202205'";
    	PreparedStatement ps = connection.prepareStatement(sql);
    	ps.execute();
    	ps.close();
    }

修改（示例）：

    public void update() throws SQLException{
    	String sql = "ALTER TABLE t_skip UPDATE website=? WHERE id=?";
    	PreparedStatement ps = connection.prepareStatement(sql);
    	ps.setString(1, "xxxxxxxxxxx");
    	ps.setString(2, "1");
    	ps.execute();
    	ps.close();
    	
    }
    ```