Android 数据库操作

Android 数据库操作

1、数据库的创建与其中的注意事项。

public class DBHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "thecar.db";

    private static final int DATABASE_VERSION = 1;


    public DBHelper(Context context) {

        //CursorFactory设置为null,使用默认值

        super(context, DATABASE_NAME, null, DATABASE_VERSION);

    }

    @Override

    public void onCreate(SQLiteDatabase db) {

        db.execSQL("CREATE TABLE IF NOT EXISTS account" +

                "(_id INTEGER PRIMARY KEY AUTOINCREMENT, p_name VARCHAR,p_level VARCHAR, p_address VARCHAR, p_tel VARCHAR, p_card VARCHAR, " +

                "c_num VARCHAR, c_model VARCHAR, c_card VARCHAR, c_trip VARCHAR,remarks TEXT)");

    }

    @Override

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        db.execSQL("ALTER TABLE account ADD COLUMN other STRING");

    }

}

①、DATABASE_NAME ：数据库名称。

②、DATABASE_VERSION ：数据库版本，如果数据库版本发生了改变，那么就会执行onUpgrade()方法。其中："ALTER TABLE account ADD COLUMN other STRING"的意思是在account中添加一个新的STRING 类型的“other”列。

③、onCreate()：方法中有一个“CREATE TABLE IF NOT EXISTS”，意思就是如果不存在则建立。

2、数据库操作，基本的增删盖茶（盖茶什么鬼）。

public class DBManager {

    private DBHelper helper;

    private SQLiteDatabase db;

    public DBManager(Context context) {

        helper = new DBHelper(context);

        db = helper.getWritableDatabase();

    }

    public void addOne(MyBean bean) {

        db.beginTransaction();  //开始事务

        try {

            db.execSQL("INSERT INTO account VALUES(null, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",

                    new Object[]{bean.getpName(), bean.getpLevel(), bean.getpAddress(),

                            bean.getpTel(), bean.getpCard(), bean.getcNum(), bean.getcModel(),

                            bean.getcCard(), bean.getcTrip(), bean.getRemarks()});

            db.setTransactionSuccessful();  //设置事务成功完成

        } finally {

            db.endTransaction();    //结束事务

        }

    }

    //修改一个记录

    public void updateOne(MyBean bean) {

        ContentValues cv = new ContentValues();

        cv.put("p_name", bean.getpName());
          ......
           一些字段
          ......           
        cv.put("remarks", bean.getRemarks());

        db.update("account", cv, "_id = ?", new String[]{String.valueOf(bean.getId())});

    }

    public void deleteOne(MyBean bean) {

        db.delete("account", "_id = ?", new String[]{String.valueOf(bean.getId())});

    }

    public List<MyBean> queryAll() {

        ArrayList<MyBean> beanList = new ArrayList<MyBean>();

        Cursor c = queryTheCursor();

        while (c.moveToNext()) {

            MyBean bean = new MyBean();

            bean.setId(c.getInt(c.getColumnIndex("_id")));

              ......
               一些字段
              ...... 

            bean.setRemarks(c.getString(c.getColumnIndex("remarks")));

            beanList.add(bean);

        }

        c.close();

        return beanList;

    }


    public List<MyBean> queryByName(String name) {

        ArrayList<MyBean> beanList = new ArrayList<MyBean>();

        Cursor c = queryTheCursor(name);

        while (c.moveToNext()) {

            MyBean bean = new MyBean();

            bean.setId(c.getInt(c.getColumnIndex("_id")));
              ......
               一些字段
              ...... 

            bean.setRemarks(c.getString(c.getColumnIndex("remarks")));

            beanList.add(bean);

        }

        c.close();

        return beanList;

    }


    public List<MyBean> queryBySome(String pName, String cNum) {

        ArrayList<MyBean> beanList = new ArrayList<MyBean>();

        Cursor c = queryTheCursor(pName,cNum);

        while (c.moveToNext()) {

            MyBean bean = new MyBean();

            bean.setId(c.getInt(c.getColumnIndex("_id")));

              ......
               一些字段
              ...... 

            bean.setRemarks(c.getString(c.getColumnIndex("remarks")));

            beanList.add(bean);

        }

        c.close();

        return beanList;

    }

    public Cursor queryTheCursor(String pName, String cNum) {

        String sql = "SELECT * FROM account WHERE p_name LIKE '%" + pName + "%' " +

                "AND c_num LIKE '%" + cNum + "%'";

        Cursor c = db.rawQuery(sql, null);

        return c;

    }


    public Cursor queryTheCursor() {

        String sql = "SELECT * FROM account";

        Cursor c = db.rawQuery(sql, null);

        return c;

    }


    public Cursor queryTheCursor(String name) {

        String sql = "SELECT * FROM account where p_name ='"+name+"'";

        Cursor c = db.rawQuery(sql, null);

        return c;

    }


    /**

     * 关闭数据库

     */

    public void closeDB() {

        db.close();

    }

}

①、updateOne() -> ContentValues的使用：

一般地:

ContentValues cv = new ContentValues();
cv.put("数据库字段名", 对应的值);
db.update("表名", cv, "_id = ?（这个地方相当于where）", new String[]{String.valueOf(bean.getId())});

②、query -> Cursor（每行的集合）的使用：

基本上：

String sql = "SELECT * FROM account";
Cursor c = db.rawQuery(sql, null); 
while (c.moveToNext()){…}

而这个DBManager怎么用，在该用的时候或者地方new一个就好了。