devbox
Guff_9hys
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

clickhouse通过java jdbc实现增删改查，保姆级教程_clickhouse java

作者：Guff_9hys | 2024-07-03 22:08:57

赞

踩

clickhouse java

一、clickhouse是一款开源的用于在线分析处理查询(OLAP :Online Analytical Processing)MPP架构的列式存储数据库。

二、clickhouse可以做用户行为分析，流批一体

三、我们现在用java通过jdbc的方式来操作clickhouse

四、先安装clickhouse，安装资料自行查找

五、操作步骤如下

5.1、在maven中引入clickhouse jar包

  1.             <dependency>
  2. 				<groupId>com.clickhouse</groupId>
  3. 				<artifactId>clickhouse-jdbc</artifactId>
  4. 				<version>0.5.0</version>
  5. 			</dependency>
  6. 			<dependency>
  7. 				<groupId>com.clickhouse</groupId>
  8. 				<artifactId>clickhouse-client</artifactId>
  9. 				<version>0.5.0</version>
  10. 			</dependency>
  11. 			<dependency>
  12. 				<groupId>com.clickhouse</groupId>
  13. 				<artifactId>clickhouse-http-client</artifactId>
  14. 				<version>0.5.0</version>
  15. 			</dependency>
  16.  
  17. 			<dependency>
  18. 				<groupId>org.apache.httpcomponents.client5</groupId>
  19. 				<artifactId>httpclient5</artifactId>
  20. 				<version>5.2.1</version>
  21. 			</dependency>
  22. 			<dependency>
  23. 				<groupId>org.apache.httpcomponents.client5</groupId>
  24. 				<artifactId>httpclient5-fluent</artifactId>
  25. 				<version>5.1.3</version>
  26. 			</dependency>

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

5.3、编写一个DriverPropertyCreator 驱动接口

  1. import java.sql.DriverPropertyInfo;
  2. import java.util.Properties;
  3.  
  4. public interface DriverPropertyCreator {
  5.  
  6. 	DriverPropertyInfo createDriverPropertyInfo(Properties properties);
  7. }

5.4、编写一个ClickHouseQueryParam枚举

  1.  
  2. import java.sql.DriverPropertyInfo;
  3. import java.util.Locale;
  4. import java.util.Properties;
  5.  
  6. public enum ClickHouseQueryParam implements DriverPropertyCreator {
  7.  
  8. 	DATABASE("database", null, String.class, "database name used by default"),
  9. 	USER("user", null, String.class, "user name, by default - default"),
  10. 	PASSWORD("password", null, String.class, "user password, by default null");
  11.  
  12. 	private final String key;
  13. 	private final Object defaultValue;
  14. 	private final Class<?> clazz;
  15. 	private final String description;
  16.  
  17. 	<T> ClickHouseQueryParam(String key, T defaultValue, Class<T> clazz, String description) {
  18. 		this.key = key;
  19. 		this.defaultValue = defaultValue;
  20. 		this.clazz = clazz;
  21. 		this.description = description;
  22. 	}
  23.  
  24. 	public String getKey() {
  25. 		return key;
  26. 	}
  27.  
  28. 	public Object getDefaultValue() {
  29. 		return defaultValue;
  30. 	}
  31.  
  32. 	public Class<?> getClazz() {
  33. 		return clazz;
  34. 	}
  35.  
  36. 	public String getDescription() {
  37. 		return description;
  38. 	}
  39.  
  40. 	@Override
  41. 	public String toString() {
  42. 		return name().toLowerCase(Locale.ROOT);
  43. 	}
  44.  
  45. 	@Override
  46. 	public DriverPropertyInfo createDriverPropertyInfo(Properties properties) {
  47. 		DriverPropertyInfo propertyInfo = new DriverPropertyInfo(key, driverPropertyValue(properties));
  48. 		propertyInfo.required = false;
  49. 		propertyInfo.description = description;
  50. 		propertyInfo.choices = driverPropertyInfoChoices();
  51. 		return propertyInfo;
  52. 	}
  53.  
  54. 	private String[] driverPropertyInfoChoices() {
  55. 		return clazz == Boolean.class || clazz == Boolean.TYPE ? new String[] { "true", "false" } : null;
  56. 	}
  57.  
  58. 	private String driverPropertyValue(Properties properties) {
  59. 		String value = properties.getProperty(key);
  60. 		if (value == null) {
  61. 			value = defaultValue == null ? null : defaultValue.toString();
  62. 		}
  63. 		return value;
  64. 	}
  65. }

