This is an old revision of the document!
Database: SQLite
DB Connector
When using the built-in android.database.sqlite
library, there is not need to add an additional database connector.
Manifest
Add permission to access the Internet. Go to app
> manifests
> AndroidManifest.xml
:
<manifest...> <uses-permission android:name="android.permission.INTERNET"/> <application...> </application> </manifest>
Database Contract
Create a class to hold the database strings: File DbContract.java
:
package com.acme.myapp; import android.provider.BaseColumns; public final class DbContract { // To prevent someone from accidentally instantiating the contract class, // make the constructor private. private DbContract() {} /* Inner class that defines the table contents */ public class TeamEntry implements BaseColumns { public static final String TABLE_NAME = "entry"; public static final String COL_NAME = "name"; public static final String COL_NUMBER = "number"; public static final String COL_RANKING = "ranking"; } }
Database Helper
Create a class to hold the database strings: File DbHelper.java
:
package com.acme.myapp; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; public class DbHelper extends SQLiteOpenHelper { //----------------------------------- // If you change the database schema, you must increment the database version. //----------------------------------- // Properties public static final String DATABASE_NAME = "database.db"; public static final int DATABASE_VERSION = 1; private static final String SQL_CREATE_ENTRIES = "CREATE TABLE " + DbContract.TeamEntry.TABLE_NAME + " (" + DbContract.TeamEntry._ID + " INTEGER PRIMARY KEY," + DbContract.TeamEntry.COL_NAME + " TEXT," + DbContract.TeamEntry.COL_NUMBER + " TEXT," + DbContract.TeamEntry.COL_RANKING + " TEXT)"; private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + DbContract.TeamEntry.TABLE_NAME; //----------------------------------- // Methods //----------------------------------- public DbHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); Boolean createDatabase = false; if(createDatabase) { SQLiteDatabase db = this.getWritableDatabase(); } } public void onCreate(SQLiteDatabase db) { db.execSQL(SQL_CREATE_ENTRIES); // Add content to table for(int i=1; i <= 20; i++) { ContentValues values = new ContentValues(); values.put(DbContract.TeamEntry.COL_NAME, "Team " + i); values.put(DbContract.TeamEntry.COL_NUMBER, i + ""); // Insert the new row, returning the primary key value of the new row db.insert(DbContract.TeamEntry.TABLE_NAME, null, values); } } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // This database is only a cache for online data, so its upgrade policy is // to simply to discard the data and start over db.execSQL(SQL_DELETE_ENTRIES); onCreate(db); } public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) { onUpgrade(db, oldVersion, newVersion); } public String getDbVersionNumber() { String query = "select sqlite_version() AS sqlite_version"; SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(":memory:", null); Cursor cursor = db.rawQuery(query, null); String sqliteVersion = ""; if (cursor.moveToNext()) { sqliteVersion = cursor.getString(0); } return sqliteVersion; } }
Database Utils
Create a class to hold the database strings: File DbUtils.java
:
package com.acme.myapp; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.provider.BaseColumns; import java.util.ArrayList; import java.util.List; public class DbUtils { private DbHelper dbHelper; private SQLiteDatabase db; public DbHelper getConnection(Context cx) { dbHelper = new DbHelper(cx); return dbHelper; } public long addTeamRecord() { // Gets the data repository in write mode SQLiteDatabase db = dbHelper.getWritableDatabase(); // Create a new map of values, where column names are the keys ContentValues values = new ContentValues(); values.put(DbContract.TeamEntry.COL_NAME, "Team A"); values.put(DbContract.TeamEntry.COL_NUMBER, "1234"); // Insert the new row, returning the primary key value of the new row long newRowId = db.insert(DbContract.TeamEntry.TABLE_NAME, null, values); return newRowId; } public List readTeamRecords() { db = dbHelper.getReadableDatabase(); // Define a projection that specifies which columns from the database // you will actually use after this query. String[] projection = { BaseColumns._ID, DbContract.TeamEntry.COL_NAME, DbContract.TeamEntry.COL_NUMBER, DbContract.TeamEntry.COL_RANKING }; // Filter results WHERE "title" = 'My Team' String selection = DbContract.TeamEntry.COL_NAME + " = ?"; String[] selectionArgs = { "My Team" }; // How you want the results sorted in the resulting Cursor String sortOrder = DbContract.TeamEntry.COL_NUMBER + " DESC"; Cursor cursor = db.query( DbContract. TeamEntry.TABLE_NAME, // The table to query projection, // The array of columns to return (pass null to get all) selection, // The columns for the WHERE clause selectionArgs, // The values for the WHERE clause null, // don't group the rows null, // don't filter by row groups sortOrder // The sort order ); List itemIds = new ArrayList<>(); while(cursor.moveToNext()) { long itemId = cursor.getLong( cursor.getColumnIndexOrThrow(DbContract.TeamEntry._ID) ); itemIds.add(itemId); } cursor.close(); return itemIds; } public Map<String,String> findTeamRecordsAll() { db = dbHelper.getReadableDatabase(); // Define a projection that specifies which columns from the database // you will actually use after this query. String[] projection = { BaseColumns._ID, DbContract.TeamEntry.COL_NAME, DbContract.TeamEntry.COL_NUMBER, DbContract.TeamEntry.COL_RANKING }; // Filter results WHERE "title" = 'My Team' //String selection = DbContract.TeamEntry.COL_NAME + " = ?"; //String[] selectionArgs = { "My Team" }; // How you want the results sorted in the resulting Cursor //String sortOrder = DbContract.TeamEntry.COL_NUMBER + " ASC"; String sortOrder = DbContract.TeamEntry.COL_NAME + " ASC"; Cursor cursor = db.query( DbContract.TeamEntry.TABLE_NAME, // The table to query projection, // The array of columns to return (pass null to get all) //selection, // The columns for the WHERE clause null, //selectionArgs, // The values for the WHERE clause null, null, // don't group the rows null, // don't filter by row groups sortOrder // The sort order ); Map<String,String> mapTeam = new LinkedHashMap<String,String>(); while(cursor.moveToNext()) { String name = cursor.getString( cursor.getColumnIndexOrThrow(DbContract.TeamEntry.COL_NAME) ); String number = cursor.getString( cursor.getColumnIndexOrThrow(DbContract.TeamEntry.COL_NUMBER) ); mapTeam.put(name,number); } cursor.close(); return mapTeam; } public List<String> findTeamNamesAll() { try { db = dbHelper.getReadableDatabase(); } catch (Exception ex) { ex.printStackTrace(); } // Define a projection that specifies which columns from the database // you will actually use after this query. String[] projection = { BaseColumns._ID, DbContract.TeamEntry.COL_NAME, DbContract.TeamEntry.COL_NUMBER, DbContract.TeamEntry.COL_RANKING }; // Filter results WHERE "title" = 'My Team' //String selection = DbContract.TeamEntry.COL_NAME + " = ?"; //String[] selectionArgs = { "My Team" }; // How you want the results sorted in the resulting Cursor //String sortOrder = DbContract.TeamEntry.COL_NUMBER + " ASC"; String sortOrder = DbContract.TeamEntry.COL_NAME + " ASC"; Cursor cursor = db.query( DbContract.TeamEntry.TABLE_NAME, // The table to query projection, // The array of columns to return (pass null to get all) //selection, // The columns for the WHERE clause null, //selectionArgs, // The values for the WHERE clause null, null, // don't group the rows null, // don't filter by row groups sortOrder // The sort order ); List<String> arrNames = new ArrayList<String>(); while(cursor.moveToNext()) { String name = cursor.getString( cursor.getColumnIndexOrThrow(DbContract.TeamEntry.COL_NAME) ); arrNames.add(name); } cursor.close(); return arrNames; } public void deleteTeamRecord() { // Define selection filter String selection = DbContract.TeamEntry.COL_NAME + " LIKE ?"; String[] selectionArgs = { "MyTeam" }; // Issue SQL statement. int deletedRows = db.delete(DbContract.TeamEntry.TABLE_NAME, selection, selectionArgs); } public int updateTeamRecords() { SQLiteDatabase db = dbHelper.getWritableDatabase(); // New value for one column String name = "MyNewTeam"; ContentValues values = new ContentValues(); values.put(DbContract.TeamEntry.COL_NAME, name); // Which row to update, based on the title String selection = DbContract.TeamEntry.COL_NAME + " LIKE ?"; String[] selectionArgs = { "MyOldTeam" }; int count = db.update( DbContract.TeamEntry.TABLE_NAME, values, selection, selectionArgs ); return count; } public void destroy() { dbHelper.close(); } }
MainActivity
public class MainActivity extends AppCompatActivity { // Const private static final String TAG = "ROBOTICS_LEAGUE_APP"; // Properties ListView lstTeams; String[] arrTeams; List<String> teamNames; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); // Connect to resources Resources res = getResources(); lstTeams = (ListView) findViewById(R.id.lstTeams); //arrTeams = res.getStringArray(R.array.teams); // get data from resource stings try { DbUtils dbutil = new DbUtils(); dbutil.getConnection(this); teamNames = dbutil.findTeamNamesAll(); // get data from database } catch (Exception ex) { ex.printStackTrace(); } // Using custom ItemAdapter with custom layout TeamItemAdapter itemAdapter = new TeamItemAdapter( this, // context //arrTeams // data teamNames // data ); lstTeams.setAdapter(itemAdapter); lstTeams.setOnItemClickListener(new AdapterView.OnItemClickListener() { @Override public void onItemClick(AdapterView<?> parent, View view, int position, long id) { Log.d(TAG, "Loading rubric for Team " + (position+1)); Toast.makeText(getApplicationContext(), "Loading rubric for Team " + (position+1), Toast.LENGTH_SHORT).show(); Intent showRubricActivity = new Intent(getApplicationContext(), RobotRubricActivity.class); showRubricActivity.putExtra("com.voirtech.roboticsleague.TEAM_ID", (position+1)+""); showRubricActivity.putExtra("com.voirtech.roboticsleague.TEAM_INDEX", position); startActivity(showRubricActivity); } }); } }
activiy_main.xml Layout:
<?xml version="1.0" encoding="utf-8"?> <android.support.constraint.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:app="http://schemas.android.com/apk/res-auto" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" tools:context=".MainActivity"> <ListView android:id="@+id/lstTeams" android:layout_width="match_parent" android:layout_height="495dp" android:layout_marginEnd="8dp" android:layout_marginStart="8dp" android:layout_marginTop="8dp" app:layout_constraintEnd_toEndOf="parent" app:layout_constraintStart_toStartOf="parent" app:layout_constraintTop_toTopOf="parent" /> </android.support.constraint.ConstraintLayout>
ItemAdapter
package com.acme.myapp; import android.content.Context; import android.view.LayoutInflater; import android.view.View; import android.view.ViewGroup; import android.widget.BaseAdapter; import android.widget.TextView; import java.util.List; public class TeamItemAdapter extends BaseAdapter { LayoutInflater mInflater; //String[] arrTeams; List<String> arrTeams; //public TeamItemAdapter(Context cx, String[] teams) public TeamItemAdapter(Context cx, List<String> teams) { arrTeams = teams; mInflater = (LayoutInflater) cx.getSystemService(Context.LAYOUT_INFLATER_SERVICE); } @Override //public int getCount() //{ // return arrTeams.length; //} public int getCount() { return arrTeams.size(); } @Override //public Object getItem(int idx) //{ // return arrTeams[idx]; //} public Object getItem(int idx) { return arrTeams.get(idx); } @Override public long getItemId(int idx) { return idx; } @Override public View getView(int idx, View view, ViewGroup viewGroup) { // Use layout inflater. Use listview_detail_team.xml layout View vw = mInflater.inflate(R.layout.listview_detail_team, null); TextView lblItemNumber = (TextView) vw.findViewById(R.id.lblItemNumber); TextView lblTeamName = (TextView) vw.findViewById(R.id.lblTeamName); String strItemNumber = (idx+1) + ""; //String strTeam = arrTeams[idx]; String strTeam = arrTeams.get(idx); lblItemNumber.setText(strItemNumber); lblTeamName.setText(strTeam); return vw; } }