Android SQLite

SQLite

今天玩了下android的SQLite引擎，觉得倍儿爽，以前只会用sharepreferences，现在升了下Level。

现在我说一下简单用法

建表

db.execSQL("CREATE TABLE IF NOT EXISTS news" +
            "(id INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT, content TEXT)");

插入

db.execSQL("INSERT INTO news VALUES(?,?,?)", new Objects[]{news.getId(),news.getTitle(),news.getContent()});

删除

db.delete("news", "id = ?", new String[{String.valueOf(news.getId())})

查询

 Cursor c = db.rawQuery("SELECT*FROM news", null);//查表
 List<News> newsList= new ArrayList<>();
  while (c.moveToNext()) {
        News news= new News();
        news.setId(c.getInt(c.getColumnIndex("id")));
        news.setTitle(c.getString(c.getColumnIndex("title")));
        news.setContent(c.getString(c.getColumnIndex("content")));
        newsList.add(news);
    }
    c.close();
    }

更新

db.update("news", values, "id = ?", new String[]{String.valueOf(news.getId())});

附上部分代码

NewsDBHelper .java

public class NewsDBHelper extends SQLiteOpenHelper {

public NewsDBHelper (Context context) {//必须声明构造函数
    super(context, "news_db", null, 1);
}

//数据库第一次被创建时onCreate会被调用
@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE IF NOT EXISTS news" +
            "(id INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT, content TEXT)");
}

//必须重载此方法
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("ALTER TABLE news ADD COLUMN other STRING");
}

}

DBManage.java

public class DBManage {
private NewsDBHelper helper;
private SQLiteDatabase db;

public DBManage(Context context) {
    helper = new NewsDBHelper(context);
    db = helper.getWritableDatabase();
}

//插入
public boolean add(News news) {
    db.beginTransaction();
    try {
        db.execSQL("INSERT INTO news VALUES(?,?,?)"
                , new Object[]{news.getId(), news .getTitle(), news.getContent()});
        db.setTransactionSuccessful();//设置事务成功完成
        return true;
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }finally {
        db.endTransaction();//结束事务
    }

}

//删除
public void delete(News news) {
    db.delete("news", "id = ?", new String[]{String.valueOf(news.getId())});//删除当前id为xx的数据
}

//查询
public List<News> query() {
    List<News> newsList= new ArrayList<>();
    Cursor c = queryTheCursor();
    while (c.moveToNext()) {
        News news= new News();
        news.setId(c.getInt(c.getColumnIndex("id")));
        news.setTitle(c.getString(c.getColumnIndex("title")));
        news.setContent(c.getString(c.getColumnIndex("content")));
        newsList.add(news);
    }
    c.close();
    return notes;
}

//更新
public Boolean update(News news) {
    db.beginTransaction();
    try {
        ContentValues values = new ContentValues();
        values.put("title", news.getTitle());
        values.put("content", news.getContent());
        db.update("news", values, "id = ?", new String[]{String.valueOf(news.getId())});
        db.setTransactionSuccessful();//设置事务成功完成
        return true;
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }finally {
        db.endTransaction();//结束事务
    }

}

public Cursor queryTheCursor() {
    Cursor c = db.rawQuery("SELECT*FROM news", null);//查表
    return c;
}

public void closeDB() {
    db.close();
}//关闭数据库

News.java

public class News{
private int id;
private String title;
private String content;

public News() {

}

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public String getTitle() {
    return title;
}

public void setTitle(String title) {
    this.title = title;
}

public String getContent() {
    return content;
}

public void setContent(String content) {
    this.content = content;
}
}

最后记得在程序结束时添上一句

db.close();//关闭数据库