View Javadoc

    1   package sharin.sql.runner;
    2
    3   import java.sql.Connection;
    4   import java.sql.PreparedStatement;
    5   import java.sql.ResultSet;
    6   import java.util.ArrayList;
    7   import java.util.List;
    8
    9   import javax.sql.DataSource;
    10
    11  import sharin.sql.Sql;
    12  import sharin.util.SqlUtils;
    13
    14  public class BasicSqlRunner implements SqlRunner {
    15
    16      private final DataSource dataSource;
    17
    18      private final ResultSetProcessor objectProcessor;
    19
    20      private final ResultSetProcessor idProcessor;
    21
    22      public BasicSqlRunner(DataSource dataSource) {
    23          this(dataSource, null);
    24      }
    25
    26      public BasicSqlRunner(DataSource dataSource,
    27              ResultSetProcessor objectProcessor) {
    28
    29          this(dataSource, objectProcessor, null);
    30      }
    31
    32      public BasicSqlRunner(DataSource dataSource,
    33              ResultSetProcessor objectProcessor, ResultSetProcessor idProcessor) {
    34
    35          this.dataSource = dataSource;
    36          this.objectProcessor = objectProcessor;
    37          this.idProcessor = idProcessor;
    38      }
    39
    40      @SuppressWarnings("unchecked")
    41      public <R> R selectForObject(Sql sql) {
    42          return (R) selectForObject(sql, objectProcessor);
    43      }
    44
    45      @SuppressWarnings("unchecked")
    46      public <R> R selectForObject(Sql sql, ResultSetProcessor processor) {
    47          R result = null;
    48          Connection conn = SqlUtils.getConnection(dataSource);
    49
    50          try {
    51              PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
    52                      .getText());
    53
    54              try {
    55                  Object[] params = sql.getParams();
    56
    57                  if (params != null) {
    58
    59                      for (int i = 0; i < params.length; i++) {
    60                          SqlUtils.setObject(stmt, i + 1, params[i]);
    61                      }
    62                  }
    63
    64                  ResultSet rs = SqlUtils.executeQuery(stmt);
    65
    66                  try {
    67                      Object context = processor.prepare(rs);
    68
    69                      if (SqlUtils.next(rs)) {
    70                          result = (R) processor.process(rs, context);
    71                      }
    72
    73                  } finally {
    74                      SqlUtils.close(rs);
    75                  }
    76
    77              } finally {
    78                  SqlUtils.close(stmt);
    79              }
    80
    81          } finally {
    82              SqlUtils.close(conn);
    83          }
    84
    85          return result;
    86      }
    87
    88      public <R> List<R> selectForList(Sql sql) {
    89          return selectForList(sql, objectProcessor);
    90      }
    91
    92      public <R> List<R> selectForList(Sql sql, ResultSetProcessor processor) {
    93          return selectForList(sql, Integer.MAX_VALUE, 0, processor);
    94      }
    95
    96      public <R> List<R> selectForList(Sql sql, int limit, int offset) {
    97          return selectForList(sql, limit, offset, objectProcessor);
    98      }
    99
    100     @SuppressWarnings("unchecked")
    101     public <R> List<R> selectForList(Sql sql, int limit, int offset,
    102             ResultSetProcessor processor) {
    103
    104         List<R> resultList = new ArrayList<R>();
    105         Connection conn = SqlUtils.getConnection(dataSource);
    106
    107         try {
    108             PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
    109                     .getText());
    110
    111             try {
    112                 Object[] params = sql.getParams();
    113
    114                 if (params != null) {
    115
    116                     for (int i = 0; i < params.length; i++) {
    117                         SqlUtils.setObject(stmt, i + 1, params[i]);
    118                     }
    119                 }
    120
    121                 ResultSet rs = SqlUtils.executeQuery(stmt);
    122
    123                 try {
    124                     Object context = processor.prepare(rs);
    125
    126                     for (int i = 0; i < offset; i++) {
    127
    128                         if (!SqlUtils.next(rs)) {
    129                             return resultList;
    130                         }
    131                     }
    132
    133                     for (int i = 0; i < limit; i++) {
    134
    135                         if (!SqlUtils.next(rs)) {
    136                             break;
    137                         }
    138
    139                         resultList.add((R) processor.process(rs, context));
    140                     }
    141
    142                 } finally {
    143                     SqlUtils.close(rs);
    144                 }
    145
    146             } finally {
    147                 SqlUtils.close(stmt);
    148             }
    149
    150         } finally {
    151             SqlUtils.close(conn);
    152         }
    153
    154         return resultList;
    155     }
    156
    157     public int insert(Sql sql) {
    158         int count = 0;
    159         Connection conn = SqlUtils.getConnection(dataSource);
    160
    161         try {
    162             PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
    163                     .getText());
    164
    165             try {
    166                 Object[] params = sql.getParams();
    167
    168                 if (params != null) {
    169
    170                     for (int i = 0; i < params.length; i++) {
    171                         SqlUtils.setObject(stmt, i + 1, params[i]);
    172                     }
    173                 }
    174
    175                 count = SqlUtils.executeUpdate(stmt);
    176
    177             } finally {
    178                 SqlUtils.close(stmt);
    179             }
    180
    181         } finally {
    182             SqlUtils.close(conn);
    183         }
    184
    185         return count;
    186     }
    187
    188     @SuppressWarnings("unchecked")
    189     public <I> I insertForId(Sql sql) {
    190         return (I) insertForId(sql, idProcessor);
    191     }
    192
    193     @SuppressWarnings("unchecked")
    194     public <I> I insertForId(Sql sql, ResultSetProcessor processor) {
    195         I id = null;
    196         Connection conn = SqlUtils.getConnection(dataSource);
    197
    198         try {
    199             PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
    200                     .getText());
    201
    202             try {
    203                 Object[] params = sql.getParams();
    204
    205                 if (params != null) {
    206
    207                     for (int i = 0; i < params.length; i++) {
    208                         SqlUtils.setObject(stmt, i + 1, params[i]);
    209                     }
    210                 }
    211
    212                 int count = SqlUtils.executeUpdate(stmt);
    213
    214                 if (count == 1) {
    215                     ResultSet rs = SqlUtils.getGeneratedKeys(stmt);
    216
    217                     try {
    218                         Object context = processor.prepare(rs);
    219
    220                         if (SqlUtils.next(rs)) {
    221                             id = (I) processor.process(rs, context);
    222                         }
    223
    224                     } finally {
    225                         SqlUtils.close(rs);
    226                     }
    227                 }
    228
    229             } finally {
    230                 SqlUtils.close(stmt);
    231             }
    232
    233         } finally {
    234             SqlUtils.close(conn);
    235         }
    236
    237         return id;
    238     }
    239
    240     public int update(Sql sql) {
    241         assert sql.getText().substring(0, 6).equalsIgnoreCase("UPDATE");
    242         return execute(sql);
    243     }
    244
    245     public int delete(Sql sql) {
    246         assert sql.getText().substring(0, 6).equalsIgnoreCase("DELETE");
    247         return execute(sql);
    248     }
    249
    250     public int execute(Sql sql) {
    251         int count = 0;
    252         Connection conn = SqlUtils.getConnection(dataSource);
    253
    254         try {
    255             PreparedStatement stmt = SqlUtils.prepareStatement(conn, sql
    256                     .getText());
    257
    258             try {
    259                 Object[] params = sql.getParams();
    260
    261                 if (params != null) {
    262
    263                     for (int i = 0; i < params.length; i++) {
    264                         SqlUtils.setObject(stmt, i + 1, params[i]);
    265                     }
    266                 }
    267
    268                 count = SqlUtils.executeUpdate(stmt);
    269
    270             } finally {
    271                 SqlUtils.close(stmt);
    272             }
    273
    274         } finally {
    275             SqlUtils.close(conn);
    276         }
    277
    278         return count;
    279     }
    280 }