JDBC代码学习

1.jdbc

org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations

官网地址：

https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcOperations.html

todo：读一下类的注释&常见的方法

org.springframework.jdbc.core.JdbcTemplate#batchUpdate(java.lang.String, org.springframework.jdbc.core.BatchPreparedStatementSetter)
  
  
//如果jdbc引擎不支持批量更新，会变成单条更新
public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
		if (logger.isDebugEnabled()) {
			logger.debug("Executing SQL batch update [" + sql + "]");
		}

		int[] result = execute(sql, (PreparedStatementCallback<int[]>) ps -> {
			try {
				int batchSize = pss.getBatchSize();
				InterruptibleBatchPreparedStatementSetter ipss =
						(pss instanceof InterruptibleBatchPreparedStatementSetter ?
						(InterruptibleBatchPreparedStatementSetter) pss : null);
				if (JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
					for (int i = 0; i < batchSize; i++) {
						pss.setValues(ps, i);
						if (ipss != null && ipss.isBatchExhausted(i)) {
							break;
						}
						ps.addBatch();
					}
					return ps.executeBatch();
				}
				else {
					List<Integer> rowsAffected = new ArrayList<>();
					for (int i = 0; i < batchSize; i++) {
						pss.setValues(ps, i);
						if (ipss != null && ipss.isBatchExhausted(i)) {
							break;
						}
						rowsAffected.add(ps.executeUpdate());
					}
					int[] rowsAffectedArray = new int[rowsAffected.size()];
					for (int i = 0; i < rowsAffectedArray.length; i++) {
						rowsAffectedArray[i] = rowsAffected.get(i);
					}
					return rowsAffectedArray;
				}
			}
			finally {
				if (pss instanceof ParameterDisposer) {
					((ParameterDisposer) pss).cleanupParameters();
				}
			}
		});

		Assert.state(result != null, "No result array");
		return result;
	}

疑问

插入时

on duplicate key update set a = :a
#values() function的区别
on duplicate key update set a = values(a)

1.1 org.springframework.jdbc.core.namedparam.MapSqlParameterSource

这个类是用来描述什么模型，有什么方法，使用

