Java - Efficient way to loop through the database records

Question

I want to validate whether the telephone number exists in the database column(TelephoneNumber). If exists I will return true or else false This code is not only to verify the TelephoneNumber Column, It may validate other DB columns like FirstName, LastName, EmailAddress etc

public boolean executeDBQuery(String tableName, String columnName,
        String columnValue) {
    try {
        PreparedStatement ps = null;
        String query = "SELECT TOP 1 " + columnName + "FROM" + tableName
                + "WHERE" + columnName + "=" + '?';
        ps = conn.prepareStatement(query);
        ps.setString(1, columnValue);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            return true;
        } else {
            return false;
        }
    } catch (SQLException ex) {

    }

    return false;
}

Main Method:

public static void main(String[] args) {
Database db = new Database();
boolean result = db.executeDBQuery("Application","FirstName","asd");
System.out.println(result);
}

The above code is returning a true value even if the values are not found in the DB. Not sure what I'm doing wrong here. Can anyone help me out

First of all, using "*" is not a good idea. Second: why not use "WHERE" and have the db do the lookup. Using indexes this is probably way more efficient. — Fildor
– Fildor, Commented Mar 6, 2017 at 9:06
I don't see how you'd validate other columns using the code given so far. Does that mean, you'll use the same just for another col name? In that case all of the columns would have to be string columns. What about numerical ones? Also note that you can control case-sensitivity in your DB by chosing the appropriate collation. — Fildor
– Fildor, Commented Mar 6, 2017 at 9:09
yeah, I'll convert integer to String while passing it to the arguments — Aishu
– Aishu, Commented Mar 6, 2017 at 9:11
@Fildor: Yes, of course you are right. I just wanted to stress that not using "WHERE" is not just a bad idea, but more like a disaster waiting to happen. — sleske
– sleske, Commented Mar 6, 2017 at 9:20
I guess you'll have to make that ... + columnName + " = ?;"; — Fildor
– Fildor, Commented Mar 6, 2017 at 10:26

Tim Biegeleisen · Accepted Answer · 2017-03-06 09:40:16Z

3

I think you are not using your database correctly here. Databases were designed to efficiently iterate over all records in a given table and answer a business question. Java, on the other hand, wasn't really designed for this. Currently, you are iterating over the entire table in Java searching for a match. This has the following problems:

A potentially massive amount of data is being passed across the network between your database and Java app
No aids, such as indices, can be used during this search, which may be inefficient
Java wasn't really built to do low level database operations, so the amount of computing time spent in your app could easily exceed the resources which would be needed by the database for the same query. This operation is a resource hog to be certain.

In your case, since you are using SQL Server, the following single query should accomplish the same thing as your current code:

SELECT TOP 1 TelephoneNumber FROM Application WHERE TelephoneNumber = '6553438888';

Here is a code snippet which you can try:

try {
    PreparedStatement ps = null;
    String sql = "SELECT TOP 1 TelephoneNumber FROM Application ";
           sql += "WHERE TelephoneNumber = ?;";
    ps = conn.prepareStatement(sql);
    ps.setString(1, "6553438888");
    ResultSet rs = ps.executeQuery();
    if (rs.next()) {
        System.out.println("Found the phone number");
    }
    else {
        System.out.println("Did not find the phone number");
    }
} catch (SQLException e) {
    // handle exception
} finally {
    // cleanup, close connections, etc.
}

If the number were not found, then the above result set would be empty, and the call to rs.next() would return false. This lets you determine whether or not you found the record in question.

edited Mar 6, 2017 at 9:40

answered Mar 6, 2017 at 9:06

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Fildor Over a year ago

@Aishu Note that Tim used hard coded values in the query. With PreparedStatements you can of course use replacements to set argument values.

Tim Biegeleisen Over a year ago

@Fildor Good idea

Aishu Over a year ago

@Tim - The above code still returns true if even the values are not found in the db

Aishu Over a year ago

@ Tim - Can u pls tell me what i'm doing wrong here...Not sure

Tim Biegeleisen Over a year ago

This isn't the way to get help here. If you want to change your approach, try something, and then open a new question. I can't/won't debug your code via comments like this.

|

AB HAT · Accepted Answer · 2017-03-06 09:29:18Z

You might not want to fetch some million rows through a query in your java application. You will most probably run out of space. Therefore, this kind of query is not recommended.

What you can do is, send the desired phone number to be found to the database query in the where clause. If the number of rows returned is zero then you may return false else you may return true. You may also try fetching only the first row from the database using top or limit.

Note: You may create index on the TelephoneNumber column of your table. Indexes improves the searching performance. If you have kept the TelephoneNumber column of type number instead of varchar then using index will be even faster because number comparisons are faster than text comparisons. Also note that using indexes consumes more space on your database so be careful about using them.

Collectives™ on Stack Overflow

Java - Efficient way to loop through the database records

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related