Spring Architecture Series-6.Implementing JDBC Module in Spring - 拾光赋-拾光赋

Spring Architecture Series-6.Implementing JDBC Module in Spring

15天前发布

03814

Introduce

JDBC(Java Database Connectivity) is the standard API for database access in Java.However,working with JDBC can be tedious and error-prone.In this article,i”ll explore how to implement a JDBC module that simplifies database operations, based on my miniSpring project’s implementation.

Core Components

The JDBC module consists of several key components:


src/com/yaruyng/jdbc/
├── core/
│   ├── JdbcTemplate.java
│   ├── RowMapper.java
│   ├── ResultSetExtractor.java
│   ├── StatementCallBack.java
│   ├── PreparedStatementCallBack.java
│   └── ArgumentPreparedStatementSetter.java
├── datasource/
└── pool/
src/com/yaruyng/jdbc/
├── core/
│   ├── JdbcTemplate.java
│   ├── RowMapper.java
│   ├── ResultSetExtractor.java
│   ├── StatementCallBack.java
│   ├── PreparedStatementCallBack.java
│   └── ArgumentPreparedStatementSetter.java
├── datasource/
└── pool/
src/com/yaruyng/jdbc/
├── core/
│   ├── JdbcTemplate.java
│   ├── RowMapper.java
│   ├── ResultSetExtractor.java
│   ├── StatementCallBack.java
│   ├── PreparedStatementCallBack.java
│   └── ArgumentPreparedStatementSetter.java
├── datasource/
└── pool/

Enter fullscreen mode Exit fullscreen mode

JdbcTemplate:The Core Class

The JdbcTemplate class is the central component that simplifies JDBC operations:


<span>public</span> <span>class</span> <span>JdbcTemplate</span> <span>{</span>
  <span>private</span> <span>DataSource</span> <span>dataSource</span><span>;</span>
  <span>public</span> <span>Object</span> <span>query</span><span>(</span><span>StatementCallBack</span> <span>stmtCallBack</span><span>)</span> <span>{</span>
    <span>Connection</span> <span>con</span> <span>=</span> <span>null</span><span>;</span>
    <span>Statement</span> <span>stmt</span> <span>=</span> <span>null</span><span>;</span>
    <span>try</span> <span>{</span>
      <span>con</span> <span>=</span> <span>dataSource</span><span>.</span><span>getConnection</span><span>();</span>
      <span>stmt</span> <span>=</span> <span>con</span><span>.</span><span>createStatement</span><span>();</span>
      <span>return</span> <span>stmtCallBack</span><span>.</span><span>doInStatement</span><span>(</span><span>stmt</span><span>);</span>
    <span>}</span> <span>catch</span> <span>(</span><span>SQLException</span> <span>e</span><span>)</span> <span>{</span>
      <span>e</span><span>.</span><span>printStackTrace</span><span>();</span>
    <span>}</span> <span>finally</span> <span>{</span>
      <span>try</span> <span>{</span>
        <span>stmt</span><span>.</span><span>close</span><span>();</span>
        <span>con</span><span>.</span><span>close</span><span>();</span>
      <span>}</span> <span>catch</span> <span>(</span><span>SQLException</span> <span>e</span><span>)</span> <span>{</span>
      <span>}</span>
    <span>}</span>
    <span>return</span> <span>null</span><span>;</span>
  <span>}</span>
  <span>public</span> <span>Object</span> <span>query</span><span>(</span><span>String</span> <span>sql</span><span>,</span> <span>Object</span><span>[]</span> <span>args</span><span>,</span>
                      <span>PreparedStatementCallBack</span> <span>pstmtCallBack</span><span>)</span> <span>{</span>
    <span>Connection</span> <span>con</span> <span>=</span> <span>null</span><span>;</span>
    <span>PreparedStatement</span> <span>pstmt</span> <span>=</span> <span>null</span><span>;</span>
    <span>try</span> <span>{</span>
      <span>con</span> <span>=</span> <span>dataSource</span><span>.</span><span>getConnection</span><span>();</span>
      <span>pstmt</span> <span>=</span> <span>con</span><span>.</span><span>prepareStatement</span><span>(</span><span>sql</span><span>);</span>
      <span>ArgumentPreparedStatementSetter</span> <span>setter</span> <span>=</span>
        <span>new</span> <span>ArgumentPreparedStatementSetter</span><span>(</span><span>args</span><span>);</span>
      <span>setter</span><span>.</span><span>setValues</span><span>(</span><span>pstmt</span><span>);</span>
      <span>return</span> <span>pstmtCallBack</span><span>.</span><span>doInPreparedStatement</span><span>(</span><span>pstmt</span><span>);</span>
    <span>}</span> <span>catch</span> <span>(</span><span>SQLException</span> <span>e</span><span>)</span> <span>{</span>
      <span>throw</span> <span>new</span> <span>RuntimeException</span><span>(</span><span>e</span><span>);</span>
    <span>}</span> <span>finally</span> <span>{</span>
      <span>try</span> <span>{</span>
        <span>pstmt</span><span>.</span><span>close</span><span>();</span>
        <span>con</span><span>.</span><span>close</span><span>();</span>
      <span>}</span> <span>catch</span> <span>(</span><span>SQLException</span> <span>e</span><span>)</span> <span>{</span>
      <span>}</span>
    <span>}</span>
  <span>}</span>
<span>}</span>
<span>public</span> <span>class</span> <span>JdbcTemplate</span> <span>{</span>
  <span>private</span> <span>DataSource</span> <span>dataSource</span><span>;</span>

  <span>public</span> <span>Object</span> <span>query</span><span>(</span><span>StatementCallBack</span> <span>stmtCallBack</span><span>)</span> <span>{</span>
    <span>Connection</span> <span>con</span> <span>=</span> <span>null</span><span>;</span>
    <span>Statement</span> <span>stmt</span> <span>=</span> <span>null</span><span>;</span>
    <span>try</span> <span>{</span>
      <span>con</span> <span>=</span> <span>dataSource</span><span>.</span><span>getConnection</span><span>();</span>
      <span>stmt</span> <span>=</span> <span>con</span><span>.</span><span>createStatement</span><span>();</span>
      <span>return</span> <span>stmtCallBack</span><span>.</span><span>doInStatement</span><span>(</span><span>stmt</span><span>);</span>
    <span>}</span> <span>catch</span> <span>(</span><span>SQLException</span> <span>e</span><span>)</span> <span>{</span>
      <span>e</span><span>.</span><span>printStackTrace</span><span>();</span>
    <span>}</span> <span>finally</span> <span>{</span>
      <span>try</span> <span>{</span>
        <span>stmt</span><span>.</span><span>close</span><span>();</span>
        <span>con</span><span>.</span><span>close</span><span>();</span>
      <span>}</span> <span>catch</span> <span>(</span><span>SQLException</span> <span>e</span><span>)</span> <span>{</span>
      <span>}</span>
    <span>}</span>
    <span>return</span> <span>null</span><span>;</span>
  <span>}</span>

  <span>public</span> <span>Object</span> <span>query</span><span>(</span><span>String</span> <span>sql</span><span>,</span> <span>Object</span><span>[]</span> <span>args</span><span>,</span>
                      <span>PreparedStatementCallBack</span> <span>pstmtCallBack</span><span>)</span> <span>{</span>
    <span>Connection</span> <span>con</span> <span>=</span> <span>null</span><span>;</span>
    <span>PreparedStatement</span> <span>pstmt</span> <span>=</span> <span>null</span><span>;</span>
    <span>try</span> <span>{</span>
      <span>con</span> <span>=</span> <span>dataSource</span><span>.</span><span>getConnection</span><span>();</span>
      <span>pstmt</span> <span>=</span> <span>con</span><span>.</span><span>prepareStatement</span><span>(</span><span>sql</span><span>);</span>
      <span>ArgumentPreparedStatementSetter</span> <span>setter</span> <span>=</span>
        <span>new</span> <span>ArgumentPreparedStatementSetter</span><span>(</span><span>args</span><span>);</span>
      <span>setter</span><span>.</span><span>setValues</span><span>(</span><span>pstmt</span><span>);</span>
      <span>return</span> <span>pstmtCallBack</span><span>.</span><span>doInPreparedStatement</span><span>(</span><span>pstmt</span><span>);</span>
    <span>}</span> <span>catch</span> <span>(</span><span>SQLException</span> <span>e</span><span>)</span> <span>{</span>
      <span>throw</span> <span>new</span> <span>RuntimeException</span><span>(</span><span>e</span><span>);</span>
    <span>}</span> <span>finally</span> <span>{</span>
      <span>try</span> <span>{</span>
        <span>pstmt</span><span>.</span><span>close</span><span>();</span>
        <span>con</span><span>.</span><span>close</span><span>();</span>
      <span>}</span> <span>catch</span> <span>(</span><span>SQLException</span> <span>e</span><span>)</span> <span>{</span>
      <span>}</span>
    <span>}</span>
  <span>}</span>
<span>}</span>
public class JdbcTemplate {
  private DataSource dataSource;

