Android开发之创建数据库

前言：在Android中创建数据库及操作

 * 1、创建一个类，继承SQLiteOpenHelper
 * 2、重写方法和创建构造方法
 * 3、创建子类对象，再调用getReadableDatabase()/getWriteableDatabase()方法，即可创建数据库

1. 创建一个空的数据库

创建类，并继承SQLiteOpenHelper

package com.example.databasedemo;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import androidx.annotation.Nullable;
public class DatabaseHelper extends SQLiteOpenHelper {
    /**
     *
     * @param context
     * @param name  数据库名称
     * @param factory  游标工厂
     * @param version 版本
     */
	/*  
	 *  此处为最初的构造方法，为了方便省事，已将其中的三个参数提前设置相应的数据
	public DatabaseHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, Constants.DATABASE_NAME, null, Constants.VERSION_CODE);
    }
    */
    public DatabaseHelper(@Nullable Context context) {
        super(context, Constants.DATABASE_NAME, null, Constants.VERSION_CODE);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

创建子类对象

package com.example.databasedemo;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //创建数据库
        DatabaseHelper helper = new DatabaseHelper(this);
        helper.getWritableDatabase();

    }
}

地址为：data/data/com.example.databasedemo/databases/…

2. 创建一个有表的数据库

（主要是在重写的两个方法中进行操作）

@Override
    public void onCreate(SQLiteDatabase db) {
        //首次创建时的回调
        Log.d(TAG,"创建数据库时回调...");
        //创建字段
        //sql   ：  create table table_name(_id integer,name varchar,age integer, salary integer)
        String sql = "create table "+Constants.TABLE_NAME+"(_id integer,name varchar,age integer, salary integer)";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //当版本升级时回调
        Log.d(TAG,"升级数据库...");
        //添加字段
        //sql ：  alter table table_name add phone integer;
        String sql;
        //对版本进行一个判断
        switch (oldVersion){
            case 1:
                sql = "alter table "+ Constants.TABLE_NAME+" add phone integer";
                db.execSQL(sql);
                break;
            case 2:
                sql = "alter table "+ Constants.TABLE_NAME+" add address varchar";
                db.execSQL(sql);
                break;
        }
    }

3.使用一个测试类进行测试。

在一个DAO类中写好了查询的方法

DatabaseHelper mHelper = new DatabaseHelper(context);

public void query(){
		//获取一个SQLiteDatabase 
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "select * from "+Constants.TABLE_NAME;
        //获取游标
        Cursor cursor = db.rawQuery(sql, null);
        //moveToNext()移动到下一位
        while (cursor.moveToNext()){
        //根据指定字段获取其坐标
            int index = cursor.getColumnIndex("name");
            String name = cursor.getString(index);
            Log.d(TAG,"name =="+ name);
        }
        db.close();
    }

使用测试机类进行测试

package com.example.databasedemo;

import android.content.Context;

import androidx.test.platform.app.InstrumentationRegistry;
import androidx.test.ext.junit.runners.AndroidJUnit4;

import org.junit.Test;
import org.junit.runner.RunWith;

import static org.junit.Assert.*;

/**
 1. Instrumented test, which will execute on an Android device.
 2.  3. @see <a href="http://d.android.com/tools/testing">Testing documentation</a>
 */
@RunWith(AndroidJUnit4.class)
public class ExampleInstrumentedTest {
    @Test
    public void testQuery(){
        Context context = InstrumentationRegistry.getInstrumentation().getTargetContext();
        Dao dao = new Dao(context);
        dao.query();
    }
}

**

4. 使用Android提供的API进行增删改查（重点）

**
查看提供的源码

• insert（）

