1.安装sqlite等入门可以查看:http://yuanzhifei89.iteye.com/blog/1123870
或者红薯大大的http://www.oschina.net/question/12_53183
2.需要导入sqlite-jdbc的jar包,下载地址:https://bitbucket.org/xerial/sqlite-jdbc/downloads
我使用的是sqlite-jdbc-3.8.11.jar,然后放在WEB-INF的lib下直接加入就好。
import java.sql.*;
private void createRssDB(List byteList,String firmId) throws SQLException, Exception {
Connection conn=null;
PreparedStatement prep=null;
PreparedStatement rssInfoPrep=null;
InputStream in = null;
try{
Class.forName("org.sqlite.JDBC");
String url=Thread.currentThread().getContextClassLoader().getResource("/").toString().substring(5);
conn = DriverManager.getConnection("jdbc:sqlite:"+url+"rss.db");
LOG.info("rss.db的路径"+url+"rss.db");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists rssType;");
StringBuffer rssTableSql=new StringBuffer("create table rssType (");
rssTableSql.append("id INTEGER, name NTEXT,");
rssTableSql.append("typeId INTEGER,logoUrl NTEXT,");
rssTableSql.append("sort INTEGER);");
stat.executeUpdate(rssTableSql.toString());
LOG.info("创建rssTypeSql:"+rssTableSql.toString());
prep = conn.prepareStatement(
"insert into rssType values (?, ?, ?, ?, ?);");
List list=this.getRssType(firmId);
if(list.size()>0){
for (SubscribeManage subscribeManage : list) {
prep.setInt(1,subscribeManage.getId());
prep.setString(2,subscribeManage.getRssCategory());
prep.setInt(3,subscribeManage.getId());
prep.setString(4,subscribeManage.getRssImgUrl());
prep.setInt(5,subscribeManage.getOrder());
prep.addBatch();
}
}
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
Statement stats = conn.createStatement();
stats.executeUpdate("drop table if exists rssInfo;");
StringBuffer rssInfoSql=new StringBuffer("create table rssInfo (");
rssInfoSql.append("id INTEGER, name NTEXT,");
rssInfoSql.append("itr INTEGER,typeId INTEGER,");
rssInfoSql.append("snum INTEGER,url NTEXT,");
rssInfoSql.append("isSubs INTEGER,sort NTEXT,");
rssInfoSql.append("vurl NTEXT,wid INTEGER,");
rssInfoSql.append("subTime TIMESTAMP);");
stats.executeUpdate(rssInfoSql.toString());
rssInfoPrep = conn.prepareStatement(
"insert into rssInfo(id,name,itr,typeId,url,sort) values (?, ?, ?, ?, ?, ?);");
List subList=subscribeService.getRssInfo(firmId);
if(subList.size()>0){
for (SubscribeDTO subscribeDTO : subList) {
rssInfoPrep.setInt(1, subscribeDTO.getId());
rssInfoPrep.setString(2, subscribeDTO.getSubName());
rssInfoPrep.setString(3, subscribeDTO.getInfo());
rssInfoPrep.setInt(4, subscribeDTO.getSubManageId());
rssInfoPrep.setString(5, subscribeDTO.getRssImgUrl());
rssInfoPrep.setInt(6, subscribeDTO.getOrder());
rssInfoPrep.addBatch();
}
}
conn.setAutoCommit(false);
rssInfoPrep.executeBatch();
conn.setAutoCommit(true);
in=new FileInputStream(url+"rss.db");
byte[] b = IOUtils.toByteArray(in);
for (int i = 0; i
byteList.add(b[i]);
}
LOG.info("rss.db的路径"+url+"rss.db");
}catch(Exception e){
LOG.info("异常:"+e.getMessage());
e.printStackTrace();
}finally{
if(prep!=null){
prep.close();
}
if(rssInfoPrep!=null){
rssInfoPrep.close();
}
if(conn!=null){
conn.close();
}
if(in!=null){
in.close();
}
}
}
会在项目中产生rss.db,F5刷新一下工程就会看到。
后来我在纠结生成rss.db的生成路径的时候发现如果这段代码放在controller中是产生在项目中,如果放在service调用产生在tomcat路径下,我就很疑惑,然后我就指定了路径。
linus下安装sqlite3: