Android开发之SQLite数据库

直接上代码，包含创建数据库，建表，添加数据，更新数据，删除数据，查询数据

建数据库类MyDatabaseHelper

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;
    }
    public void onCreate(SQLiteDatabase db){
        db.execSQL(CREATE_BOOK);
        db.execSQL(CREATE_CATEGORY);
        Toast.makeText(mContext, "Create succeeded", Toast.LENGTH_SHORT).show();
    }
    public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){

    }
}

主类

package com.example.databasetest;

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.Menu;
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,"TookStore.db",null,2);

        Button createDatabase=(Button)findViewById(R.id.create_database);
        Button addData=(Button)findViewById(R.id.add_data);
        Button updateData=(Button)findViewById(R.id.update_data);
        Button deleteButton=(Button)findViewById(R.id.delete_data);
        Button queryButton=(Button)findViewById(R.id.query_data);

        addData.setOnClickListener(new OnClickListener(){
            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.98);
                db.insert("Book", null, values);
                //SQL 模式 db.execSQL("insert into Book(name,author,pages,price) values(?,?,?,?)",
                //                   new String[]{"The Da Vinic Code","DanBrown","454","16.98"});
                values.clear();
                values.put("name", "The Lost Symbol");
                values.put("author", "Dan Brown");
                values.put("pages", 510);
                values.put("price", 19.98);
                db.insert("Book", null, values);


            }
        });
        updateData.setOnClickListener(new OnClickListener(){
            public void onClick(View v)
            {
                SQLiteDatabase db=dbHelper.getWritableDatabase();
                ContentValues values=new ContentValues();
                values.put("price", 99.99);
                db.update("Book", values, "name=?",new String[]{"The Da Vinci Code"} );
                // SQL db.execSQL("update Book set price=? where name=?",new String[]{"99.99","The Da Vinci Code"});
            }
        });
        createDatabase.setOnClickListener(new OnClickListener(){
            public void onClick(View v)
            {
                dbHelper.getWritableDatabase();
            }
        });
        deleteButton.setOnClickListener(new OnClickListener(){
            public void onClick(View v){
                SQLiteDatabase db=dbHelper.getWritableDatabase();
                db.delete("Book", "Pages > ?", new String[]{"500"});
                //db.execSQL("delete from Book where pages > ?",new String[]{"500"});
            }
        });
        queryButton.setOnClickListener(new OnClickListener(){
            public void onClick(View v){
                SQLiteDatabase db=dbHelper.getWritableDatabase();
                //查询Book表中所有的数据
                Cursor cursor=db.query("Book", null, 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"));
                        //db.rawQuery("select * from Book",null);
                        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();
            }
        });
    }


    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        // Inflate the menu; this adds items to the action bar if it is present.
        getMenuInflater().inflate(R.menu.main, menu);
        return true;
    }

}

布局文件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>