  public Object query(StatementCallBack stmtCallBack) {
    Connection con = null;
    Statement stmt = null;
    try {
      con = dataSource.getConnection();
      stmt = con.createStatement();
      return stmtCallBack.doInStatement(stmt);
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      try {
        stmt.close();
        con.close();
      } catch (SQLException e) {
      }
    }
    return null;
  }

  public Object query(String sql, Object[] args,
                      PreparedStatementCallBack pstmtCallBack) {
    Connection con = null;
    PreparedStatement pstmt = null;
    try {
      con = dataSource.getConnection();
      pstmt = con.prepareStatement(sql);
      ArgumentPreparedStatementSetter setter =
        new ArgumentPreparedStatementSetter(args);
      setter.setValues(pstmt);
      return pstmtCallBack.doInPreparedStatement(pstmt);
    } catch (SQLException e) {
      throw new RuntimeException(e);
    } finally {
      try {
        pstmt.close();
        con.close();
      } catch (SQLException e) {
      }
    }
  }
}

Enter fullscreen mode Exit fullscreen mode

Key features:

Resource management
Exception handing
Connection pooling support
Prepared statement support

Row Mapping

The RowMapper interface provides a flexible way to map database rows to objects:


<span>public</span> <span>interface</span> <span>RowMapper</span><span><</span><span>T</span><span>></span> <span>{</span>
    <span>T</span> <span>mapRow</span><span>(</span><span>ResultSet</span> <span>rs</span><span>,</span> <span>int</span> <span>rowNum</span><span>)</span> <span>throws</span> <span>SQLException</span><span>;</span>
<span>}</span>
<span>public</span> <span>interface</span> <span>RowMapper</span><span><</span><span>T</span><span>></span> <span>{</span>
    <span>T</span> <span>mapRow</span><span>(</span><span>ResultSet</span> <span>rs</span><span>,</span> <span>int</span> <span>rowNum</span><span>)</span> <span>throws</span> <span>SQLException</span><span>;</span>
<span>}</span>
public interface RowMapper<T> {
    T mapRow(ResultSet rs, int rowNum) throws SQLException;
}

Enter fullscreen mode Exit fullscreen mode

Usage example


<span>public</span> <span>class</span> <span>UserRowMapper</span> <span>implements</span> <span>RowMapper</span><span><</span><span>User</span><span>></span> <span>{</span>
    <span>@Override</span>
    <span>public</span> <span>User</span> <span>mapRow</span><span>(</span><span>ResultSet</span> <span>rs</span><span>,</span> <span>int</span> <span>rowNum</span><span>)</span> <span>throws</span> <span>SQLException</span> <span>{</span>
        <span>User</span> <span>user</span> <span>=</span> <span>new</span> <span>User</span><span>();</span>
        <span>user</span><span>.</span><span>setId</span><span>(</span><span>rs</span><span>.</span><span>getInt</span><span>(</span><span>"id"</span><span>));</span>
        <span>user</span><span>.</span><span>setName</span><span>(</span><span>rs</span><span>.</span><span>getString</span><span>(</span><span>"name"</span><span>));</span>
        <span>user</span><span>.</span><span>setEmail</span><span>(</span><span>rs</span><span>.</span><span>getString</span><span>(</span><span>"email"</span><span>));</span>
        <span>return</span> <span>user</span><span>;</span>
    <span>}</span>
<span>}</span>
<span>public</span> <span>class</span> <span>UserRowMapper</span> <span>implements</span> <span>RowMapper</span><span><</span><span>User</span><span>></span> <span>{</span>
    <span>@Override</span>
    <span>public</span> <span>User</span> <span>mapRow</span><span>(</span><span>ResultSet</span> <span>rs</span><span>,</span> <span>int</span> <span>rowNum</span><span>)</span> <span>throws</span> <span>SQLException</span> <span>{</span>
        <span>User</span> <span>user</span> <span>=</span> <span>new</span> <span>User</span><span>();</span>
        <span>user</span><span>.</span><span>setId</span><span>(</span><span>rs</span><span>.</span><span>getInt</span><span>(</span><span>"id"</span><span>));</span>
        <span>user</span><span>.</span><span>setName</span><span>(</span><span>rs</span><span>.</span><span>getString</span><span>(</span><span>"name"</span><span>));</span>
        <span>user</span><span>.</span><span>setEmail</span><span>(</span><span>rs</span><span>.</span><span>getString</span><span>(</span><span>"email"</span><span>));</span>
        <span>return</span> <span>user</span><span>;</span>
    <span>}</span>
<span>}</span>
public class UserRowMapper implements RowMapper<User> {
    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setName(rs.getString("name"));
        user.setEmail(rs.getString("email"));
        return user;
    }
}

