SQLiteOpenHelper数据库的创建or升级or增删改查

来源：互联网发布：phpstorm端口号改80 编辑：程序博客网时间：2024/05/14 06:34

SQLiteOpenHelper数据库的创建or升级or增删改查
import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.Window;
import android.widget.Button;
import android.widget.Toast;

public class MainActivity extends Activity {

private Button createDataBase;private MyDataBaseHelper dbHelper;private Button addData;private Button updateData;private Button deleteData;private Button queryData;private Button replaceData;@Overrideprotected void onCreate(Bundle savedInstanceState) {    super.onCreate(savedInstanceState);    requestWindowFeature(Window.FEATURE_NO_TITLE);    setContentView(R.layout.activity_main);    init();    setAction();}/** 初始化View */private void init() {    createDataBase = (Button) findViewById(R.id.create_database);    addData = (Button) findViewById(R.id.add_data);    updateData = (Button) findViewById(R.id.update_data);    deleteData = (Button) findViewById(R.id.delete_data);    queryData = (Button) findViewById(R.id.query_data);    replaceData = (Button) findViewById(R.id.replace_data);    /** 通过构造函数的参数将数据库信息指定成功;做数据库升级时要改版本号 1改为2 */    dbHelper = new MyDataBaseHelper(this, "BookStore.db", null, 2);}/** 设置监听事件 */private void setAction() {    createDataBase.setOnClickListener(new OnClickListener() {        @Override        public void onClick(View v) {            dbHelper.getWritableDatabase();        }    });    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 Last Symbol");            values.put("author", "dan Brown");            values.put("pages", 510);            values.put("price", 19.96);            /** 插入第二条数据 */            db.insert("book", null, values);            Toast.makeText(MainActivity.this, "inset succeeded----",                    Toast.LENGTH_SHORT).show();        }    });    updateData.setOnClickListener(new OnClickListener() {        @Override        public void onClick(View v) {            SQLiteDatabase db = dbHelper.getWritableDatabase();            ContentValues values = new ContentValues();            values.put("price", 10.99);            /** 以下的意思是将名the Da Vinci Code这本书的价格改为10.99 */            db.update("Book", values, "name = ?",                    new String[] { "the Da Vinci Code" });            Toast.makeText(MainActivity.this, "update succeeded+++++++",                    Toast.LENGTH_SHORT).show();        }    });    deleteData.setOnClickListener(new OnClickListener() {        @Override        public void onClick(View v) {            SQLiteDatabase db = dbHelper.getWritableDatabase();            /** 删除页数超过500页的书籍 */            db.delete("Book", "pages > ?", new String[] { "500" });            Toast.makeText(MainActivity.this, "delete succeeded$$$$$$$",                    Toast.LENGTH_SHORT).show();        }    });    queryData.setOnClickListener(new OnClickListener() {        @Override        public void onClick(View v) {            SQLiteDatabase db = dbHelper.getWritableDatabase();            /** 查询表中所有的数据 */            Cursor cursor = db.query("Book", null, null, null, null, null,                    null);            if (cursor.moveToFirst()) {                do {                    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"));                } while (cursor.moveToNext());            }            cursor.close();            Toast.makeText(MainActivity.this, "query succeeded@@@@@@",                    Toast.LENGTH_SHORT).show();        }    });    /** 使用事务进行数据的更换，这样会比较安全  */    replaceData.setOnClickListener(new OnClickListener() {        @Override        public void onClick(View v) {            SQLiteDatabase db = dbHelper.getWritableDatabase();            /** 开启事务 作用：删除表里面的数据的时，要么成功，要么失败，两个事件只能同时完成 */            db.beginTransaction();            try {                db.delete("Book", null, null);                if (true) {                    /** 这里抛出一个异常，让事务失败 */                    throw new NullPointerException();                }                @SuppressWarnings("unused")                ContentValues values = new ContentValues();                values.put("name", "Game of Thrones");                values.put("author", "George Martin");                values.put("pages", 720);                values.put("price", 20.55);                db.insert("Book", null, values);                /** 事务已经执行完成 */                db.setTransactionSuccessful();            } catch (Exception e) {                e.printStackTrace();            } finally {                /** 结束事务，在finally里面结束，无论如何都会执行到这边的 */                db.endTransaction();            }        }    });}

}

直接上代码
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

/**
*
* @项目名：DataBaseTest
* @类名称：MyDataBaseHelper
* @类描述：对数据库的创建和操作
* @创建人：HXF
* @修改人：
* @创建时间：2015-8-4 上午9:07:02
* @version
*
*/
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);    this.mContext = context;}@Overridepublic void onCreate(SQLiteDatabase db) {    /**     * 创建数据库表格,当Toast打出创建成功的时候，此事就有了book这个表格，     * 数据库的名字是在MainActivity中利用构造函数的参数来命名的,后面的那个表格     * 是对上一个表格的升级操作，     */    db.execSQL(CREATE_BOOK);    db.execSQL(CREATE_CATEGORY);    Toast.makeText(mContext, "Create Successed", Toast.LENGTH_SHORT).show();}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {    /** 这是对版本号进行升级的操作，如果数据库中有这个表格的时候，就会删除重新创建*/    db.execSQL("drop table if exists Book");    db.execSQL("drop table if exists category");    onCreate(db);}

}

0 0