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 }