SQLite是一個很輕量的資料庫。詳細介紹我就不多說了,這個東西應該在嵌入式裡面用得比較多。根據我看到的資料(幾個月以前看的),現在安卓應用應該也會用這個,HTML5 也支援sqlite。
SQLite也支援 SQL 語句。它的每一個資料庫都是一個字尾為db的檔案
我手裡有一個這樣的檔案,裡面已經有一些資料了。
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIn5GcuETYhFDZwEDZ1kjM4MGNzgjYxM2Y1UWZ5YzNkRjYlZDOfdWbp9CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.png)
怎麼通路這個資料?
1. 最簡單的在 windows 平台下下載下傳一個 SQLiteSpy 程式
2. 使用 Java JDBC 通路 SQLite 資料庫
需要添加驅動依賴
<!-- https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc -->
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.23.1</version>
</dependency>
java代碼
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class App {
public static void main(String[] args) {
String url = "jdbc:sqlite:sqlDb.db";
Connection conn = null;
PreparedStatement pStatement = null;
ResultSet resultSet = null;
try {
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection(url);
pStatement = conn.prepareStatement("select * from `AccLvl`");
resultSet = pStatement.executeQuery();
while(resultSet.next()) {
System.out.println("AccLvlNum:" + resultSet.getInt("AccLvlNum") + " " + "timeZoneTableId: " + resultSet.getInt("timeZoneTableId") + " " + "lastModified: " + resultSet.getTimestamp("lastModified"));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(resultSet != null) {
resultSet.close();
}
if(pStatement != null) {
pStatement.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
程式執行結果
AccLvlNum:0 timeZoneTableId: 0 lastModified: 1970-01-19 01:55:17.192
AccLvlNum:1 timeZoneTableId: 1 lastModified: 1970-01-19 01:55:17.192
AccLvlNum:2 timeZoneTableId: 2 lastModified: 1970-01-19 01:55:17.192
AccLvlNum:3 timeZoneTableId: 3 lastModified: 1970-01-19 01:55:17.192
AccLvlNum:4 timeZoneTableId: 4 lastModified: 1970-01-19 01:55:17.193
AccLvlNum:5 timeZoneTableId: 5 lastModified: 1970-01-19 01:55:17.193
AccLvlNum:6 timeZoneTableId: 6 lastModified: 1970-01-19 01:55:17.193
AccLvlNum:7 timeZoneTableId: 7 lastModified: 1970-01-19 01:55:17.193
AccLvlNum:8 timeZoneTableId: 8 lastModified: 1970-01-19 01:55:17.193
AccLvlNum:9 timeZoneTableId: 9 lastModified: 1970-01-19 01:55:17.194
AccLvlNum:10 timeZoneTableId: 10 lastModified: 1970-01-19 01:55:17.194
AccLvlNum:11 timeZoneTableId: 11 lastModified: 1970-01-19 01:55:17.194
AccLvlNum:12 timeZoneTableId: 12 lastModified: 1970-01-19 01:55:17.194
AccLvlNum:13 timeZoneTableId: 13 lastModified: 1970-01-19 01:55:17.194
AccLvlNum:14 timeZoneTableId: 14 lastModified: 1970-01-19 01:55:17.194
AccLvlNum:15 timeZoneTableId: 15 lastModified: 1970-01-19 01:55:17.195
AccLvlNum:16 timeZoneTableId: 16 lastModified: 1970-01-19 01:55:17.195
AccLvlNum:17 timeZoneTableId: 17 lastModified: 1970-01-19 01:55:17.195
3. linux 通路(Ubuntu)
首先安裝 sqlite3
sudo apt-get install sqlite3
然後建立一個資料庫,之前說了sqlite 的資料庫就是一個 db 檔案。 然後就可以進行資料庫操作了,大體文法跟mysql是一樣的,有細微的差別
vmware@ubuntu:~/frank$ sqlite3 test.db
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> .table
sqlite> create table t_user(
...> id integer primary key autoincrement,
...> name varchar(20) not null,
...> password varchar(20) not null
...> );
sqlite> insert into t_user(name, password) values ('jordan','1234'),('kobe', '3456'),('james', '7890');
sqlite> select * from t_user;
1|jordan|1234
2|kobe|3456
3|james|7890
sqlite> select * from t_user where name='kobe';
2|kobe|3456
sqlite>
用到的sql
.table
create table t_user(
id integer primary key autoincrement,
name varchar(20) not null,
password varchar(20) not null
);
insert into t_user(name, password) values ('jordan','1234'),('kobe', '3456'),('james', '7890');
select * from t_user
select * from t_user where name='kobe';