/**
     * Convenience method for inserting a row into the database.
     *
     * @param table the table to insert the row into
     * @param nullColumnHack optional; may be <code>null</code>.
     *            SQL doesn't allow inserting a completely empty row without
     *            naming at least one column name.  If your provided <code>values</code> is
     *            empty, no column names are known and an empty row can't be inserted.
     *            If not set to null, the <code>nullColumnHack</code> parameter
     *            provides the name of nullable column name to explicitly insert a NULL into
     *            in the case where your <code>values</code> is empty.
     * @param values this map contains the initial column values for the
     *            row. The keys should be the column names and the values the
     *            column values
     * @return the row ID of the newly inserted row, or -1 if an error occurred
     */
    public long insert(String table, String nullColumnHack, ContentValues values) {
        try {
            return insertWithOnConflict(table, nullColumnHack, values, CONFLICT_NONE);
        } catch (SQLException e) {
            Log.e(TAG, "Error inserting " + values, e);
            return -1;
        }
    }

在dao类中去调用query（）方法

public void insert(){
        SQLiteDatabase db = mHelper.getWritableDatabase();
        //一个map集合
        ContentValues values = new ContentValues();
        values.put("id",2);
        values.put("name","小芳");
        values.put("age",18);
        values.put("salary",16000);
        values.put("phone",120);
        values.put("address","CN");
        db.insert(Constants.TABLE_NAME,null,values);
        db.close();
    }

• update（）

 /**
     * Convenience method for updating rows in the database.
     *
     * @param table the table to update in
     * @param values a map from column names to new column values. null is a
     *            valid value that will be translated to NULL.
     * @param whereClause the optional WHERE clause to apply when updating.
     *            Passing null will update all rows.
     * @param whereArgs You may include ?s in the where clause, which
     *            will be replaced by the values from whereArgs. The values
     *            will be bound as Strings.
     * @return the number of rows affected
     */
    public int update(String table, ContentValues values, String whereClause, String[] whereArgs) {
        return updateWithOnConflict(table, values, whereClause, whereArgs, CONFLICT_NONE);
    }

在Dao类调用update（）方法

    public void update(){
        SQLiteDatabase db = mHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("age",20);
        db.update(Constants.TABLE_NAME,values,null,null);
        db.close();
    }

• delete（）

    /**
     * Convenience method for deleting rows in the database.
     *
     * @param table the table to delete from
     * @param whereClause the optional WHERE clause to apply when deleting.
     *            Passing null will delete all rows.
     * @param whereArgs You may include ?s in the where clause, which
     *            will be replaced by the values from whereArgs. The values
     *            will be bound as Strings.
     * @return the number of rows affected if a whereClause is passed in, 0
     *         otherwise. To remove all rows and get a count pass "1" as the
     *         whereClause.
     */
    public int delete(String table, String whereClause, String[] whereArgs) {
        acquireReference();
        try {
            SQLiteStatement statement =  new SQLiteStatement(this, "DELETE FROM " + table +
                    (!TextUtils.isEmpty(whereClause) ? " WHERE " + whereClause : ""), whereArgs);
            try {
                return statement.executeUpdateDelete();
            } finally {
                statement.close();
            }
        } finally {
            releaseReference();
        }
    }

在Dao类中调用delete（）方法

        SQLiteDatabase db = mHelper.getWritableDatabase();
/*        String sql = "delete from "+Constants.TABLE_NAME+" where age = 60";
        db.execSQL(sql);*/
        int delete = db.delete(Constants.TABLE_NAME, null, null);
        Log.d(TAG,"detele_result = "+delete);
        db.close();

