Android学习--SQLite数据库存储使用

SQLite是关系型数据库，使用涉及到CRUD（即通常所说的增删改查）

1、创建数据库

自定义MyDatabaseHelper继承SQLiteOpenHelper类，在其中进行创表

package com.example.databasetest; import android.content.Context; import android.database.sqlite.SQLiteDatabase; 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, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); this.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) { } }

在布局文件activity_main.xml增加button

<?xml version="1.0" encoding="utf-8"?> <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:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/create_database" android:text="Create Database"/> </LinearLayout>

在MainActivity中创建

package com.example.databasetest; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.util.Log; import android.view.View; import android.widget.Button; public class MainActivity extends AppCompatActivity { private MyDatabaseHelper dbHelper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); Button button = (Button) findViewById(R.id.create_database); dbHelper = new MyDatabaseHelper(MainActivity.this, "BookStore.db",null, 1); button.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { dbHelper.getWritableDatabase(); } }); }

在模拟器或手机中运行会有Toast提示

2、升级数据库

再新增一个表

package com.example.databasetest; import android.content.Context; import android.database.sqlite.SQLiteDatabase; 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, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); this.mContext = context; } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_BOOK); db.execSQL(CREATE_CATEGORY); 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); } }

主MainActivity中

package com.example.databasetest; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.util.Log; import android.view.View; import android.widget.Button; public class MainActivity extends AppCompatActivity { private MyDatabaseHelper dbHelper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); Button button = (Button) findViewById(R.id.create_database); dbHelper = new MyDatabaseHelper(MainActivity.this, "BookStore.db",null, 2); button.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { dbHelper.getWritableDatabase(); } }); } }

3、增加、更新、删除、查询分别在布局中增加button，在主MainActivity中增加相应逻辑处理

布局文件

<?xml version="1.0" encoding="utf-8"?> <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:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/create_database" android:text="Create Database"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/add_data" android:text="Add Data"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/update_data" android:text="Update Data"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/delete_data" android:text="Delete Data"/> <Button android:layout_width="match_parent" android:layout_height="wrap_content" android:id="@+id/query_data" android:text="Query Data"/> </LinearLayout> 主MainActivity类 package com.example.databasetest; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.util.Log; import android.view.View; import android.widget.Button; public class MainActivity extends AppCompatActivity { private MyDatabaseHelper dbHelper; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); Button button = (Button) findViewById(R.id.create_database); dbHelper = new MyDatabaseHelper(MainActivity.this, "BookStore.db",null, 2); button.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { dbHelper.getWritableDatabase(); } }); Button saveData = (Button) findViewById(R.id.add_data); saveData.setOnClickListener(new View.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 View.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 deleteData = (Button) findViewById(R.id.delete_data); deleteData.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { SQLiteDatabase db = dbHelper.getWritableDatabase(); db.delete("Book","name=?", new String[]{"The Da Vinci Code"}); } }); Button queryData = (Button) findViewById(R.id.query_data); queryData.setOnClickListener(new View.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")); 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()); } } }); } }

效果如下