...
- 首先我先創建一個包(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 @Override 59 public String toString() { 60 return "GamePlayer{" + 61 "id=" + id + 62 ", player='" + player + '\'' + 63 ", score=" + score + 64 ", level=" + level + 65 '}'; 66 } 67 }
- 創建包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 }
- 包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_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 }
- 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 }