Android之数据库操作

分手后的思念是犯贱 2022-07-29 09:20 112阅读 0赞

### 安卓数据库帮助类 ###

/**
     * 数据库帮助类，用于管理数据库
     * @author Administrator
     *
     */
    public class PersonSQLiteOpenHelper extends SQLiteOpenHelper {
        
    
        private String tag="PersonSQLiteOpenHelper";
    
        public PersonSQLiteOpenHelper(Context context) {
        //数据库名，数据库版本号
            super(context, "zj.db", null, 2);
            // TODO Auto-generated constructor stub
        }
    
        /**
         * 数据库第一次创建时调用此方法
         */
        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            //操作数据库
            String sql="create table person(_id integer primary key,name varchar(20),age integer);";
            db.execSQL(sql);
    
        }
    
        /**
         * 更新数据库的内容
         */
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub
            if(oldVersion==1&&newVersion==2)
            {
                Log.i(tag, "数据库更新了");
                //在表中添加一个余额列
                db.execSQL("alter table person add balance interger;");
            }else if(oldVersion==2&&newVersion==3)
            {
                //其他操作
            }
    
        }
    
    }

## 其中onCreate只在第一次创建是调用，OnUpergrade在数据库版本号升级，如要修改数据库，为数据库增加一列，或删除一列的情况下使用 ##

## 数据库操作 ##

### 打开数据库 ###

private PersonSQLiteOpenHelper mOpenHelper;//数据库帮助类
    
        public PersonDao(Context context)
        {
            mOpenHelper = new PersonSQLiteOpenHelper(context);
    
        }

### 由mOpenHelper得到数据库 ###

SQLiteDatabase db= mOpenHelper.getWritableDatabase();

### 此时可设置版本号,便会根据版本号调用OnUpgrate ###

db.getVersion();
            db.setVersion(2);

## 对数据库增删改查有两种方式    ##

### 插入数据 ,直接用SQL语句 ###

public void insert(Person person)
        {
            SQLiteDatabase db= mOpenHelper.getWritableDatabase();
            if(db.isOpen())
    
            {
                //执行添加的操作
                db.execSQL("insert into person(name, age) values(?, ?);",new Object[]{person.getName(),person.getAge()});
                db.close();
            }
        }

### 插入数据方法二 ###

public void insert(Person person)
        {
            SQLiteDatabase db= mOpenHelper.getWritableDatabase();
            if(db.isOpen())
            {
                ContentValues values=new ContentValues();
                values.put("name", person.getName());
                values.put("age", person.getAge());
                //执行添加的操作
                long id=db.insert("person", null, values);
                Log.i(tag, "id:"+id);
                db.close();
            }
        }

### 第一种方法删除与更新 ###

public void delete(int id)
        {
            SQLiteDatabase db= mOpenHelper.getWritableDatabase();
            if(db.isOpen())
            {
                db.execSQL("delete from person where _id = ?;",new Integer[]{id});
                //执行添加的操作
                db.close();
            }
        }
    
        public void update(int id,String name)
        {
            SQLiteDatabase db= mOpenHelper.getWritableDatabase();
            if(db.isOpen())
            {
                db.execSQL("update person set name = ? where id = ?;",new Object[]{name,id});
                //执行添加的操作
                db.close();
            }
        }

### 第二种方法删除与更新 ###

public void delete(int id)
        {
            SQLiteDatabase db= mOpenHelper.getWritableDatabase();
            if(db.isOpen())
            {
                //执行添加的操作
                String whereClause=" _id = ?";
                String []whereArgs={id+""};
                int count=db.delete("person", whereClause,whereArgs );
                Log.i(tag, "count="+count+"行");
                db.close();
            }
        }
    
        public void update(int id,String name)
        {
            SQLiteDatabase db= mOpenHelper.getWritableDatabase();
            if(db.isOpen())
            {
                //执行添加的操作
                ContentValues values=new ContentValues();
                values.put("name", name);
    
                int count=db.update("person", values, "_id=?", new String[]{id+""});
                Log.i(tag, "修改了count="+count+"行");
                db.close();
            }
        }

### 第一种方法查询一项与查询所有 ###

