Spring对JDBC的最佳实践--上

转x33g5p2x  于2022-05-05 转载在 Spring  
字(26.3k)|赞(0)|评价(0)|浏览(885)

引子

在一开始，会介绍一下原生JDBC的使用，此时不会涉及到spring的使用，但是我们还是利用yml文件来存放数据源配置信息，因此我们需要一个yaml读取的工具类

  1. <dependency>
  2.             <groupId>org.yaml</groupId>
  3.             <artifactId>snakeyaml</artifactId>
  4.             <version>1.26</version>
  5.         </dependency>

工具类:

  1. package org.TX;
  3. import lombok.SneakyThrows;
  4. import lombok.extern.slf4j.Slf4j;
  5. import org.yaml.snakeyaml.Yaml;
  6. import org.yaml.snakeyaml.error.YAMLException;
  8. import java.io.File;
  9. import java.io.FileInputStream;
  10. import java.io.FileNotFoundException;
  11. import java.util.Map;
  12. import java.util.concurrent.ConcurrentHashMap;
  14. /**
  15.  * @author 大忽悠
  16.  * @create 2022/4/28 17:09
  17.  */
  18. @Slf4j
  19. public class YamlUtil {
  20.     private Yaml yaml;
  21.     private Map<String, Map<String, Object>> yamlContent;
  22.     private Map<String, String> resCache;
  23.     private final String ymlFilePath;
  24.     private final String KEY_DELIMITER = "\\.";
  26.     public YamlUtil(String ymlFilePath) {
  27.         this.ymlFilePath = ymlFilePath;
  28.     }
  30.     @SneakyThrows
  31.     public String get(String key) {
  32.         if (resCache != null && resCache.containsKey(key)) {
  33.             return resCache.get(key);
  34.         }
  35.         //懒加载
  36.         if (yaml == null) {
  37.             //初始化yaml
  38.             initYaml();
  39.         }
  40.         //查询,放入缓存
  41.         return queryAndPutCache(key);
  42.     }
  44.     private void initYaml() throws FileNotFoundException {
  45.         try {
  46.             yaml=new Yaml();
  47.             yamlContent = yaml.load(YamlUtil.class.getClassLoader().getResourceAsStream(ymlFilePath));
  48.         } catch (YAMLException yamlException) {
  49.             //尝试去文件系统中定位yaml文件
  50.             File file = new File(ymlFilePath);
  51.             if(!file.exists()){
  52.                 throw new YAMLException("classPath和文件系统中无法找到名为" + ymlFilePath + "的文件");
  53.             }
  54.             yamlContent=yaml.load(new FileInputStream(file));
  55.         }
  56.     }
  58.     private String queryAndPutCache(String key) {
  59.         String[] keys = key.split(KEY_DELIMITER);
  60.         String value = extractValue(keys, yamlContent, 0);
  61.         if (resCache == null) {
  62.             resCache = new ConcurrentHashMap<>();
  63.         }
  64.         resCache.put(key, "value");
  65.         return value;
  66.     }
  68.     private String extractValue(String[] keys, Map<String, Map<String, Object>> yamlContent, int index) {
  69.         if (index == keys.length) {
  70.             return null;
  71.         }
  72.         Object valueMap = yamlContent.get(keys[index]);
  73.         if (valueMap == null || !(valueMap instanceof Map)) {
  74.             return valueMap instanceof String ? (String) valueMap:valueMap.toString();
  75.         }
  76.         return extractValue(keys, (Map<String, Map<String, Object>>) valueMap, index + 1);
  77.     }
  78. }

基于Template的JDBC使用方式

JDBC的尴尬

首先JDBC的api设计偏向于底层化发展，因此对于开发者而言，使用起来会有大量的雷同重复代码产生。

并且烦人的资源关闭问题，也让人头大不止。

JDBC知识点回顾教程

简单的jdbc工具类一览:

  1. public class JdbcUtil {
  2.     private final String yamlFilePath;
  3.     private Connection connection;
  4.     private Statement statement;
  6.     public JdbcUtil(String yamlFilePath) {
  7.         this.yamlFilePath = yamlFilePath;
  8.     }
  10.     private synchronized Connection getConn() throws SQLException {
  11.         if(connection==null){
  12.             YamlUtil yamlUtil=new YamlUtil(yamlFilePath);
  13.             String url = yamlUtil.get("spring.datasource.url");
  14.             String username = yamlUtil.get("spring.datasource.username");
  15.             String password = yamlUtil.get("spring.datasource.password");
  16.             String driveClassName = yamlUtil.get("spring.datasource.driver-class-name");
  17.             connection = DriverManager.getConnection(url,username,password);   
  18.         }
  19.        return connection;
  20.    }
  22.    private synchronized Statement createStatement() throws SQLException {
  23.         if(statement==null){
  24.             Connection connection = getConn();
  25.             statement=connection.createStatement();   
  26.         }
  27.        return statement;
  28.    }
  30.    public int updateOrInsertOrDelete(String sql) throws SQLException {
  31.         return createStatement().executeUpdate(sql);
  32.    }
  34.    public void close(){
  35.         if(statement!=null){
  36.             try {
  37.                 statement.close();
  38.             } catch (SQLException e) {
  39.                 e.printStackTrace();
  40.             }
  41.         }
  42.         if(connection!=null){
  43.             try {
  44.                 connection.close();
  45.             } catch (SQLException e) {
  46.                 e.printStackTrace();
  47.             }
  48.         }
  49.     }
  50. }

