API encapsulation for operating SQLite on Blackberry

 

API encapsulation for operating SQLite on Blackberry

 

Blackberry provides SQLite APIs, but it is still troublesome to use them directly. Here we write a small API encapsulation.

 

1. The core tool class dbutil provides operations on database tables.

 

Package dB; <br/> Import Java. util. vector; <br/> Import net. rim. device. API. database. cursor; <br/> Import net. rim. device. API. database. database; <br/> Import net. rim. device. API. database. databaseexception; <br/> Import net. rim. device. API. database. databasefactory; <br/> Import net. rim. device. API. database. databaseioexception; <br/> Import net. rim. device. API. database. row; <br/> Import net. rim. device. API. database. Statement; <br/> Import net. rim. device. API. io. uri; <br/> public class dbutil {</P> <p> Public static dbutil util = new dbutil (); </P> <p> private dbutil () {</P> <p >}</P> <p> Public static dbutil getinstance () {<br/> return util; <br/>}</P> <p> Public vector query (string SQL, rowmapper) throws dbexception {<br/> return this. query (SQL, new object [0], rowmapper); <br/>}</P> <p> Public vector query (strin G SQL, object [] Params, rowmapper) throws dbexception {<br/> vector list = new vector (); <br/> database DB = This. getdatabase (); <br/> statement stmt = NULL; <br/> cursor = NULL; </P> <p> try {<br/> stmt = dB. createstatement (SQL); <br/> stmt. prepare (); <br/> for (INT I = 0; I <Params. length; I ++) {<br/> stmt. BIND (I + 1, Params [I]. tostring (); <br/>}< br/> cursor = stmt. getcursor (); <br/> W Hile (cursor. next () {<br/> row = cursor. getrow (); <br/> list. addelement (rowmapper. maprow (ROW); <br/>}< br/> stmt.exe cute (); <br/>} catch (exception ex) {<br/> throw new dbexception (ex. getmessage (); <br/>}finally {<br/> This. close (cursor); <br/> This. close (stmt); <br/> This. close (db); <br/>}< br/> return list; <br/>}</P> <p> Public vector query (string SQL, parameterbinder binder, rowmapper rowmapp Er) throws dbexception {<br/> vector list = new vector (); <br/> database DB = This. getdatabase (); <br/> statement stmt = NULL; <br/> cursor = NULL; </P> <p> try {<br/> stmt = dB. createstatement (SQL); <br/> stmt. prepare (); <br/> binder. BIND (stmt); <br/> cursor = stmt. getcursor (); <br/> while (cursor. next () {<br/> row = cursor. getrow (); <br/> list. addelement (rowmapper. maprow (ROW); <br/>}< br/> stmt. Execute (); <br/>}catch (exception ex) {<br/> throw new dbexception (ex. getmessage (); <br/>}finally {<br/> This. close (cursor); <br/> This. close (stmt); <br/> This. close (db); <br/>}< br/> return list; <br/>}</P> <p> Public void Update (string SQL) throws dbexception {<br/> This. update (SQL, new object [0]); <br/>}</P> <p> Public void Update (string SQL, object [] Params) throws dbexception {<br/> database DB = This. getdatabase (); <br/> statement stmt = NULL; <br/> try {<br/> dB. begintransaction (); <br/> stmt = dB. createstatement (SQL); <br/> stmt. prepare (); <br/> for (INT I = 0; I <Params. length; I ++) {<br/> stmt. BIND (I + 1, Params [I]. tostring (); <br/>}< br/> stmt.exe cute (); <br/> dB. committransaction (); <br/>} catch (exception ex) {<br/> throw new dbexception (ex. getmessage (); <br/>}finally {<br/> This. cl Ose (stmt); <br/> This. close (db); <br/>}</P> <p> Public void Update (string SQL, parameterbinder binder) throws dbexception {<br/> database DB = This. getdatabase (); <br/> statement stmt = NULL; <br/> try {<br/> dB. begintransaction (); <br/> stmt = dB. createstatement (SQL); <br/> stmt. prepare (); <br/> binder. BIND (stmt); <br/> stmt.exe cute (); <br/> dB. committransaction (); <br/>} catch (exception ex) {<br/> Throw new dbexception (ex. getmessage (); <br/>}finally {<br/> This. close (stmt); <br/> This. close (db); <br/>}</P> <p> private database getdatabase () throws dbexception {<br/> try {<br/> URI myuri = Uri. create ("file: // sdcard/databases/test. DB "); <br/> database DB = databasefactory. openorcreate (myuri); <br/> return dB; <br/>} catch (exception ex) {<br/> throw new dbexception (ex. getmessage (); <br />}< Br/>}</P> <p> private void close (Database dB) {<br/> try {<br/> If (DB! = NULL) {<br/> dB. close (); <br/>}< br/>} catch (databaseioexception ex) {<br/> ex. printstacktrace (); <br/>}< br/> private void close (statement stmt) {<br/> try {<br/> If (stmt! = NULL) {<br/> stmt. close (); <br/>}< br/>} catch (databaseexception ex) {<br/> ex. printstacktrace (); <br/>}< br/> private void close (cursor) {<br/> try {<br/> If (cursor! = NULL) {<br/> cursor. close (); <br/>}< br/>} catch (databaseexception ex) {<br/> ex. printstacktrace (); <br/>}< br/>}

 

2. dbexception is the exception type in database operations. Due to the limitations of BB, dbexception inherits the runtimeexception class, rather than the exception class.

Package dB; <br/> public class dbexception extends runtimeexception {</P> <p> Private Static final long serialversionuid = 1l; <br/> Public dbexception () {<br/> super (); <br/>}< br/> Public dbexception (string message) {<br/> super (Message ); <br/>}< br/>} 

 

3. The parameterbinder interface is used for parameterized execution of SQL statements, which is used to pass parameters.

Package dB; <br/> Import net. rim. device. API. database. statement; <br/> Public interface parameterbinder {</P> <p> void BIND (statement stmt) throws exception; </P> <p>} 

 

4. The rowmapper interface is used to convert each row of records.

Package dB; <br/> Import net. rim. device. API. database. row; <br/> Public interface rowmapper {</P> <p> Object maprow (Row row) throws exception; <br/>} 

 

5. Test class

Package dB; <br/> Import net. rim. device. API. database. row; <br/> Import net. rim. device. API. database. statement; <br/> public class test {<br/> Public static void test () throws exception {<br/> dbutil. getinstance (). update ("create table if not exists test ('id' long, 'col1' text, 'col2' text, 'col3' text )"); </P> <p> dbutil. getinstance (). update ("insert into test (ID, col1, col2, col3) values (?, ?, ?, ?) ", New object [] {long. tostring (system. currenttimemillis (), "A", "B", "C"}); <br/> dbutil. getinstance (). update ("insert into test (ID, col1, col2, col3) values (?, ?, ?, ?) ", New parameterbinder () {<br/> Public void BIND (statement stmt) throws exception {<br/> stmt. BIND (1, 1); <br/> stmt. BIND (2, "111"); <br/> stmt. BIND (3, "222"); <br/> stmt. BIND (4, "333"); <br/>}< br/>}); </P> <p> dbutil. getinstance (). query ("select * from test", new rowmapper () {<br/> Public object maprow (Row row) throws exception {<br/> system. out. println (row. getObject (0); <br/> return row. getObject (0); <B R/>}< br/>}); <br/> dbutil. getinstance (). Query ("select * from test where id =? ", <Br/> New parameterbinder () {<br/> Public void BIND (statement stmt) throws exception {<br/> stmt. BIND (1, 1); <br/>}< br/>}, <br/> New rowmapper () {<br/> Public object maprow (Row row) throws exception {<br/> system. out. println (row. getObject (0); <br/> return row. getObject (0); <br/>}< br/>}); <br/>}< br/>