Java 操作 SQLite 数据库

SQLite JDBC is a library for accessing SQLite databases through the JDBC API. For the general usage of JDBC, see JDBC Tutorial or Oracle JDBC Documentation.

1. Download sqlite-jdbc-(VERSION).jar from the download page (or by using Maven) then append this jar file into your classpath.
2. Load the JDBC driver org.sqlite.JDBC from your code. (see the example below)

More usage examples are available at https://bitbucket.org/xerial/sqlite-jdbc/wiki/Usage

Usage Example (Assuming sqlite-jdbc-(VERSION).jar is placed in the current directory)

> javac Sample.java
> java -classpath ".;sqlite-jdbc-(VERSION).jar" Sample   # in Windows
or 
> java -classpath ".:sqlite-jdbc-(VERSION).jar" Sample   # in Mac or Linux
name = leo
id = 1
name = yui
id = 2

Sample.java

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

public class Sample
{
  public static void main(String[] args) throws ClassNotFoundException
  {
    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.sqlite.JDBC");

    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.

      statement.executeUpdate("drop table if exists person");
      statement.executeUpdate("create table person (id integer, name string)");
      statement.executeUpdate("insert into person values(1, 'leo')");
      statement.executeUpdate("insert into person values(2, 'yui')");
      ResultSet rs = statement.executeQuery("select * from person");
      while(rs.next())
      {
        // read the result set
        System.out.println("name = " + rs.getString("name"));
        System.out.println("id = " + rs.getInt("id"));
      }
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
  }
}

How to Specify Database Files

Here is an example to select a file C:\work\mydatabase.db (in Windows)

Connection connection = DriverManager.getConnection("jdbc:sqlite:C:/work/mydatabase.db");

A UNIX (Linux, Mac OS X, etc) file /home/leo/work/mydatabase.db

Connection connection = DriverManager.getConnection("jdbc:sqlite:/home/leo/work/mydatabase.db");

How to Use Memory Databases

SQLite supports on-memory database management, which does not create any database files. To use a memory database in your Java code, get the database connection as follows:

Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:");