可以看出，jdbc有非常多的重复性代码需要封装，这一点通过我们简单的封装一个jdbc工具类就可以看出，因此我们需要一个足够方便的，对jdbc的工具类，来简化我们对jdbc的操作，此时Spring的JdbcTemplate就完成了这件事情。

JdbcTemplate的诞生

JdbcTemplate的演化

模板方法模式与CallBack之间的关系

CallBack接口与模板方法类之间的关系可以看做是服务于被服务的关系，模板方法类想要CallBack做事，就要提供相应的资源。

CallBack使用提供的资源做事，完事之后，模板方法来处理公开的资源，CallBack接口不需要关心这些。

因为一般模板方法过程中需要变化的方法一般都是抽象的，因此当前模板类也是抽象的，这样一来就需要实现非常多的子类，如果想避免这种情况，最好的方法就是将需要变化的方法以回调接口的形式公开。

JDBC继承结构

  1. @Nullable
  2. 	private <T> T execute(StatementCallback<T> action, boolean closeResources) throws DataAccessException {
  3. 		Assert.notNull(action, "Callback object must not be null");
  4.         
  5. 		Connection con = DataSourceUtils.getConnection(obtainDataSource());
  6. 		Statement stmt = null;
  7. 		try {
  8. 			stmt = con.createStatement();
  9. 			applyStatementSettings(stmt);
  10. 			//回调接口被调用
  11. 			T result = action.doInStatement(stmt);
  12. 			handleWarnings(stmt);
  13. 			return result;
  14. 		}
  15. 		catch (SQLException ex) {
  16. 			// Release Connection early, to avoid potential connection pool deadlock
  17. 			// in the case when the exception translator hasn't been initialized yet.
  18. 			String sql = getSql(action);
  19. 			JdbcUtils.closeStatement(stmt);
  20. 			stmt = null;
  21. 			DataSourceUtils.releaseConnection(con, getDataSource());
  22. 			con = null;
  23. 			throw translateException("StatementCallback", sql, ex);
  24. 		}
  25. 		finally {
  26. 			if (closeResources) {
  27. 				JdbcUtils.closeStatement(stmt);
  28. 				DataSourceUtils.releaseConnection(con, getDataSource());
  29. 			}
  30. 		}
  31. 	}

重载execute方法:

  1. @Override
  2. 	public void execute(final String sql) throws DataAccessException {
  3. 		if (logger.isDebugEnabled()) {
  4. 			logger.debug("Executing SQL statement [" + sql + "]");
  5. 		}
  7. 		/**
  8. 		 * Callback to execute the statement.
  9. 		 */
  10. 		class ExecuteStatementCallback implements StatementCallback<Object>, SqlProvider {
  11. 			@Override
  12. 			@Nullable
  13. 			public Object doInStatement(Statement stmt) throws SQLException {
  14. 				stmt.execute(sql);
  15. 				return null;
  16. 			}
  17. 			@Override
  18. 			public String getSql() {
  19. 				return sql;
  20. 			}
  21. 		}
  22.         //执行上面给出的那个重载方法，传入回调接口
  23. 		execute(new ExecuteStatementCallback(), true);
  24. 	}
使用DataSourceUtils进行Connection的管理

这里给大家回顾一下JDBC是如何处理事务的，就明白为啥要把Connection绑定到当前线程上去了

  1. public boolean giveMoney(String Giver,String Revicer,int money)  {
  2.         //1.获取连接
  3.         Connection conn= null;
  4.         PreparedStatement pstmt1=null;
  5.         PreparedStatement pstmt2=null;
  6.         try {
  7.             conn = JDBCUtil.getConnection();
  8.             //开启事务
  9.             conn.setAutoCommit(false);
  10.             //2.定义sql
  11.             String sql1="update login set money=money-? where name= ?";
  12.             String sql2="update login set money=money+? where name= ?";
  13.             //3.获取执行sql的对象
  14.                pstmt1=conn.prepareStatement(sql1);
  15.                pstmt2=conn.prepareStatement(sql2);
  16.             //给?赋值
  17.             pstmt1.setInt(1,500);
  18.             pstmt1.setString(2,"大忽悠");
  19.             pstmt2.setInt(1,500);
  20.             pstmt2.setString(2,"小朋友");
  21.             //4.执行
  22.             pstmt1.executeUpdate();
  23.             //异常的出现
  24.             String s=null;
  25.             s.getBytes(StandardCharsets.UTF_8);
  26.             pstmt2.executeUpdate();
  27.             //结束事务
  28.             conn.commit();
  29.             return true;
  30.         } catch (SQLException throwables) {
  31.             //事务进行回滚
  32.             try {
  33.                 if(conn!=null)
  34.                 conn.rollback();
  35.             } catch (SQLException e) {
  36.                 e.printStackTrace();
  37.             }
  38.             throwables.printStackTrace();
  39.         }finally {
  40.             JDBCUtil.close(pstmt1,conn);
  41.             JDBCUtil.close(pstmt2,conn);
  42.         }
  43.         return false;
  44.     }
使用NativeJdbcExtractor来获得"真相"

