jdbctemplate查询语句

1 .这段代码用于查询回来一个list结果集：

public class BasModAuditHistoryRowMapper implements RowMapper<BasModAuditHistory> {

	@Override
	public BasModAuditHistory mapRow(ResultSet rs, int rowNum) throws SQLException {
		final BasModAuditHistory basModAuditHistory = new BasModAuditHistory();

		basModAuditHistory.setServiceId(rs.getLong("service_id"));

		return basModAuditHistory;
	}

}

public List<BasModAuditHistory> getOverridesDetail(final ServiceOrder serviceOrder) {
		final String sql = "select * from bas_mod_audit_history where service_id=?";

		final List<BasModAuditHistory> basModAuditHistorys = jdbcTemplate.query(sql,
				new Object[] { serviceOrder.getServiceId() }, new BasModAuditHistoryRowMapper());

		if (CollectionUtils.isNullOrEmpty(basModAuditHistorys)) {
			return Collections.emptyList();
		}
		return basModAuditHistorys;
	}

完整的就是

	public List<LedgerDetail> listLedgerDetails() {
		final String sql = "select * from ledger_detail";

		final List<LedgerDetail> ledgerDetails = ledgerDetailJdbcTemplate.query(sql, (查询语句中有条件的时候就要写上new Object[]{?,?} ,)new RowMapper<LedgerDetail>() {

			@Override
			public LedgerDetail mapRow(final ResultSet rs, final int rowNum) throws SQLException {
				LedgerDetail ledgerDetail = new LedgerDetail();
				ledgerDetail.setCreateByUser(rs.getString("create_by_user"));
				return ledgerDetail;
			}
		});

		if (CollectionUtils.isNullOrEmpty(ledgerDetails)) {
			return Collections.emptyList();
		}
		return ledgerDetails;
	}

2. 批量插入和更改Update

	public void insertMerchantDBData(final List<Merchant> merchants) {
		final String INSERT_MERCHANT_SQL = "insert into merchant ("
				+ "merchant_id, payment_processor_id, cid_mid, settlement_time, "
				+ "create_date, create_by, create_by_user, update_date, update_by,"
				+ " update_by_user) VALUES (?,?,?,?,?,?,?,?,?,?)";

		jdbcTemplate.batchUpdate(INSERT_MERCHANT_SQL, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(final PreparedStatement ps, final int i) throws SQLException {
				final Merchant merchant = merchants.get(i);
				DAOSecurityMethods.safeSetLong(ps, 1, merchant.getMerchantId());
				DAOSecurityMethods.safeSetBigDecimal(ps, 2, merchant.getPaymentProcessorId());
				DAOSecurityMethods.safeSetString(ps, 3, merchant.getCidMid());
				DAOSecurityMethods.safeSetTimestamp(ps, 4, merchant.getSettlementTime());
				DAOSecurityMethods.safeSetTimestamp(ps, 5, merchant.getCreateDate());
				DAOSecurityMethods.safeSetString(ps, 6, merchant.getCreateBy());
				DAOSecurityMethods.safeSetString(ps, 7, merchant.getCreateByUser());
				DAOSecurityMethods.safeSetTimestamp(ps, 8, merchant.getUpdateDate());
				DAOSecurityMethods.safeSetString(ps, 9, merchant.getUpdateBy());
				DAOSecurityMethods.safeSetString(ps, 10, merchant.getUpdateByUser());
			}

			@Override
			public int getBatchSize() {
				return merchants.size();
			}
		});
	}

这个里边的DAOSecurityMethods

public final class DAOSecurityMethods {
	private DAOSecurityMethods() {
	}
	public static void safeSetString(final PreparedStatement ps, final int index, final String value)
			throws SQLException {
		if (Strings.isNullOrEmpty(value)) {
			ps.setNull(index, Types.VARCHAR);
		} else {
			ps.setString(index, value);
		}
	}
	public static void safeSetLong(final PreparedStatement ps, final int index, final Long value) throws SQLException {
		if (value == null) {
			ps.setNull(index, Types.BIGINT);
		} else {
			ps.setLong(index, value);
		}
	}
	public static void safeSetDate(final PreparedStatement ps, final int index, final Date value) throws SQLException {
		if (value == null) {
			ps.setNull(index, Types.DATE);
		} else {
			ps.setDate(index, new java.sql.Date(value.getTime()));
		}
	}
	public static void safeSetDouble(final PreparedStatement ps, final int index, final Double value)
			throws SQLException {
		if (value == null) {
			ps.setNull(index, Types.DOUBLE);
		} else {
			ps.setDouble(index, value);
		}
	}
	public static void safeSetInt(final PreparedStatement ps, final int index, final int value) throws SQLException {

		ps.setInt(index, value);
	}
	public static void safeSetBoolean(final PreparedStatement ps, final int index, final boolean value)
			throws SQLException {
		ps.setBoolean(index, value);
	}
	public static Timestamp convertToTimestamp(final String timestamp) throws ParseException {
		final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss.SSS", Locale.ENGLISH);
		final Date timeTempDate = dateFormat.parse(timestamp);
		final String timeTempString = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS", Locale.ENGLISH)
				.format(timeTempDate);
		return Timestamp.valueOf(timeTempString);
	}
	public static void safeSetBigDecimal(final PreparedStatement ps, final int index, final BigDecimal bigDecimal)
			throws SQLException {
		if (bigDecimal == null) {
			ps.setNull(index, Types.DECIMAL);
		} else {
			ps.setBigDecimal(index, bigDecimal);
		}
	}
	public static void safeSetTimestamp(final PreparedStatement ps, final int index, final Timestamp timeStamp)
			throws SQLException {
		if (timeStamp == null) {
			ps.setNull(index, Types.TIMESTAMP);
		} else {
			ps.setTimestamp(index, timeStamp);
		}
	}
	public static void safeSetCurrentTimestamp(final PreparedStatement ps, final int index) throws SQLException {
		final TimeZone currentTimeZone = Calendar.getInstance().getTimeZone();
		final TimeZone easternTimeZone = TimeZone.getTimeZone("GMT-5:00");
		final long easternTime = new Date().getTime() + currentTimeZone.getRawOffset() - easternTimeZone.getRawOffset();
		ps.setTimestamp(index, new Timestamp(easternTime));
	}
	public static long safeGetTimestamp(final Timestamp timestamp) {
		if (timestamp == null) {
			return 0;
		} else {
			return timestamp.getTime();
		}
	}
}

删除方法

public void deleteLedgerDetailDBData(JdbcTemplate ledgerDetailJdbcTemplate) {
		final String serviceAttrValSql = "DELETE FROM ledger_detail";
		try {
			ledgerDetailJdbcTemplate.update(serviceAttrValSql);
			LOG.info("clear ledger_detail successful");
		} catch (DataAccessException e) {
			LOG.error("ledger_detail is not exist!");
		}
	}