SQL Study Notes database topic (2): Development of SQL database under Android, Study Notes for android

SQL Study Notes database topic (2): Development of SQL database under Android, Study Notes for android

In Android development, databases are indispensable. In Android development, sqllite is a small embedded database. Today I will write two articles about database development in the Android development environment, the first section describes how to use and manage databases, add, delete, modify, query, and upgrade databases using the APIS provided by android.

I. First Generation of the database 1. Preparation of the javabean file here is an example of the Person class.

Package com. example. freedomsql. bean; import java. io. serializable;/*** @ ClassName: Person * @ author emerge _freedom (x_freedom_reddevil@126.com) * @ createddate 4:22:09 * @ Description: TODO */public class Person implements Serializable {private int id; private String name; private String number; // private String nickname; public Person (int id, String name, String number) {super (); this. id = id; this. name = name; this. number = number; // this. nickname = nickname ;}}

2. To write a database class for a database, you must create a new class and inherit the SQLiteOpenHelper class. Then implement a constructor. For more information, see the code.

Package com. example. freedomsql. db; import android. content. context; import android. database. sqlite. SQLiteDatabase; import android. database. sqlite. SQLiteOpenHelper;/*** @ ClassName: FreedomDB * @ author cmd_freedom (x_freedom_reddevil@126.com) * @ createddate 2:49:15 * @ Description: TODO */public class FreedomDB extends SQLiteOpenHelper {public FreedomDB (Context context) {// The parameters are Context, database name, cursor factory is generally NULL, database version number, you need to change super (context, "freedom. db ", null, 1);}/*** trigger this method when creating the database for the first time */@ Overridepublic void onCreate (SQLiteDatabase db) {// For details about SQL statements, see section 1 of this series. db.exe cSQL ("create table person (id integer primary key autoincrement, name varchar (20), number varchar (20 ))");} /*** trigger this method when the database version number changes */@ Overridepublic void onUpgrade (SQLiteDatabase db, int oldversion, int newversion) {// if (oldversion = 1 & newversion = 2) {// db.exe cSQL ("alter table person add nickname varchar (20 )");//}}}

3. After the database operation class dao is created, an operation class is required to perform operations on the database. This class is generally named dao. For details, refer to the Code:

Package com. example. freedomsql. db. dao; import java. util. arrayList; import java. util. list; import android. content. context; import android. database. cursor; import android. database. sqlite. SQLiteDatabase; import com. example. freedomsql. bean. person; import com. example. freedomsql. db. freedomDB;/*** @ ClassName: PersonDao * @ author empty _freedom (x_freedom_reddevil@126.com) * @ createddate 2:30:24 * @ D Edo: TODO */public class PersonDao {private FreedomDB dbHelper; public PersonDao (Context context) {dbHelper = new FreedomDB (context);}/*** @ Title: add * @ Description: add * @ param name * @ param number * @ throws */public void add (String name, String number) {SQLiteDatabase db = dbhelper.getwritabledatabase()mongodb.exe cSQL ("insert into person (name, number) values (?,?) ", New Object [] {name, number}); db. close (); // system API // ContentValues = values = new ContentValues (); // values. put ("name", name); // values. put ("number", number); // long id = db. insert (table name), null, values (data); // return id;} // ** // * @ Title: add // * @ Description: add method after upgrade // * @ param name // * @ param number // * @ param nickname // * @ throws // * // public void add (String name, string number, String nicknam E) {// SQLiteDatabase db = dbHelper. getWritableDatabase (); // db.exe cSQL ("insert into person (name, number, nickname) values (?,?,?) ", // New Object [] {name, number, nickname}); // db. close (); // system API // ContentValues = values = new ContentValues (); // values. put ("name", name); // values. put ("number", number); // long id = db. insert (table name), null, values (data); // return id; //}/*** @ Title: delete * @ Description: delete * @ param name * @ throws */public void delete (String name) {SQLiteDatabase db = dbHelper. getWritableDatabase () Mongodb.exe cSQL ("delete from person where name =? ", New Object [] {name}); // system API // int number = db. delete (" person "," name =? ", New String [] {name}); db. close ();}/*** @ Title: updata * @ Description: Update * @ param name * @ param newnumber * @ throws */public void updata (String name, string newnumber) {SQLiteDatabase db = dbhelper.getwritabledatabase(mongomongodb.exe cSQL ("update person set number =? Where name =? ", New Object [] {newnumber, name}); // system API // ContentValues values = new ContentValues (); // values. put ("number", newnumber); // int num = db. update ("person", values, "name =? ", New String [] {name //}); db. close ();}/*** @ Title: find * @ Description: Query * @ param name * @ return * @ throws */public boolean find (String name) {SQLiteDatabase db = dbHelper. getReadableDatabase (); Cursor cursor = db. rawQuery ("select * from person where name =? ", New String [] {name}); // system API // Cursor cursor = db. query (table name), null," name =? ", New // String [] (name), null); boolean result = cursor. moveToNext (); cursor. close (); db. close (); return result;}/*** @ Title: findAll * @ Description: Query * @ return * @ throws */public List <Person> findAll () {List <Person> persons = new ArrayList <Person> (); SQLiteDatabase db = dbHelper. getReadableDatabase (); Cursor cursor = db. rawQuery ("select * from person", null); // system API // Cursor cursor = db. query ("Person", new // String [] {"name", "id", "number"}, null ); while (cursor. moveToNext () {int id = cursor. getInt (cursor. getColumnIndex ("id"); String name = cursor. getString (cursor. getColumnIndex ("name"); String number = cursor. getString (cursor. getColumnIndex ("number"); // String nickname = cursor. getString (cursor //. getColumnIndex ("nickname"); Person p = new Person (id, name, number); persons. add (p);} cursor. close (); db. close (); return persons ;}}

4. The code in the main Activity is operated on the database using the dao class in the activity.

Package com. example. freedomsql; import com. example. freedomsql. db. dao. personDao; import android. app. activity; import android. app. actionBar; import android. app. fragment; import android. OS. bundle; import android. view. layoutInflater; import android. view. menu; import android. view. menuItem; import android. view. view; import android. view. viewGroup; import android. OS. build; public class MainActivity extends Activity {@ Overrideprotected void onCreate (Bundle savedInstanceState) {super. onCreate (savedInstanceState); setContentView (R. layout. activity_main); PersonDao dao = new PersonDao (getBaseContext (); dao. add ("Tang boss", "110"); dao. add ("guo Daxia", "119"); // dao. add ("Tang Boss 1", "110", "Tang boss"); // dao. add ("guo Daxia 1", "119", "Guo nvxia ");}}

Here, only insert data is operated. If you are interested, you can test other operations on your own or write other methods in dao for more complex operations. After the code is executed, the database content is displayed.
2. Database Upgrade and update in actual development, the database will certainly have upgrade-related operations. However, database upgrades cannot clear data before the project and must be retained, this requires some control and processing of the database version number. So we will upgrade the database. 1. Upgrade the person class, and add a nickname field.

Package com. example. freedomsql. bean; import java. io. serializable;/*** @ ClassName: Person * @ author emerge _freedom (x_freedom_reddevil@126.com) * @ createddate 4:22:09 * @ Description: TODO */public class Person implements Serializable {private int id; private String name; private String number; private String nickname; public Person (int id, String name, String number, String nickname) {super (); this. id = id; this. name = name; this. number = number; this. nickname = nickname ;}}

2. Change the database generation class 1. Change the version number to 2.

Public FreedomDB (Context context) {// The parameters are context, database name, cursor factory is generally NULL, database version number, and super (Context, "freedom. db ", null, 2 );}

2. Processing in the onUpgrade method. In actual development, the version number must be processed logically.

/*** Trigger this method when the database version number changes */@ Overridepublic void onUpgrade (SQLiteDatabase db, int oldversion, int newversion) {if (oldversion = 1 & newversion = 2) mongodb.exe cSQL ("alter table person add nickname varchar (20 )");}}

3. Modify Dao class 1. Add method changes

/*** @ Title: add * @ Description: method to add after upgrade * @ param name * @ param number * @ param nickname * @ throws */public void add (String name, String number, String nickname) {SQLiteDatabase db = dbhelper.getwritabledatabase()mongodb.exe cSQL ("insert into person (name, number, nickname) values (?,?,?) ", New Object [] {name, number, nickname}); db. close (); // system API // ContentValues = values = new ContentValues (); // values. put ("name", name); // values. put ("number", number); // long id = db. insert (table name), null, values (data); // return id ;}

2. query method Modification

/*** @ Title: findAll * @ Description: Query * @ return * @ throws */public List <Person> findAll () {List <Person> persons = new ArrayList <Person> (); SQLiteDatabase db = dbHelper. getReadableDatabase (); Cursor cursor = db. rawQuery ("select * from person", null); // system API // Cursor cursor = db. query ("Person", new // String [] {"name", "id", "number"}, null ); while (cursor. moveToNext () {int id = cursor. getInt (cursor. getColumnIndex ("id"); String name = cursor. getString (cursor. getColumnIndex ("name"); String number = cursor. getString (cursor. getColumnIndex ("number"); String nickname = cursor. getString (cursor. getColumnIndex ("nickname"); Person p = new Person (id, name, number, nickname); persons. add (p);} cursor. close (); db. close (); return persons ;}

4. Modify the main Activity

Package com. example. freedomsql; import com. example. freedomsql. db. dao. personDao; import android. app. activity; import android. app. actionBar; import android. app. fragment; import android. OS. bundle; import android. view. layoutInflater; import android. view. menu; import android. view. menuItem; import android. view. view; import android. view. viewGroup; import android. OS. build; public class MainActivity extends Activity {@ Overrideprotected void onCreate (Bundle savedInstanceState) {super. onCreate (savedInstanceState); setContentView (R. layout. activity_main); PersonDao dao = new PersonDao (getBaseContext (); dao. add ("Tang Boss 1", "110", "Tang boss"); dao. add ("guo Daxia 1", "119", "Guo nvxia ");}}

In this way, the operation after the Database Upgrade is completed. Let's take a look at the upgraded database content. We can see from the figure that the previous data still exists and has not been cleared, the newly added field is added, and the newly created object is also created successfully.
The basic operations of the sqlite database have been introduced in Android development. Of course, in actual development, we will not perform this operation. There are many encapsulated frameworks for us to use, and the performance is greatly optimized, and the operation is simple. These basic development courses are designed to give us a clearer understanding of the underlying operating principles of the database. All database operation frameworks are basically implemented based on this theory. After learning about this, we can learn how to use the framework.