控制JdbcTemplate的行为

  1. protected void applyStatementSettings(Statement stmt) throws SQLException {
  2. 		int fetchSize = getFetchSize();
  3. 		if (fetchSize != -1) {
  4. 			stmt.setFetchSize(fetchSize);
  5. 		}
  6. 		int maxRows = getMaxRows();
  7. 		if (maxRows != -1) {
  8. 			stmt.setMaxRows(maxRows);
  9. 		}
  10. 		DataSourceUtils.applyTimeout(stmt, getDataSource(), getQueryTimeout());
  11. 	}

SQLException到DataAccessException体系的转义

  1. public interface SQLExceptionTranslator {
  2. 	@Nullable
  3. 	DataAccessException translate(String task, @Nullable String sql, SQLException ex);
  4. }

SQLErrorCodeSQLExceptionTranslator的doTranslate方法:

  1. @Override
  2. 	@Nullable
  3. 	protected DataAccessException doTranslate(String task, @Nullable String sql, SQLException ex) {
  4. 		SQLException sqlEx = ex;
  5. 		//首先对批处理异常进行处理
  6. 		if (sqlEx instanceof BatchUpdateException && sqlEx.getNextException() != null) {
  7. 			SQLException nestedSqlEx = sqlEx.getNextException();
  8. 			if (nestedSqlEx.getErrorCode() > 0 || nestedSqlEx.getSQLState() != null) {
  9. 				sqlEx = nestedSqlEx;
  10. 			}
  11. 		}
  13. 		// First, try custom translation from overridden method.
  14. 		//首先尝试应用自定义异常翻译方法
  15. 		DataAccessException dae = customTranslate(task, sql, sqlEx);
  16. 		//如果返回值不为null,那就自定义异常翻译处理后的结果
  17. 		if (dae != null) {
  18. 			return dae;
  19. 		}
  21. 		// Next, try the custom SQLException translator, if available.
  22. 		//获取sql错误码集合
  23. 		SQLErrorCodes sqlErrorCodes = getSqlErrorCodes();
  24. 		if (sqlErrorCodes != null) {
  25. 		//尝试获取SQLErrorCodes中设置的自定义异常翻译器
  26. 			SQLExceptionTranslator customTranslator = sqlErrorCodes.getCustomSqlExceptionTranslator();
  27. 			if (customTranslator != null) {
  28. 			//如果存在,就直接应用
  29. 				DataAccessException customDex = customTranslator.translate(task, sql, sqlEx);
  30. 				if (customDex != null) {
  31. 					return customDex;
  32. 				}
  33. 			}
  34. 		}
  36. 		// Check SQLErrorCodes with corresponding error code, if available.
  37. 		if (sqlErrorCodes != null) {
  38. 			String errorCode;
  39. 		//使用SqlState来进行异常转义
  40. 			if (sqlErrorCodes.isUseSqlStateForTranslation()) {
  41. 				errorCode = sqlEx.getSQLState();
  42. 			}
  43. 			else {
  44. 				// Try to find SQLException with actual error code, looping through the causes.
  45. 				// E.g. applicable to java.sql.DataTruncation as of JDK 1.6.
  46. 				SQLException current = sqlEx;
  47. 				while (current.getErrorCode() == 0 && current.getCause() instanceof SQLException) {
  48. 					current = (SQLException) current.getCause();
  49. 				}
  50. 				errorCode = Integer.toString(current.getErrorCode());
  51. 			}
  53. 			if (errorCode != null) {
  54. 				// Look for defined custom translations first.
  55. 				CustomSQLErrorCodesTranslation[] customTranslations = sqlErrorCodes.getCustomTranslations();
  56. 				if (customTranslations != null) {
  57. 					for (CustomSQLErrorCodesTranslation customTranslation : customTranslations) {
  58. 						if (Arrays.binarySearch(customTranslation.getErrorCodes(), errorCode) >= 0 &&
  59. 								customTranslation.getExceptionClass() != null) {
  60. 							DataAccessException customException = createCustomException(
  61. 									task, sql, sqlEx, customTranslation.getExceptionClass());
  62. 							if (customException != null) {
  63. 								logTranslation(task, sql, sqlEx, true);
  64. 								return customException;
  65. 							}
  66. 						}
  67. 					}
  68. 				}
  69. 				// Next, look for grouped error codes.
  70. 				if (Arrays.binarySearch(sqlErrorCodes.getBadSqlGrammarCodes(), errorCode) >= 0) {
  71. 					logTranslation(task, sql, sqlEx, false);
  72. 					return new BadSqlGrammarException(task, (sql != null ? sql : ""), sqlEx);
  73. 				}
  74. 				else if (Arrays.binarySearch(sqlErrorCodes.getInvalidResultSetAccessCodes(), errorCode) >= 0) {
  75. 					logTranslation(task, sql, sqlEx, false);
  76. 					return new InvalidResultSetAccessException(task, (sql != null ? sql : ""), sqlEx);
  77. 				}
  78. 				else if (Arrays.binarySearch(sqlErrorCodes.getDuplicateKeyCodes(), errorCode) >= 0) {
  79. 					logTranslation(task, sql, sqlEx, false);
  80. 					return new DuplicateKeyException(buildMessage(task, sql, sqlEx), sqlEx);
  81. 				}
  82. 				else if (Arrays.binarySearch(sqlErrorCodes.getDataIntegrityViolationCodes(), errorCode) >= 0) {
  83. 					logTranslation(task, sql, sqlEx, false);
  84. 					return new DataIntegrityViolationException(buildMessage(task, sql, sqlEx), sqlEx);
  85. 				}
  86. 				else if (Arrays.binarySearch(sqlErrorCodes.getPermissionDeniedCodes(), errorCode) >= 0) {
  87. 					logTranslation(task, sql, sqlEx, false);
  88. 					return new PermissionDeniedDataAccessException(buildMessage(task, sql, sqlEx), sqlEx);
  89. 				}
  90. 				else if (Arrays.binarySearch(sqlErrorCodes.getDataAccessResourceFailureCodes(), errorCode) >= 0) {
  91. 					logTranslation(task, sql, sqlEx, false);
  92. 					return new DataAccessResourceFailureException(buildMessage(task, sql, sqlEx), sqlEx);
  93. 				}
  94. 				else if (Arrays.binarySearch(sqlErrorCodes.getTransientDataAccessResourceCodes(), errorCode) >= 0) {
  95. 					logTranslation(task, sql, sqlEx, false);
  96. 					return new TransientDataAccessResourceException(buildMessage(task, sql, sqlEx), sqlEx);
  97. 				}
  98. 				else if (Arrays.binarySearch(sqlErrorCodes.getCannotAcquireLockCodes(), errorCode) >= 0) {
  99. 					logTranslation(task, sql, sqlEx, false);
  100. 					return new CannotAcquireLockException(buildMessage(task, sql, sqlEx), sqlEx);
  101. 				}
  102. 				else if (Arrays.binarySearch(sqlErrorCodes.getDeadlockLoserCodes(), errorCode) >= 0) {
  103. 					logTranslation(task, sql, sqlEx, false);
  104. 					return new DeadlockLoserDataAccessException(buildMessage(task, sql, sqlEx), sqlEx);
  105. 				}
  106. 				else if (Arrays.binarySearch(sqlErrorCodes.getCannotSerializeTransactionCodes(), errorCode) >= 0) {
  107. 					logTranslation(task, sql, sqlEx, false);
  108. 					return new CannotSerializeTransactionException(buildMessage(task, sql, sqlEx), sqlEx);
  109. 				}
  110. 			}
  111. 		}
  113. 		// We couldn't identify it more precisely - let's hand it over to the SQLState fallback translator.
  114. 		if (logger.isDebugEnabled()) {
  115. 			String codes;
  116. 			if (sqlErrorCodes != null && sqlErrorCodes.isUseSqlStateForTranslation()) {
  117. 				codes = "SQL state '" + sqlEx.getSQLState() + "', error code '" + sqlEx.getErrorCode();
  118. 			}
  119. 			else {
  120. 				codes = "Error code '" + sqlEx.getErrorCode() + "'";
  121. 			}
  122. 			logger.debug("Unable to translate SQLException with " + codes + ", will now try the fallback translator");
  123. 		}
  125. 		return null;
  126. 	}
