java使用sqlite数据库

	        
package com.rshare.utils;

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.text.SimpleDateFormat;
import java.util.Date;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 	使用sqlite数据库
 * 	统计在线游客
 * @author jspanjsp
 *
 */

public class OpSqliteDB {

    
	private static final Logger log = LoggerFactory.getLogger(OpSqliteDB.class);
	
    private static final String Class_Name = "org.sqlite.JDBC";

    //定义sqlite数据库存放位置

    private static final String DB_URL = "jdbc:sqlite:"+System.getProperty("user.dir")+"/config/"+"database.db";

    public static void main(String args[]) {

        Connection connection = null;
        try {
            //connection = createConnection();
            //initdb(connection);
            //System.out.println(IsTableExist(connection));
            //func1(connection);
        	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        	add("127.0.0.1", sdf.format(new Date()), "index.html");
        	
        }catch(Exception e) {
            e.printStackTrace();
        } finally{
            try {
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {
                System.err.println(e);
            }
        }
    }


    //往sqlite数据库增加数据			访问ip，时间，访问页面
    public static boolean add(String ip,String lastRefreshTime,String visitHtml) {
    	boolean b = false;
        Connection connection = null;
        try {
            connection = createConnection();
            String sql = "insert into online_visitor(ip,lastRefreshTime,visitHtml) values(?,?,?) ";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, ip);
            ps.setString(2, lastRefreshTime);
            ps.setString(3, visitHtml);
            ps.addBatch();
            int[] num = ps.executeBatch();
            for (int i : num) {
				System.out.println(i);
			}
        }catch (Exception e) {
            e.printStackTrace();
        }finally{
            try {
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    	return b;
    } 

    

    // 创建Sqlite数据库连接

    public static Connection createConnection() throws SQLException, ClassNotFoundException {
        Class.forName(Class_Name);
        return DriverManager.getConnection(DB_URL);
    }

    

  //判断表是否存在
  	private static boolean IsTableExist(Connection connection) throws SQLException {
  			boolean isTableExist=false;
  			int num = 0;
  	    	Statement statement = connection.createStatement();
  	    	String sql = "SELECT count(*) as c FROM sqlite_master WHERE type='table' AND name='online_visitor'";
  	    	ResultSet rs = statement.executeQuery(sql);
  	    	while (rs.next()) {
  	    		num = Integer.parseInt(rs.getString("c"));
  	    	}
  	    	if (num>=1) {
  	    		isTableExist = true;
  	    	}
  			return isTableExist;
  	 }

    //数据库不存在，初始化数据库
    public static void initdb(Connection connection) throws SQLException {
    	//建表语句
        String creatsql = "CREATE TABLE online_visitor("
	              + "ip varchar(20) not null,"
	              + "lastRefreshTime varchar(30) not null,"
	              + "visitHtml varchar(200) not null"
	              + ");";
    	Statement statement = connection.createStatement();
    	statement.executeUpdate(creatsql);
    	if(statement != null) {
    		statement.close();
    	}
    }


    public static void func1(Connection connection) throws SQLException {
        Statement statement = connection.createStatement();
        Statement statement1 = connection.createStatement();
        statement.setQueryTimeout(30); // set timeout to 30 sec.

        String creatsql = "CREATE TABLE test("
        		              + "name varchar(10) not null,"
        		              + "age int(4) not null"
        		              + ");";    
        //statement.executeUpdate(creatsql);

        //statement1.executeUpdate("insert into test(name,age) values('" + "张三'," +12 + ")");
        // 执行查询语句
        ResultSet rs = statement.executeQuery("select * from test");
        while (rs.next()) {
            String col1 = rs.getString("name");
            String col2 = rs.getString("age");
            System.out.println("col1 = " + col1 + "  col2 = " + col2);
            System.out.println();
            // 执行插入语句操作
            // 执行更新语句
            //statement1.executeUpdate("update test set name=" +  "张三" + " where id'" +  1 + "'");
        }
    }

}