使用SQLite3開發java應用程式

載SQLite資料庫的JDBC

這裡給出一個中文網站的URL:

http://www.sqlite.com.cn/Upfiles/source/sqlitejdbc-v033-nested.tgz

將下載到的包解壓後得到jar包 sqlitejdbc-v033-nested.jar 放到%JAVA_HOME%/lib
下，
並且將其添加到classpath系統內容變數中,我的classpath系統內容變數現在為:
.;%JAVA_HOME%/lib/sqlitejdbc-v033-nested.jar在你的代碼中引用這個驅動:   
Class.forName("org.sqlite.JDBC");
    Connection
conn =
DriverManager.getConnection("jdbc:sqlite:filename");//filename為你的SQLite資料名稱

    // ... use
the database ...
   
conn.close();
   
樣本程式如下： 

import java . sql . * ; import org . sqlite. JDBC; public class TestSQLite {     public static void main( String [ ] args)     {         try         {          //串連SQLite的JDBC          Class . forName ( "org.sqlite.JDBC" ) ;                    //建立一個資料庫名zieckey.db的串連，如果不存在就在目前的目錄下建立之          Connection conn = DriverManager . getConnection ( "jdbc:sqlite:zieckey.db" ) ;                    Statement stat = conn. createStatement ( ) ;                    stat. executeUpdate ( "create table tbl1(name varchar(20), salary int);" ) ; //建立一個表，兩列                    stat. executeUpdate ( "insert into tbl1 values('ZhangSan',8000);" ) ; //插入資料    stat. executeUpdate ( "insert into tbl1 values('LiSi',7800);" ) ;    stat. executeUpdate ( "insert into tbl1 values('WangWu',5800);" ) ;    stat. executeUpdate ( "insert into tbl1 values('ZhaoLiu',9100);" ) ;            ResultSet rs = stat. executeQuery ( "select * from tbl1;" ) ; //查詢資料          while ( rs. next ( ) ) { //將查詢到的資料列印出來              System . out. print ( "name = " + rs. getString ( "name" ) + " " ) ; //列屬性一              System . out. println ( "salary = " + rs. getString ( "salary" ) ) ; //列屬性二          }          rs. close ( ) ;          conn. close ( ) ; //結束資料庫的串連         }         catch ( Exception e )         {          e. printStackTrace ( ) ;         }     } }

編譯運行：   

E:/Coding/java/test>javac TestSQLite.java

E:/Coding/java/test>java TestSQLite
name =
ZhangSan      
salary = 8000
name =
LiSi      
salary = 7800
name =
WangWu      
salary = 5800
name =
ZhaoLiu      
salary = 9100  總結：本文介紹了嵌入式資料庫SQLite在Java中的應用，
通過建立表、插入資料、查詢等操作介紹了在Java中對資料庫的操縱。

SQLite3命令操作大全
========================================================>>

.資料庫、表的建立，記錄的添加、查詢、修改和刪除

F:/>sqlite3 database.db
sqlite> create table admin(username text,age
integer);
sqlite> insert into admin values('kuang',25);
sqlite> select * from admin;
sqlite> update admin set username='kk',age=24 where
username='kuang' and age=25;
sqlite> delete from admin where username='kk';

註：每條sql語句後必須以";"號結尾！

2.Sqlite系統命令

.bail
ON|OFF          
Stop after hitting an
error.  Default OFF
.databases            
List names and files of attached databases(查看目前掛的資料庫)
.dump ?TABLE?
...      Dump
the database in an SQL text format(以SQL格式輸出表結構)
.echo
ON|OFF          
Turn command echo on or off
.exit                  Exit
this program(退出程式)
.explain
ON|OFF        Turn
output mode suitable for EXPLAIN on or off.
.header(s)
ON|OFF      Turn
display of headers on or off
.help                  Show
this message(顯示協助資訊)
.import FILE
TABLE    
Import data from FILE into
TABLE(把檔案中的資料匯入到表中,各欄位用separator的值為分隔字元)
.indices
TABLE        
Show names of all indices on TABLE
.load FILE
?ENTRY?    
Load an extension library
.mode MODE
?TABLE?    
Set output mode where MODE is one of:(輸出格式)
                        
csv      Comma-separated
values(各欄位以逗號為分隔字元輸出)
                        
column   Left-aligned
columns.  (See
.width)(以.width設定的寬度顯示各欄位)
                        
html    
HTML <table> code(html表格格式輸出)
                        
insert   SQL insert statements
for TABLE(以insert SQL語句形式輸出)
                        
line    
One value per line(field = value的形式逐行輸出)
                        
list    
Values delimited by .separator string(各欄位以separator的值為分隔字元輸出)
                        
tabs    
Tab-separated values
                        
tcl      TCL
list elements
.nullvalue
STRING      Print
STRING in place of NULL values
.output
FILENAME      
Send output to FILENAME(設定把查詢輸出到檔案,後面的輸出結果都儲存到檔案中)
.output
stdout        
Send output to the screen(設定把查詢結果輸出到螢幕,預設)
.prompt MAIN CONTINUE  Replace
the standard prompts(修改提示符)
.quit                  Exit
this program(退出)
.read
FILENAME        
Execute SQL in FILENAME(執行檔案中的SQL語句)
.schema
?TABLE?        Show
the Create statements(以SQL格式輸出表結構)
.separator
STRING      Change
separator used by output mode and .import(修改分隔字元)
.show                  Show
the current values for various settings(顯示配置資訊)
.tables
?PATTERN?      List
names of tables matching a LIKE pattern(看看有建立了多少表)
.timeout
MS            Try
opening locked tables for MS milliseconds(逾時時間,單位:毫秒)
.width NUM NUM
...    
Set column widths for "column" mode(設定列寬)