Enter fullscreen mode Exit fullscreen mode

Parameter Handling

The ArgumentPreparedStatementSetter class handles parameter binding:


<span>public</span> <span>class</span> <span>ArgumentPreparedStatementSetter</span> <span>{</span>
    <span>private</span> <span>final</span> <span>Object</span><span>[]</span> <span>args</span><span>;</span>
    <span>public</span> <span>void</span> <span>setValues</span><span>(</span><span>PreparedStatement</span> <span>pstmt</span><span>)</span> <span>throws</span> <span>SQLException</span> <span>{</span>
        <span>if</span> <span>(</span><span>this</span><span>.</span><span>args</span> <span>!=</span> <span>null</span><span>)</span> <span>{</span>
            <span>for</span> <span>(</span><span>int</span> <span>i</span> <span>=</span> <span>0</span><span>;</span> <span>i</span> <span><</span> <span>this</span><span>.</span><span>args</span><span>.</span><span>length</span><span>;</span> <span>i</span><span>++)</span> <span>{</span>
                <span>Object</span> <span>arg</span> <span>=</span> <span>this</span><span>.</span><span>args</span><span>[</span><span>i</span><span>];</span>
                <span>doSetValue</span><span>(</span><span>pstmt</span><span>,</span> <span>i</span><span>+</span><span>1</span><span>,</span> <span>arg</span><span>);</span>
            <span>}</span>
        <span>}</span>
    <span>}</span>
    <span>private</span> <span>void</span> <span>doSetValue</span><span>(</span><span>PreparedStatement</span> <span>pstmt</span><span>,</span> 
            <span>int</span> <span>parameterPosition</span><span>,</span> <span>Object</span> <span>argValue</span><span>)</span> <span>throws</span> <span>SQLException</span> <span>{</span>
        <span>if</span> <span>(</span><span>argValue</span> <span>instanceof</span> <span>String</span><span>)</span> <span>{</span>
            <span>pstmt</span><span>.</span><span>setString</span><span>(</span><span>parameterPosition</span><span>,</span> <span>(</span><span>String</span><span>)</span><span>argValue</span><span>);</span>
        <span>}</span>
        <span>else</span> <span>if</span> <span>(</span><span>argValue</span> <span>instanceof</span> <span>Integer</span><span>)</span> <span>{</span>
            <span>pstmt</span><span>.</span><span>setInt</span><span>(</span><span>parameterPosition</span><span>,</span> <span>(</span><span>int</span><span>)</span><span>argValue</span><span>);</span>
        <span>}</span>
        <span>else</span> <span>if</span> <span>(</span><span>argValue</span> <span>instanceof</span> <span>java</span><span>.</span><span>util</span><span>.</span><span>Date</span><span>)</span> <span>{</span>
            <span>pstmt</span><span>.</span><span>setDate</span><span>(</span><span>parameterPosition</span><span>,</span> 
                <span>new</span> <span>java</span><span>.</span><span>sql</span><span>.</span><span>Date</span><span>(((</span><span>java</span><span>.</span><span>util</span><span>.</span><span>Date</span><span>)</span><span>argValue</span><span>).</span><span>getTime</span><span>()));</span>
        <span>}</span>
    <span>}</span>
<span>}</span>
<span>public</span> <span>class</span> <span>ArgumentPreparedStatementSetter</span> <span>{</span>
    <span>private</span> <span>final</span> <span>Object</span><span>[]</span> <span>args</span><span>;</span>

    <span>public</span> <span>void</span> <span>setValues</span><span>(</span><span>PreparedStatement</span> <span>pstmt</span><span>)</span> <span>throws</span> <span>SQLException</span> <span>{</span>
        <span>if</span> <span>(</span><span>this</span><span>.</span><span>args</span> <span>!=</span> <span>null</span><span>)</span> <span>{</span>
            <span>for</span> <span>(</span><span>int</span> <span>i</span> <span>=</span> <span>0</span><span>;</span> <span>i</span> <span><</span> <span>this</span><span>.</span><span>args</span><span>.</span><span>length</span><span>;</span> <span>i</span><span>++)</span> <span>{</span>
                <span>Object</span> <span>arg</span> <span>=</span> <span>this</span><span>.</span><span>args</span><span>[</span><span>i</span><span>];</span>
                <span>doSetValue</span><span>(</span><span>pstmt</span><span>,</span> <span>i</span><span>+</span><span>1</span><span>,</span> <span>arg</span><span>);</span>
            <span>}</span>
        <span>}</span>
    <span>}</span>

    <span>private</span> <span>void</span> <span>doSetValue</span><span>(</span><span>PreparedStatement</span> <span>pstmt</span><span>,</span> 
            <span>int</span> <span>parameterPosition</span><span>,</span> <span>Object</span> <span>argValue</span><span>)</span> <span>throws</span> <span>SQLException</span> <span>{</span>
        <span>if</span> <span>(</span><span>argValue</span> <span>instanceof</span> <span>String</span><span>)</span> <span>{</span>
            <span>pstmt</span><span>.</span><span>setString</span><span>(</span><span>parameterPosition</span><span>,</span> <span>(</span><span>String</span><span>)</span><span>argValue</span><span>);</span>
        <span>}</span>
        <span>else</span> <span>if</span> <span>(</span><span>argValue</span> <span>instanceof</span> <span>Integer</span><span>)</span> <span>{</span>
            <span>pstmt</span><span>.</span><span>setInt</span><span>(</span><span>parameterPosition</span><span>,</span> <span>(</span><span>int</span><span>)</span><span>argValue</span><span>);</span>
        <span>}</span>
        <span>else</span> <span>if</span> <span>(</span><span>argValue</span> <span>instanceof</span> <span>java</span><span>.</span><span>util</span><span>.</span><span>Date</span><span>)</span> <span>{</span>
            <span>pstmt</span><span>.</span><span>setDate</span><span>(</span><span>parameterPosition</span><span>,</span> 
                <span>new</span> <span>java</span><span>.</span><span>sql</span><span>.</span><span>Date</span><span>(((</span><span>java</span><span>.</span><span>util</span><span>.</span><span>Date</span><span>)</span><span>argValue</span><span>).</span><span>getTime</span><span>()));</span>
        <span>}</span>
    <span>}</span>
<span>}</span>
public class ArgumentPreparedStatementSetter {
    private final Object[] args;

