CODEWITHSUNDEEP
javamysqlsqljdbc
Quentin Genet
Quentin Genet

Reputation: 155

Comparing VARCHAR from my mysql DB and a String input user in my java code

i'm trying to check if a data is already present in my database and comparing it with user input from my java application. But when i use equals method, i have always a false return... And i would like a "true" return. I don't understand why it doesn't match... Here is my code :

   public boolean getTicketWithRegVehicleNumber(String vehicleRegNumber) {
    Connection con = null;
    boolean valueReturn = false;
    try {
        con = dataBaseConfig.getConnection();
        PreparedStatement ps = con.prepareStatement(DBConstants.GET_VEHICLE_REG_NUMBER);
        ps.setString(1, vehicleRegNumber);
        ResultSet rs = ps.executeQuery();

        //TODO trouver comment tester l'egalité entre la db et la saisie user
        String sqlRequestResult = DBConstants.GET_VEHICLE_REG_NUMBER;
        if (sqlRequestResult.equals(vehicleRegNumber)){
            valueReturn = true;
        }

        dataBaseConfig.closeResultSet(rs);
        dataBaseConfig.closePreparedStatement(ps);
    } catch (Exception ex) {
        logger.error("Error fetching next available slot", ex);
    } finally {
        dataBaseConfig.closeConnection(con);
    }
    return valueReturn;
}

here is my SQL request :

 public static final String GET_VEHICLE_REG_NUMBER = "select t.VEHICLE_REG_NUMBER from ticket t where t.VEHICLE_REG_NUMBER = ? ";

To sum up, i have a boolean variable : returnValue, and i want that when i use equals method on my SQL selected data (a VARCHAR type that i put in a String variable) and user's input which is a String , my boolean return true.

Thanks for helping :-)

Upvotes: 0

Views: 478

Answers (2)

Basil Bourque
Basil Bourque

Reputation: 338785

See correct Answer by Dravidian. Your code neglected to access the ResultSet you named rs. So you never retrieved any data from the database.

Here is a complete example app using the H2 Database Engine.

We hardcode the query result presented in the result set as a single row with a single column of text type with a value of Crêpe. Notice how we access the ResultSet using a loop, accessing the retrieved value with a call to getString. Then we compare strings using String#equals.

Also, notice how this example code uses try-with-resources syntax to automatically close resources such as our Connection, Statement, and ResultSet objects. Make a habit of using try-with-resources syntax to simplify your JDBC code (and similarly file i/o code, etc.).

import org.h2.jdbcx.JdbcDataSource;

import java.sql.*;
import java.time.*;
import java.util.*;

public class App5
{
    public static void main ( String[] args )
    {
        App5 app = new App5();
        app.doIt();
    }

    private void doIt ( )
    {
        JdbcDataSource dataSource = Objects.requireNonNull( new JdbcDataSource() );  // Implementation of `DataSource` bundled with H2.
        dataSource.setURL( "jdbc:h2:mem:text_match;DB_CLOSE_DELAY=-1" ); // Set `DB_CLOSE_DELAY` to `-1` to keep in-memory database in existence after connection closes.
        dataSource.setUser( "scott" );
        dataSource.setPassword( "tiger" );

        try (
                Connection conn = dataSource.getConnection() ;
        )
        {
            String sql = "SELECT 'Crêpe' ;";
            try (
                    Statement stmt = conn.createStatement() ;
                    ResultSet rs = stmt.executeQuery( sql ) ;
            )
            {
                while ( rs.next() )
                {
                    //Retrieve by column number.
                    String result = rs.getString( 1 );

                    //Display values
                    System.out.println( "result: " + result );
                    System.out.println( "result equals crepe: " + result.equals( "crepe" ) );  // False. Missing the circumflex.
                    System.out.println( "result equals crêpe: " + result.equals( "crêpe" ) );  // False. Lowercase `c` does not match `C`.
                    System.out.println( "result equals Crêpe: " + result.equals( "Crêpe" ) );  // True.
                }
            }
        }
        catch ( SQLException e )
        {
            e.printStackTrace();
        }
    }
}

When run.

result: Crêpe
result equals crepe: false
result equals crêpe: false
result equals Crêpe: true

Upvotes: 1

Dravidian
Dravidian

Reputation: 339

You're comparing your plain sql query string (DBConstants.GET_VEHICLE_REG_NUMBER) to the vehicleRegNumber parameter and no wonder they don't match.

What you need to compare is the result from your ps.executeQuery(); which is assigned to ResultSet rs.

Read the ResultSet javadoc to understand how you can extract data from it - https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html.

Upvotes: 1

Related Questions