数据库的创建以及对数据库的增删改查
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(); }