關於Android SQLite資料庫後臺處理的小例子

 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     @Override
59     public String toString() {
60         return "GamePlayer{" +
61                 "id=" + id +
62                 ", player='" + player + '\'' +
63                 ", score=" + score +
64                 ", level=" + level +
65                 '}';
66     }
67 }

 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 }

 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_table
15             "_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     @Override
25     public void onCreate(SQLiteDatabase sqLiteDatabase) {
26          sqLiteDatabase.execSQL(CREATE_TABLE_PLAYER);
27     }
28 //更新
29     @Override
30     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 }

  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 }