Android-database SQLite, android-sqlite

Android-database SQLite, android-sqlite

The SQLite database on the android platform is a lightweight database that supports standard SQL statements.

This article introduces

Create an android Database

Use SQL statements to add, delete, modify, and query Databases

Add, delete, modify, and query system api Databases

Database transactions

1. Create a database

 
Steps:
 
Write a class to inherit from SQLiteOpenHelper
Specify the database name, cursor factory, and version number in the constructor.
Initialize the database and execute getWritableDatabase or getReadableDatabase to create or open a database.
 
Execute the SQL creation statement in onCreate

To create a database on the android platform, a helper class must inherit SQLiteOpenHelper and override the constructor of the parent class.

/*** Create a helper class for the database to write a class that inherits SQLiteOpenHelper ** @ author wgk **/public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {public PersonSQLiteOpenHelper (Context context) {super (context, "PERSON. db ", null, 1); // This is super, directly calling the constructor of the parent class}/*** constructor to create a helper class, used to open/create/manage a database ** @ param context * @ param name * name of the database file * @ param factory * Cursor factory Cursor (pointer ), it does not store data. save the database reference. * @ param version * database version */public PersonSQLiteOpenHelper (Context context, String name, CursorFactory factory, int version) {super (context, name, factory, version );} @ Override // called when the database is created for the first time. It is suitable for initializing public void onCreate (SQLiteDatabase db) {// create a table db.exe cSQL ("create table person (" + "_ id integer primary key autoincrement," + "name varchar (20)," + "age integer ); ") ;}@ Override // this method is called when the database is updated // public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) is used to update the table) {// TODO Auto-generated method stub }}

This class is just a helper class. To use a database, you also need to create a database login class PersonDAO.

2. add, delete, modify, and query Databases

Public class PersonDAO {private final Context context; private PersonSQLiteOpenHelper helper; public PersonDAO (Context context) {this. context = context; helper = new PersonSQLiteOpenHelper (context);}/*** @ param name * @ param age */public void add (String name, int age) {SQLiteDatabase db = helper. getWritableDatabase (); // db.exe cSQL ("insert into person (name, age) values ('" + name + "'," + age + ")"); // Prevent SQL injection into db.exe cSQL ("insert into person (name, values) values (?,?) ", New Object [] {name, age});}/*** delete a piece of data by name ** @ param name */public void delete (String name) {SQLiteDatabase db = helper. getWritableDatabase (); db.exe cSQL ("delete from person where name =? ", New Object [] {name});} // update the age by name

Public void update (int age, String name) {SQLiteDatabase db = helper. getWritableDatabase (); db.exe cSQL ("update person set age =? Where name =? ", New Object [] {age, name});} public void querySingleRecord (String nameStr) {SQLiteDatabase db = helper. getReadableDatabase (); Cursor cursor = db. rawQuery ("select * from person where name =? ", New String [] {nameStr}); if (cursor! = Null & cursor. moveToFirst () {String _ id = cursor. getString (0); String name = cursor. getString (1); String age = cursor. getString (2); System. out. println ("_ id:" + _ id); System. out. println ("name:" + name); System. out. println ("age:" + age); cursor. close () ;}/ *** query all data */public void queryAll () {SQLiteDatabase db = helper. getReadableDatabase (); Cursor cursor = db. rawQuery ("select * form per Son ", null); if (cursor! = Null & cursor. getCount ()> 0) {while (cursor. moveToNext () {int _ id = cursor. getInt (cursor. getColumnIndex ("_ id"); String name = cursor. getString (cursor. getColumnIndex ("name"); int age = cursor. getInt (cursor. getColumnIndex ("age"); System. out. println ("_ id:" + _ id); System. out. println ("name:" + name); System. out. println ("age:" + age); System. out. println ("-----------------");} cursor. close ();}}}

Note: The database is not created when the auxiliary class Object of the database is obtained. The database is created only when the auxiliary class object calls the getxxxxDatabase method (create a readable or writable database.

// No data is created at this time. You can use PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper (getContext (); // obtain a readable/writable database, and create the database helper. getWritableDatabase ();

3. Use goole APIs to operate databases

In addition to SQL statements for addition, deletion, modification, and query, you can also use APIs provided by google.

// Main statement
// Add db. insert ("person", "name", values); // delete db. delete ("person", "name =? ", New String [] {name}); // modify db. update (" person ", values," name =? ", New String [] {name}); // query Cursor cursor = db. query ("person", // table name null, // You Need to query the column name new String [] {name, age} "name =? ", // Query condition new String [] {nameStr}, // condition parameter null, // group null, // group null); // sort

Use the google api to modify the PersonDAO as follows:

/*** Use the api provided by google to operate the database ** Database Access Object ** @ author wgk **/public class PersonDAO3 {private final Context context; private PersonSQLiteOpenHelper helper; public PersonDAO3 (Context context) {this. context = context; helper = new PersonSQLiteOpenHelper (context);}/*** add a data entry * @ param name * @ param age */public void add (String name, int age) {SQLiteDatabase db = helper. getWritableDatabase (); ContentValues v Alues = new ContentValues (); values. put ("name", name); values. put ("age", age); // to insert an empty data record, you must specify the name of any column to avoid the exception of String nullColumnHack = "null "; long insert = db. insert ("person", nullColumnHack, values); System. out. println (insert);}/*** delete a data entry by name * @ param name */public void delete (String name) {SQLiteDatabase db = helper. getWritableDatabase (); int delete = db. delete ("person", "name =? ", New String [] {name}); System. out. println (delete);}/*** update age! Based on the name * @ param age * @ param name */public void update (int age, String name) {SQLiteDatabase db = helper. getWritableDatabase (); ContentValues values = new ContentValues (); values. put ("age", age); int update = db. update ("person", values, "name =? ", New String [] {name}); System. out. println (update);}/*** query! According to a person's name * @ param name */public void querySingleRecord (String nameStr) {SQLiteDatabase db = helper. getReadableDatabase (); Cursor cursor = db. query ("person", null, // column name "name =? ", // Query condition new String [] {nameStr}, // query parameter null, // group null, // group null); // sort if (cursor! = Null & cursor. moveToFirst () {String _ id = cursor. getString (0); String name = cursor. getString (1); String age = cursor. getString (2); System. out. println ("_ id:" + _ id); System. out. println ("name:" + name); System. out. println ("age:" + age); // close cursor. close () ;}/ *** query all data */public void queryAll () {SQLiteDatabase db = helper. getReadableDatabase (); Cursor cursor = db. query ("pe Rson ", null," _ id DESC "); // sort if (cursor! = Null & cursor. getCount ()> 0) {while (cursor. moveToNext () {int _ id = cursor. getInt (cursor. getColumnIndex ("_ id"); String name = cursor. getString (cursor. getColumnIndex ("name"); int age = cursor. getInt (cursor. getColumnIndex ("age"); System. out. println ("_ id:" + _ id); System. out. println ("name:" + name); System. out. println ("age:" + age );
} // Close cursor. close ();}}}

Comparison of the two methods

1. Use SQL statements for addition, deletion, modification, and query
Advantages: flexible, table cascade query as needed.

Disadvantage: error-prone. No return value

2. add, delete, modify, and query using system APIs

Advantage: error-prone. Return Value

Disadvantages: inflexible, less efficient, and time-consuming to splice SQL statements

------------------------------------------------- I am a split line --------------------------------------------- Database Transaction (Transaction)

Use laowang to transfer 1000 to xiaosan and write a demo as follows:

Public class TransactionDemo extends AndroidTestCase {public void transactionDemo () {PersonSQLiteOpenHelper helper = new PersonSQLiteOpenHelper (getContext (); // at this time, the database SQLiteDatabase db = helper is created. getWritableDatabase (); try {// start transaction db. beginTransaction (); // transfer 1000 db.exe cSQL ("update person set money = money-1000 where name =? ", New Object [] {" laowang "}); // before the rollback point is executed, the database is not actually operated, and everything is in the memory, the database // int I = 1/0 is affected only after the rollback is performed. // transfer to 1000 db.exe cSQL ("update person set money = money + 1000 where name =? ", New Object [] {" xiaosan "}); // sets the rollback point db. setTransactionSuccessful ();} catch (Exception e) {e. printStackTrace ();} finally {db. endTransaction ();}}

 

Summary:

I recently wrote a blog only after work. Now I just want to sort my previous notes a little. Some knowledge points may be inaccurate, and some strategies may not be optimal, because I didn't realize it at the time of study. I did not pick it out. Please correct it together.
I'm going to have a dream. hiahiahiahia ~~~
------------------------------------------------- The Foundation should be rock solid !!!