public List<Person> queryAll()
        {
            SQLiteDatabase db= mOpenHelper.getReadableDatabase();
            if(db.isOpen())
            {
                Cursor cursor= db.rawQuery("select * from person;", null);
                if(cursor!=null&&cursor.getCount()>0)
                {
                    List<Person> personList=new ArrayList<Person>();
                    int id;
                    String name;
                    int age;
                    while(cursor.moveToNext())
                    {
                         id=cursor.getInt(0);
                         name=cursor.getString(1);
                         age=cursor.getInt(2);
                        personList.add(new Person(id, name, age));
    
                    }
                    db.close();
                    return personList;
                }
                db.close();
            }
            return null;
        }
    
        public Person queryItem(int id)
        {
            SQLiteDatabase db= mOpenHelper.getReadableDatabase();
            if(db.isOpen())
            {
                Cursor cursor= db.rawQuery("select * from person where id= ?;", new String []{id+""});
                if(cursor!=null&&cursor.moveToFirst())
                {
                    int id1=cursor.getInt(0);
                    String name=cursor.getString(1);
                    int age=cursor.getInt(2);
                    db.close();
                    return new Person(id1,name,age);
                }
                db.close();
            }
            return null;
        }

### 第二种方法查询数据库 ###

public List<Person> queryAll()
        {
            SQLiteDatabase db= mOpenHelper.getReadableDatabase();
            if(db.isOpen())
            {
                String [] columns={
       "_id","name","age"};
                String selection=null;
                String[] selectionArgs=null;
                String groupBy=null;
                String having=null;
                String orderBy=null;
    
                Cursor cursor=db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);
                int id;
                String name;
                int age;
                if(cursor!=null&&cursor.getCount()>0)
                {
                    List<Person> personList=new ArrayList<Person>();
                    while(cursor.moveToNext())
                    {
                        //向下移一位
                         id=cursor.getInt(0);
                         name=cursor.getString(1);
                         age=cursor.getInt(2);
                        personList.add(new Person(id,name,age));
                    }
                    db.close();
                    return personList;
                }
                db.close();
            }
            return null;
        }
    
        public Person queryItem(int id)
        {
            SQLiteDatabase db= mOpenHelper.getReadableDatabase();
            if(db.isOpen())
            {
                String [] columns={
       "_id","name","age"};
                String selection="_id=?";
                String[] selectionArgs={id+""};
                String groupBy=null;
                String having=null;
                String orderBy=null;
    
                Cursor cursor=db.query("person", columns, selection, selectionArgs, groupBy, having, orderBy);
                int _id;
                String name;
                int age;
                if(cursor!=null&&cursor.moveToFirst())
                {
                    _id=cursor.getInt(0);
                    name=cursor.getString(1);
                    age=cursor.getInt(2);
                    db.close();
                    return new Person(_id,name,age);
                }
                db.close();
            }
            return null;
        }

#### 数据库的事务，防止多线程并发操作 ####

public void testTransaction()
        {
            PersonSQLiteOpenHelper openHelper=new PersonSQLiteOpenHelper(getContext());
            SQLiteDatabase db= openHelper.getWritableDatabase();
    
            if(db.isOpen())
            {
                try
                {
                //开户事务
                db.beginTransaction();
                //执行业务逻辑
                db.execSQL("update person set balance =balance -1000 where name='zhangsan';");
    
                //ATM机损坏
                //int result=10/0;
                db.execSQL("update person set balance =balance +1000 where name='lisi';");
                //标记事务成功
                db.setTransactionSuccessful();
                }finally
                {
                    //停止事务
                    db.endTransaction();
                }
    
                db.close();
            }
        }
    
        public void testTransationInsert()
        {
            PersonSQLiteOpenHelper openHelper=new PersonSQLiteOpenHelper(getContext());
            SQLiteDatabase db= openHelper.getWritableDatabase();
    
            if(db.isOpen())
            {
                //记录当前时间
                long start=System.currentTimeMillis();
                //开始添加数据
                try
                {
                db.beginTransaction();
                for(int i=0;i<10000;i++)
                {
    
                    db.execSQL("insert into person(name, age, balance) values('wang" + i + "', " + (10 + i) + ", " + (10000 + i) + ")");            
                    }
                db.setTransactionSuccessful();
                }finally
                {
                    db.endTransaction();
                }
    
                //记住结束时间,计算耗时
                long end=System.currentTimeMillis();
                long diff=end-start;
                Log.i(tag, "耗时:"+diff+"ms");
                db.close();
            }
        }

### Android数据库基本操作完成 ###