Core JDBC Operations

public class JdbcTemplate {
    public JdbcTemplate(DataSource dataSource) {}

    // Query single value
    public <T> T queryForObject(String sql, Class<T> type, Object... args) {}
    public <T> T queryForObject(String sql, RowMapper<T> mapper, Object... args) {}

    // Query lists
    public <T> List<T> query(String sql, RowMapper<T> mapper, Object... args) {}
    public <T> List<T> queryForList(String sql, Class<T> type, Object... args) {}
    public List<Map<String, Object>> queryForList(String sql, Object... args) {}

    // Query streaming
    public <T> Stream<T> queryForStream(String sql, RowMapper<T> mapper, Object... args) {}

    // Updates
    public int update(String sql, Object... args) {}
    public int update(PreparedStatementCreator psc, KeyHolder keyHolder) {}

    // Batch
    public int[] batchUpdate(String sql, List<Object[]> batchArgs) {}
    public int[] batchUpdate(String sql, BatchPreparedStatementSetter pss) {}

    // Custom processing
    public <T> T execute(ConnectionCallback<T> action) {}
    public <T> T execute(String sql, PreparedStatementCallback<T> action) {}
    public <T> T query(String sql, ResultSetExtractor<T> rse, Object... args) {}
    public void query(String sql, RowCallbackHandler rch, Object... args) {}

    // Configuration
    public void setFetchSize(int fetchSize) {}
    public void setMaxRows(int maxRows) {}
    public void setQueryTimeout(int timeout) {}
}

String name = jdbc.queryForObject("SELECT name FROM users WHERE id = ?", String.class, 123);
Integer count = jdbc.queryForObject("SELECT COUNT(*) FROM users WHERE status = ?", Integer.class, "active");

User user = jdbc.queryForObject(
    "SELECT id, name, email FROM users WHERE id = ?",
    (rs, row) -> new User(rs.getLong("id"), rs.getString("name"), rs.getString("email")),
    123
);

List<User> users = jdbc.query(
    "SELECT id, name, email FROM users WHERE status = ?",
    (rs, row) -> new User(rs.getLong("id"), rs.getString("name"), rs.getString("email")),
    "active"
);

List<String> names = jdbc.queryForList("SELECT name FROM users", String.class);

Map<String, Object> row = jdbc.queryForMap("SELECT * FROM users WHERE id = ?", 123);
List<Map<String, Object>> rows = jdbc.queryForList("SELECT * FROM users WHERE status = ?", "active");

try (Stream<User> stream = jdbc.queryForStream(
    "SELECT * FROM users WHERE status = ?",
    (rs, row) -> new User(...),
    "active"
)) {
    stream.filter(u -> u.getEmail().contains("@company.com"))
          .forEach(this::process);
}

int rows = jdbc.update("UPDATE users SET status = ? WHERE id = ?", "inactive", 123);
int rows = jdbc.update("INSERT INTO users (name, email) VALUES (?, ?)", "John", "john@example.com");
int rows = jdbc.update("DELETE FROM users WHERE status = ?", "inactive");

KeyHolder keyHolder = new GeneratedKeyHolder();
jdbc.update(con -> {
    PreparedStatement ps = con.prepareStatement(
        "INSERT INTO users (name, email) VALUES (?, ?)",
        Statement.RETURN_GENERATED_KEYS
    );
    ps.setString(1, "John");
    ps.setString(2, "john@example.com");
    return ps;
}, keyHolder);
Long id = keyHolder.getKey().longValue();

List<Object[]> batch = List.of(
    new Object[]{"John", "john@example.com"},
    new Object[]{"Jane", "jane@example.com"}
);
int[] counts = jdbc.batchUpdate("INSERT INTO users (name, email) VALUES (?, ?)", batch);

// With BatchPreparedStatementSetter
int[] counts = jdbc.batchUpdate(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    new BatchPreparedStatementSetter() {
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setString(1, names.get(i));
            ps.setString(2, emails.get(i));
        }
        public int getBatchSize() { return names.size(); }
    }
);

public class NamedParameterJdbcTemplate {
    public NamedParameterJdbcTemplate(DataSource ds) {}
    public NamedParameterJdbcTemplate(JdbcTemplate template) {}

    // Query
    public <T> T queryForObject(String sql, Map<String,?> params, Class<T> type) {}
    public <T> T queryForObject(String sql, SqlParameterSource params, RowMapper<T> mapper) {}
    public <T> List<T> query(String sql, Map<String,?> params, RowMapper<T> mapper) {}
    public <T> Stream<T> queryForStream(String sql, Map<String,?> params, RowMapper<T> mapper) {}

    // Update
    public int update(String sql, Map<String,?> params) {}
    public int update(String sql, SqlParameterSource params, KeyHolder keyHolder) {}

    // Batch
    public int[] batchUpdate(String sql, Map<String,?>[] batch) {}
    public int[] batchUpdate(String sql, SqlParameterSource[] batch) {}
}

Map<String, Object> params = Map.of("status", "active", "minAge", 18);
List<User> users = named.query(
    "SELECT * FROM users WHERE status = :status AND age >= :minAge",
    params,
    (rs, row) -> new User(...)
);

List<Integer> ids = List.of(1, 2, 3, 4, 5);
Map<String, Object> params = Map.of("ids", ids);
List<User> users = named.query(
    "SELECT * FROM users WHERE id IN (:ids)",
    params,
    new UserRowMapper()
);

MapSqlParameterSource params = new MapSqlParameterSource()
    .addValue("name", "John")
    .addValue("email", "john@example.com")
    .addValue("status", "active");

