.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.application3.DataActivity2" android:orientation="vertical"> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="创建数据库" android:id="@+id/bt_bt1" android:onClick="bt_bt1onClick"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="升级数据库" android:id="@+id/bt_bt2" android:onClick="bt_bt2onClick"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="增加数据" android:id="@+id/bt_bt3" android:onClick="bt_bt3onClick"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="查询数据" android:id="@+id/bt_bt4" android:onClick="bt_bt4onClick"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="修改数据" android:id="@+id/bt_bt5" android:onClick="bt_bt5onClick"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="删除数据" android:id="@+id/bt_bt6" android:onClick="bt_bt6onClick"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="事务操作" android:id="@+id/bt_bt7" android:onClick="bt_bt7onClick"/> </LinearLayout>
.java
package com.hanqi.application3; 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 user1 (_id integer PRIMARY KEY AUTOINCREMENT NOT NULL,name varchar,age int)"; db.execSQL(creatTable); //2.初始化数据 ContentValues cv= new ContentValues(); cv.put("name","tom"); cv.put("age", 30); //如果不成功返回-1 //1.第一个参数是表名，第二个参数空列的默认值，第三个字段和要插入值key/value得集合 long l = db.insert("user1",null,cv); Toast.makeText(DataActivity2.this, "id="+l, Toast.LENGTH_SHORT).show(); } //升级数据库 @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { //执行升级 // update user1 set age = 30 where _id = ? //? 是占位符，然后给占位符赋值 ContentValues cv= new ContentValues(); cv.put("age", 30); int rowcount = db.update("user1",cv,"_id=? and name=?",new String[]{"1","tom"}); Log.e("TAG","rowcount = "+rowcount); } } //创建数据库 public void bt_bt1onClick(View v) { //创建 DBHelper dh = new DBHelper("test.db",1); //获取数据库实例 SQLiteDatabase sdd = dh.getWritableDatabase(); sdd.close(); } //升级数据库 public void bt_bt2onClick(View v) { //创建 DBHelper dh = new DBHelper("test.db",2); //获取数据库实例 SQLiteDatabase sdd = dh.getWritableDatabase(); sdd.close(); } //插入数据 public void bt_bt3onClick(View v) { //创建 DBHelper dh = new DBHelper("test.db",2); //获取数据库实例 SQLiteDatabase sdd = dh.getWritableDatabase(); ContentValues cv = new ContentValues(); cv.put("name","tom1"); cv.put("age", 15); long _id = sdd.insert("user1", null, cv); Log.e("TAG","_id= "+_id); sdd.close(); } //查询数据库 public void bt_bt4onClick(View v) { //创建 DBHelper dh = new DBHelper("test.db",2); //获取数据库实例 SQLiteDatabase sdd = dh.getWritableDatabase(); // 全表查询 //返回游标 Cursor cursor = sdd.query("user1", null, null, null, null, null, null); //Cursor 一开始会定位在第一条数据的上方 // 移动游标到数据的上面，提取数据后，再继续移动 while (cursor.moveToNext()) { long _id =cursor.getLong(cursor.getColumnIndex("_id")); String name = cursor.getString(1); int age = cursor.getInt(2); Log.e("TAG","_id = "+_id+"name = "+name+"age= "+age); } cursor.close(); sdd.close(); } //修改数据 public void bt_bt5onClick(View v) { //创建 DBHelper dh = new DBHelper("test.db",2); //获取数据库实例 SQLiteDatabase sdd = dh.getWritableDatabase(); ContentValues cv = new ContentValues(); cv.put("name", "tom3"); int count = sdd.update("user1",cv,"_id >= 3",null); Log.e("TAG", "Updatecount= " + count); sdd.close(); } //删除数据 public void bt_bt6onClick(View v) { //创建 DBHelper dh = new DBHelper("test.db",2); //获取数据库实例 SQLiteDatabase sdd = dh.getReadableDatabase(); int count = sdd.delete("user1","_id = 4",null); Log.e("TAG", "Deletcount= " + count); sdd.execSQL("delect from user1 where _id=3"); sdd.close(); } public void bt_bt7onClick(View v) { //创建 DBHelper dh = new DBHelper("test.db", 2); //获取数据库实例 SQLiteDatabase sdd = dh.getWritableDatabase(); //链接 sdd = SQLiteDatabase.openOrCreateDatabase("test.db",null); //判断是否建表了，是否升级了 try { //1.开启事务 sdd.beginTransaction(); ContentValues cv = new ContentValues(); cv.put("age", "40"); int count = sdd.update("user1", cv, "_id = 1", null); //抛出异常 boolean b = true; if (b) { //throw new RuntimeException("出现异常"); } count += sdd.update("user1", cv, "_id = 2", null); Log.e("TAG", "Updatecount= " + count); //2.设置事务执行成功 sdd.setTransactionSuccessful(); //提交 } catch (Exception e) { //回滚 e.printStackTrace(); Toast.makeText(DataActivity2.this, "异常信息"+e.getMessage(), Toast.LENGTH_SHORT).show(); } //一定会被执行的代码 finally { //3.结束事务,1)提交 2）回滚; sdd.endTransaction(); sdd.close(); } } }