Android开发之SQLite数据库-白红宇的个人博客

Android开发之SQLite数据库

发布日期：2021-06-30 11:20:25 浏览次数：2 分类：技术文章

本文共 12161 字，大约阅读时间需要 40 分钟。

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

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
   
     queryAll(Context context){        SQLiteDatabase database=getSQLiteDatabase(context);        ArrayList
    
      personList=new ArrayList
     
      ();        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
      
        page(Context context,int offset,int resuletNumber){        SQLiteDatabase database=getSQLiteDatabase(context);        ArrayList
       
         personList=new ArrayList
        
         (); 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
   
     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
    
      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;            }        }    }}

转载地址：https://it9527.blog.csdn.net/article/details/8195260 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！

上一篇：Servlet报404错误

下一篇：Android开发之Fragment

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！

发表评论

最新留言

关于作者

推荐文章