关于 SQLite

发表于 更新于 分类于 阅读次数：

官方文档：SQLite Home Page

自增主键

自增主键必须是 INTEGER PRIMARY KEY，INT PRIMARY KEY 都不行。

1
2
3
4
5
6
7
Caused by: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY)
	at org.sqlite.core.DB.newSQLException(DB.java:1010)
	at org.sqlite.core.DB.newSQLException(DB.java:1022)
	at org.sqlite.core.DB.throwex(DB.java:987)
	at org.sqlite.core.NativeDB._exec_utf8(NativeDB.java)
	at org.sqlite.core.NativeDB._exec(NativeDB.java:94)
	at org.sqlite.jdbc3.JDBC3Statement.executeUpdate(JDBC3Statement.java:109)

详细可参考：CREATE TABLE

With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is “INTEGER” in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an “integer primary key”. A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly “INTEGER”. Other integer type names like “INT” or “BIGINT” or “SHORT INTEGER” or “UNSIGNED INTEGER” causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.

拓展

Zepo/GYDataCenter: An alternative to Core Data for people who like using SQLite directly.

常见问题

加载 native library 出错

报错：

1
2
3
4
5
Caused by: java.lang.Exception: No native library is found for os.name=Linux and os.arch=ppc64le. path=/org/sqlite/native/Linux/ppc64le
at org.sqlite.SQLiteJDBCLoader.loadSQLiteNativeLibrary(SQLiteJDBCLoader.java:333) ~[sqlite-jdbc-3.28.0.jar:?]
at org.sqlite.SQLiteJDBCLoader.initialize(SQLiteJDBCLoader.java:64) ~[sqlite-jdbc-3.28.0.jar:?]
at org.sqlite.core.NativeDB.load(NativeDB.java:63) ~[sqlite-jdbc-3.28.0.jar:?]
at org.sqlite.SQLiteConnection.open(SQLiteConnection.java:235) ~[sqlite-jdbc-3.28.0.jar:?]

可能原因之一：

SQLite 数据初次启动时需要加载 native library，需要把打包在 JAR 中的 native library 文件解压到临时目录中，如果当前操作系统用户读写操作系统默认临时目录有权限问题，就有可能会无法正常加载到 native library 导致此报错。

参考资料

  1. Sqlite 基本概念及使用概述