Android 笔记 - SQLite简单demo

最新推荐文章于 2023-06-28 16:50:19 发布

洌冰

最新推荐文章于 2023-06-28 16:50:19 发布

阅读量586

点赞数 1

分类专栏： Android 文章标签： android

Android 专栏收录该内容

119 篇文章 6 订阅

订阅专栏

1 Bean类

public class User {
    private int Uid;
    private String Uname;
    private String Uaddress;

    public User(int uid, String uname, String uaddress) {
        super();
        Uid = uid;
        Uname = uname;
        Uaddress = uaddress;
    }

    public User(String uname, String uaddress) {
        super();
        Uname = uname;
        Uaddress = uaddress;
    }

    public int getUid() {
        return Uid;
    }

    public void setUid(int uid) {
        Uid = uid;
    }

    public String getUname() {
        return Uname;
    }

    public void setUname(String uname) {
        Uname = uname;
    }

    public String getUaddress() {
        return Uaddress;
    }

    public void setUaddress(String uaddress) {
        Uaddress = uaddress;
    }

    public User() {
        super();
    }

    @Override
    public String toString() {
        return "User [Uid=" + Uid + ", Uname=" + Uname + ", Uaddress="
                + Uaddress + "]";
    }


}

2 数据库帮助类

public class SQLiteOpenHelper extends android.database.sqlite.SQLiteOpenHelper {
    private static final String dbname = "test.db";
    public static final String dbtable = "user";

    public SQLiteOpenHelper(Context context, String name,
            CursorFactory factory, int version) {
        super(context, dbname, factory, version);
        // TODO Auto-generated constructor stub
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table "+dbtable+"(uid integer primary key autoincrement, uname varchar(20), uaddress varchar(20))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }

}

分析：
数据库创建看上去是在

super(context, dbname, factory, version);

时创建的，其实不然，Android官方文档指出，只有第一次调用getReadableDataBase或者getWritableDataBase时才会调用创建数据库，大家可以自己测试一下。
3 数据库操作类

public class DBDao {
    SQLiteOpenHelper SQLiteOpenHelper;

    public DBDao(Context context) {
        this.SQLiteOpenHelper = new SQLiteOpenHelper(context, null, null, 1);
    }

    /**
     * 添加一条数据
     * @param user
     */
    public void save(User user) {
        SQLiteDatabase db = SQLiteOpenHelper.getWritableDatabase();
        db.execSQL("insert into user(uname,uaddress) values(?,?)",
                new Object[] { user.getUname(), user.getUaddress() });
        db.close();
    }

    /**
     * 删除一条数据
     * 
     * @param uid
     */
    public void delete(Integer uid) {
        SQLiteDatabase db = SQLiteOpenHelper.getWritableDatabase();
        db.execSQL("delete from user where uid=?", new Object[] { uid });
        db.close();
    }

    /**
     * 更新一条数据
     * 
     * @param user
     */
    public void update(User user) {
        SQLiteDatabase db = SQLiteOpenHelper.getWritableDatabase();
        db.execSQL(
                "update user set uname=?,uaddress=? where uid=?",
                new Object[] { user.getUname(), user.getUaddress(),
                        user.getUid() });
        db.close();
    }

    /**
     * 查找全部数据
     * 
     * @param uid
     */

