（笔记）Spring实战_征服数据库（3）_在Spring中使用JDBC

使用JDBC能够更好地对数据访问的性能进行调优。JDBC允许用户使用数据库的所有特性，而这是其他框架不鼓励甚至禁止的。
JDBC能够让我们在更低的层次上处理数据，能够访问和管理数据库中单独的列（细粒度的数据访问方式）。
Spring的JDBC框架承担了资源管理和异常处理的工作，从而简化了JDBC代码，让我们只需编写从数据库读写数据的必要代码。
使用SimpleJdbcTemplate访问数据
pom.xml

<dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>3.2.17.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>3.2.17.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>3.2.17.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>3.2.17.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.1.0</version>
        </dependency>

DB

-- Create table
create table SPITTER
(
  id       NUMBER,
  username VARCHAR2(20) not null,
  password VARCHAR2(20) not null,
  fullname VARCHAR2(50) not null,
  email    VARCHAR2(50) not null
)
tablespace SPRINGINACTION
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table SPITTER
  add constraint P_ID primary key (ID)
  disable;

spring-spitter.xml

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
        <constructor-arg ref="dataSource_JDBC" />
    </bean>

    <bean id="spitterDao" class="com.springinaction.spitter.JdbcSpitterDAO">
        <property name="jdbcTemplate" ref="jdbcTemplate" />
    </bean>

Spitter.java

package com.springinaction.spitter;

public class Spitter
{
    private int id;

    private String username;

    private String password;

    private String fullname;

    private String email;

    /**
     * @return Returns the id.
     */
    public int getId()
    {
        return id;
    }

    /**
     * @param id
     *            The id to set.
     */
    public void setId(int id)
    {
        this.id = id;
    }

    /**
     * @return Returns the username.
     */
    public String getUsername()
    {
        return username;
    }

    /**
     * @param username
     *            The username to set.
     */
    public void setUsername(String username)
    {
        this.username = username;
    }

    /**
     * @return Returns the password.
     */
    public String getPassword()
    {
        return password;
    }

    /**
     * @param password
     *            The password to set.
     */
    public void setPassword(String password)
    {
        this.password = password;
    }

    /**
     * @return Returns the fullname.
     */
    public String getFullname()
    {
        return fullname;
    }

    /**
     * @param fullname
     *            The fullname to set.
     */
    public void setFullname(String fullname)
    {
        this.fullname = fullname;
    }

    /**
     * @return Returns the email.
     */
    public String getEmail()
    {
        return email;
    }

    /**
     * @param email
     *            The email to set.
     */
    public void setEmail(String email)
    {
        this.email = email;
    }

    @Override
    public String toString()
    {
        return "Spitter [id=" + id + ", username=" + username + ", password=" + password
               + ", fullname=" + fullname + ", email=" + email + "]";
    }

}

SpitterDAO.java

package com.springinaction.spitter;

public interface SpitterDAO
{

    public void addSpitter(Spitter spitter);

    public Spitter getSpitterById(int id);
}

JdbcSpitterDAO.java

package com.springinaction.spitter;


import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;


public class JdbcSpitterDAO implements SpitterDAO
{

    private SimpleJdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(SimpleJdbcTemplate jdbcTemplate)
    {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void addSpitter(Spitter spitter)
    {
        jdbcTemplate.update(
            "INSERT INTO SPITTER(ID,USERNAME,PASSWORD,FULLNAME,EMAIL) VALUES(?,?,?,?,?)",
            spitter.getId(), spitter.getUsername(), spitter.getPassword(), spitter.getFullname(),
            spitter.getEmail());
    }

    public Spitter getSpitterById(int id)
    {
        Spitter spitter = jdbcTemplate.queryForObject(
            "SELECT ID,USERNAME,PASSWORD,FULLNAME,EMAIL FROM SPITTER WHERE ID=?",
            new ParameterizedRowMapper<Spitter>()
            {

                public Spitter mapRow(ResultSet rs, int rowNum)
                    throws SQLException
                {
                    Spitter spitter = new Spitter();
                    spitter.setId(rs.getInt(1));
                    spitter.setUsername(rs.getString(2));
                    spitter.setPassword(rs.getString(3));
                    spitter.setFullname(rs.getString(4));
                    spitter.setEmail(rs.getString(5));
                    return spitter;
                }

            }, id);
        return spitter;
    }

}

Test Demo

package com.springinaction.springtest;


import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.springinaction.spitter.JdbcSpitterDAO;
import com.springinaction.spitter.Spitter;


public class Demo
{

