SQLite database for Android game development (17th)

Yusong MOMO takes you into the database of SQLite, the world of game development.
If the original Yusong MOMO article is reprinted, please indicate: Reprinted to my independent domain blog Yusong MOMO Program Research Institute, the original address: http://www.xuanyusong.com/archives/287

Database SQLite Introduction
            The four most classic operations of the database are adding, deleting, modifying, and searching. Using a database when processing a large amount of data can help us quickly locate the data that needs to be processed. For example, if you want to implement a search function, you only need one of them One search condition and one database statement can quickly find the data we need in N pieces of data. If you do not use a database, it will be very troublesome to find and the efficiency will be greatly reduced, so using a database when processing a large amount of data is a clear choice The database used in the development of Android is SQLite. It is a lightweight database, which is very small, portable, efficient and reliable. Because of the limitations of hardware conditions, embedded devices are very suitable for using SQLite databases.

Create and delete databases

        Wrap a class to inherit SQLiteOpenHelper. In the constructor, pass in the database name and database version number. When the database is created, the onCreate (SQLiteDatabase db) method is called. When the database version number changes, onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) method, which can conveniently handle the software game after upgrading to avoid errors caused by changes in the installation database. Call the SQLiteOpenHelper's getReadableDatabase () method to create the database. If the database does not exist, create and return the SQLiteDatabase object. If the database exists, do not create it and only return the SQLiteDatabase object. Call the deleteDatabase (DATABASE_NAME) method. Pass in the database name to delete the database.


       Encapsulating a DatabaseHelper class that inherits SQLiteOpenHelper. I used the singleton pattern in the design pattern to deal with this class. Here, let's talk about the singleton pattern. The singleton pattern is one of the common code design patterns. Its advantage is that it avoids frequent in memory. Instantiate so its object is written as static so that only one copy of its object exists in the static memory area. You can get this static object directly through getInstance ().

public class DatabaseHelper extends SQLiteOpenHelper {
    private static DatabaseHelper mInstance = null;

    /** Name database **/
    public static final String DATABASE_NAME = "xys.db";

    / ** Database version number ** /
    private static final int DATABASE_VERSION = 1;

    / ** Database SQL statement Add a table ** /
    private static final String NAME_TABLE_CREATE = "create table test ("
+ "_id INTEGER PRIMARY KEY AUTOINCREMENT," + "name TEXT," + "hp INTEGER DEFAULT 100," + "mp INTEGER DEFAULT 100,"
+ "number INTEGER);";

    DatabaseHelper (Context context) {
super (context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
     / ** Singleton Mode ** /
    static synchronized DatabaseHelper getInstance (Context context) {
if (mInstance == null) {
mInstance = new DatabaseHelper (context);
}
return mInstance;
    }

    @Override
    public void onCreate (SQLiteDatabase db) {
/ ** Add table to data ** /
db.execSQL (NAME_TABLE_CREATE);
    }

    @Override
    public void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion) {
/ ** You can get the version information of the current database and the version information of the previous database to update the database ** /
    }

    
    / **
     * Delete database
     * @param context
     * @return
     * /
    public boolean deleteDatabase (Context context) {
return context.deleteDatabase (DATABASE_NAME);
    }
}
Use the DatabaseHelper object in this class to create and delete databases,

public class NewSQLite extends Activity {
    DatabaseHelper mDbHelper = null;
    SQLiteDatabase mDb = null;
    Context mContext = null;
    @Override
    protected void onCreate (Bundle savedInstanceState) {
setContentView (R.layout.create_sql);
mContext = this;
// Create DatabaseHelper object
mDbHelper = DatabaseHelper.getInstance (mContext);
// Call the getReadableDatabase method if the database does not exist, then create it if it exists
mDb = mDbHelper.getReadableDatabase ();

Button button0 = (Button) findViewById (R.id.createDateBase);
button0.setOnClickListener (new OnClickListener () {
Ranch
@Override
public void onClick (View arg0) {

Toast.makeText (NewSQLite.this, "Database created successfully", Toast.LENGTH_LONG) .show ();
}
});
Button button1 = (Button) findViewById (R.id.deleteDateBase);
button1.setOnClickListener (new OnClickListener () {
Ranch
@Override
public void onClick (View arg0) {
mDbHelper = DatabaseHelper.getInstance (mContext);
// call the getReadableDatabase method if the database does not exist then create it if it exists
mDb = mDbHelper.getReadableDatabase ();
// close the database
mDbHelper.close ();
// delete the database
mDbHelper.deleteDatabase (mContext);
Toast.makeText (NewSQLite.this, "Successfully deleted the database", Toast.LENGTH_LONG) .show ();
}
});
Ranch
Ranch
Ranch
super.onCreate (savedInstanceState);
    }

}


The created database will be saved in the current project under the database path.

Add and delete tables in the database

        A database can consist of multiple data tables. If you add a database table, you can use the database statement create table name (content) to add it. Here is a statement to create a database. It means to create a table with the name of the gameInfo table. The fields contained in it are _id for the INTEGER type and increment name for the text type. Hp mp for the INTEGER. The default value is 100 number for the INTEGER type.