• 描述什么模型

  /**
   * {@link SqlParameterSource} implementation that holds a given Map of parameters.
   *
   * <p>This class is intended for passing in a simple Map of parameter values
   * to the methods of the {@link NamedParameterJdbcTemplate} class.
   *
   * <p>The {@code addValue} methods on this class will make adding several values
   * easier. The methods return a reference to the {@link MapSqlParameterSource}
   * itself, so you can chain several method calls together within a single statement.
   *
   * @author Thomas Risberg
   * @author Juergen Hoeller
   * @since 2.0
   * @see #addValue(String, Object)
   * @see #addValue(String, Object, int)
   * @see #registerSqlType
   * @see NamedParameterJdbcTemplate
   */
  public class MapSqlParameterSource extends AbstractSqlParameterSource {
  

  中文解释：这个类的目的是向{@link NamedParameterJdbcTemplate}类的方法传递一个简单的参数值映射。这个类上的{@code addValue}方法将使添加几个值更容易。这些方法返回对{@link MapSqlParameterSource}本身的引用，因此您可以将几个方法调用链接在一条语句中。

• 有什么方法

  <details>
  </details>
  public class MapSqlParameterSource extends AbstractSqlParameterSource {
    /**
     * 重要属性，这个类重要就是对一个LinkedHashMap进行的操作，往里面塞值&取值
     */
  	private final Map<String, Object> values = new LinkedHashMap<>();
  
  
  	/**
  	 * Create an empty MapSqlParameterSource,
  	 * with values to be added via {@code addValue}.
  	 * @see #addValue(String, Object)
  	 * 创建一个空的MapSqlParameterSource
  	 */
  	public MapSqlParameterSource() {
  	}
  
  	/**
  	 * Create a new MapSqlParameterSource, with one value
  	 * comprised of the supplied arguments.
  	 * @param paramName the name of the parameter
  	 * @param value the value of the parameter
  	 * @see #addValue(String, Object)
  	 */
  	public MapSqlParameterSource(String paramName, @Nullable Object value) {
  		addValue(paramName, value);
  	}
  
  	/**
  	 * Create a new MapSqlParameterSource based on a Map.
  	 * @param values a Map holding existing parameter values (can be {@code null})
  	 */
  	public MapSqlParameterSource(@Nullable Map<String, ?> values) {
  		addValues(values);
  	}
  
  
  	/**
  	 * Add a parameter to this parameter source.
  	 * @param paramName the name of the parameter
  	 * @param value the value of the parameter
  	 * @return a reference to this parameter source,
  	 * so it's possible to chain several calls together
  	 */
  	public MapSqlParameterSource addValue(String paramName, @Nullable Object value) {
  		Assert.notNull(paramName, "Parameter name must not be null");
  		this.values.put(paramName, value);
  		if (value instanceof SqlParameterValue) {
  			registerSqlType(paramName, ((SqlParameterValue) value).getSqlType());
  		}
  		return this;
  	}
  
  	/**
  	 * Add a parameter to this parameter source.
  	 * @param paramName the name of the parameter
  	 * @param value the value of the parameter
  	 * @param sqlType the SQL type of the parameter
  	 * @return a reference to this parameter source,
  	 * so it's possible to chain several calls together
  	 */
  	public MapSqlParameterSource addValue(String paramName, @Nullable Object value, int sqlType) {
  		Assert.notNull(paramName, "Parameter name must not be null");
  		this.values.put(paramName, value);
  		registerSqlType(paramName, sqlType);
  		return this;
  	}
  
  	/**
  	 * Add a parameter to this parameter source.
  	 * @param paramName the name of the parameter
  	 * @param value the value of the parameter
  	 * @param sqlType the SQL type of the parameter
  	 * @param typeName the type name of the parameter
  	 * @return a reference to this parameter source,
  	 * so it's possible to chain several calls together
  	 */
  	public MapSqlParameterSource addValue(String paramName, @Nullable Object value, int sqlType, String typeName) {
  		Assert.notNull(paramName, "Parameter name must not be null");
  		this.values.put(paramName, value);
  		registerSqlType(paramName, sqlType);
  		registerTypeName(paramName, typeName);
  		return this;
  	}
  
  	/**
  	 * Add a Map of parameters to this parameter source.
  	 * @param values a Map holding existing parameter values (can be {@code null})
  	 * @return a reference to this parameter source,
  	 * so it's possible to chain several calls together
  	 */
  	public MapSqlParameterSource addValues(@Nullable Map<String, ?> values) {
  		if (values != null) {
  			values.forEach((key, value) -> {
  				this.values.put(key, value);
  				if (value instanceof SqlParameterValue) {
  					registerSqlType(key, ((SqlParameterValue) value).getSqlType());
  				}
  			});
  		}
  		return this;
  	}
  
  	/**
  	 * Expose the current parameter values as read-only Map.
  	 */
  	public Map<String, Object> getValues() {
  		return Collections.unmodifiableMap(this.values);
  	}
  
  
  	@Override
  	public boolean hasValue(String paramName) {
  		return this.values.containsKey(paramName);
  	}
  
  	@Override
  	@Nullable
  	public Object getValue(String paramName) {
  		if (!hasValue(paramName)) {
  			throw new IllegalArgumentException("No value registered for key '" + paramName + "'");
  		}
  		return this.values.get(paramName);
  	}
  
  	@Override
  	@Nullable
  	public String[] getParameterNames() {
  		return StringUtils.toStringArray(this.values.keySet());
  	}
  
  }
  

  • 重要属性，这个类重要就是对一个LinkedHashMap进行的操作，往里面塞值&取值

  private final Map<String, Object> values = new LinkedHashMap<>();
  

  • 重要方法

    addValue

    /**
    	 * Add a parameter to this parameter source.
    	 * @param paramName the name of the parameter
    	 * @param value the value of the parameter
    	 * @return a reference to this parameter source,
    	 * so it's possible to chain several calls together
    	 */
    	public MapSqlParameterSource addValue(String paramName, @Nullable Object value) {
        //断言paramName不为null，如果为null，抛出异常java.lang.IllegalArgumentExceptionjava.lang.IllegalArgumentException
    		Assert.notNull(paramName, "Parameter name must not be null");
        //把key&value放入map values中
    		this.values.put(paramName, value);
    		if (value instanceof SqlParameterValue) {
    			registerSqlType(paramName, ((SqlParameterValue) value).getSqlType());
    		}
        //返回MapSqlParameterSource
    		return this;
    	}
    

    getValue

    	public Map<String, Object> getValues() {
    		return Collections.unmodifiableMap(this.values);
    	}
    	@Override
    	@Nullable
    	public Object getValue(String paramName) {
        //如果map里没有这个key会抛出异常
    		if (!hasValue(paramName)) {
    			throw new IllegalArgumentException("No value registered for key '" + paramName + "'");
    		}
    		return this.values.get(paramName);
    	}
    

• 如何使用

  //1.new一个MapSqlParameterSource对象然后add
  MapSqlParameterSource param = new MapSqlParameterSource().add("key1", "value1");
  

1.2 org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations

这个类是用来描述什么模型，有什么方法，使用

• 描述什么模型

  • 我们的项目里用的是它的实现类：org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate

  /**
   * Interface specifying a basic set of JDBC operations allowing the use
   * of named parameters rather than the traditional '?' placeholders.
   *
   * <p>This is an alternative to the classic
   * {@link org.springframework.jdbc.core.JdbcOperations} interface,
   * implemented by {@link NamedParameterJdbcTemplate}. This interface is not
   * often used directly, but provides a useful option to enhance testability,
   * as it can easily be mocked or stubbed.
   *
   * @author Thomas Risberg
   * @author Juergen Hoeller
   * @since 2.0
   * @see NamedParameterJdbcTemplate
   * @see org.springframework.jdbc.core.JdbcOperations
   */
  public interface NamedParameterJdbcOperations {
    
    /**
   * Template class with a basic set of JDBC operations, allowing the use
   * of named parameters rather than traditional '?' placeholders.
   *
   * <p>This class delegates to a wrapped {@link #getJdbcOperations() JdbcTemplate}
   * once the substitution from named parameters to JDBC style '?' placeholders is
   * done at execution time. It also allows for expanding a {@link java.util.List}
   * of values to the appropriate number of placeholders.
   *
   * <p>The underlying {@link org.springframework.jdbc.core.JdbcTemplate} is
   * exposed to allow for convenient access to the traditional
   * {@link org.springframework.jdbc.core.JdbcTemplate} methods.
   *
   * <p><b>NOTE: An instance of this class is thread-safe once configured.</b>
   *
   * @author Thomas Risberg
   * @author Juergen Hoeller
   * @since 2.0
   * @see NamedParameterJdbcOperations
   * @see org.springframework.jdbc.core.JdbcTemplate
   */
  public class NamedParameterJdbcTemplate implements NamedParameterJdbcOperations {
  

  中文翻译：接口，指定一组基本的JDBC操作，允许使用命名参数，而不是传统的’?的占位符。这是经典的{@link org.springframework.jdbc.core的替代方案。JdbcOperations}接口，由{@link NamedParameterJdbcTemplate}实现。这个接口通常不直接使用，但是提供了一个增强可测试性的有用选项，因为它很容易被模仿或存根。

  中文翻译：一旦从命名参数替换为JDBC样式’?'占位符在执行时完成。它还允许扩展{@link java.util。List}中的值指定适当数量的占位符。

• 有什么方法

  • execute

    执行JDBC数据访问操作，实现为JDBC PreparedStatement上的回调操作。这允许在Spring的托管JDBC环境中对单个语句实现任意的数据访问操作:也就是说，参与Spring的托管事务并将JDBC SQLExceptions转换为Spring的DataAccessException层次结构。回调操作可以返回一个结果对象，例如域对象或域对象的集合。

  	/**
  	 * Execute a JDBC data access operation, implemented as callback action
  	 * working on a JDBC PreparedStatement. This allows for implementing arbitrary
  	 * data access operations on a single Statement, within Spring's managed
  	 * JDBC environment: that is, participating in Spring-managed transactions
  	 * and converting JDBC SQLExceptions into Spring's DataAccessException hierarchy.
  	 * <p>The callback action can return a result object, for example a
  	 * domain object or a collection of domain objects.
  	 * @param sql the SQL to execute
  	 * @param paramSource container of arguments to bind to the query
  	 * @param action callback object that specifies the action
  	 * @return a result object returned by the action, or {@code null}
  	 * @throws DataAccessException if there is any problem
  	 */
  	@Nullable
  	<T> T execute(String sql, SqlParameterSource paramSource, PreparedStatementCallback<T> action)
  			throws DataAccessException;