Basic use of SQLite

One: basic Operation1 Inheritance Sqliteopenhelper

 Public classUsersqliteopenhelperextendsSqliteopenhelper {Private Static Final intVersionno = 1;  PublicUsersqliteopenhelper (Context context) {Super(Context, "user.db",NULL, Versionno); } @Override Public voidonCreate (Sqlitedatabase db) {Log. I ("", "SQLite onCreate" ); Db.execsql ("CREATE table User (ID Integer primary key autoincrement, name varchar (20))"); } @Override Public voidOnupgrade (Sqlitedatabase db,intOldversion,intnewversion) {Log. I ("", "SQLite onupgrade" ); Db.execsql (Insert into user values (?),Newobject[]{"Version" +Versionno}); } }

 from the code we can see that usersqliteopenhelper rewritten theSqliteopenhelper's OnCreate and Onupgrade two methodsDescription:onCreate:When a database is built, it is generally executed to create a database statement Onupgrade: When the version number ( Versionno) is incremented when you execute this method, which is typically used to change the structure of the table, add or remove * The first time you create a database, execute OnCreate, do not execute onupgrade; After you change the version number, execute the Onupgrade and do not perform the oncreate operation instructions: when Private Static Final int Versionno= 1 o'clock results are as follows when Private Static Final int Versionno= 2 o'clock results are as follows2. Create a DAO file to manipulate the SQLite database

 Public classUserdao {PrivateUsersqliteopenhelper Tsoh;  PublicUserdao (Context context) {Tsoh=NewUsersqliteopenhelper (context); }           Public voidInsert (String name) {Sqlitedatabase db=tsoh.getwritabledatabase (); Db. Execsql (Insert into user values (?),Newobject[]{name});     Db.close (); }           Public BooleanFind (String name) {Sqlitedatabase db=tsoh.getreadabledatabase (); Cursor Cursor= Db.rawquery ("SELECT * from user where name =?",Newstring[]{name}); Booleanresult =Cursor.movetonext ();           Cursor.close ();            Db.close (); returnresult; }           Public voidUpdate (string name, String newName) {Sqlitedatabase db=tsoh.getwritabledatabase (); Db. Execsql ("Update user set name =?" WHERE name =? ",Newobject[]{newname, name});     Db.close (); }           Public voidDelete (String name) {Sqlitedatabase db=tsoh.getwritabledatabase (); Db. Execsql ("Delete from user where name =?",Newobject[]{name});     Db.close (); }           PublicList<user>FindAll () {List<User> users =NewArraylist<user>(); Sqlitedatabase DB=tsoh.getreadabledatabase (); Cursor Cursor= Db.rawquery ("SELECT * from User",NULL);  while(Cursor.movetonext ()) {intid = cursor.getint (cursor.getcolumnindex ("id") )); String name= Cursor.getstring (Cursor.getcolumnindex ("name" )); User User=NewUser (ID, name);           Users.add (user);           } cursor.close ();            Db.close (); returnusers; } }

 definition of the user class

 Public classUser {Private intID; PrivateString name;  PublicUser () {Super(); }           PublicUser (intID, String name) {            Super();  This. ID =ID;  This. Name =name; }           Public intgetId () {returnID; }      Public voidSetId (intID) { This. ID =ID; }      PublicString GetName () {returnname; }      Public voidsetName (String name) { This. Name =name; } @Override PublicString toString () {return"INFO:" + id+ "," +name; }}

the code is simple and does not explain too much. It is important to remember to close the cursor and database each time you operate the database, freeing up resources. Project-level code should be manipulated using try catch Finally, in order to demonstrate a simple operation. Here is still the use of splicing string to execute the SQL statement, in fact, Android also provides us with encapsulated API (and eventually the parameters are stitched into strings, but for the caller, the new API is straightforward, the user does not consider the single quotation mark double quotes trouble), here do not introduce too much.  3. Calling code    

@Overrideprotected voidonCreate (Bundle savedinstancestate) {Super. OnCreate (savedinstancestate);               Setcontentview (r.layout. Activity_main); Userdao Testdao=NewUserdao ( This); Testdao.insert ("Name1"); Testdao.insert ("Name2"); Testdao.insert ("Name3"); Testdao.update ("Name1", "Name4"); Booleanresult = Testdao.find ("name2" ); Testdao.delete ("Name3"); List<User> users =Testdao.findall ();  for(User user:users) {Log. I ("Mainactivity", user.tostring ()); }    }

Basic operation is complete.Second: Recommended SQLite interface Tool: SQLite expert professionalTwo days ago a colleague asked me, SQLite in the integer can store the maximum value is how much, then I do not know, so on the spot test, test results: 9223372036854775808 ~ 9223372036854775807 is -2^ 63 to 2^63-1 This is the result of my experiment with the SQLite tool.

Integer: Used to store an integer, which can be stored using 1,2,3,4,6,8 bytes according to size (network transcription, from the results of my experiment, the upper limit is 8 bytes of integer)

Basic use of SQLite