I have an ArrayList coming from SQL Server stored procedure and I want to insert all the data to a SQLite database. Please look at my code below

Question

I was able to get data from SQL Server, but I don't know what to do after, Please help me or any idea how can I insert the data from my ArrayList to SQLite Database and insert it into a Custom ListView.

.................................................................................................

ConnectionHelper.java

package com.example.storedprocedures;

import android.annotation.SuppressLint;
import android.os.StrictMode;
import android.util.Log;

import java.sql.Connection;
import java.sql.DriverManager;

public class ConnectionHelper {
    Connection con;
    String ip = "***.***.*.***";
    String port = "*****";
    String classes = "net.sourceforge.jtds.jdbc.Driver";
    String database = "CentralizedDB";
    String username = "******";
    String password = "********";
    String url = "jdbc:jtds:sqlserver://"+ip+":"+port+"/"+database;

    @SuppressLint("NewApi")
    public Connection conclass(){
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
        StrictMode.setThreadPolicy(policy);
        Connection connection = null;
        try{
            Class.forName(classes);
            connection = DriverManager.getConnection(url, username, password);
        }catch(Exception exception){
            Log.e("Error:", exception.getMessage());
        }
        return connection;
    }
}

MainActivity.java

package com.example.storedprocedures;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import android.os.Bundle;

import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

import androidx.appcompat.app.AppCompatActivity;

public class MainActivity extends AppCompatActivity {
    TextView lblheader;
    Button btnviewall,btnview;
    ListView lstTaggedList;
    EditText edtid;

    Connection connect;
    ResultSet rs;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        lblheader = (TextView) findViewById(R.id.lblheader);
        lstTaggedList = (ListView) findViewById(R.id.lstTaggedList);
        btnviewall = (Button) findViewById(R.id.btnviewall);
        btnview = (Button) findViewById(R.id.btnview);
        edtid = (EditText) findViewById(R.id.edtid);


        ConnectionHelper connectionHelper = new ConnectionHelper();
        connect = connectionHelper.conclass();
        btnviewall.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                try {

                    PreparedStatement statement = connect.prepareStatement("EXEC SP_TaggedList");
                    final ArrayList list = new ArrayList();
                    rs = statement.executeQuery();
                    while (rs.next()) {
                        list.add(rs.getString("mrCode"));
                        list.add(rs.getString("docId_branch"));
                        list.add(rs.getString("brchName"));
                        list.add(rs.getString("industry_type"));
                        list.add(rs.getString("specialization_id"));
                        list.add(rs.getString("uName"));
                        list.add(rs.getString("class_id"));
                        list.add(rs.getString("max_visit"));
                    }
                    ArrayAdapter adapter = new ArrayAdapter(MainActivity.this,
                            android.R.layout.simple_list_item_1, list);
                    
                } catch (SQLException e) {
                    Toast.makeText(MainActivity.this, e.getMessage().toString(),
                            Toast.LENGTH_LONG).show();
                }
            }
        });

        btnview.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                try {

                    PreparedStatement statement = connect.prepareStatement("EXEC SP_TaggedList '"+edtid.getText().toString()+"'");
                    final ArrayList list = new ArrayList();
                    rs = statement.executeQuery();
                    while (rs.next()) {
                        list.add(rs.getString("mrCode"));
                        list.add(rs.getString("docId_branch"));
                        list.add(rs.getString("brchName"));
                        list.add(rs.getString("industry_type"));
                        list.add(rs.getString("specialization_id"));
                        list.add(rs.getString("uName"));
                        list.add(rs.getString("class_id"));
                        list.add(rs.getString("max_visit"));
                    }
                    ArrayAdapter adapter = new ArrayAdapter(MainActivity.this,
                            android.R.layout.simple_list_item_1, list);



                } catch (SQLException e) {
                    Toast.makeText(MainActivity.this, e.getMessage().toString(),
                            Toast.LENGTH_LONG).show();
                }
            }
        });
        lstTaggedList.setOnItemClickListener(new OnItemClickListener() {
            @Override
            public void onItemClick(AdapterView<?> parent, View view,
                                    int position, long id) {
                // TODO Auto-generated method stub

                String item = lstTaggedList.getItemAtPosition(position).toString();
                Toast.makeText(MainActivity.this, item + " selected", Toast.LENGTH_LONG).show();
            }
        });
    }
}

