手机数据的增删改查及版本升级
.xml
<?xml version="1.0" encoding="utf-8"?> <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:paddingBottom="@dimen/activity_vertical_margin" android:paddingLeft="@dimen/activity_horizontal_margin" android:paddingRight="@dimen/activity_horizontal_margin" android:paddingTop="@dimen/activity_vertical_margin" tools:context="com.hanqi.test5.dataActivity2" android:orientation="vertical"> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="创建数据库" android:id="@+id/bt_11" android:onClick="bt11_onClick"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="升级数据库" android:id="@+id/bt_12" android:onClick="bt12_onClick"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="增加数据" android:id="@+id/bt_13" android:onClick="bt13_onClick"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="删除数据" android:id="@+id/bt_14" android:onClick="bt14_onClick"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="修改数据" android:id="@+id/bt_15" android:onClick="bt15_onClick"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="查询数据" android:id="@+id/bt_16" android:onClick="bt16_onClick"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="事务操作" android:id="@+id/bt_17" android:onClick="bt17_onClick"/> </LinearLayout>
.java
package com.hanqi.test5; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.os.Bundle; import android.support.v7.app.AppCompatActivity; import android.util.Log; import android.view.View; import android.widget.Toast; public class dataActivity2 extends AppCompatActivity { @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_data2); } //继承SQLiteOpenHelper class DBHelper extends SQLiteOpenHelper { //构造方法 //name 数据库名 //version 创建的数据库版本号 public DBHelper(String name, int version) { //写在第一行 super(dataActivity2.this, name, null, version); } //回调方法 //在创建数据库时调用 //什么时候创建数据库：连接数据库的时候如果数据文件不存在 //只调用一次 @Override public void onCreate(SQLiteDatabase db) { //1.创建数据库 String creatTable = "create table user (_id integer PRIMARY KEY AUTOINCREMENT NOT NULL,name varchar,age int)"; db.execSQL(creatTable); //2.初始化数据 ContentValues cv = new ContentValues(); cv.put("name","元芳"); cv.put("age", 139); //如果不成功会返回 —1 //1-表名 //2-空列的默认值 //3-字段和值的key/value集合 long l = db.insert("user",null,cv); Toast.makeText(dataActivity2.this, "id =" + l, Toast.LENGTH_SHORT).show(); } //升级数据库 @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { //执行升级 //update user set age = 30 where _id = ? //? 是占位符，然后给占位符赋值 ContentValues cv = new ContentValues(); cv.put("age", 14); int rowcount = db.update("user", cv, "_id=? and name=?", new String[]{"1", "tpm"}); Log.e("TAG", "rowcount=" + rowcount); } } //创建数据库 public void bt11_onClick(View v) { //创建 DBHelper dh = new DBHelper("test.db",1); //获取数据库实例 SQLiteDatabase sd = dh.getWritableDatabase(); sd.close(); } //升级数据库 public void bt12_onClick(View v) { //创建 实现工具类 DBHelper dh = new DBHelper("test.db",2); //获取数据库实例 SQLiteDatabase sd = dh.getWritableDatabase(); sd.close(); } //插入数据 public void bt13_onClick(View v) { //创建 DBHelper dh = new DBHelper("test.db",2); //获取数据库实例 SQLiteDatabase sd = dh.getWritableDatabase(); ContentValues cv = new ContentValues(); cv.put("name","吴元芳"); cv.put("age", 19); long _id = sd.insert("user", null, cv); Log.e("TAG","_id ="+ _id); sd.close(); } //查询 public void bt16_onClick(View v) { //创建 DBHelper dh = new DBHelper("test.db",2); //获取数据库实例 SQLiteDatabase sd = dh.getWritableDatabase(); //全表查询 //返回游标 Cursor cursor = sd.query("user", null, null, null, null, null, null); //Cursor一开始会定位在第一条数据的上方 //移动游标到数据的上面，提取数据后，再继续移动 while (cursor.moveToNext()) { long _id = cursor.getInt(cursor.getColumnIndex("_id")); String name = cursor.getString(1); int age = cursor.getInt(2); Log.e("TAG","id ="+_id +"name ="+name+"age ="+age); cursor.close(); sd.close(); } } //修改 public void bt15_onClick(View v) { //创建 DBHelper dh = new DBHelper("test.db",2); //获取数据库实例 SQLiteDatabase sd = dh.getWritableDatabase(); ContentValues cv = new ContentValues(); cv.put("name","吴元芳"); int count = sd.update("user", cv, "_id >= 3", null); Log.e("TAG","updatecount ="+ count); sd.close(); } //删除 public void bt14_onClick(View v) { //创建 DBHelper dh = new DBHelper("test.db",2); //获取数据库实例 SQLiteDatabase sd = dh.getWritableDatabase(); int count = sd.delete("user", "_id = 3", null); Log.e("TAG","deletecount ="+ count); sd.execSQL("delete from user where _id = 5"); sd.close(); } //事务操作 public void bt17_onClick(View v) { //创建 DBHelper dh = new DBHelper("test.db",2); //获取数据库实例 SQLiteDatabase sd = dh.getWritableDatabase(); //连接 //sd = SQLiteDatabase.openOrCreateDatabase("test.db",null); //判断是否建表了，是否升级了 try { //一·开启事务 sd.beginTransaction(); ContentValues cv = new ContentValues(); cv.put("age", "45"); int count = sd.update("user", cv, "_id = 3", null); //抛出异常 boolean b = true; if (b) { throw new RuntimeException("出现异常了"); } count += sd.update("user", cv, "_id = 6", null); Log.e("TAG", "updatecount =" + count); //二·设置事务执行成功 sd.setTransactionSuccessful(); } catch (Exception e) { e.printStackTrace(); Toast.makeText(dataActivity2.this, e.getMessage(), Toast.LENGTH_SHORT).show(); } finally { //三·结束事务，1）提交；2）回滚； sd.endTransaction(); sd.close(); } } }
操作效果图