    public void setValues(PreparedStatement pstmt) throws SQLException {
        if (this.args != null) {
            for (int i = 0; i < this.args.length; i++) {
                Object arg = this.args[i];
                doSetValue(pstmt, i+1, arg);
            }
        }
    }

    private void doSetValue(PreparedStatement pstmt, 
            int parameterPosition, Object argValue) throws SQLException {
        if (argValue instanceof String) {
            pstmt.setString(parameterPosition, (String)argValue);
        }
        else if (argValue instanceof Integer) {
            pstmt.setInt(parameterPosition, (int)argValue);
        }
        else if (argValue instanceof java.util.Date) {
            pstmt.setDate(parameterPosition, 
                new java.sql.Date(((java.util.Date)argValue).getTime()));
        }
    }
}

Enter fullscreen mode Exit fullscreen mode

Features:

Type-safe parameter binding
Support for common data types
Extensible design

Result Set Extraction

The ResultSetExtractor interface provides a way to process result sets:


<span>public</span> <span>interface</span> <span>ResultSetExtractor</span><span><</span><span>T</span><span>></span> <span>{</span>
  <span>T</span> <span>extractData</span><span>(</span><span>ResultSet</span> <span>rs</span><span>)</span> <span>throws</span> <span>SQLException</span><span>;</span>
<span>}</span>
<span>public</span> <span>interface</span> <span>ResultSetExtractor</span><span><</span><span>T</span><span>></span> <span>{</span>
  <span>T</span> <span>extractData</span><span>(</span><span>ResultSet</span> <span>rs</span><span>)</span> <span>throws</span> <span>SQLException</span><span>;</span>
<span>}</span>
public interface ResultSetExtractor<T> {
  T extractData(ResultSet rs) throws SQLException;
}

