Spring对JDBC的最佳实践--上
- 引子
- 基于Template的JDBC使用方式
- JDBC的尴尬
- JdbcTemplate的诞生 - JdbcTemplate的演化 - 模板方法模式与CallBack之间的关系 - JDBC继承结构 - 使用DataSourceUtils进行Connection的管理 - 使用NativeJdbcExtractor来获得"真相" - 控制JdbcTemplate的行为 - SQLException到DataAccessException体系的转义 - 扩展SQLErrorCodeSQLExceptionTranslator完成自定义异常转义 - 提供sql-error-codes.xml自定义配置 - JDBCTemplate和它的兄弟们 - 使用JDBCTemplate进行数据访问 - 初始化JDBCTemplate - 基于JDBCTemplate的数据访问 - 基于JDBCTemplate的数据更新 - 批量更新 - 调用存储过程 - 递增主键生成策略抽象 - 基于独立主键表的DataFieldMaxValueIncrementer - Spring中的LOB类型处理 - LobHandler - NamedParameterJdbcTemplate - SimpleJdbcCTemplate
引子
在一开始，会介绍一下原生JDBC的使用，此时不会涉及到spring的使用，但是我们还是利用yml文件来存放数据源配置信息，因此我们需要一个yaml读取的工具类
代码语言：javascript复制 <dependency>
<groupId>org.yaml</groupId>
<artifactId>snakeyaml</artifactId>
<version>1.26</version>
</dependency>工具类:
代码语言：javascript复制package org.TX;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.yaml.snakeyaml.Yaml;
import org.yaml.snakeyaml.error.YAMLException;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
/**
* @author 大忽悠
* @create 2022/4/28 17:09
*/
@Slf4j
public class YamlUtil {
private Yaml yaml;
private Map<String, Map<String, Object>> yamlContent;
private Map<String, String> resCache;
private final String ymlFilePath;
private final String KEY_DELIMITER = "\.";
public YamlUtil(String ymlFilePath) {
this.ymlFilePath = ymlFilePath;
}
@SneakyThrows
public String get(String key) {
if (resCache != null && resCache.containsKey(key)) {
return resCache.get(key);
}
//懒加载
if (yaml == null) {
//初始化yaml
initYaml();
}
//查询,放入缓存
return queryAndPutCache(key);
}
private void initYaml() throws FileNotFoundException {
try {
yaml=new Yaml();
yamlContent = yaml.load(YamlUtil.class.getClassLoader().getResourceAsStream(ymlFilePath));
} catch (YAMLException yamlException) {
//尝试去文件系统中定位yaml文件
File file = new File(ymlFilePath);
if(!file.exists()){
throw new YAMLException("classPath和文件系统中无法找到名为" ymlFilePath "的文件");
}
yamlContent=yaml.load(new FileInputStream(file));
}
}
private String queryAndPutCache(String key) {
String[] keys = key.split(KEY_DELIMITER);
String value = extractValue(keys, yamlContent, 0);
if (resCache == null) {
resCache = new ConcurrentHashMap<>();
}
resCache.put(key, "value");
return value;
}
private String extractValue(String[] keys, Map<String, Map<String, Object>> yamlContent, int index) {
if (index == keys.length) {
return null;
}
Object valueMap = yamlContent.get(keys[index]);
if (valueMap == null || !(valueMap instanceof Map)) {
return valueMap instanceof String ? (String) valueMap:valueMap.toString();
}
return extractValue(keys, (Map<String, Map<String, Object>>) valueMap, index 1);
}
}基于Template的JDBC使用方式
JDBC的尴尬
首先JDBC的api设计偏向于底层化发展，因此对于开发者而言，使用起来会有大量的雷同重复代码产生。
并且烦人的资源关闭问题，也让人头大不止。
JDBC知识点回顾教程
简单的jdbc工具类一览:
代码语言：javascript复制public class JdbcUtil {
private final String yamlFilePath;
private Connection connection;
private Statement statement;
public JdbcUtil(String yamlFilePath) {
this.yamlFilePath = yamlFilePath;
}
private synchronized Connection getConn() throws SQLException {
if(connection==null){
YamlUtil yamlUtil=new YamlUtil(yamlFilePath);
String url = yamlUtil.get("spring.datasource.url");
String username = yamlUtil.get("spring.datasource.username");
String password = yamlUtil.get("spring.datasource.password");
String driveClassName = yamlUtil.get("spring.datasource.driver-class-name");
connection = DriverManager.getConnection(url,username,password);
}
return connection;
}
private synchronized Statement createStatement() throws SQLException {
if(statement==null){
Connection connection = getConn();
statement=connection.createStatement();
}
return statement;
}
public int updateOrInsertOrDelete(String sql) throws SQLException {
return createStatement().executeUpdate(sql);
}
public void close(){
if(statement!=null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}可以看出，jdbc有非常多的重复性代码需要封装，这一点通过我们简单的封装一个jdbc工具类就可以看出，因此我们需要一个足够方便的，对jdbc的工具类，来简化我们对jdbc的操作，此时Spring的JdbcTemplate就完成了这件事情。
JdbcTemplate的诞生
JdbcTemplate的演化
模板方法模式与CallBack之间的关系
CallBack接口与模板方法类之间的关系可以看做是服务于被服务的关系，模板方法类想要CallBack做事，就要提供相应的资源。
CallBack使用提供的资源做事，完事之后，模板方法来处理公开的资源，CallBack接口不需要关心这些。
因为一般模板方法过程中需要变化的方法一般都是抽象的，因此当前模板类也是抽象的，这样一来就需要实现非常多的子类，如果想避免这种情况，最好的方法就是将需要变化的方法以回调接口的形式公开。
JDBC继承结构
@Nullable
private <T> T execute(StatementCallback<T> action, boolean closeResources) throws DataAccessException {
Assert.notNull(action, "Callback object must not be null");
Connection con = DataSourceUtils.getConnection(obtainDataSource());
Statement stmt = null;
try {
stmt = con.createStatement();
applyStatementSettings(stmt);
//回调接口被调用
T result = action.doInStatement(stmt);
handleWarnings(stmt);
return result;
}
catch (SQLException ex) {
// Release Connection early, to avoid potential connection pool deadlock
// in the case when the exception translator hasn't been initialized yet.
String sql = getSql(action);
JdbcUtils.closeStatement(stmt);
stmt = null;
DataSourceUtils.releaseConnection(con, getDataSource());
con = null;
throw translateException("StatementCallback", sql, ex);
}
finally {
if (closeResources) {
JdbcUtils.closeStatement(stmt);
DataSourceUtils.releaseConnection(con, getDataSource());
}
}
}重载execute方法:
代码语言：javascript复制 @Override
public void execute(final String sql) throws DataAccessException {
if (logger.isDebugEnabled()) {
logger.debug("Executing SQL statement [" sql "]");
}
/**
* Callback to execute the statement.
*/
class ExecuteStatementCallback implements StatementCallback<Object>, SqlProvider {
@Override
@Nullable
public Object doInStatement(Statement stmt) throws SQLException {
stmt.execute(sql);
return null;
}
@Override
public String getSql() {
return sql;
}
}
//执行上面给出的那个重载方法，传入回调接口
execute(new ExecuteStatementCallback(), true);
}使用DataSourceUtils进行Connection的管理
这里给大家回顾一下JDBC是如何处理事务的，就明白为啥要把Connection绑定到当前线程上去了
public boolean giveMoney(String Giver,String Revicer,int money) {
//1.获取连接
Connection conn= null;
PreparedStatement pstmt1=null;
PreparedStatement pstmt2=null;
try {
conn = JDBCUtil.getConnection();
//开启事务
conn.setAutoCommit(false);
//2.定义sql
String sql1="update login set money=money-? where name= ?";
String sql2="update login set money=money ? where name= ?";
//3.获取执行sql的对象
pstmt1=conn.prepareStatement(sql1);
pstmt2=conn.prepareStatement(sql2);
//给?赋值
pstmt1.setInt(1,500);
pstmt1.setString(2,"大忽悠");
pstmt2.setInt(1,500);
pstmt2.setString(2,"小朋友");
//4.执行
pstmt1.executeUpdate();
//异常的出现
String s=null;
s.getBytes(StandardCharsets.UTF_8);
pstmt2.executeUpdate();
//结束事务
conn.commit();
return true;
} catch (SQLException throwables) {
//事务进行回滚
try {
if(conn!=null)
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}finally {
JDBCUtil.close(pstmt1,conn);
JDBCUtil.close(pstmt2,conn);
}
return false;
}使用NativeJdbcExtractor来获得"真相"
控制JdbcTemplate的行为
protected void applyStatementSettings(Statement stmt) throws SQLException {
int fetchSize = getFetchSize();
if (fetchSize != -1) {
stmt.setFetchSize(fetchSize);
}
int maxRows = getMaxRows();
if (maxRows != -1) {
stmt.setMaxRows(maxRows);
}
DataSourceUtils.applyTimeout(stmt, getDataSource(), getQueryTimeout());
}
SQLException到DataAccessException体系的转义
public interface SQLExceptionTranslator {
@Nullable
DataAccessException translate(String task, @Nullable String sql, SQLException ex);
}
SQLErrorCodeSQLExceptionTranslator的doTranslate方法:
代码语言：javascript复制 @Override
@Nullable
protected DataAccessException doTranslate(String task, @Nullable String sql, SQLException ex) {
SQLException sqlEx = ex;
//首先对批处理异常进行处理
if (sqlEx instanceof BatchUpdateException && sqlEx.getNextException() != null) {
SQLException nestedSqlEx = sqlEx.getNextException();
if (nestedSqlEx.getErrorCode() > 0 || nestedSqlEx.getSQLState() != null) {
sqlEx = nestedSqlEx;
}
}
// First, try custom translation from overridden method.
//首先尝试应用自定义异常翻译方法
DataAccessException dae = customTranslate(task, sql, sqlEx);
//如果返回值不为null,那就自定义异常翻译处理后的结果
if (dae != null) {
return dae;
}
// Next, try the custom SQLException translator, if available.
//获取sql错误码集合
SQLErrorCodes sqlErrorCodes = getSqlErrorCodes();
if (sqlErrorCodes != null) {
//尝试获取SQLErrorCodes中设置的自定义异常翻译器
SQLExceptionTranslator customTranslator = sqlErrorCodes.getCustomSqlExceptionTranslator();
if (customTranslator != null) {
//如果存在,就直接应用
DataAccessException customDex = customTranslator.translate(task, sql, sqlEx);
if (customDex != null) {
return customDex;
}
}
}
// Check SQLErrorCodes with corresponding error code, if available.
if (sqlErrorCodes != null) {
String errorCode;
//使用SqlState来进行异常转义
if (sqlErrorCodes.isUseSqlStateForTranslation()) {
errorCode = sqlEx.getSQLState();
}
else {
// Try to find SQLException with actual error code, looping through the causes.
// E.g. applicable to java.sql.DataTruncation as of JDK 1.6.
SQLException current = sqlEx;
while (current.getErrorCode() == 0 && current.getCause() instanceof SQLException) {
current = (SQLException) current.getCause();
}
errorCode = Integer.toString(current.getErrorCode());
}
if (errorCode != null) {
// Look for defined custom translations first.
CustomSQLErrorCodesTranslation[] customTranslations = sqlErrorCodes.getCustomTranslations();
if (customTranslations != null) {
for (CustomSQLErrorCodesTranslation customTranslation : customTranslations) {
if (Arrays.binarySearch(customTranslation.getErrorCodes(), errorCode) >= 0 &&
customTranslation.getExceptionClass() != null) {
DataAccessException customException = createCustomException(
task, sql, sqlEx, customTranslation.getExceptionClass());
if (customException != null) {
logTranslation(task, sql, sqlEx, true);
return customException;
}
}
}
}
// Next, look for grouped error codes.
if (Arrays.binarySearch(sqlErrorCodes.getBadSqlGrammarCodes(), errorCode) >= 0) {
logTranslation(task, sql, sqlEx, false);
return new BadSqlGrammarException(task, (sql != null ? sql : ""), sqlEx);
}
else if (Arrays.binarySearch(sqlErrorCodes.getInvalidResultSetAccessCodes(), errorCode) >= 0) {
logTranslation(task, sql, sqlEx, false);
return new InvalidResultSetAccessException(task, (sql != null ? sql : ""), sqlEx);
}
else if (Arrays.binarySearch(sqlErrorCodes.getDuplicateKeyCodes(), errorCode) >= 0) {
logTranslation(task, sql, sqlEx, false);
return new DuplicateKeyException(buildMessage(task, sql, sqlEx), sqlEx);
}
else if (Arrays.binarySearch(sqlErrorCodes.getDataIntegrityViolationCodes(), errorCode) >= 0) {
logTranslation(task, sql, sqlEx, false);
return new DataIntegrityViolationException(buildMessage(task, sql, sqlEx), sqlEx);
}
else if (Arrays.binarySearch(sqlErrorCodes.getPermissionDeniedCodes(), errorCode) >= 0) {
logTranslation(task, sql, sqlEx, false);
return new PermissionDeniedDataAccessException(buildMessage(task, sql, sqlEx), sqlEx);
}
else if (Arrays.binarySearch(sqlErrorCodes.getDataAccessResourceFailureCodes(), errorCode) >= 0) {
logTranslation(task, sql, sqlEx, false);
return new DataAccessResourceFailureException(buildMessage(task, sql, sqlEx), sqlEx);
}
else if (Arrays.binarySearch(sqlErrorCodes.getTransientDataAccessResourceCodes(), errorCode) >= 0) {
logTranslation(task, sql, sqlEx, false);
return new TransientDataAccessResourceException(buildMessage(task, sql, sqlEx), sqlEx);
}
else if (Arrays.binarySearch(sqlErrorCodes.getCannotAcquireLockCodes(), errorCode) >= 0) {
logTranslation(task, sql, sqlEx, false);
return new CannotAcquireLockException(buildMessage(task, sql, sqlEx), sqlEx);
}
else if (Arrays.binarySearch(sqlErrorCodes.getDeadlockLoserCodes(), errorCode) >= 0) {
logTranslation(task, sql, sqlEx, false);
return new DeadlockLoserDataAccessException(buildMessage(task, sql, sqlEx), sqlEx);
}
else if (Arrays.binarySearch(sqlErrorCodes.getCannotSerializeTransactionCodes(), errorCode) >= 0) {
logTranslation(task, sql, sqlEx, false);
return new CannotSerializeTransactionException(buildMessage(task, sql, sqlEx), sqlEx);
}
}
}
// We couldn't identify it more precisely - let's hand it over to the SQLState fallback translator.
if (logger.isDebugEnabled()) {
String codes;
if (sqlErrorCodes != null && sqlErrorCodes.isUseSqlStateForTranslation()) {
codes = "SQL state '" sqlEx.getSQLState() "', error code '" sqlEx.getErrorCode();
}
else {
codes = "Error code '" sqlEx.getErrorCode() "'";
}
logger.debug("Unable to translate SQLException with " codes ", will now try the fallback translator");
}
return null;
}扩展SQLErrorCodeSQLExceptionTranslator完成自定义异常转义
public class ToySQLExceptionTranslator extends SQLErrorCodeSQLExceptionTranslator {
@Override
protected DataAccessException customTranslate(String task, String sql, SQLException sqlEx) {
if(sqlEx.getErrorCode()==123456){
String msg = new StringBuilder()
.append("出现未知错误,当执行")
.append(task)
.append(" with sql >")
.append(sql)
.toString();
return new UnexpectedDataAccessException(msg,sqlEx);
}
return null;
}
}
提供sql-error-codes.xml自定义配置
public class SQLErrorCodes {
@Nullable
private String[] databaseProductNames;
private boolean useSqlStateForTranslation = false;
private String[] badSqlGrammarCodes = new String[0];
private String[] invalidResultSetAccessCodes = new String[0];
private String[] duplicateKeyCodes = new String[0];
private String[] dataIntegrityViolationCodes = new String[0];
private String[] permissionDeniedCodes = new String[0];
private String[] dataAccessResourceFailureCodes = new String[0];
private String[] transientDataAccessResourceCodes = new String[0];
private String[] cannotAcquireLockCodes = new String[0];
private String[] deadlockLoserCodes = new String[0];
private String[] cannotSerializeTransactionCodes = new String[0];
....
JDBCTemplate和它的兄弟们
使用JDBCTemplate进行数据访问
初始化JDBCTemplate
public class TxMain {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
DataSource dataSource = getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
}
private static DataSource getDataSource() {
BasicDataSource basicDataSource = new BasicDataSource();
YamlUtil yamlUtil = new YamlUtil("application.yml");
basicDataSource.setDriverClassName(yamlUtil.get("spring.datasource.driver-class-name"));
basicDataSource.setUrl(yamlUtil.get("spring.datasource.url"));
basicDataSource.setUsername(yamlUtil.get("spring.datasource.username"));
basicDataSource.setPassword(yamlUtil.get("spring.datasource.password"));
return basicDataSource;
}
}
基于JDBCTemplate的数据访问
三种查询结果处理回调接口使用演示：
代码语言：javascript复制public class TxMain {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
DataSource dataSource = getDataSource();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
List<Shopper> shopperList = jdbcTemplate.query("select * from Salers", new ResultSetExtractor<List<Shopper>>() {
@Override
public List<Shopper> extractData(ResultSet rs) throws SQLException, DataAccessException {
List<Shopper> res = new ArrayList<>();
while (rs.next()) {
Shopper shopper = Shopper.builder()
.sno(rs.getString(1))
.sname(rs.getString(2))
.status(rs.getString(3))
.city(rs.getString(4))
.build();
res.add(shopper);
}
return res;
}
});
List<Shopper> shoppers = jdbcTemplate.query("select * from Salers", new RowMapper<Shopper>() {
@Override
public Shopper mapRow(ResultSet rs, int rowNum) throws SQLException {
return Shopper.builder()
.sno(rs.getString(1))
.sname(rs.getString(2))
.status(rs.getString(3))
.city(rs.getString(4))
.build();
}
});
List<Shopper> res = new ArrayList<>();
jdbcTemplate.query("select * from Salers", new RowCallbackHandler() {
@Override
public void processRow(ResultSet rs) throws SQLException {
res.add(Shopper.builder()
.sno(rs.getString(1))
.sname(rs.getString(2))
.status(rs.getString(3))
.city(rs.getString(4))
.build());
}
});
}
private static DataSource getDataSource() {
BasicDataSource basicDataSource = new BasicDataSource();
YamlUtil yamlUtil = new YamlUtil("application.yml");
basicDataSource.setDriverClassName(yamlUtil.get("spring.datasource.driver-class-name"));
basicDataSource.setUrl(yamlUtil.get("spring.datasource.url"));
basicDataSource.setUsername(yamlUtil.get("spring.datasource.username"));
basicDataSource.setPassword(yamlUtil.get("spring.datasource.password"));
return basicDataSource;
}
}
基于JDBCTemplate的数据更新
private static void updateCallBack(JdbcTemplate jdbcTemplate) {
int count = jdbcTemplate.update("update Salers set SNAME= ? where SNO= ?", new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, "大忽悠集团");
ps.setString(2, "1");
}
});
System.out.println("影响的行数为: " count);
int acceptRows = jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps = con.prepareStatement("update Salers set SNAME= ? where SNO= ?");
ps.setString(1, "哈哈哈哈");
ps.setString(2, "2");
return ps;
}
});
System.out.println("影响的行数为: " acceptRows);
}
private static void dropTable(JdbcTemplate jdbcTemplate) {
jdbcTemplate.execute("drop table temp");
}
private static void createTable(JdbcTemplate jdbcTemplate) {
jdbcTemplate.execute("create table temp(name varchar(20) DEFAULT '无名氏',age int DEFAULT 18)");
}
批量更新
批量更新演示:
代码语言：javascript复制 private static void batchUpdate(JdbcTemplate jdbcTemplate) {
Shopper shopper1 = Shopper.builder().sno("123").sname("123").city("123").status("123").build();
Shopper shopper2 = Shopper.builder().sno("1234").sname("1234").city("1234").status("1234").build();
List<Shopper> shoppers = Arrays.asList(shopper1, shopper2);
jdbcTemplate.batchUpdate("insert into Salers values (?,?,?,?)", new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Shopper shopper = shoppers.get(i);
ps.setString(1,shopper.sno);
ps.setString(2,shopper.sname);
ps.setString(3,shopper.status);
ps.setString(4,shopper.city);
}
@Override
public int getBatchSize() {
return shoppers.size();
}
});
}
调用存储过程
我们有下面这样一个存储过程:
代码语言：javascript复制#创建一个存储过程,统计某个表中的记录个数
DELIMITER $
CREATE PROCEDURE CountTable(IN tableName VARCHAR(30),OUT sqlStr VARCHAR(60),OUT num INT)
BEGIN
SET @tableNum=0;
SET @sqlSTR=CONCAT('select count(*) into @tableNum from ',tableName);
PREPARE stmt FROM @sqlStr; #预定义sql
EXECUTE stmt; #执行sql
DEALLOCATE PREPARE stmt; #释放连接
SET num=@tableNum;
SET sqlStr=@sqlSTR;
END $
DELIMITER ;
#测试
SET @sqlStr="";
SET @num=0;
CALL CountTable("Salers",@sqlStr,@num);
SELECT @sqlStr,@num;
使用JDBCTemplate完成上面存储过程的调用:
代码语言：javascript复制 private static void executeProcess(JdbcTemplate jdbcTemplate) {
Map res = jdbcTemplate.execute("CALL CountTable(?,?,?)", new CallableStatementCallback<Map>() {
@Override
public Map doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
//声明设置in/out参数
cs.setString(1, "Salers");
cs.registerOutParameter(2, Types.VARCHAR);
cs.registerOutParameter(3, Types.INTEGER);
//执行存储过程
cs.execute();
//抽取结果并返回
Map map = new HashMap<>();
map.put("SqlStr", cs.getString(2));
map.put("count", cs.getInt(3));
return map;
}
});
System.out.println(res);
}
递增主键生成策略抽象
public interface DataFieldMaxValueIncrementer {
int nextIntValue() throws DataAccessException;
long nextLongValue() throws DataAccessException;
String nextStringValue() throws DataAccessException;
}
基于独立主键表的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如何获取自增主键值
代码语言：javascript复制 private static void mySQLPrimaryKey(DataSource dataSource, JdbcTemplate jdbcTemplate) {
MySQLMaxValueIncrementer incrementer = new MySQLMaxValueIncrementer
(dataSource, "Products", "PNO");
incrementer.setCacheSize(10);
int update = jdbcTemplate.update("insert into Products values (?,?,?,?)", new Object[]{
incrementer.nextLongValue(), "大忽悠", "蓝色", 20
});
System.out.println("accept rows = " update);
}getNextKey就是具体自增主键的获取和生成逻辑:
代码语言：javascript复制 @Override
protected synchronized long getNextKey() throws DataAccessException {
//maxID拿到的是PNO 10,而nextID为PNO 1,那么这就是缓存，知道maxID=nextID的时候，说明缓存用完了
if (this.maxId == this.nextId) {
...
// Increment the sequence column...
String columnName = getColumnName();
try {
//关键点: 这里getIncrementerName()得到的就是Products,而columnName是PNO，cacheSize=10
//因此完整语句就是UPDATE Products SET PNO = LAST_INSERT_ID(PNO 10) LIMIT 1
//该命令就是把当前最小的PNO值加10
stmt.executeUpdate("update " getIncrementerName() " set " columnName
" = last_insert_id(" columnName " " getCacheSize() ") limit 1");
}
catch (SQLException ex) {
throw new DataAccessResourceFailureException("Could not increment " columnName " for "
getIncrementerName() " sequence table", ex);
}
// Retrieve the new max of the sequence column...
//VALUE_SQL为 "select last_insert_id()"---查询到最后一次修改得到的主键值为PNO 10
ResultSet rs = stmt.executeQuery(VALUE_SQL);
try {
if (!rs.next()) {
throw new DataAccessResourceFailureException("last_insert_id() failed after executing an update");
}
//拿到这个主键值
this.maxId = rs.getLong(1);
}
finally {
JdbcUtils.closeResultSet(rs);
}
//返回的主键id为查询到的PNO 10-10 1，即为PNO 1
this.nextId = this.maxId - getCacheSize() 1;
}
catch (SQLException ex) {
throw new DataAccessResourceFailureException("Could not obtain last_insert_id()", ex);
}
finally {
JdbcUtils.closeStatement(stmt);
if (con != null) {
if (this.useNewConnection) {
try {
con.commit();
if (mustRestoreAutoCommit) {
con.setAutoCommit(true);
}
}
catch (SQLException ignore) {
throw new DataAccessResourceFailureException(
"Unable to commit new sequence value changes for " getIncrementerName());
}
JdbcUtils.closeConnection(con);
}
else {
DataSourceUtils.releaseConnection(con, getDataSource());
}
}
}
}
else {
//走缓存的情况
this.nextId ;
}
return this.nextId;
}MySQLMaxValueIncrementer–Spring MySQL发号器介绍
基于Orcale的Sequence生成策略这里不多讲，感兴趣可以自行了解
Spring中的LOB类型处理
举个例子:
- 首先准备一张数据库表
create table `img` (
`id` double ,
`filename` varchar (600),
`entity` longblob
); blob类型可能会因为图片太大而产生溢出，可以改为使用longblob
我们将该目录下面的所有图片保存到数据库中，然后再读取出来，将图片保存到另一个目录下
完整测试源码如下,测试是成功，大家可以自行拷贝尝试:
代码语言：javascript复制public class BlobTestMain {
private static final String INPUT_IMGS_DIR="C:\Users\zdh\Desktop\数据库实验截图\实验四";
private static final String OUTPUT_IMGS_DIR="C:\Users\zdh\Desktop\数据库实验截图\testFile\";
public static void main(String[] args) throws SQLException, IOException {
DataSource dataSource = getDataSource();
Connection conn = dataSource.getConnection();
//saveImgs(conn);
readImgs(conn);
}
private static void readImgs(Connection conn) throws SQLException, IOException {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select fileName,entity from img");
while(rs.next()){
String imgName = rs.getString(1);
InputStream imgOut = rs.getBinaryStream(2);
readImg(imgName,imgOut);
}
close(conn, stmt, rs);
}
private static void readImg(String imgName,InputStream imgOut) throws IOException {
BufferedInputStream imgBuf = new BufferedInputStream(imgOut);
PrintStream imgOutPrint = new PrintStream(OUTPUT_IMGS_DIR imgName);
byte[] bytes = new byte[1024];
int len=-1;
while((len=imgBuf.read(bytes))!=-1){
imgOutPrint.write(bytes,0,len);
}
imgOutPrint.close();
imgBuf.close();
}
private static void close(Connection conn, Statement stmt, ResultSet rs) throws SQLException {
if(rs !=null){
rs.close();
}
if(stmt !=null){
stmt.close();
}
if(conn !=null){
conn.close();
}
}
private static void saveImgs(Connection conn) throws SQLException {
File imgs = getInputFile();
List<FileInputStream> fileInputs = getFileInputs(imgs);
File[] files = getFiles(imgs);
PreparedStatement ps=null;
for (int i = 0; i < fileInputs.size(); i ) {
ps = conn.prepareStatement("insert into img(filename,entity) values(?,?)");
ps.setString(1,files[i].getName());
ps.setBinaryStream(2,fileInputs.get(i),(int)files[i].length());
ps.executeUpdate();
}
close(conn, fileInputs, ps);
}
private static void close(Connection conn, List<FileInputStream> fileInputs, PreparedStatement ps) throws SQLException {
if(ps !=null){
ps.close();
}
if(conn !=null){
conn.close();
}
fileInputs.forEach(fileInputStream -> {
try {
fileInputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
});
}
private static File[] getFiles(File imgs) {
return imgs.listFiles();
}
private static List<FileInputStream> getFileInputs(File imgs) {
return Arrays.stream(imgs.listFiles()).map(file -> {
try {
return new FileInputStream(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
return null;
}).collect(Collectors.toList());
}
private static File getInputFile() {
File imgs = new File(INPUT_IMGS_DIR);
return imgs;
}
private static DataSource getDataSource() {
BasicDataSource basicDataSource = new BasicDataSource();
YamlUtil yamlUtil = new YamlUtil("application.yml");
basicDataSource.setDriverClassName(yamlUtil.get("spring.datasource.driver-class-name"));
basicDataSource.setUrl(yamlUtil.get("spring.datasource.url"));
basicDataSource.setUsername(yamlUtil.get("spring.datasource.username"));
basicDataSource.setPassword(yamlUtil.get("spring.datasource.password"));
return basicDataSource;
}
}
Oracle数据库的测试大家就自行完成吧，这里不再给出具体案例了
LobHandler
public interface LobHandler {
@Nullable
byte[] getBlobAsBytes(ResultSet rs, String columnName) throws SQLException;
@Nullable
byte[] getBlobAsBytes(ResultSet rs, int columnIndex) throws SQLException;
@Nullable
InputStream getBlobAsBinaryStream(ResultSet rs, String columnName) throws SQLException;
@Nullable
InputStream getBlobAsBinaryStream(ResultSet rs, int columnIndex) throws SQLException;
@Nullable
String getClobAsString(ResultSet rs, String columnName) throws SQLException;
@Nullable
String getClobAsString(ResultSet rs, int columnIndex) throws SQLException;
@Nullable
InputStream getClobAsAsciiStream(ResultSet rs, String columnName) throws SQLException;
@Nullable
InputStream getClobAsAsciiStream(ResultSet rs, int columnIndex) throws SQLException;
Reader getClobAsCharacterStream(ResultSet rs, String columnName) throws SQLException;
Reader getClobAsCharacterStream(ResultSet rs, int columnIndex) throws SQLException;
LobCreator getLobCreator();
}
数据插入:
代码语言：javascript复制 DefaultLobHandler lobHandler = new DefaultLobHandler();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.execute("insert into img(filename,entity) values(?,?)",
new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
@SneakyThrows
@Override
protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException, DataAccessException {
ps.setString(1,"大忽悠的图片.png");
lobCreator.setBlobAsBinaryStream(ps,2,new FileInputStream(INPUT_IMG),(int)new File(INPUT_IMG).length());
}
});数据访问:
代码语言：javascript复制 final DefaultLobHandler lobHandler = new DefaultLobHandler();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
InputStream ins = (InputStream) jdbcTemplate.queryForObject("select entity from img where id=1", new RowMapper() {
@Override
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
return lobHandler.getBlobAsBinaryStream(rs, 1);
}
});拿到input流，后面做啥，我就不多说了
final DefaultLobHandler lobHandler = new DefaultLobHandler();
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
jdbcTemplate.query("select entity from img where id=1",new AbstractLobStreamingResultSetExtractor() {
@Override
protected void streamData(ResultSet rs) throws SQLException, IOException, DataAccessException {
InputStream ins = lobHandler.getBlobAsBinaryStream(rs, 1);
}
});NamedParameterJdbcTemplate
DataSource dataSource = getDataSource();
NamedParameterJdbcTemplate npJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
MapSqlParameterSource parameterSource = new MapSqlParameterSource("filename", "任务19.png");
List<Integer> res = npJdbcTemplate.query("select count(*) from img where filename=:filename", parameterSource, new RowMapper<Integer>() {
@Override
public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
return rs.getInt(1);
}
});
System.out.println(res);
SimpleJdbcCTemplate
