轻量级数据库 SQLite 的安装和使用
(1) SQLite 介绍
(2) Linux下SQLite 编译安装
sqlite-amalgamation-3070900.zip 源码
sqlite-autoconf-3070900.tar.gz 源码 + Makefile 等
sqlite-shell-linux-x86-3070900.zip 命令行工具
解压 sqlite-autoconf-3070900.tar.gz ，进入文件夹，执行以下命令即可完成安装：
./configure --prefix=/sdb1/development/sqlite3
make && make install
make && make install
(3) Linux下SQLite 使用
解压 sqlite-autoconf-3070900.tar.gz ，使用 shell 工具操作数据库，十分方便。
[zkl@gd47 sqlite_dev]$ sqlite3 test.db (若不存在，则创建test.db数据库文件，若存在，则打开文件)
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .databases
seq name file
--- --------------- ----------------------------------------------------------
0 main /home/zkl/sqlite_dev/test.db
sqlite> .schema
CREATE TABLE score (StuID int, Score float);
CREATE TABLE test_tab (f1 int, f2 long);
sqlite> select score.*,test_tab.f2 from score, test_tab where score.StuID=test_tab.f1;
1|90.5|120
sqlite> insert into test_tab values(2,120);
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .databases
seq name file
--- --------------- ----------------------------------------------------------
0 main /home/zkl/sqlite_dev/test.db
sqlite> .schema
CREATE TABLE score (StuID int, Score float);
CREATE TABLE test_tab (f1 int, f2 long);
sqlite> select score.*,test_tab.f2 from score, test_tab where score.StuID=test_tab.f1;
1|90.5|120
sqlite> insert into test_tab values(2,120);
(4) Java中操作 SQLite
下载和使用 SqliteJDBC，下载地址：
http://www.zentus.com/sqlitejdbc/
示例代码：
package sqlitedb;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import business.BusinessLogic;
import common.ConfFiles;
/**
* Operations on sqlite database.
* Attention: Sqlite is coded by UTF-8 defaultly.
* @author zkl
*/
public class DBwrapper {
public static String SQLITEDBPATH;
static {
System.out.println("-------------------------------------");
System.out.println("Read client conf file ...");
String fileServerConf = ConfFiles.client_db_conf_file;
try {
FileReader fr = new FileReader(fileServerConf);
BufferedReader br = new BufferedReader(fr);
String line;
while ((line = br.readLine()) != null) {
if(line.trim().startsWith("#"))
continue;
int start = line.indexOf("=");
if( start < 0)
continue;
int len = line.length();
String name = line.substring(0, start);
String value = line.substring(start+1, len);
if (name.equalsIgnoreCase("dbpath")) {
SQLITEDBPATH = value;
}
System.out.println(name + " " + value);
}
} catch (IOException ioe) {
System.out.println("Read congig file error! " + ioe.getMessage());
}
System.out.println("Read client conf file finished.");
System.out.println("-------------------------------------");
}
private String connStr;
private Connection conn;
private Statement stat;
private ResultSet rs;
private String sqlStr;
private int affectedRows; /* update sql operation */
public DBwrapper()
{
connStr = "jdbc:sqlite:" + SQLITEDBPATH;
}
public DBwrapper(String dbPath)
{
connStr = "jdbc:sqlite:" + dbPath;
}
/**
* Connect to sqlite database.
* @return
* true -- sucessful.
* false -- failed.
*/
public boolean open()
{
try {
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection(connStr);
stat = conn.createStatement();
System.out.println("Connect sqlite database successful!");
return true;
} catch (SQLException ex) {
System.out.println("Connect sqlite database failed!");
System.out.println("Exception: Code - " + ex.getErrorCode()
+ ", Message - " + ex.getMessage());
return false;
} catch (ClassNotFoundException ex) {
System.out.println("Connect sqlite database failed!");
System.out.println("Exception: Message - " + ex.getMessage());
return false;
} catch (Exception ex) {
System.out.println("Connect sqlite database failed!");
System.out.println("Exception: Message - " + ex.getMessage());
return false;
}
}
/**
* Close the connection to sqlite database.
* @return
* true -- sucessful.
* false -- failed.
*/
public boolean close() {
try {
stat.close();
conn.close();
System.out.println("Close connection successful!");
return true;
} catch (SQLException ex) {
System.out.println("Close connection failed!");
System.out.println("Exception: Code - " + ex.getErrorCode()
+ ", Message - " + ex.getMessage());
return false;
} catch (Exception ex) {
System.out.println("Execute update sql failed!");
System.out.println("Exception: Message - " + ex.getMessage());
return false;
}
}
public void setSQL(String sqlStr)
{
this.sqlStr = sqlStr;
}
/**
* Execute update SQL.
* @return
* true -- sucessful.
* false -- failed.
*/
public boolean execUpdateSQL()
{
try {
stat.executeUpdate(sqlStr);
affectedRows = stat.getUpdateCount(); //affected rows
conn.commit();
System.out.println("Execute update sql successful!");
return true;
} catch (SQLException ex) {
System.out.println("Execute update sql failed!");
System.out.println("Exception: Code - " + ex.getErrorCode()
+ ", Message - " + ex.getMessage());
return false;
} catch (Exception ex) {
System.out.println("Execute update sql failed!");
System.out.println("Exception: Message - " + ex.getMessage());
return false;
}
}
/**
* Execute select SQL.
* @return
* true -- sucessful.
* false -- failed.
*/
public boolean execQuerySQL()
{
if(sqlStr.isEmpty() || sqlStr == null) {
System.out.println("SQL statement could not be null.");
return false;
}
try {
rs = stat.executeQuery(sqlStr);
System.out.println("Execute select sql successful!");
return true;
} catch (SQLException ex) {
System.out.println("Execute select sql failed!");
System.out.println("Exception: Code - " + ex.getErrorCode()
+ ", Message - " + ex.getMessage());
return false;
} catch (Exception ex) {
System.out.println("Execute select sql failed!");
System.out.println("Exception: Message - " + ex.getMessage());
return false;
}
}
public boolean commit() {
try {
conn.commit();
return true;
} catch (SQLException ex) {
System.out.println("Commit failed!");
System.out.println("Exception: Code - " + ex.getErrorCode()
+ ", Message - " + ex.getMessage());
return false;
}
}
public Connection getConnection() {
return conn;
}
public Statement getStatement() {
return stat;
}
public ResultSet getResultSet() {
return rs;
}
public int getAffectedRows() {
return affectedRows;
}
public boolean setAutoCommit(boolean autoCommit) {
try {
conn.setAutoCommit(autoCommit);
return true;
} catch (SQLException ex) {
System.out.println("Commit failed!");
System.out.println("Exception: Code - " + ex.getErrorCode()
+ ", Message - " + ex.getMessage());
return false;
}
}
/**
*
* @param sqlStr. SQL statement.
* @param op. operation, select, insert, update, delete
* @return
*/
public boolean execSQL(String sqlStr, String op)
{
if (!(op.equalsIgnoreCase("select") || op.equalsIgnoreCase("insert")
|| op.equalsIgnoreCase("update") || op
.equalsIgnoreCase("delete"))) {
System.out.println("Operations of SQL only in 'select, insert, update, delete'.");
return false;
}
this.sqlStr = sqlStr;
if (op.equalsIgnoreCase("select")) {
return execQuerySQL();
} else {
return execUpdateSQL();
}
}
/**
* Execute multiple SQL statements, batch job.
* @return
*/
public boolean execBatch()
{
/*
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);
*/
return false;
}
public static void main(String[] args) {
String sqlStr = "";
DBwrapper dbop = new DBwrapper();
if (!dbop.open())
return;
Connection conn = dbop.getConnection();
/*
// drop table
sqlStr = "drop table if exists people";
dbop.setSQL(sqlStr);
dbop.setAutoCommit(false); // must set auto commit to "false"
if (!dbop.execUpdateSQL())
return;
dbop.setAutoCommit(true);
// create table
sqlStr = "create table people (name primaey key, age int)";
dbop.setSQL(sqlStr);
dbop.setAutoCommit(false);
if (!dbop.execUpdateSQL())
return;
dbop.setAutoCommit(true);
*/
/*
// insert into table, batch job
int updateCount = 0;
try {
PreparedStatement prep = conn
.prepareStatement("insert into people values (?, ?);");
String name = "zhankunlin";
int age = 24;
for(int i=0; i<5; i++) {
prep.setString(1, name + i);
prep.setInt(2, age+1);
prep.addBatch();
}
dbop.setAutoCommit(false); // must set auto commit to "false"
prep.executeBatch();
dbop.commit(); // you must commit
updateCount = prep.getUpdateCount(); // ?
} catch (SQLException ex) {
System.out.println("Execute select sql failed!");
System.out.println("Exception: Code - " + ex.getErrorCode()
+ ", Message - " + ex.getMessage());
}
System.out.println(" Affected rows: " + updateCount );
// insert one line into table
sqlStr = "insert into people (name, age) values ('zhaoyanhong', 25)";
dbop.setSQL(sqlStr);
//dbop.setAutoCommit(false);
if (!dbop.execUpdateSQL())
return;
dbop.commit();
updateCount = dbop.getAffectedRows();
System.out.println(" Inserted rows: " + updateCount );
//dbop.setAutoCommit(true);
// insert one line into table
sqlStr = "update people set age = '24' where age = '25'";
dbop.setSQL(sqlStr);
//dbop.setAutoCommit(false);
if (!dbop.execUpdateSQL())
return;
dbop.commit();
updateCount = dbop.getAffectedRows();
System.out.println(" Updated rows: " + updateCount );
//dbop.setAutoCommit(true);
*/
/*
sqlStr = "insert into GoodsUnit(GoodsUnit_ID, GoodsUnit) values (88, '好')";
dbop.setSQL(sqlStr);
dbop.setAutoCommit(false);
if (!dbop.execUpdateSQL())
return;
dbop.commit();
*/
// query table
sqlStr = "select GoodsUnit from GoodsUnit";
dbop.setSQL(sqlStr);
if (!dbop.execQuerySQL())
return;
ResultSet rs = dbop.getResultSet();
int resultsCount = 0;
try {
while (rs.next()) {
System.out.println(rs.getString("GoodsUnit"));
++resultsCount;
}
rs.close();
} catch (SQLException ex) {
System.out.println("Execute select sql failed!");
System.out.println("Exception: Code - " + ex.getErrorCode()
+ ", Message - " + ex.getMessage());
} catch (Exception ex) {
System.out.println("Execute select sql failed!");
System.out.println("Exception: Message - " + ex.getMessage());
}
System.out.println("Line number: " + resultsCount);
sqlStr = "select TaxCategory_Name from TaxCategory";
dbop.setSQL(sqlStr);
if (!dbop.execQuerySQL())
return;
rs = dbop.getResultSet();
resultsCount = 0;
try {
while (rs.next()) {
System.out.println(rs.getString("TaxCategory_Name"));
++resultsCount;
}
rs.close();
} catch (SQLException ex) {
System.out.println("Execute select sql failed!");
System.out.println("Exception: Code - " + ex.getErrorCode()
+ ", Message - " + ex.getMessage());
} catch (Exception ex) {
System.out.println("Execute select sql failed!");
System.out.println("Exception: Message - " + ex.getMessage());
}
System.out.println("Line number: " + resultsCount);
// 查询客户端当天已打印的发票
String invoiceStatus = BusinessLogic.INVOICE_STATUS_PRINTED;
sqlStr = "select Invoice_ID, InvoiceStatus, TerminalDevice_ID, Taxpayer_ID, Invoice_ActualReceiveMoney, Invoice_DateTime from Invoice ";
sqlStr += " where InvoiceStatus = '" + invoiceStatus +"' ";
sqlStr += " and date(Invoice_DateTime) = date('now') ";
dbop.setSQL(sqlStr);
if (!dbop.execQuerySQL())
return;
rs = dbop.getResultSet();
resultsCount = 0;
String invoiceInfo = "";
try {
while (rs.next()) {
invoiceInfo += rs.getString("TerminalDevice_ID") +"|";
//invoiceInfo += rs.getString("InvoiceStatus") +"|";
invoiceInfo += rs.getString("Invoice_ID") +"|";
invoiceInfo +=rs.getString("Taxpayer_ID") +"|";
invoiceInfo +=rs.getString("Invoice_ActualReceiveMoney") +"|";
invoiceInfo +=rs.getString("Invoice_DateTime") +"\n";
++resultsCount;
}
rs.close();
} catch (SQLException ex) {
System.out.println("Execute select sql failed!");
System.out.println("Exception: Code - " + ex.getErrorCode()
+ ", Message - " + ex.getMessage());
} catch (Exception ex) {
System.out.println("Execute select sql failed!");
System.out.println("Exception: Message - " + ex.getMessage());
}
System.out.println("Line number: " + resultsCount);
System.out.print(invoiceInfo);
// 查询客户端当天打印的发票数量和交易总额
invoiceStatus = BusinessLogic.INVOICE_STATUS_PRINTED;
sqlStr = "select count(Invoice_ID) as invoiceCount, sum(Invoice_ActualReceiveMoney) as totalMoney from Invoice ";
sqlStr += " where InvoiceStatus = '" + invoiceStatus +"' ";
sqlStr += " and date(Invoice_DateTime) = date('now') ";
dbop.setSQL(sqlStr);
if (!dbop.execQuerySQL())
return;
rs = dbop.getResultSet();
resultsCount = 0;
int invoiceCount = 0;
float totalMoney = 0;
try {
while (rs.next()) {
invoiceCount = rs.getInt("invoiceCount");
totalMoney = rs.getFloat("totalMoney");
++resultsCount;
}
rs.close();
} catch (SQLException ex) {
System.out.println("Execute select sql failed!");
System.out.println("Exception: Code - " + ex.getErrorCode()
+ ", Message - " + ex.getMessage());
} catch (Exception ex) {
System.out.println("Execute select sql failed!");
System.out.println("Exception: Message - " + ex.getMessage());
}
System.out.println(invoiceCount);
System.out.println(totalMoney);
if(invoiceCount == 0) {
System.out.println("No invoice.");
}
if(invoiceCount == 4 && totalMoney == 8160) {
System.out.println("OK.");
}
System.out.println("Line number: " + resultsCount);
if (!dbop.close())
return;
}
}