-
Notifications
You must be signed in to change notification settings - Fork 0
/
IssuebookDAO.java
123 lines (101 loc) · 3.21 KB
/
IssuebookDAO.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
package com.cruds.swingproj;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Vector;
public class IssuebookDAO
{
public Vector<Vector<String>> getBookISBN(int ISBN)
{
String sql ="select b.ISBN, b.title, b.category, b.no_of_books, a.name, a.email from book b, author a where b.ISBN=a.ISBN and b.ISBN = ?";
Vector<Vector<String>> data = new Vector<Vector<String>>();
Vector<String> row = null;
try(Connection conn = DBConnectionManager.getConnection())
{
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, ISBN);
ResultSet rs = ps.executeQuery();
while(rs !=null && rs.next())
{
row = new Vector<String>();
row.add(String.valueOf(rs.getInt(1)));
row.add(rs.getString(2));
row.add(rs.getString(3));
row.add(String.valueOf(rs.getInt(4)));
row.add(rs.getString(5));
row.add(rs.getString(6));
data.add(row);
}
} catch (SQLException e) {
e.printStackTrace();
}
return data;
}
public Vector<Vector<String>> getStudentUSN(String USN)
{
String sql ="select USN, sname from student where USN = ?";
Vector<Vector<String>> data = new Vector<Vector<String>>();
Vector<String> row = null;
try(Connection conn = DBConnectionManager.getConnection())
{
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, USN);
ResultSet rs = ps.executeQuery();
while(rs !=null && rs.next())
{
row = new Vector<String>();
row.add(rs.getString(1));
row.add(rs.getString(2));
data.add(row);
}
} catch (SQLException e) {
e.printStackTrace();
}
return data;
}
public boolean book_issue(Student s, Date issued_date, int ISBN)
{
String sql = "select USN from Student where usn = ?";
String sql2 = "insert into book_issue(USN,issued_date, return_date, ISBN) values(?,?,?,?)";
String sql3 = "update book set no_of_books = no_of_books-? where ISBN = ?";
int row2=0, row3=0;
int Book = 1;
String USN = null;
Calendar cal = Calendar.getInstance();
cal.setTime(issued_date);
cal.add(Calendar.DATE, 7);
Date return_date = cal.getTime();
java.sql.Date idate = new java.sql.Date(issued_date.getTime());
java.sql.Date rdate = new java.sql.Date(return_date.getTime());
try(Connection conn = com.cruds.swingproj.DBConnectionManager.getConnection())
{
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, s.getUSN());
ResultSet rs = ps.executeQuery();
if(rs!=null && rs.next())
{
USN = rs.getString(1);
}
PreparedStatement ps2 = conn.prepareStatement(sql2);
ps2.setString(1, s.getUSN());
ps2.setDate(2, idate);
ps2.setDate(3, rdate);
ps2.setInt(4, ISBN);
row2 = ps2.executeUpdate();
PreparedStatement ps3 = conn.prepareStatement(sql3);
ps3.setInt(1, Book);
ps3.setInt(2, ISBN);
row3 = ps3.executeUpdate();
}
catch (SQLException e)
{
e.printStackTrace();
}
return (row2 > 0 && row3 > 0);
}
}