Android Study Notes (12) ---- use sqliteopenhelper to manage SQLite Databases

/*************************************** **************************************** *************
* Author: conowen @ Dazhong
* E-mail: conowen@hotmail.com
* Http://blog.csdn.net/conowen
* Note: This article is original and only used for learning and communication. For more information, indicate the author and its source.

**************************************** **************************************** ************/

1. sqliteopenhelper Introduction

Through the previous blog.

The official description of sqliteopenhelper is as follows:

A helper class to manage database creation and version management.

You create a subclass implementingonCreate(SQLiteDatabase),onUpgrade(SQLiteDatabase,
int, int)And optionallyonOpen(SQLiteDatabase), And this class takes care of Opening
The database if it exists, creating it if it does not, and upgrading it as necessary. transactions are used to make sure the database is always in a sensible state.

This class makes it easyContentProviderImplementations to defer opening and upgrading the database until first use, to avoid blocking Application
Startup with long-running database upgrades.

For an example, see the notepadprovider class in the notepad sample application, inSamples/Directory of the SDK.

Sqliteopenhelper can create databases and manage database versions.

In the class that inherits sqliteopenhelper (extends
SQLiteOpenHelper), ThroughRewriteonCreate(SQLiteDatabase),onUpgrade(SQLiteDatabase,
int, int)AndonOpen(SQLiteDatabase)(Optional.

2. Usage of sqliteopenhelper ()

The oncreate (sqlitedatabase dB) and onupgrade (sqlitedatabase dB, int oldversion, int newversion) methods are automatically added as follows.

/*
 * @author:conowen
 * @date:12.2.29 
 */

package com.conowen.sqlite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DbHelper extends SQLiteOpenHelper{

	public DbHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}

}

Detailed description

public SQLiteOpenHelper (Context context, String name, SQLiteDatabase.CursorFactory factory, int version) 

Since: API Level 1

Create a helper object to create, open, and/or manage a database. This method always returns very quickly. The database is not actually created or opened until onegetWritableDatabase()
OrgetReadableDatabase()Is called.

Parameters

Context	To use to open or create the database
Name	Of the database file, or NULL for an in-Memory Database
Factory	To use for creating cursor objects, or NULL for the default
Version	Number of the database (starting at 1); if the database is older,onUpgrade(SQLiteDatabase, int, int) Will be used to upgrade the database; if the database is newer,onDowngrade(SQLiteDatabase, int, int) Will be used to downgrade the database

Parameter description:

Name ---- indicates the database file name (excluding the file path). The sqliteopenhelper class creates a database file based on the file name.

Version ---- indicates the database version. If the input database version is later than the previous version, sqliteopenhelper calls the onupgrade () method.

public DbHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}

The above is the sqliteopenhelper constructor. When the database does not exist,The database will be created, and then the database will be opened (the process has been encapsulated)And then call the oncreate (sqlitedatabase dB) method to perform operations such as table creation. When a database exists, sqliteopenhelper will not call the oncreate (sqlitedatabase dB) method. It will detect the version number. If the passed version number is higher than the current one, it will execute onupgrade () to update the database and version.

3. Two main methods of sqliteopenhelper

3.1 oncreate Method

public abstract void onCreate (SQLiteDatabase db)

Since:
API Level 1

Called when the database is created for the first time. This is where the creation of tables and the initial population of the tables shoshould happen.

Parameters

DB	The database.

// This creates a table
@Override
public void onCreate (SQLiteDatabase db) {
// TODO Auto-generated method stub

String sql = "CREATE TABLE table_name (_id INTEGER PRIMARY KEY, filename VARCHAR, data TEXT)";
db.execSQL (sql);
Ranch
}

3.2 onupgrade Method

public abstract void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)

Since:
API Level 1

Called when the database needs to be upgraded. The implementation shocould use this method to drop tables, add tables, or do anything else it needs to upgrade to the new schema version.

The SQLite alter table documentation can be found
Here. if you add new columns you can use alter table to insert them into a live table. if you rename or remove columns you can use alter table to rename the old table, then create the new table and then populate the new table with the contents of the old
Table.

