Android数据库使用入门

Android数据库使用入门

Android本地存储有几种方式，具体使用哪一种要看我们的需求
比如我们要设置一个app的一个设置，存储简单几个数值，比如记住登录，记住亮度，偏好设置等，我们可以使用SharedPreferences，SharedPreferences使用起来非常简单方便，那么如果有这样一个需求，要本地保存多个用户，该如何做呢？当然很少会碰到这个需求，因为用户都是保存在服务器上的，如果我们要做，那我们就要使用到Android的数据库SQLite了，如果想系统全面的了解SQLite的话，我推荐这个网站：https://www.runoob.com/sqlite/sqlite-tutorial.html

1.首先我们创建一个UserHelper类继承SQLiteOpenHelper

public class UserHelper extends SQLiteOpenHelper {

    public static final String TABLE_NAME = "user"; //表名
    public static final String ID = "id";//序号
    public static final String MID = "mid";//工号
    public static final String Time = "date";//时间
    public static final String Name = "name";
    public static final String Pwd = "pwd";
    public static final String Access = "access";//权限

    private static final int DATABASE_VERSION = 1;

    public UserHelper(Context context) {
        super(context, "User", null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        String sql = "CREATE TABLE " + TABLE_NAME + " ( " + ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                Time + " text ," +
                Name + " text not null," +
                Pwd + " text not null," +
                MID + " text not null unique ," +
                Access + " text" + ");";

        Log.i("s", sql);
        db.execSQL(sql);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO 更新数据库
    }

}

2.实体类

public class User {

    private String name;
    private String pwd;
    private String mId;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public String getmId() {
        return mId;
    }

    public void setmId(String mId) {
        this.mId = mId;
    }

    public User() {
    }

    public User(String name, String pwd) {
        this.name = name;
        this.pwd = pwd;
    }

    public User(String name, String pwd, String mId) {
        this.name = name;
        this.pwd = pwd;
        this.mId = mId;
    }

    @Override
    public String toString() {
        return "User{" +
                "name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                ", mId='" + mId + '\'' +
                '}';
    }
}

3.我们再创建一个UserSQLiteControl去实现功能，这里简单封装一下

public interface ISQLiteControl<T> {

    boolean save(Context context, T t);

    List<T> get(Context context);

    boolean update(Context context, T t);

    boolean delete();
}

public class UserSQLiteControl implements ISQLiteControl<User> {

    @Override
    public boolean save(Context context, User user) {
        UserHelper mHelper = new UserHelper(context);
        SQLiteDatabase db = mHelper.getWritableDatabase();
        try {
            String sql1 = "INSERT INTO  " + UserHelper.TABLE_NAME + " ( " + UserHelper.Name + "," + UserHelper.Pwd + "," + UserHelper.MID + ") VALUES('" +
                    user.getName() + "','" +
                    user.getPwd() + "','" +
                    user.getmId() + "')";
            db.execSQL(sql1);
            db.close();
            return true;
        } catch (SQLException e) {
            Log.i("插入一条数据失败：", "");//打印输出信息，属于Info级别
            db.close();
            return false;
        }
    }

    @Override
    public List<User> get(Context context) {
        UserHelper mHelper = new UserHelper(context);
        SQLiteDatabase db = mHelper.getReadableDatabase();
        List<User> users = new ArrayList<>();
        try {
            Cursor cursor = db.query(UserHelper.TABLE_NAME, null, null, null, null, null, null, "1000");
            if (cursor.moveToFirst()) {
                do {
                    User carResult = new User(cursor.getString(cursor.getColumnIndex(UserHelper.Name + "")),
                            cursor.getString(cursor.getColumnIndex(UserHelper.Pwd + "")),
                            cursor.getString(cursor.getColumnIndex(UserHelper.MID + "")));
                    users.add(carResult);
                }
                while (cursor.moveToNext());
            }
            cursor.close();
            db.close();
            return users;
        } catch (Exception e) {
            db.close();
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public boolean update(Context context, User user) {
        UserHelper mHelper = new UserHelper(context);
        SQLiteDatabase db = mHelper.getWritableDatabase();

        return false;
    }

	//根据工号或姓名获取用户
    public User getUser(Context context, User user) {
        UserHelper mHelper = new UserHelper(context);
        SQLiteDatabase db = mHelper.getReadableDatabase();

        User result = null;
        try {
            String sql = "Select * From " + UserHelper.TABLE_NAME + " where " + UserHelper.Name + " = '" + user.getName() +
                    "' AND  " + UserHelper.Pwd + " = '" + user.getPwd() + "'" +
                    " OR  " + UserHelper.MID + " = '" + user.getmId() + "'" +
                    " AND  " + UserHelper.Pwd + " = '" + user.getPwd() + "'";
            Cursor cursor = db.rawQuery(sql, new String[]{});
            if (cursor.moveToFirst()) {
                result = new User(cursor.getString(cursor.getColumnIndex(UserHelper.Name + "")),
                        cursor.getString(cursor.getColumnIndex(UserHelper.Pwd + "")),
                        cursor.getString(cursor.getColumnIndex(UserHelper.MID + "")));
            }
            cursor.close();
            db.close();
            return result;
        } catch (Exception e) {
            db.close();
            e.printStackTrace();
            return null;
        }
    }

	//根据工号删除用户
    public boolean deleteUser(Context context, String name) {
        UserHelper mHelper = new UserHelper(context);
        SQLiteDatabase db = mHelper.getWritableDatabase();
        try {
            String sql = "Delete From " + UserHelper.TABLE_NAME + " where " + UserHelper.MID + " = '" + name + "'";
            db.execSQL(sql);
            db.close();
            return true;
        } catch (Exception e) {
            db.close();
            e.printStackTrace();
            return false;
        }
    }

    @Override
    public boolean delete() {
        File dataBaseFile = new File("/data/data/com.app/databases/User");
        return deleteFile(dataBaseFile);
    }

    private boolean deleteFile(File file) {
        if (file.exists()) { // 判断文件是否存在
            file.delete();
            Log.i("deleteFile", file.getName() + "成功删除！！");
            return true;
        } else {
            Log.i("deleteFile", file.getName() + "不存在！！！");
            return false;
        }
    }
}

注意：在写sql语句的时候如果值是string的话，需要加上’’，比如：“INSERT INTO XXX " +” (Name) VALUES(’" + car.getmName() + “’)”;

4.调用

UserSQLiteControl control = new UserSQLiteControl();
                User user = new User(name, pwd, id);
                result = control.save(NewUserActivity.this, user);
                if (!result) {
                    showLoading("创建用户失败，请检查工号是否存在！", true);
                    return;
                }
                showLoading("添加成功！");

5.好了，以上就是SQLite的全部内容了，非常简单！