实验九 SQLite数据库的增删改查

一、实验目的

（1）学习SQLite的应用

（2）创建一个自己的数据库<MyBookStore.db>

（3）实现数据库的增加、删除、修改、查找功能

二、实验内容

           制作一个APP模拟电子书店的一部分功能。首先需要创建一个名为MyBookStore.db的数据库，在数据库中新建一张Book表，表中有id(主键)、作者、价格、页数和书名等列。然后增加一些按钮，分别实现对该数据库进行增加或删除书籍，修改价格，查找书籍信息等操作的功能。

三、实验结果图

创建数据库，在DDMS中的file explorer中查看的结果：

添加数据（将数据库导出到电脑上，在数据库软件中查看）：

更新数据：

删除数据：

查询数据（LogCat中查看结果）：

四、实验代码

MainActivity:

package com.test8.database;

import android.os.Bundle;
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
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, "MyBookStore.db", null, 1);
		
		Button createButton = (Button) findViewById(R.id.create_db);
		createButton.setOnClickListener(new OnClickListener()
		{			
			@Override
			public void onClick(View view)
			{
				// TODO Auto-generated method stub
				dbHelper.getWritableDatabase();				
			}
		});
		
		Button addData = (Button) findViewById(R.id.add_data);
		addData.setOnClickListener(new OnClickListener()
		{		
			@Override
			public void onClick(View view)
			{
				// TODO Auto-generated method stub
				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 view)
			{
				// TODO Auto-generated method stub
				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 deletedata = (Button) findViewById(R.id.delete_data);
		deletedata.setOnClickListener(new OnClickListener()
		{			
			@Override
			public void onClick(View view)
			{
				// TODO Auto-generated method stub
				SQLiteDatabase db = dbHelper.getWritableDatabase();
				db.delete("Book", "pages > ? ", new String[] {"500"});
			}
		});
		
		Button querydata = (Button) findViewById(R.id.query_data);
		querydata.setOnClickListener(new OnClickListener()
		{		
			@Override
			public void onClick(View view)
			{
				// TODO Auto-generated method stub
				SQLiteDatabase db = dbHelper.getWritableDatabase();
				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 are" + pages);
						Log.d("MainActivity", "book price is" + price);
					}while(cursor.moveToNext());
				}
				cursor.close();
			}
		});
		
		/*
		 * 添加数据的方法：
		 * 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" });
		 * 
		 * 更新数据的方法
		 * db.execSQL("update Book set price = ? where name = ?", new String[] {"10.99", "The Da Vinci Code" });
		 * 
		 * 删除数据的方法
		 * db.execSQL("delete from Book where pages > ?",new String[] { "500" });
		 * 
		 * 查询数据的方法
		 * db.rawQuery("select * from Book", null);
		 */
	}

}

package com.test8.database;

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)";

	private Context mcontext;

	public MyDatabaseHelper(Context context, String name,
			CursorFactory factory, int version)
	{
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
		mcontext = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db)
	{
		// TODO Auto-generated method stub
		db.execSQL(CREATE_BOOK);
		Toast.makeText(mcontext, "创建成功", Toast.LENGTH_SHORT).show();

	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2)
	{
		// TODO Auto-generated method stub

	}

}

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity" >

   <Button
       android:id="@+id/create_db"
       android:layout_width="match_parent"
       android:layout_height="wrap_content"
       android:text="@string/create_db" />

   <Button
       android:id="@+id/add_data"
       android:layout_width="match_parent"
       android:layout_height="wrap_content"
       android:text="@string/add_data" />
   
   <Button
       android:id="@+id/update_data"
       android:layout_width="match_parent"
       android:layout_height="wrap_content"
       android:text="@string/update_data" />
   
   <Button
       android:id="@+id/delete_data"
       android:layout_width="match_parent"
       android:layout_height="wrap_content"
       android:text="@string/delete_data" />
   
   <Button
       android:id="@+id/query_data"
       android:layout_width="match_parent"
       android:layout_height="wrap_content"
       android:text="@string/query_data" />
   
</LinearLayout>

五、存在的问题

    标准的数据库语句命令在注释中给出来了，但是用本文的方法也可以实现。另外，可以在自带的adb shell中打开数据库查看。