clickhouse通过java jdbc实现批量操作

批量操作"/>

clickhouse通过java jdbc实现批量操作

1.1、在maven中引入clickhouse jar包

            <dependency><groupId>com.clickhouse</groupId><artifactId>clickhouse-jdbc</artifactId><version>0.5.0</version></dependency><dependency><groupId>com.clickhouse</groupId><artifactId>clickhouse-client</artifactId><version>0.5.0</version></dependency><dependency><groupId>com.clickhouse</groupId><artifactId>clickhouse-http-client</artifactId><version>0.5.0</version></dependency><dependency><groupId>org.apache.httpcomponents.client5</groupId><artifactId>httpclient5</artifactId><version>5.2.1</version></dependency><dependency><groupId>org.apache.httpcomponents.client5</groupId><artifactId>httpclient5-fluent</artifactId><version>5.1.3</version></dependency>

1.2、注意，httpclient5-fluent和httpclient5必须引入，否则会报错

1.3、编写一个DriverPropertyCreator 驱动接口

import java.sql.DriverPropertyInfo;
import java.util.Properties;public interface DriverPropertyCreator {DriverPropertyInfo createDriverPropertyInfo(Properties properties);
}

1.4、编写一个ClickHouseQueryParam枚举

import java.sql.DriverPropertyInfo;
import java.util.Locale;
import java.util.Properties;public enum ClickHouseQueryParam implements DriverPropertyCreator {DATABASE("database", null, String.class, "database name used by default"),USER("user", null, String.class, "user name, by default - default"),PASSWORD("password", null, String.class, "user password, by default null");private final String key;private final Object defaultValue;private final Class<?> clazz;private final String description;<T> ClickHouseQueryParam(String key, T defaultValue, Class<T> clazz, String description) {this.key = key;this.defaultValue = defaultValue;this.clazz = clazz;this.description = description;}public String getKey() {return key;}public Object getDefaultValue() {return defaultValue;}public Class<?> getClazz() {return clazz;}public String getDescription() {return description;}@Overridepublic String toString() {return name().toLowerCase(Locale.ROOT);}@Overridepublic DriverPropertyInfo createDriverPropertyInfo(Properties properties) {DriverPropertyInfo propertyInfo = new DriverPropertyInfo(key, driverPropertyValue(properties));propertyInfo.required = false;propertyInfo.description = description;propertyInfo.choices = driverPropertyInfoChoices();return propertyInfo;}private String[] driverPropertyInfoChoices() {return clazz == Boolean.class || clazz == Boolean.TYPE ? new String[] { "true", "false" } : null;}private String driverPropertyValue(Properties properties) {String value = properties.getProperty(key);if (value == null) {value = defaultValue == null ? null : defaultValue.toString();}return value;}
}

1.5、编写一个ClickHouseConnectionSettings枚举

import java.sql.DriverPropertyInfo;
import java.util.Properties;public enum ClickHouseConnectionSettings implements DriverPropertyCreator {SOCKET_TIMEOUT("socket_timeout", 30000, "");private final String key;private final Object defaultValue;private final String description;private final Class<?> clazz;ClickHouseConnectionSettings(String key, Object defaultValue, String description) {this.key = key;this.defaultValue = defaultValue;this.clazz = defaultValue.getClass();this.description = description;}public String getKey() {return key;}public Object getDefaultValue() {return defaultValue;}public Class<?> getClazz() {return clazz;}public String getDescription() {return description;}public DriverPropertyInfo createDriverPropertyInfo(Properties properties) {DriverPropertyInfo propertyInfo = new DriverPropertyInfo(key, driverPropertyValue(properties));propertyInfo.required = false;propertyInfo.description = description;propertyInfo.choices = driverPropertyInfoChoices();return propertyInfo;}private String[] driverPropertyInfoChoices() {return clazz == Boolean.class || clazz == Boolean.TYPE ? new String[] { "true", "false" } : null;}private String driverPropertyValue(Properties properties) {String value = properties.getProperty(key);if (value == null) {value = defaultValue == null ? null : defaultValue.toString();}return value;}
}

1.6、编写一个ClickHouseProperties类

