Презентация на тему 7. Java databases and JDBC 3. JDBC Additional Tasks

for merchants accordingly their parameters: period of payment and

minimal payment sum
Please, create a DB table for saving pay list

*

Victor Mozharsky

(
id int not null generated always

as identity,
merchantId int constraint merchmoney_fk references merchant,
sumSent decimal(19,2),
sentDate timestamp,
status char(1),
primary key (id)
);

*

Victor Mozharsky

WEEKLY, TENDAYS, MONTHLY};
*
Victor Mozharsky

conn) throws SQLException{
Statement stmt = conn.createStatement();

ArrayList list = new ArrayList();
String sql = "select id, period, needToSend, lastSent, minSum from merchant";
ResultSet rs = stmt.executeQuery(sql);

*

Victor Mozharsky

int id;
private java.sql.Date lastSent;
private double sum;
private periodEnum period;
private double

minSum;
public MerchantInfo(){}
// accessors
}

*

Victor Mozharsky

(rs.next()){
MerchantInfo info = new MerchantInfo();

info.setId(rs.getInt("id"));
info.setLastSent(rs.getDate("lastSent"));
info.setPeriod(periodEnum.values()[rs.getInt("period")]);
info.setSum(rs.getDouble("needToSend"));
info.setMinSum(rs.getDouble("minSum"));
list.add(info);
}
return list;
}

*

Victor Mozharsky

list){
ArrayList listRet =

new ArrayList();
for (MerchantInfo info: list){
if (info.getMinSum() > info.getSum()) continue;
Instant instant = Instant.ofEpochMilli(info.getLastSent().getTime());
LocalDate dt = LocalDateTime.ofInstant(instant,
ZoneId.systemDefault()).toLocalDate();
LocalDate current = LocalDate.now();

*

Victor Mozharsky

switch(info.getPeriod()){
case WEEKLY:

if (dt.until(current, ChronoUnit.WEEKS) < 1) continue;
break;
case TENDAYS:
if (dt.until(current, ChronoUnit.DAYS) < 10) continue;
break;
case MONTHLY:
if (dt.until(current, ChronoUnit.MONTHS) < 1) continue;
break;

*

Victor Mozharsky

default:

break;
}
listRet.add(info);
}
return listRet;
}

*

Victor Mozharsky

conn, ArrayList list) throws SQLException{
String sql =

"INSERT INTO transMoney(merchantId, sumSent, sentDate, status) values(?,?,?, '0')";
PreparedStatement stmt = conn.prepareStatement(sql);
for(MerchantInfo info: list){
stmt.setInt(1, info.getId());
stmt.setDouble(2, info.getSum());
java.sql.Timestamp dt = new java.sql.Timestamp(new java.util.Date().getTime());
stmt.setTimestamp(3, dt);
stmt.executeUpdate();
}}

*

Victor Mozharsky

main(String[] args) {
try{
Connection

conn = getConnection();
TransMoney t = new TransMoney();
ArrayList list = t.getMerchantInfo(conn);
list = t.filterList(list);
t.addToTrans(conn, list);
conn.close();
} catch(Exception ex){
System.out.println("Error " + ex.getMessage());
}}

*

Victor Mozharsky

text
*
Victor Mozharsky

accessible transfer sum as a parameter and sends money

to merchants accordingly to the pay list under condition that general transfer sum should not grater then accessible transfer sum.

*

Victor Mozharsky

(
id int not null generated always

as identity,
merchantId int constraint merchmoney_fk references merchant,
sumSent decimal(19,2),
sentDate timestamp,
status char(1),
primary key (id)
);

*

Victor Mozharsky

double sumSent;
private java.sql.Date sentDate;
private String status;

public TransferInfo(){}
// accessors
}
*
Victor Mozharsky

throws SQLException{
Statement stmt = conn.createStatement();
ArrayList

list = new ArrayList();
String sql = "select id, merchantId, sumSent, sentDate, status from transMoney where status='0' order by sentDate, sumSent";
ResultSet rs = stmt.executeQuery(sql);

*

Victor Mozharsky

info = new TransferInfo();
info.setId(rs.getInt("id"));
info.setMerchantId(rs.getInt("merchantId"));

info.setSumSent(rs.getDouble("sumSent"));
info.setSentDate(rs.getDate("sentDate"));
info.setStatus(rs.getString("status"));
list.add(info);
}
return list;
}

*

Victor Mozharsky

ArrayList list, double sum) throws SQLException{

double sentSum = 0.0;
for(TransferInfo info: list){
if (sentSum + info.getSumSent() > sum) continue;
sentSum += info.getSumSent();
try{
conn.setAutoCommit(false);
sendPayment(conn, info);
updateMerchant(conn, info);
conn.commit();

*

Victor Mozharsky

ex.printStackTrace();
conn.rollback();
}

}
}

*

Victor Mozharsky

TransferInfo info) throws SQLException{
String sql =

"UPDATE transMoney set sentDate=?, status='1' where id=?";
PreparedStatement stmt = conn.prepareStatement(sql);
java.sql.Timestamp dt = new java.sql.Timestamp(new java.util.Date().getTime());
stmt.setTimestamp(1, dt);
stmt.setInt(2, info.getId());
stmt.executeUpdate();
}

*

Victor Mozharsky

TransferInfo info) throws SQLException{
String sql = "SELECT

needToSend, sent FROM merchant where id=?";
PreparedStatement stmtRead = conn.prepareStatement(sql);
stmtRead.setInt(1, info.getMerchantId());
ResultSet rs = stmtRead.executeQuery();
rs.next();
double needToSend = rs.getDouble("needToSend");
double sent = rs.getDouble("sent");

*

Victor Mozharsky

"UPDATE merchant set lastSent=?, needToSend=?, sent=? where id=?";

PreparedStatement stmt = conn.prepareStatement(sql);
java.sql.Timestamp dt = new java.sql.Timestamp(new
java.util.Date().getTime());
stmt.setTimestamp(1, dt);
stmt.setDouble(2, needToSend - info.getSumSent());
stmt.setDouble(3, sent + info.getSumSent());
stmt.setInt(4, info.merchantId);
stmt.executeUpdate();
}

*

Victor Mozharsky

args) throws SQLException{
Connection conn = null;
try{

double sum1 = Double.valueOf(args[0]);
conn = getConnection();
MainTrans t = new MainTrans();
ArrayList list = t.getUnpayed(conn);
t.procUnpayed(conn, list, sum1);
}

*

Victor Mozharsky