1 package org.opensync.importexport;
2
3 import java.sql.Connection;
4 import java.sql.SQLException;
5 import java.sql.SQLWarning;
6 import java.sql.Statement;
7 import java.util.Hashtable;
8 import java.util.Vector;
9
10 import org.opensync.tools.ConnectionPool;
11 import org.opensync.tools.Utils;
12
13 public class Update extends Object {
14
15 private Hashtable valuesUpdate,primaryKeysValues;
16 private View theme;
17 private Vector primaryKeys;
18
19 private String fileUpdate;
20 private Vector fieldsUpdate, conditionsWhere;
21 private boolean insertOK;
22 private boolean batchMode = false;
23
24
25 public Update(ConnectionPool pool, View thm, Hashtable values) throws SQLException,SQLWarning {
26
27 theme = thm;
28 valuesUpdate = values;
29 primaryKeys = theme.getPrimaryKeys();
30 primaryKeysValues = buildPrimaryKeysValues();
31
32 fileUpdate = theme.getIdParentFile();
33 fieldsUpdate = buildFieldsUpdate();
34 conditionsWhere = buildConditionsWhere();
35 if (conditionsWhere.size()>0) launchQueryUpdate(pool);
36
37 else insertOK = true;
38
39 }
40
41 public Update(Connection conn, Statement stmt, boolean batchMode, View thm, Hashtable values) throws SQLException,SQLWarning {
42
43 theme = thm;
44 valuesUpdate = values;
45 primaryKeys = theme.getPrimaryKeys();
46 primaryKeysValues = buildPrimaryKeysValues();
47
48 fileUpdate = theme.getIdParentFile();
49 fieldsUpdate = buildFieldsUpdate();
50 conditionsWhere = buildConditionsWhere();
51 if (conditionsWhere.size()>0) launchQueryUpdate(conn, stmt, batchMode);
52
53 else insertOK = true;
54
55 }
56
57 private Hashtable buildPrimaryKeysValues () {
58 int i,iMax;
59 String data,keyW;
60 Hashtable h;
61 h = new Hashtable();
62 iMax = primaryKeys.size();
63 for (i=0;i<iMax;i++) {
64 keyW = (String)primaryKeys.elementAt(i);
65 if ( valuesUpdate!=null && valuesUpdate.containsKey(keyW)) data = (String)valuesUpdate.get(keyW);
66 else data = "";
67 if ( data != null && data.length() > 0 ) h.put(keyW,data);
68 else h.put(keyW,"");
69 }
70 if (Utils.debug) System.out.println("primaryKeys hastable : "+h);
71 return h;
72 }
73
74 Vector buildFieldsUpdate() {
75 int i,j,indexP,iMax,jMax,indexPMax,x,y;
76 Column e;
77 Vector v;
78 Column v1[];
79 String dataF,typeF;
80
81 v = new Vector();
82 v1 = theme.getElements();
83 iMax = v1.length;
84
85
86 for (i=0;i<iMax;i++) {
87 e = v1[i];
88 dataF = "";
89 typeF = "";
90 if ( e.getFile().equals(fileUpdate) ) {
91 dataF = (String)valuesUpdate.get(e.getKeyWord());
92 if (dataF==null || dataF.trim().length()==0) {
93
94 v.addElement(e.getField()+"=NULL");
95 }
96 else {
97
98
99 typeF = e.getType();
100 if (typeF.equals("N")) {
101 v.addElement(e.getField()+"="+dataF.trim());
102 }
103 else {
104 dataF = Utils.checkSpecialChar(dataF , '\'' , "''" );
105 v.addElement(e.getField()+"='"+dataF.trim().toUpperCase()+"'");
106 }
107 }
108 }
109 }
110
111 if (Utils.debug)
112 for (i=0;i<v.size();i++)
113 System.out.println("Field Update : " + v.elementAt(i));
114
115 return v;
116 }
117
118 Vector buildConditionsWhere() {
119 int i,j,indexP,iMax,jMax,indexPMax,x,y;
120 Column e;
121 Vector v;
122 Column v1[];
123 String dataF,typeF,keyW;
124
125 v = new Vector();
126 v1 = theme.getElements();
127 iMax = v1.length;
128
129 for (j=0;j<primaryKeys.size();j++) {
130
131 for (i=0;i<iMax;i++) {
132 e = v1[i];
133 dataF = "";
134 typeF = "";
135 keyW = (String)primaryKeys.elementAt(j);
136
137 if ( e.getFile().equals(fileUpdate) && e.getKeyWord().equals(keyW) ) {
138 dataF = (String)primaryKeysValues.get(e.getKeyWord());
139 if (dataF!=null && dataF.equals("")==false) {
140
141 typeF = e.getType();
142 if (typeF.equals("N")) v.addElement(e.getField()+"="+dataF.trim().toUpperCase());
143 else {
144 if (typeF.equals("C") || typeF.equals("T")) {
145
146 dataF = Utils.checkSpecialChar(dataF , '\'' , "''" );
147 v.addElement(e.getField()+"='"+dataF.trim().toUpperCase()+"'");
148 }
149 else v.addElement(e.getField()+"='"+dataF.trim().toUpperCase()+"'");
150 }
151 }
152 break;
153 }
154 }
155 }
156
157 if (Utils.debug){
158 for (i=0;i<v.size();i++){
159
160 }
161 }
162
163
164 return v;
165 }
166
167 private void launchQueryUpdate(ConnectionPool pool) throws SQLException,SQLWarning {
168
169 String queryUpdate;
170 StringBuffer s;
171
172 int i,j,indexP,iMax,jMax,indexPMax;
173 boolean found ;
174
175 s = new StringBuffer("");
176 s.append("UPDATE ");
177 s.append(fileUpdate);
178 s.append(" SET ");
179 iMax = fieldsUpdate.size();
180 for (i=0;i<iMax;i++) {
181 s.append(fieldsUpdate.elementAt(i));
182 s.append(" ,");
183 }
184 s.delete(s.length()-1,s.length());
185 s.append(" WHERE ");
186
187 iMax = conditionsWhere.size();
188 for (i=0;i<iMax;i++) {
189 s.append(conditionsWhere.elementAt(i));
190 s.append(" AND ");
191 }
192 s.delete(s.length()-4,s.length());
193
194 Connection conn = null;
195 Statement stmt = null;
196 try {
197 conn = pool.getConnection();
198 stmt = conn.createStatement();
199 int ok = stmt.executeUpdate(s.toString());
200
201
202
203 if (ok==0) insertOK=true;
204 else insertOK=false;
205 stmt.close();
206 stmt = null;
207 pool.freeConnection(conn);
208 if (!insertOK && Utils.debug==true) System.out.println("Update done.... : ");
209
210 }
211
212 catch(SQLWarning e1) {
213 throw new SQLWarning("We got a SQLWarning when executing this query: "+s.toString()+"\n"+e1.getMessage());
214 }
215
216 catch(SQLException e) {
217 throw new SQLException("We got a SQLException when executing this query: "+s.toString()+"\n"+e.getMessage());
218 }
219 finally {
220 if (stmt!=null) stmt.close();
221 pool.freeConnection(conn);
222 }
223
224
225
226
227
228
229
230 }
231
232 private void launchQueryUpdate(Connection conn, Statement stmt, boolean batchMode) throws SQLException,SQLWarning {
233
234 String queryUpdate;
235 StringBuffer s;
236
237 int i,j,indexP,iMax,jMax,indexPMax;
238 boolean found ;
239
240 s = new StringBuffer("");
241 s.append("UPDATE ");
242 s.append(fileUpdate);
243 s.append(" SET ");
244 iMax = fieldsUpdate.size();
245 for (i=0;i<iMax;i++) {
246 s.append(fieldsUpdate.elementAt(i));
247 s.append(" ,");
248 }
249 s.delete(s.length()-1,s.length());
250 s.append(" WHERE ");
251
252 iMax = conditionsWhere.size();
253 for (i=0;i<iMax;i++) {
254 s.append(conditionsWhere.elementAt(i));
255 s.append(" AND ");
256 }
257 s.delete(s.length()-4,s.length());
258
259 Statement stmt_simple = null;
260 try {
261 if (batchMode) {
262 stmt.addBatch(s.toString());
263 System.out.println("Update added to batch ...");
264 } else {
265 stmt_simple = conn.createStatement();
266 int ok = stmt_simple.executeUpdate(s.toString());
267
268
269 if (ok==0) insertOK=true;
270 else insertOK=false;
271 stmt_simple.close();
272 stmt_simple = null;
273 if (!insertOK) System.out.println("Update done.... : ");
274
275 }
276 }
277
278 catch(SQLWarning e1) {
279 throw new SQLWarning("We got a SQLWarning when executing this query: "+s.toString()+"\n"+e1.getMessage());
280 }
281
282 catch(SQLException e) {
283 throw new SQLException("We got a SQLException when executing this query: "+s.toString()+"\n"+e.getMessage());
284 }
285 finally {
286 if (stmt_simple!=null) stmt_simple.close();
287 }
288
289
290
291
292
293
294 }
295
296 public boolean getInsertOK() {
297 return insertOK;
298 }
299
300 }