Java > Open Source Codes > com > knowgate > hipergate > DBLanguages


1 /*
2   Copyright (C) 2003 Know Gate S.L. All rights reserved.
3                       C/Oña, 107 1º2 28050 Madrid (Spain)
4
5   Redistribution and use in source and binary forms, with or without
6   modification, are permitted provided that the following conditions
7   are met:
8
9   1. Redistributions of source code must retain the above copyright
10      notice, this list of conditions and the following disclaimer.
11
12   2. The end-user documentation included with the redistribution,
13      if any, must include the following acknowledgment:
14      "This product includes software parts from hipergate
15      (http://www.hipergate.org/)."
16      Alternately, this acknowledgment may appear in the software itself,
17      if and wherever such third-party acknowledgments normally appear.
18
19   3. The name hipergate must not be used to endorse or promote products
20      derived from this software without prior written permission.
21      Products derived from this software may not be called hipergate,
22      nor may hipergate appear in their name, without prior written
23      permission.
24
25   This library is distributed in the hope that it will be useful,
26   but WITHOUT ANY WARRANTY; without even the implied warranty of
27   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
28
29   You should have received a copy of hipergate License with this code;
30   if not, visit http://www.hipergate.org or mail to info@hipergate.org
31 */
32
33 package com.knowgate.hipergate;
34
35 import java.util.HashMap ;
36 import java.util.Iterator ;
37 import java.util.ArrayList ;
38
39 import java.rmi.RemoteException ;
40 import java.sql.Connection ;
41 import java.sql.SQLException ;
42 import java.sql.PreparedStatement ;
43 import java.sql.Statement ;
44 import java.sql.ResultSet ;
45 import java.sql.ResultSetMetaData ;
46 import java.sql.Types ;
47
48 import com.knowgate.debug.DebugFile;
49 import com.knowgate.jdc.JDCConnection;
50 import com.knowgate.dataobjs.DB;
51 import com.knowgate.dataobjs.DBBind;
52 import com.knowgate.dataobjs.DBSubset;
53
54 import com.knowgate.cache.DistributedCachePeer;
55
56 /**
57  * <p>Display static tables as HTML elements like &lt;SELECT&gt;.</p>
58  * This class is a singleton memory cache for frecuently accessed static tables.
59  * @version 3.0
60  */
61
62 public class DBLanguages {
63
64   private static String CNul(String sTr) {
65     return DBBind.Functions.ISNULL+"("+sTr+","+DB.tr_country_en+")";
66   }
67
68   private static String LNul(String sTr) {
69     return DBBind.Functions.ISNULL+"("+sTr+","+DB.tr_lang_en+")";
70   }
71
72   public DBLanguages() {
73     oTranslations = null;
74     oCountries = null;
75     oHTMLCache = new HashMap (83);
76     oCountryCache = new HashMap (387);
77     bLoaded = false;
78     bCountries = false;
79   }
80
81   // ----------------------------------------------------------
82
83   /**
84    * <p>Get an HTML ComboBox with a list of all languages available at table k_lu_languages.</p>
85    * Language names are written in the language passed as parameter.<br>
86    * Languages names are sorted.
87    * @param oConn Database Connection
88    * @param sIdLanguage 2 chraracters code of language for displaying &lt;OPTION&gt; texts.
89    * @return <OPTION VALUE="xx">Language 1<OPTION VALUE="yy">Language 2<OPTION ...
90    * @throws SQLException
91    */
92   public String toHTMLSelect(JDCConnection oConn, String sIdLanguage) throws SQLException {
93     String sLang = sIdLanguage.toLowerCase();
94     String sHTML = null;
95     int iTRCol;
96
97     if (DebugFile.trace)
98       {
99       DebugFile.writeln("Begin DBLanguages.toHTMLSelect([Connection]," + sIdLanguage + ")");
100       DebugFile.incIdent();
101       }
102
103     if (!bLoaded) {
104       if (DebugFile.trace) DebugFile.writeln(" Loading language table");
105
106       Statement oStmt = oConn.createStatement();
107       ResultSet oRSet = oStmt.executeQuery("SELECT * FROM "+DB.k_lu_languages+" WHERE 1=0");
108       ResultSetMetaData oMDat = oRSet.getMetaData();
109       final int nCols = oMDat.getColumnCount();
110       StringBuffer oCols = new StringBuffer (1000);
111       oCols.append(DB.id_language);
112       final String sLCaseTr = DB.tr_.toLowerCase();
113       for (int c=1; c<=nCols; c++) {
114         String sColNameLCase = oMDat.getColumnName(c).toLowerCase();
115         if (sColNameLCase.startsWith(sLCaseTr)) {
116           if (sColNameLCase.equalsIgnoreCase(DB.tr_lang_en) || sColNameLCase.equalsIgnoreCase(DB.tr_lang_es))
117             oCols.append(","+oMDat.getColumnName(c));
118           else
119             oCols.append(","+LNul(oMDat.getColumnName(c))+" AS "+oMDat.getColumnName(c));
120         } // fi (sColNameLCase startsWith sLCaseTr)
121 } // next
122 oRSet.close();
123       oStmt.close();
124
125       oTranslations = new DBSubset(DB.k_lu_languages, oCols.toString(), null, 60);
126       oTranslations.load(oConn);
127       bLoaded = true;
128
129       if (DebugFile.trace) DebugFile.writeln(" Languages loaded " + oTranslations.getRowCount());
130     } // endif (bLoaded)
131
132     try {
133       sHTML = (String ) oHTMLCache.get(sLang);
134     } catch (NullPointerException e) {
135       // Ignore Null Pointer Exception on assigning sHTML
136 }
137
138     if (null==sHTML) {
139       if (DebugFile.trace) DebugFile.writeln(" Composing HTML <OPTION>");
140
141       sHTML = new String ("");
142       iTRCol = oTranslations.getColumnPosition(DB.tr_lang_ + sLang);
143       if (-1==iTRCol) iTRCol = 0;
144
145       if (DebugFile.trace) DebugFile.writeln(" Translated column position is " + iTRCol + " (zero offset)");
146
147       oTranslations.sortBy(iTRCol);
148
149       for (int iRow=0; iRow<oTranslations.getRowCount(); iRow++) {
150         sHTML += "<OPTION VALUE='" + oTranslations.getString(0,iRow).trim() + "'>" + oTranslations.getString(iTRCol,iRow);
151       }
152
153       oHTMLCache.put(sLang, sHTML);
154     } // endif (null==sHTML)
155 else
156       if (DebugFile.trace) DebugFile.writeln(" HTML cache hit, no composing performed");
157
158     if (DebugFile.trace)
159       {
160       DebugFile.decIdent();
161       DebugFile.writeln("End DBLanguages.toHTMLSelect()");
162       }
163
164     return sHTML;
165   } // toHTMLSelect()
166
167   // ----------------------------------------------------------
168
169   /**
170    * <p>Get an HTML ComboBox with a list of all languages at k_lu_countries.</p>
171    * Country names are written in the language passed as parameter.<br>
172    * Country names are sorted.
173    * @param oConn Database Connection
174    * @param sIdLanguage 2 chraracters code of language for displaying &lt;OPTION&gt; texts.
175    * @return <OPTION VALUE="xx">Country 1<OPTION VALUE="yy">Country 2<OPTION ...
176    * @throws SQLException
177    */
178   public String getHTMLCountrySelect(JDCConnection oConn, String sIdLanguage)
179     throws SQLException {
180
181     if (DebugFile.trace)
182       {
183       DebugFile.writeln("Begin DBLanguages.getHTMLCountrySelect([Connection]," + sIdLanguage + ")");
184       DebugFile.incIdent();
185       }
186
187     StringBuffer oHTML = new StringBuffer (8000);
188
189     int iRows;
190     int iCols;
191
192     if (!bCountries) {
193       Statement oStmt = oConn.createStatement();
194       if (DebugFile.trace) DebugFile.writeln("Statement.executeQuery(SELECT * FROM "+DB.k_lu_countries+" WHERE 1=0)");
195       ResultSet oRSet = oStmt.executeQuery("SELECT * FROM "+DB.k_lu_countries+" WHERE 1=0");
196       ResultSetMetaData oMDat = oRSet.getMetaData();
197       final int nCols = oMDat.getColumnCount();
198       if (DebugFile.trace) DebugFile.writeln("Column count is " + String.valueOf(nCols));
199       StringBuffer oCols = new StringBuffer (1000);
200       oCols.append(DB.id_country);
201       ArrayList aCols = new ArrayList (nCols);
202       aCols.add(DB.id_language);
203       final String sLCaseTr = DB.tr_.toLowerCase();
204       for (int c=1; c<=nCols; c++) {
205         String sColNameLCase = oMDat.getColumnName(c).toLowerCase();
206         if (sColNameLCase.startsWith(sLCaseTr)) {
207           aCols.add(sColNameLCase);
208           if (sColNameLCase.equalsIgnoreCase(DB.tr_country_en) || sColNameLCase.equalsIgnoreCase(DB.tr_country_es))
209             oCols.append(","+oMDat.getColumnName(c));
210           else
211             oCols.append(","+CNul(oMDat.getColumnName(c))+" AS "+oMDat.getColumnName(c));
212         } // fi (sColNameLCase startsWith sLCaseTr)
213 } // next
214 oRSet.close();
215       oStmt.close();
216
217       oCountries = new DBSubset(DB.k_lu_countries, oCols.toString(), null, 256);
218       iRows = oCountries.load(oConn);
219       iCols = oCountries.getColumnCount();
220
221       for (int c=1; c<iCols; c++) {
222         oCountries.sortBy(c);
223         for (int r=0; r<iRows; r++)
224           oHTML.append("<OPTION VALUE=\"" + oCountries.getString(0,r).trim() + "\">" + oCountries.getStringNull(c,r,"") + "</OPTION>");
225         String sColName = (String ) aCols.get(c);
226         String sCntryCd = sColName.substring(sColName.lastIndexOf('_')+1);
227         if (DebugFile.trace) DebugFile.writeln("caching "+sCntryCd);
228         oCountryCache.put(sCntryCd, oHTML.toString());
229         oHTML.setLength(0);
230       }
231
232       bCountries = true;
233     }
234
235     String sRetVal;
236     if (oCountryCache.containsKey(sIdLanguage.toLowerCase()))
237       sRetVal = (String ) oCountryCache.get(sIdLanguage.toLowerCase());
238     else
239       sRetVal = "";
240
241     if (DebugFile.trace)
242       {
243       DebugFile.decIdent();
244       DebugFile.writeln("End DBLanguages.getHTMLCountrySelect() : "+String.valueOf(sRetVal.length()) + " characters");
245       }
246
247     return sRetVal;
248   } // getHTMLCountrySelect()
249
250   // ----------------------------------------------------------
251
252   /**
253    * <p>Get an HTML ComboBox options with translated labels for a standard hipergate lookup table.</p>
254    * This method goes directly to database without any intermediate cache or temporary storage object.
255    * @param oConn Database connection
256    * @param sTableName Lookup table name
257    * @param sOwnerId WorkArea for filtering results
258    * @param sSectionId Name of section (field) to retrieve
259    * @param sDefaultValue Default selected value, if null then there is no default
260    * @param sLanguage 2 chracters language code for ComboBox texts (see k_lu_languages table)
261    * @return <OPTION VALUE="...">...</OPTION><OPTION VALUE="...">...</OPTION>...
262    * @throws SQLException
263    * @since 3.0
264    */
265   public static String getHTMLSelectLookUp(JDCConnection oConn, String sTableName,
266                                            String sOwnerId, String sSectionId,
267                                            String sLanguage, String sDefaultValue)
268     throws SQLException {
269
270     StringBuffer oBuff = new StringBuffer (2048);
271     PreparedStatement oStmt;
272     ResultSet oRSet;
273     String sValue;
274
275     if (DebugFile.trace) {
276       DebugFile.writeln("Begin DBLanguages.getHTMLSelectLookUp([Connection], " + sTableName + "," + sOwnerId + "," + sSectionId + "," + sLanguage + ")");
277       DebugFile.incIdent();
278       DebugFile.writeln("Connection.prepareStatement(SELECT " + DB.vl_lookup + "," + DB.tr_ + sLanguage.toLowerCase() + " FROM " + sTableName + " WHERE " + DB.gu_owner + "='" + sOwnerId + "' AND " + DB.id_section + "='" + sSectionId + "' ORDER BY 2)");
279     }
280
281     oStmt = oConn.prepareStatement("SELECT " + DB.vl_lookup + "," + DB.tr_ + sLanguage.toLowerCase() + " FROM " + sTableName + " WHERE " + DB.gu_owner + "=? AND " + DB.id_section + "=? ORDER BY 2", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
282
283     try {
284       if (oConn.getDataBaseProduct()!=JDCConnection.DBMS_POSTGRESQL) {
285         oStmt.setQueryTimeout(20);
286         oStmt.setFetchSize(100);
287       }
288     } catch (SQLException sqle) {
289       if (DebugFile.trace) DebugFile.writeln(sqle.getMessage());
290     }
291
292     oStmt.setString(1, sOwnerId);
293     oStmt.setString(2, sSectionId);
294     oRSet = oStmt.executeQuery();
295     if (null==sDefaultValue) {
296       while (oRSet.next()) {
297         oBuff.append("<OPTION VALUE=\"");
298         sValue = oRSet.getString(1);
299         if (!oRSet.wasNull()) oBuff.append(sValue);
300         oBuff.append("\">");
301         sValue = oRSet.getString(2);
302         if (!oRSet.wasNull()) oBuff.append(sValue);
303         oBuff.append("</OPTION>");
304       } // wend()
305 } else {
306       while (oRSet.next()) {
307         oBuff.append("<OPTION VALUE=\"");
308         sValue = oRSet.getString(1);
309         if (!oRSet.wasNull()) oBuff.append(sValue);
310         oBuff.append("\"");
311         if (sDefaultValue.equals(sValue)) oBuff.append(" SELECTED=\"selected\"");
312           oBuff.append(">");
313         sValue = oRSet.getString(2);
314         if (!oRSet.wasNull()) oBuff.append(sValue);
315         oBuff.append("</OPTION>");
316       } // wend()
317 }
318     oRSet.close();
319     oStmt.close();
320
321     oRSet = null;
322     oStmt = null;
323
324     if (DebugFile.trace) {
325       DebugFile.writeln("End DBLanguages.getHTMLSelectLookUp() : " + String.valueOf(oBuff.length()));
326       DebugFile.decIdent();
327     }
328
329     return oBuff.toString();
330   } // getHTMLSelectLookUp()
331
332   // ----------------------------------------------------------
333
334   /**
335    * <p>Get an HTML ComboBox options with translated labels for a standard hipergate lookup table.</p>
336    * This method goes directly to database without any intermediate cache or temporary storage object.
337    * @param oConn Database connection
338    * @param sTableName Lookup table name
339    * @param sOwnerId WorkArea for filtering results
340    * @param sSectionId Name of section (field) to retrieve
341    * @param sLanguage 2 chracters language code for ComboBox texts (see k_lu_languages table)
342    * @return <OPTION VALUE="...">...</OPTION><OPTION VALUE="...">...</OPTION>...
343    * @throws SQLException
344    */
345   public static String getHTMLSelectLookUp(JDCConnection oConn, String sTableName,
346                                            String sOwnerId, String sSectionId,
347                                            String sLanguage) throws SQLException {
348     return getHTMLSelectLookUp(oConn, sTableName, sOwnerId, sSectionId, sLanguage, null);
349   }
350
351   // ----------------------------------------------------------
352
353   /**
354    * <p>Get an HTML ComboBox options with translated labels for a standard
355    * hipergate lookup table.</p>
356    * This method first checks the DistributedCachePeer for a matching ResultSet,
357    * then goes to database if ResultSet is not cached. Result is placed at cache
358    * as a DBSubset object with key sTableName.sSectionId[sOwnerId].
359    * @param oCache Local cache peer
360    * @param oConn Database connection
361    * @param sTableName Lookup table name
362    * @param sOwnerId WorkArea for filtering results
363    * @param sSectionId Name of section (field) to retrieve
364    * @param sLanguage 2 chracters language code for ComboBox texts (see k_lu_languages table)
365    * @return <OPTION VALUE="...">...</OPTION><OPTION VALUE="...">...</OPTION>...
366    * @throws SQLException
367    * @see com.knowgate.cache.DistributedCachePeer
368    */
369
370   public static String getHTMLSelectLookUp(DistributedCachePeer oCache, JDCConnection oConn,
371                                            String sTableName, String sOwnerId,
372                                            String sSectionId, String sLanguage)
373     throws RemoteException ,SQLException {
374
375     StringBuffer oBuff = new StringBuffer (2048);
376     DBSubset oDBSS;
377     int iDBSS;
378     Object aFilter[] = { sOwnerId, sSectionId };
379
380     if (DebugFile.trace) {
381       DebugFile.writeln("Begin DBLanguages.getHTMLSelectLookUp([DistributedCacheClient], [Connection], " + sTableName + "," + sOwnerId + "," + sSectionId + "," + sLanguage + ")");
382       DebugFile.incIdent();
383     }
384
385     oDBSS = oCache.getDBSubset(sTableName + "." + sSectionId + "[" + sOwnerId + "]");
386
387     if (null==oDBSS) {
388       if (oConn!=null) {
389         oDBSS = new DBSubset(sTableName,
390                              DB.vl_lookup + "," + DB.tr_ + sLanguage.toLowerCase() + "," + DB.pg_lookup,
391                              DB.gu_owner + "=? AND " + DB.id_section + "=? ORDER BY 2",
392                              100);
393         iDBSS = oDBSS.load(oConn, aFilter);
394         oCache.putDBSubset(sTableName, sTableName + "." + sSectionId + "[" + sOwnerId + "]", oDBSS);
395       } else {
396         iDBSS = 0;
397       }
398     } // fi(oDBSS)
399 else
400       iDBSS = oDBSS.getRowCount();
401
402     for (int r=0; r<iDBSS; r++) {
403
404       oBuff.append("<OPTION VALUE=\"");
405       oBuff.append(oDBSS.getStringNull(0,r,""));
406       oBuff.append("\">");
407       oBuff.append(oDBSS.getStringNull(1,r,""));
408       oBuff.append("</OPTION>");
409     } // next()
410
411     if (DebugFile.trace) {
412       DebugFile.writeln("End DBLanguages.getHTMLSelectLookUp() : " + String.valueOf(oBuff.length()));
413       DebugFile.decIdent();
414     }
415
416     return oBuff.toString();
417   } // getHTMLSelectLookUp()
418
419   // ----------------------------------------------------------
420
421   /**
422    * <p>Get a translated label for a lookup value.</p>
423    * @param oConn Database Connection
424    * @param sTableName Lookup table name
425    * @param sOwnerId WorkArea GUID
426    * @param sSectionId Section name (field)
427    * @param sLanguage Language code for retrieved label
428    * @param sLookupId Lookup value to find
429    * @return Translated label of lookup value or <b>null</b> if label for such language was not found
430    * @throws SQLException
431    */
432   public static String getLookUpTranslation(Connection oConn, String sTableName, String sOwnerId, String sSectionId, String sLanguage, String sLookupId) throws SQLException {
433
434     PreparedStatement oStmt;
435     ResultSet oRSet;
436     String sValue;
437
438     if (DebugFile.trace) {
439       DebugFile.writeln("Begin DBLanguages.getLookUpTranslation([Connection]," + sTableName + "," + sOwnerId + "," + sSectionId + "," + sLanguage + "," + sLookupId);
440       DebugFile.incIdent();
441       DebugFile.writeln("Connection.prepareStatement(SELECT " + DB.tr_ + sLanguage + " FROM " + sTableName + " WHERE " + DB.gu_owner + "=? AND " + DB.id_section + "=? AND " + DB.vl_lookup + "=?)");
442     }
443
444     oStmt = oConn.prepareStatement("SELECT " + DB.tr_ + sLanguage + " FROM " + sTableName + " WHERE " + DB.gu_owner + "=? AND " + DB.id_section + "=? AND " + DB.vl_lookup + "=?", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
445     oStmt.setString(1, sOwnerId);
446     oStmt.setString(2, sSectionId);
447     oStmt.setString(3, sLookupId);
448     oRSet = oStmt.executeQuery();
449     if (oRSet.next())
450       sValue = oRSet.getString(1);
451     else
452       sValue = null;
453     oRSet.close();
454     oStmt.close();
455
456     oRSet = null;
457     oStmt = null;
458
459     if (DebugFile.trace) {
460       DebugFile.decIdent();
461       DebugFile.writeln("End DBLanguages.getLookUpTranslation() : " + (sValue!=null ? sValue : "null"));
462     }
463
464     return sValue;
465   } // getLookUpTranslation
466
467   // ----------------------------------------------------------
468
469   /**
470    * <p>Get a Map of lookup values and their corresponding translated labels for a language.</p>
471    * This method is to be used when a listing routine has to lookup several values
472    * at a base table for their translated lookup labels. Instead of joining the base table and
473    * the lookup table, a memory map may be fetched first and then the painting routine translates
474    * each value into its labels without any database access.
475    * @param oConn Database connection
476    * @param sTableName Lookup table name
477    * @param sOwnerId WorkArea GUID
478    * @param sSectionId Section name (field)
479    * @param sLanguage Language code for retrieved labels
480    * @return A Map associating looukp values (as keys) with values for translated labels into the given language.
481    * @throws SQLException
482    */
483   public HashMap getLookUpMap(Connection oConn, String sTableName, String sOwnerId, String sSectionId, String sLanguage) throws SQLException {
484
485     if (DebugFile.trace) {
486       DebugFile.writeln("Begin DBLanguages.getLookUpMap([Connection], " + sTableName + "," + sOwnerId + "," + sSectionId + "," + sLanguage + ")");
487       DebugFile.incIdent();
488     }
489
490     HashMap oMap = new HashMap ();
491
492     String sSQL = "SELECT " + DB.vl_lookup + "," + DB.tr_ + sLanguage + " FROM " + sTableName + " WHERE " + DB.gu_owner + "=? AND " + DB.id_section + "=?";
493
494     if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement(SELECT " + DB.vl_lookup + "," + DB.tr_ + sLanguage + " FROM " + sTableName + " WHERE " + DB.gu_owner + "='" + sOwnerId + "' AND " + DB.id_section + "='" + sSectionId + "')");
495
496     PreparedStatement oStmt = oConn.prepareStatement(sSQL, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
497     oStmt.setString(1, sOwnerId);
498     oStmt.setString(2, sSectionId);
499     ResultSet oRSet = oStmt.executeQuery();
500
501     while (oRSet.next()) {
502       oMap.put(oRSet.getObject(1), oRSet.getObject(2));
503     } // wend
504
505     oRSet.close();
506     oStmt.close();
507
508     oRSet = null;
509     oStmt = null;
510
511     if (DebugFile.trace) {
512       DebugFile.decIdent();
513       DebugFile.writeln("End DBLanguages.getLookUpMap() : " + String.valueOf(oMap.size()));
514     }
515
516     return oMap;
517   } // getLookUpMap()
518
519   // ----------------------------------------------------------
520
521   /**
522    * Find value of next unused lookup index for a given table section
523    * @param oConn Connection Opened JDBC database connection
524    * @param sLookupTableName String Look up table name (ex. k_companies_lookup)
525    * @param sGuOwner String GUID of WorkArea to which the new lookup value will belong
526    * @param sIdSection String Name of section (usually corresponding column name at base table)
527    * @return int Next unused lookup index (pg_lookup column at sLookupTableName)
528    * @since 3.0
529    */
530   public static int nextLookuUpProgressive(Connection oConn, String sLookupTableName,
531                                            String sGuOwner, String sIdSection)
532     throws SQLException {
533
534     if (DebugFile.trace) {
535       DebugFile.writeln("Begin DBLanguages.nextLookuUpProgressive([Connection], " + sLookupTableName + "," + sGuOwner + "," + sIdSection + ")");
536       DebugFile.incIdent();
537       DebugFile.writeln("Connection.prepareStatement(SELECT MAX(" + DB.pg_lookup + ")+1 FROM " + sLookupTableName + " WHERE " + DB.gu_owner + "='"+sGuOwner+"' AND " + DB.id_section + "="+sIdSection+")");
538     }
539
540     int iNextPg;
541     PreparedStatement oStmt = oConn.prepareStatement("SELECT MAX(" + DB.pg_lookup + ")+1 FROM " + sLookupTableName + " WHERE " + DB.gu_owner + "=? AND " + DB.id_section + "=?",
542                                                      ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
543     oStmt.setString(1, sGuOwner);
544     oStmt.setString(2, sIdSection);
545     ResultSet oRSet = oStmt.executeQuery();
546     if (oRSet.next()) {
547       Object oNextPg = oRSet.getObject(1);
548       if (oRSet.wasNull())
549         iNextPg = 1;
550       else
551         iNextPg = Integer.parseInt(oNextPg.toString());
552     }
553     else
554       iNextPg = 1;
555     oRSet.close();
556     oStmt.close();
557
558     if (DebugFile.trace) {
559       DebugFile.decIdent();
560       DebugFile.writeln("End DBLanguages.nextProgressive() : " + String.valueOf(iNextPg));
561     }
562
563     return iNextPg;
564   } // nextProgressive
565
566   // ----------------------------------------------------------
567
568   /**
569    * <p>Add a lookup value for a given section</p>
570    * This methods checks whether the lookup value exists and, if not, then inserts it.<br>
571    * If lookup value already exists then it is not updated.
572    * @param oConn JDCConnection
573    * @param sLookupTableName String Name of Lookup Table
574    * @param sGuOwner String GUID of Owner WorkArea
575    * @param sIdSection String Lookup Section name
576    * @param sVlLookUp String Lookup Internal Value
577    * @param oTranslatMap HashMap with one entry for each language.
578    * Language codes must be those from id_language column of k_lu_languages table.
579    * @return boolean <b>true</b> if value was added, <b>false</b> if it already existed
580    * @throws SQLException
581    * @since 3.0
582    */
583
584   public static boolean addLookup (Connection oConn, String sLookupTableName,
585                                    String sGuOwner, String sIdSection,
586                                    String sVlLookUp, HashMap oTranslatMap)
587     throws SQLException {
588
589     if (DebugFile.trace) {
590       DebugFile.writeln("Begin DBLanguages.addLookup([Connection], " + sLookupTableName + "," + sGuOwner + "," + sIdSection + "," + sVlLookUp + ", [HashMap])");
591       DebugFile.incIdent();
592       DebugFile.writeln("Connection.prepareStatement(SELECT NULL FROM "+sLookupTableName+" WHERE "+DB.gu_owner+"='"+sGuOwner+"' AND "+DB.id_section+"='"+sIdSection+"' AND "+DB.vl_lookup+"='"+sVlLookUp+"')");
593     }
594     ResultSet oRSet;
595     PreparedStatement oStmt = oConn.prepareStatement("SELECT NULL FROM "+sLookupTableName+" WHERE "+DB.gu_owner+"=? AND "+DB.id_section+"=? AND "+DB.vl_lookup+"=?",
596                                                      ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
597     oStmt.setString(1, sGuOwner);
598     oStmt.setString(2, sIdSection);
599     oStmt.setString(3, sVlLookUp);
600     oRSet = oStmt.executeQuery();
601     boolean bAlreadyExists = oRSet.next();
602     oRSet.close();
603     oStmt.close();
604
605     if (!bAlreadyExists) {
606       if (DebugFile.trace) DebugFile.writeln("lookup does not already exists");
607
608       oStmt = oConn.prepareStatement("SELECT "+DBBind.Functions.ISNULL+"(MAX("+DB.pg_lookup+")+1,1) FROM "+sLookupTableName+" WHERE "+DB.gu_owner+"=? AND "+DB.id_section+"=?",
609                                      ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
610       oStmt.setString(1, sGuOwner);
611       oStmt.setString(2, sIdSection);
612       oRSet = oStmt.executeQuery();
613       oRSet.next();
614       int iNextPg = oRSet.getInt(1);
615       oRSet.close();
616       oStmt.close();
617
618       int iQuestMarks = 1;
619       String sSQL = "INSERT INTO "+sLookupTableName+"("+DB.gu_owner+","+DB.id_section+","+DB.pg_lookup+","+DB.vl_lookup;
620       Iterator oKeys = oTranslatMap.keySet().iterator();
621       while (oKeys.hasNext()) {
622         sSQL += ","+DB.tr_+oKeys.next();
623         iQuestMarks++;
624       } // wend
625
626       sSQL += ") VALUES (?,?,?,?";
627       for (int q=1; q<iQuestMarks; q++) sSQL += ",?";
628       sSQL += ")";
629       if (DebugFile.trace) DebugFile.writeln("Connection.prepareStatement("+sSQL+")");
630       oStmt = oConn.prepareStatement(sSQL);
631       int iParam = 1;
632       oStmt.setString(iParam++, sGuOwner);
633       oStmt.setString(iParam++, sIdSection);
634       oStmt.setInt(iParam++, iNextPg);
635       oStmt.setString(iParam++, sVlLookUp);
636       oKeys = oTranslatMap.keySet().iterator();
637       while (oKeys.hasNext()) {
638         oStmt.setObject(iParam++, oTranslatMap.get(oKeys.next()), Types.VARCHAR);
639       } // wend
640 if (DebugFile.trace) DebugFile.writeln("PreparedStatement.executeUpdate()");
641       oStmt.executeUpdate();
642       oStmt.close();
643     } // fi
644
645     if (DebugFile.trace) {
646       DebugFile.decIdent();
647       DebugFile.writeln("End DBLanguages.addLookup() : " + String.valueOf(!bAlreadyExists));
648     }
649
650     return !bAlreadyExists;
651   } // addLookup
652
653   // ----------------------------------------------------------
654
655   /**
656    * <p>Add or update a lookup value for a given section</p>
657    * This methods checks whether the lookup value exists and, if not, then inserts it.<br>
658    * If lookup value already exists then it is updated.
659    * @param oConn JDCConnection
660    * @param sLookupTableName String Name of Lookup Table
661    * @param sGuOwner String GUID of Owner WorkArea
662    * @param sIdSection String Lookup Section name
663    * @param sVlLookUp String Lookup Internal Value
664    * @param oTranslations HashMap with one entry for each language.
665    * Language codes must be those from id_language column of k_lu_languages table.
666    * @return boolean <b>true</b> if value was added, <b>false</b> if it already existed
667    * @throws SQLException
668    * @since 3.0
669    */
670
671   public static void storeLookup (Connection oConn, String sLookupTableName,
672                                   String sGuOwner, String sIdSection,
673                                   String sVlLookUp, HashMap oTranslations)
674     throws SQLException {
675
676     if (DebugFile.trace) {
677       DebugFile.writeln("Begin DBLanguages.storeLookup([Connection], " + sLookupTableName + "," + sGuOwner + "," + sIdSection + "," + sVlLookUp + ", [HashMap])");
678       DebugFile.incIdent();
679     }
680
681     if (!addLookup(oConn, sLookupTableName, sGuOwner, sIdSection, sVlLookUp, oTranslations)) {
682       String sSQL = "";
683       Iterator oKeys = oTranslations.keySet().iterator();
684       while (oKeys.hasNext()) {
685         sSQL += (sSQL.length()>0 ? "," : "")+DB.tr_+oKeys.next()+"=?";
686       } // wend
687 sSQL += " WHERE "+DB.gu_owner+"=? AND "+DB.id_section+"=? AND "+DB.vl_lookup+"=?";
688       if (DebugFile.trace)
689         DebugFile.writeln("Connection.prepareStatement(UPDATE "+sLookupTableName+" SET "+sSQL+")");
690       PreparedStatement oStmt = oConn.prepareStatement("UPDATE "+sLookupTableName+" SET "+sSQL);
691       oKeys = oTranslations.keySet().iterator();
692       int iParam = 1;
693       while (oKeys.hasNext()) {
694         oStmt.setObject(iParam++, oTranslations.get(oKeys.next()), Types.VARCHAR);
695       } // wend
696 oStmt.setString(iParam++, sGuOwner);
697       oStmt.setString(iParam++, sIdSection);
698       oStmt.setString(iParam++, sVlLookUp);
699       oStmt.executeUpdate();
700       oStmt.close();
701     }
702
703     if (DebugFile.trace) {
704       DebugFile.decIdent();
705       DebugFile.writeln("End DBLanguages.storeLookup()");
706     }
707   } // storeLookup
708
709   // ----------------------------------------------------------
710
711   /**
712    * Delete lookup value
713    * @param oConn Connection
714    * @param sLookupTableName String Name of Lookup Table
715    * @param sBaseTable String Base table which column named like sIdSection will be set to null
716    * @param sGuOwner String GUID of Owner WorkArea
717    * @param sIdSection String Lookup Section name
718    * @param sVlLookUp String Lookup Internal Value
719    * @throws SQLException
720    * @since 3.0
721    */
722   public static void deleteLookup (Connection oConn, String sLookupTableName,
723                                    String sBaseTable, String sGuOwner,
724                                    String sIdSection, String sVlLookUp)
725     throws SQLException {
726     PreparedStatement oStmt;
727
728     if (DebugFile.trace) {
729       DebugFile.writeln("Begin DBLanguages.deleteLookup([Connection], " + sLookupTableName + "," + sBaseTable + "," + sGuOwner + "," + sIdSection + "," + sVlLookUp + ")");
730       DebugFile.incIdent();
731     }
732
733     if (null!=sBaseTable) {
734       if (DebugFile.trace)
735         DebugFile.writeln("Connection.prepareStatement(UPDATE "+sBaseTable+" SET "+sIdSection+"=NULL "+"WHERE "+sIdSection+"='"+sIdSection+"' AND "+DB.gu_workarea+"='"+sGuOwner+"')");
736       oStmt = oConn.prepareStatement("UPDATE "+sBaseTable+" SET "+sIdSection+"=NULL "+"WHERE "+sIdSection+"=? AND "+DB.gu_workarea+"=?");
737       oStmt.setString(1, sIdSection);
738       oStmt.setString(2, sGuOwner);
739       oStmt.executeUpdate();
740       oStmt.close();
741     }
742     if (DebugFile.trace)
743       DebugFile.writeln("Connection.prepareStatement(DELETE FROM "+sLookupTableName+" WHERE "+DB.gu_owner+"='"+sGuOwner+"' AND "+DB.id_section+"='"+sIdSection+"' AND "+DB.vl_lookup+"='"+sVlLookUp+"')");
744     oStmt = oConn.prepareStatement("DELETE FROM "+sLookupTableName+" WHERE "+DB.gu_owner+"=? AND "+DB.id_section+"=? AND "+DB.vl_lookup+"=?");
745     oStmt.setString(1, sGuOwner);
746     oStmt.setString(2, sIdSection);
747     oStmt.setString(3, sVlLookUp);
748     oStmt.executeUpdate();
749     oStmt.close();
750
751     if (DebugFile.trace) {
752       DebugFile.decIdent();
753       DebugFile.writeln("End DBLanguages.deleteLookup()");
754     }
755   } // deleteLookup
756
757   // ----------------------------------------------------------
758
759   /**
760    * Delete lookup value
761    * @param oConn Connection
762    * @param sLookupTableName String Name of Lookup Table
763    * @param sBaseTable String Base table which column named like sIdSection will be set to null
764    * @param sGuOwner String GUID of Owner WorkArea
765    * @param sIdSection String Lookup Section name
766    * @param iPgLookUp int Lookup Ordinal Value
767    * @throws SQLException
768    * @since 3.0
769    */
770   public static void deleteLookup (Connection oConn, String sLookupTableName,
771                                    String sBaseTable, String sGuOwner,
772                                    String sIdSection, int iPgLookUp)
773     throws SQLException {
774     PreparedStatement oStmt;
775
776     if (DebugFile.trace) {
777       DebugFile.writeln("Begin DBLanguages.deleteLookup([Connection], " + sLookupTableName + "," + sBaseTable + "," + sGuOwner + "," + sIdSection + "," + String.valueOf(iPgLookUp) + ")");
778       DebugFile.incIdent();
779     }
780
781     if (null!=sBaseTable) {
782       if (DebugFile.trace)
783         DebugFile.writeln("Connection.prepareStatement(UPDATE "+sBaseTable+" SET "+sIdSection+"=NULL "+"WHERE "+sIdSection+"='"+sIdSection+"' AND "+DB.gu_workarea+"='"+sGuOwner+"'");
784       oStmt = oConn.prepareStatement("UPDATE "+sBaseTable+" SET "+sIdSection+"=NULL "+"WHERE "+sIdSection+"=? AND "+DB.gu_workarea+"=?");
785       oStmt.setString(1, sIdSection);
786       oStmt.setString(2, sGuOwner);
787       oStmt.executeUpdate();
788       oStmt.close();
789     }
790     if (DebugFile.trace)
791         DebugFile.writeln("Connection.prepareStatement(DELETE FROM "+sLookupTableName+" WHERE "+DB.gu_owner+"='"+sGuOwner+"' AND "+DB.id_section+"="+sIdSection+" AND "+DB.pg_lookup+"="+iPgLookUp+"");
792     oStmt = oConn.prepareStatement("DELETE FROM "+sLookupTableName+" WHERE "+DB.gu_owner+"=? AND "+DB.id_section+"=? AND "+DB.pg_lookup+"=?");
793     oStmt.setString(1, sGuOwner);
794     oStmt.setString(2, sIdSection);
795     oStmt.setInt(3, iPgLookUp);
796     oStmt.executeUpdate();
797     oStmt.close();
798
799     if (DebugFile.trace) {
800       DebugFile.decIdent();
801       DebugFile.writeln("End DBLanguages.deleteLookup()");
802     }
803   } // deleteLookup
804
805   // ----------------------------------------------------------
806
807   private DBSubset oTranslations;
808   private DBSubset oCountries;
809   private HashMap oHTMLCache;
810   private HashMap oCountryCache;
811   private boolean bLoaded;
812   private boolean bCountries;
813 }
814

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags