sqlite数据库的简单运用

  年纪轻轻，记忆力却不好，总而言之，还是敲的少（很上口有木有）

自当是复习一下数据库 。

 

在用到数据库的时候，一般是在工程目录下新建一个包 , 起名做db.然后再在里面新建一个DBOpenHelper，从字面意思理解就是DB（DataBase数据库）Open（开启）Helper（帮助类）,即数据库开启帮助类。

public class DBOpenHelper extends SQLiteOpenHelper {
    public DBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    // 当数据库被调用时创建
    @Override
    public void onCreate(SQLiteDatabase db) {
        //执行sql语句  创建一个表  "DataOfMakeTask"
        String createSql = "CREATE TABLE \"DataOfMakeTask\" (\n" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                "title NVARCHAR(50) NOT NULL,\n" +
                "content NVARCHAR(1000),\n" +
                "principle NVARCHAR(10) NOT NULL ,\n" +
                "auditor NVARCHAR(10) NOT NULL,\n" +
                "writer NVARCHAR(10) NOT NULL );";
        db.execSQL(createSql);
    }

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

    }
}

然后我们需要一个bean类

新建一个bean包，写一个包含数据库表中属性的类，起名为Task（任务），写上构造函数和get and set方法。

public class Task {
    private String mTitle;
    private String mContent;
    private String mPrinciple;
    private String mAuditor;
    private String mWriter;
    public Task() {
    }

    public Task(String title,String content, String principle, String auditor, String writer) {
        mTitle = title;
        mContent = content;
        mPrinciple = principle;
        mAuditor = auditor;
        mWriter = writer;
    }

    public String getTitle() {
        return mTitle;
    }

    public void setTitle(String title) {
        mTitle = title;
    }

    public String getContent() {
        return mContent;
    }

    public void setContent(String content) {
        mContent = content;
    }

    public String getPrinciple() {
        return mPrinciple;
    }

    public void setPrinciple(String principle) {
        mPrinciple = principle;
    }

    public String getAuditor() {
        return mAuditor;
    }

    public void setAuditor(String auditor) {
        mAuditor = auditor;
    }

    public String getWriter() {
        return mWriter;
    }

    public void setWriter(String writer) {
        mWriter = writer;
    }
}

然后我们需要对这个bean类进行开刀，进行增删改查。在db包下新建一个DaoTask类。

public class DaoTask｛

//以类名作为标签  如果类名改了tag不会失效;

private final String mTag = this。getClass().getName();

//数据库的实例

privata SQLiteDatabase mDb;

//构造函数私有化 

private DaoTask(){

}

//使用静态内部类来实现单例

private static class SingletonHolder{

private static DaoTask mDao = new DaoTask();

}

//获取当前类的实例

private static DaoTask instance()

return Singgleton.mDao;

｝

//根据上下文对DaoTask进行初始化，可以理解为Dao类对数据库进行初始化.

public SQLiteDatabase init(Context context)

{

String userName="用户名";

if(!TextUtils.isEmpty(userName)){

DBOpenHelper helper = new DBOpenHleper(context,userName,null,1);

mDb = helper.getReadbleDatabase();

}

else{

log.e(mTag,"userName  is null")}

return mDb;

}

//对数据库进行增删改查  1  增

public boolean insert(Task task){

String insertSql =

"INSERT OR REPLACE INTO DataOfMakeTask(title,content,principle,auditor,writer)\n" +
        "VALUES(?,?,?,?,?);";
//？相当于占位符

Object[] values ={task.getTitle(), task.getContent(), task.getPrinciple(), task.getAuditor(), task.getWriter()}

boolean result;

try {
            if (mDb != null) {
                mDb.execSQL(insertSql,values);
            }
            result =true;
        } catch (SQLException e) {
//            e.printStackTrace();
            Log.e(mTag, "insert  error!!!!!" + e.getMessage());
            result = false;
        }
        return result;
}

************这里需要手动加try   catch  所以直接粘贴复制了************

2  增删改查方法之删

public boolean delete(int id){

String deleteSql = "DELETE FROM DataOfTask WHERE id=?";
if (mDb == null) {
    Log.e(mTag, "mTag   is   null!!!");
    return false;
}
try {
    mDb.execSQL(deleteSql,new  Object[]{id});
    return true;
} catch (SQLException e) {
    e.printStackTrace();
    return false;
}

}

3 增删改查方法之改

public boolean upDate(Task task){

if (mDb == null) {
    Log.e(mTag, "mDb  is  null !!!!");
    return false;
}
boolean result;
try {
    String upDateSql="UPDATE DataOfTask SET content=? WHERE title=? ;";
    mDb.execSQL(upDateSql,new Object[]{task.getTitle(),task.getPrinciple(),task.getAuditor(),task.getWriter()});
    result = true;
} catch (SQLException e) {
    Log.e(mTag, "upDate  error!!!");
    result = false;
}
return result;

}

4 增删改查方法之查（相比较于增删改较难一些，但用的还是比较多的）

//查询一个task

public Task loadOne(String id){

if(mDb == null){

return null}

String loadSql =

"SELECT * FROM DataCenterOfTask WHERE _id=?;";

Task task = null;

Cursor cursor = null;

try{

cursor=mDb.rawQuery(loadSql,String[]{id})

task=null;

int index=0;

if(cursor!=null && cursor.moveToFirst()){

task=new Task();

因为我们加了一个自增长的_id,  所以title是位于第二位的，index是1;

task.setTitle(cursor.getString(index+1));

task.setContent(cursor.getString(index+2));

task.setPrinciple(cursor.getString(index+3));

task.setAuditor(cursor.getString(index+4));

task.setWriter(cursor.getString(index+5));

}

catch (Exception e) {
    e.printStackTrace();
} finally {
    if (cursor != null) {
        cursor.close();
    }
}
return task;

//别忘了用完游标之后把游标关闭.close();

}

********************** 查询表中所有数据

public List<Task> queryAll(){

if (mDb == null) {
            Log.e(mTag, "mDb is  null!!!!");
            return null;
        }
        Cursor cursor = null;
        List<Task> list = null;
        try {
            cursor = mDb.rawQuery("SELECT * FROM DataOfMakeTask ;", new String[]{});
            if (cursor != null) {
                list = new ArrayList<>();
                int index = 0;
                while (cursor.moveToNext()) {
                    Task task = new Task();
//                    task.setId(cursor.getString(index));
                    task.setTitle(cursor.getString(index+1));
                    task.setContent(cursor.getString(index+2));
                    task.setPrinciple(cursor.getString(index+3));
                    task.setAuditor(cursor.getString(index+4));
                    task.setWriter(cursor.getString(index+4));
                    list.add(task);
                }
            }
        } catch (Exception e) {
            Log.e(mTag, "queryAll  error");
        } finally {
            if (cursor != null) {
                cursor.close();

            }
        }
        return list;

}

当然  我们还需要给之前的init中的数据库注册一下

新建一个MyApplication

public class MyApplication extends Application{
    /**
     * 搁这对sharedPreference初始化
     */
    @Override
    public void onCreate() {
        super.onCreate();
        DaoTask.instance().init(this);
    }
}

最后我们就可以用DaoTask（）中的方法  来对数据库中的DataOfTask表进行增删改查了,写完了   alikeduo  gouzayimasi  谢谢