Introduction and use of Android SQLite (ii)

The previous section briefly describes SQLite, which we started with in the app for SQLite in Android.

Android provides a database Help class SQLiteOpenHelper for managing database creation and versioning. We can inherit this class and implement its onCreate and onUpgrade methods. Here we can set the database version, database name, create database table and so on. Here's a look at the code:

public class DBHelper extends Sqliteopenhelper {//Database version number must be greater than 1 public final static int db_version = 1;    The name of the database public final static String db_name = "user.db";    database table name Public final static String table_name = "UserInfo";    public static DBHelper helper = null;        public static DBHelper Gethelper () {if (helper = = null) {helper = new DBHelper (App.getcontext ());    } return helper; The private DBHelper (context context) {//calls the parent class method to create the database, Cusorfactory is generally empty and uses the default//cursorfactory object, which is used to construct the query when it is finished.        The cursor's child class object, which is null when the default Cursorfactory construct is used.    Super (context, db_name, NULL, db_version);        }/** * The database is called the first time it is created, we perform some database table creation, initialization, etc. */@Override public void OnCreate (Sqlitedatabase db) {    Createtableuser (DB);     /** * When the database is updated, we can perform database modifications during the upgrade, such as modifying tables, deleting tables, adding tables, and so on. */@Override public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {if (NewVersion > O    Ldversion) {        Droptableuser (DB);        Createtableuser (DB); }} private void Createtableuser (Sqlitedatabase db) {String sql = "CREATE TABLE" + table_name + "(_id Integ        ER primary key autoincrement, NAME text,age int,address TEXT); ";    Db.execsql (SQL);        } private void Droptableuser (Sqlitedatabase db) {String sql = "DROP table if exists" + table_name;    Db.execsql (SQL); }}

Once we have created our database helper class, we can use it to operate the database. Of course, we can also put some database to add, delete, change, check the method put into this database help class.

Add (Insert):

There are two ways to insert data into a database table, one is to use SQL statements, and one is to use the Insert method provided by Android.

Using SQL statements

Using SQL statements, we first need to group our SQL statements, we use StringBuilder:

StringBuilder sql = new StringBuilder();sql.append("insert into " + DBHelper.TABLE_NAME + "(name,age,address) values(")    .append(" ‘张三‘").append(",")    .append("20").append(",")    .append("‘火星" + i + "号‘")    .append(" );")

The SQL statement is then executed using the Execsql of the database.

mDatabase.execSQL(sql.toString());

Insert method provided by Android

Using the Insert method provided by Android, you need to use another object, Contentvalues, which is similar to map, which holds the data as a key-value pair, which represents the column name of the table, and the value represents the value inserted by the column. Specific use is as follows.

    ContentValues values = new ContentValues();    values.put("name", "张三");    values.put("age", 20);    values.put("address", "火星1号");

Then call the Insert method:

 mDatabase.insert(DBHelper.TABLE_NAME, null, values);

Delete (delete): Use sql:

Group Consolidation sql:

    StringBuilder builder = new StringBuilder();    builder.append(" delete from ").append(DBHelper.TABLE_NAME).append(" where _id = ?");

You should notice the condition of the where statement at the end of the SQL statement is '? ', here's '? ' Play the role of placeholder, we need to use "string[" in the following to fill in the specific values, such as here we can:

String[] bindArgs = {"1"};

Then we call the database execSQL method to perform the deletion of the data:

mDatabase.execSQL(builder.toString(), bindArgs);

The SQL statement it executes is equivalent to:

delete from userInfo where _id = 1 ;

Of course, we can also set the conditional value directly into a string statement.

Use the Delete method provided by Android:

The Delete method provided by Android requires the condition of the table name, WHERE clause, WHERE clause, and this method returns the number of rows affected by the delete.

    //where子句    String whereClause = "_id = ?";    //where字句里的里占位符的值    String[] whereArgs = {"1"};    int affectedRows = mDatabase.delete(DBHelper.TABLE_NAME, whereClause, whereArgs);

Change (update) using SQL statements

First, we set up the SQL statement:

    StringBuilder builder = new StringBuilder();    builder.append("update ").append(DBHelper.TABLE_NAME).append(" set name=?,age=?,address=? where _id=?;");

Then define an array to represent the SET clause '? ' Placeholder values:

Object[] bindArgs = {"赵六", 13, "水星1号", 5};

Then we call the database execSQL method to perform the update of the data:

mDatabase.execSQL(builder.toString(), bindArgs);

Using the Update method that comes with Android

Using the Update method provided by Android, you need to use the Contentvalues object to determine the columns and values that need to be updated.

    ContentValues values = new ContentValues();    values.put("name", "钱八");    values.put("age", 15);    values.put("address", "水星2号");

Then we need to be prepared with the WHERE clause and the value of the placeholder in the WHERE clause:

    String whereClause = "_id=?";    String[] whereArgs = {"4"};

Then call the database's Update method to perform the update data operation:

  mDatabase.update(DBHelper.TABLE_NAME, values, whereClause, whereArgs);

Check (SELECT) Using SQL statements:

The results of the Android query database are returned to a cursor object. It will be positioned before the first row. All data is obtained by subscript.

First I need to group the SQL statements:

 StringBuilder builder = new StringBuilder();    builder.append(" select * from ").append(DBHelper.TABLE_NAME);

Then call the database's rawQuery method to execute the query, which returns a cursor:

Cursor cursor = mDatabase.rawQuery(builder.toString(), null);

Since cursor fetching data is obtained by subscript, the cursor specifically provides a way to get the subscript through a list:

cursor.getColumnIndex(列名)；

Cursor positioning is fixed before the first line we need to call MoveToNext () to navigate to the first row, and of course there are a lot of move start methods, you can study more.

So we can use loops to get what we need, and we'll save the contents of the data table to the list.

    List<User> users = new ArrayList<>();    while (cursor.moveToNext()) {        String name = cursor.getString(cursor.getColumnIndex("name"));        int age = cursor.getInt(cursor.getColumnIndex("age"));        String address = cursor.getString(cursor.getColumnIndex("address"));        User user = new User(name, age, address);        users.add(user);    }

Finally, our cursor object needs the Close method to release the resource.

cursor.close();

Use the Query method provided by Android:

We need to provide the name of the column that needs to be queried:

String[] columns = {"name", "age", "address"};

Where Condition:

String selection = "_id=?";

Where condition '? ' Substitution values for:

String[] selectionArgs = {"1"};

We call the query method to execute the queries:

Cursor cursor = mDatabase.query(DBHelper.TABLE_NAME, columns, selection, selectionArgs, null, null, null);

The next three numbers are the GROUPBY clause, the HAVING clause, the ORDER BY clause, and if any of these requirements can be defined by themselves.

Then we take the data out of the cursor and put it in the list.

    List<User> users = new ArrayList<>();    while (cursor.moveToNext()) {        String name = cursor.getString(cursor.getColumnIndex("name"));        int age = cursor.getInt(cursor.getColumnIndex("age"));        String address = cursor.getString(cursor.getColumnIndex("address"));        User user = new User(name, age, address);        users.add(user);    }

Finally close the Cursor and release the resource:

