SQLite数据库的DAO标准CRUD操作

基本功能

• 添加：输入信息，点击“添加”按钮添加进数据表中
• 修改：选定要修改的信息，输入要修改的数据，点击“修改”按钮保存修改
• 删除：选定要删除的信息，点击“删除”按钮删除该信息
• 查询：呈现表中所有信息到页面上

主要代码
DbHelper.java

SQLite数据库打开助手DbHelper作为抽象类SQLiteOpenHelper的子类需要重写2个抽象方法onCreate()和onUpgrade()

public class DbHelper extends SQLiteOpenHelper {
    public static final String TB_NAME = "student";

    public DbHelper(Context context, String dbname, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, dbname, factory, version);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS " +
                TB_NAME + "( _id integer primary key autoincrement," +
                "name varchar," + "age integer"+ ")");
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TB_NAME);
        onCreate(db);
    }
}

MyDAO.java
添加增删查改基本操作

public class MyDAO {
    private SQLiteDatabase myDb;
    private DbHelper dbHelper;

    public MyDAO(Context context) {
        dbHelper = new DbHelper(context,"test.db",null,4);
    }

    public Cursor allQuery(){    //查询所有
        myDb = dbHelper.getReadableDatabase();
        return myDb.rawQuery("select * from student",null);
    }
    public  int getRecordsNumber(){
        myDb = dbHelper.getReadableDatabase();
        Cursor cursor= myDb.rawQuery("select * from student",null);
        return cursor.getCount();
    }

    public void insertInfo(String name,int age){  //插入
        myDb = dbHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("age", age);
        long rowid=myDb.insert(DbHelper.TB_NAME, null, values);
        if(rowid==-1)
            Log.i("myDbDemo", "数据插入失败！");
        else
            Log.i("myDbDemo", "数据插入成功！"+rowid);
    }
    public void deleteInfo(String selId){  //删除
        String where = "_id=" + selId;
        int i = myDb.delete(DbHelper.TB_NAME, where, null);
        if (i > 0)
            Log.i("myDbDemo", "数据删除成功！");
        else
            Log.i("myDbDemo", "数据未删除！");
    }
    public void updateInfo(String name,int age,String selId){  //修改记录

        ContentValues values = new ContentValues();
        values.put("name", name);
        values.put("age", age);
        String where="_id="+selId;
        int i=myDb.update(DbHelper.TB_NAME, values, where, null);



        if(i>0)
            Log.i("myDbDemo","数据更新成功！");
        else
            Log.i("myDbDemo","数据未更新！");
    }
}

MainActivity.java
使用了MyDAO类的相关方法，首次运行时，增加3条记录并使用ListView控件显示出来

public class MainActivity extends Activity implements View.OnClickListener{

    private MyDAO myDAO;
    private ListView listView;
    private List<Map<String,Object>> listData;
    private Map<String,Object> listItem;
    private SimpleAdapter listAdapter;

    private EditText name;
    private EditText age;

    private  String selId=null;  //选择项id

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        Button bt_add= (Button) findViewById(R.id.add);bt_add.setOnClickListener(this);
        Button bt_modify=(Button)findViewById(R.id.modify);bt_modify.setOnClickListener(this);
        Button bt_del=(Button)findViewById(R.id.del);bt_del.setOnClickListener(this);

        name=(EditText)findViewById(R.id.name);
        age=(EditText)findViewById(R.id.age);

        myDAO = new MyDAO(this);
        if(myDAO.getRecordsNumber()==0) {
            myDAO.insertInfo("lier", 26);
            myDAO.insertInfo("wangwu", 45);
            myDAO.insertInfo("zhangsan", 80);
        }
        displayRecords();
    }
    public void displayRecords(){
        listView = (ListView)findViewById(R.id.listView);
        listData = new ArrayList<Map<String,Object>>();
        Cursor cursor = myDAO.allQuery();
        while (cursor.moveToNext()){
            int id=cursor.getInt(0);
            String name=cursor.getString(1);
            //int age=cursor.getInt(2);
            int age=cursor.getInt(cursor.getColumnIndex("age"));
            listItem=new HashMap<String,Object>();
            listItem.put("_id", id);
            listItem.put("name", name);
            listItem.put("age", age);
            listData.add(listItem);
        }
        listAdapter = new SimpleAdapter(this, listData, R.layout.item, new String[]{"_id","name","age"}, new int[]{R.id.id2,R.id.name2,R.id.age2});
        listView.setAdapter(listAdapter);  //应用适配器
        listView.setOnItemClickListener(new AdapterView.OnItemClickListener() {  //列表项监听
            @Override
            public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
                Map<String,Object> rec= (Map<String, Object>) listAdapter.getItem(position);
                name.setText(rec.get("name").toString());
                age.setText(rec.get("age").toString());
                Log.i("ly",rec.get("_id").toString());
                selId=rec.get("_id").toString();
            }
        });
    }
    @Override
    public void onClick(View v) {
        if(selId!=null) {
            String p1 = name.getText().toString().trim();
            int p2 = Integer.parseInt(age.getText().toString());
            switch (v.getId()){
                case  R.id.add:
                    myDAO.insertInfo(p1,p2);
                    break;
                case  R.id.modify:
                    myDAO.updateInfo(p1,p2,selId);
                    Toast.makeText(getApplicationContext(),"更新成功！",Toast.LENGTH_SHORT).show();
                    break;
                case  R.id.del:
                    myDAO.deleteInfo(selId);
                    Toast.makeText(getApplicationContext(),"删除成功！",Toast.LENGTH_SHORT).show();
                    name.setText(null);age.setText(null); selId=null;
            }
        }else{
            if(v.getId()==R.id.add) {
                String p1 = name.getText().toString();
                String p2=age.getText().toString();
                if(p1.equals("")||p2.equals("")){
                    Toast.makeText(getApplicationContext(),"姓名和年龄都不能空！",Toast.LENGTH_SHORT).show();
                }else{
                    myDAO.insertInfo(p1, Integer.parseInt(p2));
                }
            } else{
                Toast.makeText(getApplicationContext(),"请先选择记录！",Toast.LENGTH_SHORT).show();
            }
        }
        displayRecords();
    }
}

效果演示

完整项目源码
https://gitee.com/GAIALscariot/SQLite