5.5、编写一个ClickHouseConnectionSettings枚举

  1.  
  2. import java.sql.DriverPropertyInfo;
  3. import java.util.Properties;
  4.  
  5. public enum ClickHouseConnectionSettings implements DriverPropertyCreator {
  6.  
  7. 	SOCKET_TIMEOUT("socket_timeout", 30000, "");
  8.  
  9. 	private final String key;
  10. 	private final Object defaultValue;
  11. 	private final String description;
  12. 	private final Class<?> clazz;
  13.  
  14. 	ClickHouseConnectionSettings(String key, Object defaultValue, String description) {
  15. 		this.key = key;
  16. 		this.defaultValue = defaultValue;
  17. 		this.clazz = defaultValue.getClass();
  18. 		this.description = description;
  19. 	}
  20.  
  21. 	public String getKey() {
  22. 		return key;
  23. 	}
  24.  
  25. 	public Object getDefaultValue() {
  26. 		return defaultValue;
  27. 	}
  28.  
  29. 	public Class<?> getClazz() {
  30. 		return clazz;
  31. 	}
  32.  
  33. 	public String getDescription() {
  34. 		return description;
  35. 	}
  36.  
  37. 	public DriverPropertyInfo createDriverPropertyInfo(Properties properties) {
  38. 		DriverPropertyInfo propertyInfo = new DriverPropertyInfo(key, driverPropertyValue(properties));
  39. 		propertyInfo.required = false;
  40. 		propertyInfo.description = description;
  41. 		propertyInfo.choices = driverPropertyInfoChoices();
  42. 		return propertyInfo;
  43. 	}
  44.  
  45. 	private String[] driverPropertyInfoChoices() {
  46. 		return clazz == Boolean.class || clazz == Boolean.TYPE ? new String[] { "true", "false" } : null;
  47. 	}
  48.  
  49. 	private String driverPropertyValue(Properties properties) {
  50. 		String value = properties.getProperty(key);
  51. 		if (value == null) {
  52. 			value = defaultValue == null ? null : defaultValue.toString();
  53. 		}
  54. 		return value;
  55. 	}
  56. }

