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
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
package com.database.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.database.DataBaseUtils;
import com.database.entity.Person;
public class PersonDAO
{
private static PersonDAO personDAO;
private PersonDAO() {}
public static PersonDAO getInstance()
{
if (personDAO == null)
{
personDAO = new PersonDAO();
}
return personDAO;
}
public Integer addPerson(String firstName, String lastName, String address, String zip, String city) throws Exception
{
Connection connection = null;
Integer uid = 0;
try
{
connection = DataBaseUtils.getConnection();
PreparedStatement pstmt = connection.prepareStatement("insert into T_PERSON(FIRST_NAME, LAST_NAME, ADDRESS, ZIP, CITY) values(?, ?, ?, ?, ?)");
pstmt.setString(1, firstName);
pstmt.setString(2, lastName);
pstmt.setString(3, address);
pstmt.setString(4, zip);
pstmt.setString(5, city);
pstmt.executeUpdate();
connection.commit();
ResultSet rs = pstmt.getGeneratedKeys();
if(rs.next()) uid = rs.getInt(1);
}
catch (Exception e)
{
// logger
throw e;
}
finally
{
DataBaseUtils.closeConection(connection);
}
return uid;
}
public Integer copyPerson(Integer uid, String prefix, String suffix) throws Exception
{
Connection connection = null;
try
{
connection = DataBaseUtils.getConnection();
PreparedStatement pstmt = connection.prepareStatement("select * from T_PERSON where UID=?");
pstmt.setInt(1, uid);
ResultSet rs = pstmt.executeQuery();
if (rs.next())
{
uid = addPerson(rs.getString("FIRST_NAME"), prefix+rs.getString("LAST_NAME")+suffix, rs.getString("ADDRESS"), rs.getString("ZIP"), rs.getString("CITY"));
}
}
catch (Exception e)
{
// logger
throw e;
}
finally
{
DataBaseUtils.closeConection(connection);
}
return uid;
}
public void updatePerson(Person person) throws Exception
{
Connection connection = null;
try
{
connection = DataBaseUtils.getConnection();
PreparedStatement pstmt = connection.prepareStatement("update T_PERSON set FIRST_NAME=?, LAST_NAME=?, ADDRESS=?, ZIP=?, CITY=? where UID=?");
pstmt.setString(1, person.getFirstName());
pstmt.setString(2, person.getLastName());
pstmt.setString(3, person.getAddress());
pstmt.setString(4, person.getZip());
pstmt.setString(5, person.getCity());
pstmt.setInt(6, person.getUid());
pstmt.executeUpdate();
connection.commit();
}
catch (Exception e)
{
// logger
throw e;
}
finally
{
DataBaseUtils.closeConection(connection);
}
}
public void deletePerson(Integer uid) throws Exception
{
Connection connection = null;
try
{
connection = DataBaseUtils.getConnection();
PreparedStatement pstmt = connection.prepareStatement("delete from T_PERSON where UID = ?");
pstmt.setInt(1, uid);
pstmt.executeUpdate();
connection.commit();
}
catch (Exception e)
{
// logger
throw e;
}
finally
{
DataBaseUtils.closeConection(connection);
}
}
} |
