你的位置:首页 > 操作系统

[操作系统]关于Android SQLite数据库后台处理的小例子


  1. 首先我先创建一个包(package)entity
     1 package com.example.sqlgameplayer.entity; 2  3 /** 4  * Created by 徐伟杰 on 2016/9/25. 5  * 游戏玩家的实体类 6 */ 7  8 public class GamePlayer { 9   private int id;10   private String player;11   private int score;12   private int level;13   public GamePlayer(int id,String player,int score,int level){14     this.id=id;15     this.player=player;16     this.score=score;17     this.level=level;18   }19   public GamePlayer(String player,int score,int level){20     this.player=player;21     this.score=score;22     this.level=level;23   }24   public GamePlayer(){}25 26   public int getId() {27     return id;28   }29 30   public void setId(int id) {31     this.id = id;32   }33 34   public String getPlayer() {35     return player;36   }37 38   public void setPlayer(String player) {39     this.player = player;40   }41 42   public int getScore() {43     return score;44   }45 46   public void setScore(int score) {47     this.score = score;48   }49 50   public int getLevel() {51     return level;52   }53 54   public void setLevel(int level) {55     this.level = level;56   }57 58   @Override59   public String toString() {60     return "GamePlayer{" +61         "id=" + id +62         ", player='" + player + '\'' +63         ", score=" + score +64         ", level=" + level +65         '}';66   }67 }

     

  2. 创建包db,在db里创建类GameMetaData
     1 package com.example.sqlgameplayer.db; 2  3 import android.provider.BaseColumns; 4  5 /** 6  * Created by 徐伟杰 on 2016/9/25. 7  * 数据库元数据 8 */ 9 10 public final class GameMetaData {11   private GameMetaData(){};12   public static abstract class GamePlayer implements BaseColumns{13     public static final String TABLE_NAME="player_table";14     public static final String PLAYER="player";15     public static final String SCORE="score";16     public static final String LEVEL="level";17   }18 }

     

  3. 包db创建类DatabaseHelper
     1 package com.example.sqlgameplayer.db; 2  3 import android.content.Context; 4 import android.database.sqlite.SQLiteDatabase; 5 import android.database.sqlite.SQLiteOpenHelper; 6  7 /** 8  * Created by 徐伟杰 on 2016/9/25. 9 */10 11 public class DatabaseHelper extends SQLiteOpenHelper {12   private static final String DB_NAME="game.db";//数据库名字13   private static final int VERSION=1;//版本号14   private static final String CREATE_TABLE_PLAYER="CREATE TABLE IF NOT EXISTS player_table("+//如果没有表单创建表单player_table15       "_id INTEGER PRIMARY KEY AUTOINCREMENT,"+//id,主键自动增加16       "player TEXT,score INTEGER,level INTEGER)";//数据的类型17 18  private static final String DROP_TABLE_PLAYER="DROP TABLE IF EXISTS player_table";19   public DatabaseHelper(Context context) {20     //context,数据库名字,游标,版本号21     super(context, DB_NAME, null, VERSION);22   }23 24   @Override25   public void onCreate(SQLiteDatabase sqLiteDatabase) {26      sqLiteDatabase.execSQL(CREATE_TABLE_PLAYER);27   }28 //更新29   @Override30   public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {//i代表老版本,i1代表新版本31      sqLiteDatabase.execSQL(DROP_TABLE_PLAYER);32      sqLiteDatabase.execSQL(CREATE_TABLE_PLAYER);33     //先删除后创建34   }35 }

     

  4. db包创建类DatabaseAdapter
     1 package com.example.sqlgameplayer.db; 2  3 import android.content.ContentValues; 4 import android.content.Context; 5 import android.database.Cursor; 6 import android.database.sqlite.SQLiteDatabase; 7  8  9 import com.example.sqlgameplayer.entity.GamePlayer; 10  11 import java.util.ArrayList; 12  13 /** 14  * Created by 徐伟杰 on 2016/9/25. 15  * 数据库的工具类 16 */ 17  18 public class DatabaseAdapter { 19   private DatabaseHelper dbHelper; 20   public DatabaseAdapter(Context context){dbHelper=new DatabaseHelper(context);} 21   //添加操作 22   public void add(GamePlayer gamePlayer){ 23     //获取可写的数据库 24     SQLiteDatabase db=dbHelper.getWritableDatabase(); 25     ContentValues values=new ContentValues(); 26     values.put(GameMetaData.GamePlayer.PLAYER,gamePlayer.getPlayer()); 27     values.put(GameMetaData.GamePlayer.SCORE,gamePlayer.getScore()); 28     values.put(GameMetaData.GamePlayer.LEVEL,gamePlayer.getLevel()); 29     db.insert(GameMetaData.GamePlayer.TABLE_NAME,null,values); 30     db.close(); 31   } 32   //删除操作(实际开发应用中是不会删除的,而是隐藏) 33   public void delete(int id){ 34   SQLiteDatabase db=dbHelper.getWritableDatabase(); 35     String whereClause=GameMetaData.GamePlayer._ID+"=?"; 36     String[] whereArgs={String.valueOf(id)}; 37     //表名,条件,条件的值 38     db.delete(GameMetaData.GamePlayer.TABLE_NAME,whereClause,whereArgs); 39     db.close(); 40   } 41   //更新操作 42   public void update(GamePlayer gamePlayer){ 43   SQLiteDatabase db=dbHelper.getWritableDatabase(); 44     ContentValues values=new ContentValues(); 45     values.put(GameMetaData.GamePlayer.PLAYER,gamePlayer.getPlayer()); 46     values.put(GameMetaData.GamePlayer.SCORE,gamePlayer.getScore()); 47     values.put(GameMetaData.GamePlayer.LEVEL,gamePlayer.getLevel()); 48     String whereClause=GameMetaData.GamePlayer._ID+"=?"; 49     String[] whereArgs={String.valueOf(gamePlayer.getId())}; 50     db.update(GameMetaData.GamePlayer.TABLE_NAME,values,whereClause,whereArgs); 51     db.close(); 52   } 53   //查询 54   public GamePlayer findById(int id) { 55     //获取只读 56     SQLiteDatabase db=dbHelper.getReadableDatabase(); 57     //属性:是否可重复,表名,读多少个列表(这里数据比较少所以填null),条件,条件值,分组,排序等等 58     Cursor c=db.query(true, GameMetaData.GamePlayer.TABLE_NAME,null, GameMetaData.GamePlayer._ID+"=?",new String[]{String.valueOf(id)},null,null,null,null); 59     GamePlayer gamePlayer=null; 60     if(c.moveToNext()) { 61       gamePlayer=new GamePlayer(); 62       gamePlayer.setId(c.getInt(c.getColumnIndexOrThrow(GameMetaData.GamePlayer._ID))); 63       gamePlayer.setPlayer(c.getString(c.getColumnIndexOrThrow(GameMetaData.GamePlayer.PLAYER))); 64       gamePlayer.setScore(c.getInt(c.getColumnIndexOrThrow(GameMetaData.GamePlayer.SCORE))); 65       gamePlayer.setLevel(c.getInt(c.getColumnIndexOrThrow(GameMetaData.GamePlayer.LEVEL))); 66     } 67     c.close(); 68     db.close(); 69  70     return gamePlayer; 71   } 72   //查询所有 73   public ArrayList<GamePlayer> findAll(){ 74     SQLiteDatabase db=dbHelper.getReadableDatabase(); 75     //收集 _id,player,score,level(注意这里最好不要使用*号,回影响性能)从paly_table 排序通过score 降序 76     String sql="select _id,player,score,level from player_table order by score desc"; 77     Cursor c=db.rawQuery(sql,null); 78     ArrayList<GamePlayer> gamePlayers=new ArrayList<>(); 79     GamePlayer gamePlayer=null; 80     while (c.moveToNext()){ 81       gamePlayer=new GamePlayer(); 82       gamePlayer.setId(c.getInt(c.getColumnIndexOrThrow(GameMetaData.GamePlayer._ID))); 83       gamePlayer.setPlayer(c.getString(c.getColumnIndexOrThrow(GameMetaData.GamePlayer.PLAYER))); 84       gamePlayer.setScore(c.getInt(c.getColumnIndexOrThrow(GameMetaData.GamePlayer.SCORE))); 85       gamePlayer.setLevel(c.getInt(c.getColumnIndexOrThrow(GameMetaData.GamePlayer.LEVEL))); 86       gamePlayers.add(gamePlayer); 87     } 88     c.close(); 89     db.close(); 90     return gamePlayers; 91   } 92   //获取总记录数 93   public int getCount(){ 94     int count=0; 95     String sql="select count(_id) from player_table"; 96     SQLiteDatabase db=dbHelper.getReadableDatabase(); 97     Cursor c=db.rawQuery(sql,null); 98     c.moveToFirst(); 99     count=c.getInt(0);100     c.close();101     db.close();102    return count;103   }104 }