 cursor.close();

The full code is as follows:

public class DBHelper extends Sqliteopenhelper {//Database version number must be greater than 1 public final static int db_version = 1;    The name of the database public final static String db_name = "user.db";    database table name Public final static String table_name = "UserInfo";    public static DBHelper helper = null;    Public Sqlitedatabase mdatabase;        public static DBHelper Gethelper () {if (helper = = null) {helper = new DBHelper (App.getcontext ());    } return helper; /** * CREATE database tables through Sqliteopenerhelper, * <p/> * differences between Getwritabledatabase and Getreadabledatabase * < P/> * Getwritabledatabase The instance is not just a function of writing, but a function of reading and writing at the same time * Similarly, the getreadabledatabase obtained is also a function of reading and writing the database. Do not lie in * Getwritabledatabase The instance is opened in a read-write way to open the database, if the open database disk full, at this time can only read can not write, when the instance of Getwritabledatabase is called, then an error (Exception) will occur * Getrea     Dabledatabase gets an instance of calling Getwritabledatabase to open the database in a read-write manner, and if the database's disk is full, the return to open fails, and the database is opened in a read-only manner with the instance of Getreadabledatabase      */Private DBHelper (context context) {  Call the parent class method to create the database, Cusorfactory is generally empty, using the default//cursorfactory object, used to construct the child class object of the cursor that is returned when the query is complete, and null when using the default Cursorfactory construct.        Super (context, db_name, NULL, db_version);        Gets the database object.    Mdatabase = Getreadabledatabase ();//mdatabase = Getwritabledatabase ();        }/** * The database is called the first time it is created, we perform some database table creation, initialization, etc. */@Override public void OnCreate (Sqlitedatabase db) {    Createtableuser (DB);     /** * When the database is updated, we can perform database modifications during the upgrade, such as modifying tables, deleting tables, adding tables, and so on. */@Override public void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {if (NewVersion > O            ldversion) {droptableuser (db);        Createtableuser (DB); }} public void Createtableuser (Sqlitedatabase db) {String sql = "CREATE TABLE" + table_name + "(_id intege        R primary Key AutoIncrement, NAME text,age int,address TEXT); ";    Db.execsql (SQL); } public void Droptableuser (Sqlitedatabase db) {String sql = "DROP table if exists" + TabLe_name;    Db.execsql (SQL);      }/** * To see if a database table exists * <p/> * sqlite_master:sqlite database has a table called Sqlite_master, which defines the schema of the database, this table is read-only * * @param tableName * @return */public boolean istableexist (string tableName) {String sql = "Selec        T COUNT (*) as C from Sqlite_master WHERE type= ' table ' and name= ' "+ tableName +" ' ";        cursor cursor = mdatabase.rawquery (sql, NULL);            if (Cursor.movetonext ()) {int count = cursor.getint (0);            if (Count > 0) {return true;    }} return false;        }/** * Insert with SQL implementation */public void Insertwithsql () {sqlitedatabase db = mdatabase;        Db.begintransaction ();                try {for (int i = 0; i < i++) {StringBuilder sql = new StringBuilder (); Sql.append ("INSERT into" + Dbhelper.table_name + "(name,age,address) VALUES ("). Append ("' Zhang San '"). A       Ppend (",")                 . Append (""). Append (","). Append ("Mars" + i + "No."). Append                (" );");            Db.execsql (Sql.tostring ());        } db.settransactionsuccessful ();        } finally {db.endtransaction (); }}/** * Use Contentvalues to insert data */public void Insert () {contentvalues values = new Contentvalu        ES ();        Values.put ("name", "Zhang San");        Values.put ("Age", 20);        Values.put ("Address", "Mars 1th");    Mdatabase.insert (dbhelper.table_name, null, values);        /** * Delete data using SQL statement */public void Deletebysql () {StringBuilder builder = new StringBuilder ();        Builder.append ("Delete from"). Append (Dbhelper.table_name). Append ("Where _id =?");        String[] Bindargs = {"1"};    Mdatabase.execsql (Builder.tostring (), Bindargs); }/** * Using the Delete method provided by Android * * @return */public int Delete () {//where clause String whereclause = "_id =?";        The value of the placeholder in the WHERE clause string[] Whereargs = {"1"};        int affectedrows = Mdatabase.delete (Dbhelper.table_name, Whereclause, Whereargs);    return affectedrows;        }/** * Modify data using SQL */public void Updatebysql () {StringBuilder builder = new StringBuilder (); Builder.append ("Update"). Append (Dbhelper.table_name). Append ("Set name=?,age=?,address=?        where _id=?; ");        Object[] Bindargs = {"Zhao Liu", 13, "Mercury 1th", 5};    Mdatabase.execsql (Builder.tostring (), Bindargs); }/** * Modify data using the Update method provided by Android */public void update () {contentvalues values = new Contentvalues (        );        Values.put ("name", "Money Eight");        Values.put ("Age", 15);        Values.put ("Address", "Mercury 2nd");        String whereclause = "_id=?";        String[] Whereargs = {"4"};    Mdatabase.update (Dbhelper.table_name, values, Whereclause, Whereargs);    /** * Query all data using SQL implementation */public void Querydatabysql () {    StringBuilder builder = new StringBuilder ();        Builder.append ("SELECT * from"). Append (Dbhelper.table_name);        cursor cursor = mdatabase.rawquery (builder.tostring (), NULL);        list<user> users = new arraylist<> ();            while (Cursor.movetonext ()) {String name = cursor.getstring (Cursor.getcolumnindex ("name"));            int age = Cursor.getint (Cursor.getcolumnindex ("Age"));            String address = cursor.getstring (Cursor.getcolumnindex ("Address"));            User user = new User (name, age, address);        Users.add (user);        } logutils.e (Users + "");    Cursor.close (); /** * Query a data using SQL implementation */public void queryDataBySql1 () {StringBuilder builder = new StringBuilder (        );        Builder.append ("Select Name,age,address from"). Append (Dbhelper.table_name). Append ("Where _id =?");        String[] Selectargs = {"1"};      cursor cursor = mdatabase.rawquery (builder.tostring (), Selectargs);  list<user> users = new arraylist<> ();            while (Cursor.movetonext ()) {String name = cursor.getstring (Cursor.getcolumnindex ("name"));            int age = Cursor.getint (Cursor.getcolumnindex ("Age"));            String address = cursor.getstring (Cursor.getcolumnindex ("Address"));            User user = new User (name, age, address);        Users.add (user);        } logutils.e (Users + "");    Cursor.close (); /** * Use the Query method provided by Android to implement querying all data */public void Queryall () {CURSOR cursor = Mdatabase.query (D        Bhelper.table_name, NULL, NULL, nulls, NULL, NULL, NULL);        list<user> users = new arraylist<> ();            while (Cursor.movetonext ()) {String name = cursor.getstring (Cursor.getcolumnindex ("name"));            int age = Cursor.getint (Cursor.getcolumnindex ("Age"));            String address = cursor.getstring (Cursor.getcolumnindex ("Address")); User user = new User (name, age, address);        Users.add (user);        } logutils.e (Users + "");    Cursor.close ();  /** * Use the Query method provided by Android to implement querying a data */public void query () {//Those columns you want to query string[] columns =        {"Name", "Age", "address"};        Where condition String selection = "_id=?"; Where condition '? '        Alternative value string[] Selectionargs = {"1"};        cursor cursor = mdatabase.query (dbhelper.table_name, columns, selection, Selectionargs, NULL, NULL, NULL);        list<user> users = new arraylist<> ();            while (Cursor.movetonext ()) {String name = cursor.getstring (Cursor.getcolumnindex ("name"));            int age = Cursor.getint (Cursor.getcolumnindex ("Age"));            String address = cursor.getstring (Cursor.getcolumnindex ("Address"));            User user = new User (name, age, address);        Users.add (user);        } logutils.e (Users + "");    Cursor.close (); }}

Introduction and use of Android SQLite (ii)