第十二章：Android数据存储（下）
16lz
2021-01-23
一、SQLite介绍
提到数据存储问题，数据库是不得不提的。数据库是用来存储关系型数据的不二利器。Android为开发者提供了强大的数据库支持，可以用来轻松地构造基于数据库的应用。Android的数据库应用，依托于当下最流行的开源嵌入式数据库SQLite。在Android中，应用的数据库文件是该应用私有的，存储在应用数据目录下的databases子目录内。从代码结构来看，Android的数据库实现可以分成两个层次，在底层通过C++调用SQLite的接口来执行SQL语句，并通过JNI向上暴露Java可访问的接口。
SQLite可视化工具：SQLite Expert Professional 3；
二、Android访问SQLite
主要步骤：
(1)创建某个类继承SQLiteOpenHelper,并重写 -- 带Context参数的构造函数; //用来创建数据库 -- onCreate(SQLiteDatabase db); //在创建数据库时调用 -- onUpgrade(SQLiteDatabase db,int old,int newversion); //数据库版本更改时调用 (2)创建数据库：SQLiteDatabase db = helper.getWritableDatabase(); (3)SQL语句：db.execSQL(String sql); 语句使用：1.打开数据库
SQLiteDatabase db = helper.getWritableDatabase(); SQLiteDatabase db = helper.getReadableDatabase(); //此函数内部其实也调用了getWritableDatabase()函数，即调用了此函数，也可以写数据2.insert语句
(1)db.execSQL("insert into person(name,age) values(?,?)",new Object[]{"xiazdong",20}); (2)ContentValues values = new ContentValues();values.put("name","xiazdong");values.put("age",20);db.insert("tablename",null,values); 3.delete语句
(1)db.execSQL("delete from person where id=?",new Object[]{id}); (2)db.delete("tablename","id=?",new String[]{id+""});4.update语句
(1)db.exeSQL("update person set age=? where name=?",new Object[]{30,"xiazdong"}); (2) ContentValues values = new ContentValues();values.put("age",30);/*set子句*/db.update("tablename",values,"name=?"/*where子句*/,new String[]{"xiazdong"}/*where子句参数*/); 5.query语句
(1)Cursor cursor = db.rawQuery("select * from person where name=?",new Object[]{"xiazdong"});while(cursor.moveToNext()){ //int index = cursor.getColumnIndex(String name);//根据name获得索引 //String name = cursor.getString(int index); //根据索引获得值 String name = cursor.getString(cursor.getColumnIndex("name")); } (2) Cursor cursor = db.query("tablename",null /*表示select * */,"name=?" /*where语句*/,new String[]{"xiazdong"},null/*group by 语句*/,null/*having 语句*/,null/*order by语句*/,null/*limit 语句*/); 6.分页语句
Cursor cursor = db.rawQuery("select * from person limit ?,?",new Object[]{5,5}); //第一个5表示跳过5条记录，下一个5为查询结果的记录个数 while(cursor.moveToNext()){ String name = cursor.getString(cursor.getColumnIndex("name")); }7.获得记录个数语句
(1)Cursor cursor = db.rawQuery("select count(*) from person", null);cursor.moveToFirst();int count = cursor.getInt(0); (2) db.query("person",new String[]{"count(*)"},null,null,null,null,null,null); 示例代码： MySQLiteOpenHelper.java: 
package com.example.helloandroid;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.database.sqlite.SQLiteDatabase.CursorFactory;public class MySQLiteOpenHelper extends SQLiteOpenHelper { public MySQLiteOpenHelper(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 }}View Code
DataBaseActivity.java
package com.example.helloandroid;import android.app.Activity;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.os.Bundle;import android.view.Menu;import android.view.MenuItem;import android.view.View;import android.view.View.OnClickListener;import android.widget.Button;import android.widget.Toast;public class DataBaseActivity extends Activity { private Button createDatabase, createTable, selectData, insertData, updateData, deleteDaata; private MySQLiteOpenHelper helper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_data_base); // 初始化按钮 createDatabase = (Button) findViewById(R.id.createDatabase); createTable = (Button) findViewById(R.id.createTable); selectData = (Button) findViewById(R.id.selectData); insertData = (Button) findViewById(R.id.insertData); updateData = (Button) findViewById(R.id.updateData); deleteDaata = (Button) findViewById(R.id.deleteDaata); helper = new MySQLiteOpenHelper(DataBaseActivity.this, "mydb.db", null, 1); /* * 创建数据库 */ createDatabase.setOnClickListener(new OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub SQLiteDatabase sqlitedatabase = helper.getWritableDatabase(); Toast.makeText(DataBaseActivity.this, "数据库创建成功", 1000).show(); } }); /* * 创建表 */ createTable.setOnClickListener(new OnClickListener() { public void onClick(View arg0) { // TODO Auto-generated method stub SQLiteDatabase sqlitedatabase = helper.getWritableDatabase(); // 创建表 sqlitedatabase .execSQL("create table student(id INTEGER PRIMARY KEY autoincrement,name text)"); Toast.makeText(DataBaseActivity.this, "表创建成功", 1000).show(); } }); /* * 插入数据 */ insertData.setOnClickListener(new OnClickListener() { public void onClick(View v) { // TODO Auto-generated method stub SQLiteDatabase sqlitedatabase = helper.getWritableDatabase(); sqlitedatabase .execSQL("insert into student(name) values ('tinyphp')"); Toast.makeText(DataBaseActivity.this, "插入数据成功", 1000).show(); } }); /* * 更新数据 */ updateData.setOnClickListener(new OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub SQLiteDatabase sqlitedatabase = helper.getWritableDatabase(); sqlitedatabase .execSQL("update student set name='monkey' where id='1'"); Toast.makeText(DataBaseActivity.this, "数据更新成功", 1000).show(); } }); /* * 查询数据 */ selectData.setOnClickListener(new OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub SQLiteDatabase sqlitedatabase = helper.getReadableDatabase(); //参数：表名、查询的列、查询条件、查询参数、查询结果分组、分组结果限制、排序 Cursor cursor = sqlitedatabase.query("student", new String[] { "id", "name" }, "id=?", new String[] { "1" }, null, null, null); String name=null; while(cursor.moveToNext()){ name =cursor.getString(cursor.getColumnIndex("name")); } Toast.makeText(DataBaseActivity.this, "查询结果name为："+name, 1000).show(); } }); /* * 删除数据 * */ deleteDaata.setOnClickListener(new OnClickListener() { @Override public void onClick(View v) { // TODO Auto-generated method stub SQLiteDatabase sqlitedatabase =helper.getWritableDatabase(); sqlitedatabase.delete("student", "id=?", new String[]{"1"}); Toast.makeText(DataBaseActivity.this, "数据删除成功", 1000).show(); } }); }}View Code
activity_data_base.xml
<?xml version="1.0" encoding="utf-8"?><LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" > <Button android:id="@+id/createDatabase" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="创建数据库" /> <Button android:id="@+id/createTable" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="创建表" /> <Button android:id="@+id/insertData" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="插入" /> <Button android:id="@+id/updateData" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="更新" /> <Button android:id="@+id/selectData" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="查询" /> <Button android:id="@+id/deleteDaata" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="删除" /></LinearLayout>View Code
效果图：
更多相关文章
- 基于 android 数据备份恢复的一种实现
- Android开发实践 数据存储 学习、应用、总结
- Android studio 通过以servlet搭建的服务器访问 PC端 mysql数据
- android 连接远程数据库
- Android[中级教程]第八章 Json数据的处理
- (转)Android从服务器端获取数据的几种方法
- 【Android Training - 04】保存数据 [ Lesson 2 - 保存文件]