Answer 1

With SQLite on android, the typically way to use an SQLite database is to have a class that extends SQLiteOpenHelper.

SQLiteOpenHelper will if the database does not exist call the onCreate method passing an empty SQLiteDatabase to the method (not totally empty as sqlite_master and android_metadata tables will exist, but empty as far as user tables will be concerned). You can then create the tables using the execSQL method for each table (or other components such as indexes, view triggers).

You can also include other methods that cater for CRUD operations to access the data.

Here's a Simple Demo of a database that drives a ListView.

First DatabaseHelper i.e. the class that extends SQLiteOpenHelper :-

class DatabaseHelper extends SQLiteOpenHelper {

   public static final String DATABASE_NAME = "the_database.db";
   public static final int DATABASE_VERSION = 1;

   private static volatile DatabaseHelper INSTANCE; /* singleton instance of DatabaseHelper */
   private SQLiteDatabase db;

   /* Constructor (not publicly available) */
   private DatabaseHelper(Context context) {
      super(context,DATABASE_NAME,null,DATABASE_VERSION);
      db = this.getWritableDatabase();
   }

   /* Get singleton instance of DatabaseHelper */
   public static DatabaseHelper getInstance(Context context) {
      if (INSTANCE==null) {
         INSTANCE = new DatabaseHelper(context);
      }
      return INSTANCE;
   }

   public static final String TABLE_NAME = "the_table";
   public static final String COLUMN_MRCODE = BaseColumns._ID; /* Cursor Adapter must have _id column */
   public static final String COLUMN_DOCID_BRANCH = "docid_branch";
   public static final String COLUMN_BRCHNAME = "brchname";
   public static final String COLUMN_INDUSTRY_TYPE = "industry_type";
   public static final String COLUMN_SPECIALIZATION_ID = "specilization_id";
   public static final String COLUMN_UNAME = "uname";
   public static final String COLUMN_CLASS_ID = "class_id";
   public static final String COLUMN_MAX_VISIT = "max_visit";
   private static final String CRTSQL_THE_TABLE =
           "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(" +
                   COLUMN_MRCODE + " INTEGER PRIMARY KEY" +
                   "," + COLUMN_DOCID_BRANCH + " INTEGER " +
                   "," + COLUMN_BRCHNAME + " TEXT " +
                   "," + COLUMN_INDUSTRY_TYPE + " TEXT " +
                   "," + COLUMN_SPECIALIZATION_ID + " INTEGER " +
                   "," + COLUMN_UNAME + " TEXT " +
                   "," + COLUMN_CLASS_ID + " INTEGER " +
                   "," + COLUMN_MAX_VISIT + " INTEGER " +
                   ")";
   private static final String CRTSQL_DOCID_BRANCH_INDEX =
           "CREATE INDEX IF NOT EXISTS " + COLUMN_DOCID_BRANCH + "_" + TABLE_NAME + "_idx001 " +
                   " ON " + TABLE_NAME + "(" +
                   COLUMN_DOCID_BRANCH +
                   ")";

   /* MUST be overridden (not much use if not used) */
   @Override
   public void onCreate(SQLiteDatabase db) {
      db.execSQL(CRTSQL_THE_TABLE);
      db.execSQL(CRTSQL_DOCID_BRANCH_INDEX);
   }

