SQLite之 JDBC入门

SQLite是一种C语言库，实现了一个 小型， 快速， 自包含， 高可靠性， 功能齐全的 SQL数据库引擎。本身不提供java接口，如果需要通过java连接SQLite数据库，则需要SQLite JDBC驱动程序，来充当媒介。

由Taro L. Saito开发的SQLite JDBC驱动程序是Zentus SQLite JDBC驱动程序的扩展，使Java能够访问SQLite数据库文件。Xerial版本的SQLiteJDBC库不需要进行配置，在任何OS环境中均可运行,Windows，Mac OS X，Linux和纯Java SQLite的所有本机库都被组装到一个JAR（Java存档）中文件。Xerial版本的SQLiteJDBC相比较最初的Zentus的SQLite JDBC驱动程序，用户不需要使用命令行参数（例如-Djava.library.path =）来设置本机代码（dll，jnilib，so文件，它们是JNDI C程序）的路径等参数。

1.使用方式

SQLiteJDBC有多种使用方式，本文介绍2种：jar运行方式和maven方式

1. 1 jar独立方式

• 从https://bitbucket.org/xerial/sqlite-jdbc/downloads/下载需要版本的jar包；
• 从代码中加载JDBC驱动程序org.sqlite.JDBC

如果没有指定路径，默认db文件存储在System.getProperty("java.io.tmpdir").

编写测试文件 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);
      }
    }
  }
}

编译Sample.java

 javac Sample.java

运行java文件，确保之前下载的sqlite-jdbc-(VERSION).jar在当前目录，

> java -classpath ".;sqlite-jdbc-(VERSION).jar" Sample   # in Windows
> java -classpath ".:sqlite-jdbc-(VERSION).jar" Sample   # in Mac or Linux

输出 

name = leo
id = 1
name = yui
id = 2

1.2 maven方式

maven方式与其他通过maven构建的没有什么区别，直接添加依赖即可。

        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.30.1</version>
        </dependency>

2.常见问题

2.1 加载外部db

因为，SQLite 文件格式稳定，跨平台且向后兼容，开发人员保证至少在2050年之前保持这种格式。SQLite数据库文件通常用作在系统之间传输丰富内容的容器并作为数据的长期存档格式 。我们可以加载其他平台的db直接使用

Windows中加载，file C:\work\mydatabase.db

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

其他平台 UNIX (Linux, Mac OS X, etc ) file /home/leo/work/mydatabase.db

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

Java package中 

Connection conn = DriverManager.getConnection("jdbc:sqlite::resource:org/yourdomain/sample.db"); 

Connection conn = DriverManager.getConnection("jdbc:sqlite::resource:jar:http://www.xerial.org/svn/sqlite-jdbc/src/test/resources/testdb.jar!/sample.db"); 

远程服务器中 

Connection conn = DriverManager.getConnection("jdbc:sqlite::resource:http://www.xerial.org/svn/project/XerialJ/trunk/sqlite-jdbc/src/test/java/org/sqlite/sample.db"); 

2.2 Memory方式

SQLite数据库通常存储在单个普通磁盘文件中。但是，在某些情况下，数据库可能存储在内存中。完成此操作后，不会打开任何磁盘文件。而是纯粹在内存中创建一个新数据库。一旦数据库连接关闭，该数据库将不复存在。

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

2.3 连接配置

SQLite默认支持需要配置，我们可以通过配置参数来更好使用和优化。

SQLiteConfig config = new SQLiteConfig();
// 设置共享缓存  
//这允许单独的数据库连接共享相同的内存数据库。当然，共享内存数据库的所有数据库连接都必
//须处于同一进程中。当与数据库的最后一个连接关闭时，数据库将被自动删除并回收内存。
config.setSharedCache(true);

//设置数据库的读写模式
config.setReadOnly(boolean readOnly) 

//更改每次打开数据库文件时SQLite将在内存中同时保存的数据库磁盘页的最大数量。
config.setCacheSize（int numberOfPages）

Connection conn = DriverManager.getConnection("jdbc:sqlite:sample.db", config.toProperties());

除此之外，还可以通过set(Pragma pragma, boolean flag) 手动设置一些参数 ，详细可以参考官网配置

   // Parameters requiring SQLite3 API invocation
        OPEN_MODE("open_mode", "Database open-mode flag", null),
        SHARED_CACHE("shared_cache", "Enable SQLite Shared-Cache mode, native driver only", OnOff),
        LOAD_EXTENSION("enable_load_extension", "Enable SQLite load_extention() function, native driver only", OnOff),

        // Pragmas that can be set after opening the database
        CACHE_SIZE("cache_size"),
        MMAP_SIZE("mmap_size"),  //内存映射模式
        CASE_SENSITIVE_LIKE("case_sensitive_like", OnOff),
        COUNT_CHANGES("count_changes", OnOff),
        DEFAULT_CACHE_SIZE("default_cache_size"),
	DEFER_FOREIGN_KEYS("defer_foreign_keys", OnOff),
        EMPTY_RESULT_CALLBACKS("empty_result_callback", OnOff),
        ENCODING("encoding", toStringArray(Encoding.values())),
        FOREIGN_KEYS("foreign_keys", OnOff),
        FULL_COLUMN_NAMES("full_column_names", OnOff),
        FULL_SYNC("fullsync", OnOff),
        INCREMENTAL_VACUUM("incremental_vacuum"),
        JOURNAL_MODE("journal_mode", toStringArray(JournalMode.values())),
        JOURNAL_SIZE_LIMIT("journal_size_limit"),
        LEGACY_FILE_FORMAT("legacy_file_format", OnOff),
        LOCKING_MODE("locking_mode", toStringArray(LockingMode.values())),
        PAGE_SIZE("page_size"),
        MAX_PAGE_COUNT("max_page_count"),
        READ_UNCOMMITED("read_uncommited", OnOff),
        RECURSIVE_TRIGGERS("recursive_triggers", OnOff),
        REVERSE_UNORDERED_SELECTS("reverse_unordered_selects", OnOff),
        SECURE_DELETE("secure_delete", new String[] { "true", "false", "fast" }),
        SHORT_COLUMN_NAMES("short_column_names", OnOff),
        SYNCHRONOUS("synchronous", toStringArray(SynchronousMode.values())),
        TEMP_STORE("temp_store", toStringArray(TempStore.values())),
        TEMP_STORE_DIRECTORY("temp_store_directory"),
        USER_VERSION("user_version"),
        APPLICATION_ID("application_id"),
        
        // Others
        TRANSACTION_MODE("transaction_mode", toStringArray(TransactionMode.values())),
        DATE_PRECISION("date_precision", "\"seconds\": Read and store integer dates as seconds from the Unix Epoch (SQLite standard).\n\"milliseconds\": (DEFAULT) Read and store integer dates as milliseconds from the Unix Epoch (Java standard).", toStringArray(DatePrecision.values())),
        DATE_CLASS("date_class", "\"integer\": (Default) store dates as number of seconds or milliseconds from the Unix Epoch\n\"text\": store dates as a string of text\n\"real\": store dates as Julian Dates", toStringArray(DateClass.values())),
        DATE_STRING_FORMAT("date_string_format", "Format to store and retrieve dates stored as text. Defaults to \"yyyy-MM-dd HH:mm:ss.SSS\"", null),
        BUSY_TIMEOUT("busy_timeout", null),
        HEXKEY_MODE("hexkey_mode", toStringArray(HexKeyMode.values())),
        PASSWORD("password", null);