SpringBoot集成ClockHouse之JDBC

• 本来是用JPA来操作的，但是JPA无法插入数据、查询没问题，我也不知道用JPA该咋整了，百度上搜了一圈，好麻烦还不如直接用JDBC

引入依赖

<!-- clickhouse依赖 -->
<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.3.1-patch</version>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

配置数据库连接信息

spring.datasource.username=aizi
spring.datasource.password=0.0123.
spring.datasource.database=default
spring.datasource.address=jdbc:clickhouse://192.168.1.219:8123
spring.datasource.connection-timeout=5000
spring.datasource.socket-timeout=10000
#spring.datasource.url=jdbc:clickhouse://192.168.1.219:8123/default
#spring.datasource.driver-class-name=ru.yandex.clickhouse.ClickHouseDriver

创建建个表

create table av_girl(
	name String,
	height decimal(3,2),
	born Date
)engine = MergeTree()
 order by(born); 
# "engine = MergeTree()"  表引擎类型
# "order by(born)"        born字段作为排序键（ClickHouse允许不设主键。如没定义主键，则使用排序键作为主键）

# 插入数据
INSERT INTO av_girl (*) VALUES 
('仓井空',1.55,'1981-04-26'),
('波多野结衣',1.63,'1988-05-24'),
('天海翼',1.60,'1988-03-08'),
('明花绮罗',1.58,'1988-10-02'),
('宇都宫紫苑',1.61,'1994-03-01'),
('柚木提娜',1.54,'1986-10-29'),
('吉泽明步',1.61,'1984-03-03'),
('古川伊织',1.60,'1992-09-25'),
('葵司',1.61,'1994-09-23'),
('麻仓优',1.60,'1989-12-24'),
('希志爱野',1.57,'1988-02-01'),
('小川阿佐美',1.67,'1985-04-11'),
('濑亚美莉',1.67,'1987-09-16'),
('蒂亚',1.59,'1991-09-23'),
('冲田杏梨',1.72,'1986-10-28'),
('麻生希',1.70,'1988-12-20'),
('桃谷绘里香',1.65,'1994-06-15'),
('京香JULIA',1.58,'1987-05-25'),
('三上悠亚',1.59,'1993-08-16'),
('大桥未久',1.58,'1987-12-24'),
('佐佐木明希',1.66,'1979-08-19'),
('希崎杰西卡',1.60,'1989-06-10'),
('小野夕子',1.60,'1993-12-03'),
('藤浦惠',1.55,'1989-05-04'),
('前田香织',1.60,'1990-09-08'),
('木南菜',1.58,'1995-11-17'),
('林由奈',1.59,'1976-11-30'),
('芽森滴',1.59,'1992-08-06'),
('铃木心春',1.60,'1993-12-01');

实体类

@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
public class AvGirl{
    private String name;
    private double height;
    private Data born;

    @Override
    public String toString() {
        return "AvGirl{" +
                "姓名='" + name + '\'' +
                ", 身高=" + height +
                ", 出生日期=" + born +
                '}';
    }
}

DAO

package com.jpa.clickhouse.dao;

import com.jpa.clickhouse.pojo.AvGirl;
import lombok.NonNull;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;
import ru.yandex.clickhouse.ClickHouseConnection;
import ru.yandex.clickhouse.ClickHouseDataSource;
import ru.yandex.clickhouse.ClickHouseStatement;
import ru.yandex.clickhouse.settings.ClickHouseProperties;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Slf4j
@Repository
public class ClickHouseJdbcRepository {

    private static ClickHouseConnection connection = null;

    @Value("${spring.datasource.address}")
    private String clickhouseAddress;
    @Value("${spring.datasource.username}")
    private String clickhouseUsername;
    @Value("${spring.datasource.password}")
    private String clickhousePassword;
    @Value("${spring.datasource.database}")
    private String clickhouseDB;
    @Value("${spring.datasource.socket-timeout}")
    private int clickhouseSocketTimeout;
    @Value("${spring.datasource.connection-timeout}")
    private int clickhouseConnectionTimeout;