    / ** SQL statement to create a table ** /
    private static final String NAME_TABLE_CREATE = "create table gameInfo ("
+ "_id INTEGER PRIMARY KEY AUTOINCREMENT," + "name TEXT," + "hp INTEGER DEFAULT 100," + "mp INTEGER DEFAULT 100,"
+ "number INTEGER);";
Delete a table in the database by directly using the DROP TABLE table name

    / ** Delete SQL statement of a table ** /
    private static final String NAME_TABLE_DELETE = "DROP TABLE gameInfo";
Execute an SQL statement in the code Use SQLiteDatabase object to call execSQL () and pass in the SQL statement.

 mDb.execSQL (NAME_TABLE_CREATE);
Take the creation of a table named gameInfo as an example, and give the code implementation

public class NewTable extends Activity {
    DatabaseHelper mDbHelper = null;
    SQLiteDatabase mDb = null;
    Context mContext = null;
  
    / ** SQL statement to create a table ** /
    private static final String NAME_TABLE_CREATE = "create table gameInfo ("
+ "_id INTEGER PRIMARY KEY AUTOINCREMENT," + "name TEXT," + "hp INTEGER DEFAULT 100," + "mp INTEGER DEFAULT 100,"
+ "number INTEGER);";
    
    
    / ** Delete SQL statement of a table ** /
    private static final String NAME_TABLE_DELETE = "DROP TABLE gameInfo";
    
    
    @Override
    protected void onCreate (Bundle savedInstanceState) {
setContentView (R.layout.create_table);
mContext = this;
mDbHelper = DatabaseHelper.getInstance (mContext);
mDb = mDbHelper.getReadableDatabase ();
Ranch
Button button0 = (Button) findViewById (R.id.createTable);
button0.setOnClickListener (new OnClickListener () {
Ranch
@Override
public void onClick (View arg0) {
try {
mDb.execSQL (NAME_TABLE_CREATE);
Toast.makeText (NewTable.this, "Data table successfully added", Toast.LENGTH_LONG) .show ();
} catch (SQLiteException e) {
Toast.makeText (NewTable.this, "This table already exists in the database", Toast.LENGTH_LONG) .show ();
}
}
});
Button button1 = (Button) findViewById (R.id.deleteTable);
button1.setOnClickListener (new OnClickListener () {
Ranch
@Override
public void onClick (View arg0) {
try {
mDb.execSQL (NAME_TABLE_DELETE);
Toast.makeText (NewTable.this, "Successfully deleted the data table", Toast.LENGTH_LONG) .show ();
} catch (SQLiteException e) {
Toast.makeText (NewTable.this, "This table no longer exists in the database", Toast.LENGTH_LONG) .show ();
}

}
});
Ranch
Ranch
Ranch
super.onCreate (savedInstanceState);
    }

}

Add delete modify query data in database

Use the SQLiteDatabase object to call the insert () method, passing in the name of the target and the content added by ContentValues. You can write a piece of data to the database table. Delete () deletes a piece of data update () updates a piece of data.

Let me elaborate on the method used to find a piece of data in query followed by 8 parameters

public Cursor query (String table, String [] columns, String selection, String [] selectionArgs, String groupBy, String having, String orderBy, String limit);

Parameter Description:

table: the name of the database table

columns: array of database column names. Only the contents of the columns can be found in the Cursor that is returned after writing.

selection: query condition

selectionArgs: query results

groupBy: grouping columns

having: grouping conditions

orderBy: sort column

limit: Pagination query limit

Cursor: return value, all results will be stored in Cursor

Cursor is a cursor interface. The results of each query are stored in Cursor. You can get all the information currently queried by traversing the Cursor method.

Cursor's method

moveToFirst () // Move Curor's cursor to the first one
moveToLast () // Move Curor's cursor to the last one
move (int offset) // Move Curor's cursor to the specified ID
moveToNext () // Move Curor's cursor to the next one
moveToPrevious () // Move Curor's cursor to the previous one
getCount () // Get the total number of Cursor records
isFirst () // Determine whether the current cursor is the first record
isLast () // Judgment whether the current cursor is the last data
getInt (int columnIndex) // Get the column index ID based on the column name
getString (int columnIndex) // Get the field stored in the table according to the index ID

Here is an example to traverse Cursor
    private void query (SQLiteDatabase db) {
// Query all the data of the entire table into the cursor
Cursor cursor = db.query (TABLE_NAME, null, null, null, null, null, null);
// It is important to judge that the cursor is not empty
if (cursor! = null) {
// loop through the cursor
while (cursor.moveToNext ()) {
// get the value of name and hp for each line
String name = cursor.getString (cursor.getColumnIndex ("name"));
String hp = cursor.getString (cursor.getColumnIndex ("hp"));
Log.v ("info", "name is" + name + "hp is" + hp);
}
    // shut down
cursor.close ();
}
    }
I recommend a software to view the database is very easy to use. The name is SQLiteSpy.exe. Open the xys.db file to clearly see the contents stored in the database table. The software supports the execution of SQL statements and can be directly operated in the software. I give this Software.
: http: //download.csdn.net/source/3481140

public class Newdate extends Activity {
    DatabaseHelper mDbHelper = null;
    SQLiteDatabase mDb = null;
    Context mContext = null;

