SQLInvocable

Here my SQLInvocable pattern. I dislike JPA's such as hibernate/toplink, and I use pure JDBC.


Base class for SQL action is:

 1 /*
 2  * 
 3  * Currently unlicensed
 4  */
 5
 6 package cy6ergn0m.myweb.db.procs;
 7
 8 import cy6ergn0m.myweb.cfg.AppConfig;
 9 import cy6ergn0m.myweb.exceptions.DatabaseException;
10 import java.sql.Connection;
11 import java.sql.ResultSet;
12 import java.sql.SQLException;
13 import java.sql.Statement;
14
15 /**
16  *
17  * @author  cy6erGn0m <cy6erGn0m@gmail.com>
18  */
19 public abstract class SQLInvocable {
20
21     /**
22      * Call this method to execute SQL
23      * @throws cy6ergn0m.myweb.exceptions.DatabaseException
24      */
25     public final void invoke() throws DatabaseException {
26         Connection connection = null;
27         try {
28             connection = AppConfig.getConfig().getConnection();
29             execute( connection );
30         } catch( SQLException e ) {
31             throw new DatabaseException( e );
32         } finally {
33             try {
34                 if( connection != null )
35                     connection.close();
36             } catch( SQLException e ) {
37                 e.printStackTrace();
38             }
39         }
40     }
41
42     protected abstract void execute( Connection connection ) throws SQLException, DatabaseException;
43
44     protected static final void closeResultSet( ResultSet rs ) {
45         try {
46             if( rs != null )
47                 rs.close();
48         } catch ( SQLException e ) {
49             e.printStackTrace();
50         }
51     }
52
53     protected static final void closeStatement( Statement st ) {
54         try {
55             if( st != null )
56                 st.close();
57         } catch ( SQLException e ) {
58             e.printStackTrace();
59         }
60     }
61 }
62
63

Then, implement method execute, like this:

 1 /*
 2  * 
 3  * Currently unlicensed
 4  */
 5
 6 package cy6ergn0m.myweb.db.procs;
 7
 8 import cy6ergn0m.myweb.exceptions.DatabaseException;
 9 import java.sql.Connection;
10 import java.sql.PreparedStatement;
11 import java.sql.ResultSet;
12 import java.sql.SQLException;
13
14 /**
15  *
16  * @author  cy6erGn0m <cy6erGn0m@gmail.com>
17  */
18 public class GetMessagesCountSQL extends SQLInvocable {
19
20     private static final String getMessageCountSql = "select count(id) from messages where user_reciever = ? and was_read = 0";
21
22     private int userId;
23     private int count;
24
25     public void setUserId ( int userId ) {
26         this.userId = userId;
27     }
28
29     public int getCount () {
30         return count;
31     }
32
33     @Override
34     protected void execute ( Connection connection ) throws SQLException,
35                                                             DatabaseException {
36         PreparedStatement st = null;
37         ResultSet rs = null;
38         try {
39             st = connection.prepareStatement( getMessageCountSql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY );
40             st.setInt( 1, userId );
41             rs = st.executeQuery();
42             if( rs.next() )
43                 count = rs.getInt( 1 );
44         } finally {
45             closeResultSet( rs );
46             closeStatement( st );
47         }
48     }
49
50 }
51
52

And now, just use:

GetMessagesCountSQL sql = new GetMessagesCountSQL();
sql.setUserId( userId );
sql.invoke();
count = sql.getCount();