I am doing an attendence program with java and mySQL, and its not working well with me. Here's the code:

Question

There are two tables, EntryTable and ExitTable. Both have similar columns: id, date, time. When the employee punches the card, this function is invoked. If he has already punched on that day, his record is entered into the exit table. Otherwise, it is entered in the entry table.

public int addEntryRecord(String id) {

    try{
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/Attend","rt","rt");
        Statement stmt = con.createStatement();

        long millis=System.currentTimeMillis();
        java.sql.Date date=new java.sql.Date(millis);
        Date today = new Date();
        Date currentTime = new java.sql.Time(today.getTime());

        stmt.executeQuery("SELECT id from Attend.EntryTable WHERE EntryTable.id ='"+id+"'and   
        EntryTable.DayOf='"+date+"'");                       
        ResultSet rs = stmt.getResultSet();
        System.out.println(rs.next());

        if(rs.next()== true){
            String sql = "INSERT Attend.ExitTable VALUES('"+id+"','"+date+"','"+currentTime+" ')";
            stmt.executeUpdate(sql);
            System.out.println("Good job");
           }

        else{
            String sql ="INSERT Attend.EntryTable VALUES('"+id+"','"+date+"','"+currentTime+"')";
            stmt.executeUpdate(sql);
            System.out.println("Entry noted");
           }
       }catch (Exception e) {
            System.out.println(e);
        }
        return 0;
    }

The error is that the record is always into the ExitTable, and I don't know why. Please help.

Answer 1

You have a few problem in your code.

Fist of all ResultSet.next() is not reasuble. If you write :

System.out.println(rs.next());

The next call to rs.next() could be false (if the record was only one as in your case)

If you need to do that, better to do :

boolean next = rs.next();
System.out.println( next );
if ( next ) {  

Then I would suggest using prepared statement and not statement when you need to substituite parameters :

Prepared Statement Javadoc

Plus next time publish table schema, time comparison could be tricky sometimes, have you checked you are using correct date types? (ex, date and not timestamp?) Anyway comaprison would work better if you use Prepared statement.

I would add that you never closed resources (which is wrong MOST of the times).

Here is a short example I've tried locally and work on my system :

    /*

Database schema :

CREATE DATABASE Attend;

GRANT ALL PRIVILEGES ON Attend.* TO rt IDENTIFIED BY 'Tester_2020';

CREATE TABLE EntryTable ( id VARCHAR(100) NOT NULL, DayOf DATE NOT NULL, TimeOf TIME NOT NULL );

CREATE TABLE ExitTable ( id VARCHAR(100) NOT NULL, DayOf DATE NOT NULL, TimeOf TIME NOT NULL );

 */

/*
 * Use an external method to access the connection, better in an external data source or factory.
 */
public Connection getConnection() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    return DriverManager.getConnection("jdbc:mysql://localhost:3306/Attend", "rt", "Tester_2020");
}

/*
 * Usually is better to use a logging facility (slf4j, log4j, java.util.logging etc.)
 */
public void log( String message ) {
    System.out.println( message );
}

/*
 * Better not to suppress exception usually
 */
public int addEntryRecordPS(String id) throws Exception {
    // using try with resources to delegate closing connection.
    try ( Connection con = getConnection() ) {
        long millis = System.currentTimeMillis();
        java.sql.Date date = new java.sql.Date(millis);
        Date today = new Date();
        Time currentTime = new java.sql.Time(today.getTime());
        try ( PreparedStatement checkPstm = con.prepareStatement( "SELECT id from Attend.EntryTable WHERE EntryTable.id = ? and EntryTable.DayOf = ?") ) {
            checkPstm.setString( 1 , id );
            checkPstm.setDate( 2 , date );
            try ( ResultSet rs = checkPstm.executeQuery() ) {
                boolean next = rs.next();
                log( "hasNext? : "+next );
                if ( next ) {
                    // again try with resource (no need to close pstm)
                    try ( PreparedStatement pstm = con.prepareStatement( "INSERT INTO Attend.ExitTable ( id, DayOf, TimeOf ) VALUES ( ?, ? ,? ) " )  ) {
                        pstm.setString( 1 , id );
                        pstm.setDate( 2 , date );
                        pstm.setTime( 3 , currentTime );
                        int res = pstm.executeUpdate();
                        log("Good job "+res);
                    }
                } else {
                    // again try with resource (no need to close pstm)
                    try ( PreparedStatement pstm = con.prepareStatement( "INSERT INTO Attend.EntryTable ( id, DayOf, TimeOf ) VALUES ( ?, ? ,? ) " )  ) {
                        pstm.setString( 1 , id );
                        pstm.setDate( 2 , date );
                        pstm.setTime( 3 , currentTime );
                        int res = pstm.executeUpdate();
                        log("Entry noted "+res);
                    }
                }
            }
        }
    }
    return 0;
}

Hope it can help.