本文讲诉多个站点的同时抓取rss的新闻正文,然后导入到discuz 论坛的设计与实现
新闻正文截取,请看的第一二节
http://blog.csdn.net/kissliux/article/details/14227057
http://blog.csdn.net/kissliux/article/details/14521775
discuz论坛现在很多,很多人用这个,有时候有需求把新闻自动倒入到论坛,而不是手动去发帖,那这个自动发帖怎么实现呢。其实就是操纵其数据库,discuz 几百个表很难找,不过还是有人找出来了。现在我用java代码实现了倒入的功能,在此和大家交流一下,也算做一个总结
发帖涉及到四个表
forum_post(帖子信息统计)、
forum_thread(主题资料表)、
forum_post_tableid()、
forum_forum(版块资料表),
把找到的数据 插入这个四个表即可,这里涉及到事物处理,要么同时插入成功,要么都失败
看我的DBdao 没有写的很复杂,能对付这个操作就ok
mysql配置文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.2.150:3306/home?useUnicode=true&characterEncoding=GBK
username=anyone
password=anyone
import com.liux.util.PropertiesUtil;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
public class DBUtil {
// 创建静态全局变量
static Connection conn;
private static DBUtil dbUtil;
static PreparedStatement st;
private static String driver;
private static String url;
// 连接数据库的用户名
private static String username;
// 连接数据库的密码
private static String password;
static {
PropertiesUtil pu = PropertiesUtil.getInstance();
Properties p = pu.getProerties();
driver = p.getProperty("driver").trim();
url = p.getProperty("url").trim();
username = p.getProperty("username").trim();
password = p.getProperty("password").trim();
}
/* 获取数据库连接的函数*/
public static Connection getConnection() {
Connection con = null; //创建用于连接数据库的Connection对象
try {
Class.forName(driver);// 加载Mysql数据驱动
con = DriverManager.getConnection(url, username, password);// 创建数据连接
} catch (Exception e) {
System.out.println("数据库连接失败" + e.getMessage());
}
return con; //返回所建立的数据库连接
}
/**
* 开始事务
*
* @param cnn
*/
public static void beginTransaction(Connection cnn) {
if (cnn != null) {
try {
if (cnn.getAutoCommit()) {
cnn.setAutoCommit(false);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 提交事务
*
* @param cnn
*/
public static void commitTransaction(Connection cnn) {
if (cnn != null) {
try {
if (!cnn.getAutoCommit()) {
cnn.commit();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 回滚事务
*
* @param cnn
*/
public static void rollBackTransaction(Connection cnn) {
if (cnn != null) {
try {
if (!cnn.getAutoCommit()) {
cnn.rollback();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
开始插入操作:
package com.liux.db;
import com.liux.bean.PreForumPost;
import com.liux.bean.RSSItemBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Created with IntelliJ IDEA.
* User: liuxing
* Date: 13-11-5
* Time: 下午4:41
* To change this template use File | Settings | File Templates.
*/
public class RssDao {
/* 插入数据记录,并输出插入的数据记录数*/
public void insert(RSSItemBean rss) {
int lastPid = getLastPostPid();
int lastTid = getLastPostTid();
Connection conn = DBUtil.getConnection(); // 首先要获取连接,即连接到数据库
int fid = rss.getFid();
int time = 0;
if (!checkExist(rss.getTitle())) { //检查内容是否已经被抓取了
int currentPId =lastPid;
int currentTid = lastTid;
PreparedStatement st;
try {
DBUtil.beginTransaction(conn); //开启事物
//values(15,36,13,1,'狂飙蜗牛',3,'aa',1383553857,'bb','1270.0.1',2998,1,-1,0,1);
String sql = "INSERT INTO pre_forum_post(pid, fid, tid,first, author,authorid,subject,dateline,message,useip,port,usesig,htmlon,smileyoff,attachment,position) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
st = (PreparedStatement) conn.prepareStatement(sql); // 创建用于执行静态sql语句的Statement对象
st.setInt(1, ++currentPId); // pid
st.setInt(2, fid); // fid
st.setInt(3, ++currentTid); // tid
st.setBoolean(4, true); // first
st.setString(5, "狂飙蜗牛"); // author
st.setInt(6, 3); // authorid
st.setString(7, rss.getTitle()); // subject
time = (int) (System.currentTimeMillis() / 1000);
st.setInt(8, time); // dateline
st.setString(9, rss.getContent()); // message
st.setString(10, "127.0.0.1"); // useip
st.setInt(11, 2998); // port
st.setBoolean(12, true); // usesig
st.setBoolean(13, true); //htmlon
st.setBoolean(14, false); // smileyoff
st.setBoolean(15, false); // attachment
st.setInt(16, 1); // position
st.executeUpdate();
//插入第二个表
String sql2 = "INSERT INTO pre_forum_thread (`fid`, `posttableid`, `typeid`, `sortid`, `readperm`, `price`, `author`,`authorid`, `subject`, `dateline`, `lastpost`, `lastposter`, `views`,`replies`, `displayorder`, `highlight`, `digest`, `rate`, `special`,`attachment`, `moderated`, `closed`, `stickreply`, `recommends`,`recommend_add`, `recommend_sub`, `heats`, `status`, `isgroup`, `favtimes`,`sharetimes`, `stamp`, `icon`, `pushedaid`, `cover`, `replycredit`) VALUES\n" +
"( ?, 0, 0, 0, 0, 0, '狂飙蜗牛', 3, ?,?, ?, '狂飙蜗牛', 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,32, 0, 0, 0, -1, -1, 0, 0, 0)";
st = (PreparedStatement) conn.prepareStatement(sql2);
//st.setInt(1,currentPId);
st.setInt(1, fid);
st.setString(2, rss.getTitle()); //subject
st.setInt(3, time);
st.setInt(4, time);
st.executeUpdate();
//插入第三个表
String sql3 = "INSERT INTO pre_forum_post_tableid(`pid`) VALUES (" + currentPId + ")";
st = (PreparedStatement) conn.prepareStatement(sql3);
st.executeUpdate();
//插入第四个表
//首先要查找一下forum_post (forum_post_tableid 和他的pid是一样的) 的 pid最大值 、 forum_thread 的 tid 最大值 ,由查出来的起始id+1 作为新的id。
String sql4 = "UPDATE `pre_forum_forum` SET threads=threads+1, posts=posts+1,todayposts=todayposts+1 ,lastpost='" + currentPId + " " + rss.getTitle() + " " + time + " 狂飙蜗牛" + "' WHERE fid=" + fid;
st = (PreparedStatement) conn.prepareStatement(sql4);
st.executeUpdate();
DBUtil.commitTransaction(conn);
} catch (Exception e) {
e.printStackTrace();
DBUtil.rollBackTransaction(conn);
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
System.out.println("success add subject:" + rss.getTitle());
} else {
System.out.println("this subject is exist :" + rss.getTitle());
}
}
public int getLastPostPid() {
String sql = "select pid from pre_forum_post order by pid desc limit 0,1";
Connection conn = DBUtil.getConnection();
PreForumPost pf = null;
try {
PreparedStatement prest = conn.prepareStatement(sql);
ResultSet rs = prest.executeQuery();
//List<PreForumPost> list = new ArrayList<PreForumPost>();
while (rs.next()) {
return rs.getInt(1);
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public int getLastPostTid() {
String sql = "select tid from pre_forum_post order by tid desc limit 0,1";
Connection conn = DBUtil.getConnection();
PreForumPost pf = null;
try {
PreparedStatement prest = conn.prepareStatement(sql);
ResultSet rs = prest.executeQuery();
//List<PreForumPost> list = new ArrayList<PreForumPost>();
while (rs.next()) {
return rs.getInt(1);
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
/**
* 根据title 判断内容是否已经抓取
*
* @param title
*/
public Boolean checkExist(String title) {
String sql = "select * from pre_forum_post p where p.subject = ?";
Connection conn = DBUtil.getConnection();
PreForumPost pf = null;
try {
PreparedStatement prest = conn.prepareStatement(sql);
prest.setString(1, title);
ResultSet rs = prest.executeQuery();
if (rs.next()) {
return true;
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
}
项目完整的下载地址:http://download.csdn.net/detail/a442180673/6523263
直接运行main函数 就可以了,也可以放到tomcat里面运行,有任何问题都可以找我