import java.util.Properties;public class ClickHouseProperties {private String user;private String password;private String database;private int socketTimeout;public ClickHouseProperties() {this(new Properties());}public ClickHouseProperties(Properties info) {this.socketTimeout = (Integer) getSetting(info, ClickHouseConnectionSettings.SOCKET_TIMEOUT);this.database = getSetting(info, ClickHouseQueryParam.DATABASE);this.user = getSetting(info, ClickHouseQueryParam.USER);this.password = getSetting(info, ClickHouseQueryParam.PASSWORD);}public Properties asProperties() {PropertiesBuilder ret = new PropertiesBuilder();ret.put(ClickHouseConnectionSettings.SOCKET_TIMEOUT.getKey(), String.valueOf(socketTimeout));ret.put(ClickHouseQueryParam.USER.getKey(), user);ret.put(ClickHouseQueryParam.PASSWORD.getKey(), password);ret.put(ClickHouseQueryParam.DATABASE.getKey(), database);return ret.getProperties();}public ClickHouseProperties(ClickHouseProperties properties) {setUser(properties.user);setPassword(properties.password);setDatabase(properties.database);setSocketTimeout(properties.socketTimeout);}private <T> T getSetting(Properties info, ClickHouseQueryParam param) {return getSetting(info, param.getKey(), param.getDefaultValue(), param.getClazz());}private <T> T getSetting(Properties info, ClickHouseConnectionSettings settings) {return getSetting(info, settings.getKey(), settings.getDefaultValue(), settings.getClazz());}@SuppressWarnings("unchecked")private <T> T getSetting(Properties info, String key, Object defaultValue, Class<?> clazz) {String val = info.getProperty(key);if (val == null) {return (T) defaultValue;}if (clazz == int.class || clazz == Integer.class) {return (T) clazz.cast(Integer.valueOf(val));}if (clazz == long.class || clazz == Long.class) {return (T) clazz.cast(Long.valueOf(val));}if (clazz == boolean.class || clazz == Boolean.class) {final Boolean boolValue;if ("1".equals(val) || "0".equals(val)) {boolValue = "1".equals(val);} else {boolValue = Boolean.valueOf(val);}return (T) clazz.cast(boolValue);}return (T) clazz.cast(val);}public int getSocketTimeout() {return socketTimeout;}public void setSocketTimeout(int socketTimeout) {this.socketTimeout = socketTimeout;}public String getUser() {return user;}public void setUser(String user) {this.user = user;}public String getDatabase() {return database;}public void setDatabase(String database) {this.database = database;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}private static class PropertiesBuilder {private final Properties properties;public PropertiesBuilder() {properties = new Properties();}public void put(String key, String value) {if (value != null) {properties.put(key, value);}}public Properties getProperties() {return properties;}}
}

1.7、开始编写jdbc操作批量操作clickhouse的ClickhouseBatchDemo

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;import org.demoflowablemon.ClickHouseProperties;import com.clickhouse.jdbc.ClickHouseConnection;
import com.clickhouse.jdbc.ClickHouseDataSource;public class ClickhouseBatchDemo {private static String username = "default";private static String password = "123456";private static String address = "jdbc:clickhouse://192.168.42.142:8123";private static String db = "bigdata";private static int socketTimeout = 600000;public static void main(String[] args) throws Exception {getConnection();createTable("create table t_demo_04(id UInt32,sku String,amount Decimal(16,2),create_time Datetime) engine =MergeTree partition by toYYYYMMDD(create_time) primary key (id) order by (id,sku);");batchInsertDemo();}/*** 创建表* * @throws Exception*/public static void createTable(String tableSql) throws Exception {Connection connection = null;try {connection = getConnection();Statement statement = connection.createStatement();boolean execute = statement.execute(tableSql);if (execute) {System.out.println("创建表成功," + execute);}} finally {close(connection);}}public static void batchInsertDemo() throws Exception {Connection connection = null;try {connection = getConnection();PreparedStatement pstmt = connection.prepareStatement("insert into t_demo_03 (id,sku,amount,create_time) values(?,?,?,?)");for (int i = 0; i < 10000; i++) {String id = "10" + i;String sku = "sku20231108" + i;pstmt.setString(1, id);pstmt.setString(2, sku);pstmt.setString(3, "50000.00");pstmt.setString(4, "2023-11-08 12:00:00");pstmt.addBatch();if (i % 500 == 0) {pstmt.executeBatch();pstmt.clearBatch();}pstmt.executeBatch();}System.out.println("clickhouse批量插入成功");} finally {close(connection);}}public static Connection getConnection() {ClickHouseProperties properties = new ClickHouseProperties();properties.setUser(username);properties.setPassword(password);properties.setDatabase(db);properties.setSocketTimeout(socketTimeout);try {ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(address, properties.asProperties());ClickHouseConnection conn = clickHouseDataSource.getConnection();System.out.println("Clickhouse连接成功");return conn;} catch (SQLException e) {e.printStackTrace();}return null;}public static void close(Connection connection) {try {if (connection != null) {connection.close();}} catch (SQLException e) {e.printStackTrace();}}
}