CODEWITHSUNDEEP
javasqlservletsjdbc
Sai Upadhyayula
Sai Upadhyayula

Reputation: 2496

What is the Best practice for writing a SQL query in JDBC

I am learning Java Web Development by developing an e-Commerce web application using JSP and Servlets with JDBC. I was checking some projects in GitHub, GoogleCode etc and I came across some code which I found unusual like declaring select,update,insert methods in an interface like below :

public interface DBDriver {

public void init( DBConnection connection );
public ResultSet selectQuery( String sqlStatement );
public int updateQuery( String sqlStatement );
public ResultSet select( String table, String[] fields, String where );
public ResultSet select( String table, String[] fields, String where, String[] join, String groupBy[], String having, String orderBy[], int start, int limit );
public int insert( String table, HashMap<String, String> fields );
public int update( String table, HashMap<String, String> fields, String where );
public int update( String table, HashMap<String, String> fields, String where, String orderBy[], int start, int limit );
public int delete( String table, String where );
public int delete( String table, String where, String orderBy[], int start, int limit );
public DBConnection getConnection();

}

And implementing these methods in another class for eg: DBDriverSQL.

One of the implemented method is :

public ResultSet select( String table, String[] fields, String where, String[] join, String groupBy[], String having, String orderBy[], int start, int limit ) {
    StringBuilder sql = new StringBuilder();

    /* Make sure a table is specified */
    if( table == null ) {
        throw new RuntimeException();
    }

    sql.append( "SELECT " );

    /* Empty field list means we'll select all fields */
    if( fields == null || fields.length < 1 ) {
        sql.append( "*" );
    }
    else {
        sql.append( Util.joinArray( fields, "," ) );
    }

    /* Add table and fields list to query */
    sql.append( " FROM " ).append( getFullTableName( table ) );

    /* Any JOINs?*/
    if( join != null && join.length > 0 ) {
        sql.append( " " ).append( Util.joinArray( join, " " ) );
    }

    /* Searching on a WHERE condition? */
    if( where != null && !where.isEmpty() ) {
        sql.append( " WHERE " ).append( where );
    }        

    /* Add GROUP BY clause */
    if( groupBy != null && groupBy.length > 0 ) {
        sql.append( Util.joinArray( groupBy, "," ) );
    }

    if( having != null && !having.isEmpty() ) {
        sql.append( " HAVING " ).append( having );
    }

    if( orderBy != null && orderBy.length > 0 ) {
        sql.append( " ORDER BY " ).append( Util.joinArray( orderBy, "," ) );
    }

    if( limit > 0 ) {
        if( start < 1 ) {
            start = 0;
        }

        sql.append( " LIMIT " ).append( start ).append( "," ).append( limit );
    }

    /* Return the compiled SQL code */
    return selectQuery( sql.toString() );
}

These methods are called in the controller Servlets for data extraction from database. Example:

String where = "listId = " + listId;
    String[] fields = { "b.*, l.listId, l.price, l.comment, l.listDate, l.active, l.condition, l.currency, u.*" };
    String[] join = { "INNER JOIN bzb.book b ON l.isbn=b.isbn",
                "INNER JOIN bzb.user u ON l.userId=u.userId" };
    ResultSet result = bzb.getDriver().select( "booklisting l", fields, where, join, null, null, null, 0, 1 );

My question is, whether this method is considered as a good practice compared to standard JDBC procedure like:

String sql = "select SetID,SetName,SetPrice,SetQuality from setdetails  where heroID = " + id;

        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {

            lists.add(new Set(rs.getInt("SetID"), rs.getString("SetName"), rs.getString("SetPrice"), rs.getString("SetQuality")));
        }
        return lists;

Upvotes: 3

Views: 5708

Answers (2)

user2810910
user2810910

Reputation: 287

I suggest you use the standard JDBC way (or move the SQL to the database as a stored procedure if its complex enough and/or used in many parts of the project). I've written SQL statements using joins that went on for several pages and it will look butt-ugly using the DBDriver method. I suggest staying with the goal of making all your code easy to read over attempting any hard-to-read coding that may save you from typing a few more lines. A simliar argument goes for poorly structured code or ugly-ing up the code to achieve a minor performance gain.

Note the goal of a lot of SQL templates is to avoid writing boiler code over and over again (try/catch/finally/handle exceptions) for each SQL query. The example you provided doesn't do this. It only helps you build the sql statement.

I suggest you use a try/catch/finally block where you close the connection, preparedStatement, and resultSet in the finally block in the reverse order you create them (first checking to see if they are null before closing them).

And if an SQLException is thrown, catch it, add the primary key value of the record that caused the problem (for logging purposes) to the SQLException, and re-throw it.

Upvotes: 4

kosa
kosa

Reputation: 66637

When you use PreparedStament, it doesn't make sense to pass raw parameter. I would to do small modification to make it more Preparedstatement compliance:

String sql = "select SetID,SetName,SetPrice,SetQuality from setdetails  where heroID = ?";

        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,intVal);

Upvotes: 2

Related Questions