package com.example.wang.testapp3;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
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.getReadableDatabase();
Toast.makeText(TestActivity2.this, "连接数据库成功", Toast.LENGTH_SHORT).show();
//关闭连接
sd.close();
}
//初始化数据库
public void bt2_OnClick(View v)
{
//使用工具类得到数据库对象
MyDBHelper myDBHelper=new MyDBHelper("test.db",2);
//得到连接
SQLiteDatabase sd = myDBHelper.getReadableDatabase();
Toast.makeText(TestActivity2.this, "连接数据库成功", Toast.LENGTH_SHORT).show();
//关闭连接
sd.close();
}
//插入新数据
public void bt3_OnClick(View v)
{
//1-连接数据库，得到数据库连接对象
SQLiteDatabase sd = new MyDBHelper("test.db",2).getReadableDatabase();
//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).getReadableDatabase();
//2.全表查询
Cursor c=sd.query("t_user", null, null, null, null, null, null);
//3.遍历结果集
while (c.moveToNext())
{
//读取数据
String str="_id="+c.getLong(0)+" name="+c.getString(1)+
" sex="+c.getString(2)+" age="+c.getInt(3);
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).getReadableDatabase();
String strWhere="1=1";
//得到条件
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.getLong(0)+" name="+c.getString(1)+
" sex="+c.getString(2)+" age="+c.getInt(3);
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).getReadableDatabase();
//修改
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).getReadableDatabase();
//2.删除
int i= sd.delete("t_user", "_id=?", new String[]{et_id.getText().toString()});
Toast.makeText(TestActivity2.this, "删除的记录条数="+i, Toast.LENGTH_SHORT).show();
//4.关闭连接
sd.close();
}
//数据库事务
public void bt8_OnClick(View v)
{
SQLiteDatabase sd = new MyDBHelper("test.db",2).getReadableDatabase();
try {
//1.得到连接
sd.beginTransaction();//1-启动事务
//修改
ContentValues cv=new ContentValues();
cv.put("age", "44");
//修改1
int i= sd.update("t_user", cv, "_id=1", null);
//抛出异常
if (i>0)
{
throw new RuntimeException("模拟事务异常");
}
ContentValues cv1=new ContentValues();
cv1.put("age", "54");
//修改2
int k= sd.update("t_user", cv1, "_id=2", null);
Toast.makeText(TestActivity2.this, "修改的记录条数="+(i+k), Toast.LENGTH_SHORT).show();
//设置事务是成功完成的
sd.setTransactionSuccessful();
//结束事务
//1.没有异常，提交事务
//2.发生异常，回滚事务
}
catch (Exception e)
{
e.printStackTrace();
Toast.makeText(TestActivity2.this, "修改出错", Toast.LENGTH_SHORT).show();
}
finally {
//一定会被执行的代码
sd.endTransaction();
//4.关闭连接
sd.close();
}
}
//实现SQLiteOpenHelper的内部类
class MyDBHelper extends SQLiteOpenHelper
{
//构造方法
public MyDBHelper(String dbname,int ver)
{
//显示调用父类的构造方法
//必须在第一行
super(TestActivity2.this,dbname,null,ver);
}
//创建和初始化数据库
@Override
public void onCreate(SQLiteDatabase db) {
//1-执行创建数据库的语句
String sql="CREATE TABLE t_user" + " ("_id" INTEGER AUTOINCREMENT NOT NULL," +
""name" VARCHAR(20) NOT NULL," +
""sex" CHAR(1)," +
""age" INTEGER," +
"PRIMARY KEY ("_id"))";
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==2)
{
ContentValues cv=new ContentValues();
cv.put("name","李四");
String sql="update t_user set name='李四' where _id=1";
//调用数据库连接类的方法
String[] str={"1","18"};
int i=db.update("t_user", cv, "_id=? and age>?",str);
Log.e("TAG","升级数据 数据条数="+i);
}
}
}
}
