1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package org.apache.juddi.datastore.jdbc;
17
18 import java.sql.Connection;
19 import java.sql.PreparedStatement;
20 import java.sql.ResultSet;
21 import java.util.Vector;
22
23 import org.apache.commons.logging.Log;
24 import org.apache.commons.logging.LogFactory;
25 import org.apache.juddi.datatype.Email;
26 import org.apache.juddi.registry.RegistryEngine;
27 import org.apache.juddi.util.Config;
28
29 /***
30 * @author Steve Viens (sviens@apache.org)
31 */
32 class EmailTable
33 {
34
35 private static Log log = LogFactory.getLog(EmailTable.class);
36
37 static String insertSQL = null;
38 static String selectSQL = null;
39 static String deleteSQL = null;
40 static String tablePrefix = "";
41
42 static {
43 tablePrefix = Config.getStringProperty(
44 RegistryEngine.PROPNAME_TABLE_PREFIX,RegistryEngine.DEFAULT_TABLE_PREFIX);
45
46 StringBuffer sql = null;
47
48
49 sql = new StringBuffer(150);
50 sql.append("INSERT INTO ").append(tablePrefix).append("EMAIL (");
51 sql.append("BUSINESS_KEY,");
52 sql.append("CONTACT_ID,");
53 sql.append("EMAIL_ID,");
54 sql.append("USE_TYPE,");
55 sql.append("EMAIL_ADDRESS) ");
56 sql.append("VALUES (?,?,?,?,?)");
57 insertSQL = sql.toString();
58
59
60 sql = new StringBuffer(200);
61 sql.append("SELECT ");
62 sql.append("USE_TYPE,");
63 sql.append("EMAIL_ADDRESS, ");
64 sql.append("EMAIL_ID ");
65 sql.append("FROM ").append(tablePrefix).append("EMAIL ");
66 sql.append("WHERE BUSINESS_KEY=? ");
67 sql.append("AND CONTACT_ID=? ");
68 sql.append("ORDER BY EMAIL_ID");
69 selectSQL = sql.toString();
70
71
72 sql = new StringBuffer(100);
73 sql.append("DELETE FROM ").append(tablePrefix).append("EMAIL ");
74 sql.append("WHERE BUSINESS_KEY=?");
75 deleteSQL = sql.toString();
76 }
77
78 /***
79 * Insert new row into the EMAIL table.
80 *
81 * @param businessKey String to the BusinessEntity object that owns the Contact to be inserted
82 * @param contactID The unique ID generated when saving the parent Contact instance.
83 * @param emailList Vector of Email objects holding values to be inserted
84 * @param connection JDBC connection
85 * @throws java.sql.SQLException
86 */
87 public static void insert(
88 String businessKey,
89 int contactID,
90 Vector emailList,
91 Connection connection)
92 throws java.sql.SQLException
93 {
94 if ((emailList == null) || (emailList.size() == 0))
95 return;
96
97 PreparedStatement statement = null;
98
99 try
100 {
101 statement = connection.prepareStatement(insertSQL);
102 statement.setString(1, businessKey.toString());
103 statement.setInt(2, contactID);
104
105 int listSize = emailList.size();
106 for (int emailID = 0; emailID < listSize; emailID++)
107 {
108 Email email = (Email) emailList.elementAt(emailID);
109
110 statement.setInt(3, emailID);
111 statement.setString(4, email.getUseType());
112 statement.setString(5, email.getValue());
113
114 if (log.isDebugEnabled()) {
115 log.debug(
116 "insert into " + tablePrefix + "EMAIL table:\n\n\t"
117 + insertSQL
118 + "\n\t BUSINESS_KEY="
119 + businessKey.toString()
120 + "\n\t CONTACT_ID="
121 + contactID
122 + "\n\t EMAIL_ID="
123 + emailID
124 + "\n\t USE_TYPE="
125 + email.getUseType()
126 + "\n\t EMAIL_ADDRESS="
127 + email.getValue()
128 + "\n");
129 }
130
131 statement.executeUpdate();
132 }
133 }
134 finally
135 {
136 try
137 {
138 statement.close();
139 }
140 catch (Exception e)
141 {
142 }
143 }
144 }
145
146 /***
147 * Select all rows from the CONTACT table for a given BusinessKey.
148 *
149 * @param businessKey String
150 * @param contactID Unique ID representing the parent Contact instance
151 * @param connection JDBC connection
152 * @throws java.sql.SQLException
153 */
154 public static Vector select(
155 String businessKey,
156 int contactID,
157 Connection connection)
158 throws java.sql.SQLException
159 {
160 Vector emailList = new Vector();
161 PreparedStatement statement = null;
162 ResultSet resultSet = null;
163
164 try
165 {
166
167 statement = connection.prepareStatement(selectSQL);
168 statement.setString(1, businessKey.toString());
169 statement.setInt(2, contactID);
170
171 if (log.isDebugEnabled()) {
172 log.debug(
173 "select from " + tablePrefix + "EMAIL table:\n\n\t"
174 + selectSQL
175 + "\n\t BUSINESS_KEY="
176 + businessKey.toString()
177 + "\n\t CONTACT_ID="
178 + contactID
179 + "\n");
180 }
181
182
183 resultSet = statement.executeQuery();
184 while (resultSet.next())
185 {
186 Email email = new Email();
187 email.setUseType(resultSet.getString(1));
188 email.setValue(resultSet.getString(2));
189 emailList.add(email);
190 }
191
192 return emailList;
193 }
194 finally
195 {
196 try
197 {
198 resultSet.close();
199 statement.close();
200 }
201 catch (Exception e)
202 {
203 }
204 }
205 }
206
207 /***
208 * Delete multiple rows from the EMAIL table that are assigned to the
209 * BusinessKey specified.
210 *
211 * @param businessKey String
212 * @param connection JDBC connection
213 * @throws java.sql.SQLException
214 */
215 public static void delete(String businessKey, Connection connection)
216 throws java.sql.SQLException
217 {
218 PreparedStatement statement = null;
219
220 try
221 {
222
223 statement = connection.prepareStatement(deleteSQL);
224 statement.setString(1, businessKey.toString());
225
226 if (log.isDebugEnabled()) {
227 log.debug(
228 "delete from " + tablePrefix + "EMAIL table:\n\n\t"
229 + deleteSQL
230 + "\n\t BUSINESS_KEY="
231 + businessKey.toString()
232 + "\n");
233 }
234
235
236 statement.executeUpdate();
237 }
238 finally
239 {
240 try
241 {
242 statement.close();
243 }
244 catch (Exception e)
245 {
246 }
247 }
248 }
249 }