• query（）

    /**
     * Query the given URL, returning a {@link Cursor} over the result set.
     *
     * @param distinct true if you want each row to be unique, false otherwise.
     * @param table The table name to compile the query against.
     * @param columns A list of which columns to return. Passing null will
     *            return all columns, which is discouraged to prevent reading
     *            data from storage that isn't going to be used.
     * @param selection A filter declaring which rows to return, formatted as an
     *            SQL WHERE clause (excluding the WHERE itself). Passing null
     *            will return all rows for the given table.
     * @param selectionArgs You may include ?s in selection, which will be
     *         replaced by the values from selectionArgs, in order that they
     *         appear in the selection. The values will be bound as Strings.
     * @param groupBy A filter declaring how to group rows, formatted as an SQL
     *            GROUP BY clause (excluding the GROUP BY itself). Passing null
     *            will cause the rows to not be grouped.
     * @param having A filter declare which row groups to include in the cursor,
     *            if row grouping is being used, formatted as an SQL HAVING
     *            clause (excluding the HAVING itself). Passing null will cause
     *            all row groups to be included, and is required when row
     *            grouping is not being used.
     * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause
     *            (excluding the ORDER BY itself). Passing null will use the
     *            default sort order, which may be unordered.
     * @param limit Limits the number of rows returned by the query,
     *            formatted as LIMIT clause. Passing null denotes no LIMIT clause.
     * @return A {@link Cursor} object, which is positioned before the first entry. Note that
     * {@link Cursor}s are not synchronized, see the documentation for more details.
     * @see Cursor
     */
    public Cursor query(boolean distinct, String table, String[] columns,
            String selection, String[] selectionArgs, String groupBy,
            String having, String orderBy, String limit) {
        return queryWithFactory(null, distinct, table, columns, selection, selectionArgs,
                groupBy, having, orderBy, limit, null);
    }

在Dao类调用Query（）方法

 public void query(){
        SQLiteDatabase db = mHelper.getWritableDatabase();
      /*String sql = "select * from "+Constants.TABLE_NAME;
        Cursor cursor = db.rawQuery(sql, null);
	  */
        Cursor cursor1 = db.query(false, Constants.TABLE_NAME, null, null, null, null, null, null, null);
        while (cursor1.moveToNext()){
            int index = cursor1.getColumnIndex("name");
            String name = cursor1.getString(index);
            Log.d(TAG,"name = "+name);
        }
        cursor1.close();
        db.close();

    }

5. 数据库的事务

特点：安全性、高效性

安全性：银行转账的例子。

//不开启事务，会出现一方扣钱，一方未加钱的事情发生。
//开启事务，要么成功转账，要么失败后金额不变
public void testUpdate(){
	DatabaseHelper helper = new DatabaseHelper(getContext());
	SQLiteDatabase db = helper.getReadableDatabase();
	 //开始事务
	 db.beginTransaction();
	 try{
	 	db.execSQL("update account set money =10000-12 where name = 'company'");
	 	//故意让此处发生异常
	 	int i = 10/0；
	 	db.execSQL("update account set money = 10+12 where name = 'Tom'");
	 	db.setTransactionSuccessful();
	 }catch(Exception e){
	 	//处理异常
	 	throw new RuntimeException("发生了异常");
	 }finally{
	 	db.endTransaction();
	 	db.close();
	 }
}

高效性:同样是插入6000条数据,看时间

//正常插入数据的时间：15204ms
public void TestInsert(){
	DatabaseHelper helper = new DatabaseHelper(getContext());
	SQLiteDatabase db = helper.getReadableDatabase();
	long start = System.currentTimeMillis();
	db.beginTransaction();
	for(int i = 0 ;i < 3000;i++){
		db.insert("insert into account values (1,'company','100000')");
		db.insert("insert into account values (1,'Tom','10')");
	}
	db.endTransaction();
	long end = System.currentTimeMillis();
	Log.d("Test","耗时为："+(end-start))
}


//开启事务后插入数据的耗时：218ms
public void TestInsert(){
	DatabaseHelper helper = new DatabaseHelper(getContext());
	SQLiteDatabase db = helper.getReadableDatabase();
	long start = System.currentTimeMillis();
	for(int i = 0 ;i < 3000;i++){
		db.insert("insert into account values (1,'company','100000')");
		db.insert("insert into account values (1,'Tom','10')");
	}
	long end = System.currentTimeMillis();
	Log.d("Test","耗时为："+(end-start))
}

仅为学习过程中的记录，方便自己以后查阅，也希望能帮助到每一位查阅者。