Get the row count of a MYSQL table where each row contains same date

Question

I have a MYSQL table called "LCI" in which I have the flowing columns and datatypes respectivelyas shown in the picture.

The java application reads two user inputs User name and a date UI where user have to give User name and a date. and compare those two with the columns SUB_ID and the L_DATErespectively.If both of them are matching with a certain record that record should be retrieved onto a table model in java application.Currently this happens if there is only one record exists which satisfies the both of the conditions.If there are many, it retrieves only the latest dates's record

What I actually need is to check how many records are there which tally with the user name(SUB_ID) and the date(L_DATE).If there are more than one record.All of them should be retrieved.

Let's imagine if there are 4 records saved in the table on 2018-03-20 (L_DATE), respectively under the SUB_IDs E/14/, E/1, E/5, E/14, When the user gives the user name E/14 and the date as 2018-03-20, then the table model should show the two records saved under the SUB_ID E/14.

As I see, I need to get the row count of the MYSQL table where username = SUB_ID and date= L_DATE.Then I can put the number of rows in a loop so the vectors can retrieve those records.

I am desperately looking for an answer.Somebody please help me to figure out how to get the row count of a MYSQL table in which I have stored a date and that date tallys with the user input.Thank you all.

Below is the structure of table as sown in MYSQL CLI

mysql> desc lci;
+----------+--------------+------+-----+------------+-------+
| Field    | Type         | Null | Key | Default    | Extra |
+----------+--------------+------+-----+------------+-------+
| L_ID     | varchar(30)  | NO   | PRI |            |       |
| L_DATE   | date         | NO   |     | 0000-00-00 |       |
| HEADING  | varchar(300) | NO   |     |            |       |
| SEC_ID   | varchar(30)  | NO   |     |            |       |
| SUB_ID   | varchar(30)  | YES  |     | NULL       |       |
| I_ID     | varchar(30)  | YES  |     | NULL       |       |
| NEW_INS  | varchar(100) | YES  |     | NULL       |       |
| SEND_ID  | varchar(30)  | YES  |     | NULL       |       |
| NEW_SEND | varchar(100) | YES  |     | NULL       |       |
| R_DATE   | datetime     | YES  |     | NULL       |       |
| L_DATE_  | time         | YES  |     | NULL       |       |
+----------+--------------+------+-----+------------+-------+
11 rows in set (0.02 sec)

Below is the data that I have fed into the table LCI

mysql> select * from lci;
+-------+------------+---------------------+-----------------------+--------+------+---------+---------+----------+---------------------+----------+
| L_ID  | L_DATE     | HEADING             | SEC_ID                | SUB_ID | I_ID | NEW_INS | SEND_ID | NEW_SEND | R_DATE              | L_DATE_  |
+-------+------------+---------------------+-----------------------+--------+------+---------+---------+----------+---------------------+----------+
| 1     | 2017-12-28 | qwerty test         | Development Section   | E/14   | SPDA |         | Item 1  |          | 2017-12-19 00:00:00 | NULL     |
| 10630 | 2018-01-02 | test                | Development Section   | DEV1   | CCO  |         | Item 4  |          | 2018-01-04 00:00:00 | NULL     |
| 12    | 2018-01-02 | test                | Development Section   | E5     | CSO  |         | Item 4  |          | 2018-01-04 10:59:12 | NULL     |
| 14    | 2018-01-04 | Testing             | Establishment Section | E/14   | SPDA |         | Item 2  |          | 2018-01-17 10:54:57 | 10:54:32 |
| 15    | 2018-01-04 | Testing             | Establishment Section | E/14   | CSO  |         | Item 3  |          | 2018-01-08 10:54:57 | 10:54:32 |
| 2     | 2018-01-03 | test                | Development Section   | E14    | PSC  |         | Item 2  |          | 2018-01-05 10:59:12 | NULL     |
| 3     | 2018-01-01 | test                | Establishment Section | E5     | CSO  |         | Item 2  |          | 2018-01-04 11:35:02 | NULL     |
| 34    | 2018-01-15 | 2018 action plan    | Development Section   | E3     | PSC  |         | Item 1  |          | 2018-01-15 15:49:38 | NULL     |
| 4     | 2018-01-02 | test                | Development Section   | DEV1   | CCO  |         | Item 4  |          | 2018-01-04 00:00:00 | NULL     |
| 5     | 2018-01-02 | test                | Development Section   | DEV1   | CCO  |         | Item 4  |          | 2018-01-04 00:00:00 | NULL     |
| 6     | 2018-01-03 | you can do this     | Establishment Section | E14    | CSO  |         | Item 1  |          | 2018-01-10 12:57:04 | NULL     |
| 678   | 2018-03-07 | qwerty              | Establishment Section | E/1    | CSO  |         | Item 1  |          | 2018-03-06 12:28:37 | 12:28:24 |
| 679   | 2018-03-07 | qwerty              | Establishment Section | E/14   | CSO  |         | Item 1  |          | 2018-03-06 12:28:37 | 12:28:24 |
| 7     | 2018-01-04 | yes you can do this | Establishment Section | E/14   | CSO  |         | Item 1  |          | 2018-01-09 14:10:56 | 14:10:36 |
+-------+------------+---------------------+-----------------------+--------+------+---------+---------+----------+---------------------+----------+
14 rows in set (0.00 sec)

Answer 1

you dont need to get the record count from the database to fill in your vector you can use the size of the returned dataset.

Still you can get the record count with this query:

select count(*) from LCI where username = ? and date= ?

Or

if you want the records along with their count then use this but its not the right way to do this:

select * , (select count(*) from LCI where  username = ? and date= ?  ) as thecount
from LCI 
where username = ? and date= ?