安卓中SQLite数据库操作学习

 Android中内置的是SQLite小型数据库,类似access一样是以文件方式存在的,操作起来十分方便。android操作SQLite需要继承自SQLiteOpenHelper 来得到连接。实现了2个方法:onCreate和onUpgrade。第一次获得对数据库的读或写操作的时候调用onCreate,所以这里面写创建数据表的语句;在数据库版本变化的时候会调用onUpgrade,所以如果表结构变化了就在这里修改。

        下面的代码继承了SQLiteOpenHelper ,并且自己写了几个增删改查方法,将对数据库操作的DAO模块继承到一起,以后操作数据库用这一个java文件基本就OK啦。

[代码]java代码：

?

001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030 031 032 033 034 035 036 037 038 039 040 041 042 043 044 045 046 047 048 049 050 051 052 053 054 055 056 057 058 059 060 061 062 063 064 065 066 067 068 069 070 071 072 073 074 075 076 077 078 079 080 081 082 083 084 085 086 087 088 089 090 091 092 093 094 095 096 097 098 099 100 101 102 103 104

package cn.itcast.service;   import java.util.ArrayList; import java.util.List;   import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper;   public class DBOpenHelper extends SQLiteOpenHelper {      private static final String DATABASENAME = "test.db" ; //数据库名称      private static final int DATABASEVERSION = 1; //数据库版本,大于0        public DBOpenHelper(Context context) {          super (context, DATABASENAME, null , DATABASEVERSION);      }        @Override      public void onCreate(SQLiteDatabase db) {          db.execSQL( "CREATE TABLE person (personid integer primary key autoincrement, name varchar(20), amount integer)" ); //执行有更改的sql语句      }        @Override      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {          db.execSQL( "DROP TABLE IF EXISTS person" );          onCreate(db);      }        public void payment(){          SQLiteDatabase db = getWritableDatabase();          db.beginTransaction(); //事启事务          try {              db.execSQL( "update person set amount=amount-10 where personid=?" , new Object[]{1});              db.execSQL( "update person set amount=amount+10 where personid=?" , new Object[]{2});              db.setTransactionSuccessful(); //设置事务标志为成功，当结束事务时就会提交事务          }finally{              db.endTransaction();          }      }        public void save(Person person){          //如果要对数据进行更改，就调用此方法得到用于操作数据库的实例,该方法以读和写方式打开数据库          SQLiteDatabase db = getWritableDatabase();          db.execSQL( "insert into person (name,amount) values(?,?)" ,                  new Object[]{person.getName(),person.getAmount()});      }            public void update(Person person){          SQLiteDatabase db = getWritableDatabase();          db.execSQL( "update person set name=? where personid=?" ,                  new Object[]{person.getName(),person.getId()});      }            public void delete (Integer id){          SQLiteDatabase db = getWritableDatabase();          db.execSQL( "delete from person where personid=?" , new Object[]{id.toString()});      }            public Person find(Integer id){          //如果只对数据进行读取，建议使用此方法          SQLiteDatabase db = getReadableDatabase();          Cursor cursor = db.rawQuery( "select * from person where personid=?" , new String[]{id.toString()}); //得到游标          if (cursor.moveToFirst()){              int personid = cursor.getInt(cursor.getColumnIndex( "personid" ));              String name = cursor.getString(cursor.getColumnIndex( "name" ));              int amount = cursor.getInt(cursor.getColumnIndex( "amount" ));              Person person = new Person(personid, name);              person.setAmount(amount);              return person;          }          return null ;      }            public List<Person> getScrollData(Integer offset, Integer maxResult){          List<Person> persons = new ArrayList<Person>();          SQLiteDatabase db = getReadableDatabase();          Cursor cursor = db.rawQuery( "select * from person limit ?,?" ,                  new String[]{offset.toString(), maxResult.toString()});          while (cursor.moveToNext()){              int personid = cursor.getInt(cursor.getColumnIndex( "personid" ));              String name = cursor.getString(cursor.getColumnIndex( "name" ));              int amount = cursor.getInt(cursor.getColumnIndex( "amount" ));              Person person = new Person(personid, name);              person.setAmount(amount);              persons.add(person);          }          cursor.close();          return persons;      }            public Cursor getCursorScrollData(Integer offset, Integer maxResult){          SQLiteDatabase db = getReadableDatabase();          return db.rawQuery( "select personid as _id, name, amount from person limit ?,?" ,                  new String[]{offset.toString(), maxResult.toString()});      }            public long getCount() {          SQLiteDatabase db = getReadableDatabase();          Cursor cursor = db.rawQuery( "select count(*) from person" , null );          cursor.moveToFirst();          return cursor.getLong(0);      } }

涉及到的bean

[代码]java代码：

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

class Person {      private Integer id;      private String name;      private Integer amount;            public Integer getAmount() {          return amount;      }        public void setAmount(Integer amount) {          this .amount = amount;      }        public Person(){}            public Person(Integer id, String name) {          this .id = id;          this .name = name;      }      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;      }        @Override      public String toString() {          return "Person [amount=" + amount + ", id=" + id + ", name=" + name                  + "]" ;      }     }