   /* MUST be overridden but doesn't necessarily have to do anything*/
   @Override
   public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

   }

   /* CRUD */

   public long insert(Long mrcode, long docid_branch, String brchname, String industry_type, String uname, long class_id, long max_visit ) {
      ContentValues cv = new ContentValues();
      if (mrcode != null) {
         cv.put(COLUMN_MRCODE,mrcode);
      }
      cv.put(COLUMN_DOCID_BRANCH,docid_branch);
      if (brchname != null) {
         cv.put(COLUMN_BRCHNAME,brchname);
      }
      if (industry_type != null) {
         cv.put(COLUMN_INDUSTRY_TYPE,industry_type);
      }
      if (uname != null) {
         cv.put(COLUMN_UNAME,uname);
      }
      cv.put(COLUMN_CLASS_ID,class_id);
      cv.put(COLUMN_MAX_VISIT,max_visit);
      return db.insert(TABLE_NAME,null,cv);
   }

   public Cursor getAllTheTableRows() {
      return db.query(TABLE_NAME,null,null,null,null,null,null);
   }
   @SuppressLint("Range")
   public long getNumberOfRowsInTheTable() {
      long rv=0;
      String output_column = "row_count";
      Cursor csr = db.query(TABLE_NAME,new String[]{"count(*) AS " + output_column},null,null,null,null,null);
      if (csr.moveToFirst()) {
         rv = csr.getLong(csr.getColumnIndex(output_column));
      }
      csr.close(); /* should always close cursors when done with them */
      return rv;
   }
}

• as can be seen the database will

  • have 1 table named the_table
  • have an index on the docid_branch column
  • all the component names are defined based upon a single hard coded name.

• have columns along the lines of what can be seen from your code.

• a singleton approach has been used

• some basic CRUD methods have been included to allow data to be inserted and extracted.

To demonstrate use of the DatabaseHelper and a means of driving a ListView (via a CursorAdapter) then MainActivity :-

public class MainActivity extends AppCompatActivity {

    DatabaseHelper dbHelper;
    ListView lv;
    SimpleCursorAdapter sca;
    Cursor csr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        dbHelper = DatabaseHelper.getInstance(this);
        lv = this.findViewById(R.id.lv);
        if (dbHelper.getNumberOfRowsInTheTable() < 1) {
            addSomeTestData();
        }
        setOrRefreshListView();
    }

    /*
        Whenever the ListView is to be changed (including initially)
        this can be called.
     */
    private void setOrRefreshListView() {
        csr = dbHelper.getAllTheTableRows(); /* gets the latest data from the database */
        if (sca == null) {
            sca = new SimpleCursorAdapter(
                    this,
                    android.R.layout.simple_list_item_2,
                    csr,
                    new String[]{DatabaseHelper.COLUMN_UNAME,DatabaseHelper.COLUMN_INDUSTRY_TYPE}, /* The column names in the Cursor */
                    new int[]{android.R.id.text1,android.R.id.text2},0); /* The respective id's in the ListView's layout */
            lv.setAdapter(sca);
        } else {
            sca.swapCursor(csr);
        }
    }

    private void addSomeTestData() {
        dbHelper.insert(001L,111,"Branch001","MINING","FRED",1,10);
        dbHelper.insert(null,222,"Branch002","HOSPITALITY","MARY",6,1);
        dbHelper.insert(null,333,"Branch003","REFINING","JANE",5,5);
    }
}

• Note that for brevity a predefined/stock layout has been used

Results

When run then :-

Using App Inspection then :-

• as can bee seen the specialization_id is NULL this is because the insert method in the DatabaseHelper class omitted consideration of the column.

Again using App Inspection but with a custom query then :-

• android_metadata table can be seen to exist (contains the locale and is android specific)
• the docid_branch_the_table_idx001 index has been created.

how can I insert the data from my ArrayList to SQLite Database

So with the above you just need to get an Instance of the the DatabaseHelper and then invoke the insert method whilst looping through your ArrayList.

• This isn't the best solution as each insert is done in it's own transaction.