package rewards.internal.reward;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import rewards.AccountContribution;
import rewards.Dining;
import rewards.RewardConfirmation;
import common.datetime.SimpleDate;
/**
* JDBC implementation of a reward repository that records the result of a reward transaction by inserting a reward
* confirmation record.
*/
@Repository
public class JdbcRewardRepository implements RewardRepository {
private DataSource dataSource;
@Autowired
public JdbcRewardRepository(DataSource dataSource) {
this.dataSource = dataSource;
}
public RewardConfirmation confirmReward(AccountContribution contribution, Dining dining) {
String sql = "insert into T_REWARD (CONFIRMATION_NUMBER, REWARD_AMOUNT, REWARD_DATE, ACCOUNT_NUMBER, DINING_MERCHANT_NUMBER, DINING_DATE, DINING_AMOUNT) values (?, ?, ?, ?, ?, ?, ?)";
Connection conn = null;
PreparedStatement ps = null;
try {
conn = dataSource.getConnection();
ps = conn.prepareStatement(sql);
String confirmationNumber = nextConfirmationNumber();
ps.setString(1, confirmationNumber);
ps.setBigDecimal(2, contribution.getAmount().asBigDecimal());
ps.setDate(3, new Date(SimpleDate.today().inMilliseconds()));
ps.setString(4, contribution.getAccountNumber());
ps.setString(5, dining.getMerchantNumber());
ps.setDate(6, new Date(dining.getDate().inMilliseconds()));
ps.setBigDecimal(7, dining.getAmount().asBigDecimal());
ps.execute();
return new RewardConfirmation(confirmationNumber, contribution);
} catch (SQLException e) {
throw new RuntimeException("SQL exception occured inserting reward record", e);
} finally {
if (ps != null) {
try {
// Close to prevent database cursor exhaustion
ps.close();
} catch (SQLException ex) {
}
}
if (conn != null) {
try {
// Close to prevent database connection exhaustion
conn.close();
} catch (SQLException ex) {
}
}
}
}
private String nextConfirmationNumber() {
String sql = "select next value for S_REWARD_CONFIRMATION_NUMBER from DUAL_REWARD_CONFIRMATION_NUMBER";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
rs.next();
return rs.getString(1);
} catch (SQLException e) {
throw new RuntimeException("SQL exception getting next confirmation number", e);
} finally {
if (rs != null) {
try {
// Close to prevent database cursor exhaustion
rs.close();
} catch (SQLException ex) {
}
}
if (ps != null) {
try {
// Close to prevent database cursor exhaustion
ps.close();
} catch (SQLException ex) {
}
}
if (conn != null) {
try {
// Close to prevent database connection exhaustion
conn.close();
} catch (SQLException ex) {
}
}
}
}
}