    public ArrayList<User> findAll(){
        SQLiteDatabase db = SQLiteOpenHelper.getReadableDatabase();
        /**
         * Query the given table, returning a {@link Cursor} over the result set.
         *
         * @param table The table name to compile the query against.
         * @param columns A list of which columns to return. Passing null will
         *            return all columns, which is discouraged to prevent reading
         *            data from storage that isn't going to be used.
         * @param selection A filter declaring which rows to return, formatted as an
         *            SQL WHERE clause (excluding the WHERE itself). Passing null
         *            will return all rows for the given table.
         * @param selectionArgs You may include ?s in selection, which will be
         *         replaced by the values from selectionArgs, in order that they
         *         appear in the selection. The values will be bound as Strings.
         * @param groupBy A filter declaring how to group rows, formatted as an SQL
         *            GROUP BY clause (excluding the GROUP BY itself). Passing null
         *            will cause the rows to not be grouped.
         * @param having A filter declare which row groups to include in the cursor,
         *            if row grouping is being used, formatted as an SQL HAVING
         *            clause (excluding the HAVING itself). Passing null will cause
         *            all row groups to be included, and is required when row
         *            grouping is not being used.
         * @param orderBy How to order the rows, formatted as an SQL ORDER BY clause
         *            (excluding the ORDER BY itself). Passing null will use the
         *            default sort order, which may be unordered.
         * @return A {@link Cursor} object, which is positioned before the first entry. Note that
         * {@link Cursor}s are not synchronized, see the documentation for more details.
         * @see Cursor
         */
        Cursor cursor = db.query(SQLiteOpenHelper.dbtable, null, null, null, null, null, null);
        ArrayList<User> list = new ArrayList<User>();
        if(null != cursor){
            while (cursor.moveToNext()) {
                int uid2 = cursor.getInt(cursor.getColumnIndex("uid"));
                String uname = cursor.getString(cursor.getColumnIndex("uname"));
                String uaddress = cursor.getString(cursor
                        .getColumnIndex("uaddress"));
                User user = new User();
                user.setUid(uid2);
                user.setUname(uname);
                user.setUaddress(uaddress);
                list.add(user);
            }
            cursor.close();
            return list;
        }
        return null;
    }

    /**
     * 查找一条数据
     * 
     * @param uid
     */
    public User find(Integer uid) {
        SQLiteDatabase db = SQLiteOpenHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from user where uid=?",
                new String[] { uid.toString() });
        if (cursor.moveToFirst()) {
            int uid2 = cursor.getInt(cursor.getColumnIndex("uid"));
            String uname = cursor.getString(cursor.getColumnIndex("uname"));
            String uaddress = cursor.getString(cursor
                    .getColumnIndex("uaddress"));
            User user = new User();
            user.setUid(uid2);
            user.setUname(uname);
            user.setUaddress(uaddress);
            return user;
        }
        cursor.close();
        return null;
    }

    /**
     * 分页查找数据
     * 
     * @param offset
     *            跳过多少条数据
     * @param maxResult
     *            每页多少条数据
     * @return
     */
    public List<User> getScrollData(int offset, int maxResult) {
        List<User> users = new ArrayList<User>();
        SQLiteDatabase db = SQLiteOpenHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery(
                "select * from user order by uid asc limit ?,?", new String[] {
                        String.valueOf(offset), String.valueOf(maxResult) });
        while (cursor.moveToNext()) {
            int uid2 = cursor.getInt(cursor.getColumnIndex("uid"));
            String uname = cursor.getString(cursor.getColumnIndex("uname"));
            String uaddress = cursor.getString(cursor
                    .getColumnIndex("uaddress"));
            User user = new User();
            user.setUid(uid2);
            user.setUname(uname);
            user.setUaddress(uaddress);
            users.add(user);
        }
        return users;
    }

    /**
     * 获取数据总数
     * 
     * @return
     */
    public long getCount() {
        SQLiteDatabase db = SQLiteOpenHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select count(*) from user", null);
        cursor.moveToFirst();
        long reslut = cursor.getLong(0);
        return reslut;
    }
}

4 主界面

public class MainActivity extends Activity {
    private static final String TAG = "MainActivity";

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

        MyAsyncTask tast = new MyAsyncTask();
        tast.execute();
    }

    public class MyAsyncTask extends AsyncTask<String, Integer, byte []>{

        @Override
        protected void onPreExecute() {
            Log.v(TAG,"onPreExecute");
            super.onPreExecute();
        }

        @Override
        protected byte[] doInBackground(String... params) {
            Log.v(TAG,"doInBackground");
            DBDao dbDao = new DBDao(MainActivity.this);
            User user = new User("chj", "新民");

            dbDao.save(user);
            ArrayList<User> users = dbDao.findAll();
            //在这个方法可以调用onProgressUpdate来更新进度
            return users.toString().getBytes();//return给了onPostExecute
        }

        @Override
        protected void onProgressUpdate(Integer... values) {
            Log.v(TAG,"onProgressUpdate");
            super.onProgressUpdate(values);
        }

        @Override
        protected void onPostExecute(byte[] result) {
            Log.v(TAG,"onPostExecute");
            Toast.makeText(MainActivity.this, ""+new String(result), Toast.LENGTH_LONG).show();
            super.onPostExecute(result);
        }

    }
}