Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision 		Previous revision
swdev:android:database_sqlite [2018/04/29 13:37]
smayr created 		swdev:android:database_sqlite [2018/05/02 20:30] (current)
smayr [Database Utils]
Line 1: Line 1:
 = Database: SQLite = = Database: SQLite =
 ==== DB Connector ==== ==== DB Connector ====
-Download the appropriate Java database connector: +When using the built-in ''android.database.sqlite'' library, there is not need to add an additional database connector. 
-  * [[https://dev.mysql.com/downloads/connector/j/3.0.html|Download JDBC / ConnectorJ library]] (select 'Platform Independent' version). +
-  * Extract JAR file to some folder. +
-  * Add JAR file as module: ''app'', right-click then ''New'' > ''Module'', then select 'Import .JAR/.AAR Package', and find JAR file. +
-  * JAR package should be now listed under the project Gradle Scripts: ''build.gradle (sqlite-jdbc-3.20.0)''. +
-  * If there is an error including the JAR package, try the following: +
-    * In ''settings.gradle (Project Settings)'', add references to the sqlite-jdbc: <code bash> +
-include ':app' +
-include ':mysql-connector-java-8.0.11' +
-</code> +
-  * Sync project (File > Sync Project with Gradle Files). +
-  * Add ''sqlite-jdbc'' module as dependency to ''app'' module: +
-    * Right-click on the ''app'' module folder, and select "Open Module Settings" (F4). +
-    * In the top left navigation pane of the "Project Structure" window, go to category "Modules" and select tab "Dependencies". +
-    * Click on the green plus (+), and select 'Module Dependencies'. Select the ''sqlite-jdbc'' module. +
-    * Click OK, and close all the opened windows. A synchronization process should take place once more. +
-  * If there is an error including the JAR package, try the following: +
-    * In ''build.gradle (Module:app)'', add references to compile ''sqlite-jdbc'' under dependencies: <code bash> +
-dependencies { +
-  //... +
-  compile project(':sqlite-jdbc-3.20.0') +
-} +
-</code> +
-  * If succesfull, under the ''app'' module folder there should be a ''sqlite-jdbc'' module folder. +
-  * Include the classes required in your code.+
  
  
Line 76: Line 52:
 public class DbHelper extends SQLiteOpenHelper public class DbHelper extends SQLiteOpenHelper
 { {
-    //----------------------------------- 
-    // Properties 
     //-----------------------------------     //-----------------------------------
     // If you change the database schema, you must increment the database version.     // If you change the database schema, you must increment the database version.
-    public static final int DATABASE_VERSION = 1;+    //----------------------------------- 
 +    // Properties
     public static final String DATABASE_NAME = "database.db";     public static final String DATABASE_NAME = "database.db";
 +    public static final int DATABASE_VERSION = 1;
  
     private static final String SQL_CREATE_ENTRIES =     private static final String SQL_CREATE_ENTRIES =
-            "CREATE TABLE " + DbContract.TeamEntry.TABLE_NAME + " (" + +        "CREATE TABLE " + DbContract.TeamEntry.TABLE_NAME + " (" + 
-                    DbContract.TeamEntry._ID + " INTEGER PRIMARY KEY," + +                DbContract.TeamEntry._ID + " INTEGER PRIMARY KEY," + 
-                    DbContract.TeamEntry.COL_NAME + " TEXT," + +                DbContract.TeamEntry.COL_NAME + " TEXT," + 
-                    DbContract.TeamEntry.COL_NUMBER + " TEXT)";+                DbContract.TeamEntry.COL_NUMBER + " TEXT," + 
 +                DbContract.TeamEntry.COL_RANKING + " TEXT)";
  
     private static final String SQL_DELETE_ENTRIES =     private static final String SQL_DELETE_ENTRIES =
-            "DROP TABLE IF EXISTS " + DbContract.TeamEntry.TABLE_NAME;+        "DROP TABLE IF EXISTS " + DbContract.TeamEntry.TABLE_NAME;
  
  
Line 96: Line 73:
     // Methods     // Methods
     //-----------------------------------     //-----------------------------------
-    public DbHelper(Context context) {+    public DbHelper(Context context) 
 +    {
         super(context, DATABASE_NAME, null, DATABASE_VERSION);         super(context, DATABASE_NAME, null, DATABASE_VERSION);
 +        Boolean createDatabase = false;
 +        if(createDatabase) {
 +            SQLiteDatabase db = this.getWritableDatabase();
 +        }
     }     }
  
-    public void onCreate(SQLiteDatabase db) {+    public void onCreate(SQLiteDatabase db) 
 +    {
         db.execSQL(SQL_CREATE_ENTRIES);         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) {+    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
 +    {
         // This database is only a cache for online data, so its upgrade policy is         // This database is only a cache for online data, so its upgrade policy is
         // to simply to discard the data and start over         // to simply to discard the data and start over
Line 111: Line 105:
     }     }
  
-    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {+    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) 
 +    {
         onUpgrade(db, oldVersion, newVersion);         onUpgrade(db, oldVersion, newVersion);
     }     }
Line 147: Line 142:
 { {
     private DbHelper dbHelper;     private DbHelper dbHelper;
-    SQLiteDatabase db;+    private SQLiteDatabase db;
  
     public DbHelper getConnection(Context cx)     public DbHelper getConnection(Context cx)
Line 155: Line 150:
     }     }
  
-    public long addTeamEntry()+    public long addTeamRecord()
     {     {
         // Gets the data repository in write mode         // Gets the data repository in write mode
Line 171: Line 166:
     }     }
  
-    public List readTeamEntry()+    public List readTeamRecords()
     {     {
         db = dbHelper.getReadableDatabase();         db = dbHelper.getReadableDatabase();
Line 180: Line 175:
                 BaseColumns._ID,                 BaseColumns._ID,
                 DbContract.TeamEntry.COL_NAME,                 DbContract.TeamEntry.COL_NAME,
-                DbContract.TeamEntry.COL_NUMBER+                DbContract.TeamEntry.COL_NUMBER, 
 +                DbContract.TeamEntry.COL_RANKING
         };         };
  
Line 188: Line 184:
  
         // How you want the results sorted in the resulting Cursor         // How you want the results sorted in the resulting Cursor
-        String sortOrder = +        String sortOrder =  DbContract.TeamEntry.COL_NUMBER + " DESC";
-                DbContract.TeamEntry.COL_NUMBER + " DESC";+
  
         Cursor cursor = db.query(         Cursor cursor = db.query(
Line 203: Line 198:
         List itemIds = new ArrayList<>();         List itemIds = new ArrayList<>();
         while(cursor.moveToNext()) {         while(cursor.moveToNext()) {
-            long itemId = cursor.getLong( +            long itemId = cursor.getLong( cursor.getColumnIndexOrThrow(DbContract.TeamEntry._ID) );
-                    cursor.getColumnIndexOrThrow(DbContract.TeamEntry._ID));+
             itemIds.add(itemId);             itemIds.add(itemId);
         }         }
Line 212: Line 206:
     }     }
  
-    public void deleteInfo()+    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" }; 
 +        String selection = null;         // no filters 
 +        String[] selectionArgs = null;   // no filters 
 + 
 +        // 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 
 +                selectionArgs,          // The values for the WHERE clause 
 +                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" }; 
 +        String selection = null;         // no filters 
 +        String[] selectionArgs = null;   // no filters 
 + 
 +        // 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 
 +                //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<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         // Define selection filter
Line 222: Line 307:
     }     }
  
-    public int updateDatabase()+    public int updateTeamRecords()
     {     {
         SQLiteDatabase db = dbHelper.getWritableDatabase();         SQLiteDatabase db = dbHelper.getWritableDatabase();
Line 239: Line 324:
                 values,                 values,
                 selection,                 selection,
-                selectionArgs);+                selectionArgs 
 +        );
  
         return count;         return count;
Line 250: Line 336:
  
  
 +}
 +</code>
 +
 +=== MainActivity ===
 +<code java>
 +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.acme.roboticsleague.TEAM_ID", (position+1)+"");
 +                showRubricActivity.putExtra("com.acme.roboticsleague.TEAM_INDEX", position);
 +                startActivity(showRubricActivity);
 +            }
 +        });
 +    }
 +}
 +</code>
 +
 +activiy_main.xml Layout:
 +<code xml>
 +<?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>
 +</code>
 +
 +=== ItemAdapter ===
 +<code java>
 +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;
 +    }
 } }
 </code> </code>
Line 258: Line 495:
   * [[https://developer.android.com/training/data-storage/sqlite|Data Storage: SQLite]]   * [[https://developer.android.com/training/data-storage/sqlite|Data Storage: SQLite]]
   * [[https://developer.android.com/training/data-storage/room|Data Storage: Room]]   * [[https://developer.android.com/training/data-storage/room|Data Storage: Room]]
 +  * [[https://dzone.com/articles/create-a-database-android-application-in-android-s|Create a database Android application]]
swdev/android/database_sqlite.1525023477.txt.gz · Last modified: 2018/04/29 13:37 by smayr
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International


  • Show page
  • Old revisions
  • Backlinks
  • Recent Changes
  • Media Manager
  • Sitemap