Android中SQLite的运用_SQL

Android中SQLite的使用

<1>创建数据库

<2>更新数据库

<3>删除数据库

<4>创建数据表

<5>删除数据表

<6>增加数据

<7>更新数据

<8>删除数据

strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
?<string name="app_name">SQLite</string>
?<string name="careteDataBase">创建数据库</string>
?<string name="updateDataBase">更新数据库</string>
?<string name="deleteDataBase">删除数据库</string>
?<string name="createTable">创建数据表</string>
?<string name="deleteTable">删除数据表</string>
?<string name="insertData">增加数据</string>
?<string name="updateData">更新数据</string>
?<string name="selectData">查询数据</string>
?<string name="deleteData">删除数据</string>
</resources>

main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
?android:orientation="vertical" android:layout_width="fill_parent"
?android:layout_height="fill_parent">

?
?<Button android:id="@+id/careteDataBaseButtonId"
??android:layout_width="fill_parent" android:layout_height="wrap_content"
??android:layout_margin="10px" android:layout_gravity="center"
??android:gravity="center" android:text="@string/careteDataBase" />

?
?<Button android:id="@+id/updateDataBaseButtonId"
??android:layout_width="fill_parent" android:layout_height="wrap_content"
??android:layout_margin="10px" android:layout_gravity="center"
??android:gravity="center" android:text="@string/updateDataBase" />

?
?<Button android:id="@+id/deleteDataBaseButtonId"
??android:layout_width="fill_parent" android:layout_height="wrap_content"
??android:layout_margin="10px" android:layout_gravity="center"
??android:gravity="center" android:text="@string/deleteDataBase" />

?
?<Button android:id="@+id/createTableButtonId"
??android:layout_width="fill_parent" android:layout_height="wrap_content"
??android:layout_margin="10px" android:layout_gravity="center"
??android:gravity="center" android:text="@string/createTable" />

?
?<Button android:id="@+id/deleteTableButtonId"
??android:layout_width="fill_parent" android:layout_height="wrap_content"
??android:layout_margin="10px" android:layout_gravity="center"
??android:gravity="center" android:text="@string/deleteTable" />

?
?<Button android:id="@+id/insertDataButtonId"
??android:layout_width="fill_parent" android:layout_height="wrap_content"
??android:layout_margin="10px" android:layout_gravity="center"
??android:gravity="center" android:text="@string/insertData" />

?
?<Button android:id="@+id/updateDataButtonId"
??android:layout_width="fill_parent" android:layout_height="wrap_content"
??android:layout_margin="10px" android:layout_gravity="center"
??android:gravity="center" android:text="@string/updateData" />

?
?<Button android:id="@+id/selectDataButtonId"
??android:layout_width="fill_parent" android:layout_height="wrap_content"
??android:layout_margin="10px" android:layout_gravity="center"
??android:gravity="center" android:text="@string/selectData" />

?
?<Button android:id="@+id/deleteDataButtonId"
??android:layout_width="fill_parent" android:layout_height="wrap_content"
??android:layout_margin="10px" android:layout_gravity="center"
??android:gravity="center" android:text="@string/deleteData" />

</LinearLayout>

SQLiteActivity.java

package com.duoguo.android;

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.widget.Button;

import com.duoguo.sql.DataBaseHelpr;

