1 package ejava.projects.esales.jdbc;
2
3 import java.lang.reflect.Method;
4 import java.sql.Connection;
5 import java.sql.PreparedStatement;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.sql.Statement;
9 import java.util.List;
10
11 import org.slf4j.Logger;
12 import org.slf4j.LoggerFactory;
13
14 import ejava.projects.esales.bo.Account;
15 import ejava.projects.esales.bo.Address;
16 import ejava.projects.esales.dao.AccountDAO;
17 import ejava.projects.esales.dao.AccountDAOException;
18
19 public class JDBCAccountDAO implements AccountDAO {
20 private static Logger log = LoggerFactory.getLogger(JDBCAccountDAO.class);
21 private Connection connection;
22
23 public void setConnection(Connection connection) {
24 this.connection = connection;
25 }
26
27 public void createAccount(Account account) {
28 PreparedStatement statement1 = null;
29 PreparedStatement statement2 = null;
30 Statement statement3 = null;
31 PreparedStatement statement4 = null;
32 ResultSet rs = null;
33 try {
34 statement1 = connection.prepareStatement(
35 "INSERT INTO ESALES_ACCT " +
36 "(USER_ID, FIRST_NAME) " +
37 "VALUES (?, ?)");
38 statement1.setString(1, account.getUserId());
39 statement1.setString(2, account.getFirstName());
40 statement1.execute();
41
42 statement2 = connection.prepareStatement(
43 "INSERT INTO ESALES_ADDRESS " +
44 "(ID, NAME, CITY) " +
45 "VALUES (null, ?, ?)");
46 statement3 = connection.createStatement();
47 statement4 = connection.prepareStatement(
48 "INSERT INTO ESALES_ACCT_ADDRESS_LINK " +
49 "(USER_ID, ADDRESS_ID) " +
50 "VALUES (?, ?)");
51
52 Method setId = Address.class.getDeclaredMethod(
53 "setId", new Class[]{long.class});
54 setId.setAccessible(true);
55 for (Address address : account.getAddresses()) {
56
57 statement2.setString(1, address.getName());
58 statement2.setString(2, address.getCity());
59 statement2.execute();
60
61
62
63 rs = statement3.executeQuery("call identity()");
64 rs.next();
65 long id = rs.getLong(1);
66 setId.invoke(address, id);
67
68 statement4.setString(1, account.getUserId());
69 statement4.setLong(2, address.getId());
70 statement4.execute();
71 }
72 }
73 catch (SQLException ex) {
74 log.error("SQL error creating account", ex);
75 throw new AccountDAOException("error creating account"+ex, ex);
76 }
77 catch (Exception ex) {
78 log.error("error creating account", ex);
79 throw new AccountDAOException("error creating account"+ex, ex);
80 }
81 finally {
82 try { rs.close(); } catch (Exception ignored) {}
83 try { statement1.close(); } catch (Exception ignored) {}
84 try { statement2.close(); } catch (Exception ignored) {}
85 try { statement3.close(); } catch (Exception ignored) {}
86 try { statement4.close(); } catch (Exception ignored) {}
87 }
88 }
89
90 public List<Account> getAccounts(int index, int count)
91 throws AccountDAOException {
92 throw new AccountDAOException("not implemented", null);
93 }
94 }