or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

advanced-patterns.mdcore-operations.mddatabase-init.mddatasource.mdembedded-database.mdexception-translation.mdindex.mdkey-generation.mdlob-support.mdresult-parameter-handling.mdsimplified-operations.mdsqlrowset.mdtransactions.md
tile.json

simplified-operations.mddocs/

Simplified Operations

SimpleJdbcInsert and SimpleJdbcCall provide metadata-driven operations for inserts and stored procedures.

SimpleJdbcInsert

Metadata-driven inserts with auto-discovery of columns and generated keys.

API

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

    // Configuration (fluent)
    public SimpleJdbcInsert withTableName(String tableName) {}
    public SimpleJdbcInsert withSchemaName(String schemaName) {}
    public SimpleJdbcInsert withCatalogName(String catalogName) {}
    public SimpleJdbcInsert usingColumns(String... columns) {}
    public SimpleJdbcInsert usingGeneratedKeyColumns(String... columns) {}
    public SimpleJdbcInsert withoutTableColumnMetaDataAccess() {}
    public SimpleJdbcInsert includeSynonymsForTableColumnMetaData() {}

    // Execution
    public int execute(Map<String,?> args) {}
    public int execute(SqlParameterSource args) {}
    public Number executeAndReturnKey(Map<String,?> args) {}
    public Number executeAndReturnKey(SqlParameterSource args) {}
    public KeyHolder executeAndReturnKeyHolder(Map<String,?> args) {}
    public KeyHolder executeAndReturnKeyHolder(SqlParameterSource args) {}
    public int[] executeBatch(Map<String,?>... batch) {}
    public int[] executeBatch(SqlParameterSource... batch) {}
}

Basic Usage

SimpleJdbcInsert insert = new SimpleJdbcInsert(dataSource)
    .withTableName("users");

Map<String, Object> params = Map.of(
    "name", "John Doe",
    "email", "john@example.com",
    "status", "active"
);

int rows = insert.execute(params);

Generated Keys

SimpleJdbcInsert insert = new SimpleJdbcInsert(dataSource)
    .withTableName("users")
    .usingGeneratedKeyColumns("id");

// Single key
Number id = insert.executeAndReturnKey(Map.of("name", "John", "email", "john@example.com"));

// Multiple keys
KeyHolder keyHolder = insert.executeAndReturnKeyHolder(
    Map.of("name", "John", "email", "john@example.com")
);
Long id = keyHolder.getKey().longValue();
Map<String, Object> keys = keyHolder.getKeys();

With Schema

SimpleJdbcInsert insert = new SimpleJdbcInsert(dataSource)
    .withSchemaName("public")
    .withTableName("users")
    .usingGeneratedKeyColumns("id");

Specific Columns

// Only insert specific columns (ignore others in map)
SimpleJdbcInsert insert = new SimpleJdbcInsert(dataSource)
    .withTableName("users")
    .usingColumns("name", "email");

Map<String, Object> params = Map.of(
    "name", "John",
    "email", "john@example.com",
    "extraField", "ignored"  // Ignored
);
insert.execute(params);

Bean-Based Insert

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

SimpleJdbcInsert insert = new SimpleJdbcInsert(dataSource)
    .withTableName("users")
    .usingGeneratedKeyColumns("id");

Number id = insert.executeAndReturnKey(new BeanPropertySqlParameterSource(user));

Batch Insert

SimpleJdbcInsert insert = new SimpleJdbcInsert(dataSource)
    .withTableName("users");

Map<String, Object>[] batch = new Map[]{
    Map.of("name", "User1", "email", "user1@example.com"),
    Map.of("name", "User2", "email", "user2@example.com")
};

int[] counts = insert.executeBatch(batch);

// With SqlParameterSource
SqlParameterSource[] batchParams = SqlParameterSourceUtils.createBatch(users.toArray());
int[] counts = insert.executeBatch(batchParams);

Without Metadata Access

// Skip metadata retrieval (better performance)
SimpleJdbcInsert insert = new SimpleJdbcInsert(dataSource)
    .withTableName("users")
    .usingColumns("name", "email", "status")
    .usingGeneratedKeyColumns("id")
    .withoutTableColumnMetaDataAccess();

SimpleJdbcCall

Metadata-driven stored procedure and function calls.

API

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

    // Configuration
    public SimpleJdbcCall withProcedureName(String name) {}
    public SimpleJdbcCall withFunctionName(String name) {}
    public SimpleJdbcCall withSchemaName(String schema) {}
    public SimpleJdbcCall withCatalogName(String catalog) {}
    public SimpleJdbcCall withReturnValue() {}
    public SimpleJdbcCall declareParameters(SqlParameter... params) {}
    public SimpleJdbcCall withoutProcedureColumnMetaDataAccess() {}
    public SimpleJdbcCall useInParameterNames(String... names) {}

    // Execution
    public Map<String,Object> execute(Object... args) {}
    public Map<String,Object> execute(Map<String,?> args) {}
    public Map<String,Object> execute(SqlParameterSource args) {}
    public <T> T executeFunction(Class<T> returnType, Object... args) {}
    public <T> T executeFunction(Class<T> returnType, Map<String,?> args) {}
    public <T> T executeFunction(Class<T> returnType, SqlParameterSource args) {}
    public <T> T executeObject(Class<T> returnType, Map<String,?> args) {}
}

