How to learn and use SQLite in Android

SQLite Introduction

SQLite is a lightweight, embedded, and relational database. It is currently used in iPhone, Android, and other mobile phone systems. SQLite is portable, easy to use, small, efficient, and reliable. SQLite is embedded into applications that use it.ProgramThey share the same process space, rather than a separate process. From the external perspective, it is not like an RDBMS, but inside the process, it is complete, self-contained database engine.

In Android, when you need to operate the SQLite database, you need to get a sqliteopenhelper object, while sqliteopenhelper is an abstract class. You need to inherit this class and implement some methods in this class.

1. inherit from sqliteopenhelper and you will have the following two methods:

◆ Getreadabledatabase () Create or open a query database

◆ Getwritabledatabase () Create or open a writable Database

◆ They will return the sqlitedatabase object, and the user will perform subsequent operations through the obtained sqlitedatabase object

2. At the same time, the user can override the following callback functions and call back the following methods when operating the database:

◆ Oncreate (sqlitedatabase): This method is called when the database is created for the first time. Generally, we create a database table in this method.

◆ Onupgrade (sqlitedatabase, Int, INT): When the database needs to be modified, the android system will actively call this method. In general, we delete database tables and create new database tables in this method. Of course, whether other operations are required depends on the needs of the application.

◆ Onopen (sqlitedatabase): This is the callback function when the database is opened and is generally not used.

Note:

1. The following constructor must exist in the subclass of sqliteoepnhelper:

1. Public databasehelper (context, string name, cursorfactory factory,
2. Int version ){
3. // The constructor in the parent class must be called through Super
4. Super (context, name, factory, version );
5. }
6.  

For convenience, you can also create other constructors with two or three parameters.

2. The public void oncreate (sqlitedatabase dB) function is executed when getreadabledatabase () is called or getwritabledatabase () is called to create a database for the first time. In fact, it is the first time that the sqlitedatabse object is obtained, to call this method.

1. Public void oncreate (sqlitedatabase dB ){
2. System. Out. println ("Create a database ");
3. // Execsql function is used to execute SQL statements
4. Db.exe csql ("create table user (ID int, name varchar (20 ))");
5. }
6.  

When inserting a record into a database table, you must first include the data in a contentvalues and insert a key-value pair to the object. The key-value pair is the column name, the value is the value that you want to insert into this column. The value must be consistent with the data type in the database. Call the getwritabledatabase method of databasehelper to obtain the databasehelper object that can be written, and then insert records into it. Pay attention to the transmission of parameters that call the insert, update, or query method of the databasehelper object.

In addition, after the query method is executed, a cursor is returned. The cursor first points to the previous row of the first row in the record set. Therefore, you must first call cursor. next () move the cursor to the first row of the record set, and then obtain the data.

JavaCode

2. Public class sqliteactivity extends activity {
4. /** Called when the activity is first created .*/
6. Private button createbutton;
8. Private button insertbutton;
10. Private button updatebutton;
12. Private button updaterecordbutton;
14. Private button querybutton;
16. @ Override
18. Public void oncreate (bundle savedinstancestate ){
20. Super. oncreate (savedinstancestate );
22. Setcontentview (R. layout. Main );
24. Createbutton = (button) findviewbyid (R. Id. createdatabase );
26. Updatebutton = (button) findviewbyid (R. Id. updatedatabase );
28. Insertbutton = (button) findviewbyid (R. Id. insert );
30. Updaterecordbutton = (button) findviewbyid (R. Id. Update );
32. Querybutton = (button) findviewbyid (R. Id. query );
34. Createbutton. setonclicklistener (New createlistener ());
36. Updatebutton. setonclicklistener (New updatelistener ());
38. Insertbutton. setonclicklistener (New insertlistener ());
40. Updaterecordbutton. setonclicklistener (New updaterecordlistener ());
42. Querybutton. setonclicklistener (New querylistener ());
44. }
46. Class createlistener implements onclicklistener {
48. @ Override
50. Public void onclick (view v ){
52. // Create a databasehelper object
54. Databasehelper dbhelper = new databasehelper (sqliteactivity. This, "test_mars_db ");
56. // A database is created or opened only after the getreadabledatabase () method of the databasehelper object is called or the getwritabledatabase () method.
58. Sqlitedatabase DB = dbhelper. getreadabledatabase ();
60. }
62. }
64. Class updatelistener implements onclicklistener {
68. @ Override
70. Public void onclick (view v ){
72. Databasehelper dbhelper = new databasehelper (sqliteactivity. This, "test_mars_db", 2 );
74. Sqlitedatabase DB = dbhelper. getreadabledatabase ();
76. }
80. }
82. Class insertlistener implements onclicklistener {
86. @ Override
88. Public void onclick (view v ){
90. // Generate the contentvalues object
92. Contentvalues values = new contentvalues ();
94. // To insert a key-value pair to the object, the key-value pair is the column name and the value is the value to be inserted into the column. The value must be consistent with the data type in the database.
96. Values. Put ("ID", 1 );
98. Values. Put ("name", "zhangsan ");
100. Databasehelper dbhelper = new databasehelper (sqliteactivity. This, "test_mars_db", 2 );
102. Sqlitedatabase DB = dbhelper. getwritabledatabase ();
104. // Call the insert method to insert data to the database.
106. DB. insert ("user", null, values );
108. }
110. }
112. // The update operation is equivalent to executing the update statement in the SQL statement.
114. // Update table_name setXxcol=XxxWhereXxXxcol= XX...
116. Class updaterecordlistener implements onclicklistener {
120. @ Override
122. Public void onclick (view arg0 ){
124. // Todo auto-generated method stub
126. // Get a writable sqlitedatabase object
128. Databasehelper dbhelper = new databasehelper (sqliteactivity. This, "test_mars_db ");
130. Sqlitedatabase DB = dbhelper. getwritabledatabase ();
132. Contentvalues values = new contentvalues ();
134. Values. Put ("name", "zhangsanfeng ");
136. // The first parameter is the name of the table to be updated.
138. // The second parameter is a contentvaleus object.
140. // The third parameter is the WHERE clause.
142. DB. Update ("user", values ,"ID=? ", New string [] {" 1 "});
144. }
146. }
148. Class querylistener implements onclicklistener {
152. @ Override
154. Public void onclick (view v ){
156. System. Out. println ("AAA ------------------");
158. Log. D ("mydebug", "myfirstdebugmsg ");
162. Databasehelper dbhelper = new databasehelper (sqliteactivity. This, "test_mars_db ");
164. Sqlitedatabase DB = dbhelper. getreadabledatabase ();
166. Cursor cursor = dB. Query ("user", new string [] {"ID", "name "},"ID=? ", New string [] {" 1 "}, null );
168. While (cursor. movetonext ()){
170. String name = cursor. getstring (cursor. getcolumnindex ("name "));
172. System. Out. println ("query --->"+ Name );
174. }
176. }
178. }
182. }