I'm making an application that stores personal information in the SQLite database and show the data in the format of TableLayout in the activity. In order to implement the idea, I have created several files so far, and they are as follows.
(1) AddAPersonActivity.java: This class takes the value input, stores it into the SQLite database and adds a row with the data into a table layout that will be shown in the ViewGroupActivity.
(2) ViewGroupActivity.java: This class shows a table that stores the personal information.
(3) SQLController.java: This class defines methods for opening and closing the SQLite database, inserting data and reading the entry.
(4) SQLiteHandler.java: This class extends the SQLiteOpenHelper class and deals with creating database and table.
The codes are as follows.
(1) AddAPersonActivity.java
public class AddAPersonActivity extends Activity implements View.OnClickListener, AdapterView.OnItemSelectedListener, RadioGroup.OnCheckedChangeListener {
private Button btnAdd;
private EditText etName;
private TextView tvDob;
private Spinner spNationality;
private RadioButton rbMale, rbFemale;
private Calendar birthday;
private NameValidator nameValidator;
private RadioGroup rgGenderGroup;
private TableLayout tlViewGroup;
private String gender = "";
private SQLController sqlController;
private ProgressDialog progressDialog;
private Person person;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_add_a_person);
btnAdd = (Button) findViewById(R.id.btnAdd);
etName = (EditText) findViewById(R.id.etName);
tvDob = (TextView) findViewById(R.id.tvDob);
spNationality = (Spinner) findViewById(R.id.spNationality);
rbMale = (RadioButton) findViewById(R.id.rbMale);
rbFemale = (RadioButton) findViewById(R.id.rbFemale);
rgGenderGroup = (RadioGroup) findViewById(R.id.rgGenderGroup);
tlViewGroup = (TableLayout) findViewById(R.id.tlViewGroup);
// Build a table
sqlController = new SQLController(this);
buildTable();
// Initialise the NameValidator
nameValidator = new NameValidator();
birthday = Calendar.getInstance();
// Import countries into spNationality
Locale[] locales = Locale.getAvailableLocales();
ArrayList<String> countries = new ArrayList<>();
for(Locale locale: locales) {
String country = locale.getDisplayCountry();
if(country.trim().length() > 0 && !countries.contains(country)) {
countries.add(country);
}
}
Collections.sort(countries);
for(String country: countries) {
System.out.println(country);
}
ArrayAdapter<String> dataAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_spinner_item, countries);
dataAdapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
spNationality.setAdapter(dataAdapter);
spNationality.setSelection(0);
spNationality.setOnItemSelectedListener(this);
tvDob.setOnClickListener(this);
rgGenderGroup.setOnCheckedChangeListener(this);
btnAdd.setOnClickListener(this);
}
void updateLabel() {
String format = "MM/dd/yy";
SimpleDateFormat dateFormat = new SimpleDateFormat(format, Locale.US);
tvDob.setText(dateFormat.format(birthday.getTime()));
}
@Override
public void onClick(View v) {
switch(v.getId()) {
case R.id.btnAdd:
// Create an instance of a person
person = new Person();
// set person's name
String name = etName.getText().toString();
person.setName(name);
// set person's age
Calendar today = Calendar.getInstance();
int age = today.get(Calendar.YEAR) - today.get(Calendar.YEAR);
if(today.get(Calendar.MONTH) < birthday.get(Calendar.MONTH)) {
age--;
} else if(today.get(Calendar.MONTH) == birthday.get(Calendar.MONTH) && today.get(Calendar.DAY_OF_MONTH) < birthday.get(Calendar.DAY_OF_MONTH)) {
age--;
}
person.setAge(age);
// set person's nationality
String nationality = spNationality.getSelectedItem().toString();
person.setNationality(nationality);
// set person's created time
DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
java.util.Date currentDate = new java.util.Date();
Date currentTime = new Date(currentDate.getTime());
String created_at = dateFormat.format(currentTime);
person.setCreated_at(created_at);
if(!(name.length() > 0)) {
Toast.makeText(this, "Please don't leave the name empty.", Toast.LENGTH_LONG).show();
} else if(!(tvDob.getText().toString().length() > 0)) {
Toast.makeText(this, "Please set a valid birthday.", Toast.LENGTH_LONG).show();
} else if(birthday.getTime().compareTo(new java.util.Date()) > 0) {
Toast.makeText(this, "Wrong birthday. Please set the valid birthday again.", Toast.LENGTH_LONG).show();
} else if(!(rbMale.isChecked() || rbFemale.isChecked())) {
Toast.makeText(this, "Please choose your gender.", Toast.LENGTH_LONG).show();
} else if(!nameValidator.validate(etName.getText().toString())) {
Toast.makeText(this, "Wrong name. Please enter a proper name.", Toast.LENGTH_LONG).show();
} else {
Toast.makeText(this, "New person is added to the database!", Toast.LENGTH_LONG).show();
new MyAsync().execute();
}
break;
case R.id.tvDob:
// Create a DatePickerDialog and store date info into the birthday variable.
DatePickerDialog.OnDateSetListener date = new DatePickerDialog.OnDateSetListener() {
@Override
public void onDateSet(DatePicker view, int year, int monthOfYear, int dayOfMonth) {
birthday.set(Calendar.YEAR, year);
birthday.set(Calendar.MONTH, monthOfYear);
birthday.set(Calendar.DAY_OF_MONTH, dayOfMonth);
updateLabel();
}
};
new DatePickerDialog(this, date, birthday.get(Calendar.YEAR), birthday.get(Calendar.MONTH), birthday.get(Calendar.DAY_OF_MONTH)).show();
break;
}
}
void addDataToRow(Person person) {
}
@Override
public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {
}
@Override
public void onNothingSelected(AdapterView<?> parent) {
}
@Override
public void onCheckedChanged(RadioGroup group, int checkedId) {
switch(checkedId) {
case(R.id.rbMale):
gender = "M";
break;
case(R.id.rbFemale):
gender = "F";
break;
}
}
void buildTable() {
sqlController.open();
Cursor cursor = sqlController.readEntry();
int rows = cursor.getCount();
int columns = cursor.getCount();
cursor.moveToFirst();
// Outer for loop
for(int i=1; i<rows; i++) {
TableRow row = new TableRow(this);
row.setLayoutParams(new LayoutParams(LayoutParams.MATCH_PARENT, LayoutParams.WRAP_CONTENT));
// Inner for loop
for(int j=0; j<columns; j++) {
TextView textView = new TextView(this);
textView.setLayoutParams(new LayoutParams(LayoutParams.MATCH_PARENT, LayoutParams.WRAP_CONTENT));
textView.setGravity(Gravity.CENTER);
textView.setTextSize(10);
textView.setPadding(0, 5, 0, 5);
textView.setText(cursor.getString(j));
row.addView(textView);
}
cursor.moveToNext();
tlViewGroup.addView(row);
}
sqlController.close();
}
private class MyAsync extends AsyncTask<Void, Void, Void> {
@Override
protected void onPreExecute() {
super.onPreExecute();
tlViewGroup.removeAllViews();
progressDialog = new ProgressDialog(getApplicationContext());
progressDialog.setTitle("Please wait");
progressDialog.setMessage("Loading");
progressDialog.setCancelable(false);
progressDialog.show();
}
@Override
protected Void doInBackground(Void... params) {
sqlController.open();
sqlController.insertData(person);
return null;
}
@Override
protected void onPostExecute(Void result) {
super.onPreExecute();
buildTable();
progressDialog.dismiss();
}
}
}
(2) ViewGroupActivity.java
public class ViewGroupActivity extends Activity implements View.OnClickListener {
private TableLayout tlViewGroup;
private TextView tvName, tvAge, tvNationality, tvGender, tvCreatedAt;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_viewgroup);
tlViewGroup = (TableLayout) findViewById(R.id.tlViewGroup);
tvName = (TextView) findViewById(R.id.tvName);
tvAge = (TextView) findViewById(R.id.tvAge);
tvNationality = (TextView) findViewById(R.id.tvNationality);
tvGender = (TextView) findViewById(R.id.tvGender);
tvCreatedAt = (TextView) findViewById(R.id.tvCreatedAt);
tvName.setOnClickListener(this);
tvAge.setOnClickListener(this);
tvNationality.setOnClickListener(this);
tvGender.setOnClickListener(this);
tvCreatedAt.setOnClickListener(this);
}
@Override
public void onClick(View v) {
switch(v.getId()) {
case R.id.tvName:
break;
case R.id.tvAge:
break;
case R.id.tvNationality:
break;
case R.id.tvGender:
break;
case R.id.tvCreatedAt:
break;
}
}
}
(3) SQLController.java
public class SQLController {
private SQLiteHandler handler;
private Context context;
private SQLiteDatabase db;
public SQLController(Context context) {
this.context = context;
}
public SQLController open() throws SQLiteException {
handler = new SQLiteHandler(context);
db = handler.getWritableDatabase();
return this;
}
public void close() {
handler.close();
}
public void insertData(Person person) {
ContentValues values = new ContentValues();
values.put(SQLiteHandler.COLUMN_NAME, person.getName());
values.put(SQLiteHandler.COLUMN_AGE, person.getAge());
values.put(SQLiteHandler.COLUMN_NATIONALITY, person.getNationality());
values.put(SQLiteHandler.COLUMN_GENDER, person.getGender());
values.put(SQLiteHandler.COLUMN_CREATED_AT, person.getCreated_at());
db.insert(SQLiteHandler.TABLE_NAME, null, values);
}
public Cursor readEntry() {
String[] allColumns = new String[] {
SQLiteHandler.COLUMN_NAME, SQLiteHandler.COLUMN_AGE, SQLiteHandler.COLUMN_NATIONALITY, SQLiteHandler.COLUMN_GENDER,
SQLiteHandler.COLUMN_CREATED_AT
};
Cursor cursor = db.query(SQLiteHandler.TABLE_NAME, allColumns, null, null, null, null, null);
if(cursor != null) {
cursor.moveToFirst();
}
return cursor;
}
}
(4) SQLiteHandler.java
public class SQLiteHandler extends SQLiteOpenHelper {
// DB information
static final int DB_VERSION = 1;
static final String DB_NAME = "sortingtester.db";
// Table information
public static final String TABLE_NAME = "People";
public static final String COLUMN_NAME = "Name";
public static final String COLUMN_AGE = "Age";
public static final String COLUMN_NATIONALITY = "Nationality";
public static final String COLUMN_GENDER = "Gender";
public static final String COLUMN_CREATED_AT = "Created_At";
// Statement to create table
private final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " ( " +
COLUMN_NAME + " TEXT NOT NULL, " + COLUMN_AGE + " INT, " + COLUMN_NATIONALITY + " TEXT NOT NULL, " +
COLUMN_GENDER + " CHAR(1) NOT NULL, " + COLUMN_CREATED_AT + " TEXT NOT NULL);";
// Statement to drop table
private final String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE_NAME;
public SQLiteHandler(Context context) {
super(context, DB_NAME, null, DB_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL(DROP_TABLE);
onCreate(db);
}
}
When running the application and trying to start the AddAPersonActivity, I could not even reach the activity itself, as the application is forcefully closed showing the following error messages.
Caused by: java.lang.IllegalStateException: Couldn't read row 0, col 5 from CursorWindow. Make sure the Cursor is initialized correctly before accessing data from it.
at android.database.CursorWindow.nativeGetString(Native Method)
at android.database.CursorWindow.getString(CursorWindow.java:439)
at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:51)
at com.marshall.dbtester.AddAPersonActivity.buildTable(AddAPersonActivity.java:221)
at com.marshall.dbtester.AddAPersonActivity.onCreate(AddAPersonActivity.java:68)
at android.app.Activity.performCreate(Activity.java:5451)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1093)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2377)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2471)
at android.app.ActivityThread.access$900(ActivityThread.java:175)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1308)
at android.os.Handler.dispatchMessage(Handler.java:102)
at android.os.Looper.loop(Looper.java:146)
at android.app.ActivityThread.main(ActivityThread.java:5602)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:515)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1283)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1099)
at dalvik.system.NativeStart.main(Native Method)
It seems like the problem is occurring from the cursor, so I think it is also related to the layout of the ViewGroupActivity. So I'll also attach the xml file of the ViewGroupActivity.
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:orientation="vertical" android:layout_width="match_parent"
android:layout_height="match_parent"
android:padding="30dp"
android:weightSum="1"
android:background="@color/default_background">
<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_weight="0.05"
android:layout_marginBottom="20dp"
android:text="@string/group"
android:textSize="20dp"
android:textColor="@color/default_text"/>
<ScrollView
android:layout_width="match_parent"
android:layout_height="match_parent"
android:layout_weight="0.95">
<TableLayout
android:id="@+id/tlViewGroup"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:orientation="horizontal"
android:shrinkColumns="*"
android:stretchColumns="*"
android:weightSum="1">
<TableRow>
<TextView
android:id="@+id/tvName"
android:text="@string/name"
android:textAlignment="center"
android:layout_weight="0.2"
android:textColor="@color/default_text"/>
<TextView
android:id="@+id/tvAge"
android:text="@string/age"
android:textAlignment="center"
android:layout_weight="0.2"
android:textColor="@color/default_text"/>
<TextView
android:id="@+id/tvNationality"
android:text="@string/nationality"
android:textAlignment="center"
android:layout_weight="0.2"
android:textColor="@color/default_text"/>
<TextView
android:id="@+id/tvGender"
android:text="@string/gender"
android:textAlignment="center"
android:layout_weight="0.2"
android:textColor="@color/default_text"/>
<TextView
android:id="@+id/tvCreatedAt"
android:text="@string/created_at"
android:textAlignment="center"
android:layout_weight="0.2"
android:textColor="@color/default_text"/>
</TableRow>
</TableLayout>
</ScrollView>
</LinearLayout>
I've been struggling with this problem for quite a long time, so your advice will be very much helpful!
You're setting the column count to the size of the cursor.
int columns = cursor.getCount();
User getColumnCount() and it probably should work.