Android开发之SQLite数据库

Person 

package com.etime.myproject;
public class Person {
    private Integer id;
    private String name;
    private String phone;

    public Person(String name, String phone) {
        this.name = name;
        this.phone = phone;
    }
    public Person(Integer id, String name, String phone) {
        this.id = id;
        this.name = name;
        this.phone = phone;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "Person{" + "id=" + id + ", name='" + name + ''' + ", phone='" + phone + ''' + '}';
    }
}

activity_main 

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingLeft="20dp"
    android:paddingRight="20dp"
    android:orientation="vertical"
    >

    <Button
        android:id="@+id/tipButton"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="SQLite数据库测试"
        android:background="@android:color/holo_blue_light"
        android:textColor="@android:color/white"
        android:textSize="20sp"
        android:layout_marginTop="15dp"
        android:clickable="false"
        android:textAllCaps="false"
        />

    <Button
        android:id="@+id/addButton"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="添加数据"
        android:background="@android:color/holo_blue_light"
        android:textColor="@android:color/white"
        android:textSize="20sp"
        android:layout_marginTop="15dp"
        />

    <Button
        android:id="@+id/queryAllButton"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查询所有"
        android:background="@android:color/holo_blue_light"
        android:textColor="@android:color/white"
        android:textSize="20sp"
        android:layout_marginTop="15dp"
        />

    <Button
        android:id="@+id/queryOneButton"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查询单个"
        android:background="@android:color/holo_blue_light"
        android:textColor="@android:color/white"
        android:textSize="20sp"
        android:layout_marginTop="15dp"
        />

    <Button
        android:id="@+id/updateButton"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="更新数据"
        android:background="@android:color/holo_blue_light"
        android:textColor="@android:color/white"
        android:textSize="20sp"
        android:layout_marginTop="15dp"
        />

    <Button
        android:id="@+id/deleteButton"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="删除数据"
        android:background="@android:color/holo_blue_light"
        android:textColor="@android:color/white"
        android:textSize="20sp"
        android:layout_marginTop="15dp"
        />

    <Button
        android:id="@+id/countButton"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="统计数据"
        android:background="@android:color/holo_blue_light"
        android:textColor="@android:color/white"
        android:textSize="20sp"
        android:layout_marginTop="15dp"
        />

    <Button
        android:id="@+id/pageButton"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="数据分页"
        android:background="@android:color/holo_blue_light"
        android:textColor="@android:color/white"
        android:textSize="20sp"
        android:layout_marginTop="15dp"
        />

    <Button
        android:id="@+id/transactionButton"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="事务操作"
        android:background="@android:color/holo_blue_light"
        android:textColor="@android:color/white"
        android:textSize="20sp"
        android:layout_marginTop="15dp"
        />

</LinearLayout>

 DataBaseOpenHelper

package com.etime.myproject;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
 * 要点概述：
 * 1、SQLiteOpenHelper的作用：创建和更新数据库与数据表
 * 2、构造函数的用途
 *  public DataBaseOpenHelper(Context context) {
 *      super(context, DATABASE_NAME, null, 1);
 *  }
 *  当调用该构造函数的时候如果不存在名字为DATABASE_NAME的数据库就
 *  执行onCreate(SQLiteDatabase db)方法创建该数据库；如果存在则不再创建。
 *
 *  请注意该方法的第三个参数：version 版本号
 *  当version变大时会自动调用onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)方法
 *  改变数据库或数据表的。所以，该参数通常用于数据库升级。
 */
public class DataBaseOpenHelper extends SQLiteOpenHelper {
    private final static String DATABASE_NAME="test.db";
    private static DataBaseOpenHelper mDataBaseOpenHelper;

