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, Mar 06 '17 at 09: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, Mar 06 '17 at 09:09
yeah, I'll convert integer to String while passing it to the arguments — Aishu, Mar 06 '17 at 09:11
@Fildor: Yes, `SELECT *` is not a good idea. However, much worse is using `SELECT` _without a WHERE-condition_. If you run it on a big production database, first you will kill the database, then the DBAs will (probably) kill you... — sleske, Mar 06 '17 at 09:14
How would you do that? Doing `resultSet.getString(columnName)` on a num-type column will fail. — Fildor, Mar 06 '17 at 09:14
@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, Mar 06 '17 at 09:20
@sleske Agreed. Actually one cannot stress that enough. You can kill a perfectly designed DB with bad selects ... — Fildor, Mar 06 '17 at 09:22
@Aishu You may also consider using try-with-resources to gracefully close your statements. — Fildor, Mar 06 '17 at 09:26
After edit: Your query is missing some spaces. `"SELECT TOP 1 " + columnName + "FROM" + tableName + "WHERE" + columnName + "=" + '?';` will result in `"SELECT TOP 1 columnNameFROMtableNameWHEREcolumnName=?;` — Fildor, Mar 06 '17 at 10:22
String query = "SELECT TOP 1 " + columnName + " FROM " + tableName + " WHERE " + columnName + " = " + '?'; — Aishu, Mar 06 '17 at 10:24
I guess you'll have to make that `... + columnName + " = ?;";` — Fildor, Mar 06 '17 at 10:26

Tim Biegeleisen · Accepted Answer · 2017-03-06T09:40:16.760

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.

@Aishu Note that Tim used hard coded values in the query. With PreparedStatements you can of course use replacements to set argument values. — Fildor, Mar 06 '17 at 09:20
@Tim - The above code still returns true if even the values are not found in the db — Aishu, Mar 06 '17 at 10:01
@ Tim - Can u pls tell me what i'm doing wrong here...Not sure — Aishu, Mar 06 '17 at 10:02
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. — Tim Biegeleisen, Mar 06 '17 at 10:03
Downvoter: I'd *really* like to know the reason why this answer should be considered "not helpful". — Fildor, Mar 06 '17 at 10:23
@Aishu Yes, it is. *Your* code is not. See my comment to your question. — Fildor, Mar 06 '17 at 10:25

score 0 · Answer 2 · answered Mar 06 '17 at 09:29

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.

Java - Efficient way to loop through the database records

2 Answers2