扩展SQLErrorCodeSQLExceptionTranslator完成自定义异常转义

  1. public class ToySQLExceptionTranslator extends SQLErrorCodeSQLExceptionTranslator {
  2.     @Override
  3.     protected DataAccessException customTranslate(String task, String sql, SQLException sqlEx) {
  4.         if(sqlEx.getErrorCode()==123456){
  5.             String msg = new StringBuilder()
  6.                     .append("出现未知错误,当执行")
  7.                     .append(task)
  8.                     .append(" with sql >")
  9.                     .append(sql)
  10.                     .toString();
  11.             return new UnexpectedDataAccessException(msg,sqlEx);
  12.         }
  13.         return null;
  14.     }
  15. }

提供sql-error-codes.xml自定义配置

  1. public class SQLErrorCodes {
  3. 	@Nullable
  4. 	private String[] databaseProductNames;
  6. 	private boolean useSqlStateForTranslation = false;
  8. 	private String[] badSqlGrammarCodes = new String[0];
  10. 	private String[] invalidResultSetAccessCodes = new String[0];
  12. 	private String[] duplicateKeyCodes = new String[0];
  14. 	private String[] dataIntegrityViolationCodes = new String[0];
  16. 	private String[] permissionDeniedCodes = new String[0];
  18. 	private String[] dataAccessResourceFailureCodes = new String[0];
  20. 	private String[] transientDataAccessResourceCodes = new String[0];
  22. 	private String[] cannotAcquireLockCodes = new String[0];
  24. 	private String[] deadlockLoserCodes = new String[0];
  26. 	private String[] cannotSerializeTransactionCodes = new String[0];
  27. 	....

JDBCTemplate和它的兄弟们

使用JDBCTemplate进行数据访问
初始化JDBCTemplate

  1. public class TxMain {
  2.     public static void main(String[] args) throws SQLException, ClassNotFoundException {
  3.         DataSource dataSource = getDataSource();
  4.         JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
  5.     }
  7.     private static DataSource getDataSource() {
  8.         BasicDataSource basicDataSource = new BasicDataSource();
  9.         YamlUtil yamlUtil = new YamlUtil("application.yml");
  10.         basicDataSource.setDriverClassName(yamlUtil.get("spring.datasource.driver-class-name"));
  11.         basicDataSource.setUrl(yamlUtil.get("spring.datasource.url"));
  12.         basicDataSource.setUsername(yamlUtil.get("spring.datasource.username"));
  13.         basicDataSource.setPassword(yamlUtil.get("spring.datasource.password"));
  14.         return basicDataSource;
  15.     }
  16. }

基于JDBCTemplate的数据访问

三种查询结果处理回调接口使用演示：

  1. public class TxMain {
  2.     public static void main(String[] args) throws SQLException, ClassNotFoundException {
  3.         DataSource dataSource = getDataSource();
  4.         JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
  5.         List<Shopper> shopperList = jdbcTemplate.query("select * from Salers", new ResultSetExtractor<List<Shopper>>() {
  6.             @Override
  7.             public List<Shopper> extractData(ResultSet rs) throws SQLException, DataAccessException {
  8.                 List<Shopper> res = new ArrayList<>();
  9.                 while (rs.next()) {
  10.                     Shopper shopper = Shopper.builder()
  11.                             .sno(rs.getString(1))
  12.                             .sname(rs.getString(2))
  13.                             .status(rs.getString(3))
  14.                             .city(rs.getString(4))
  15.                             .build();
  16.                     res.add(shopper);
  17.                 }
  18.                 return res;
  19.             }
  20.         });
  22.         List<Shopper> shoppers = jdbcTemplate.query("select * from Salers", new RowMapper<Shopper>() {
  23.             @Override
  24.             public Shopper mapRow(ResultSet rs, int rowNum) throws SQLException {
  25.                 return Shopper.builder()
  26.                         .sno(rs.getString(1))
  27.                         .sname(rs.getString(2))
  28.                         .status(rs.getString(3))
  29.                         .city(rs.getString(4))
  30.                         .build();
  31.             }
  32.         });
  34.         List<Shopper> res = new ArrayList<>();
  35.         jdbcTemplate.query("select * from Salers", new RowCallbackHandler() {
  36.             @Override
  37.             public void processRow(ResultSet rs) throws SQLException {
  38.                 res.add(Shopper.builder()
  39.                         .sno(rs.getString(1))
  40.                         .sname(rs.getString(2))
  41.                         .status(rs.getString(3))
  42.                         .city(rs.getString(4))
  43.                         .build());
  44.             }
  45.         });
  46.     }
  48.     private static DataSource getDataSource() {
  49.         BasicDataSource basicDataSource = new BasicDataSource();
  50.         YamlUtil yamlUtil = new YamlUtil("application.yml");
  51.         basicDataSource.setDriverClassName(yamlUtil.get("spring.datasource.driver-class-name"));
  52.         basicDataSource.setUrl(yamlUtil.get("spring.datasource.url"));
  53.         basicDataSource.setUsername(yamlUtil.get("spring.datasource.username"));
  54.         basicDataSource.setPassword(yamlUtil.get("spring.datasource.password"));
  55.         return basicDataSource;
  56.     }
  57. }

基于JDBCTemplate的数据更新

  1. private static void updateCallBack(JdbcTemplate jdbcTemplate) {
  2.         int count = jdbcTemplate.update("update Salers set SNAME= ? where SNO= ?", new PreparedStatementSetter() {
  3.             @Override
  4.             public void setValues(PreparedStatement ps) throws SQLException {
  5.                 ps.setString(1, "大忽悠集团");
  6.                 ps.setString(2, "1");
  7.             }
  8.         });
  9.         System.out.println("影响的行数为: "+count);
  11.         int acceptRows = jdbcTemplate.update(new PreparedStatementCreator() {
  12.             @Override
  13.             public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
  14.                 PreparedStatement ps = con.prepareStatement("update Salers set SNAME= ? where SNO= ?");
  15.                 ps.setString(1, "哈哈哈哈");
  16.                 ps.setString(2, "2");
  17.                 return ps;
  18.             }
  19.         });
  20.         System.out.println("影响的行数为: "+acceptRows);
  21.     }

  1. private static void dropTable(JdbcTemplate jdbcTemplate) {
  2.         jdbcTemplate.execute("drop table temp");
  3.     }
  4.     private static void createTable(JdbcTemplate jdbcTemplate) {
  5.         jdbcTemplate.execute("create table temp(name varchar(20) DEFAULT '无名氏',age int DEFAULT 18)");
  6.     }

