本次实验的任务是实现书籍的增删改查,其实我们可以对数据进行的操作也就无非四种,即CRUD。其中C代表添加(Create),R代表查询(Retrieve),U代表更新(Update),D代表删除(Delete)。每一种操作又各自对应了一种SQL命令,如果你比较熟悉SQL语言的话,一定会知道添加数据时使用insert,查询数据时使用select,更新数据时使用update,删除数据时使用delete。
一、实验效果图:
二、主要代码:
1、Java代码:
(1)MyDatabaseHelper.Java
Android为了让我们能够更加方便地管理数据库,专门提供了一个SQLiteOpenHelper帮助类,借助这个类就可以非常简单地对数据库进行创建和升级。
- package com.example.databasetest;
- import android.content.Context;
- import android.database.sqlite.SQLiteDatabase;
- import android.database.sqlite.SQLiteDatabase.CursorFactory;
- import android.database.sqlite.SQLiteOpenHelper;
- import android.widget.Toast;
- public class MyDatabaseHelper extends SQLiteOpenHelper {
- public static final String CREATE_BOOK = "create table book ("
- + "id integer primary key autoincrement, " + "author text, "
- + "price real, " + "pages integer, " + "name text)";
- public static final String CREATE_CATEGORY = "create table Category ("
- + "id integer primary key autoincrement, " + "category_name text, "
- + "category_code integer)";
- private Context mContext;
- public MyDatabaseHelper(Context context, String name,
- CursorFactory factory, int version) {
- super(context, name, factory, version);
- mContext = context;
- }
- @Override
- public void onCreate(SQLiteDatabase db) {
- db.execSQL(CREATE_BOOK);
- Toast.makeText(mContext, "Create succeeded", Toast.LENGTH_SHORT).show();
- }
- @Override
- public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
- db.execSQL("drop table if exists Book");
- db.execSQL("drop table if exists Category");
- onCreate(db);
- }
- }
(2)MainActivity.java
db.execSQL("insert into Book (name, author, pages, price) values(?, ?, ?, ?)",
new String[] { "The Da Vinci Code", "Dan Brown", "454", "16.96" });
db.execSQL("insert into Book (name, author, pages, price) values(?, ?, ?, ?)",
new String[] { "The Lost Symbol", "Dan Brown", "510", "19.95" });
- package com.example.databasetest;
- import android.app.Activity;
- import android.content.ContentValues;
- import android.database.Cursor;
- import android.database.sqlite.SQLiteDatabase;
- import android.os.Bundle;
- import android.util.Log;
- import android.view.View;
- import android.view.View.OnClickListener;
- import android.widget.Button;
- public class MainActivity extends Activity {
- private MyDatabaseHelper dbHelper;
- @Override
- protected void onCreate(Bundle savedInstanceState) {
- super.onCreate(savedInstanceState);
- setContentView(R.layout.activity_main);
- dbHelper = new MyDatabaseHelper(this, "BookStore.db", null, 2);
- /* 创建数据 */
- Button createDatabase = (Button) findViewById(R.id.create_database);
- createDatabase.setOnClickListener(new OnClickListener() {
- @Override
- public void onClick(View v) {
- dbHelper.getWritableDatabase();
- }
- });
- /* 添加数据 */
- Button addData = (Button) findViewById(R.id.add_data);
- addData.setOnClickListener(new OnClickListener() {
- @Override
- public void onClick(View v) {
- SQLiteDatabase db = dbHelper.getWritableDatabase();
- ContentValues values = new ContentValues();
- // 开始组装第一条数据
- values.put("name", "The Da Vinci Code");
- values.put("author", "Dan Brown");
- values.put("pages", 454);
- values.put("price", 16.96);
- db.insert("Book", null, values); // 插入第一条数据
- values.clear();
- // 开始组装第二条数据
- values.put("name", "The Lost Symbol");
- values.put("author", "Dan Brown");
- values.put("pages", 510);
- values.put("price", 19.95);
- db.insert("Book", null, values); // 插入第二条数据
- }
- });
- /* 更新数据 */
- Button updateData = (Button) findViewById(R.id.update_data);
- updateData.setOnClickListener(new OnClickListener() {
- @Override
- public void onClick(View v) {
- SQLiteDatabase db = dbHelper.getWritableDatabase();
- ContentValues values = new ContentValues();
- values.put("price", 10.99);
- db.update("Book", values, "name = ?",
- new String[] { "The Da Vinci Code" });
- }
- });
- /* 删除数据 */
- Button deleteButton = (Button) findViewById(R.id.delete_data);
- deleteButton.setOnClickListener(new OnClickListener() {
- @Override
- public void onClick(View v) {
- SQLiteDatabase db = dbHelper.getWritableDatabase();
- db.delete("Book", "pages > ?", new String[] { "500" });
- }
- });
- /* 查询数据 */
- Button queryButton = (Button) findViewById(R.id.query_data);
- queryButton.setOnClickListener(new OnClickListener() {
- @Override
- public void onClick(View v) {
- SQLiteDatabase db = dbHelper.getWritableDatabase();
- // 查询Book表中所有的数据
- Cursor cursor = db.query("Book", null, null, null, null, null,
- null);
- if (cursor.moveToFirst()) {
- do {
- // 遍历Cursor对象,取出数据并打印
- String name = cursor.getString(cursor
- .getColumnIndex("name"));
- String author = cursor.getString(cursor
- .getColumnIndex("author"));
- int pages = cursor.getInt(cursor
- .getColumnIndex("pages"));
- double price = cursor.getDouble(cursor
- .getColumnIndex("price"));
- Log.d("MainActivity", "book name is " + name);
- Log.d("MainActivity", "book author is " + author);
- Log.d("MainActivity", "book pages is " + pages);
- Log.d("MainActivity", "book price is " + price);
- } while (cursor.moveToNext());
- }
- cursor.close();
- }
- });
- }
- }
activity_main.xml:
- <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
- android:layout_width="match_parent"
- android:layout_height="match_parent"
- android:orientation="vertical" >
- <Button
- android:id="@+id/create_database"
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:text="Create database" />
- <Button
- android:id="@+id/add_data"
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:text="Add data" />
- <Button
- android:id="@+id/update_data"
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:text="Update data" />
- <Button
- android:id="@+id/delete_data"
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:text="Delete data" />
- <Button
- android:id="@+id/query_data"
- android:layout_width="match_parent"
- android:layout_height="wrap_content"
- android:text="Query data" />
- </LinearLayout>
1、创建数据、添加数据、更新数据、删除数据都较为简单,在操作方法上有一定的相似性,而查询数据也是在CRUD中最复杂的一种操作。
2、使用SQL来完成CRUD操作:
db.execSQL("insert into Book (name, author, pages, price) values(?, ?, ?, ?)",
new String[] { "The Da Vinci Code", "Dan Brown", "454", "16.96" });
db.execSQL("insert into Book (name, author, pages, price) values(?, ?, ?, ?)",
new String[] { "The Lost Symbol", "Dan Brown", "510", "19.95" });