1 package ch.ige.edossier.web.server.dao;
2
3 import java.sql.*;
4 import java.util.ArrayList;
5 import java.util.List;
6 import org.apache.log4j.Logger;
7 import ch.ige.edossier.util.DBHelper;
8 import ch.ige.edossier.web.vo.AddressVO;
9
10
18 public class AddressDAO
19 {
20 private static final Logger LOG = Logger.getLogger( AddressDAO.class );
22
23
29 public List select( int dossierId ) throws SQLException
30 {
31 Connection con = null;
32 PreparedStatement ps = null;
33 ResultSet rs = null;
34 String query = null;
35 List list = new ArrayList();
36 AddressVO addressVO = null;
37
38 try
39 {
40 query = "SELECT a.address_id, a.name, a.address, a.city, a.country_cd, a.created_dat, " +
41 "a.changed_dat, b.role_id, b.dossier_id " +
42 "FROM edossier.address a, edossier.role_adr_dossier b " +
43 "WHERE b.dossier_id = ? " +
44 "AND a.address_id = b.address_id ORDER BY b.role_id";
45
46 con = DBHelper.getInstance().getEsolutionConnection();
47 ps = con.prepareStatement( query );
48 ps.setInt( 1, dossierId );
49 rs = ps.executeQuery();
50
51 list.clear();
52
53 while( rs.next() )
54 {
55 addressVO = new AddressVO();
56 addressVO.setAddressId( rs.getInt( "address_id" ) );
57 addressVO.setRoleId( rs.getInt( "role_id" ) );
58 addressVO.setDossierId( rs.getInt( "dossier_id" ) );
59 addressVO.setName( rs.getString( "name" ) );
60 addressVO.setAddress( rs.getString( "address" ) );
61 addressVO.setCity( rs.getString( "city" ) );
62 addressVO.setCountryCd( rs.getString( "country_cd" ) );
63 addressVO.setCreatedDat( rs.getDate( "created_dat" ) );
64 addressVO.setChangedDat( rs.getDate( "changed_dat" ) );
65 list.add( addressVO );
66 }
67 }
68 finally
69 {
70 DBHelper.getInstance().close( con, ps );
71 }
72 return list;
73 }
74
75
81 public AddressVO selectAddress( int addressId ) throws SQLException
82 {
83 Connection con = null;
84 PreparedStatement ps = null;
85 ResultSet rs = null;
86 String query = null;
87 AddressVO addressVO = null;
88
89 try
90 {
91 query = "SELECT address_id, name, address, city, country_cd, created_dat, changed_dat " +
92 "FROM edossier.address " +
93 "WHERE address_id = ?";
94
95 con = DBHelper.getInstance().getEsolutionConnection();
96 ps = con.prepareStatement( query );
97 ps.setInt( 1, addressId );
98 rs = ps.executeQuery();
99
100 if( rs.next() )
101 {
102 addressVO = new AddressVO();
103 addressVO.setAddressId( rs.getInt( "address_id" ) );
104 addressVO.setName( rs.getString( "name" ) );
105 addressVO.setAddress( rs.getString( "address" ) );
106 addressVO.setCity( rs.getString( "city" ) );
107 addressVO.setCountryCd( rs.getString( "country_cd" ) );
108 addressVO.setCreatedDat( rs.getDate( "created_dat" ) );
109 addressVO.setChangedDat( rs.getDate( "changed_dat" ) );
110 }
111 }
112 finally
113 {
114 DBHelper.getInstance().close( con, ps );
115 }
116 return addressVO;
117 }
118
119
120
125 public void insert( AddressVO addressVO ) throws Exception
126 {
127 Connection con = null;
128 PreparedStatement ps = null;
129
130 try
131 {
132 con = DBHelper.getInstance().getEsolutionConnection();
133 ps = con.prepareStatement(
134 "INSERT INTO edossier.address(" +
135 "address_id, " +
136 "name, " +
137 "address, " +
138 "city, " +
139 "country_cd ) " +
140 "VALUES(?,?,?,?,?)"
141 );
142
143 ps.setInt( 1, addressVO.getAddressId() );
144 ps.setString( 2, addressVO.getName() );
145 ps.setString( 3, addressVO.getAddress() );
146 ps.setString( 4, addressVO.getCity() );
147 ps.setString( 5, addressVO.getCountryCd() );
148
149 if( ps.executeUpdate() != 1 )
150 {
151 throw new SQLException();
152 }
153 }
154 finally
155 {
156 DBHelper.getInstance().close( con, ps );
157 }
158 }
159
160
165 public void update( AddressVO addressVO ) throws Exception
166 {
167 Connection con = null;
168 PreparedStatement ps = null;
169
170 try
171 {
172 con = DBHelper.getInstance().getEsolutionConnection();
173 ps = con.prepareStatement( "UPDATE edossier.address " +
174 "SET name = ?, address = ?, city = ?, country_cd = ?, changed_dat = sysdate " +
175 "WHERE address_id = ?" );
176
177 ps.setString( 1, addressVO.getName() );
178 ps.setString( 2, addressVO.getAddress() );
179 ps.setString( 3, addressVO.getCity() );
180 ps.setString( 4, addressVO.getCountryCd() );
181 ps.setInt( 5, addressVO.getAddressId() );
182
183 if( ps.executeUpdate() != 1 )
184 {
185 throw new SQLException();
186 }
187 }
188 finally
189 {
190 DBHelper.getInstance().close( con, ps );
191 }
192 }
193
194
200 public int delete( int addressId ) throws SQLException
201 {
202 Connection con = null;
203 PreparedStatement ps = null;
204
205 try
206 {
207 con = DBHelper.getInstance().getEsolutionConnection();
208
209 ps = con.prepareStatement(
210 "DELETE FROM edossier.address where address_id = ? "
211 );
212 ps.setInt( 1, addressId );
213
214 return ps.executeUpdate();
215 }
216 finally
217 {
218 DBHelper.getInstance().close( con, ps );
219 }
220 }
221 }
222