bind parameter converter sample

Application.java

/**
 * @author Tadaya Tsuyukubo
 */
@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication app = new SpringApplication(Application.class);
        app.setWebApplicationType(WebApplicationType.NONE);
        app.run(args);
    }

    @Bean
    DataSource dataSource() {
        return new EmbeddedDatabaseBuilder()
            .setType(EmbeddedDatabaseType.H2)
            .build();
    }

    @Bean
    CommandLineRunner bootstrap(DataSource dataSource, ConnectionFactory connectionFactory) {
        return args -> {
            JdbcOperations jdbcOperations = new JdbcTemplate(dataSource);
            jdbcOperations.execute("DROP TABLE IF EXISTS test");
            jdbcOperations.execute("CREATE TABLE names ( first_name VARCHAR(20), last_name VARCHAR(20) )");


            AtomicReference<Throwable> failure = new AtomicReference<>();
            Mono.usingWhen(connectionFactory.create(), conn ->
                Mono.from(conn.createStatement("INSERT INTO names (first_name, last_name) VALUES (:FIRST:, :LAST:) ")
                    .bind(":LAST:", "FOO")
                    .bind(":IGNORE:", "will be ignored")
                    .bind(":FIRST:", "foo")
                    .execute()
                ), Connection::close, Connection::close, Connection::close)
                .doOnError(failure::set)
                .subscribe();

            if (failure.get() != null) {
                throw new RuntimeException(failure.get());
            }
        };
    }

    @Bean
    ConnectionFactory connectionFactory() {
        H2ConnectionConfiguration h2Configuration = H2ConnectionConfiguration.builder()
            .username("sa")
            .password("")
            .inMemory("testdb")
            .build();

        ConnectionFactory connectionFactory = new H2ConnectionFactory(h2Configuration);

        ProxyConfig proxyConfig = new ProxyConfig();
        proxyConfig.setBindParameterConverter(new NamedBindParameterConverter());

        QueryExecutionInfoFormatter formatter = QueryExecutionInfoFormatter.showAll();

        ConnectionFactory proxyConnectionFactory = ProxyConnectionFactory.builder(connectionFactory, proxyConfig)
            .listener(new ProxyExecutionListener() {

                @Override
                public void beforeQuery(QueryExecutionInfo execInfo) {
                    System.out.println(formatter.format(execInfo));
                }
            })
            .build();

        return proxyConnectionFactory;
    }

}

NamedBindParameterConverter.java

/**
 * Example converter implementation that converts colon surrounded parameter(":<param>:")
 * to "?" and convert name based binding to index based binding.
 *
 * @author Tadaya Tsuyukubo
 */
public class NamedBindParameterConverter implements BindParameterConverter {

    private static final Pattern PARAMETER_PATTERN = Pattern.compile("(:\\w*:)");

    private static final String PARAMETER_MAP_KEY = "parameter_map";

    private static final String PARAMETER_NAME_TO_IGNORE = ":IGNORE:";

    @Override
    public String onCreateStatement(String query, StatementInfo statementInfo) {

        Matcher matcher = PARAMETER_PATTERN.matcher(query);

        // construct a map that holds named-param to parameter-index. (e.g.: {":foo:":1, ":bar:":2})
        Map<String, Integer> indexByParameter = new HashMap<>();

        int index = 0;  // 0-based
        while (matcher.find()) {
            indexByParameter.put(matcher.group(), index++);
        }

        statementInfo.addCustomValue(PARAMETER_MAP_KEY, indexByParameter);

        String replacedQuery = matcher.replaceAll("?");
        return replacedQuery;
    }

    @Override
    @SuppressWarnings("unchecked")
    public void onBind(BindInfo bindInfo, Statement proxyStatement, Supplier<Statement> defaultBinding) {

        Binding binding = bindInfo.getBinding();
        Object bindKey = binding.getKey();

        if (bindKey instanceof Integer) {  // TODO: may add BindType enum on Binding
            // bind by index
            defaultBinding.get();  // perform default binding behavior
            return;
        }

        String parameterKey = (String) binding.getKey();

        // check the key to skip the bind
        if (PARAMETER_NAME_TO_IGNORE.equalsIgnoreCase(parameterKey)) {
            return;  // skip binding for this key
        }

        StatementInfo statementInfo = bindInfo.getStatementInfo();
        Map<String, Integer> indexByParameter = statementInfo.getCustomValue(PARAMETER_MAP_KEY, Map.class);
        int index = indexByParameter.get(parameterKey);

        // perform index based binding
        BoundValue boundValue = binding.getBoundValue();
        if (boundValue.isNull()) {
            proxyStatement.bindNull(index, boundValue.getNullType());
        } else {
            proxyStatement.bind(index, boundValue.getValue());
        }
    }
}