Parameters

DB	The database.
Oldversion	The old database version.
Newversion	The new database version.

Update databases, including deleting tables and adding tables. If the version is the first version, that is, the database just created, there is no need to write in the onupgrade () method, because the first version of the database should be updated. if the version or database is released later, you can add various update operations in the onupgrade () method.

4. Notes

After creating the sqliteopenhelper class, you can use sqliteopenhelper. getwritabledatabase () or getreadabledatabase () to obtain the database instance created in the sqliteopenhelper class in the main activity. (That is to say, only the two methods are called can the database be instantiated)

Getwritabledatabase () method ---- open the database in read/write mode. If the disk space of the database is full and the getwritabledatabase () method is used, an error occurs.

Because the database can only read but cannot write at this time,

Getreadabledatabase () method ---- the read/write method is used to open the database first. If the disk space of the database is full, it will fail to open, but when the Open fails, it will continue to try to read-only

Open the database. No error is reported.

========================================================== ========================================================== ======================================

The following is an example of a listview with SQLite database as adapter (which can also be used as a Address Book tool)

As follows:

/ * Main activity
 * @author: conowen
 * @date: 12.3.1
 * /
package com.conowen.sqlite;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.Toast;

public class SqliteActivity extends Activity {
SQLiteDatabase sqldb;
public String DB_NAME = "sql.db";
public String DB_TABLE = "num";
public int DB_VERSION = 1;
final DbHelper helper = new DbHelper (this, DB_NAME, null, DB_VERSION);

// The DbHelper class is created in the DbHelper.java file

/ ** Called when the activity is first created. * /
@Override
public void onCreate (Bundle savedInstanceState) {
super.onCreate (savedInstanceState);
setContentView (R.layout.main);
sqldb = helper.getWritableDatabase ();
// Get the database created by SQLiteOpenHelper via getWritableDatabase () of the helper
Button insert = (Button) findViewById (R.id.insert);
Button delete = (Button) findViewById (R.id.delete);
Button update = (Button) findViewById (R.id.update);
Button query = (Button) findViewById (R.id.query);
final ContentValues cv = new ContentValues ();
// ContentValues is the data carrier for the two operations "add" and "update"
updatelistview (); // Update listview
// add insert
insert.setOnClickListener (new OnClickListener () {

@Override
public void onClick (View v) {
// TODO Auto-generated method stub
EditText et_name = (EditText) findViewById (R.id.name);
EditText et_phone = (EditText) findViewById (R.id.phone);
cv.put ("name", et_name.getText (). toString ());
cv.put ("phone", et_phone.getText (). toString ());
// name and phone are column names
long res = sqldb.insert ("addressbook", null, cv); // insert data
if (res == -1) {
Toast.makeText (SqliteActivity.this, "Add failed",
Toast.LENGTH_SHORT) .show ();
} else {
Toast.makeText (SqliteActivity.this, "Added successfully",
Toast.LENGTH_SHORT) .show ();
}
updatelistview (); // Update listview
}
});
// delete
delete.setOnClickListener (new OnClickListener () {

@Override
public void onClick (View v) {
// TODO Auto-generated method stub
int res = sqldb.delete ("addressbook", "name = '大 钟'", null);
// delete the column name name, the row name is "big clock", all data in this row, null means all data in this row
// If the second parameter is null, delete the data of all rows corresponding to all columns in the table, that is, clear the table.
// name = 'Big Bell'
// The return value is the number of deleted rows
if (res == 0) {
Toast.makeText (SqliteActivity.this, "Delete failed",
Toast.LENGTH_SHORT) .show ();
} else {
Toast.makeText (SqliteActivity.this, "Successfully deleted" + res + "row data",
Toast.LENGTH_SHORT) .show ();
}
updatelistview (); // Update listview

}
});
// change
update.setOnClickListener (new OnClickListener () {

@Override
public void onClick (View v) {
// TODO Auto-generated method stub
cv.put ("name", "Big Bell");
cv.put ("phone", "1361234567");
int res = sqldb.update ("addressbook", cv, "name = '张三'", null);
// Update the data of name = Zhang San's line with the data corresponding to ContentValues
// number of rows successfully updated on return
Toast.makeText (SqliteActivity.this, "Successfully updated" + res + "row data",
Toast.LENGTH_SHORT) .show ();

updatelistview (); // Update listview
}
});
		// Inquire
query.setOnClickListener (new OnClickListener () {

@Override
public void onClick (View v) {
// TODO Auto-generated method stub
Cursor cr = sqldb.query ("addressbook", null, null, null, null,
null, null);
// return all data for the table named addressbook
Toast.makeText (SqliteActivity.this,
"Total" + cr.getCount () + "Records", Toast.LENGTH_SHORT)
.show ();

updatelistview (); // Update listview
}
});

}

// update listview
public void updatelistview () {
ListView lv = (ListView) findViewById (R.id.lv);

final Cursor cr = sqldb.query ("addressbook", null, null, null, null,
null, null);
String [] ColumnNames = cr.getColumnNames ();
// ColumnNames is the column names of the database table, getColumnNames () is to get all the column names of the specified table

ListAdapter adapter = new SimpleCursorAdapter (this, R.layout.layout,
cr, ColumnNames, new int [] {R.id.tv1, R.id.tv2, R.id.tv3});
// layout is the layout file of listView, including three TextViews, used to display the values corresponding to the three column names
// ColumnNames are the column names of the database tables
// The last parameter is of type int [], which is the id of view type, used to display the values corresponding to the ColumnNames column names. view of type TextView
lv.setAdapter (adapter);
}
}
/ * SQLiteOpenHelper Class
 * @author: conowen
 * @date: 12.3.1
 * /