Enter fullscreen mode Exit fullscreen mode

Implementation example:


<span>public</span> <span>class</span> <span>RowMapperResultSetExtractor</span><span><</span><span>T</span><span>></span> <span>implements</span> <span>ResultSetExtractor</span><span><</span><span>List</span><span><</span><span>T</span><span>>></span> <span>{</span>
    <span>private</span> <span>final</span> <span>RowMapper</span><span><</span><span>T</span><span>></span> <span>rowMapper</span><span>;</span>
    <span>public</span> <span>RowMapperResultSetExtractor</span><span>(</span><span>RowMapper</span><span><</span><span>T</span><span>></span> <span>rowMapper</span><span>)</span> <span>{</span>
        <span>this</span><span>.</span><span>rowMapper</span> <span>=</span> <span>rowMapper</span><span>;</span>
    <span>}</span>
    <span>@Override</span>
    <span>public</span> <span>List</span><span><</span><span>T</span><span>></span> <span>extractData</span><span>(</span><span>ResultSet</span> <span>rs</span><span>)</span> <span>throws</span> <span>SQLException</span> <span>{</span>
        <span>List</span><span><</span><span>T</span><span>></span> <span>results</span> <span>=</span> <span>new</span> <span>ArrayList</span><span><>();</span>
        <span>int</span> <span>rowNum</span> <span>=</span> <span>0</span><span>;</span>
        <span>while</span> <span>(</span><span>rs</span><span>.</span><span>next</span><span>())</span> <span>{</span>
            <span>results</span><span>.</span><span>add</span><span>(</span><span>rowMapper</span><span>.</span><span>mapRow</span><span>(</span><span>rs</span><span>,</span> <span>++</span><span>rowNum</span><span>));</span>
        <span>}</span>
        <span>return</span> <span>results</span><span>;</span>
    <span>}</span>
<span>}</span>
<span>public</span> <span>class</span> <span>RowMapperResultSetExtractor</span><span><</span><span>T</span><span>></span> <span>implements</span> <span>ResultSetExtractor</span><span><</span><span>List</span><span><</span><span>T</span><span>>></span> <span>{</span>
    <span>private</span> <span>final</span> <span>RowMapper</span><span><</span><span>T</span><span>></span> <span>rowMapper</span><span>;</span>

    <span>public</span> <span>RowMapperResultSetExtractor</span><span>(</span><span>RowMapper</span><span><</span><span>T</span><span>></span> <span>rowMapper</span><span>)</span> <span>{</span>
        <span>this</span><span>.</span><span>rowMapper</span> <span>=</span> <span>rowMapper</span><span>;</span>
    <span>}</span>

    <span>@Override</span>
    <span>public</span> <span>List</span><span><</span><span>T</span><span>></span> <span>extractData</span><span>(</span><span>ResultSet</span> <span>rs</span><span>)</span> <span>throws</span> <span>SQLException</span> <span>{</span>
        <span>List</span><span><</span><span>T</span><span>></span> <span>results</span> <span>=</span> <span>new</span> <span>ArrayList</span><span><>();</span>
        <span>int</span> <span>rowNum</span> <span>=</span> <span>0</span><span>;</span>
        <span>while</span> <span>(</span><span>rs</span><span>.</span><span>next</span><span>())</span> <span>{</span>
            <span>results</span><span>.</span><span>add</span><span>(</span><span>rowMapper</span><span>.</span><span>mapRow</span><span>(</span><span>rs</span><span>,</span> <span>++</span><span>rowNum</span><span>));</span>
        <span>}</span>
        <span>return</span> <span>results</span><span>;</span>
    <span>}</span>
<span>}</span>
public class RowMapperResultSetExtractor<T> implements ResultSetExtractor<List<T>> {
    private final RowMapper<T> rowMapper;