批量更新

批量更新演示:

  1. private static void batchUpdate(JdbcTemplate jdbcTemplate) {
  2.         Shopper shopper1 = Shopper.builder().sno("123").sname("123").city("123").status("123").build();
  3.         Shopper shopper2 = Shopper.builder().sno("1234").sname("1234").city("1234").status("1234").build();
  4.         List<Shopper> shoppers = Arrays.asList(shopper1, shopper2);
  5.         jdbcTemplate.batchUpdate("insert into Salers values (?,?,?,?)", new BatchPreparedStatementSetter() {
  6.             @Override
  7.             public void setValues(PreparedStatement ps, int i) throws SQLException {
  8.                 Shopper shopper = shoppers.get(i);
  9.                 ps.setString(1,shopper.sno);
  10.                 ps.setString(2,shopper.sname);
  11.                 ps.setString(3,shopper.status);
  12.                 ps.setString(4,shopper.city);
  13.             }
  15.             @Override
  16.             public int getBatchSize() {
  17.                 return shoppers.size();
  18.             }
  19.         });
  20.     }

调用存储过程

我们有下面这样一个存储过程:

  1. #创建一个存储过程,统计某个表中的记录个数
  2. DELIMITER $
  3. CREATE PROCEDURE CountTable(IN tableName VARCHAR(30),OUT sqlStr VARCHAR(60),OUT num INT)
  4. BEGIN
  6. SET @tableNum=0;
  7. SET @sqlSTR=CONCAT('select count(*) into @tableNum from ',tableName);
  8. PREPARE stmt FROM @sqlStr; #预定义sql
  9. EXECUTE stmt; #执行sql
  10. DEALLOCATE PREPARE stmt;  #释放连接
  12. SET num=@tableNum;
  13. SET sqlStr=@sqlSTR;
  15. END $
  16. DELIMITER ;
  18. #测试
  19. SET @sqlStr="";
  20. SET @num=0;
  21. CALL CountTable("Salers",@sqlStr,@num);
  22. SELECT @sqlStr,@num;