/**
?* Android系统中的增删查该等操作
?*
?* @author shyboy([email protected])
?*
?*/
public class SQLiteActivity extends Activity {

?// 声明Button控件
?private Button createDataBaseButton;
?private Button updateDataBaseButton;
?private Button deleteDataBaseButton;

?private Button createTableButton;
?private Button deleteTableButton;

?private Button insertDataButton;
?private Button updataDataButton;
?private Button selectDataButton;
?private Button deleteDataButton;

[email protected]
?public void onCreate(Bundle savedInstanceState) {

??super.onCreate(savedInstanceState);
??setContentView(R.layout.main);

??// 通过控件的ID获取Button控件对象
??createDataBaseButton = (Button) findViewById(R.id.careteDataBaseButtonId);
??updateDataBaseButton = (Button) findViewById(R.id.updateDataBaseButtonId);
??deleteDataBaseButton = (Button) findViewById(R.id.deleteDataBaseButtonId);

??createTableButton = (Button) findViewById(R.id.createTableButtonId);
??deleteTableButton = (Button) findViewById(R.id.deleteTableButtonId);

??insertDataButton = (Button) findViewById(R.id.insertDataButtonId);
??updataDataButton = (Button) findViewById(R.id.updateDataButtonId);
??selectDataButton = (Button) findViewById(R.id.selectDataButtonId);
??deleteDataButton = (Button) findViewById(R.id.deleteDataButtonId);

??createDataBaseButton
????.setOnClickListener(new CreateDataBaseClickListener());// 为创建数据库添加单击事件监听器
??updateDataBaseButton
????.setOnClickListener(new UpdateDataBaseClickListener());// 为更新数据库添加单击事件监听器
??deleteDataBaseButton
????.setOnClickListener(new DeleteDataBaseClickListener());// 为删除数据库添加单击事件监听器

??createTableButton.setOnClickListener(new CreateTableClickListener());// 为创建数据表添加单击事件监听器
??deleteTableButton.setOnClickListener(new DeleteTableClickListener());// 为删除数据表添加单击事件监听器

??insertDataButton.setOnClickListener(new InsertDataClickListener());// 为增加数据添加单击事件监听器
??updataDataButton.setOnClickListener(new UpdateDataClickListener());// 为更新数据添加单击事件监听器
??selectDataButton.setOnClickListener(new SelectDataClickListener());// 为查询数据添加单击事件监听器
??deleteDataButton.setOnClickListener(new DeleteDataClickListener());// 为删除数据添加单击事件监听器

?// 创建数据库监听器
?class CreateDataBaseClickListener implements OnClickListener {

[email protected]
??public void onClick(View v) {

???DataBaseHelpr dataBaseHelpr = new DataBaseHelpr(
?????SQLiteActivity.this, "user_db");// 实例化DataBaseHelpr对象
???dataBaseHelpr.getReadableDatabase();// 创建数据库

??}

?// 更新数据库监听器
?class UpdateDataBaseClickListener implements OnClickListener {

[email protected]
??public void onClick(View v) {

???DataBaseHelpr dataBaseHelpr = new DataBaseHelpr(
?????SQLiteActivity.this, "user_db", 2);// 实例化DataBaseHelpr对象
???dataBaseHelpr.getReadableDatabase();// 更新数据库

??}

?// 删除数据库监听器
?class DeleteDataBaseClickListener implements OnClickListener {

[email protected]
??public void onClick(View v) {

???System.out.println("delete database");

???boolean success = deleteDatabase("user_db");
???if (success) {
????System.out.println("delete successfully");
???} else {
????System.out.println("delete unsuccessfully");
???}

??}

?// 创建数据表监听器
?class CreateTableClickListener implements OnClickListener {

[email protected]
??public void onClick(View v) {

???System.out.println("create table");
???DataBaseHelpr dataBaseHelpr = new DataBaseHelpr(
?????SQLiteActivity.this, "user_db");// 实例化DataBaseHelpr对象
???SQLiteDatabase sqLiteDatabase = dataBaseHelpr.getWritableDatabase();// 获取SQLiteDatabase对象
???sqLiteDatabase
?????.execSQL("create table user(id int,name varchar(20))");// 创建数据表

??}

?// 删除数据表监听器
?class DeleteTableClickListener implements OnClickListener {

[email protected]
??public void onClick(View v) {

???System.out.println("delete table");
???DataBaseHelpr dataBaseHelpr = new DataBaseHelpr(
?????SQLiteActivity.this, "user_db");// 实例化DataBaseHelpr对象
???SQLiteDatabase sqLiteDatabase = dataBaseHelpr.getWritableDatabase();// 获取SQLiteDatabase对象
???sqLiteDatabase.execSQL("drop table if exists user");// 删除数据表

??}

?// 增加数据监听器
?class InsertDataClickListener implements OnClickListener {

[email protected]
??public void onClick(View v) {

???System.out.println("insert data");
???ContentValues contentValues = new ContentValues();// 实例化ContentValues对象

???// 向数据表中插入相应的数据，其中key是列名，value是列名对应的值
???contentValues.put("id", 1);
???contentValues.put("name", "shyboy");

???DataBaseHelpr dataBaseHelpr = new DataBaseHelpr(
?????SQLiteActivity.this, "user_db");// 实例化DataBaseHelpr对象
???SQLiteDatabase sqLiteDatabase = dataBaseHelpr.getWritableDatabase();// 获取SQLiteDatabase对象，因要插入相应的数据，故获取可写入的
???sqLiteDatabase.insert("user", null, contentValues);// 增加数据

??}

?// 更新数据监听器
?class UpdateDataClickListener implements OnClickListener {

[email protected]
??public void onClick(View v) {

???System.out.println("update data");
???DataBaseHelpr dataBaseHelpr = new DataBaseHelpr(
?????SQLiteActivity.this, "user_db");// 实例化DataBaseHelpr对象
???SQLiteDatabase sqLiteDatabase = dataBaseHelpr.getWritableDatabase();// 获取SQLiteDatabase对象，因要更新相应的数据，故获取可写入的

???ContentValues contentValues = new ContentValues();// 实例化ContentValues对象
???contentValues.put("name", "playboy");

???sqLiteDatabase.update("user", contentValues, "id=?",
?????new String[] { "1" });// 更新数据，其中参数一为表名，参数二为修改的值，参数三为修改的条件，参数四位修改的条件值

??}

?// 查询数据监听器
?class SelectDataClickListener implements OnClickListener {

[email protected]
??public void onClick(View v) {

???System.out.println("select data");
???DataBaseHelpr dataBaseHelpr = new DataBaseHelpr(
?????SQLiteActivity.this, "user_db");// 实例化DataBaseHelpr对象
???SQLiteDatabase sqLiteDatabase = dataBaseHelpr.getReadableDatabase();// 获取SQLiteDatabase对象
???Cursor cursor = sqLiteDatabase.query("user", new String[] { "id",
?????"name" }, "id=?", new String[] { "1" }, null, null, null);// 查询数据

???while (cursor.moveToNext()) {
????int id = cursor.getInt(cursor.getColumnIndex("id"));// 获取编号
????System.out.println("id:" + id);
????String name = cursor.getString(cursor.getColumnIndex("name"));// 获取名称
????System.out.println("name:" + name);
???}

??}

?// 删除数据监听器
?class DeleteDataClickListener implements OnClickListener {

[email protected]
??public void onClick(View v) {

???System.out.println("delete data");
???DataBaseHelpr dataBaseHelpr = new DataBaseHelpr(
?????SQLiteActivity.this, "user_db");// 实例化DataBaseHelpr对象
???SQLiteDatabase sqLiteDatabase = dataBaseHelpr.getWritableDatabase();// 获取SQLiteDatabase对象
???sqLiteDatabase.delete("user", "id=?", new String[] { "1" });// 删除数据

??}

}

DataBaseHelpr.java

/**
?*
?*/
package com.duoguo.sql;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

/**
?* SQLiteOpenHelper的使用
?*
?* @author shyboy([email protected])
?*
?*/
public class DataBaseHelpr extends SQLiteOpenHelper {

?private static final int VERSION = 1;// 声明数据库的版本为1

?/**
? * 第一构造方法
? *
? * @param context
? *??????????? ：当前的Activity
? * @param name
? *??????????? ：数据库名称
? * @param factory
? *??????????? ：用于查询时返回相应的值
? * @param version
? *??????????? ：数据库版本
? */
?public DataBaseHelpr(Context context, String name, CursorFactory factory,
???int version) {

??super(context, name, factory, version);// 继承父类的构造方法

?/**
? * 第二构造方法
? *
? * @param context
? *??????????? ：当前的Activity
? * @param name
? *??????????? ：数据库名称
? * @param version
? *??????????? ：数据库版本
? */
?public DataBaseHelpr(Context context, String name, int version) {

??this(context, name, null, version);// 调用第一构造方法

?/**
? * 第三构造方法
? *
? * @param context
? *??????????? ：当前的Activity
? * @param name
? *??????????? ：数据库名称
? */
?public DataBaseHelpr(Context context, String name) {

??this(context, name, VERSION);// 调用第二构造方法

?/*
? * (non-Javadoc)
? *
? * @see
? * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite
? * .SQLiteDatabase) 该方法在第一次创建数据库时执行，用于创建数据表
? */
[email protected]
?public void onCreate(SQLiteDatabase arg0) {

??System.out.println("create a database");
??arg0.execSQL("create table user(id int,name varchar(20))");// 执行sql语句

?/*
? * (non-Javadoc)
? *
? * @see
? * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite
? * .SQLiteDatabase, int, int) 该方法用于更新数据库
? */
[email protected]
?public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

??System.out.println("update a database");

}

希望对你有所帮助