用Java访问SQLite

最近使用了SQLite.

为什么用SQLite
最近开发一个应用需要储存小部分数据，经过简单如下简单考虑后，决定使用SQLite.
1. SQLite为小型手持设备或者嵌入式设计的，非常小巧;
2. Nokia, Oracle and Adobe等是发起人；
3. Andorid平台上采用了SQLite.

资源
1. SQLite: http://www.sqlite.org/
2. SQLite JDBC: http://www.zentus.com/sqlitejdbc/

用Java访问SQLite
1. 创建数据库test.db。首先解压sqlite-shell-win32-x86-3071100.zip. 运行: SQLite3 test.db。
2. 在Eclipse中建一个Java项目。把sqlitejdbc-v056.jar加到编译路径(Build Path)上。
3. 写代码测试。在JDBC的网站上有测试JDBC的基本的例子。如下:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class TestSQLite {

	  public static void main(String[] args) throws Exception {
	    Class.forName("org.sqlite.JDBC");
	    Connection conn =
	      DriverManager.getConnection("jdbc:sqlite:test.db");
	    Statement stat = conn.createStatement();
	    stat.executeUpdate("drop table if exists people;");
	    stat.executeUpdate("create table people (name, occupation);");
	    PreparedStatement prep = conn.prepareStatement(
	      "insert into people values (?, ?);");

	    prep.setString(1, "Gandhi");
	    prep.setString(2, "politics");
	    prep.addBatch();
	    prep.setString(1, "Turing");
	    prep.setString(2, "computers");
	    prep.addBatch();
	    prep.setString(1, "Wittgenstein");
	    prep.setString(2, "smartypants");
	    prep.addBatch();

	    conn.setAutoCommit(false);
	    prep.executeBatch();
	    conn.setAutoCommit(true);

	    ResultSet rs = stat.executeQuery("select * from people;");
	    while (rs.next()) {
	      System.out.println("name = " + rs.getString("name"));
	      System.out.println("job = " + rs.getString("occupation"));
	    }
	    rs.close();
	    conn.close();
	  }
	}

4.测试结果:
name = Gandhi
job = politics
name = Turing
job = computers
name = Wittgenstein

job = smartypants

5. 在当前项目所在的目录，会多出一个文件:test.db.  

所以这一步"1. 创建数据库test.db。首先解压sqlite-shell-win32-x86-3071100.zip. 运行: SQLite3 test.db。"应该可以省略的。