本实例中使用的是mysql数据库
1、maven中加入storm-jdbc和mysql的connector
<dependency>
<groupId>org.apache.storm</groupId>
<artifactId>storm-jdbc</artifactId>
<version>0.10.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.31</version>
</dependency>
2.表结构
CREATE TABLE `userinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(50) DEFAULT NULL,
`resource_id` varchar(10) DEFAULT NULL,
`create_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`count` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3.实例代码
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.storm.guava.collect.Lists;
import org.apache.storm.jdbc.bolt.JdbcInsertBolt;
import org.apache.storm.jdbc.bolt.JdbcLookupBolt;
import org.apache.storm.jdbc.common.Column;
import org.apache.storm.jdbc.common.ConnectionProvider;
import org.apache.storm.jdbc.common.HikariCPConnectionProvider;
import org.apache.storm.jdbc.mapper.JdbcMapper;
import org.apache.storm.jdbc.mapper.SimpleJdbcLookupMapper;
import org.apache.storm.jdbc.mapper.SimpleJdbcMapper;
import backtype.storm.tuple.Fields;
public class PersistentBolt {
private static Map<String,Object> hikariConfigMap = new HashMap<String, Object>(){{
put("dataSourceClassName", "com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
put("dataSource.url", "jdbc:mysql://localhost/storm");
put("dataSource.user","user");
put("dataSource.password", "password");
}};
public static ConnectionProvider connectionProvider = new HikariCPConnectionProvider(hikariConfigMap);
public static JdbcInsertBolt getJdbcInsertBolt(){
//使用tablename进行插入数据,需要指定表中的所有字段
/*String tableName="userinfo";
JdbcMapper simpleJdbcMapper = new SimpleJdbcMapper(tableName, connectionProvider);
JdbcInsertBolt jdbcInsertBolt = new JdbcInsertBolt(connectionProvider, simpleJdbcMapper)
.withTableName("userinfo")
.withQueryTimeoutSecs(50);*/
//使用schemaColumns,可以指定字段要插入的字段
List<Column> schemaColumns = Lists.newArrayList(new Column("user_id", Types.VARCHAR),
new Column("resource_id", Types.VARCHAR), new Column("count", Types.INTEGER));
JdbcMapper simpleJdbcMapper = new SimpleJdbcMapper(schemaColumns);
JdbcInsertBolt jdbcInsertBolt = new JdbcInsertBolt(connectionProvider,simpleJdbcMapper)
.withInsertQuery("insert into userinfo(id,user_id,resource_id,count) values(?,?,?)")
.withQueryTimeoutSecs(50);
return jdbcInsertBolt;
}
public static JdbcLookupBolt getJdbcLookupBlot(){
//查询
//指定bolt的输出字段
Fields outputFields = new Fields("user_id","resource_id","count");
//指定查询条件字段
List<Column> queryColumns = Lists.newArrayList(new Column("user_id", Types.VARCHAR),new Column("resource_id",Types.VARCHAR));
String selectSql = "select count from userinfo where user_id=? and resource_id=?";
SimpleJdbcLookupMapper lookupMapper = new SimpleJdbcLookupMapper(outputFields, queryColumns);
JdbcLookupBolt jdbcLookupBolt = new JdbcLookupBolt(connectionProvider, selectSql, lookupMapper);
return jdbcLookupBolt;
}
}
具体可参考官方文档。。