Spring jdbcTemplate示例

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