Installation and Use of lightweight database SQLite

Lightweight database SQLite Installation and Use (1) SQLite introduction see Baidu Encyclopedia: http://baike.baidu.com/view/19310.htm Official Website: http://www.sqlite.org/(2) linux SQLite compilation and installation of sqlite-amalgamation-3070900.zip source code sqlite-autoconf-3070900.tar.gz source code + makefile and other sqlite-shell-linux-x86-3070900.zip command line tool extract sqlite-autoconf-3070900.tar.gz, enter the folder, execute the following command to complete the installation :. /configure -- prefix =/sdb1/development/sqlite3
Make & make install (3) Linux SQLite uses extract sqlite-autoconf-3070900.tar.gz, use shell tool to operate the database, very convenient. [Zkl @ gd47 sqlite_dev] $ sqlite3 test. dB (If NO, create the test. DB database file. If yes, open the file)
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ". Help" for instructions
Enter SQL statements terminated with ";"
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) operate SQLite in Java to download and use sqlitejdbc ,:
Http://www.zentus.com/sqlitejdbc/Sample Code:

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) {Conn STR = "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 again stat.exe cuteupdate (sqlstr); affectedrows = stat. getupdatecount (); // affected rowsconn. 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 cocould not be null. "); Return false;} Try {rs = stat.exe cutequery (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. revoke signorecase ("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" commandid prep.addbatch(commandid conn.setautocommit(false=*prep.exe cutebatch (); 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.exe cupdatesql () return; dbop. setautocommit (true); // create table sqlstr = "create table people (name primaey key, age INT)"; dbop. setsql (sqlstr); dbop. setautocommit (false); If (! Dbop.exe cupdatesql () return; dbop. setautocommit (true); * // insert into table, batch jobint 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.exe cutebatch (); dbop. commit (); // You must commitupdatecount = 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.exe cupdatesql () 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.exe cupdatesql () return; dbop. commit (); updatecount = dbop. getaffectedrows (); system. out. println ("updated rows:" + updatecount); // dbop. setautocommit (true); * // * sqlstr = "insert into goodsunit (goodsunit_id, goodsunit) values (88, 'hao')"; dbop. setsql (sqlstr); dbop. setautocommit (false); If (! Dbop.exe cupdatesql () return; dbop. Commit (); * // query tablesqlstr = "select goodsunit from goodsunit"; dbop. setsql (sqlstr); If (! Dbop.exe cquerysql () 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.exe cquerysql () 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); // query the invoice string invoicestatus = businesslogic that has been printed by the client on the current day. invoice_status_printed; sqlstr = "select invoice_id, invoicestatus, terminaldevice_id, taxpayer_id, role, invoice_datetime from invoice"; sqlstr + = "where invoicestatus = '" + invoicestatus + "'"; sqlstr + =" Nd date (invoice_datetime) = Date ('now ') "; dbop. setsql (sqlstr); If (! Dbop.exe cquerysql () 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. getst Ring ("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); // query the number of invoices and total transaction volume printed by the client on the current day. invoicestatus = businesslogic. invoice_status_printed; sqlstr = "select count (invoice_id) as invoicecount, sum (latency) as totalmoney from invoice"; sqlstr + = "where invoicestatus = '" + invoicestatus + "'"; sqlstr + = "And date (invoice_datetime) = Date ('now ')"; dbop. setsql (sqlstr); If (! Dbop.exe cquerysql () 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 ;}}