使用JDBCTemplate完成上面存储过程的调用:

  1. private static void executeProcess(JdbcTemplate jdbcTemplate) {
  2.         Map res = jdbcTemplate.execute("CALL CountTable(?,?,?)", new CallableStatementCallback<Map>() {
  3.             @Override
  4.             public Map doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
  5.                 //声明设置in/out参数
  6.                 cs.setString(1, "Salers");
  7.                 cs.registerOutParameter(2, Types.VARCHAR);
  8.                 cs.registerOutParameter(3, Types.INTEGER);
  9.                 //执行存储过程
  10.                 cs.execute();
  11.                 //抽取结果并返回
  12.                 Map map = new HashMap<>();
  13.                 map.put("SqlStr", cs.getString(2));
  14.                 map.put("count", cs.getInt(3));
  15.                 return map;
  16.             }
  17.         });
  18.         System.out.println(res);
  19.     }

递增主键生成策略抽象

  1. public interface DataFieldMaxValueIncrementer {
  2. 	int nextIntValue() throws DataAccessException;
  3. 	long nextLongValue() throws DataAccessException;
  4. 	String nextStringValue() throws DataAccessException;
  5. }

基于独立主键表的DataFieldMaxValueIncrementer

LAST_INSERT_ID()会返回最近执行的insert语句中由AUTO_INCREMENT生成的值。

注意一下几点：

  • 如果最近一条insert语句不涉及到AUTO_INCREMENT字段，也不会影响到LAST_INSERT_ID()的值。
  • insert需要成功插入数据，如果执行失败，LAST_INSERT_ID()不会改变。
  • insert语句如果指定了AUTO_INCREMENT字段的值，LAST_INSERT_ID()返回的值不会改变。
  • insert语句如果一次插入多行数据，则LAST_INSERT_ID()会返回插入的第一条数据的AUTO_INCREMENT值。
  • LAST_INSERT_ID()返回的值，在服务器端是以每个连接为基础保存的，也就是说，对于不同的连接，返回的值是不一样的。

下面我演示一下mysql相关的MySQLMaxValueIncrementer如何获取自增主键值

  1. private static void mySQLPrimaryKey(DataSource dataSource, JdbcTemplate jdbcTemplate) {
  2.         MySQLMaxValueIncrementer incrementer = new MySQLMaxValueIncrementer
  3.                 (dataSource, "Products", "PNO");
  4.         incrementer.setCacheSize(10);
  5.         int update = jdbcTemplate.update("insert into Products values (?,?,?,?)", new Object[]{
  6.                 incrementer.nextLongValue(), "大忽悠", "蓝色", 20
  7.         });
  8.         System.out.println("accept rows = "+ update);
  9.     }

