SqlUpdate with Named Query and Parameters : SqlUpdate « Spring « Java Tutorial

1. Java Tutorial
2. Spring
3. SqlUpdate

File: context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:lang="http://www.springframework.org/schema/lang"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
                           http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.0.xsd
                           http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd
                           http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd
                           http://www.springframework.org/schema/lang http://www.springframework.org/schema/lang/spring-lang-2.0.xsd">

<!--
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
        <property name="url" value="jdbc:oracle:thin:@oracle.devcake.co.uk:1521:INTL"/>
        <property name="username" value="sa"/>
        <property name="password" value=""/>
    </bean>
-->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
    <property name="url" value="jdbc:hsqldb:mem:."/>
    <property name="username" value="sa"/>
    <property name="password" value=""/>
    </bean>
<!--
    <bean id="mysqlDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://dbhost-prospring-psql/prospring"/>
        <property name="username" value="sa"/>
        <property name="password" value=""/>
    </bean>
-->
    <bean id="lobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler">
        <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/>
    </bean>

    <bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"/>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>


</beans>

File: Main.java

import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

class Main {
  public static void main(String args[]) throws Exception {
    ApplicationContext ac = new ClassPathXmlApplicationContext("context.xml", Main.class);
    DataSource dataSource = (DataSource) ac.getBean("dataSource");
    //DataSource mysqlDataSource = (DataSource) ac.getBean("mysqlDataSource");

    NamedInsert namedInsert = new NamedInsert(dataSource);
    Map<String, Object> parameterMap = new HashMap<String, Object>();
    parameterMap.put("id", 6L);
    parameterMap.put("firstName", "A");
    parameterMap.put("lastName", "B");
    parameterMap.put("lastLogin", null);
    parameterMap.put("comments", null);
    namedInsert.updateByNamedParam(parameterMap);

  }
}

class NamedInsert extends SqlUpdate {
  private static final String SQL = "insert into t_customer (id, first_name, last_name, last_login, " +
          "comments) values (:id, :firstName, :lastName, :lastLogin, :comments)";

  NamedInsert(DataSource dataSource) {
      super(dataSource, SQL);
      declareParameter(new SqlParameter(Types.INTEGER));
      declareParameter(new SqlParameter(Types.VARCHAR));
      declareParameter(new SqlParameter(Types.VARCHAR));
      declareParameter(new SqlParameter(Types.TIMESTAMP));
      declareParameter(new SqlParameter(Types.CLOB));
  }
}

28.29.SqlUpdate
	28.29.1.	SqlUpdate with Named Query and Parameters
	28.29.2.	DeleteStatement With Parameter
	28.29.3.	Extends SqlUpdate To Execute InsertStatement
	28.29.4.	Extends SqlUpdate Execute UpdateStatement
	28.29.5.	Extends SqlUpdate And Provide CustomizedParameters