Issue
I have created one listview where I want to retrieve the items from sqlite database. So I have created one method as a List named as getrequestitems where I will map the details and add to a arraylist.
public List<Map<String, String>> getrequestitems() {
List<Map<String, String>> data = null;
data = new ArrayList<Map<String, String>>();
try {
SQLiteDatabase db = dbHandler.getReadableDatabase();
String sql = "SELECT * from request WHERE time > date('now','-1 day')";
Cursor rs = db.rawQuery(sql,null);
if(rs.moveToFirst()) {
do {
Map<String, String> dtname = new HashMap<String, String>();
dtname.put("bloodgrp", rs.getString(rs.getColumnIndex("bloodgrp")));
dtname.put("date", "Date:- " + rs.getString(rs.getColumnIndex("Datetime1")));
dtname.put("time", "Time:- " + rs.getString(rs.getColumnIndex("time")));
dtname.put("name", "Name:- " + rs.getString(rs.getColumnIndex("Name")));
dtname.put("Username", rs.getString(rs.getColumnIndex("Username")));
data.add(dtname);
}
while (rs.moveToNext());
}
} catch (Exception e) {
Toast.makeText(getApplicationContext(), e.getMessage(), Toast.LENGTH_SHORT).show();
}
return data;
}
But When I open that activity It doesn't displayed any records. I have debug the code but can't any exception. I have written wrong query? as "SELECT * from request WHERE time > date('now','-1 day')";
Here is my complete code.
public class request extends AppCompatActivity {
SimpleAdapter ad;
TextView list3;
private SlidrInterface slidr;
private DBHandler dbHandler;
private static ArrayList<String> arrayList = new ArrayList<>();
@RequiresApi(api = Build.VERSION_CODES.N)
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_request);
slidr = Slidr.attach(this);
slidr.unlock();
dbHandler = new DBHandler(this);
list3 = findViewById(R.id.list3);
TextView txt = findViewById(R.id.list4);
ListView lst1 = (ListView) findViewById(R.id.list2);
List<Map<String, String>> MyDataList = null;
MyDataList = getrequestitems();
String[] from = {"bloodgrp", "date", "time", "name", "Username"};
int[] to = {R.id.listt, R.id.date, R.id.time1, R.id.name3, R.id.user};
ad = new SimpleAdapter(request.this, MyDataList, R.layout.listrequest, from, to);
lst1.setAdapter(ad);
lst1.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> adapterView, View view, int i, long l) {
List<Map<String, String>> MyDataList1 = null;
MyDataList1 = getrequestitems();
String[] from = {"Username"};
int[] to = {R.id.user};
Toast.makeText(getApplicationContext(), MyDataList1.toString(), Toast.LENGTH_SHORT).show();
ad = new SimpleAdapter(request.this, MyDataList1, R.layout.listrequest, from, to);
if(ad.getCount()==0){
Toast.makeText(getApplicationContext(), "No requests are currently visible", Toast.LENGTH_SHORT).show();
}
HashMap<String, String> obj = (HashMap<String, String>) ad.getItem(i);
String name = (String) obj.get("Username");
try {
SQLiteDatabase db = dbHandler.getReadableDatabase();
String sql = "select * from request where Username = '" + name + "'";
Cursor rs = db.rawQuery(sql,null);
if (rs.moveToFirst()) {
do {
runOnUiThread(new Runnable() {
@SuppressLint("Range")
@Override
public void run() {
try {
final AlertDialog.Builder alert = new AlertDialog.Builder(request.this);
View mView = getLayoutInflater().inflate(R.layout.custom_dialog3, null);
final TextView txt_inputText = (TextView) mView.findViewById(R.id.name1);
txt_inputText.setText(rs.getString(rs.getColumnIndex("Name")));
final TextView txt_inputText2 = (TextView) mView.findViewById(R.id.contactno1);
txt_inputText2.setText(rs.getString(rs.getColumnIndex("contactNo")));
final TextView txt_inputText3 = (TextView) mView.findViewById(R.id.message1);
txt_inputText3.setText(rs.getString(rs.getColumnIndex("message")));
Button btn_cancel = (Button) mView.findViewById(R.id.btn_cancel);
Button btn_okay = (Button) mView.findViewById(R.id.btn_okay);
alert.setView(mView);
final AlertDialog alertDialog = alert.create();
alertDialog.setCanceledOnTouchOutside(false);
btn_cancel.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
alertDialog.dismiss();
}
});
btn_okay.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
String number = txt_inputText2.getText().toString();
Intent intent = new Intent(Intent.ACTION_DIAL, Uri.parse("tel:" + number));// Initiates the Intent
startActivity(intent);
alertDialog.dismiss();
}
});
alertDialog.show();
alertDialog.getWindow().setLayout(730, 850); //Controlling width and height.
} catch (Exception e) {
runOnUiThread(new Runnable() {
public void run() {
Toast.makeText(getApplicationContext(), e.getMessage(), Toast.LENGTH_LONG).show();
}
});
}
}
});
}while (rs.moveToNext());
}
} catch (Exception e) {
runOnUiThread(new Runnable() {
public void run() {
Toast.makeText(getApplicationContext(),e.getMessage(),Toast.LENGTH_LONG).show();
}
});
}
}
});
}
@SuppressLint("Range")
public List<Map<String, String>> getrequestitems() {
List<Map<String, String>> data = null;
data = new ArrayList<Map<String, String>>();
try {
SQLiteDatabase db = dbHandler.getReadableDatabase();
String sql = "SELECT * from request WHERE time > date('now','-1 day')";
Cursor rs = db.rawQuery(sql,null);
if(rs.moveToFirst()) {
do {
Map<String, String> dtname = new HashMap<String, String>();
dtname.put("bloodgrp", rs.getString(rs.getColumnIndex("bloodgrp")));
dtname.put("date", "Date:- " + rs.getString(rs.getColumnIndex("Datetime1")));
dtname.put("time", "Time:- " + rs.getString(rs.getColumnIndex("time")));
dtname.put("name", "Name:- " + rs.getString(rs.getColumnIndex("Name")));
dtname.put("Username", rs.getString(rs.getColumnIndex("Username")));
data.add(dtname);
}
while (rs.moveToNext());
}
} catch (Exception e) {
Toast.makeText(getApplicationContext(), e.getMessage(), Toast.LENGTH_SHORT).show();
}
return data;
}
}
Solution
But When I open that activity It doesn't displayed any records. I have debug the code but can't any exception. I have written wrong query?
probably as it appears that you are very likely comparing the anecdotal oranges and apples (see explanation/example) that is, at a guess you are comparing the time and a date (see Possible fix below BUT this very much depends upon what you are actually storing in the time column, see Possible debug action below that would allow you to see a) if you are extracting any data and if applying the get all (no WHERE clause) to see the actual underlying data)
format is yyyy-MM-dd –
That seems unlikely or if not a waste seeing that you have a column for the date and a column for the time.
If the time contains just the time hh:mm or even hh:mm:ss, as an example then extracting the date is going to result in unintended results.
Consider the following that highlights the issues that you may encounter:-
DROP TABLE IF EXISTS request;
CREATE TABLE IF NOT EXISTS request (booldgrp TEXT, Datetime1 TEXT, time TEXT, Name TEXT, Username TEXT);
INSERT INTO request VALUES
('O','2023-01-01 10:10','2023-01-01 10:10','FRED','USER001')
,('O','2023-01-01 10:10','10:10','FRED','USER002')
,('O','2023-01-01 10:10','2023-01-01 23:11','FRED','USER003')
,('O','2023-01-01 10:10','23:59','FRED','USER004')
,('O','2023-01-01 10:10','21:59','FRED','USER005')
,('O','2023-01-01 10:10','22:00','FRED','USER006')
,('O','2023-01-01 10:10','20:00','FRED','USER007')
,('O','2023-01-01 10:10','20:01','FRED','USER008')
,('O','2023-01-01 10:10','19:59:59','FRED','USER009')
;
SELECT *, date('now','-1 day') AS comparedate, time > date('now','-1 day') AS comparison FROM request;
SELECT *, date('now','-1 day') AS comparedate, datetime1 > date('now','-1 day') AS comparison FROM request;
DROP table IF EXISTS request;
- that you can run in your favourite SQLite tool (Navicat was used)
The resultant output (what would be in the Cursor) is :-
As can be seen if the time is actually stored as hh:mm or hh:mm:ss (or similar where you just have the time and not the date) then the first 2 characters are significant, so if the year of the current date - 1 day is in the 21st century that only times that are 8 pm (20:00) or greater would be selected.
Possible fix (if date column has the date only or the date and time and the time column has the time)
Perhaps you want "SELECT * from request WHERE datetime1 > date('now','-1 day')"
Possible debug action
If the above is not the fix for your situation then consider changing your code to :-
SQLiteDatabase db = dbHandler.getReadableDatabase();
String sql = "SELECT * from request /* WHERE time > date('now','-1 day') */"; // <<<<<<<<<< WHERE clause commented out to get ALL data, try also with WHERE clause */
Cursor rs = db.rawQuery(sql,null);
DatabaseUtils.dumpCursor(rs); /* ADDED FOR DEBUG, run and check the LOG */
if(rs.moveToFirst()) {
- see comments
The log would include either something like (where data is extracted ):-
2022-07-19 13:21:50.995 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@31816bf
2022-07-19 13:21:50.996 I/System.out: 0 {
2022-07-19 13:21:50.996 I/System.out: bloodgrp=O
2022-07-19 13:21:50.996 I/System.out: Datetime1=2023-01-01
2022-07-19 13:21:50.996 I/System.out: time=10:30
2022-07-19 13:21:50.996 I/System.out: Name=FRED
2022-07-19 13:21:50.996 I/System.out: Username=USER001
2022-07-19 13:21:50.996 I/System.out: }
2022-07-19 13:21:50.996 I/System.out: <<<<<
or (where no data has been extracted) :-
2022-07-19 13:24:12.216 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@30380ea
2022-07-19 13:24:12.217 I/System.out: <<<<<
Note
The getColumnIndex index method is case sensitive (or at least was) and as such if any of the column names mismatch, even if only due to the case, then the trapped exception row 0 column -1 will also result in no result (only a short while to notice this with a Toast). I would suggest:-
using Constants for column names and ALWAYS using those Constants to refer to column names i.e. only ever hard code each column name once. Incorrect variables names will not compile. Coded incorrect column names will happily compile and may not be noticed.
consider using
e.printStackTrace()as well as or even instead of the Toast- trapping and catching SQLite errors can so easily mask issues
Example
based upon your code, but with :-
DBhandler and layouts composed from what can be gleaned from your code AND
with it inserting a row when the database is created as per :-
public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(" + REQUEST_COLUMN_BLOODGROUP + " TEXT" + "," + REQUEST_COLUMN_DATE + " TEXT" + "," + REQUEST_COLUMN_TIME + " TEXT" + "," + REQUEST_COLUMN_NAME + " TEXT " + "," + REQUEST_COLUMN_USERNAME + " TEXT" + "," + REQUEST_COLUMN_CONTACTNo + " TEXT" + "," + REQUEST_COLUMN_MESSAGE + " TEXT" + ")"); ContentValues cv = new ContentValues(); cv.put(REQUEST_COLUMN_BLOODGROUP,"O"); cv.put(REQUEST_COLUMN_DATE,"2023-01-01"); cv.put(REQUEST_COLUMN_TIME,"10:30"); cv.put(REQUEST_COLUMN_NAME,"FRED"); cv.put(REQUEST_COLUMN_USERNAME,"USER001"); cv.put(REQUEST_COLUMN_CONTACTNo,"0000000000"); cv.put(REQUEST_COLUMN_MESSAGE,"MESSAGE X"); db.insert(TABLE_NAME,null,cv); }with the Request (changed to capitalise) Activity started directly from the MainActivity
and with the possible fix applied to the WHERE clause (i.e. Datetime column used for the comparison rather than the time column)
The ListView with a Teal background (to distinguish it)
Then when run :-
and then when the item is clicked:-
and if OK is clicked :-
Answered By - MikeT
Answer Checked By - Dawn Plyler (JavaFixing Volunteer)
