①DBHelp
public class DBHelp extends SQLiteOpenHelper{
private static final String DATABASE_NAME = "mysqlitedb";public DBHelp(Context context) {super(context, DATABASE_NAME, null, 1);//依次为环境，数据库名称，指针工厂，版本号}@Overridepublic void onCreate(SQLiteDatabase db) {//初始化你的数据库，是一些建表语句String sql = "create table t_user(_id integer primary key autoincrement, username text,password text)"; db.execSQL(sql);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { //对数据库升级，与onCreate方法是相对的，onCreate 与 构造方法 仅在首次使用数据库时执行}}
②DAO
public class UserDao {
private DBHelp dbHelp ;
private SQLiteDatabase db; //特别之处
public UserDao(Context context) {
dbHelp = new DBHelp(context);
}
public void save(User user) {
db = dbHelp.getWritableDatabase();
String sql = "insert into t_user(username,password) values(?,?)";
db.execSQL(sql,new String[]{user.getUsername(),user.getPassword()});//调用db的execSQL方法，第二个参数为Object[]
db.close();//用完关闭
}
public void update(User user) {
db = dbHelp.getWritableDatabase();
String sql = "update t_user set username = ?,password= ? where _id = ?";
db.execSQL(sql, new Object[]{user.getUsername(),user.getPassword(),user.getId()});
db.close();
}
public void del(int id) {
db = dbHelp.getWritableDatabase();
String sql = "delete from t_user where _id = ?";
db.execSQL(sql, new Object[]{id});
db.close();
}
public List<User> findAll() {
List<User> list = new ArrayList<User>();
db = dbHelp.getReadableDatabase();
String sql = "select _id,username,password from t_user";
Cursor cursor = db.rawQuery(sql, null);
while(cursor.moveToNext()) {
User user = new User();
user.setId(cursor.getInt(cursor.getColumnIndex("_id")));
user.setUsername(cursor.getString(cursor.getColumnIndex("username")));
user.setPassword(cursor.getString(cursor.getColumnIndex("password")));
list.add(user);
}
db.close();
return list;
}
public User findById(int id) {
User user = null;
db = dbHelp.getReadableDatabase();
String sql = "select _id,username,password from t_user where _id = ?";
Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(id)});
while(cursor.moveToNext()) {
user = new User();
user.setId(cursor.getInt(cursor.getColumnIndex("_id")));
user.setUsername(cursor.getString(cursor.getColumnIndex("username")));
user.setPassword(cursor.getString(cursor.getColumnIndex("password")));
}
return user;
}
}
③DAO withORM（不带sql语句的DAO）
public class UserDaoWithOrm {private DBHelp dbHelp ;private SQLiteDatabase db;private static final String TABLE_NAME = "t_user";public UserDaoWithOrm(Context context) {dbHelp = new DBHelp(context);}public void save(User user) {db = dbHelp.getWritableDatabase();ContentValues cv = new ContentValues();cv.put("username", user.getUsername());cv.put("password", user.getPassword());db.insert(TABLE_NAME, null, cv);db.close();}public void update(User user) {db = dbHelp.getWritableDatabase();ContentValues cv = new ContentValues();cv.put("username", user.getUsername());cv.put("password", user.getPassword());db.update(TABLE_NAME, cv, "_id = ?", new String[]{String.valueOf(user.getId())});db.close();}public void del(int id) {db = dbHelp.getWritableDatabase();db.delete(TABLE_NAME, "_id=?", new String[]{String.valueOf(id)});db.close();}public List<User> findAll() {List<User> list = new ArrayList<User>();db = dbHelp.getReadableDatabase();Cursor cursor = db.query(TABLE_NAME, new String[]{"_id","username","password"}, null, null, null, null, null);while(cursor.moveToNext()) {User user = new User();user.setId(cursor.getInt(cursor.getColumnIndex("_id")));user.setUsername(cursor.getString(cursor.getColumnIndex("username")));user.setPassword(cursor.getString(cursor.getColumnIndex("password")));list.add(user);}db.close();return list;}public User findById(int id) {User user = null;db = dbHelp.getReadableDatabase();Cursor cursor = db.query(TABLE_NAME, new String[]{"_id","username","password"}, "_id=?", new String[]{String.valueOf(id)}, null, null, null);//db.rawQuery(sql, new String[]{String.valueOf(id)});while(cursor.moveToNext()) {user = new User();user.setId(cursor.getInt(cursor.getColumnIndex("_id")));user.setUsername(cursor.getString(cursor.getColumnIndex("username")));user.setPassword(cursor.getString(cursor.getColumnIndex("password")));}return user;}}
