今天有一个任务,是要将微博用户的昵称从数据库导入到神箭手中。
以前一直是手动操作,现在有一个需求是要直接导入到设置中。
数据库使用的是mysql,数据库连接使用的是JDBCtemplate,使用spring构建,还要用到神箭手的SDK。
之前没有接触过JDBCtemplate,一直使用的是JPA和Mybatis,所以趁着这个机会,学习一个JDBCtemplate。
一、大体框架
1.要利用JdbcTemplate,首先要添加Spring依赖。用quickstart模板创建Maven项目,在pom.xml中添加Spring依赖;
2.创建微博用户类(数据传递类),描述微博用户信息;
3.创建数据操作接口,规定需要哪些方法来操作数据,增、改各一种方法,删、查各4中方法;
4.创建数据操作类,实现数据操作接口。
1. ①创建maven项目,增加pom依赖:
<dependency><groupId>org.springframework</groupId><artifactId>spring-jdbc</artifactId><version>4.3.8.RELEASE</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-context</artifactId><version>4.3.8.RELEASE</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-core</artifactId><version>4.3.8.RELEASE</version></dependency><dependency><groupId>org.springframework</groupId><artifactId>spring-tx</artifactId><version>4.3.8.RELEASE</version></dependency><dependency><groupId>commons-dbcp</groupId><artifactId>commons-dbcp</artifactId><version>1.4</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.13</version></dependency><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency>
② Spring配置数据库:
(1)配置db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
username=root
password=root
initialSize=3
maxActive=10
遇到问题:报一个找不到时区的错误,不知道为什么,解决方法是在url中加上时区设置。
注意:修改mysql的账号密码
(2)配置applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:context="http://www.springframework.org/schema/context"xmlns:jdbc="http://www.springframework.org/schema/jdbc"xmlns:util="http://www.springframework.org/schema/util"xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsdhttp://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.3.xsdhttp://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.3.xsd"><!--<context:property-placeholder location="classpath:db.properties"/>--><bean id="propertyConfigurer"class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"><property name="location" value="file:config/db.properties"/></bean><bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"><property name="username" value="${username}"></property><property name="password" value="${password}"></property><property name="url" value="${url}"></property><property name="driverClassName" value="${driver}"></property><property name="initialSize" value="${initialSize}"></property><property name="maxActive" value="${maxActive}"></property></bean><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"></property></bean>
</beans>
注意:要修改location的路径。
配置文件就配置完了。
③创建WeibaoUser类,用于描述,设置get set 方法 和构造方法。
package add.users;import java.util.Date;public class WeiboUser {private long id;private long sysUserId;private long cId;private long mBlogUserId;private String userName;private String url;private String urlMd5;private boolean siteType;private boolean status;private String insertTime;private String updateTime;/*** 构造方法*/public WeiboUser() {}public WeiboUser(long id, String userName, boolean siteType, boolean status) {this.id = id;this.userName = userName;this.siteType = siteType;this.status = status;}public WeiboUser(long id, long sysUserId, long cId, long mBlogUserId, String userName,String url, String urlMd5, boolean siteType, boolean status, String insertTime, String updateTime) {this.id = id;this.sysUserId = sysUserId;this.cId = cId;this.mBlogUserId = mBlogUserId;this.userName = userName;this.url = url;this.urlMd5 = urlMd5;this.siteType = siteType;this.status = status;this.insertTime = insertTime;this.updateTime = updateTime;}public long getId() {return id;}public void setId(long id) {this.id = id;}public long getSysUserId() {return sysUserId;}public void setSysUserId(long sysUserId) {this.sysUserId = sysUserId;}public long getcId() {return cId;}public void setcId(long cId) {this.cId = cId;}public long getmBlogUserId() {return mBlogUserId;}public void setmBlogUserId(long mBlogUserId) {this.mBlogUserId = mBlogUserId;}public String getUserName() {return userName;}public void setUserName(String userName) {this.userName = userName;}public String getUrl() {return url;}public void setUrl(String url) {this.url = url;}public String getUrlMd5() {return urlMd5;}public void setUrlMd5(String urlMd5) {this.urlMd5 = urlMd5;}public boolean isSiteType() {return siteType;}public void setSiteType(boolean siteType) {this.siteType = siteType;}public boolean isStatus() {return status;}public void setStatus(boolean status) {this.status = status;}public String getInsertTime() {return insertTime;}public void setInsertTime(String insertTime) {this.insertTime = insertTime;}public String getUpdateTime() {return updateTime;}public void setUpdateTime(String updateTime) {this.updateTime = updateTime;}
}
④创建数据传递接口,规定操作数据的方法
package add.users.dao;import add.users.WeiboUser;
import org.springframework.jdbc.core.JdbcTemplate;import java.util.List;public interface BaseJdbcTemplate {//private JdbcTemplate template;//public abstract boolean addUser(WeiboUser user);WeiboUser queryUserById(long id);List<WeiboUser> queryUsers();
}
⑤.创建数据传递类,实现数据操作(目前只使用到获得数据,增删改查以后补上)这是根据ID查一行和查全部行
package add.users;import add.users.dao.BaseJdbcTemplate;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;import java.util.List;public class JdbcTemplateImpl implements BaseJdbcTemplate {private ApplicationContext apc = null;private JdbcTemplate jdbcTemplate = null;public JdbcTemplateImpl(){apc = new FileSystemXmlApplicationContext(new String[]{"config/applicationContext.xml"});jdbcTemplate =apc.getBean("jdbcTemplate", JdbcTemplate.class);}// public boolean addUser(WeiboUser user) {
// return false;
// }public WeiboUser queryUserById(long id) {String sql = "SELECT id, cid, sys_user_id, mblog_userid, url, url_md5, insert_time, update_time, " +"mblog_user_name, site_type, status FROM mblog_user_config WHERE id=?";WeiboUser user = null;user = jdbcTemplate.queryForObject(sql, new QueryUserRowMapper(), id);return user;}public List<WeiboUser> queryUsers() {String sql = "SELECT * FROM mblog_user_config";//WeiboUser user = null;List<WeiboUser> users = null;users = jdbcTemplate.query(sql, new QueryUserRowMapper());return users;}
}
提取出的RowMapper实现类:(获取全属性数据,指定数据获取暂时没有实现)
package add.users;import org.springframework.jdbc.core.RowMapper;import java.sql.ResultSet;
import java.sql.SQLException;public class QueryUserRowMapper implements RowMapper<WeiboUser> {public WeiboUser mapRow(ResultSet resultSet, int rowNum) throws SQLException {WeiboUser user = new WeiboUser(resultSet.getLong("id"),resultSet.getString("mblog_user_name"),resultSet.getBoolean("site_type"),resultSet.getBoolean("status"));user.setcId(resultSet.getLong("cid"));user.setmBlogUserId(resultSet.getLong("mblog_userid"));user.setUrl(resultSet.getString("url"));user.setUrlMd5(resultSet.getString("url_md5"));user.setSysUserId(resultSet.getLong("sys_user_id"));user.setInsertTime(resultSet.getDate("insert_time").toString());user.setUpdateTime(resultSet.getDate("update_time").toString());return user;}
}
简单的test一下:
import java.util.List;public class test1 {public static void main(String[] args) throws AWTException {JdbcTemplateImpl jdbcTemplate = new JdbcTemplateImpl();List<WeiboUser> users = jdbcTemplate.queryUsers();for(WeiboUser user:users){if(user.isSiteType()&&user.isStatus())System.out.println(user.getUserName());}}
}
控制台输出数据:
jdbc配置连接完成。
下一步是将数据上传到神箭手的 新浪微博用户昵称或用户ID或关键字或话题 框中。
根据神箭手的java SDK,首先是在pom中引入神箭手的依赖,然后配置账号密钥,然后就是自定义设置。
①在pom中引入
<dependency><groupId>io.shenjian.sdk</groupId><artifactId>java-sdk</artifactId><version>1.0.4</version></dependency>
②配置连接,配置stop和start
package add.users;import io.shenjian.sdk.ShenjianClient;
import io.shenjian.sdk.ShenjianException;
import io.shenjian.sdk.model.*;public class shanjianshouConnect {String userKey = "<your user_key>";String userSecret = "<your user_secret>";ShenjianClient client = new ShenjianClient(userKey, userSecret);public void stopCrawler(){try{int crawlerId = 3365960;client.stopCrawler(crawlerId);} catch(ShenjianException e){e.printStackTrace();}}public boolean startCrawler(){try {/* 此处填写要操作的爬虫的ID */int crawlerId = 3365960;client.startCrawler(crawlerId);} catch (ShenjianException e) {e.printStackTrace();}return true;}
}
③使用ShenjianClient类中的configCrawlerCustom方法上传名字
package add.users;import io.shenjian.sdk.ShenjianClient;
import io.shenjian.sdk.ShenjianException;
import io.shenjian.sdk.model.Webhook;import java.util.HashMap;
import java.util.List;
import java.util.Map;public class addUserToSjs {shanjianshouConnect connect = new shanjianshouConnect();public boolean addUser(){JdbcTemplateImpl jdbcTemplate = new JdbcTemplateImpl();List<WeiboUser> userList = jdbcTemplate.queryUsers();StringBuffer sb = new StringBuffer("");for(WeiboUser user:userList){if(user.isSiteType()&&user.isStatus()){sb.append(user.getUserName());sb.append(" ");}}String totalUSers = sb.toString();String[] users = totalUSers.split(" ");try {int crawlerId = 3365960;Map<String, Object> configMap = new HashMap<String, Object>();configMap.put("keywordsOrNames", new String[]{"黄磊微博"});//configMap.put("keywordsOrNames", users);connect.client.configCrawlerCustom(crawlerId, configMap);return true;} catch (ShenjianException e) {e.printStackTrace();return false;}}public void getWebHook(){ShenjianClient client = new ShenjianClient(connect.userKey, connect.userSecret);try {/* 此处填写要操作的爬虫的ID */int crawlerId = 3365960;Webhook webhookInfo = client.getWebhookInfo(crawlerId);System.out.println("Webhook URL : " + webhookInfo.getUrl());System.out.println("Events : ");for (int i = 0; i < webhookInfo.getEvents().length; i++){System.out.println(i+1 + webhookInfo.getEvents()[i]);}} catch (ShenjianException e) {e.printStackTrace();}}}
注意:上传格式为数组,关键字是keywordsOrNames
关键字在:
然后进行测试:
package add.users;import io.shenjian.sdk.ShenjianException;
import io.shenjian.sdk.model.AppStatus;import java.awt.*;
import java.util.List;public class test1 {public static void main(String[] args) throws AWTException {addUserToSjs addUser = new addUserToSjs();
// addUser.addUser();Robot r = new Robot();shanjianshouConnect connect = new shanjianshouConnect();//AppStatus status = connect.client.getCrawlerStatus(3365960);addUser.addUser();}
}
注意,添加完成后需要重启爬虫服务,才能配置成功
然后进入神箭手查看设置:
配置成功
参考:
https://blog.csdn.net/u010004204/article/details/76222442 JDBC
https://docs.shenjian.io/develop/platform/sdk/sdk-java.html 神箭手