    public RowMapperResultSetExtractor(RowMapper<T> rowMapper) {
        this.rowMapper = rowMapper;
    }

    @Override
    public List<T> extractData(ResultSet rs) throws SQLException {
        List<T> results = new ArrayList<>();
        int rowNum = 0;
        while (rs.next()) {
            results.add(rowMapper.mapRow(rs, ++rowNum));
        }
        return results;
    }
}

Enter fullscreen mode Exit fullscreen mode

Usage Example

1. Simple Query


<span>List</span><span><</span><span>User</span><span>></span> <span>users</span> <span>=</span> <span>jdbcTemplate</span><span>.</span><span>query</span><span>(</span>
    <span>"SELECT * FROM users WHERE age > ?"</span><span>,</span>
    <span>new</span> <span>Object</span><span>[]{</span><span>18</span><span>},</span>
    <span>new</span> <span>UserRowMapper</span><span>()</span>
<span>);</span>
<span>List</span><span><</span><span>User</span><span>></span> <span>users</span> <span>=</span> <span>jdbcTemplate</span><span>.</span><span>query</span><span>(</span>
    <span>"SELECT * FROM users WHERE age > ?"</span><span>,</span>
    <span>new</span> <span>Object</span><span>[]{</span><span>18</span><span>},</span>
    <span>new</span> <span>UserRowMapper</span><span>()</span>
<span>);</span>
List<User> users = jdbcTemplate.query(
    "SELECT * FROM users WHERE age > ?",
    new Object[]{18},
    new UserRowMapper()
);

Enter fullscreen mode Exit fullscreen mode

2. Custom Statement Processing


