android MVP模式下对数据库进行简单操作

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();
            }
        }
    }
}

简单的实体Bean

public 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;
    }
}