    public DataBaseOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    /**
     * 注意:
     * 将DataBaseOpenHelper写成单例的.
     * 否则当在一个for循环中频繁调用openHelper.getWritableDatabase()时
     * 会报错,提示数据库没有执行关闭操作
     */
    public static synchronized DataBaseOpenHelper getDBInstance(Context context) {
        if (mDataBaseOpenHelper == null) {
            mDataBaseOpenHelper = new DataBaseOpenHelper(context);
        }
        return mDataBaseOpenHelper;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建表
        db.execSQL("create table person(personid integer primary key autoincrement,name varchar(20),phone varchar(20))");
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

}

DBUtils 

package com.etime.myproject;

import java.util.ArrayList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DBUtils {

    /**
     *  获取数据库实例
     */
    public static SQLiteDatabase getSQLiteDatabase(Context context){
        DataBaseOpenHelper openHelper=DataBaseOpenHelper.getDBInstance(context);
        SQLiteDatabase database=openHelper.getWritableDatabase();
        return database;
    }
    /**
     *添加数据
     */
    public static void add(Context context,Person person){
        SQLiteDatabase database=getSQLiteDatabase(context);
        database.execSQL(
                "insert into person (name,phone) values(?,?)",
                new Object[]{person.getName(),person.getPhone()}
                );
    }

    /**
     * 查询所有数据
     */
    public static List<Person> queryAll(Context context){
        SQLiteDatabase database=getSQLiteDatabase(context);
        ArrayList<Person> personList=new ArrayList<Person>();
        Person person=null;
        Cursor  cursor=database.rawQuery(
                "select * from person",
                null
        );
        while(cursor.moveToNext()){
            int personidIndex=cursor.getColumnIndex("personid");
            int personid=cursor.getInt(personidIndex);

            int nameIndex=cursor.getColumnIndex("name");
            String name=cursor.getString(nameIndex);

            int phoneIndex=cursor.getColumnIndex("phone");
            String phone=cursor.getString(phoneIndex);

            person=new Person(personid, name, phone);

            personList.add(person);
        }
        return personList;
    }

    /**
     *查询单个数据
     *
     * 注意:
     * 1、rawQuery()方法查询后返回的结果是一个Cursor类的对象
     * 2、最后需要关闭cursor即cursor.close();、
     */
    public  static Person queryOne(Context context,int id){
        SQLiteDatabase database=getSQLiteDatabase(context);
        Cursor cursor=database.rawQuery(
                "select * from person where personid=?",
                new String[]{String.valueOf(id)}
                );
        while(cursor.moveToFirst()){
            int personidIndex=cursor.getColumnIndex("personid");
            int personid=cursor.getInt(personidIndex);

            int nameIndex=cursor.getColumnIndex("name");
            String name=cursor.getString(nameIndex);

            int phoneIndex=cursor.getColumnIndex("phone");
            String phone=cursor.getString(phoneIndex);

            return new Person(personid, name, phone);
        }
        cursor.close();
        return null;
    }

    /**
     *更新数据
     *
     * 因为name和phone的类型都是String,但是id是整型的
     * 所以这里的数组写成了Object类型的
     */
    public  static void update(Context context,Person person){
        SQLiteDatabase database=getSQLiteDatabase(context);
        database.execSQL(
                "update person set name=?,phone=? where personid=?",
                new Object[]{person.getName(),person.getPhone(),person.getId()}
                );
    }

    /**
     * 删除数据
     */
    public  static void delete(Context context,int id){
        SQLiteDatabase database=getSQLiteDatabase(context);
        database.execSQL(
                "delete from person where personid=?",
                new Object[]{String.valueOf(id)}
                );
    }

    /**
     * 统计数据数量
     *
     * 注意事项：
     * 在while循环里要注意终止循环,否则是个死循环
     * 因为如果cursor不为空那么cursor.moveToFirst()总是返回true
     *
     */
    public  static int count(Context context){
        SQLiteDatabase database=getSQLiteDatabase(context);
        Cursor cursor=database.rawQuery(
                "select count(*) from person",
                null
                 );
        int i=0;
        while(cursor.moveToFirst()){
            i=cursor.getInt(0);
            break;
        }
        return i;
    }

    /**
     * 分页查询
     */
    public  static List<Person> page(Context context,int offset,int resuletNumber){
        SQLiteDatabase database=getSQLiteDatabase(context);
        ArrayList<Person> personList=new ArrayList<Person>();
        Person person=null;
        Cursor  cursor=database.rawQuery(
                "select * from person limit ?,?",
                new String []{String.valueOf(offset),String.valueOf(resuletNumber)}
                );
        while(cursor.moveToNext()){
            int personidIndex=cursor.getColumnIndex("personid");
            int personid=cursor.getInt(personidIndex);

            int nameIndex=cursor.getColumnIndex("name");
            String name=cursor.getString(nameIndex);

            int phoneIndex=cursor.getColumnIndex("phone");
            String phone=cursor.getString(phoneIndex);

            person=new Person(personid, name, phone);

            personList.add(person);
        }
        return personList;
    }

    /**
     * 数据库事务
     *
     * 结束事务有两种:提交事务和回滚事务.
     * 默认情况是回滚事务!!!!
     * 事务是否提交是由事务的标志来决定:
     * 如果事务的标志位失败(false),就回滚事务;否则(true)提交事务。
     * 所以默认情况下事务的标志为失败(false)即回滚事务.
     */
    public  static void transaction(Context context,Person person){
        SQLiteDatabase database=getSQLiteDatabase(context);
        //开启事务
        database.beginTransaction();

        try{
            //依据id修改名字
            database.execSQL("update person set name=? where personid=?",
                    new Object[]{person.getName(),person.getId()}
                    );

            //模拟错误
            //int result=9527/0;

            //依据id修改电话号码
            database.execSQL("update person set phone=? where personid=?",
                    new Object[]{person.getPhone(),person.getId()}
                    );
            //设置事务的标志为成功
            database.setTransactionSuccessful();
        }catch (Exception e){
            Log.i("TAG","发生异常:"+e.toString());
        }finally{
            //结束事务,默认情况下是回滚事务
            database.endTransaction();
            Log.i("TAG","事务回滚");
        }
    }

}

MainActivity 

package com.etime.myproject;

import java.util.List;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.app.Activity;
import android.content.Context;

public class MainActivity extends Activity {
    private Button mAddButton;
    private Button mQueryAllButton;
    private Button mQueryOneButton;
    private Button mUpdateButton;
    private Button mDeleteButton;
    private Button mCountButton;
    private Button mPageButton;
    private Button mTransactionButton;
    private Context mContext;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        init();
    }

