Android Operation SQLite Database and experience

Write this article is mainly online on SQLite operation too many and too miscellaneous, many times can not be very good use of their projects, the structure is not clear, I wrote a suitable for just contact people to see the operation method.

The recent use of Android to save some data, a start with preferences, and later to save more things, found that with preferences obviously can not meet the requirements, and found that with this code will become a bit messy, So just started to learn to use the SQLite database, the first thought is not a database, and the usual MySQL AH or SQL Server are the same, are very simple, but then really is in use when only to find the difficulty one after another, but still in the constant groping in the continuous solution of difficulties, Later found that learning Android words when they really can not find the idea of the online some of the teaching video is also a good choice. Recommended a video here is good, I just follow this video to learn. http://www.tudou.com/programs/view/2qH5Am_3MsM/

Write about some of the preparations in the Android operations database.

First, with the ADB environment variables, because each time to the ADB directory down to start is too troublesome, the following is the specific steps, of course, you can also the other files, I am accustomed to change this, can be changed after the source to make it effective.

1, sudo gedit /etc/profile
2. Add the following two sentences to the file opened above
Export android_home=/home/sdk file path
export path= $PATH: $ANDROID _home/platform-tools

3, restart the computer, done!!

After the ADB is well, we'd better give the access to the database in the phone, generally in the/data/data/package name/database inside, with the ADB shell into the post-su to get the root rights of the phone, and then give permission chmod.

To read the database file, you use the command sqlite3 the database file, where the database can be run directly in the ADB shell sqlite3, but I do not follow the Internet to open the Sqlite3 database in the ADB shell, said the command was not found, I should pass the files are transmitted, No way, only in the Ddms file explore in Eclipse, the database files are everywhere and then run in the Linux terminal Sqlite3 database to see.

Also note that when writing SQL statements, it is important to note that the from and quotation marks in the "select * from" +table_name are left with spaces, otherwise they are joined together.

The following has a knowledge to tell, SQLite, delete and other operations are very simple, the trouble is the query operation, generally borrow the cursor to save the query data, at the beginning I did not pay much attention to this is a pointer type, pointing to the data in the database, And I started to write the database closed operation in front of the cursor operation, that is, the database is closed before the cursor object to operate, so that the cursor is caused by the empty pointer, it is doomed to a cup with a long time ...

Here are some examples of SQLite database operations, so that some people who are still confused some help, but also help to review later.

Sqlitehelper.java (Database helper Class)

