Spring对JDBC的最佳实践--上

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

引子

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

<dependency>
            <groupId>org.yaml</groupId>
            <artifactId>snakeyaml</artifactId>
            <version>1.26</version>
        </dependency>

工具类:

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工具类一览:

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方法:

@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方法:

@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的数据访问

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

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)");
    }

批量更新

批量更新演示:

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();
            }
        });
    }

调用存储过程

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

#创建一个存储过程,统计某个表中的记录个数
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完成上面存储过程的调用:

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如何获取自增主键值

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就是具体自增主键的获取和生成逻辑:

@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

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

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

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();
}

数据插入:

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());
                     }
                 });

数据访问:

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

创作打卡挑战赛

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

相关文章