Main主页逻辑代码
public class MainActivity extends AppCompatActivity implements View.OnClickListener {
private Button btn_create,btn_add,btn_delete,btn_update,btn_query,btn_renew;
private HandlerDao hd;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
//绑定控件和添加点击事件
findView();
}
private void findView() {
btn_create= (Button) findViewById(R.id.button);
btn_add= (Button) findViewById(R.id.button2);
btn_update= (Button) findViewById(R.id.button3);
btn_query= (Button) findViewById(R.id.button4);
btn_delete= (Button) findViewById(R.id.button5);
btn_renew= (Button) findViewById(R.id.button6);
btn_create.setOnClickListener(this);
btn_add.setOnClickListener(this);
btn_update.setOnClickListener(this);
btn_query.setOnClickListener(this);
btn_delete.setOnClickListener(this);
btn_renew.setOnClickListener(this);
//调用MVP中的P也就是逻辑层代码
hd=new HandlerDaoImpl();
}
@Override
public void onClick(View v) {
String sql="";
Object [] obj=null;
switch (v.getId()){
case R.id.button:
//查看user数据库表是否存在判断是否创建成功!
sql="SELECT COUNT(*) FROM sqlite_master where type='table' and name='user'";
break;
case R.id.button2:
//插入一条测试数据
sql="insert into user(name,age) values(?,?)";
//插入的值
obj=new Object[]{"张三",20};
break;
case R.id.button3:
//根据id修改插入的数据
sql="update user set name=? where _id=?";
obj=new Object[]{"李四",1};
break;
case R.id.button4:
//查询数据库数据
sql="select * from user";
obj=null;
break;
case R.id.button5:
//根据名称删除某条数据
sql="delete from user where name=?";
obj=new Object[]{"李四"};
break;
case R.id.button6:
//更新数据库版本
sql="";
obj=null;
break;
}
//调用实现类完成具体的逻辑操作
hd.handler(v.getId(),this,sql,obj);
}
}
HandDao层代码主要是一个接口
public interface HandlerDao {
public void handler(int num, Context context, String sql, Object[] obj);}
主要的代码逻辑通过Main主页传递过来的sql语句执行相应操作
public class HandlerDaoImpl implements HandlerDao {
//
private Context context;
//数据库表
private UserDB userDB;
@Override
public void handler(int num, Context context, String sql,Object[] obj) {
this.context = context;
if (userDB == null) {
userDB = new UserDB(context, "user.db", null, 1);
}
switch (num) {
case R.id.button:
if (isCreate(sql)) {
showMake("创建成功!");
} else {
showMake("创建失败!");
}
break;
case R.id.button2:
if (isNotQuery(sql,obj)) {
showMake("新增成功!");
} else {
showMake("新增失败!");
}
break;
case R.id.button3:
if (isNotQuery(sql,obj)) {
showMake("修改成功!");
} else {
showMake("修改失败!");
}
break;
case R.id.button4:
int count = findAll(sql);
if (count > 0) {
showMake("总共查询出" + count + "条数据!");
} else if (count == 0) {
showMake("为查找到数据!");
} else {
showMake("有一个未知错误!");
}
break;
case R.id.button5:
if (isNotQuery(sql,obj)) {
showMake("删除成功!");
} else {
showMake("删除失败!");
}
break;
case R.id.button6:
try {
UserDB ud = new UserDB(context, "user.db", null, 2);
SQLiteDatabase sd = userDB.getWritableDatabase();
showMake("更新成功!");
} catch (Exception e) {
Log.d("###", e.getMessage());
showMake("更新失败!");
e.printStackTrace();
}
break;
}
}
private void showMake(String message) {
Toast.makeText(context, message, Toast.LENGTH_LONG).show();
}
//创建数据库
private boolean isCreate(String sql) {
boolean isCreate = false;
try{
SQLiteDatabase sqLiteDatabase = userDB.getWritableDatabase();
Cursor cursor = sqLiteDatabase.rawQuery(sql, null);
while (cursor.moveToNext()) {
int count = cursor.getInt(0);
if (count > 0) {
isCreate = true;
}
}
//关闭访问
sqLiteDatabase.close();
}catch (Exception e){
Log.d("###",e.getMessage());
e.printStackTrace();
}
return isCreate;
}
//新增,修改,删除数据库
private boolean isNotQuery(String sql,Object[] obj) {
SQLiteDatabase sqLiteDatabase = null;
try {
sqLiteDatabase = userDB.getWritableDatabase();
sqLiteDatabase.execSQL(sql,obj);
return true;
} catch (Exception e) {
Log.d("###", e.getMessage());
return false;
} finally {
//关闭数据库链接
sqLiteDatabase.close();
}
}
private int findAll(String sql) {
List<UserInfo> list = new ArrayList<UserInfo>();
SQLiteDatabase sqLiteDatabase=null;
try {
sqLiteDatabase = userDB.getWritableDatabase();
Cursor cursor = sqLiteDatabase.rawQuery(sql, null);
while (cursor.moveToNext()) {
UserInfo user = new UserInfo();
//根据数据库表中字段的顺序获取值
user.setId(cursor.getInt(0));
//根据数据库表中字段的名称获取值
user.setName(cursor.getColumnName(cursor.getColumnIndex("name")));
user.setAge(cursor.getInt(2));
list.add(user);
}
} catch (Exception e) {
list.clear();
Log.d("###", e.getMessage());
}
sqLiteDatabase.close();
return list.size();
}
}
数据库的相应代码
public class UserDB extends SQLiteOpenHelper{
public UserDB(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
public UserDB(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) {
super(context, name, factory, version, errorHandler);
}
@Override
public void onCreate(SQLiteDatabase db) {
//创建数据库表sql语句
String sql="create table user(_id integer parmary key,name varchar(10) not null,age integer not null)";
//执行创建数据库表sql
db.execSQL(sql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if(newVersion==2){
//开启一个事务 如果数据库表更新操作失败可以通过事务回滚
db.beginTransaction();
try{
//1:修改之前的数据库表的表名
db.execSQL("alter table user rename to user_test");
//2:创建一张新的数据库表
db.execSQL("create table user(_id integer primary key,,name varchar(20) not null,,age integer not null,see varchar(10) not null)");
//3:拷贝之前的数据库表数据到新表里面并给新添加的字段赋值
db.execSQL("insert into user select _id,name,age,'' from user_test");
//4:删除原版本数据库表
db.execSQL("drop table user_test");
//5:设置事务处理成功,如果不设置会自动回滚不会提交
db.setTransactionSuccessful();
//6:从beginTransaction开始的操作都会提交
db.endTransaction();
}catch (Exception e){
//数据库表更新操作出错因设置了事务会自动回滚
e.printStackTrace();
}
}
}
}
简单的实体Beanpublic class UserInfo implements Serializable{
private int id;
private String name;
private int age;
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}