Spring jdbcTemplate示例
程序员文章站
2022-04-25 08:51:37
...
Spring jdbcTemplate示例
1. 单个插入
2. 批量插入
3. 单个更新
4. 单个删除
5. 批量删除
6. 查询（返回单个对象）
7. 查询（返回集合）
8. 查询（返回基本数据类型）
9. 查询（返回 String）
10. 调用存储过程
11. 批量插入--每次n条
12. 批量更新--每次n条
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import oracle.jdbc.OracleTypes;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
// 1. 单个插入
public int saveImageCharacters(HwCharacter po) throws Exception {
StringBuffer sql = new StringBuffer(100);
sql.append("INSERT INTO HW_CHARACTER(CHARACTER_ID, IMAGE_ID, CH, ");
sql.append("CREATE_TIME) VALUES(?, ?, ?, ?)");
Object[] params = new Object[] { po.getCharacterId(), po.getImageId(),
po.getCh(), po.getCreateTime() };
return getJdbcTemplate().update(sql.toString(), params);
}
// 2. 批量插入
public int batchSave(final List<HwDept> list) throws Exception {
StringBuffer sql = new StringBuffer(150);
sql.append("INSERT INTO HW_DEPT(");
sql.append("DEPT_ID, DEPT_NAME, DEPT_LEVEL, MANAGE_LEVEL, PARENT_ID");
sql.append(")");
sql.append("VALUES(?, ?, ?, ?, ?)");
return getJdbcTemplate().batchUpdate(sql.toString(),
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i)
throws SQLException {
ps.setString(1, list.get(i).getDeptId());
ps.setString(2, list.get(i).getDeptName());
ps.setString(3, list.get(i).getDeptLevel());
ps.setString(4, list.get(i).getManageLevel());
ps.setString(5, list.get(i).getParentId());
}
@Override
public int getBatchSize() {
return list.size();
}
}).length;
}
// 3. 更新
public int updateImageCharacters(String ch, String characterId,
String imageId) throws Exception {
StringBuffer sql = new StringBuffer(200);
sql.append("UPDATE HW_CHARACTER SET CH = ? ");
sql.append("WHERE CHARACTER_ID = ? AND IMAGE_ID = ?");
Object[] params = new Object[] { ch, characterId, imageId };
return getJdbcTemplate().update(sql.toString(), params);
}
// 4. 单个删除
public int deleteUserRoleByUserId(String userId) throws Exception {
String sql = "DELETE FROM SS_USER_ROLE WHERE USER_ID = ?";
return getJdbcTemplate().update(sql, new Object[] { userId });
}
// 5. 批量删除
public int batchDelete(final List<String> idList) throws Exception {
String sql = "DELETE FROM HW_DIAGNOSE_RULE_CUST WHERE CUST_RULE_ID = ?";
int[] res = getJdbcTemplate().batchUpdate(sql,
new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i)
throws SQLException {
ps.setString(1, idList.get(i));
}
@Override
public int getBatchSize() {
return idList.size();
}
});
return res.length;
}
// 6. 查询（返回单个对象）
public HwInstrument findById(String id) throws Exception {
String sql = "SELECT * FROM HW_INSTRUMENT WHERE INSTRUMENT_ID = ?";
final HwInstrument po = new HwInstrument();
getJdbcTemplate().query(sql.toString(), new Object[] { id },
new RowCallbackHandler() {
@Override
public void processRow(ResultSet rs) throws SQLException {
po.setInstrumentId(rs.getString("INSTRUMENT_ID"));
po.setInstrumentModel(rs.getString("INSTRUMENT_MODEL"));
po.setInstrumentNo(rs.getString("INSTRUMENT_NO"));
po.setInstrumentName(rs.getString("INSTRUMENT_NAME"));
po.setFactory(rs.getString("FACTORY"));
po.setCreateTime(rs.getTimestamp("CREATE_TIME"));
}
});
return po;
}
// 7. 查询（返回集合）
public List<HwUseLimit> findUseLimit() throws SQLException,
DataAccessException, Exception {
StringBuffer sql = new StringBuffer(100);
sql.append("SELECT LIMIT_ID, IS_LIMIT, LIMIT_DATE, USE_NOS ");
sql.append(" FROM HW_USE_LIMIT");
List<HwUseLimit> list = new ArrayList<HwUseLimit>(10);
list = getJdbcTemplate().query(sql.toString(),
new RowMapper<HwUseLimit>() {
@Override
public HwUseLimit mapRow(ResultSet rs, int index)
throws SQLException {
HwUseLimit ul = new HwUseLimit();
ul.setLimitId(rs.getString("LIMIT_ID"));
ul.setIsLimit(rs.getString("IS_LIMIT"));
ul.setLimitDate(rs.getString("LIMIT_DATE"));
ul.setUseNos(rs.getInt("USE_NOS"));
return ul;
}
});
return list;
}
// 8. 查询（返回基本数据类型）
public int findByLoginName(final String loginName) throws Exception {
int count = getJdbcTemplate().execute(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection conn)
throws SQLException {
String sql = "SELECT COUNT(ID) FROM SS_USER WHERE LOGIN_NAME = ?";
return conn.prepareStatement(sql);
}
}, new PreparedStatementCallback<Integer>() {
@Override
public Integer doInPreparedStatement(PreparedStatement pstmt)
throws SQLException, DataAccessException {
pstmt.setString(1, loginName);
pstmt.execute();
ResultSet rs = pstmt.getResultSet();
rs.next();
return rs.getInt(1);
}
});
return count;
}
// 9. 查询（返回 String）
public String getCharacterByImageId(String imageId) throws Exception {
String sql = "SELECT CH FROM HW_CHARACTER WHERE IMAGE_ID = ?";
Object[] params = new Object[] { imageId };
try {
return getJdbcTemplate().queryForObject(sql, params, String.class);
} catch (EmptyResultDataAccessException e) {
// EmptyResultDataAccessException : 防止返回空值时报异常
return "";
}
}
10. 调用存储过程
public String saveNewDatas() throws Exception {
log.info("Into BusBarSectionDAOImpl.saveNewDatas()");
// 调用存储过程
String sql = "{CALL P_COMPARE_BUSBARSECTION(?)}";
Object obj = this.jdbcTemplate.execute(sql,
new CallableStatementCallback<String>() {
@Override
public String doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
// 设置出参
cs.registerOutParameter(1, OracleTypes.VARCHAR);
cs.execute();
// 获取存储过程返回值
return cs.getString(1);
}
});
log.info("Out of BusBarSectionDAOImpl.saveNewDatas()");
return (String) obj;
}
11. 批量插入--每次n条
public int batchSaveNew(final List<BusBarSectionPO> list) throws Exception {
log.info("Into BusBarSectionDAOImpl.batchSaveNew(list)");
StringBuffer sql = new StringBuffer(200);
sql.append("INSERT INTO T_BUSBARSECTION_NEW(");
sql.append("ID, CIMID, NAME, X, Y, HEALTH_SITUATION, INODE, V_VALUE, ");
sql.append("V_QUALITY, A_VALUE, A_QUALITY, BAYID, VOLTAGELEVEL_ID, ");
sql.append("PATHNAME) ");
sql.append(" VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
sql.append("");
int count = this.jdbcTemplate.execute(sql.toString(),
new PreparedStatementCallback<Integer>() {
public Integer doInPreparedStatement(PreparedStatement ps)
throws SQLException, DataAccessException {
int length = list.size();
Connection conn = null;
try {
conn = ps.getConnection();
conn.setAutoCommit(false);
BusBarSectionPO po = null;
for (int i = 0; i < list.size(); i++) {
po = list.get(i);
ps.setString(1, MethodsUtils.getPKValue());
ps.setString(2, po.getCimId());
ps.setString(3, po.getName());
ps.setInt(4, po.getX());
ps.setInt(5, po.getY());
ps.setInt(6, po.getHealthSituation());
ps.setString(7, po.getiNode());
ps.setDouble(8, po.getvValue());
ps.setInt(9, po.getvQuality());
ps.setDouble(10, po.getaValue());
ps.setInt(11, po.getaQuality());
ps.setString(12, po.getBayId());
ps.setString(13, po.getVoltagelevelId());
ps.setString(14, po.getPathName());
ps.addBatch();
// 1000条记录insert一次
if (i % 1000 == 0) {
ps.executeBatch();
conn.commit();
}
}
// 最后insert剩余的数据
ps.executeBatch();
conn.commit();
return length;
} catch (SQLException e) {
log.error(
"Batch save BusBarSectionNew by cimId fail:"
+ e.getMessage(), e);
// 数据回滚
MethodsUtils.connectionRollback(conn);
throw e;
} catch (DataAccessException e) {
log.error(
"Batch save BusBarSectionNew by cimId fail:"
+ e.getMessage(), e);
// 数据回滚
MethodsUtils.connectionRollback(conn);
throw e;
} finally {
if (null != conn) {
// 如果用<aop:config> 来控制事务，需要把下面一行注掉，否则会报错
conn.setAutoCommit(true);
}
}
}
});
return count;
12. 批量更新--每次n条
public int batchUpdateByCimId(final List<BusBarSectionPO> list)
throws Exception {
log.info("Into BusBarSectionDAOImpl.batchUpdateByCimId(list)");
if (null == list || list.isEmpty()) {
log.info("Batch update BusBarSection by cimId,the list is empty.");
return 0;
}
StringBuffer sql = new StringBuffer(200);
sql.append("UPDATE T_BUSBARSECTION SET ");
sql.append("NAME = ?, X = ?, Y = ?, HEALTH_SITUATION = ?, ");
sql.append("INODE = ?, V_VALUE = ?, V_QUALITY = ?, A_VALUE = ?, ");
sql.append("A_QUALITY = ?, BAYID = ?, VOLTAGELEVEL_ID = ?, ");
sql.append("PATHNAME = ? ");
sql.append(" WHERE CIMID = ?");
int count = this.jdbcTemplate.execute(sql.toString(),
new PreparedStatementCallback<Integer>() {
public Integer doInPreparedStatement(PreparedStatement ps)
throws SQLException, DataAccessException {
int length = list.size();
Connection conn = null;
try {
conn = ps.getConnection();
conn.setAutoCommit(false);
BusBarSectionPO po = null;
for (int i = 0; i < list.size(); i++) {
po = list.get(i);
ps.setString(1, po.getName());
ps.setInt(2, po.getX());
ps.setInt(3, po.getY());
ps.setInt(4, po.getHealthSituation());
ps.setString(5, po.getiNode());
ps.setDouble(6, po.getvValue());
ps.setInt(7, po.getvQuality());
ps.setDouble(8, po.getaValue());
ps.setInt(9, po.getaQuality());
ps.setString(10, po.getBayId());
ps.setString(11, po.getVoltagelevelId());
ps.setString(12, po.getPathName());
ps.setString(13, po.getCimId());
ps.addBatch();
// 1000条记录更新一次
if (i % 1000 == 0) {
ps.executeBatch();
conn.commit();
}
}
// 最后更新剩余的数据
ps.executeBatch();
conn.commit();
return length;
} catch (SQLException e) {
log.error(
"Batch update BusBarSection by cimId fail:"
+ e.getMessage(), e);
// 数据回滚
MethodsUtils.connectionRollback(conn);
throw e;
} catch (DataAccessException e) {
log.error(
"Batch update BusBarSection by cimId fail:"
+ e.getMessage(), e);
// 数据回滚
MethodsUtils.connectionRollback(conn);
throw e;
} finally {
if (null != conn) {
// 如果用<aop:config> 来控制事务，需要把下面一行注掉，否则会报错
conn.setAutoCommit(true);
}
}
}
});
return count;
}
下一篇： 什么是 Java ？