getNextKey就是具体自增主键的获取和生成逻辑:

  1. @Override
  2. 	protected synchronized long getNextKey() throws DataAccessException {
  3. 	//maxID拿到的是PNO+10,而nextID为PNO+1,那么这就是缓存，知道maxID=nextID的时候，说明缓存用完了
  4. 		if (this.maxId == this.nextId) {
  5. 			    ...
  6. 				// Increment the sequence column...
  7. 				String columnName = getColumnName();
  8. 				try {
  9. 				//关键点: 这里getIncrementerName()得到的就是Products,而columnName是PNO，cacheSize=10
  10. 				//因此完整语句就是UPDATE Products SET PNO = LAST_INSERT_ID(PNO + 10) LIMIT 1
  11. 				//该命令就是把当前最小的PNO值加10
  12. 					stmt.executeUpdate("update " + getIncrementerName() + " set " + columnName +
  13. 							" = last_insert_id(" + columnName + " + " + getCacheSize() + ") limit 1");
  14. 				}
  15. 				catch (SQLException ex) {
  16. 					throw new DataAccessResourceFailureException("Could not increment " + columnName + " for " +
  17. 							getIncrementerName() + " sequence table", ex);
  18. 				}
  19. 				// Retrieve the new max of the sequence column...
  20. 				//VALUE_SQL为 "select last_insert_id()"---查询到最后一次修改得到的主键值为PNO+10
  21. 				ResultSet rs = stmt.executeQuery(VALUE_SQL);
  22. 				try {
  23. 					if (!rs.next()) {
  24. 						throw new DataAccessResourceFailureException("last_insert_id() failed after executing an update");
  25. 					}
  26. 					//拿到这个主键值
  27. 					this.maxId = rs.getLong(1);
  28. 				}
  29. 				finally {
  30. 					JdbcUtils.closeResultSet(rs);
  31. 				}
  32. 				//返回的主键id为查询到的PNO+10-10+1，即为PNO+1
  33. 				this.nextId = this.maxId - getCacheSize() + 1;
  34. 			}
  35. 			catch (SQLException ex) {
  36. 				throw new DataAccessResourceFailureException("Could not obtain last_insert_id()", ex);
  37. 			}
  38. 			finally {
  39. 				JdbcUtils.closeStatement(stmt);
  40. 				if (con != null) {
  41. 					if (this.useNewConnection) {
  42. 						try {
  43. 							con.commit();
  44. 							if (mustRestoreAutoCommit) {
  45. 								con.setAutoCommit(true);
  46. 							}
  47. 						}
  48. 						catch (SQLException ignore) {
  49. 							throw new DataAccessResourceFailureException(
  50. 									"Unable to commit new sequence value changes for " + getIncrementerName());
  51. 						}
  52. 						JdbcUtils.closeConnection(con);
  53. 					}
  54. 					else {
  55. 						DataSourceUtils.releaseConnection(con, getDataSource());
  56. 					}
  57. 				}
  58. 			}
  59. 		}
  60. 		else {
  61. 		//走缓存的情况
  62. 			this.nextId++;
  63. 		}
  64. 		return this.nextId;
  65. 	}

MySQLMaxValueIncrementer–Spring MySQL发号器介绍

基于Orcale的Sequence生成策略这里不多讲，感兴趣可以自行了解

Spring中的LOB类型处理

举个例子:

  • 首先准备一张数据库表
  1. create table `img` (
  2. 	`id` double ,
  3. 	`filename` varchar (600),
  4. 	`entity` longblob
  5. );

blob类型可能会因为图片太大而产生溢出，可以改为使用longblob

我们将该目录下面的所有图片保存到数据库中，然后再读取出来，将图片保存到另一个目录下

完整测试源码如下,测试是成功，大家可以自行拷贝尝试:

  1. public class BlobTestMain {
  2.     private static final String INPUT_IMGS_DIR="C:\\Users\\zdh\\Desktop\\数据库实验截图\\实验四";
  3.     private static final String OUTPUT_IMGS_DIR="C:\\Users\\zdh\\Desktop\\数据库实验截图\\testFile\\";
  5.     public static void main(String[] args) throws SQLException, IOException {
  6.         DataSource dataSource = getDataSource();
  7.         Connection conn = dataSource.getConnection();
  8.         //saveImgs(conn);
  9.         readImgs(conn);
  10.     }
  12.     private static void readImgs(Connection conn) throws SQLException, IOException {
  13.         Statement stmt = conn.createStatement();
  14.         ResultSet rs = stmt.executeQuery("select fileName,entity from img");
  15.         while(rs.next()){
  16.             String imgName = rs.getString(1);
  17.             InputStream imgOut = rs.getBinaryStream(2);
  18.             readImg(imgName,imgOut);
  19.         }
  20.         close(conn, stmt, rs);
  21.     }
  23.     private static void readImg(String imgName,InputStream imgOut) throws IOException {
  24.         BufferedInputStream imgBuf = new BufferedInputStream(imgOut);
  25.         PrintStream imgOutPrint = new PrintStream(OUTPUT_IMGS_DIR+imgName);
  26.         byte[] bytes = new byte[1024];
  27.         int len=-1;
  28.         while((len=imgBuf.read(bytes))!=-1){
  29.             imgOutPrint.write(bytes,0,len);
  30.         }
  31.         imgOutPrint.close();
  32.         imgBuf.close();
  33.     }
  34.     private static void close(Connection conn, Statement stmt, ResultSet rs) throws SQLException {
  35.         if(rs !=null){
  36.             rs.close();
  37.         }
  38.         if(stmt !=null){
  39.             stmt.close();
  40.         }
  41.         if(conn !=null){
  42.             conn.close();
  43.         }
  44.     }
  45.     private static void saveImgs(Connection conn) throws SQLException {
  46.         File imgs = getInputFile();
  47.         List<FileInputStream> fileInputs = getFileInputs(imgs);
  48.         File[] files = getFiles(imgs);
  49.         PreparedStatement ps=null;
  50.         for (int i = 0; i < fileInputs.size(); i++) {
  51.             ps = conn.prepareStatement("insert into img(filename,entity) values(?,?)");
  52.             ps.setString(1,files[i].getName());
  53.             ps.setBinaryStream(2,fileInputs.get(i),(int)files[i].length());
  54.             ps.executeUpdate();
  55.         }
  56.         close(conn, fileInputs, ps);
  57.     }
  58.     private static void close(Connection conn, List<FileInputStream> fileInputs, PreparedStatement ps) throws SQLException {
  59.         if(ps !=null){
  60.             ps.close();
  61.         }
  62.         if(conn !=null){
  63.             conn.close();
  64.         }
  65.         fileInputs.forEach(fileInputStream -> {
  66.             try {
  67.                 fileInputStream.close();
  68.             } catch (IOException e) {
  69.                 e.printStackTrace();
  70.             }
  71.         });
  72.     }
  73.     private static File[] getFiles(File imgs) {
  74.         return imgs.listFiles();
  75.     }
  76.     private static List<FileInputStream> getFileInputs(File imgs) {
  77.         return Arrays.stream(imgs.listFiles()).map(file -> {
  78.             try {
  79.                 return new FileInputStream(file);
  80.             } catch (FileNotFoundException e) {
  81.                 e.printStackTrace();
  82.             }
  83.             return null;
  84.         }).collect(Collectors.toList());
  85.     }
  86.     private static File getInputFile() {
  87.         File imgs = new File(INPUT_IMGS_DIR);
  88.         return imgs;
  89.     }
  90.     private static DataSource getDataSource() {
  91.         BasicDataSource basicDataSource = new BasicDataSource();
  92.         YamlUtil yamlUtil = new YamlUtil("application.yml");
  93.         basicDataSource.setDriverClassName(yamlUtil.get("spring.datasource.driver-class-name"));
  94.         basicDataSource.setUrl(yamlUtil.get("spring.datasource.url"));
  95.         basicDataSource.setUsername(yamlUtil.get("spring.datasource.username"));
  96.         basicDataSource.setPassword(yamlUtil.get("spring.datasource.password"));
  97.         return basicDataSource;
  98.     }
  99. }