    /**
     * 分页查询所有数据
     * 排序规则：按照`time`字段”升序“排序
     *
     * @param page 页码
     * @param size 数量
     * @return 查询到的数据
     */
    public List<AvGirl> findAll(int page, int size) {
        try {
            String sql = "SELECT * FROM av_girl ag order by born ASC LIMIT ?,?";
            page = (page - 1) * size;
            ClickHouseConnection conn = getConn();
            assert conn != null;
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, page);
            statement.setInt(2, size);
            List<AvGirl> avGirls = executeQuery(statement);
            for (AvGirl avGirl : avGirls) {
                log.info(avGirl.toString());
            }
            return avGirls;
        } catch (SQLException e) {

            e.printStackTrace();
        }

        return null;
    }

    /**
     * 查询`date`之前的的数据
     *
     * @param date 日期
     * @param page 页码
     * @param size 数量
     * @return 查询到的数据
     */
    public List<AvGirl> findByBornBefore(Date date, int page, int size) {
        try {
            String sql = "SELECT * FROM av_girl ag WHERE born < ? order by born ASC LIMIT ?,?";
            page = (page - 1) * size;
            ClickHouseConnection conn = getConn();
            assert conn != null;
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setDate(1, date);
            statement.setInt(2, page);
            statement.setInt(3, size);
            List<AvGirl> avGirls = executeQuery(statement);
            for (int i = 0; i < avGirls.size(); i++) {
                log.info(avGirls.get(i).toString());
            }
            return avGirls;
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return null;
    }

    /**
     * 删除所有数据
     * 由于SQL不能这样写：ALTER TABLE 表名 DELETE ；必须加上where条件，那么只能将将日期搞大一天
     *
     * @return
     */
    public boolean deleteAll() {
        long time = System.currentTimeMillis() + 86400000L;
        Date date = new Date(time);
        log.info(date.toString());
        return deleteByBornAfter(date);
    }

    /**
     * 删除数据
     * 根据日期来删除此日期之前的数据
     *
     * @param date 日期
     * @return
     */
    public boolean deleteByBornAfter(Date date) {
        String sql = "ALTER TABLE av_girl DELETE WHERE `born` < ?";
        try {
            ClickHouseConnection conn = getConn();
            assert conn != null;
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setDate(1, date);
            preparedStatement.executeUpdate();
            log.info("SQL --- > " + sql.replace("?", date.toString()));
            return true;
        } catch (SQLException e) {
            log.error("哦豁！出问题了 SQL --- > " + sql.replace("?", date.toString()));
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 保存数据到Clickhouse数据库   --- 插入13w数据需要0.23秒，
     *
     * @param avGirls   List<AvGirl>
     * @param batchSize 批量插入数量
     */
    public void save(List<AvGirl> avGirls, int batchSize) {
        try {
            String sql = " INSERT INTO av_girl (*) VALUES (?,?,?)";
            ClickHouseConnection conn = getConn();
            assert conn != null;
            PreparedStatement statement = conn.prepareStatement(sql);
            for (int i = 0; i < avGirls.size(); i++) {
                AvGirl avGirl = avGirls.get(i);
                statement.setString(1, avGirl.getName());
                statement.setDouble(2, avGirl.getHeight());
                statement.setDate(3, avGirl.getBorn());
                statement.addBatch(); // 将上面的数据添加到PreparedStatement对象中
                // 每满batchSize 次插入一次数据
                if (i % batchSize == 0) {
                    statement.executeBatch(); // 插入
                    statement.clearParameters(); // 清除PreparedStatement里的数据
                }
            }
            // 最后插入不足batchSize条的数据 【这里是不是该判断下PreparedStatement对象里是否还有参数。。。但是不知道怎么弄】
            /*  statement.executeBatch();*/
            if (avGirls.size() % batchSize != 0) {
                statement.executeBatch(); // 插入
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 保存数据到Clickhouse数据库
     *
     * @param avGirls List<AvGirl>
     */
    public void save(List<AvGirl> avGirls) {
        // 默认10万条数据插入一次
        int batchSize = 100000;
        save(avGirls, batchSize);
    }

    /**
     * 更新数据
     * 根据`name`字段更新
     */
    public void updateByName(String name, String newName) {
        try {
            String sql = "ALTER TABLE av_girl UPDATE name = ? WHERE name = ?";
            ClickHouseConnection conn = getConn();
            assert conn != null;
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setString(1, newName);
            preparedStatement.setString(2, name);
            preparedStatement.executeUpdate();
            log.info("SQL --- >  ALTER TABLE av_girl UPDATE name = " + newName + " WHERE name = " + name);
        } catch (SQLException e) {
            log.error("哦豁！出问题了 SQL --- >  ALTER TABLE av_girl UPDATE name = " + newName + " WHERE name = " + name);
            e.printStackTrace();
        }
    }

    private ClickHouseConnection getConn() {
        log.info(" --- > 连接ClickHouse数据库");
        if (connection != null) {
            return connection;
        }
        ClickHouseProperties properties = new ClickHouseProperties();
        properties.setUser(clickhouseUsername);
        properties.setPassword(clickhousePassword);
        properties.setDatabase(clickhouseDB);
        properties.setSocketTimeout(clickhouseSocketTimeout);
        properties.setConnectionTimeout(clickhouseConnectionTimeout);
        ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(clickhouseAddress, properties);
        try {
            connection = clickHouseDataSource.getConnection();
            return connection;
        } catch (SQLException e) {
            log.error("连接ClickHouse失败！");
            e.printStackTrace();
        }
        return null;
    }


    private List<AvGirl> executeQuery(@NonNull PreparedStatement preparedStatement) {

        try {
            log.info("preparedStatement --- >" + preparedStatement);
            ResultSet resultSet = preparedStatement.executeQuery();
            log.info("------ > " + resultSet.toString());
            ResultSetMetaData metaData = resultSet.getMetaData();
            List<AvGirl> list = new ArrayList<>();
            while (resultSet.next()) {
                AvGirl symbolPrice = new AvGirl();
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String columnLabel = metaData.getColumnName(i);
                    switch (columnLabel) {
                        case "name":
                            symbolPrice.setName(resultSet.getString(columnLabel));
                            break;
                        case "height":
                            symbolPrice.setHeight(resultSet.getDouble(columnLabel));
                            break;
                        case "born":
                            symbolPrice.setBorn(resultSet.getDate(columnLabel));
                            break;
                    }
                }
                list.add(symbolPrice);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 执行自己拼接好的SQL --- 插入13w数据需要3秒，
     *
     * @param sql 拼接好的SQL语句
     * @return
     */
    @SneakyThrows
    public Object execute(String sql) {
        ClickHouseConnection conn = getConn();

        assert conn != null;
        ClickHouseStatement statement = conn.createStatement();
        long start = System.currentTimeMillis();
        boolean execute = statement.execute(sql);
        log.info("耗时" + ((System.currentTimeMillis()) - start) + "毫秒");
        return execute;
    }

}

测试

• 测试类

@Slf4j
@RestController
@RequestMapping("/test")
public class PriceSaveController {
    @Autowired
    private ClickHouseJdbcRepository avGirlDao;


    @SneakyThrows
    @GetMapping("/add")
    public String add() {
        List<AvGirl> avGirlList = new ArrayList<>();
        long time = new SimpleDateFormat("yyyy-MM-dd").parse("1996-12-15").getTime();
        AvGirl avGirl = new AvGirl("橋本ありな", 1.60, new java.sql.Date(time));
        avGirlList.add(avGirl);
        avGirlDao.save(avGirlList);
        return null;
    }

    @GetMapping("/update")
    public void up() {
        avGirlDao.updateByName("仓井空", "苍老师");
    }

    /**
     * 分页查询所有数据
     *
     * @param page 页码
     * @param size 每页数量
     * @return 返回查询到的数据
     */
    @GetMapping("/findAll/{page}/{size}")
    public List<AvGirl> findAll(@PathVariable("page") int page, @PathVariable("size") int size) {
        return avGirlDao.findAll(page, size);
    }


    /**
     * 查询成熟的小姐姐
     *
     * @return 返回查询到的数据
     */
    @GetMapping("/findByBornBefore/{time}/{page}/{size}")
    public List<AvGirl> findByBornBefore(@PathVariable("time") String time, @PathVariable("page") int page, @PathVariable("size") int size) {
        try {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date date = new Date(simpleDateFormat.parse(time).getTime());
            return avGirlDao.findByBornBefore(date, page, size);
        } catch (ParseException e) {
            e.printStackTrace();
            return null;
        }

    }

    /**
     * 删除数据
     * 根据日期来删除此日期之前的数据
     *
     * @param date 日期
     * @return
     */
    @SneakyThrows
    @GetMapping("/delByBornAfter/{date}")
    public String deleteByBornAfter(@PathVariable(name = "date") String date) {
        long time = new SimpleDateFormat("yyyy-MM-dd").parse(date).getTime();
        boolean delete = avGirlDao.deleteByBornAfter(new Date(time));
        return "删除数据 --- > " + delete;

    }


    /**
     * 删除所有数据
     *
     * @return
     */
    @GetMapping("/delAll")
    public String deleteAll() {
        boolean delete = avGirlDao.deleteAll();
        return "删除数据 --- > " + delete;

    }
}

查询所有数据

• 按照出生日期升序排序

/**
 * 分页查询所有数据
 *
 * @param page 页码
 * @param size 每页数量
 * @return 返回查询到的数据
 */
@GetMapping("/findAll/{page}/{size}")
public List<AvGirl> findAll(@PathVariable("page") int page, @PathVariable("size") int size) {
    return avGirlDao.findAll(page, size);
}

按条件查询

• 查询成熟点的小姐姐

/**
 * 查询成熟的小姐姐
 *
 * @return 返回查询到的数据
 */
@GetMapping("/findByBornBefore/{time}/{page}/{size}")
public List<AvGirl> findByBornBefore(@PathVariable("time") String time, @PathVariable("page") int page, @PathVariable("size") int size) {
    try {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date date = new Date(simpleDateFormat.parse(time).getTime());
        return avGirlDao.findByBornBefore(date, page, size);
    } catch (ParseException e) {
        e.printStackTrace();
        return null;
    }
}

• http://192.168.1.216:8080/test/findByBornBefore/1990-01-01/1/10

更新操作

• 咱将”仓井空“改为“苍老师”

@GetMapping("/update")
public void up() {
    avGirlDao.updateByName("仓井空", "苍老师");
}

添加数据

• 试了下插入数据好像很快，13万数据230毫秒

@SneakyThrows
@GetMapping("/add")
public String add() {
    List<AvGirl> avGirlList = new ArrayList<>();
    long time = new SimpleDateFormat("yyyy-MM-dd").parse("1996-12-15").getTime();
    AvGirl avGirl = new AvGirl("橋本ありな", 1.60, new java.sql.Date(time));
    avGirlList.add(avGirl);
    avGirlDao.save(avGirlList);
    return null;
}

删除

/**
 * 删除所有数据
 *
 * @return
 */
@GetMapping("/delAll")
public String deleteAll() {
    boolean delete = avGirlDao.deleteAll();
    return "删除数据 --- > " + delete;
}

• 删完了，没得小姐姐了