如果是maven项目,在pom.xml引入依赖
<!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.7.2</version>
</dependency>
非maven项目jar下载:
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 + "'");
}
}
}