package com.hanqi.testapp3; 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.EditText; import android.widget.Toast; public class TestActivity2 extends AppCompatActivity { EditText et_id, et_name,et_sex,et_age; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_test2); et_id=(EditText)findViewById(R.id.et_id); et_name=(EditText)findViewById(R.id.et_name); et_sex=(EditText)findViewById(R.id.et_sex); et_age=(EditText)findViewById(R.id.et_age); } //初始化数据库 public void bt1_OnClick(View v) { //使用工具类得到数据库连接对象 MYDBHelper mydbHelper=new MYDBHelper("test.db",1); //得到连接 SQLiteDatabase sd= mydbHelper.getWritableDatabase(); Toast.makeText(TestActivity2.this, "连接数据库成功", Toast.LENGTH_SHORT).show(); //关闭连接 sd.close(); } public void bt2_OnClick(View v) { //使用工具类得到数据库连接对象 MYDBHelper mydbHelper=new MYDBHelper("test.db",3); //得到连接 SQLiteDatabase sd= mydbHelper.getWritableDatabase(); Toast.makeText(TestActivity2.this, "升级成功", Toast.LENGTH_SHORT).show(); //关闭连接 sd.close(); } //插入新数据 public void bt3_OnClick(View v) { //1.连接数据库,得到数据库连接对象 SQLiteDatabase sd= new MYDBHelper("test.db",2).getWritableDatabase(); //2.准备数据 ContentValues cv=new ContentValues(); cv.put("name",et_name.getText().toString()); cv.put("sex",et_sex.getText().toString()); cv.put("age",et_age.getText().toString()); //3.调用insert（） long l=sd.insert("t_user", null, cv); Toast.makeText(TestActivity2.this, "插入数据的主键="+l, Toast.LENGTH_SHORT).show(); //4.关闭连接 sd.close(); } //数据查询 public void bt4_OnClick(View v) { //1.连接数据库,得到数据库连接对象 SQLiteDatabase sd= new MYDBHelper("test.db",2).getWritableDatabase(); //2.全表全字段查询 Cursor c=sd.query("t_user", null, null, null, null, null, null); //3.遍历结果集 while (c.moveToNext()) { //读取数据 String str="_id="+c.getString(0)+"name=" +c.getString(1)+"sex"+c.getString(2)+ "age"+c.getString(c.getColumnIndex("age")); Log.e("TAG",str); } Toast.makeText(TestActivity2.this, "查询结果集的记录条数"+c.getCount(), Toast.LENGTH_SHORT).show(); c.close(); //4.关闭连接 sd.close(); } //带条件数据查询 public void bt5_OnClick(View v) { //1.连接数据库,得到数据库连接对象 SQLiteDatabase sd= new MYDBHelper("test.db",2).getWritableDatabase(); String strWhere=" 1=1 ";//添加一个永远为真的条件再查询 // select * from t_user where 1=1 and _id=1 and name like '%张%' and sex='' //得到条件 if (et_id.getText().length()>0) { strWhere+=" and _id= "+et_id.getText().toString(); } if (et_name.getText().length()>0) { strWhere+=" and name like '%"+et_name.getText().toString()+"%'"; } if (et_sex.getText().length()>0) { strWhere+=" and sex = '%"+et_sex.getText().toString()+"%'"; } if (et_age.getText().length()>0) { strWhere+=" and age ="+et_age.getText().toString()+""; } //2.查询 Cursor c=sd.query("t_user", null, strWhere, null, null, null, null); //3.遍历结果集 while (c.moveToNext()) { //读取数据 String str="_id="+c.getString(0)+"name=" +c.getString(1)+"sex"+c.getString(2)+ "age"+c.getString(c.getColumnIndex("age")); Log.e("TAG",str); } Toast.makeText(TestActivity2.this, "查询结果集的记录条数"+c.getCount(), Toast.LENGTH_SHORT).show(); c.close(); //4.关闭连接 sd.close(); } //修改数据 public void bt6_OnClick(View v) { //1.连接数据库,得到数据库连接对象 SQLiteDatabase sd= new MYDBHelper("test.db",2).getWritableDatabase(); //2.修改 ContentValues cv=new ContentValues(); cv.put("name",et_name.getText().toString()); cv.put("sex", et_sex.getText().toString()); cv.put("age", et_age.getText().toString()); int i=sd.update("t_user", cv, " _id = ? ", new String[]{et_id.getText().toString()}); Toast.makeText(TestActivity2.this, "修改的记录条数="+i, Toast.LENGTH_SHORT).show(); //4.关闭连接 sd.close(); } //删除数据 public void bt7_OnClick(View v) { //1.连接数据库,得到数据库连接对象 SQLiteDatabase sd= new MYDBHelper("test.db",2).getWritableDatabase(); //2.删除 int i=sd.delete("t_user", "_id=?", new String[]{et_id.getText().toString()}); Toast.makeText(TestActivity2.this, "修改的记录条数="+i, Toast.LENGTH_SHORT).show(); //3.关闭连接 sd.close(); } //修改数据 public void bt8_OnClick(View v) { SQLiteDatabase sd = new MYDBHelper("test.db", 2).getWritableDatabase(); try { //1.连接数据库,得到数据库连接对象 //启动事务 sd.beginTransaction(); //2.修改 ContentValues cv1 = new ContentValues(); cv1.put("age", 41); //修改1 int i = sd.update("t_user", cv1, "_id=1", null); //抛出异常 // if (i > 0) { // throw new RuntimeException("模拟事务异常"); // } ContentValues cv2 = new ContentValues(); cv2.put("age", 51); //修改2 int k = sd.update("t_user", cv2, "_id=2", null); //设置事务是成功完成的 sd.setTransactionSuccessful(); Toast.makeText(TestActivity2.this, "修改的记录条数=" + (i + k), Toast.LENGTH_SHORT).show(); }catch (Exception e) { Toast.makeText(TestActivity2.this, "修改出错", Toast.LENGTH_SHORT).show(); }finally { //一定会被执行的代码 //结束事务 //1.没有异常，提交事务 //2.发生异常，回滚事务 sd.endTransaction(); sd.close(); } } //内部类 class MYDBHelper extends SQLiteOpenHelper { //构造方法 public MYDBHelper(String dbname,int ver) { //显示调用继承父类的构造方法 //必须在第一行 super(TestActivity2.this,dbname,null,1); } //创建和初始化数据库 @Override public void onCreate(SQLiteDatabase db) { // 1.执行创建数据库的语句 String sql="CREATE TABLE t_user(_id INTEGER PRIMARY KEY AUTOINCREMENT " + "NOT NULL,name VARCHAR NOT NULL,sex CHAR,age INTEGER);"; db.execSQL(sql); Log.e("TAG","表创建成功"); //2.执行初始化数据的语句,insert语句 ContentValues cv=new ContentValues(); cv.put("name","张三"); cv.put("sex","男"); cv.put("age", 20); //执行插入，返回主键 long l=db.insert("t_user",null,cv); Log.e("TAG","初始化数据="+l); } //升级数据库 //触发条件：当版本号增大 @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { //修改数据 if (newVersion==3) { ContentValues cv=new ContentValues(); cv.put("name","李五"); String sql="update t_user set name='李四' where _id=1"; String[] str={"1","18"}; //调用db的更新方法 int i=db.update("t_user", cv, "_id = ? and age > ?" ,str); Log.e("TAG","升级数据 数据条数="+i); } } } }
<?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.testapp3.TestActivity2" android:orientation="vertical"> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="初始化数据库" android:onClick="bt1_OnClick" /> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="升级数据库" android:onClick="bt2_OnClick" /> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content"> <EditText android:layout_width="0dp" android:layout_height="match_parent" android:layout_weight="1" android:id="@+id/et_id" android:hint="id"/> <EditText android:layout_width="0dp" android:layout_height="match_parent" android:layout_weight="1" android:id="@+id/et_name" android:hint="名称"/> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="wrap_content"> <EditText android:layout_width="0dp" android:layout_height="match_parent" android:layout_weight="1" android:id="@+id/et_sex" android:hint="性别"/> <EditText android:layout_width="0dp" android:layout_height="match_parent" android:layout_weight="1" android:id="@+id/et_age" android:hint="年龄"/> </LinearLayout> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="新增数据" android:onClick="bt3_OnClick" /> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="查询全部数据" android:onClick="bt4_OnClick" /> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="带条件查询数据" android:onClick="bt5_OnClick" /> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="修改数据" android:onClick="bt6_OnClick" /> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="删除数据" android:onClick="bt7_OnClick" /> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:text="数据库" android:onClick="bt7_OnClick" /> </LinearLayout>