5.6、编写一个ClickHouseProperties类

  1.  
  2. import java.util.Properties;
  3.  
  4. public class ClickHouseProperties {
  5.  
  6. 	private String user;
  7. 	private String password;
  8. 	private String database;
  9. 	private int socketTimeout;
  10.  
  11. 	public ClickHouseProperties() {
  12. 		this(new Properties());
  13. 	}
  14.  
  15. 	public ClickHouseProperties(Properties info) {
  16. 		this.socketTimeout = (Integer) getSetting(info, ClickHouseConnectionSettings.SOCKET_TIMEOUT);
  17.  
  18. 		this.database = getSetting(info, ClickHouseQueryParam.DATABASE);
  19.  
  20. 		this.user = getSetting(info, ClickHouseQueryParam.USER);
  21. 		this.password = getSetting(info, ClickHouseQueryParam.PASSWORD);
  22. 	}
  23.  
  24. 	public Properties asProperties() {
  25. 		PropertiesBuilder ret = new PropertiesBuilder();
  26. 		ret.put(ClickHouseConnectionSettings.SOCKET_TIMEOUT.getKey(), String.valueOf(socketTimeout));
  27. 		ret.put(ClickHouseQueryParam.USER.getKey(), user);
  28. 		ret.put(ClickHouseQueryParam.PASSWORD.getKey(), password);
  29. 		ret.put(ClickHouseQueryParam.DATABASE.getKey(), database);
  30. 		return ret.getProperties();
  31. 	}
  32.  
  33. 	public ClickHouseProperties(ClickHouseProperties properties) {
  34. 		setUser(properties.user);
  35. 		setPassword(properties.password);
  36. 		setDatabase(properties.database);
  37. 		setSocketTimeout(properties.socketTimeout);
  38. 	}
  39.  
  40. 	private <T> T getSetting(Properties info, ClickHouseQueryParam param) {
  41. 		return getSetting(info, param.getKey(), param.getDefaultValue(), param.getClazz());
  42. 	}
  43.  
  44. 	private <T> T getSetting(Properties info, ClickHouseConnectionSettings settings) {
  45. 		return getSetting(info, settings.getKey(), settings.getDefaultValue(), settings.getClazz());
  46. 	}
  47.  
  48. 	@SuppressWarnings("unchecked")
  49. 	private <T> T getSetting(Properties info, String key, Object defaultValue, Class<?> clazz) {
  50. 		String val = info.getProperty(key);
  51. 		if (val == null) {
  52. 			return (T) defaultValue;
  53. 		}
  54. 		if (clazz == int.class || clazz == Integer.class) {
  55. 			return (T) clazz.cast(Integer.valueOf(val));
  56. 		}
  57. 		if (clazz == long.class || clazz == Long.class) {
  58. 			return (T) clazz.cast(Long.valueOf(val));
  59. 		}
  60. 		if (clazz == boolean.class || clazz == Boolean.class) {
  61. 			final Boolean boolValue;
  62. 			if ("1".equals(val) || "0".equals(val)) {
  63. 				boolValue = "1".equals(val);
  64. 			} else {
  65. 				boolValue = Boolean.valueOf(val);
  66. 			}
  67. 			return (T) clazz.cast(boolValue);
  68. 		}
  69. 		return (T) clazz.cast(val);
  70. 	}
  71.  
  72. 	public int getSocketTimeout() {
  73. 		return socketTimeout;
  74. 	}
  75.  
  76. 	public void setSocketTimeout(int socketTimeout) {
  77. 		this.socketTimeout = socketTimeout;
  78. 	}
  79.  
  80. 	public String getUser() {
  81. 		return user;
  82. 	}
  83.  
  84. 	public void setUser(String user) {
  85. 		this.user = user;
  86. 	}
  87.  
  88. 	public String getDatabase() {
  89. 		return database;
  90. 	}
  91.  
  92. 	public void setDatabase(String database) {
  93. 		this.database = database;
  94. 	}
  95.  
  96. 	public String getPassword() {
  97. 		return password;
  98. 	}
  99.  
  100. 	public void setPassword(String password) {
  101. 		this.password = password;
  102. 	}
  103.  
  104. 	private static class PropertiesBuilder {
  105. 		private final Properties properties;
  106.  
  107. 		public PropertiesBuilder() {
  108. 			properties = new Properties();
  109. 		}
  110.  
  111. 		public void put(String key, String value) {
  112. 			if (value != null) {
  113. 				properties.put(key, value);
  114. 			}
  115. 		}
  116.  
  117. 		public Properties getProperties() {
  118. 			return properties;
  119. 		}
  120. 	}
  121. }