package com.conowen.sqlite;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DbHelper extends SQLiteOpenHelper {

public DbHelper (Context context, String name, CursorFactory factory,
int version) {
super (context, name, factory, version);
// TODO Auto-generated constructor stub
Ranch
}

@Override
public void onCreate (SQLiteDatabase db) {
// TODO Auto-generated method stub

String sql = "CREATE TABLE addressbook (_id INTEGER PRIMARY KEY, name VARCHAR, phone VARCHAR)";
db.execSQL (sql);

}

@Override 
public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub

}

}

Main. xml

<? xml version = "1.0" encoding = "utf-8"?>
<LinearLayout xmlns: android = "http://schemas.android.com/apk/res/android"
    android: layout_width = "fill_parent"
    android: layout_height = "fill_parent"
    android: orientation = "vertical">

    <EditText
        android: id = "@ + id / name"
        android: layout_width = "fill_parent"
        android: layout_height = "wrap_content" />

    <EditText
        android: id = "@ + id / phone"
        android: layout_width = "fill_parent"
        android: layout_height = "wrap_content" />

    <LinearLayout
        android: id = "@ + id / linearLayout1"
        android: layout_width = "fill_parent"
        android: layout_height = "wrap_content">

        <Button
            android: id = "@ + id / insert"
            android: layout_width = "wrap_content"
            android: layout_height = "wrap_content"
            android: text = "Increase" />

        <Button
            android: id = "@ + id / delete"
            android: layout_width = "wrap_content"
            android: layout_height = "wrap_content"
            android: text = "delete" />

        <Button
            android: id = "@ + id / update"
            android: layout_width = "wrap_content"
            android: layout_height = "wrap_content"
            android: text = "change" />

        <Button
            android: id = "@ + id / query"
            android: layout_width = "wrap_content"
            android: layout_height = "wrap_content"
            android: text = "Query" />
    </ LinearLayout>

    <ListView
        android: id = "@ + id / lv"
        android: layout_width = "fill_parent"
        android: layout_height = "wrap_content">
    </ ListView>

</ LinearLayout>

Layout. xml layout file of listview

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="horizontal" >
<TextView
        android:id="@+id/tv1"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="20sp" 
        android:width="50px" />
<TextView
        android:id="@+id/tv2"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="20sp" 
        android:width="50px"
        />
<TextView
        android:id="@+id/tv3"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="20sp" 
        android:width="150px" />

</LinearLayout>