数据库的增删改查操作，实现对封装了bean的操作。
实现分页的功能的查询：
建表：
@Overridepublic void onCreate(SQLiteDatabase db) {String sql = "CREATE TABLE " + TABLE_NAME + "(" + _ID+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + _USERNAME+ " TEXT NOT NULL," + _PASSWORD + " TEXT)";Log.i(TAG, sql);db.execSQL(sql);}
增加数据：
①利用sql语句
public void add(User u) {String sql = "INSERT INTO " + TABLE_NAME + "(" + _USERNAME + ", "+ _PASSWORD + ") VALUES(?,?)";Log.i(TAG, sql);db.execSQL(sql, new Object[] { u.getUserName(), u.getPassWord() });}
②高级做法
public void add_(User u) {ContentValues values = new ContentValues();values.put(_USERNAME, u.getUserName());values.put(_PASSWORD, u.getPassWord());db.insert(TABLE_NAME, null, values);}
db.insert(TABLE_NAME, null, values);
①参数一：表名
②参数二： 可选的，一般为空，当values参数为空或者里面没有内容的时候，insert是会失败的(底层数据库不允许插入一个空行)，为了防止这种情况，我们要在这里设置一个列名，到时候如果发现将要插入的行为空行时，就会将你指定的这个列名的值设为null,然后再向数据库中插入。
删除数据：
①利用sql语句
public void del(int id) {String sql = "DELETE FROM " + TABLE_NAME + " WHERE " + _ID + "= ?";Log.i(TAG, sql);db.execSQL(sql, new Object[] { id });}
②高级做法
public void del_(int id) {String whereClause = _ID + " = ?";String[] whereArgs = { id + "" };db.delete(TABLE_NAME, whereClause, whereArgs);}
db.delete(TABLE_NAME, whereClause, whereArgs)
参数一：表名
参数二：where后面添加的条件，看好格式
参数三：条件中？参数的值的数组
更改数据：
①利用sql
public void update(User u) {String sql = "UPDATE " + TABLE_NAME + " SET " + _USERNAME + " = ?,"+ _PASSWORD + "= ?" + " WHERE " + _ID + "=?";Log.i(TAG, sql);db.execSQL(sql,new Object [] { u . getUserName (), u . getPassWord (), u . getId () });}
②推荐做法
public void update_(User u) {ContentValues values = new ContentValues();values.put(_USERNAME, u.getUserName());values.put(_PASSWORD, u.getPassWord());String whereClause = _ID + "=?"; //可以有多个条件的String[] whereArgs = { u.getId() + "" };db.update(TABLE_NAME, values, whereClause, whereArgs);}
db.update(TABLE_NAME, values, whereClause, whereArgs) ：
荐做法
参数一：表名
参数二：更改之后的
ContentValues
类型的值
参数三：条件语句，格式
参数四：条件中?号所对应的值
查询数据：
①sql语句做法
public List<User> queryAll() {List<User> list = new ArrayList<User>();String sql = "SELECT * FROM " + TABLE_NAME;Log.i(TAG, sql);Cursor cursor = db.rawQuery(sql, null);while (cursor.moveToNext()) {User u = new User();u.setId(cursor.getInt(cursor.getColumnIndex(_ID)));u.setUserName(cursor.getString(cursor.getColumnIndex(_USERNAME)));u.setPassWord(cursor.getString(cursor.getColumnIndex(_PASSWORD)));list.add(u);}return list;}
②高级用法
public List<User> queryAll_() {List<User> list = new ArrayList<User>();Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null,null);while (cursor.moveToNext()) {User u = new User();u.setId(cursor.getInt(cursor.getColumnIndex(_ID)));u.setUserName(cursor.getString(cursor.getColumnIndex(_USERNAME)));u.setPassWord(cursor.getString(cursor.getColumnIndex(_PASSWORD)));list.add(u);}return list;}
db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy) 上面都给空，代表就是select * from user;
参数一：表名
参数二：需要查询的字段
参数三：筛选的语句，类似于where中的条件
参数四：筛选条件中的值
参数四：为分组增加条件
参数五：分组之后的条件
参数六：排序
还有三个构造方法。看名称就能看懂。
实现分页的功能的查询：
public Cursor query(int currentPage,int pageSize){int start=(currentPage-1)*pageSize;String limit=start+","+pageSize; //和sql语句基本一样Cursor cursor=db.query(TABLE_NAME, null, null, null, null, null, null, limit);return cursor;}
整体的代码：
import java.util.ArrayList;import java.util.List;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;import android.util.Log;import com.mixm.bean.User;public class UserDBHelper {private static final String TAG = "UserDBHelper";private static final String DATABASE_NAME = "user.db";private static final int VERSISON = 1;private static final String TABLE_NAME = "user";private static final String _ID = "id";private static final String _USERNAME = "username";private static final String _PASSWORD = "password";private UserHelper userHelper;private SQLiteDatabase db;// 内部类class UserHelper extends SQLiteOpenHelper {public UserHelper(Context context) {super(context, DATABASE_NAME, null, VERSISON);}@Overridepublic void onCreate(SQLiteDatabase db) {String sql = "CREATE TABLE " + TABLE_NAME + "(" + _ID+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + _USERNAME+ " TEXT NOT NULL," + _PASSWORD + " TEXT)";Log.i(TAG, sql);db.execSQL(sql);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}}public UserDBHelper(Context context) {userHelper = new UserHelper(context);db = userHelper.getWritableDatabase();}// 普通public void add(User u) {String sql = "INSERT INTO " + TABLE_NAME + "(" + _USERNAME + ", "+ _PASSWORD + ") VALUES(?,?)";Log.i(TAG, sql);db.execSQL(sql, new Object[] { u.getUserName(), u.getPassWord() });}// 高级 一般做法public void add_(User u) {ContentValues values = new ContentValues();values.put(_USERNAME, u.getUserName());values.put(_PASSWORD, u.getPassWord());db.insert(TABLE_NAME, null, values);}public void del(int id) {String sql = "DELETE FROM " + TABLE_NAME + " WHERE " + _ID + "= ?";Log.i(TAG, sql);db.execSQL(sql, new Object[] { id });}public void del_(int id) {String whereClause = _ID + " = ?";String[] whereArgs = { id + "" };db.delete(TABLE_NAME, whereClause, whereArgs);}public void update(User u) {String sql = "UPDATE " + TABLE_NAME + " SET " + _USERNAME + " = ?,"+ _PASSWORD + "= ?" + " WHERE " + _ID + "=?";Log.i(TAG, sql);db.execSQL(sql,new Object[] { u.getUserName(), u.getPassWord(), u.getId() });}public void update_(User u) {ContentValues values = new ContentValues();values.put(_USERNAME, u.getUserName());values.put(_PASSWORD, u.getPassWord());String whereClause = _ID + "=?";String[] whereArgs = { u.getId() + "" };db.update(TABLE_NAME, values, whereClause, whereArgs);}public List<User> queryAll() {List<User> list = new ArrayList<User>();String sql = "SELECT * FROM " + TABLE_NAME;Log.i(TAG, sql);Cursor cursor = db.rawQuery(sql, null);while (cursor.moveToNext()) {User u = new User();u.setId(cursor.getInt(cursor.getColumnIndex(_ID)));u.setUserName(cursor.getString(cursor.getColumnIndex(_USERNAME)));u.setPassWord(cursor.getString(cursor.getColumnIndex(_PASSWORD)));list.add(u);}return list;}public List<User> queryAll_() {List<User> list = new ArrayList<User>();Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);while (cursor.moveToNext()) {User u = new User();u.setId(cursor.getInt(cursor.getColumnIndex(_ID)));u.setUserName(cursor.getString(cursor.getColumnIndex(_USERNAME)));u.setPassWord(cursor.getString(cursor.getColumnIndex(_PASSWORD)));list.add(u);}return list;}}
扫一扫
1461
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
