java连接sqlite数据库

iszjw

package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class TestSQLite {
public static void main(String[] a) {
TestSQLite sqlite = new TestSQLite();
String sql = sqlLogData();
// ////////////////////////
String databaseName = "c:/logdata.db";
sqlite.exeCreateTable(sql, databaseName);
// ///////////////////////
sql = sqlIpAddress();
databaseName = "c:/IPaddress.db";
sqlite.exeCreateTable(sql, databaseName);
}
/**
* 创建数据库
*
* @param sql
*/
public static void exeCreateTable(String sql, String databaseName) {
try {
// 连接SQLite的JDBC
Class.forName("org.sqlite.JDBC");
// 建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建之
// String filename = "IPaddress.db";此行是在此类滴当前路经下建一个IPaddress数据库
// String filename = "c:/IPaddress.db";//此行是在C盘下建一个IPaddress数据库,盘符好象要小写才行.注意前面的有个"/"
Connection conn = DriverManager.getConnection("jdbc:sqlite:/"
+ databaseName);
Statement stat = conn.createStatement();
stat.executeUpdate(sql);// 创建一个表，两列
/*
* stat.executeUpdate("create table FlyCrocodile (name varchar(30),age varchar(30)) "); //建表
* stat.executeUpdate("insert into flycrocodile values('flucrocodile',21);"); //插入数据
* 插入数据
* ResultSet rs = stat.executeQuery("select * from tbl1;"); // 查询数据
*
* while (rs.next()) { // 将查询到的数据打印出来
*
* System.out.print("name = " + rs.getString("name") + " "); // 列属性一
*
* System.out.println("salary = " + rs.getString("age")); // 列属性二 }
*/
// rs.close();
conn.close(); // 结束数据库的连接
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 创建logdata数据库
*
* @return String
*/
private static String sqlLogData() {
String s = null;
// ////////////////////////////////////////
String s1 = "Create TABLE LogData(";
String s2 = "logid integer PRIMARY KEY ASC ON CONFLICT Ignore AUTOINCREMENT UNIQUE NOT NULL";
//   String s3 = ",logdate datetime default date()";
String s3 = ", logdate varchar(12)";
String s4 = ",logtime varchar(10)";
String s5 = ",host varchar(50)";
String s6 = ",url varchar(50)";
String s7 = ",verb varchar(50)";
String s8 = ",clientIp varchar(50)";
String s9 = ",event varchar(50)";
String s10 = ",content text";
String s11 = ");";
// ///////////////////
s = s1 + s2 + s3 + s4 + s5 + s6 + s7 + s8 + s9 + s10 + s11;
return s;
}
/**
* 创建IpAddress数据库
*
* @return String
*/
private static String sqlIpAddress() {
String s = null;
// ////////////////////////////////////////
String s1 = "Create TABLE iptable( ";
String s2 = "ip1 bigint";
String s3 = ",ip2 bigint";
String s4 = ",country varchar(100)";
String s5 = ",Primary Key(ip2,ip1)";
String s6 = ");";
// ///////////////////
s = s1 + s2 + s3 + s4 + s5 + s6;
return s;
}
/**
* Connection
*
* @return Connection
*/
public static Connection getConnection() {
Connection conn = null;
try {
// 连接SQLite的JDBC
Class.forName("org.sqlite.JDBC");
// 建立一个数据库名zieckey.db的连接，如果不存在就在当前目录下创建之
// String filename = "c:/IPaddress.db";
conn = DriverManager.getConnection("jdbc:sqlite:/c:/IPaddress.db");
/*
* Statement stat = conn.createStatement();
*
* stat.executeUpdate(sql);// 创建一个表，两列
*/
} catch (Exception e) {
}
return conn;
}
}