int rows = named.update(
    "INSERT INTO users (name, email, status) VALUES (:name, :email, :status)",
    params
);

User user = new User();
user.setName("John");
user.setEmail("john@example.com");

SqlParameterSource params = new BeanPropertySqlParameterSource(user);
named.update("INSERT INTO users (name, email) VALUES (:name, :email)", params);

List<User> users = getUsers();
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(users.toArray());
named.batchUpdate(
    "INSERT INTO users (name, email) VALUES (:name, :email)",
    batch
);

public interface JdbcClient {
    static JdbcClient create(DataSource ds) {}
    StatementSpec sql(String sql);

    interface StatementSpec {
        // Parameters
        StatementSpec param(Object value);
        StatementSpec param(int index, Object value);
        StatementSpec param(String name, Object value);
        StatementSpec params(Object... values);
        StatementSpec params(Map<String,?> params);
        StatementSpec paramSource(SqlParameterSource source);

        // Configuration
        StatementSpec withFetchSize(int size);
        StatementSpec withMaxRows(int max);
        StatementSpec withQueryTimeout(int timeout);

        // Execution
        <T> MappedQuerySpec<T> query(RowMapper<T> mapper);
        <T> MappedQuerySpec<T> query(Class<T> type);
        <T> T query(ResultSetExtractor<T> extractor);
        void query(RowCallbackHandler handler);
        ResultQuerySpec query();
        int update();
        int update(KeyHolder keyHolder);
    }

    interface MappedQuerySpec<T> {
        Optional<T> optional();
        T single();
        List<T> list();
        Set<T> set();
        Stream<T> stream();
    }

    interface ResultQuerySpec {
        Map<String,Object> singleRow();
        List<Map<String,Object>> listOfRows();
        List<Object> singleColumn();
        Object singleValue();
        Optional<Object> optionalValue();
        SqlRowSet rowSet();
    }
}

User user = client.sql("SELECT * FROM users WHERE id = ?")
    .param(123)
    .query((rs, row) -> new User(...))
    .single();

List<User> users = client.sql("SELECT * FROM users WHERE status = ? AND age > ?")
    .params("active", 18)
    .query((rs, row) -> new User(...))
    .list();

User user = client.sql("SELECT * FROM users WHERE id = :userId")
    .param("userId", 123)
    .query((rs, row) -> new User(...))
    .single();

List<User> users = client.sql("SELECT * FROM users WHERE status = :status AND age >= :minAge")
    .param("status", "active")
    .param("minAge", 18)
    .query((rs, row) -> new User(...))
    .list();

Optional<User> user = client.sql("SELECT * FROM users WHERE email = ?")
    .param("unknown@example.com")
    .query((rs, row) -> new User(...))
    .optional();

if (user.isPresent()) {
    // Process user
}

int rows = client.sql("UPDATE users SET status = ? WHERE id = ?")
    .params("inactive", 123)
    .update();

KeyHolder keyHolder = new GeneratedKeyHolder();
client.sql("INSERT INTO users (name, email) VALUES (?, ?)")
    .params("John", "john@example.com")
    .update(keyHolder);
Long id = keyHolder.getKey().longValue();

Map<String, Object> row = client.sql("SELECT * FROM users WHERE id = ?")
    .param(123)
    .query()
    .singleRow();

List<Map<String, Object>> rows = client.sql("SELECT * FROM users WHERE status = ?")
    .param("active")
    .query()
    .listOfRows();

try (Stream<User> stream = client.sql("SELECT * FROM users")
    .query((rs, row) -> new User(...))
    .stream()) {
    stream.filter(u -> u.isActive())
          .forEach(this::process);
}

// ConnectionCallback
Boolean supports = jdbc.execute((ConnectionCallback<Boolean>) con ->
    con.getMetaData().supportsTransactions()
);

// PreparedStatementCallback
Integer count = jdbc.execute(
    "SELECT COUNT(*) FROM users WHERE status = ?",
    (PreparedStatementCallback<Integer>) ps -> {
        ps.setString(1, "active");
        ResultSet rs = ps.executeQuery();
        return rs.next() ? rs.getInt(1) : 0;
    }
);

Map<Long, User> userMap = jdbc.query(
    "SELECT id, name, email FROM users",
    (ResultSetExtractor<Map<Long, User>>) rs -> {
        Map<Long, User> map = new LinkedHashMap<>();
        while (rs.next()) {
            Long id = rs.getLong("id");
            map.put(id, new User(id, rs.getString("name"), rs.getString("email")));
        }
        return map;
    }
);

jdbc.query(
    "SELECT email FROM users WHERE status = ?",
    (RowCallbackHandler) rs -> emailService.send(rs.getString("email"), "Welcome!"),
    "new"
);

JdbcTemplate jdbc = new JdbcTemplate(dataSource);
jdbc.setQueryTimeout(30);      // 30 seconds
jdbc.setFetchSize(1000);       // Fetch 1000 rows at a time
jdbc.setMaxRows(10000);        // Maximum 10000 rows

@Configuration
public class DatabaseConfig {
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @Bean
    public NamedParameterJdbcTemplate namedTemplate(JdbcTemplate jdbc) {
        return new NamedParameterJdbcTemplate(jdbc);
    }

    @Bean
    public JdbcClient jdbcClient(DataSource dataSource) {
        return JdbcClient.create(dataSource);
    }
}

@Service
public class UserService {
    private final JdbcTemplate jdbc;

    public UserService(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    public User findById(Long id) {
        return jdbc.queryForObject(
            "SELECT * FROM users WHERE id = ?",
            (rs, row) -> new User(...),
            id
        );
    }
}