storm-jdbc解析
来源：互联网 发布：iphone笔记软件 编辑：程序博客网 时间：2024/06/11 16:05
strom为了方便程序员的开发，提供了对jdbc的封装，对外总共有三个类：
1：AbstractJdbcBolt
是对jdbc的抽象类，下面两个类是对这个抽象类的具体实现
2：JdbcInsertBolt
是对jdbc插入操作的封装，主要是对数据库进行插入操作，没有返回值，是bolt的终点
3：JdbcLookupBolt
是对jdbc查找操作的封装，主要是对数据库进行查找操作，以查找结果作为返回值，查找结果单条数据作为单独一个Tuple，发送到下一个bolt节点。
附上AbstractJdbcBolt代码
package org.apache.storm.jdbc.bolt;import backtype.storm.Config;import backtype.storm.task.OutputCollector;import backtype.storm.task.TopologyContext;import backtype.storm.topology.base.BaseRichBolt;import org.apache.storm.jdbc.common.ConnectionProvider;import org.apache.storm.jdbc.common.JdbcClient;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import java.util.Map;public abstract class AbstractJdbcBolt extends BaseRichBolt { private static final Logger LOG = LoggerFactory.getLogger(AbstractJdbcBolt.class); /** *全局对象，storm的输出流 **/ protected OutputCollector collector; /** *jdbc客户端 **/ protected transient JdbcClient jdbcClient; protected String configKey; /* *数据查找超时时间 **/ protected Integer queryTimeoutSecs; /* *数据库连接池，需要实现ConnectionProvider接口，在ConnectionProvider实现对数据库连接池的初始化操作 **/ protected ConnectionProvider connectionProvider; @Override public void prepare(Map map, TopologyContext topologyContext, OutputCollector collector) { this.collector = collector; /* *对数据库连接池实现初始化 **/ connectionProvider.prepare(); /* *设置数据库的超时时间 **/ if(queryTimeoutSecs == null) { queryTimeoutSecs = Integer.parseInt(map.get(Config.TOPOLOGY_MESSAGE_TIMEOUT_SECS).toString()); } /* *获得数据库连接 **/ this.jdbcClient = new JdbcClient(connectionProvider, queryTimeoutSecs); } public AbstractJdbcBolt(ConnectionProvider connectionProvider) { this.connectionProvider = connectionProvider; } /* *关闭数据库连接池 **/ @Override public void cleanup() { connectionProvider.cleanup(); }}ConnectionProvider数据库连接池接口，如果想替换为其他数据库连接池，可以通过重写此方法即可
package org.apache.storm.jdbc.common;import java.io.Serializable;import java.sql.Connection;import java.util.Map;/** * Provides a database connection. */public interface ConnectionProvider extends Serializable { /** * 对数据库连接池进行初始化 * method must be idempotent. */ void prepare(); /** *获得数据库连接 * @return a DB connection over which the queries can be executed. */ Connection getConnection(); /** * 关闭数据库连接池 * called once when the system is shutting down, should be idempotent. */ void cleanup();}如果对strom不熟悉完全可以直接使用strom-jdbc完成对数据库的操作。
Column是对jdbc列的封装，具体格式为：列名，值，值类型
package org.apache.storm.jdbc.common;import java.io.Serializable;/** * A database table can be defined as a list of rows and each row can be defined as a list of columns where * each column instance has a name, a value and a type. This class represents an instance of a column in a database * row. For example if we have the following table named user: * <pre> * ____________________________ * | UserId | UserName | * | 1 | Foo | * | 2 | Bar | * ---------------------------- * </pre> * * The following class can be used to represent the data in the table as * <pre> * List<List<Column>> rows = new ArrayList<List<Column>>(); * List<Column> row1 = Lists.newArrayList(new Column("UserId", 1, Types.INTEGER), new Column("UserName", "Foo", Types.VARCHAR)) * List<Column> row1 = Lists.newArrayList(new Column("UserId", 2, Types.INTEGER), new Column("UserName", "Bar", Types.VARCHAR)) * * rows.add(row1) * rows.add(row2) * * </pre> * * @param <T> */public class Column<T> implements Serializable { /** *列名 */ private String columnName; /** *值 */ private T val; /** * 值的类型 * The sql type(e.g. varchar, date, int) Ideally we would have an enum but java's jdbc API uses integer. * See {@link java.sql.Types} */ private int sqlType; public Column(String columnName, T val, int sqlType) { this.columnName = columnName; this.val = val; this.sqlType = sqlType; } public Column(String columnName, int sqlType) { this.columnName = columnName; this.sqlType = sqlType; } public String getColumnName() { return columnName; } public T getVal() { return val; } public int getSqlType() { return sqlType; } @Override public boolean equals(Object o) { if (this == o) return true; if (!(o instanceof Column)) return false; Column<?> column = (Column<?>) o; if (sqlType != column.sqlType) return false; if (!columnName.equals(column.columnName)) return false; return val != null ? val.equals(column.val) : column.val == null; } @Override public int hashCode() { int result = columnName.hashCode(); result = 31 * result + (val != null ? val.hashCode() : 0); result = 31 * result + sqlType; return result; } @Override public String toString() { return "Column{" + "columnName='" + columnName + '\'' + ", val=" + val + ", sqlType=" + sqlType + '}'; }}JdbcClient封装的是对数据库的操作，包括对数据库的增加修改删除查找操作
package org.apache.storm.jdbc.common;import com.google.common.base.Function;import com.google.common.base.Joiner;import com.google.common.collect.Collections2;import com.google.common.collect.Lists;import org.apache.commons.lang.StringUtils;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import java.sql.*;import java.sql.Date;import java.util.*;public class JdbcClient { private static final Logger LOG = LoggerFactory.getLogger(JdbcClient.class); private ConnectionProvider connectionProvider; private int queryTimeoutSecs; /** *初始化操作，需要一个数据库连接池 */ public JdbcClient(ConnectionProvider connectionProvider, int queryTimeoutSecs) { this.connectionProvider = connectionProvider; this.queryTimeoutSecs = queryTimeoutSecs; } /** *插入操作 */ public void insert(String tableName, List<List<Column>> columnLists) { String query = constructInsertQuery(tableName, columnLists); executeInsertQuery(query, columnLists); } /** *批量插入操作，通过多次插入，一个事务来完成对数据的批量操作 */ public void executeInsertQuery(String query, List<List<Column>> columnLists) { Connection connection = null; try { connection = connectionProvider.getConnection(); boolean autoCommit = connection.getAutoCommit(); if(autoCommit) { connection.setAutoCommit(false); } LOG.debug("Executing query {}", query); PreparedStatement preparedStatement = connection.prepareStatement(query); if(queryTimeoutSecs > 0) { preparedStatement.setQueryTimeout(queryTimeoutSecs); } for(List<Column> columnList : columnLists) { setPreparedStatementParams(preparedStatement, columnList); preparedStatement.addBatch(); } int[] results = preparedStatement.executeBatch(); if(Arrays.asList(results).contains(Statement.EXECUTE_FAILED)) { connection.rollback(); throw new RuntimeException("failed at least one sql statement in the batch, operation rolled back."); } else { try { connection.commit(); } catch (SQLException e) { throw new RuntimeException("Failed to commit insert query " + query, e); } } } catch (SQLException e) { throw new RuntimeException("Failed to execute insert query " + query, e); } finally { closeConnection(connection); } } /** *批量操作，通过一句sql执行完成 */ private String constructInsertQuery(String tableName, List<List<Column>> columnLists) { StringBuilder sb = new StringBuilder(); sb.append("Insert into ").append(tableName).append(" ("); Collection<String> columnNames = Collections2.transform(columnLists.get(0), new Function<Column, String>() { @Override public String apply(Column input) { return input.getColumnName(); } }); String columns = Joiner.on(",").join(columnNames); sb.append(columns).append(") values ( "); String placeHolders = StringUtils.chop(StringUtils.repeat("?,", columnNames.size())); sb.append(placeHolders).append(")"); return sb.toString(); } /** *查找操作 */ public List<List<Column>> select(String sqlQuery, List<Column> queryParams) { Connection connection = null; try { connection = connectionProvider.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(sqlQuery); if(queryTimeoutSecs > 0) { preparedStatement.setQueryTimeout(queryTimeoutSecs); } setPreparedStatementParams(preparedStatement, queryParams); ResultSet resultSet = preparedStatement.executeQuery(); List<List<Column>> rows = Lists.newArrayList(); while(resultSet.next()){ ResultSetMetaData metaData = resultSet.getMetaData(); int columnCount = metaData.getColumnCount(); List<Column> row = Lists.newArrayList(); for(int i=1 ; i <= columnCount; i++) { String columnLabel = metaData.getColumnLabel(i); int columnType = metaData.getColumnType(i); Class columnJavaType = Util.getJavaType(columnType); if (columnJavaType.equals(String.class)) { row.add(new Column<String>(columnLabel, resultSet.getString(columnLabel), columnType)); } else if (columnJavaType.equals(Integer.class)) { row.add(new Column<Integer>(columnLabel, resultSet.getInt(columnLabel), columnType)); } else if (columnJavaType.equals(Double.class)) { row.add(new Column<Double>(columnLabel, resultSet.getDouble(columnLabel), columnType)); } else if (columnJavaType.equals(Float.class)) { row.add(new Column<Float>(columnLabel, resultSet.getFloat(columnLabel), columnType)); } else if (columnJavaType.equals(Short.class)) { row.add(new Column<Short>(columnLabel, resultSet.getShort(columnLabel), columnType)); } else if (columnJavaType.equals(Boolean.class)) { row.add(new Column<Boolean>(columnLabel, resultSet.getBoolean(columnLabel), columnType)); } else if (columnJavaType.equals(byte[].class)) { row.add(new Column<byte[]>(columnLabel, resultSet.getBytes(columnLabel), columnType)); } else if (columnJavaType.equals(Long.class)) { row.add(new Column<Long>(columnLabel, resultSet.getLong(columnLabel), columnType)); } else if (columnJavaType.equals(Date.class)) { row.add(new Column<Date>(columnLabel, resultSet.getDate(columnLabel), columnType)); } else if (columnJavaType.equals(Time.class)) { row.add(new Column<Time>(columnLabel, resultSet.getTime(columnLabel), columnType)); } else if (columnJavaType.equals(Timestamp.class)) { row.add(new Column<Timestamp>(columnLabel, resultSet.getTimestamp(columnLabel), columnType)); } else { throw new RuntimeException("type = " + columnType + " for column " + columnLabel + " not supported."); } } rows.add(row); } return rows; } catch (SQLException e) { throw new RuntimeException("Failed to execute select query " + sqlQuery, e); } finally { closeConnection(connection); } } /** *获的表的全部列 */ public List<Column> getColumnSchema(String tableName) { Connection connection = null; List<Column> columns = new ArrayList<Column>(); try { connection = connectionProvider.getConnection(); DatabaseMetaData metaData = connection.getMetaData(); ResultSet resultSet = metaData.getColumns(null, null, tableName, null); while (resultSet.next()) { columns.add(new Column(resultSet.getString("COLUMN_NAME"), resultSet.getInt("DATA_TYPE"))); } return columns; } catch (SQLException e) { throw new RuntimeException("Failed to get schema for table " + tableName, e); } finally { closeConnection(connection); } } /** *执行sql语句 */ public void executeSql(String sql) { Connection connection = null; try { connection = connectionProvider.getConnection(); Statement statement = connection.createStatement(); statement.execute(sql); } catch (SQLException e) { throw new RuntimeException("Failed to execute SQL", e); } finally { closeConnection(connection); } } /** *解析列 */ private void setPreparedStatementParams(PreparedStatement preparedStatement, List<Column> columnList) throws SQLException { int index = 1; for (Column column : columnList) { Class columnJavaType = Util.getJavaType(column.getSqlType()); if (column.getVal() == null) { preparedStatement.setNull(index, column.getSqlType()); } else if (columnJavaType.equals(String.class)) { preparedStatement.setString(index, (String) column.getVal()); } else if (columnJavaType.equals(Integer.class)) { preparedStatement.setInt(index, (Integer) column.getVal()); } else if (columnJavaType.equals(Double.class)) { preparedStatement.setDouble(index, (Double) column.getVal()); } else if (columnJavaType.equals(Float.class)) { preparedStatement.setFloat(index, (Float) column.getVal()); } else if (columnJavaType.equals(Short.class)) { preparedStatement.setShort(index, (Short) column.getVal()); } else if (columnJavaType.equals(Boolean.class)) { preparedStatement.setBoolean(index, (Boolean) column.getVal()); } else if (columnJavaType.equals(byte[].class)) { preparedStatement.setBytes(index, (byte[]) column.getVal()); } else if (columnJavaType.equals(Long.class)) { preparedStatement.setLong(index, (Long) column.getVal()); } else if (columnJavaType.equals(Date.class)) { preparedStatement.setDate(index, (Date) column.getVal()); } else if (columnJavaType.equals(Time.class)) { preparedStatement.setTime(index, (Time) column.getVal()); } else if (columnJavaType.equals(Timestamp.class)) { preparedStatement.setTimestamp(index, (Timestamp) column.getVal()); } else { throw new RuntimeException("Unknown type of value " + column.getVal() + " for column " + column.getColumnName()); } ++index; } } /** *关闭数据库连接 */ private void closeConnection(Connection connection) { if (connection != null) { try { connection.close(); } catch (SQLException e) { throw new RuntimeException("Failed to close connection", e); } } }}strom-jdbc也提供了一个具体的实现列子
package org.apache.storm.jdbc.common;import com.zaxxer.hikari.HikariConfig;import com.zaxxer.hikari.HikariDataSource;import java.sql.Connection;import java.sql.SQLException;import java.util.Map;import java.util.Properties;public class HikariCPConnectionProvider implements ConnectionProvider { private Map<String, Object> configMap; private transient HikariDataSource dataSource; /** *初始化传输数据库的相关配置 */ public HikariCPConnectionProvider(Map<String, Object> hikariCPConfigMap) { this.configMap = hikariCPConfigMap; } /** *初始化数据库连接池 */ @Override public synchronized void prepare() { if(dataSource == null) { Properties properties = new Properties(); properties.putAll(configMap); HikariConfig config = new HikariConfig(properties); this.dataSource = new HikariDataSource(config); this.dataSource.setAutoCommit(false); } } /** *获得数据库连接 */ @Override public Connection getConnection() { try { return this.dataSource.getConnection(); } catch (SQLException e) { throw new RuntimeException(e); } } /** *关闭数据库连接池 */ @Override public void cleanup() { if(dataSource != null) { dataSource.shutdown(); } }}可以看到storm-jdbc对数据库的操作已经进行了，封装，完全可以完成程序员对普通程序的开发要求，如果要求开发效率，可以直接拿来使用，如果对执行效率有要求，则可以根据自己要求，自己实现对数据库的封装。
0 0
- storm-jdbc解析
- Storm与JDBC整合
- storm+kafka+jdbc整合实例
- storm+kafka+jdbc整合实例
- Storm和JDBC native集成
- Storm 配置图文解析
- Storm-Kafka源代码解析
- 解析storm的KafkaSpout
- 解析jdbc
- JDBC解析
- Storm-JDBC 中Date类型转换bug
- Storm JDBC插入数据到数据库
- 解析JDBC(1)
- 解析JDBC（2）
- 全面解析JDBC(一)
- 全面解析JDBC
- 全面解析JDBC
- 【转】JDBC解析
- 解决虚拟机打不开Ubuntu的问题：
- Android 控件按下，点击自定义效果
- 机器学习笔记：ID3算法建立决策树(二)
- Java并发编程：线程池的使用
- 极信头条--微信小程序
- storm-jdbc解析
- javascript最佳实践（持续中....）
- BM CC150 Ch5 BM53
- 树莓派MJPG-Streamer实现网络监控
- JAVA在Windows7上的环境变量设置
- float失真问题
- [LeetCode] 205. Isomorphic Strings
- 怎样防止App在后台运行，点击应用桌面图标重新启动？
- 微信小程序开发 --安装篇
