Skip to content

Instantly share code, notes, and snippets.

@eungju
Created January 21, 2011 13:30
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save eungju/789673 to your computer and use it in GitHub Desktop.
Save eungju/789673 to your computer and use it in GitHub Desktop.
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import javax.sql.DataSource;
public class FooDao extends JdbcDaoSupport {
private final String COLUMNS = "id a b";
private final RowMapper<Foo> rowMapper = new RowMapper<Foo>() {
@Override
public Foo mapRow(ResultSet rs, int rowNum) throws SQLException {
return new Foo(
rs.getInt("id"),
rs.getString("a"),
rs.getTimestamp("b"),
);
}
};
public FooDao(DataSource dataSource) {
super(dataSource);
}
public Foo find(int id) {
return queryFirst("SELECT " + COLUMNS + " FROM foo WHERE id=?",
new Object[] { id },
new int[] { Types.INTEGER }, rowMapper);
}
public List<Foo> findAll() {
return query("SELECT " + COLUMNS + " FROM foo",
new Object[] {},
new int[] {}, rowMapper);
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
public abstract class JdbcDaoSupport {
protected final DataSource dataSource;
public JdbcDaoSupport(DataSource dataSource) {
this.dataSource = dataSource;
}
public <T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = dataSource.getConnection();
statement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
statement.setObject(i + 1, args[i], argTypes[i]);
}
rs = statement.executeQuery();
List<T> result = new ArrayList<T>();
int rowNum = 0;
while (rs.next()) {
result.add(rowMapper.mapRow(rs, rowNum));
rowNum++;
}
return result;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
SQLUtils.closeQuietly(rs);
SQLUtils.closeQuietly(statement);
SQLUtils.closeQuietly(connection);
}
}
public <T> T queryFirst(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = dataSource.getConnection();
statement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
statement.setObject(i + 1, args[i], argTypes[i]);
}
rs = statement.executeQuery();
if (rs.next()) {
return rowMapper.mapRow(rs, 0);
}
return null;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
SQLUtils.closeQuietly(rs);
SQLUtils.closeQuietly(statement);
SQLUtils.closeQuietly(connection);
}
}
}
import java.sql.ResultSet;
import java.sql.SQLException;
public interface RowMapper<T> {
T mapRow(ResultSet rs, int rowNum) throws SQLException;
}
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLUtils {
public static void closeQuietly(Connection resource) {
if (resource != null) {
try {
resource.close();
} catch (SQLException e) {
}
}
}
public static void closeQuietly(Statement resource) {
if (resource != null) {
try {
resource.close();
} catch (SQLException e) {
}
}
}
public static void closeQuietly(ResultSet resource) {
if (resource != null) {
try {
resource.close();
} catch (SQLException e) {
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment