Android开发之创建数据库_万卷书情似故人_android 创建数据库

网络投稿 02-07 6984

前言：在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)) }

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