<span>Object</span> <span>result</span> <span>=</span> <span>jdbcTemplate</span><span>.</span><span>query</span><span>(</span><span>new</span> <span>StatementCallBack</span><span>()</span> <span>{</span>
    <span>@Override</span>
    <span>public</span> <span>Object</span> <span>doInStatement</span><span>(</span><span>Statement</span> <span>stmt</span><span>)</span> <span>throws</span> <span>SQLException</span> <span>{</span>
        <span>ResultSet</span> <span>rs</span> <span>=</span> <span>stmt</span><span>.</span><span>executeQuery</span><span>(</span><span>"SELECT COUNT(*) FROM users"</span><span>);</span>
        <span>if</span> <span>(</span><span>rs</span><span>.</span><span>next</span><span>())</span> <span>{</span>
            <span>return</span> <span>rs</span><span>.</span><span>getInt</span><span>(</span><span>1</span><span>);</span>
        <span>}</span>
        <span>return</span> <span>0</span><span>;</span>
    <span>}</span>
<span>});</span>
<span>Object</span> <span>result</span> <span>=</span> <span>jdbcTemplate</span><span>.</span><span>query</span><span>(</span><span>new</span> <span>StatementCallBack</span><span>()</span> <span>{</span>
    <span>@Override</span>
    <span>public</span> <span>Object</span> <span>doInStatement</span><span>(</span><span>Statement</span> <span>stmt</span><span>)</span> <span>throws</span> <span>SQLException</span> <span>{</span>
        <span>ResultSet</span> <span>rs</span> <span>=</span> <span>stmt</span><span>.</span><span>executeQuery</span><span>(</span><span>"SELECT COUNT(*) FROM users"</span><span>);</span>
        <span>if</span> <span>(</span><span>rs</span><span>.</span><span>next</span><span>())</span> <span>{</span>
            <span>return</span> <span>rs</span><span>.</span><span>getInt</span><span>(</span><span>1</span><span>);</span>
        <span>}</span>
        <span>return</span> <span>0</span><span>;</span>
    <span>}</span>
<span>});</span>
Object result = jdbcTemplate.query(new StatementCallBack() {
    @Override
    public Object doInStatement(Statement stmt) throws SQLException {
        ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM users");
        if (rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }
});

Enter fullscreen mode Exit fullscreen mode

3. Prepared Statement with Callback


<span>Object</span> <span>result</span> <span>=</span> <span>jdbcTemplate</span><span>.</span><span>query</span><span>(</span>
    <span>"UPDATE users SET name = ? WHERE id = ?"</span><span>,</span>
    <span>new</span> <span>Object</span><span>[]{</span><span>"John"</span><span>,</span> <span>1</span><span>},</span>
    <span>new</span> <span>PreparedStatementCallBack</span><span>()</span> <span>{</span>
        <span>@Override</span>
        <span>public</span> <span>Object</span> <span>doInPreparedStatement</span><span>(</span><span>PreparedStatement</span> <span>pstmt</span><span>)</span> 
                <span>throws</span> <span>SQLException</span> <span>{</span>
            <span>return</span> <span>pstmt</span><span>.</span><span>executeUpdate</span><span>();</span>
        <span>}</span>
    <span>}</span>
<span>);</span>
<span>Object</span> <span>result</span> <span>=</span> <span>jdbcTemplate</span><span>.</span><span>query</span><span>(</span>
    <span>"UPDATE users SET name = ? WHERE id = ?"</span><span>,</span>
    <span>new</span> <span>Object</span><span>[]{</span><span>"John"</span><span>,</span> <span>1</span><span>},</span>
    <span>new</span> <span>PreparedStatementCallBack</span><span>()</span> <span>{</span>
        <span>@Override</span>
        <span>public</span> <span>Object</span> <span>doInPreparedStatement</span><span>(</span><span>PreparedStatement</span> <span>pstmt</span><span>)</span> 
                <span>throws</span> <span>SQLException</span> <span>{</span>
            <span>return</span> <span>pstmt</span><span>.</span><span>executeUpdate</span><span>();</span>
        <span>}</span>
    <span>}</span>
<span>);</span>
Object result = jdbcTemplate.query(
    "UPDATE users SET name = ? WHERE id = ?",
    new Object[]{"John", 1},
    new PreparedStatementCallBack() {
        @Override
        public Object doInPreparedStatement(PreparedStatement pstmt) 
                throws SQLException {
            return pstmt.executeUpdate();
        }
    }
);

Enter fullscreen mode Exit fullscreen mode

Key Features

1. Resource Management

Automatic connection handling
Statement cleanup
Exception handling ### 2. Type Safety
Generic row mapping
Type-safe parameter binding
Result type conversion ### 3. Flexibility
Custom statement processing
Extensible row mapping
Configurable result extraction ### 4. Error Handling
SQL exception wrapping
Resource cleanup
Transaction support

Best Practices

1. Connection Management

Use connection pooling
Proper resource cleanup
Transaction boundaries ### 2. Exception Handling
Custom exception types
Proper error propagation
Resource cleanup in finally blocks ### 3. Performance Optimization
Statement caching
Batch processing
Connection pooling

Common Challenges and Solutions

1. Resource Leaks

Use try-with-resources
Proper cleanup in finally blocks
Connection pooling ### 2. Type Conversion
Implement type handlers
Use prepared statements
Handle null values ### 3. Transaction Management
Spring transaction integration
Proper isolation levels
Rollback handling

Conclusion

Implementing a JDBC module provides:

Simplified database access
Type-safe operations
Resource management
Error handling Key takeaways:
Understanding JDBC fundamentals
Resource management patterns
Type safety in database operations
Performance optimization techniques

This implementation demonstrates how to create a robust database access layer while maintaining simplicity and flexibility.

原文链接：Spring Architecture Series-6.Implementing JDBC Module in Spring

展开阅读全文

© 版权声明

文章版权声明 1、本网站名称：拾光赋
2、本站永久网址：https://www.blogs.ink
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长QQ：805375623进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END

Java（EN）
# java # Spring

喜欢就支持一下吧

Nobody can go back and start a new beginning, but anyone can start today and make a new ending.

没有人可以回到过去从头再来，但是每个人都可以从今天开始，创造一个全新的结局

评论抢沙发

请登录后发表评论

暂无评论内容