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);
}
}
} |
Partager