Оцените презентацию от 1 до 5 баллов!
Тип файла:
ppt / pptx (powerpoint)
Всего слайдов:
25 слайдов
Для класса:
1,2,3,4,5,6,7,8,9,10,11
Размер файла:
329.00 kB
Просмотров:
67
Скачиваний:
0
Автор:
неизвестен
Слайды и текст к этой презентации:
№1 слайд![. Databases and JDBC . JDBC](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img0.jpg)
Содержание слайда: 7. Databases and JDBC
3. JDBC Additional Tasks
№2 слайд![. Pay List You should create](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img1.jpg)
Содержание слайда: 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
№3 слайд![. Pay List DB Structure](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img2.jpg)
Содержание слайда: 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)
);
№4 слайд![. Pay List periodEnum public](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img3.jpg)
Содержание слайда: 1. Pay List – periodEnum
public enum periodEnum {UNKNOWN, WEEKLY, TENDAYS, MONTHLY};
№5 слайд![. Pay List getMerchantInfo](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img4.jpg)
Содержание слайда: 1. Pay List – getMerchantInfo Method
public ArrayList<MerchantInfo> getMerchantInfo(Connection conn) throws SQLException{
Statement stmt = conn.createStatement();
ArrayList<MerchantInfo> list = new ArrayList<MerchantInfo>();
String sql = "select id, period, needToSend, lastSent, minSum from merchant";
ResultSet rs = stmt.executeQuery(sql);
№6 слайд![. Pay List MerchantInfo Inner](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img5.jpg)
Содержание слайда: 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
}
№7 слайд![. Pay List getMerchantInfo](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img6.jpg)
Содержание слайда: 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;
}
№8 слайд![. Pay List filterList Method](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img7.jpg)
Содержание слайда: 1. Pay List – filterList Method
public ArrayList<MerchantInfo> filterList(ArrayList<MerchantInfo> list){
ArrayList<MerchantInfo> listRet = new ArrayList<MerchantInfo>();
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();
№9 слайд![. Pay List filterList Method](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img8.jpg)
Содержание слайда: 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;
№10 слайд![. Pay List filterList Method](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img9.jpg)
Содержание слайда: 1. Pay List – filterList Method
default:
break;
}
listRet.add(info);
}
return listRet;
}
№11 слайд![. Pay List addToTrans Method](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img10.jpg)
Содержание слайда: 1. Pay List – addToTrans Method
public void addToTrans(Connection conn, ArrayList<MerchantInfo> 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();
}}
№12 слайд![. Pay List main Method public](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img11.jpg)
Содержание слайда: 1. Pay List – main Method
public static void main(String[] args) {
try{
Connection conn = getConnection();
TransMoney t = new TransMoney();
ArrayList<MerchantInfo> list = t.getMerchantInfo(conn);
list = t.filterList(list);
t.addToTrans(conn, list);
conn.close();
} catch(Exception ex){
System.out.println("Error " + ex.getMessage());
}}
№13 слайд![. Pay List See TransMoney](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img12.jpg)
Содержание слайда: 1. Pay List
See 729TransMoney project for the full text
№14 слайд![. Money Transfer Create a](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img13.jpg)
Содержание слайда: 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.
№15 слайд![. Money Transfer transMoney](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img14.jpg)
Содержание слайда: 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)
);
№16 слайд![TransferInfo Inner Class](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img15.jpg)
Содержание слайда: 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
}
№17 слайд![. Money Transfer - getUnpayed](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img16.jpg)
Содержание слайда: 2. Money Transfer - getUnpayed
public ArrayList<TransferInfo> getUnpayed(Connection conn) throws SQLException{
Statement stmt = conn.createStatement();
ArrayList<TransferInfo> list = new ArrayList<TransferInfo>();
String sql = "select id, merchantId, sumSent, sentDate, status from transMoney where status='0' order by sentDate, sumSent";
ResultSet rs = stmt.executeQuery(sql);
№18 слайд![. Money Transfer - getUnpayed](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img17.jpg)
Содержание слайда: 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;
}
№19 слайд![. Money Transfer -](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img18.jpg)
Содержание слайда: 2. Money Transfer - procUnpayed
public void procUnpayed(Connection conn,
ArrayList<TransferInfo> 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();
№20 слайд![. Money Transfer -](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img19.jpg)
Содержание слайда: 2. Money Transfer - procUnpayed
} catch (Exception ex){
ex.printStackTrace();
conn.rollback();
}
}
}
№21 слайд![. Money Transfer -](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img20.jpg)
Содержание слайда: 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();
}
№22 слайд![. Money Transfer -](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img21.jpg)
Содержание слайда: 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");
№23 слайд![. Money Transfer -](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img22.jpg)
Содержание слайда: 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();
}
№24 слайд![. Money Transfer - main](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img23.jpg)
Содержание слайда: 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<TransferInfo> list = t.getUnpayed(conn);
t.procUnpayed(conn, list, sum1);
}
№25 слайд![. Money Transfer - main catch](/documents_6/4693f466c18189dcc04dbaa6abd66dc7/img24.jpg)
Содержание слайда: 2. Money Transfer - main
catch(SQLException ex){
System.out.println("Error " + ex.getMessage());
}
catch(Exception ex){
System.out.println("Error " + ex.getMessage());
}
finally{
if (conn!= null) conn.close();
}
}
See 729aTransMoney project for the full text