output.txt

.   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.4.RELEASE)

2019-05-01 17:56:25.237  INFO 75861 --- [           main] io.r2dbc.example.Application             : Starting Application on ttsuyukubo-a01.vmware.com with PID 75861 (/Users/ttsuyukubo/repo/ttddyy/r2dbc-proxy-playground/target/classes started by ttsuyukubo in /Users/ttsuyukubo/repo/ttddyy/r2dbc-proxy-playground)
2019-05-01 17:56:25.239  INFO 75861 --- [           main] io.r2dbc.example.Application             : No active profile set, falling back to default profiles: default
2019-05-01 17:56:25.876  INFO 75861 --- [           main] o.s.j.d.e.EmbeddedDatabaseFactory        : Starting embedded database: url='jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false', username='sa'
2019-05-01 17:56:26.405  INFO 75861 --- [           main] io.r2dbc.example.Application             : Started Application in 1.549 seconds (JVM running for 2.04)
Thread:main(1) Connection:1 Transaction:{Create:0 Rollback:0 Commit:0} Success:False Time:0 Type:Statement BatchSize:0 BindingsSize:1 Query:["INSERT INTO names (first_name, last_name) VALUES (?, ?) "] Bindings:[(foo,FOO)]
2019-05-01 17:56:26.516  INFO 75861 --- [       Thread-3] o.s.j.d.e.EmbeddedDatabaseFactory        : Shutting down embedded database: url='jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false'

Process finished with exit code 0

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
		 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
		 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>net.ttddyy</groupId>
	<artifactId>r2dbc-proxy-playground</artifactId>
	<packaging>jar</packaging>
	<version>1.0-SNAPSHOT</version>
	<name>r2dbc-proxy-playground</name>
	<url>http://maven.apache.org</url>


	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

		<java.version>1.8</java.version>

		<!-- Plugin versions -->
		<compiler.version>3.8.0</compiler.version>
		<surefire.version>2.22.1</surefire.version>
		<source.version>3.0.1</source.version>
		<javadoc.version>3.0.1</javadoc.version>
		<release.version>2.5.3</release.version>

		<!-- Main dependency versions -->
		<spring-boot.version>2.1.4.RELEASE</spring-boot.version>
		<r2dbc-bom.version>0.8.0.BUILD-SNAPSHOT</r2dbc-bom.version>
		<r2dbc-proxy.version>0.8.0.BUILD-SNAPSHOT</r2dbc-proxy.version>

	</properties>

	<repositories>
		<repository>
			<id>spring-snapshots</id>
			<name>Spring Snapshots</name>
			<url>https://repo.spring.io/snapshot</url>
			<snapshots>
				<enabled>true</enabled>
			</snapshots>
		</repository>
		<repository>
			<id>spring-milestones</id>
			<name>Spring Milestones</name>
			<url>https://repo.spring.io/milestone</url>
			<snapshots>
				<enabled>false</enabled>
			</snapshots>
		</repository>
	</repositories>

	<dependencyManagement>
		<dependencies>
			<dependency>
				<groupId>io.r2dbc</groupId>
				<artifactId>r2dbc-bom</artifactId>
				<version>${r2dbc-bom.version}</version>
				<type>pom</type>
				<scope>import</scope>
			</dependency>
			<dependency>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-dependencies</artifactId>
				<version>${spring-boot.version}</version>
				<type>pom</type>
				<scope>import</scope>
			</dependency>
		</dependencies>
	</dependencyManagement>

	<build>
		<plugins>

			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>${compiler.version}</version>
				<configuration>
					<source>${java.version}</source>
					<target>${java.version}</target>
					<testSource>${java.version}</testSource>
					<testTarget>${java.version}</testTarget>
				</configuration>
			</plugin>

		</plugins>
	</build>

	<dependencies>
		<dependency>
			<groupId>io.r2dbc</groupId>
			<artifactId>r2dbc-proxy</artifactId>
			<version>${r2dbc-proxy.version}</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-webflux</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>

		<dependency>
			<groupId>io.r2dbc</groupId>
			<artifactId>r2dbc-h2</artifactId>
			<version>${r2dbc-bom.version}</version>
		</dependency>

	</dependencies>

</project>