1、数据库类
public class MyDB extends SQLiteOpenHelper {
public MyDB(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
private Context myContext = null;
//根据情况,自己定义修改表名称,以及字段内容。
public static String CREATE_TABLE = "create table "+ DbStatic.TABLE_NAME +"(" +
DbStatic.BOOK_NAME + " varchar(30), " +
DbStatic.ID + " Integer primary key autoincrement, " +
DbStatic.AUTHOR + " varchar(20) not null, " +
DbStatic.PRICE + " real)"; // 用于创建表的SQL语句
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE);
}
public MyDB(Context context)
{
super(context, DbStatic.DATABASE_NAME, null, DbStatic.DATABASE_VERSION);
myContext = context;
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
2、数据库名称、字段及相关定义,不是必须。可以直接放入代码中
//数据库结构,字段相关
public class DbStatic {
public final static String DATABASE_NAME = "BookStore.db";
public final static int DATABASE_VERSION = 1;
public final static String TABLE_NAME = "book";
public final static String BOOK_NAME = "bookName";
public final static String ID = "_id";
public final static String AUTHOR = "author";
public final static String PRICE = "price";
public final static String DATE = "sellData";
}
3、数据库操作相关,网上抄录,根据情况自由调整:
private void createDatabase() // 创建或者打开数据库
{
myHelper = new MyDB(this);
/*
* 调用getWritabelDatabase方法或者
* getReadableDatabase方法时,如果数据库文
* 件中不存在(注意一个数据库中可以存在多个表格),
* 那么会回调MyHelper类的onCreate方法新建一个数据库文
* 件并且在这个数据库文件中新建一
* 个book表格
*/
myHelper.getWritableDatabase();
}
private void insertDatabase() // 向数据库中插入新数据
{
if(myHelper == null)
{
myHelper = new MyDB(this);
}
database = myHelper.getWritableDatabase();
ContentValues cV = new ContentValues();
cV.put(DbStatic.BOOK_NAME, "C Language");
cV.put(DbStatic.ID, ++bookSum);
cV.put(DbStatic.AUTHOR, "zhidian");
cV.put(DbStatic.PRICE,46.1);
/*
* 这个方法是留给不熟悉SQL语句的小伙伴用的,Android把
* SQLite的插入语句封装了起来,
* 通过 ContentValues 类的对象来保存数据库中的数据,
* 于HashMap
*/
database.insert(DbStatic.TABLE_NAME, null, cV);
/*
* 对应的SQL语句:
* database.execSQL("insert into " + DatabaseStatic.TABLENAME + " values(?, ?, ?, ?)",
* new Object[]{"C Language", ++bookSum, "zhidian", 42.6});
* 或者是这个:
* database.execSQL("insert into " + DatabaseStatic.TABLENAME + "(" +
* DatabaseStatic.BOOKNAME + ", " + DatabaseStatic.ID + ", " +
* DatabaseStatic.AUTHOR + ", " + DatabaseStatic.PRICE +
* ") values(?, ?, ?, ?)", new Object[]{"C Language", ++bookSum, "zhidian", 42.6});
* 这里将 ? 号理解成一个C语言里面的占位符,然后通过 Object[] 数组中的内容补全,下同
* 参数中的 Object[] 数组是一个通用的数组,里面的数据可以转换为任意类型的数据,通过这个完成不同数据类型变量之间的储存
*/
Toast.makeText(this, "插入数据成功", Toast.LENGTH_SHORT).show();
}
private void updateDatabase() // 更新数据
{
if(myHelper == null)
{
myHelper = new MyDB(this);
}
database = myHelper.getWritableDatabase();
ContentValues cV = new ContentValues();
cV.put(DbStatic.AUTHOR, "xiaoming");
/*
* 调用 update 方法,将书名为"C Language" 的书作者更新为 "xiaoming
*/
database.update(DbStatic.TABLE_NAME, cV,
DbStatic.BOOK_NAME + "= ?", new String[]{"C Language"});
/*
* 对应的SQL语句:
* database.execSQL("update " + DatabaseStatic.TABLENAME + " set " + DatabaseStatic.AUTHOR +
* "= ? where " + DatabaseStatic.BOOKNAME + " = ?", new String[]{"xiaoming", "C Language"});
*/
Toast.makeText(this, "数据更新成功", Toast.LENGTH_SHORT).show();
}
private void deleteDatabase() // 数据库中删除数据
{
if(myHelper == null)
{
myHelper = new MyDB(this);
}
database = myHelper.getWritableDatabase();
/*
* 调用 delete 方法删除数据库中的数据
* 对应的SQL语句:
* database.execSQL("delete from " +
* DatabaseStatic.TABLE_NAME + " where " +
* DatabaseStatic.BOOK_NAME + " = ?", new
* String[]{"C Language"});
*/
database.delete(DbStatic.TABLE_NAME, DbStatic.BOOK_NAME + " = ? ",
new String[]{"C Language"});
Toast.makeText(this, "数据删除成功", Toast.LENGTH_SHORT).show();
}
private void searchDatabase() // 查询数据库中的数据
{
if(myHelper == null)
{
myHelper = new MyDB(this);
}
database = myHelper.getWritableDatabase();
/*
* 调用database的query方法,第一个参数是要查询的表名,
* 后面的参数是一些查询的约束条件,对应于SQL语句的一些参
* 数, 这里全为null代表查询表格中所有的数据
* 查询的结果返回一个 Cursor对象
* 对应的SQL语句:
* Cursor cursor = database.rawQuery("select * from book", null);
*/
Cursor cursor = database.query(DbStatic.TABLE_NAME, null, null, null, null, null, null);
StringBuilder str = new StringBuilder();
if(cursor.moveToFirst()) // 显示数据库的内容
{
for(; !cursor.isAfterLast(); cursor.moveToNext()) // 获取查询游标中的数据
{
str.append(cursor.getString(cursor.getColumnIndex(DbStatic.ID)) + " ");
str.append(cursor.getString(cursor.getColumnIndex(DbStatic.BOOK_NAME)) + " ");
str.append(cursor.getString(cursor.getColumnIndex(DbStatic.AUTHOR)) + " ");
str.append(cursor.getString(cursor.getColumnIndex(DbStatic.PRICE)) + "\n");
}
}
cursor.close(); // 记得关闭游标对象
if(str.toString().equals(""))
{
str.append("数据库为空!");
textView.setTextColor(Color.RED);
}
else
{
textView.setTextColor(Color.BLACK);
}
textView.setText(str.toString());
}