public class Sqlitehelper extends sqliteopenhelper{private static final String database_name= "fpp.db";p rivate static Final int database_version=17;//changes the version after the database will recreate the private static final String table_name= "test";/** * sub-class in Sqliteopenhelper You must have this constructor * @param context's current Activity * @param name of the name table (not the name of the database, this class is used to manipulate the database) * @param facto Ry is used to return a child of the cursor when querying a database, passing a null value * @param version of the current database, integer and incremented number */public sqlitedata (Context Contex     T) {Super (context, database_name, NULL, database_version);//Inherit parent//TODO auto-generated constructor stub }/** * This function is executed the first time the database is created, only if it calls Getreadabledatebase () * or getwrittleabledatebase () and is the first to create the database, the function is executed */P ublic void OnCreate (Sqlitedatabase db) {//TODO auto-generated method stub String sql = "CREATE TAB LE "+ table_name +" ("+" id integer, "+" Nid VARCHAR (one-by-one), "+" Sid CHAR (1), "+" type Integer, "+" Stime DATETIME, "       + "Locate_main varchar", "+" locate_detail varchar, "+" state INTEGER "+") ";        Db.execsql (SQL);    LOG.E ("Create", "Database Creation succeeded"); The}/*** database update function executes this function when the database is updated */public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {String sq     L = "DROP TABLE IF EXISTS" + table_name;    Db.execsql (SQL);        This.oncreate (DB);        TODO auto-generated Method Stub System.out.println ("Database has been updated"); /** * Add Update database Here is the action to be performed */}}

Myoperator.java (Database Operations Class)

public class Myoperator {private static final String table_name = "TEST";//The name of the data table to manipulate private sqlitedatabase db=null;//database Operation//Constructor public Myoperator (Sqlitedatabase db) {this.db=db;} Insert Operation//public void Insert (int id,string nid,string sid,int type,//string stime,string etime,string desc,string Locate _main,string locate_detail,int state)//{//string sql = "INSERT into" + table_name + "(id,nid,sid,type,stime,etime,desc,l ocate_main,locate_detail,state) "//+" VALUES (?,?,?,?,?,?,?,?,?,?) "; /object args[]=new Object[]{id,nid,sid,type,stime,etime,desc,locate_main,locate_detail,state};//this.db.execsql ( SQL, args);//this.db.close ();//}//Insert overloaded operation public void insert (int id,int state) {String sql = "INSERT INTO" + table_name + " (id,state) "+" VALUES (?,?) "; O Bject args[]=new object[]{id,state};this.db.execsql (sql, args); This.db.close ();} Update operation public void Update (Int. id,int state) {String sql = "UPDATE" + table_name + "SET state=? WHERE id=? "; O Bject args[]=new object[]{state,id};this.db.execsql (sql, args); This.db.close ();} Delete operation, delete public void delete (int id) {String sql = ' DELETE FROM ' + table_name + "WHERE id=?"; O Bject args[]=new object[]{id};this.db.execsql (sql, args); This.db.close ();} A query operation that queries all records in a table returns a list of public list<string> find () {list<string> all = new arraylist<string> ();// At this point just stringstring sql = "SELECT * from" + table_name; Cursor result = this.db.rawQuery (sql, NULL); Executes the query statement for (Result.movetofirst ();! Result.isafterlast (); Result.movetonext ())//query data in a circular way {All.add (result.getint (0) + "," +result.getstring (1) + "," + Result.getstring (2) + "," +result.getint (3) + "," +result.getstring (4) + "," +result.getstring (5) + "," +result.getstring ( 6) + "," +result.getstring (7) + "," +result.getstring (8)); This.db.close (); return all;} Query operator overload function, returns the list of specified IDs public int getstatebyid (int id) {int num=-1;//Error state -1list<string> all = new arraylist< String> ();//At this time just stringstring sql = "Select state from" + table_name + "where id=?"; String args[] = new string[]{string.valueof (ID)}; Cursor result = This. db.rawquery (sql, args); for (Result.movetofirst ();! Result.isafterlast (); Result.movetonext ()) {num=result.getint (0);} LOG.E ("Database", "Picture status State" + string.valueof (num)); This.db.close (); return num;} Determines whether the ID of the inserted data already exists in the database. public boolean check_same (int id) {String sql= ' select ID from ' + table_name + ' WHERE id =? '; String args[] =new string[]{string.valueof (ID)}; Cursor result=this.db.rawquery (Sql,args); LOG.E ("Database", "The SQL has been Excuate"); LOG.E ("Database", "the Hang Count" + string.valueof (Result.getcount ())), if (Result.getcount () ==0)//Determines whether the resulting return data is empty { LOG.E ("Database", "return false and not exist the same result" + string.valueof (Result.getcount ())); This.db.close (); return false;} ELSE{LOG.E ("Database", "return true and exist the same result" + string.valueof (Result.getcount ())); This.db.close (); return true;}}}

A random activity in a project (activity class, Operation Open database already inside)

public class Mainactivity extends Activity {private static linkedlist<map<string, object>> Mlistitems; Private Pulltorefreshlistview pulltorefreshlistview;private Problemcontroller Problemcontroller =    Problemcontroller.getinstance ();p rivate sqliteopenhelper helper =null;private myoperator mytab=null; /** called when the activity is first created.        */@Override public void OnCreate (Bundle savedinstancestate) {super.oncreate (savedinstancestate);        Setcontentview (R.layout.activity_main);    This.helper=new Sqlitedata (this);//Database Operation Auxiliary class Setpulltorefreshview ();    } @Override public boolean oncreateoptionsmenu (Menu _menu) {super.oncreateoptionsmenu (_menu);    _menu.add (0, 0, 0, "set");    return true; } @Override public boolean onoptionsitemselected (MenuItem _item) {switch (_item.getitemid ()) {case 0:{intent    Intent = new Intent (Getapplicationcontext (), settingsactivity.class); startactivity (intent); break;}} return true;   } private class Getdatatask extends Asynctask<void, Void, string[]> {@Override protected St Ring[] Doinbackground (Void ... params) {if (Listtoshowdata (Problemcontroller.getnewproblems ())) {}els        e {Message message = new Message (); handler.sendmessage (message);        } return mstrings; } @Override protected void OnPostExecute (string[] result) {Pulltorefreshlistview.onrefreshcomple            Te ();        Super.onpostexecute (result); }} private string[] mstrings = {};/** * @param _newslist the list of messages to be displayed */private Boolean listtoshowdata (linkedlist<p Roblem> _problems) {if (_problems! = null) {mlistitems.clear (); for (Problem news: _problems) {//Insert data into database for initialization// A duplicate operation is required here, and if the ID of the data is already in the database there is no need to insert the data Mytab = new Myoperator (Helper.getwritabledatabase ()); LOG.E ("Database", "Start check if ID exists and insert the Id,the ID is" +string.valueof (News.getid ())); if (!mytab.check_sa Me (News.getid ())) {LOG.E ("datAbase "," The ID is not exist,insert the new ID now ... "); mytab = new Myoperator (Helper.getwritabledatabase ()); Mytab.inser T (News.getid (), 1); LOG.E ("Database", "Insert Finished");} map<string, object> tmpmap = new hashmap<string, object> ();//Maptmpmap.put to store log names ("id", News.getid ()); Tmpmap.put ("Describe", "module:" + news.getsid () + "malfunctioning!"); Tmpmap.put ("Time", News.getstime ()); Tmpmap.put ("img", r.drawable.icon_important); LOG.E ("Database", "Start Read database");//Read the databases to determine the status of this event to display the corresponding icon, 1 for the default status question mark, 2 to see, 3 to indicate that the event has been completed tick Mytab = new Myoperator ( Helper.getwritabledatabase ()), int state = Mytab.getstatebyid (News.getid ()), switch (state), Case 1:tmpmap.put ("State_ IMG ", r.drawable.icon_question); Break;case 2:tmpmap.put (" state_img ", r.drawable.icon_process); Break;case 3: Tmpmap.put ("State_img", R.drawable.icon_correct); Break;default:tmpmap.put ("State_img", R.drawable.icon_correct);} Mlistitems.add (TMPMAP); LOG.E (News.tostring (), news.tostring ());} return true;} else {return false;}} /** * @param to the drop-down refresh control intoRow set */private void Setpulltorefreshview () {mlistitems = new linkedlist<map<string,object>> (); Pulltorefreshlistview = (Pulltorefreshlistview) Findviewbyid (R.ID.PULLTOREFRESHLISTVIEW1); Pulltorefreshlistview.setonrefreshlistener (New Onrefreshlistener () {public void Onrefresh () {New Getdatatask () . Execute ();//Pull Data Thread Open}});p Ulltorefreshlistview.setonitemclicklistener (new Onitemclicklistener () {public void Onitemclick (adapterview<?> arg0, View arg1, final int arg2,long arg3) {log.e ("pull", string.valueof (arg2)); Sharedata.showproblem = Problemcontroller.getoldproblems (). get (arg2-1); Intent Intent = new Intent ( Getapplicationcontext (), detailsactivity.class);//Set a new picture, now with the database method, so this operation is not required, then unified read the picture//imageview tempimage= ( ImageView) Arg1.findviewbyid (R.ID.IMAGEVIEW2);//tempimage.setimageresource (r.drawable.icon_correct);//Put the state into the database, Determine the picture status, if 1 is not visited, change to 2mytab = new Myoperator (Helper.getwritabledatabase ()); if (Mytab.getstatebyid ( ShareData.showProblem.getID ()) ==1) {MYTAB = new Myoperator (Helper.getwritabledatabase ()); Mytab.update (ShareData.showProblem.getID (), 2);} Pass the fault ID to the options interface to determine which page selected the option. int Id=sharedata.showproblem.getid (); Bundle Bd=new Bundle (); Bd.putint ("id", id), Intent.putextra ("id", BD); startactivity (intent);//passed to another interface}}); Pulltorefreshlistview.setonitemlongclicklistener (New Onitemlongclicklistener () {public Boolean Onitemlongclick ( Adapterview<?> arg0, View arg1,int arg2, long Arg3) {log.e ("Pulllong", String.valueof (ARG2)); return true;}); Simpleadapter adapter = new Simpleadapter (Getapplicationcontext (), Mlistitems, R.layout.layout_listitem, new String[]{ "id", "img", "describe", "time", "state_img"}, New Int[]{r.id.title_tv, R.id.imageview1, R.id.content_tv, R.ID.DATE_TV,        R.ID.IMAGEVIEW2});        Pulltorefreshlistview.setadapter (adapter); }private Handler Handler = new Handler () {@Overridepublic void handlemessage (Message message) {Toast.maketext ( Getapplicationcontext (), "There is a problem with the network condition!", Toast.length_long). Show ();}};