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