sqlite建立本地資料庫並插入資料

package com.jxtech.cscsyn.util;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.poi.util.IOUtils;

public class GenSqliteDbUtil {
	private static void createRssDB(List<Byte> byteList) 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<SubscribeManage> 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<SubscribeDTO> 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 < b.length; 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();
		    }
		}
	}
	
	public static  void main(String[]args){

		// TODO Auto-generated method stub
		try {
			long start = System.currentTimeMillis();
			// 連線SQLite的JDBC
			Class.forName("org.sqlite.JDBC");
			// 建立一個資料庫名test.db的連線，如果不存在就在當前目錄下建立之
			Connection conn = DriverManager
					.getConnection("jdbc:sqlite://d:/shb.db");
			long end = System.currentTimeMillis();
			System.out.println("建立資料庫檔案並連線耗費時間：" + (end - start));
			conn.close();
			start = System.currentTimeMillis();
			conn = DriverManager.getConnection("jdbc:sqlite://d:/shb.db");
			end = System.currentTimeMillis();
			System.out.println("資料庫連線耗費時間：" + (end - start));

			start = System.currentTimeMillis();
			Statement stat = conn.createStatement();
			stat.executeUpdate ("drop table if exists tbl1;" ) ;
			// 建立一個表，兩列
			stat.executeUpdate("create table tbl1(name varchar(20), salary int);");
			end = System.currentTimeMillis();
			System.out.println("建立表耗費時間：" + (end - start));

			// 插入資料
			start = System.currentTimeMillis();
			stat.executeUpdate("insert into tbl1 values('林凡',8000);");
			stat.executeUpdate("insert into tbl1 values('羅航',7800);");
			stat.executeUpdate("insert into tbl1 values('小利',5800);");
			stat.executeUpdate("insert into tbl1 values('密詔',9100);");

			end = System.currentTimeMillis();
			System.out.println("插入四行資料耗費時間：" + (end - start));

			start = System.currentTimeMillis();
			ResultSet rs = stat.executeQuery("select * from tbl1;"); // 查詢資料
			while (rs.next()) { // 將查詢到的資料列印出來
				System.out.print("name = " + rs.getString("name") + " "); // 列屬性一
				System.out.println("salary = " + rs.getString("salary")); // 列屬性二
			}
			rs.close();
			end = System.currentTimeMillis();
			System.out.println("查詢資料耗費時間：" + (end - start));

			conn.close(); // 結束資料庫的連線

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}