5.7、开始编写jdbc操作clickhouse的ClickhouseDemo

  1.  
  2. import java.sql.Connection;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.ResultSetMetaData;
  6. import java.sql.SQLException;
  7. import java.sql.Statement;
  8. import java.util.ArrayList;
  9. import java.util.HashMap;
  10. import java.util.List;
  11. import java.util.Map;
  12.  
  13. import org.demoflowable.common.ClickHouseProperties;
  14.  
  15. import com.clickhouse.jdbc.ClickHouseConnection;
  16. import com.clickhouse.jdbc.ClickHouseDataSource;
  17.  
  18.  
  19. public class ClickhouseDemo {
  20.  
  21. 	private static String username = "default";
  22. 	private static String password = "123456";
  23. 	private static String address = "jdbc:clickhouse://192.168.42.142:8123";
  24. 	private static String db = "bigdata";
  25. 	private static int socketTimeout = 600000;
  26.  
  27. 	public static void main(String[] args) throws Exception {
  28. 		getConnection();
  29. 		dropTable();
  30. 		createTable(
  31. 				"create table t_demo_02(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);");
  32. 		insertDemo();
  33. 		queryDemo();
  34. 		updateDemoById();
  35. 		deleteDemoById();
  36. 	}
  37.  
  38. 	/**
  39. 	 * 查询数据
  40. 	 */
  41. 	public static void queryDemo() {
  42. 		List<Map<String, Object>> list = new ArrayList<>();
  43. 		String sql = "select * from t_demo";
  44. 		Connection connection = getConnection();
  45. 		try {
  46. 			Statement statement = connection.createStatement();
  47. 			ResultSet rs = statement.executeQuery(sql);
  48. 			ResultSetMetaData rsmd = rs.getMetaData();
  49. 			while (rs.next()) {
  50. 				Map<String, Object> row = new HashMap<>();
  51. 				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
  52. 					row.put(rsmd.getColumnName(i), rs.getObject(rsmd.getColumnName(i)));
  53. 				}
  54. 				list.add(row);
  55. 			}
  56. 		} catch (SQLException e) {
  57. 			e.printStackTrace();
  58. 		}
  59. 		list.stream().forEach(item -> {
  60. 			Map<String, Object> rowData = item;
  61. 			System.out.println(rowData);
  62. 		});
  63. 		close(connection);
  64. 	}
  65.  
  66. 	/**
  67. 	 * 创建表
  68. 	 * 
  69. 	 * @throws Exception
  70. 	 */
  71. 	public static void createTable(String tableSql) throws Exception {
  72. 		Connection connection = null;
  73. 		try {
  74. 			connection = getConnection();
  75. 			Statement statement = connection.createStatement();
  76. 			boolean execute = statement.execute(tableSql);
  77. 			if (execute) {
  78. 				System.out.println("创建表成功," + execute);
  79. 			}
  80. 		} finally {
  81. 			close(connection);
  82. 		}
  83. 	}
  84.  
  85. 	/**
  86. 	 * 删除表
  87. 	 * 
  88. 	 * @throws Exception
  89. 	 */
  90. 	public static void dropTable() throws Exception {
  91. 		Connection connection = null;
  92. 		try {
  93. 			connection = getConnection();
  94. 			Statement statement = connection.createStatement();
  95. 			statement.execute("drop table t_demo_02;");
  96. 			System.out.println("删除表成功");
  97. 		} finally {
  98. 			close(connection);
  99. 		}
  100. 	}
  101.  
  102. 	/**
  103. 	 * 
  104. 	 * @throws Exception
  105. 	 */
  106. 	public static void insertDemo() throws Exception {
  107. 		Connection connection = null;
  108. 		try {
  109. 			connection = getConnection();
  110. 			for (int i = 0; i < 5; i++) {
  111. 				String id = "10" + i;
  112. 				String sku = "sku20231108" + i;
  113. 				PreparedStatement pstmt = connection.prepareStatement(
  114. 						"insert into t_demo values(" + id + ", '" + sku + "', '6500.00','2023-11-08 12:00:00')");
  115. 				pstmt.execute();
  116. 			}
  117. 			System.out.println("insert success");
  118. 		} finally {
  119. 			close(connection);
  120. 		}
  121. 	}
  122.  
  123. 	/**
  124. 	 */
  125. 	public static void deleteDemoById() throws Exception {
  126. 		Connection connection = null;
  127. 		try {
  128. 			connection = getConnection();
  129. 			PreparedStatement pstmt = connection.prepareStatement("delete from t_demo where sku = 'sku2023110801';");
  130. 			pstmt.execute();
  131. 			System.out.println("delete success");
  132. 		} finally {
  133. 			close(connection);
  134. 		}
  135. 	}
  136.  
  137. 	/**
  138. 	 */
  139. 	public static void updateDemoById() throws Exception {
  140. 		Connection connection = null;
  141. 		try {
  142. 			connection = getConnection();
  143. 			PreparedStatement pstmt = connection
  144. 					.prepareStatement("alter table t_demo update amount=toDecimal32(1200.00,3) where id = '101'");
  145. 			pstmt.execute();
  146. 			System.out.println("update success");
  147. 		} finally {
  148. 			close(connection);
  149. 		}
  150. 	}
  151.  
  152. 	public static Connection getConnection() {
  153. 		ClickHouseProperties properties = new ClickHouseProperties();
  154. 		properties.setUser(username);
  155. 		properties.setPassword(password);
  156. 		properties.setDatabase(db);
  157. 		properties.setSocketTimeout(socketTimeout);
  158. 		try {
  159. 			ClickHouseDataSource clickHouseDataSource = new ClickHouseDataSource(address, properties.asProperties());
  160. 			ClickHouseConnection conn = clickHouseDataSource.getConnection();
  161. 			System.out.println("连接成功");
  162. 			return conn;
  163. 		} catch (SQLException e) {
  164. 			e.printStackTrace();
  165. 		}
  166. 		return null;
  167. 	}
  168.  
  169. 	public static void close(Connection connection) {
  170. 		try {
  171. 			if (connection != null) {
  172. 				connection.close();
  173. 			}
  174. 		} catch (SQLException e) {
  175. 			e.printStackTrace();
  176. 		}
  177.  
  178. 	}
  179. }

整个过程就是这样，完全保姆级教程。操作后的效果，如下

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Guff_9hys/article/detail/784648
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号