7. Java databases and JDBC 3. JDBC Additional Tasks

Июль 22, 2022

Главная
Информатика
7. Java databases and JDBC 3. JDBC Additional Tasks

Содержание

2. 1. Pay List You should create a pay list for merchants accordingly their parameters: period of
3. 1. Pay List – DB Structure create table transMoney ( id int not null generated always
4. 1. Pay List – periodEnum public enum periodEnum {UNKNOWN, WEEKLY, TENDAYS, MONTHLY}; * Victor Mozharsky
5. 1. Pay List – getMerchantInfo Method public ArrayList getMerchantInfo(Connection conn) throws SQLException{ Statement stmt = conn.createStatement();
6. 1. Pay List – MerchantInfo Inner Class class MerchantInfo{ private int id; private java.sql.Date lastSent; private
7. 1. Pay List – getMerchantInfo Method while (rs.next()){ MerchantInfo info = new MerchantInfo(); info.setId(rs.getInt("id")); info.setLastSent(rs.getDate("lastSent")); info.setPeriod(periodEnum.values()[rs.getInt("period")]);
8. 1. Pay List – filterList Method public ArrayList filterList(ArrayList list){ ArrayList listRet = new ArrayList ();
9. 1. Pay List – filterList Method switch(info.getPeriod()){ case WEEKLY: if (dt.until(current, ChronoUnit.WEEKS) break; case TENDAYS: if
10. 1. Pay List – filterList Method default: break; } listRet.add(info); } return listRet; } * Victor
11. 1. Pay List – addToTrans Method public void addToTrans(Connection conn, ArrayList list) throws SQLException{ String sql
12. 1. Pay List – main Method public static void main(String[] args) { try{ Connection conn =
13. 1. Pay List See 729TransMoney project for the full text * Victor Mozharsky
14. 2. Money Transfer Create a method that gets an accessible transfer sum as a parameter and
15. 2. Money Transfer – transMoney Table create table transMoney ( id int not null generated always
16. TransferInfo Inner Class class TransferInfo{ private int id; private int merchantId; private double sumSent; private java.sql.Date
17. 2. Money Transfer - getUnpayed public ArrayList getUnpayed(Connection conn) throws SQLException{ Statement stmt = conn.createStatement(); ArrayList
18. 2. Money Transfer - getUnpayed while (rs.next()){ TransferInfo info = new TransferInfo(); info.setId(rs.getInt("id")); info.setMerchantId(rs.getInt("merchantId")); info.setSumSent(rs.getDouble("sumSent")); info.setSentDate(rs.getDate("sentDate"));
19. 2. Money Transfer - procUnpayed public void procUnpayed(Connection conn, ArrayList list, double sum) throws SQLException{ double
20. 2. Money Transfer - procUnpayed } catch (Exception ex){ ex.printStackTrace(); conn.rollback(); } } } * Victor
21. 2. Money Transfer - sendPayment public void sendPayment(Connection conn, TransferInfo info) throws SQLException{ String sql =
22. 2. Money Transfer - updateMerchant public void updateMerchant(Connection conn, TransferInfo info) throws SQLException{ String sql =
23. 2. Money Transfer - updateMerchant sql = "UPDATE merchant set lastSent=?, needToSend=?, sent=? where id=?"; PreparedStatement
24. 2. Money Transfer - main public static void main(String[] args) throws SQLException{ Connection conn = null;
26. Скачать презентацию

Слайд 2

1. Pay List
You should create a pay list for merchants accordingly

their parameters: period of payment and minimal payment sum
Please, create a DB table for saving pay list

Victor Mozharsky

Слайд 3

1. Pay List – DB Structure
create table transMoney (
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

Слайд 4

1. Pay List – periodEnum
public enum periodEnum {UNKNOWN, WEEKLY, TENDAYS, MONTHLY};
*
Victor

Mozharsky

Слайд 5

1. Pay List – getMerchantInfo Method
public ArrayList getMerchantInfo(Connection 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

Слайд 6

1. Pay List – MerchantInfo Inner Class
class MerchantInfo{
private int id;
private java.sql.Date

lastSent;
private double sum;
private periodEnum period;
private double minSum;
public MerchantInfo(){}
// accessors
}

Victor Mozharsky

Слайд 7

1. Pay List – getMerchantInfo Method
while (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

Слайд 8

1. Pay List – filterList Method
public ArrayList filterList(ArrayList 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

Слайд 9

1. Pay List – filterList Method
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

Слайд 10

1. Pay List – filterList Method
default:
break;
}
listRet.add(info);

}
return listRet;
}

Victor Mozharsky

Слайд 11

1. Pay List – addToTrans Method
public void addToTrans(Connection 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

Слайд 12

1. Pay List – main Method
public static void 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

Слайд 13

1. Pay List
See 729TransMoney project for the full text
*
Victor Mozharsky

Слайд 14

2. Money Transfer
Create a method that gets an 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

Слайд 15

2. Money Transfer – transMoney Table
create table transMoney (
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

Слайд 16

TransferInfo Inner Class
class TransferInfo{
private int id;
private int merchantId;
private double sumSent;
private java.sql.Date

sentDate;
private String status;
public TransferInfo(){}
// accessors
}

Victor Mozharsky

Слайд 17

2. Money Transfer - getUnpayed
public ArrayList getUnpayed(Connection conn) 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

Слайд 18

2. Money Transfer - getUnpayed
while (rs.next()){
TransferInfo 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

Слайд 19

2. Money Transfer - procUnpayed
public void procUnpayed(Connection conn,
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

Слайд 20

2. Money Transfer - procUnpayed
} catch (Exception ex){
ex.printStackTrace();
conn.rollback();
}
}
}
*
Victor

Mozharsky

Слайд 21

2. Money Transfer - sendPayment
public void sendPayment(Connection conn, 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

Слайд 22

2. Money Transfer - updateMerchant
public void updateMerchant(Connection conn, 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

Слайд 23

2. Money Transfer - updateMerchant
sql = "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

Слайд 24

2. Money Transfer - main
public static void main(String[] 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

7. Java databases and JDBC 3. JDBC Additional Tasks

Содержание

1. Pay ListYou should create a pay list for merchants accordingly

1. Pay List – DB Structurecreate table transMoney ( id

1. Pay List – periodEnumpublic enum periodEnum {UNKNOWN, WEEKLY, TENDAYS, MONTHLY};*Victor

1. Pay List – getMerchantInfo Methodpublic ArrayList getMerchantInfo(Connection conn) throws SQLException{

1. Pay List – MerchantInfo Inner Classclass MerchantInfo{ private int id; private java.sql.Date

1. Pay List – getMerchantInfo Method while (rs.next()){ MerchantInfo info =

1. Pay List – filterList Methodpublic ArrayList filterList(ArrayList list){ ArrayList listRet

1. Pay List – filterList Method switch(info.getPeriod()){ case WEEKLY: if (dt.until(current,

1. Pay List – filterList Method default: break; } listRet.add(info);

1. Pay List – addToTrans Methodpublic void addToTrans(Connection conn, ArrayList list)

1. Pay List – main Methodpublic static void main(String[] args) {

1. Pay ListSee 729TransMoney project for the full text*Victor Mozharsky

2. Money TransferCreate a method that gets an accessible transfer sum

2. Money Transfer – transMoney Tablecreate table transMoney ( id

TransferInfo Inner Classclass TransferInfo{ private int id; private int merchantId; private double sumSent; private java.sql.Date

2. Money Transfer - getUnpayedpublic ArrayList getUnpayed(Connection conn) throws SQLException{ Statement

2. Money Transfer - getUnpayedwhile (rs.next()){ TransferInfo info = new TransferInfo();

2. Money Transfer - procUnpayedpublic void procUnpayed(Connection conn, ArrayList list,

2. Money Transfer - procUnpayed } catch (Exception ex){ ex.printStackTrace(); conn.rollback(); } }}*Victor

2. Money Transfer - sendPaymentpublic void sendPayment(Connection conn, TransferInfo info) throws

2. Money Transfer - updateMerchantpublic void updateMerchant(Connection conn, TransferInfo info) throws

2. Money Transfer - updateMerchant sql = "UPDATE merchant set lastSent=?,

2. Money Transfer - mainpublic static void main(String[] args) throws SQLException{ Connection

Похожие презентации

1. Pay List
You should create a pay list for merchants accordingly

1. Pay List – DB Structure
create table transMoney (
id

1. Pay List – periodEnum
public enum periodEnum {UNKNOWN, WEEKLY, TENDAYS, MONTHLY};
*
Victor

1. Pay List – getMerchantInfo Method
public ArrayList getMerchantInfo(Connection conn) throws SQLException{

1. Pay List – MerchantInfo Inner Class
class MerchantInfo{
private int id;
private java.sql.Date

1. Pay List – getMerchantInfo Method
while (rs.next()){
MerchantInfo info =

1. Pay List – filterList Method
public ArrayList filterList(ArrayList list){
ArrayList listRet

1. Pay List – filterList Method
switch(info.getPeriod()){
case WEEKLY:
if (dt.until(current,

1. Pay List – filterList Method
default:
break;
}
listRet.add(info);

1. Pay List – addToTrans Method
public void addToTrans(Connection conn, ArrayList list)

1. Pay List – main Method
public static void main(String[] args) {

1. Pay List
See 729TransMoney project for the full text
*
Victor Mozharsky

2. Money Transfer
Create a method that gets an accessible transfer sum

2. Money Transfer – transMoney Table
create table transMoney (
id

TransferInfo Inner Class
class TransferInfo{
private int id;
private int merchantId;
private double sumSent;
private java.sql.Date

2. Money Transfer - getUnpayed
public ArrayList getUnpayed(Connection conn) throws SQLException{
Statement

2. Money Transfer - getUnpayed
while (rs.next()){
TransferInfo info = new TransferInfo();

2. Money Transfer - procUnpayed
public void procUnpayed(Connection conn,
ArrayList list,

2. Money Transfer - procUnpayed
} catch (Exception ex){
ex.printStackTrace();
conn.rollback();
}
}
}
*
Victor

2. Money Transfer - sendPayment
public void sendPayment(Connection conn, TransferInfo info) throws

2. Money Transfer - updateMerchant
public void updateMerchant(Connection conn, TransferInfo info) throws

2. Money Transfer - updateMerchant
sql = "UPDATE merchant set lastSent=?,

2. Money Transfer - main
public static void main(String[] args) throws SQLException{
Connection