数据库的创建以及对数据库的增删改查

数据库的创建以及对数据库的增删改查

• 1 新建类继承AQLiteOpenHelper

  '''public class NewsSQLiteOpenHelper extends SQLiteOpenHelper{
  private static final String  DB_NAME = "NEWS.DB";//数据库名称
  private static final int DB_VERSION = 4;//数据库版本号
  public NewsSQLiteOpenHelper(Context context){
  super(context,DB_NAME,null,DB_VERSION);
  }
  //version  数据库版本号 context 上下文 name数据库名字
          public NewsSQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
  super(context, name, factory, version);
  }
  public NewsSQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) {
  super(context, name, factory, version, errorHandler);
  }
      @Override
      public void **onCreate**(SQLiteDatabase db) {
          //获取创建表格的SQL
          String netNewsSQL =Constant.NewNewsTable.getCreatNetNewsSQL();
      //Z执行SQL
      db.execSQL(netNewsSQL);
  }
  
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      db.execSQL("drop table "+ Constant.NewNewsTable.TBL_NAME);
      onCreate(db);
  }
  

  }”’

在oncreat方法中获取创建表格的SQL，并执行SQL
* 2**创建一个工具类，在工具类中构造方法，使用数据库语言，创建一个数据库**

    public static final class NewNewsTable {
    public static final String TBL_NAME = "NETNEWS";
    public static final String COL_ID = "ID";
    public static final String COL_TITLE = "TITLE";
    public static final String COL_CTIME = "CTIME";
    public static final String COL_PICURL = "PICURL";
    public static final String COL_URL = "URL";
    public static final String COL_DESCRIPTION = "DESCRIPTION";
    public static final String COL_NEWSTYPE = "NEWSTYPE";

    public static String getCreatNetNewsSQL() {
        String sql = "CREATE TABLE IF NOT EXISTS "
                + TBL_NAME + "(" + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COL_TITLE + " VARCHAR(200),"
                + COL_CTIME + " VARCHAR(50),"
                + COL_PICURL + " VARCHAR(200),"
                + COL_URL + " VARCHAR(200),"
                + COL_DESCRIPTION + " TEXT,"
                + COL_NEWSTYPE + " integer"
                + ")";
        return sql;
    }'

• 3 创建一个工具类，提供对数据库的增删改查等一系列操作.

  public class NetNewsDao  {
  private NewsSQLiteOpenHelper openHelper;
  public NetNewsDao(Context mContext){
      this.openHelper = new NewsSQLiteOpenHelper(mContext);
  }
  public List<NetNews> findNewsByType(int type){
      List<NetNews> list = new ArrayList<>();
      SQLiteDatabase db = this.openHelper.getReadableDatabase();
      Cursor cursor = db.query(
              Constant.NewNewsTable.TBL_NAME,
              new String[]{
                      Constant.NewNewsTable.COL_ID,
                      Constant.NewNewsTable.COL_CTIME,
                      Constant.NewNewsTable.COL_DESCRIPTION,
                      Constant.NewNewsTable.COL_TITLE,
                      Constant.NewNewsTable.COL_PICURL,
                      Constant.NewNewsTable.COL_URL,
                      Constant.NewNewsTable.COL_NEWSTYPE
              },
              Constant.NewNewsTable.COL_NEWSTYPE+" =?",
              new String[]{type+""},
              null,
              null,
              null,
              null
      );
      while(cursor.moveToNext()){
          NetNews nn = new NetNews();
          nn.setTitle(cursor.getString(cursor.getColumnIndex(Constant.NewNewsTable.COL_TITLE)));
          nn.setCtime(cursor.getString(cursor.getColumnIndex(Constant.NewNewsTable.COL_CTIME)));
          nn.setDescription(cursor.getString(cursor.getColumnIndex(Constant.NewNewsTable.COL_DESCRIPTION)));
          nn.setPicUrl(cursor.getString(cursor.getColumnIndex(Constant.NewNewsTable.COL_PICURL)));
          nn.setUrl(cursor.getString(cursor.getColumnIndex(Constant.NewNewsTable.COL_URL)));
          nn.setNewsType(cursor.getInt(cursor.getColumnIndex(Constant.NewNewsTable.COL_NEWSTYPE)));
          list.add(nn);
      }
      cursor.close();
      db.close();
      return list;
  }
  public long addNetNews(NetNews nn){
     SQLiteDatabase db = this.openHelper.getReadableDatabase();
     ContentValues values = new ContentValues();
     values.put(Constant.NewNewsTable.COL_TITLE,nn.getTitle());
     values.put(Constant.NewNewsTable.COL_DESCRIPTION,nn.getDescription());
     values.put(Constant.NewNewsTable.COL_CTIME,nn.getCtime());
     values.put(Constant.NewNewsTable.COL_PICURL,nn.getPicUrl());
     values.put(Constant.NewNewsTable.COL_URL,nn.getPicUrl());
     values.put(Constant.NewNewsTable.COL_NEWSTYPE, nn.getNewsType());
     long id = db.insert(
             Constant.NewNewsTable.TBL_NAME,
             null,
             values
     );
     db.close();
     return id;  
     }
     public  void addNetNews(List<NetNews> netNewsList){
          for(NetNews nn:netNewsList){
        addNetNews(nn);
      }
  }
  public void removeNewsByType(int type){
      SQLiteDatabase DB = this.openHelper.getReadableDatabase();
      //根据新闻类型删除
      DB.delete(
              Constant.NewNewsTable.TBL_NAME,
              Constant.NewNewsTable.COL_NEWSTYPE+" =?",
              new String[]{type+""}
      );
      DB.close();
  }