storm-jdbc解析

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&lt;List&lt;Column&gt;&gt; rows = new ArrayList&lt;List&lt;Column&gt;&gt;();
 * List&lt;Column&gt; row1 = Lists.newArrayList(new Column("UserId", 1, Types.INTEGER), new Column("UserName", "Foo", Types.VARCHAR))
 * List&lt;Column&gt; 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对数据库的操作已经进行了，封装，完全可以完成程序员对普通程序的开发要求，如果要求开发效率，可以直接拿来使用，如果对执行效率有要求，则可以根据自己要求，自己实现对数据库的封装。