Stored Procedure

SimpleJdbcCall call = new SimpleJdbcCall(dataSource)
    .withProcedureName("create_user");

Map<String, Object> inParams = Map.of(
    "p_name", "John Doe",
    "p_email", "john@example.com"
);

Map<String, Object> outParams = call.execute(inParams);
Long userId = ((Number) outParams.get("user_id")).longValue();

Stored Function

SimpleJdbcCall call = new SimpleJdbcCall(dataSource)
    .withFunctionName("calculate_total")
    .withReturnValue();

BigDecimal total = call.executeFunction(
    BigDecimal.class,
    Map.of("order_id", 123)
);

With Schema

SimpleJdbcCall call = new SimpleJdbcCall(dataSource)
    .withSchemaName("myschema")
    .withProcedureName("update_inventory");

Explicit Parameter Declaration

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlOutParameter;
import java.sql.Types;

SimpleJdbcCall call = new SimpleJdbcCall(dataSource)
    .withProcedureName("get_user_stats")
    .declareParameters(
        new SqlParameter("user_id", Types.BIGINT),
        new SqlOutParameter("login_count", Types.INTEGER),
        new SqlOutParameter("last_login", Types.TIMESTAMP)
    );

Map<String, Object> result = call.execute(Map.of("user_id", 123));
Integer loginCount = (Integer) result.get("login_count");
Timestamp lastLogin = (Timestamp) result.get("last_login");

Positional Parameters

SimpleJdbcCall call = new SimpleJdbcCall(dataSource)
    .withProcedureName("create_order");

Map<String, Object> result = call.execute(123, "pending", new BigDecimal("99.99"));

ResultSet Processing

import org.springframework.jdbc.core.RowMapper;

SimpleJdbcCall call = new SimpleJdbcCall(dataSource)
    .withProcedureName("get_user_orders")
    .returningResultSet("orders", (rs, row) -> new Order(
        rs.getLong("id"),
        rs.getBigDecimal("total")
    ));

Map<String, Object> result = call.execute(Map.of("user_id", 123));
@SuppressWarnings("unchecked")
List<Order> orders = (List<Order>) result.get("orders");

Multiple ResultSets

SimpleJdbcCall call = new SimpleJdbcCall(dataSource)
    .withProcedureName("get_user_data")
    .returningResultSet("user", new UserRowMapper())
    .returningResultSet("orders", new OrderRowMapper())
    .returningResultSet("addresses", new AddressRowMapper());

Map<String, Object> result = call.execute(Map.of("user_id", 123));
@SuppressWarnings("unchecked")
List<User> users = (List<User>) result.get("user");
@SuppressWarnings("unchecked")
List<Order> orders = (List<Order>) result.get("orders");
@SuppressWarnings("unchecked")
List<Address> addresses = (List<Address>) result.get("addresses");

Without Metadata

SimpleJdbcCall call = new SimpleJdbcCall(dataSource)
    .withProcedureName("update_user")
    .withoutProcedureColumnMetaDataAccess()
    .useInParameterNames("user_id", "name", "email")
    .declareParameters(
        new SqlParameter("user_id", Types.BIGINT),
        new SqlParameter("name", Types.VARCHAR),
        new SqlParameter("email", Types.VARCHAR)
    );

Integration Patterns

Service Layer

@Service
public class UserService {
    private final SimpleJdbcInsert userInsert;
    private final SimpleJdbcCall getUserStatsCall;

    public UserService(DataSource dataSource) {
        this.userInsert = new SimpleJdbcInsert(dataSource)
            .withTableName("users")
            .usingGeneratedKeyColumns("id");

        this.getUserStatsCall = new SimpleJdbcCall(dataSource)
            .withProcedureName("get_user_stats");
    }

    public Long createUser(User user) {
        return userInsert.executeAndReturnKey(
            new BeanPropertySqlParameterSource(user)
        ).longValue();
    }

    public UserStats getStats(Long userId) {
        Map<String, Object> result = getUserStatsCall.execute(Map.of("user_id", userId));
        return new UserStats(
            (Integer) result.get("login_count"),
            (Timestamp) result.get("last_login")
        );
    }
}

Configuration as Bean

@Configuration
public class JdbcConfig {
    @Bean
    public SimpleJdbcInsert userInsert(DataSource dataSource) {
        return new SimpleJdbcInsert(dataSource)
            .withTableName("users")
            .usingGeneratedKeyColumns("id");
    }

    @Bean
    public SimpleJdbcCall createUserProcedure(DataSource dataSource) {
        return new SimpleJdbcCall(dataSource)
            .withProcedureName("create_user");
    }
}

Performance Notes

  • Metadata caching: First call retrieves metadata, subsequent calls use cache
  • Skip metadata: Use withoutTableColumnMetaDataAccess() / withoutProcedureColumnMetaDataAccess() for better performance
  • Explicit columns: Declare columns/parameters explicitly to avoid metadata retrieval
  • Thread-safe: Safe to reuse after configuration
  • Connection pooling: Benefits from prepared statement caching