Android app performance optimization using Sqlitestatement to optimize SQLite operations

Usually in the operation of the Android database, are used by the Execsql method. I stumbled upon sqlitestatement this class today. Reminds me of doing Java Web Development Prestatement This class when writing JDBC code. Prestatement not only improves efficiency, but also solves the problem of SQL injection. Will the sqlitestatement in Android improve some efficiency?

A simple test was written to compare the time required to insert 1000 data into the Executeinsert method of Execsql and sqlitestatement. No use of things.

Create a new database and users table:

 Public classDBHelperextendsSqliteopenhelper {Private Static FinalString db_name = "UserDB"; Private Static Final intDb_version = 1;  PublicDBHelper (Context context) {Super(Context, Db_name,NULL, db_version); } @Override Public voidonCreate (Sqlitedatabase db) {StringBuffer SQL=NewStringBuffer (); Sql.append ("CREATE TABLE Users"); Sql.append ("(_id int PRIMARY key,name varchar,gender int,age int,phonenumber varchar,address varchar)");    Db.execsql (Sql.tostring ()); } @Override Public voidOnupgrade (Sqlitedatabase db,intOldversion,intnewversion) {    }}

Then use the Sqlitedatabase Execsql method and the Sqlitestatement Executeinsert method to enter the insertion, compare the time required for execution:

 Public classUserdao {PrivateDBHelper DBHelper; PrivateSqlitedatabase DB; PrivateStringBuffer Sql_insert; PrivateList<user>users;  PublicUserdao (Context context) { This. DBHelper =NewDBHelper (context);  This. db =dbhelper.getwritabledatabase (); Sql_insert=NewStringBuffer (); Sql_insert.append (INSERT into Users (name,gender,age,phonenumber,address)); Sql_insert.append ("VALUES (?,?,?,?,?)"); Users=NewArraylist<user>(); //test Data         for(inti = 0;i<1000;i++) {User User=NewUser ();            User.setid (i); User.setname ("Name" +i); User.setgender (0);            User.setage (User.getrandomage ()); User.setphonenumber ("13800138000"); User.setaddress ("Guangdong ShenZhen No." +i);        Users.add (user); }    }    /*** Insert data using Sqlitedatabase's Execsql method *@returnreturns the time that is required to execute*/     Public LongInsertexecsql () {Longstart=System.currenttimemillis ();  for(User user:users) {object[] Bindargs={user.getname (), User.getgender (), User.getage (), User.getphonenumber (), user.getaddress ()};        Db.execsql (Sql_insert.tostring (), Bindargs); }        LongEnd =System.currenttimemillis (); returnEnd-start; }    /*** Insert data using Sqlitestatement's Executeinsert method *@returnreturns the time that is required to execute*/     Public Longinsertstatement () {LongStart =System.currenttimemillis ();  for(User user:users) {sqlitestatement statement=db.compilestatement (sql_insert.tostring ()); Statement.bindstring (1, User.getname ()); Statement.bindlong (2, User.getgender ()); Statement.bindlong (3, User.getage ()); Statement.bindstring (4, User.getphonenumber ()); Statement.bindstring (5, User.getaddress ());        Statement.executeinsert (); }        LongEnd =System.currenttimemillis (); returnEnd-start; }}

The interface is about two buttons, each invoking a different insertion method, and the time required to execute is displayed on the button.

 Public classMainactivityextendsActivity {PrivateButton btn1; PrivateButton btn2; @Overrideprotected voidonCreate (Bundle savedinstancestate) {Super. OnCreate (savedinstancestate);        Setcontentview (R.layout.activity_main); FinalUserdao DAO =NewUserdao ( This); BTN1=(Button) Findviewbyid (R.ID.BTN1); BTN2=(Button) Findviewbyid (R.ID.BTN2); Btn1.setonclicklistener (NewOnclicklistener () {@Override Public voidOnClick (View v) {btn1.settext (string.valueof (Dao.insertexecsql ()));        }        }); Btn2.setonclicklistener (NewOnclicklistener () {@Override Public voidOnClick (View v) {btn2.settext (string.valueof (Dao.insertstatement ()));    }        }); }}

By several comparisons, it was found that inserting 1000 data, using the Executeinsert method of Sqlitestatement, was generally about 5 seconds faster than using Sqlitedatabase Execsql method. The gap is still very large.

It is important to note that none of the two methods above are open. When doing such a batch operation, opening things will certainly improve the efficiency to a great extent.

Db.begintransaction (); xxxx....db.settransactionsuccessful ();d b.endtransaction ();

Another thing to note is that when you bulk insert 1000 of data, you don't use an asynchronous class or a new thread. I found that the interface is obviously stalled. If you insert a larger amount of data, the response is stopped directly.

Therefore, in the case of data manipulation, if the amount of data is large, it is recommended to use an asynchronous class or open a new thread.

This article transferred from: http://liuzhichao.com/p/1664.html

Android app performance optimization using Sqlitestatement to optimize SQLite operations