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

	@Override
	public String toString() {
		return name().toLowerCase(Locale.ROOT);
	}

	@Override
	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.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.demoflowable.common.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();
		}

	}
}