    @Test
    public void test1()
    {
        ApplicationContext ctx = new ClassPathXmlApplicationContext(
            "com/springinaction/spitter/spring-spitter.xml");
        JdbcSpitterDAO dao = (JdbcSpitterDAO)ctx.getBean("spitterDao");
        Spitter spitter = new Spitter();
        spitter.setId(1);
        spitter.setUsername("Jagger");
        spitter.setPassword("123456");
        spitter.setFullname("Jagger S Y CHEN");
        spitter.setEmail("43970615@XX.com");
        dao.addSpitter(spitter);
    }

    @Test
    public void test2()
    {
        ApplicationContext ctx = new ClassPathXmlApplicationContext(
            "com/springinaction/spitter/spring-spitter.xml");
        JdbcSpitterDAO dao = (JdbcSpitterDAO)ctx.getBean("spitterDao");
        Spitter spitter = dao.getSpitterById(1);
        System.out.println(spitter);
    }

}

使用命名参数
使用命名参数查询，绑定值的顺序就不重要了，我们可以按照名字来绑定值。如果查询语句发生了变化导致参数的顺序与之前不一致，我们不需要修改绑定的代码。

    public void addSpitterByNamedParameter(Spitter spitter)
    {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("id", spitter.getId());
        params.put("username", spitter.getUsername());
        params.put("password", spitter.getPassword());
        params.put("fullname", spitter.getFullname());
        params.put("email", spitter.getEmail());
        jdbcTemplate.update(
            "INSERT INTO SPITTER(ID,USERNAME,PASSWORD,FULLNAME,EMAIL) VALUES(:id,:username,:password,:fullname,:email)",
            params);
    }

使用Spring的JDBC DAO支持类
spring-spitter.xml

    <bean id="spitterSupportDao" class="com.springinaction.spitter.JdbcSpitterSupportDAO">
        <property name="dataSource" ref="dataSource_JDBC" />
    </bean>

JdbcSpitterSupportDAO.java

package com.springinaction.spitter;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;


public class JdbcSpitterSupportDAO extends SimpleJdbcDaoSupport implements SpitterDAO
{

    public void addSpitter(Spitter spitter)
    {
        getJdbcTemplate().update(
            "INSERT INTO SPITTER(ID,USERNAME,PASSWORD,FULLNAME,EMAIL) VALUES(?,?,?,?,?)",
            spitter.getId(), spitter.getUsername(), spitter.getPassword(), spitter.getFullname(),
            spitter.getEmail());
    }

    public Spitter getSpitterById(int id)
    {
        Spitter spitter = getJdbcTemplate().queryForObject(
            "SELECT ID,USERNAME,PASSWORD,FULLNAME,EMAIL FROM SPITTER WHERE ID=?",
            new ParameterizedRowMapper<Spitter>()
            {

                public Spitter mapRow(ResultSet rs, int rowNum)
                    throws SQLException
                {
                    Spitter spitter = new Spitter();
                    spitter.setId(rs.getInt(1));
                    spitter.setUsername(rs.getString(2));
                    spitter.setPassword(rs.getString(3));
                    spitter.setFullname(rs.getString(4));
                    spitter.setEmail(rs.getString(5));
                    return spitter;
                }

            }, id);
        return spitter;
    }

    public void addSpitterByNamedParameter(Spitter spitter)
    {
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("id", spitter.getId());
        params.put("username", spitter.getUsername());
        params.put("password", spitter.getPassword());
        params.put("fullname", spitter.getFullname());
        params.put("email", spitter.getEmail());
        getJdbcTemplate().update(
            "INSERT INTO SPITTER(ID,USERNAME,PASSWORD,FULLNAME,EMAIL) VALUES(:id,:username,:password,:fullname,:email)",
            params);
    }

}

执行addSpitterByNamedParameter()方法会抛出如下异常：

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO SPITTER(ID,USERNAME,PASSWORD,FULLNAME,EMAIL) VALUES(:id,:username,:password,:fullname,:email)]; SQL state [99999]; error code [17004]; 无效的列类型; nested exception is java.sql.SQLException: 无效的列类型
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:818)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:874)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:882)
    at com.springinaction.spitter.JdbcSpitterSupportDAO.addSpitterByNamedParameter(JdbcSpitterSupportDAO.java:55)
    at com.springinaction.springtest.Demo5.test6(Demo5.java:93)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.sql.SQLException: 无效的列类型
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8516)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8034)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8767)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8748)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:230)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:402)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:234)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:165)
    at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.doSetValue(ArgumentPreparedStatementSetter.java:65)
    at org.springframework.jdbc.core.ArgumentPreparedStatementSetter.setValues(ArgumentPreparedStatementSetter.java:46)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:822)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:818)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589)
    ... 28 more