Sqlite数据库操作类

Sqlite数据库操作类

1 SQLiteOpenHelper

2 SQLiteDatabase

3 Cursor

实例

SQLiteOpenHelper工具类

package com.example.week3_day4_sqlopenhelper.db;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteOpenHelper;import android.util.Log;/** * 1 建立一个MySqliteHelper类,继承SQLiteOpenHelper,并实现方法 * 2 重写方法 */public class MySqliteHelper extends SQLiteOpenHelper {// 数据库名称private static String NAME = "info.db";// 版本号private static int VERSION = 1;/** * 构造方法 *  * @param context *            :上下文 * @param name *            :数据库名称 * @param factory *            :游标工厂 * @param version *            :数据库版本 */public MySqliteHelper(Context context, String name, CursorFactory factory,int version) {super(context, name, factory, version);}// 改写构造方法public MySqliteHelper(Context context) {super(context, NAME, null, VERSION);}/** * 数据库第一次创建时,回调此方法 */@Overridepublic void onCreate(SQLiteDatabase db) {// 创建数据库表String sql = "create table person(id integer primary key,name varchar(16),age integer)";// 执行数据库语句db.execSQL(sql);}/** * 数据库升级 数据版本发生改变时调用,newVersion>oldVersion */@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {if(newVersion>oldVersion){Log.i("===onUpgrade===", "数据库版本升级");}}/** * 数据库版本降级,只有数据库版本发生重大错误时,数据版本发生改变时调用,newVersion<oldVersion */@Overridepublic void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {super.onDowngrade(db, oldVersion, newVersion);if(newVersion<oldVersion){Log.i("===onDowngrade===", "数据库版本降级");}}/** * 每次打开数据库时调用,主要是验证数据库是否打开 */@Overridepublic void onOpen(SQLiteDatabase db) {super.onOpen(db);Log.i("===onOpen===", "数据库打开");}}

数据库常用操作

package com.example.week3_day4_sqlopenhelper;import com.example.week3_day4_sqlopenhelper.db.MySqliteHelper;import android.os.Bundle;import android.app.Activity;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.util.Log;import android.view.View;public class MainActivity extends Activity {//声明数据库private MySqliteHelper helper;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_main);        //创建数据库实例        helper=new MySqliteHelper(MainActivity.this);    }    /**     * getWritableDatabase()     * getReadableDatabase()     * 磁盘未满时他们两个一样,都可以读写     * 磁盘满时,getWritableDatabase报错,打开失败,getReadableDatabase则变成只读状态     */    public void onClickBtn(View v){    switch (v.getId()) {case R.id.btn1://创建数据库SQLiteDatabase db = helper.getWritableDatabase();db.close();break;case R.id.btn_insert://获取数据库SQLiteDatabase db1 = helper.getReadableDatabase();//插入语句String str1="insert into person values (1,'张大宝',23)";String str2="insert into person values (2,'张小宝',26)";String str3="insert into person values (3,'张包宝',29)";//执行插入语句db1.execSQL(str1);db1.execSQL(str2);db1.execSQL(str3);//批量插入for (int i = 4; i < 10; i++) {String str4="insert into person values ("+i+",'张包宝"+i+"',29)";db1.execSQL(str4);}break;case R.id.btn_query:SQLiteDatabase db2 = helper.getWritableDatabase();String str4="select * from person";//指定查询,返回一个游标Cursor cursor = db2.rawQuery(str4, null);//判断下一条语句是否存在while(cursor.moveToNext()){//查询第一列int id=cursor.getInt(0);String name=cursor.getString(1);//查询第二列int age=cursor.getInt(2);//查询第三列Log.i("----------------",id+""+age+name);}db2.close();break;//更新数据库case R.id.btn_update:SQLiteDatabase db3 = helper.getReadableDatabase();String sql4="update person set name='heihei' where id=2";db3.execSQL(sql4);db3.close();break;//删除数据case R.id.btn_delete:SQLiteDatabase db4 = helper.getReadableDatabase();String str5="delete from person where id=3";db4.execSQL(str5);db4.close();break;default:break;}    }}

API提供数据库操作

package com.example.week3_day4_sqlopenhelper.db;import com.example.week3_day4_sqlopenhelper.R;import android.app.Activity;import android.content.ContentValues;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.util.Log;import android.view.View;import android.widget.Toast;public class SqliteApiActivity extends Activity {private MySqliteHelper helper;@Overrideprotected void onCreate(Bundle savedInstanceState) {// TODO Auto-generated method stubsuper.onCreate(savedInstanceState);setContentView(R.layout.activity_main);helper = new MySqliteHelper(SqliteApiActivity.this);}public void onClickBtn(View v) {switch (v.getId()) {case R.id.btn1:// 创建数据库SQLiteDatabase db1 = helper.getReadableDatabase();db1.close();break;case R.id.btn_insert:SQLiteDatabase db2 = helper.getReadableDatabase();ContentValues values = new ContentValues();values.put("id", 1);values.put("name", "小灰灰");values.put("age", 23);/** * 参数1：表名 参数2 ：如果添加的数据是null null 参数3：添加的数据 */db2.insert("person", null, values);values = new ContentValues();values.put("id", 6);values.put("name", "小香香");values.put("age", 24);db2.insert("person", null, values);db2.close();break;case R.id.btn_query:SQLiteDatabase db3 = helper.getReadableDatabase();/** * 参数1：表名 参数2：字段数组 参数3：查询的条件 _id = ? 参数4：查询条件的值 new String{"1"} * 参数5：分组字段 参数6：在where条件后再次筛选 参数7：查询排序 *  */Cursor cursor = db3.query("person", null, null, null, null, null,null);while (cursor.moveToNext()) {//getColumnIndex获取指定列的索引int id = cursor.getInt(cursor.getColumnIndex("id"));String name = cursor.getString(cursor.getColumnIndex("name"));int age = cursor.getInt(cursor.getColumnIndex("age"));Log.i("--------------", id + "," + name + "," + age);}db3.close();break;case R.id.btn_update:SQLiteDatabase db4 = helper.getReadableDatabase();ContentValues values2 = new ContentValues();values2.put("name", "fff");/** * 参数1: 表名 参数2：要修改的值 参数3：where 条件语句 _id = ? 参数4：条件语句的值 new String * []{"1"} */int num = db4.update("person", values2, "id=?",new String[] { "1" });if (num > 0) {Toast.makeText(getApplicationContext(), "修改成功",Toast.LENGTH_SHORT).show();}else{Toast.makeText(getApplicationContext(), "修改失败",Toast.LENGTH_SHORT).show();}db4.close();break;case R.id.btn_delete:SQLiteDatabase db5 = helper.getReadableDatabase();/** * 参数1：数据库表名 * 参数2：where 条件语句  _id = ? * 参数3： 条件语句的值   new String []{"1"} */int num1 = db5.delete("person", "id = ?", new String[]{1+""});if(num1>0){Toast.makeText(getApplicationContext(), "删除成功",Toast.LENGTH_SHORT).show();}else{Toast.makeText(getApplicationContext(), "删除失败",Toast.LENGTH_SHORT).show();}db5.close();break;default:break;}}}

布局文件

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"    xmlns:tools="http://schemas.android.com/tools"    android:layout_width="match_parent"    android:layout_height="match_parent"    android:orientation="vertical" >    <Button        android:id="@+id/btn1"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:onClick="onClickBtn"        android:text="创建数据库 " />    <Button        android:id="@+id/btn_insert"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:onClick="onClickBtn"        android:text="添加数据 " />    <Button        android:id="@+id/btn_query"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:onClick="onClickBtn"        android:text="查询数据 " />     <Button        android:id="@+id/btn_update"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:onClick="onClickBtn"        android:text="更新数据 " />         <Button        android:id="@+id/btn_delete"        android:layout_width="match_parent"        android:layout_height="wrap_content"        android:onClick="onClickBtn"        android:text="删除数据" /></LinearLayout>