    private void init(){
        mContext=this;
        mAddButton=(Button) findViewById(R.id.addButton);
        mAddButton.setOnClickListener(new ClickListenerImpl());

        mQueryAllButton =(Button) findViewById(R.id.queryAllButton);
        mQueryAllButton.setOnClickListener(new ClickListenerImpl());

        mQueryOneButton =(Button) findViewById(R.id.queryOneButton);
        mQueryOneButton.setOnClickListener(new ClickListenerImpl());

        mUpdateButton=(Button) findViewById(R.id.updateButton);
        mUpdateButton.setOnClickListener(new ClickListenerImpl());

        mDeleteButton=(Button) findViewById(R.id.deleteButton);
        mDeleteButton.setOnClickListener(new ClickListenerImpl());

        mCountButton=(Button) findViewById(R.id.countButton);
        mCountButton.setOnClickListener(new ClickListenerImpl());

        mPageButton=(Button) findViewById(R.id.pageButton);
        mPageButton.setOnClickListener(new ClickListenerImpl());

        mTransactionButton=(Button) findViewById(R.id.transactionButton);
        mTransactionButton.setOnClickListener(new ClickListenerImpl());

    }

    //事件监听
    private class ClickListenerImpl implements OnClickListener {
        Person person=null;
        @Override
        public void onClick(View v) {
            switch (v.getId()) {
                case R.id.addButton:
                    for (int i = 0; i < 15; i++) {
                        person=new Person("zxx"+i, "9527"+i);
                        DBUtils.add(mContext,person);
                        Log.i("TAG","添加数据:"+person);
                    }
                    break;
                case R.id.queryAllButton:
                    List<Person> list=DBUtils.queryAll(mContext);
                    for (int i = 0; i < list.size(); i++) {
                        person=list.get(i);
                        Log.i("TAG","查询到的数据:"+person);
                    }
                    break;
                case R.id.queryOneButton:
                    person=DBUtils.queryOne(mContext,5);
                    Log.i("TAG","查询到的数据:"+person);
                    break;
                case R.id.updateButton:
                    person=DBUtils.queryOne(mContext,1);
                    Log.i("TAG","修改前:"+person);
                    person=new Person(1, "zxc", "6666");
                    DBUtils.update(mContext,person);
                    person=DBUtils.queryOne(mContext,1);
                    Log.i("TAG","修改后:"+person);
                    break;
                case R.id.deleteButton:
                    DBUtils.delete(mContext,2);
                    Log.i("TAG","删除数据");
                    break;
                case R.id.countButton:
                    int count=DBUtils.count(mContext);
                    Log.i("TAG","数据总量为:"+count);
                    break;
                case R.id.pageButton:
                    List<Person> personList=DBUtils.page(mContext,2, 5);
                    for (int i = 0; i < personList.size(); i++) {
                        person=personList.get(i);
                        Log.i("TAG","查询到的分页数据:"+person);
                    }
                    break;
                case R.id.transactionButton:
                    person=DBUtils.queryOne(mContext,1);
                    Log.i("TAG","事务操作前:"+person);
                    person=new Person(1, "sqe", "7777");
                    DBUtils.transaction(mContext,person);
                    person=DBUtils.queryOne(mContext,1);
                    Log.i("TAG","事务操作后:"+person);
                    break;
                default:
                    break;
            }
        }

    }
}

最后

以上就是喜悦路人为你收集整理的Android开发之SQLite数据库的全部内容，希望文章能够帮你解决Android开发之SQLite数据库所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。