Oracle数据库的测试大家就自行完成吧，这里不再给出具体案例了

LobHandler

  1. public interface LobHandler {
  2. 	@Nullable
  3. 	byte[] getBlobAsBytes(ResultSet rs, String columnName) throws SQLException;
  4. 	@Nullable
  5. 	byte[] getBlobAsBytes(ResultSet rs, int columnIndex) throws SQLException;
  6. 	@Nullable
  7. 	InputStream getBlobAsBinaryStream(ResultSet rs, String columnName) throws SQLException;
  8. 	@Nullable
  9. 	InputStream getBlobAsBinaryStream(ResultSet rs, int columnIndex) throws SQLException;
  10. 	@Nullable
  11. 	String getClobAsString(ResultSet rs, String columnName) throws SQLException;
  12. 	@Nullable
  13. 	String getClobAsString(ResultSet rs, int columnIndex) throws SQLException;
  14. 	@Nullable
  15. 	InputStream getClobAsAsciiStream(ResultSet rs, String columnName) throws SQLException;
  16. 	@Nullable
  17. 	InputStream getClobAsAsciiStream(ResultSet rs, int columnIndex) throws SQLException;
  18. 	Reader getClobAsCharacterStream(ResultSet rs, String columnName) throws SQLException;
  19. 	Reader getClobAsCharacterStream(ResultSet rs, int columnIndex) throws SQLException;
  20. 	LobCreator getLobCreator();
  21. }

数据插入:

  1. DefaultLobHandler lobHandler = new DefaultLobHandler();
  2.         JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
  3.          jdbcTemplate.execute("insert into img(filename,entity) values(?,?)",
  4.                  new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
  5.                      @SneakyThrows
  6.                      @Override
  7.                      protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException, DataAccessException {
  8.                          ps.setString(1,"大忽悠的图片.png");
  9.                          lobCreator.setBlobAsBinaryStream(ps,2,new FileInputStream(INPUT_IMG),(int)new File(INPUT_IMG).length());
  10.                      }
  11.                  });

数据访问:

  1. final DefaultLobHandler lobHandler = new DefaultLobHandler();
  2.         JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
  3.         InputStream ins = (InputStream) jdbcTemplate.queryForObject("select entity from img where id=1", new RowMapper() {
  4.             @Override
  5.             public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
  6.                 return lobHandler.getBlobAsBinaryStream(rs, 1);
  7.             }
  8.         });

拿到input流，后面做啥，我就不多说了

  1. final DefaultLobHandler lobHandler = new DefaultLobHandler();
  2.         JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
  3.        jdbcTemplate.query("select entity from img where id=1",new AbstractLobStreamingResultSetExtractor() {
  4.            @Override
  5.            protected void streamData(ResultSet rs) throws SQLException, IOException, DataAccessException {
  6.                InputStream ins = lobHandler.getBlobAsBinaryStream(rs, 1);
  7.            }
  8.        });
NamedParameterJdbcTemplate

  1. DataSource dataSource = getDataSource();
  2.         NamedParameterJdbcTemplate npJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
  3.         MapSqlParameterSource parameterSource = new MapSqlParameterSource("filename", "任务19.png");
  4.         List<Integer> res = npJdbcTemplate.query("select count(*) from img where filename=:filename", parameterSource, new RowMapper<Integer>() {
  5.             @Override
  6.             public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
  7.                 return rs.getInt(1);
  8.             }
  9.         });
  10.         System.out.println(res);

SimpleJdbcCTemplate

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖

版权说明 : 本文为转载文章, 版权归原作者所有 版权申明

原文链接 : https://cjdhy.blog.csdn.net/article/details/124463129

内容来源于网络，如有侵权，请联系作者删除！

Javaspring后端spring源码研读

相关文章

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新文章

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com