    / ** Database field ** /

    public final static String TABLE_NAME = "test";
    public final static String ID = "_id";
    public final static String NAME = "name";
    public final static String HP = "hp";
    public final static String MP = "mp";

    @Override
    protected void onCreate (Bundle savedInstanceState) {
setContentView (R.layout.create_date);
mContext = this;
// Create DatabaseHelper object
mDbHelper = DatabaseHelper.getInstance (mContext);
// call the getReadableDatabase method if the database does not exist then create it if it exists
mDb = mDbHelper.getReadableDatabase ();
// Initialize write some information to the database table
for (int i = 0; i <10; i ++) {
insert (NAME, "Yamatsu MOMO" + i);
}

// increase
Button button0 = (Button) findViewById (R.id.add);
button0.setOnClickListener (new OnClickListener () {

@Override
public void onClick (View arg0) {
insert (NAME, "Newly added cute");
Toast.makeText (Newdate.this, "Add a data name for cute", Toast.LENGTH_LONG)
.show ();
}
});
// delete
Button button1 = (Button) findViewById (R.id.delete);
button1.setOnClickListener (new OnClickListener () {

@Override
public void onClick (View arg0) {
delete (ID, "1");
Toast.makeText (Newdate.this, "Delete a piece of data with _id = 1", Toast.LENGTH_LONG)
.show ();
}
});
// modify
Button button2 = (Button) findViewById (R.id.modify);
button2.setOnClickListener (new OnClickListener () {

@Override
public void onClick (View arg0) {
update (NAME, "Yamatsu MOMO3", "Little cute 3");
Toast.makeText (Newdate.this, "Update the name Yusong MOMO3 to Little Cute 3",
Toast.LENGTH_LONG) .show ();
}
});
// find
Button button3 = (Button) findViewById (R.id.find);
button3.setOnClickListener (new OnClickListener () {

@Override
public void onClick (View arg0) {
Cursor cursor = find (ID, "5");
String name = cursor.getString (cursor.getColumnIndex (NAME));
Toast.makeText (Newdate.this, "Find the name of the data whose ID is 5" + name,
Toast.LENGTH_LONG) .show ();
}
});
super.onCreate (savedInstanceState);
    }

    / **
     * Insert a piece of data
     *
     * @param key
     * @param date
     * /
    public void insert (String key, String date) {
ContentValues values = new ContentValues ();
values.put (key, date);
mDb.insert (TABLE_NAME, null, values);
    }

    / **
     * Delete data
     *
     * @param key
     * @param date
     * /
    public void delete (String key, String date) {
mDb.delete (TABLE_NAME, key + "=?", new String [] {date});
    }

    / **
     * Update a piece of data
     *
     * @param key
     * @param oldDate
     * @param newDate
     * /
    public void update (String key, String oldDate, String newDate) {
ContentValues values = new ContentValues ();
values.put (key, newDate);
mDb.update (TABLE_NAME, values, key + "=?", new String [] {oldDate});
    }

    / **
     * Find a piece of data
     *
     * @param key
     * @param date
     * @return
     * /
    public Cursor find (String key, String date) {

Cursor cursor = mDb.query (TABLE_NAME, null, key + "=?",
new String [] {date}, null, null, null);
if (cursor! = null) {
cursor.moveToFirst ();
}
return cursor;
    }

}

Here I am highlighting the second parameter String [] columns in the query. For example, the current query value in the data. If I only want the name field and hp field of each row in the data that meet the conditions, then the second parameter is Don't write it as null.

The second parameter was written as new String [] {"name", "hp"}. In this case, the data in the Cursor will only have the two fields "name" and "hp" in the database table. Because other fields, we simply No need to write anything can greatly improve the efficiency of the code. If it is written as null, the data in the Cursor will save all the fields in the database table so that the code will consume more useless time when calculating the Cursor.

The meaning of cursor.getString (0); is to get the corresponding new String [] {"name", "hp"} array. The meaning is to get the value of the database field "name", cursor.getString (1 ); Means to get the value of the database field "hp". The ID in cursor.getString () is exactly corresponding to the second parameter String [] columns array subscript.

    public void find () {
Cursor cursor = mDb.query (TABLE_NAME, new String [] {"name", "hp"}, null,
null, null, null, null);
while (cursor.moveToNext ()) {
String name = cursor.getString (0);
String hp = cursor.getString (1);
Log.v ("info", "name is" + name);
Log.v ("info", "hp is" + hp);
}
    }
In the end, if you still feel that I have n’t written enough in detail, I do n’t watch it well enough. It does n’t matter if I post the source code. Welcome everyone to discuss and learn from Yusong MOMO.

: Http://www.xuanyusong.com/archives/287