Java > Open Source Codes > smile > stored > MySqlDataStore


1 /*
2  * MySqlDataStore is part of the Cofax content management system library.
3  *
4  * This library is free software; you can redistribute it and/or
5  * modify it under the terms of the GNU Lesser General Public
6  * License as published by the Free Software Foundation; either
7  * version 2.1 of the License, or (at your option) any later version.
8  *
9  * This library is distributed in the hope that it will be useful,
10  * but WITHOUT ANY WARRANTY; without even the implied warranty of
11  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12  * Lesser General Public License for more details.
13  *
14  * You should have received a copy of the GNU Lesser General Public
15  * License along with this library; if not, write to the Free Software
16  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
17  *
18  * Please see http://www.cofax.org for contact information and other related informaion.
19  *
20  * $Header: /cvsroot/cofax/cofax/src/smile/stored/MySqlDataStore.java,v 1.13.2.1 2006/12/11 16:26:04 fxrobin Exp $
21  */
22
23 package smile.stored;
24
25 import java.util.*;
26 import java.util.Date ;
27 import java.io.*;
28 import java.sql.*;
29 import org.cofax.*;
30
31 import org.cofax.connectionpool.*;
32
33 /**
34  * MySqlDataStore is an implementation of org.cofax.DataStore. Copyright 2002
35  * Smile Les motoristes Internet http://www.smile.fr/ Contact cofax@smile.fr for
36  * further information
37  *
38  * @author Smile Les motoristes Internet
39  * @created April 19, 2002
40  */
41 public class MySqlDataStore extends org.cofax.DataStore {
42
43     private boolean writeAccess;
44
45     // flag for wether the database can be updated or not
46 private boolean connected;
47
48     // flag for wether the current session is activly connected
49 private ConnectionWrapper connection;
50
51     // global database connection to share
52 static CofaxCache cacheOfPackageTags;
53
54     static PoolManager poolMgr;
55
56     /**
57      * Hashmap into a determination of if stored procedures contain clobs is
58      * placed to speed transactions.
59      */
60     public static HashMap clobColumns = new HashMap();
61
62     /**
63      * Hashmap into which the cache results parameter (1,0) is thrown upon
64      * initialization.
65      */
66     public static HashMap dbPackageTagsCacheCommand = new HashMap();
67
68     /**
69      * Hashmap into which package tags may be placed into memory upon
70      * initialization.
71      */
72     public static HashMap dbPackageTags = new HashMap();
73
74     /**
75      * Hashmap into which package tag results are cached.
76      */
77     public static HashMap dbPackageTagsCache = new HashMap();
78
79     /**
80      * Description of the Method
81      */
82     public void init() {
83         log("initializing javaDS");
84         poolMgr = PoolManager.getInstance();
85     }
86
87     /**
88      * Initializes the connection pool.
89      *
90      * @param configFile
91      * Description of the Parameter
92      */
93
94     public void init(String configFile) {
95         // TODO: J2EE: javax.naming.Context ctx = new InitialContext();
96 // TODO: J2EE: javax.sql.DataSource poolMgr =
97 // (DataSource)ctx.lookup("jdbc/cofax");
98 poolMgr = PoolManager.getInstance(configFile);
99     }
100
101     /**
102      * To be ran once before using the data store. With a pre-loaded Properties
103      * object.
104      *
105      * @param dbProps
106      * Description of the Parameter
107      */
108     public void init(Properties dbProps) {
109         // TODO: J2EE: javax.naming.Context ctx = new InitialContext();
110 // TODO: J2EE: javax.sql.DataSource poolMgr =
111 // (DataSource)ctx.lookup("jdbc/cofax");
112
113         poolMgr = PoolManager.getInstance(dbProps);
114
115     }
116
117     /**
118      * Initializes variables.
119      */
120     public MySqlDataStore() {
121
122         connected = false;
123         writeAccess = false;
124
125     }
126
127     /**
128      * Set's this data store's cache object and caches all package tags.
129      *
130      * @param cache
131      * The new cache value
132      * @param whichCache
133      * The new cache value
134      */
135     public void setCache(Object cache, int whichCache) {
136         switch (whichCache) {
137         case 1:
138             cacheOfPackageTags = (CofaxCache) cache;
139             // packageTags?
140 break;
141         case 2:
142             // processed packageTags
143 dbPackageTags = (HashMap) cache;
144             break;
145         case 3:
146             // packageTag results
147 dbPackageTagsCache = (HashMap) cache;
148             break;
149         case 4:
150             // clob columns
151 clobColumns.clear();
152             break;
153         case 5:
154             // packageTag cache command
155 dbPackageTagsCacheCommand = (HashMap) cache;
156             break;
157         default:
158             break;
159         }
160         // cacheOfPackageTags = cache;
161 return;
162     }
163
164     /**
165      * Description of the Method
166      *
167      * @param whichCache
168      * Description of the Parameter
169      */
170     public void clearCache(int whichCache) {
171
172         switch (whichCache) {
173         case 1:
174             break;
175         case 2:
176             // processed packageTags
177 dbPackageTags.clear();
178             break;
179         case 3:
180             // packageTag results
181 dbPackageTagsCache.clear();
182             break;
183         case 4:
184             // clob columns
185 clobColumns.clear();
186             break;
187         case 5:
188             // packageTag cache command
189 dbPackageTagsCacheCommand.clear();
190             break;
191         default:
192             break;
193         }
194         // log("SqlDataStore clearPackageCache clearing package cache.");
195 return;
196     }
197
198     /**
199      * Gets the cacheValue attribute of the MySqlDataStore object
200      *
201      * @param key
202      * Description of the Parameter
203      * @param whichCache
204      * Description of the Parameter
205      * @return The cacheValue value
206      */
207     public Object getCacheValue(Object key, int whichCache) {
208         String tag = (String ) key;
209         String value = null;
210         switch (whichCache) {
211         case 1:
212             // packageTags?
213 break;
214         case 2:
215             // processed packageTags
216 value = (String ) dbPackageTags.get(tag);
217             if ((value == null) || (value.equals("")))
218                 log("[ERROR] PackageTag '" + tag + "' not found");
219             break;
220         case 3:
221             // packageTag results
222 break;
223         case 4:
224             // clob columns
225 break;
226         case 5:
227             // packageTag cache command
228 break;
229         default:
230             break;
231         }
232
233         return value;
234     }
235
236     /**
237      * Connects to the database using a connection established in the database
238      * poo
239      *
240      * @param dbPool
241      * Description of the Parameter
242      * @return Description of the Return Value
243      */
244     public final boolean connect() {
245
246         setLastError("");
247         String dbPool = getDataStoreName();
248         // try to connect to data base
249 try {
250
251             connection = poolMgr.getConnection(dbPool);
252             connected = true;
253             setConnected(connected);
254
255             if (connection == null) {
256                 setLastError("Could not get connection from pool.");
257                 connected = false;
258             }
259         } catch (Exception e) {
260             try {
261                 if (connection != null) {
262                     connection.close();
263                 }
264             } catch (Exception e2) {
265             }
266
267             setLastError("Connection failed, url bad or user name " + "and password not accepted" + e.getMessage());
268
269             connected = false;
270             setConnected(connected);
271         }
272
273         return connected;
274     }
275
276     /**
277      * Description of the Method
278      */
279     public void initPool() {
280
281         /*
282          * try { /Context ctx = new InitialContext(); /ds = (DataSource)
283          * ctx.lookup("jndi-goon"); ds =
284          * com.codestudio.sql.PoolMan.findDataSource("jndi-goon"); } catch
285          * (Exception e) { e.printStackTrace(); }
286          */
287         return;
288     }
289
290     /**
291      * Connects to the database using a connection established in the database
292      * poo
293      *
294      * @return Description of the Return Value
295      */
296     public final boolean connectFromPool() {
297         /*
298          * setLastError(""); / try to connect to data base try { connection =
299          * ds.getConnection(); connected = true ; if (connection == null) {
300          * setLastError("Could not get connection from pool."); connected =
301          * false ; } } catch (Exception e) { try { if (connection != null)
302          * connection.close(); } catch (SQLException e2) {}
303          * setLastError("Connection failed, url bad or user name " + "and
304          * password not accepted"); e.printStackTrace(); connected = false ; }
305          * return connected ;
306          */
307         return false;
308     }
309
310     /**
311      * Connects to the database using a connection established in the database
312      * pool properties file.
313      *
314      * @param input
315      * Description of the Parameter
316      * @return Description of the Return Value
317      */
318
319     /*
320      * public final boolean connect(String dbPool) { setLastError(""); / try to
321      * connect to data base try { connection = poolMgr.getConnection(dbPool);
322      * connected = true ; if (connection == null) { setLastError("Could not get
323      * connection from pool."); connected = false ; } } catch (Exception e) {
324      * try { if (connection != null) connection.close(); } catch (Exception e2) {}
325      * setLastError("Connection failed, url bad or user name " + "and password
326      * not accepted" + e.getMessage()); connected = false ; } return connected ; }
327      */
328
329     /**
330      * Makes a sting sql ready by surrounding it with singlequotes, replacing
331      * internal single quotes, or making a NULL value. TO DO: This should use
332      * the database driver's internal method.
333      *
334      * @param input
335      * Description of the Parameter
336      * @return Description of the Return Value
337      */
338     private final String prepSqlString(String input) {
339
340         if (input != null) {
341             return "'" + CofaxUtil.replace(input, "'", "''") + "'";
342         } else {
343             return "NULL";
344         }
345
346     }
347
348     /**
349      * Replaces internal single quotes, or making a NULL value.
350      *
351      * @param input
352      * Description of the Parameter
353      * @return Description of the Return Value
354      */
355     private final String sqlEscape(String input) {
356
357         if (input != null) {
358             return CofaxUtil.replace(input, "'", "''");
359         } else {
360             return "NULL";
361         }
362
363     }
364
365     /**
366      * Gets the sql string that is used for the packageTag request and parses it
367      * with it's parameters.
368      *
369      * @param tag
370      * Description of the Parameter
371      * @param params
372      * Description of the Parameter
373      * @param servPage
374      * Description of the Parameter
375      * @param servAddr
376      * Description of the Parameter
377      * @return The packageTag value
378      */
379     public final String getPackageTag(String tag, HashMap params, String servPage, String servAddr) {
380
381         String tagStatement = null;
382         setError(false);
383
384         if (cacheOfPackageTags != null && cacheOfPackageTags.isPageCached(tag)) {
385             cacheOfPackageTags.incrementHitCount(tag);
386             tagStatement = cacheOfPackageTags.getPage(tag).toString();
387         } else {
388
389             // Get tag meaning
390 String sqlString = "SELECT tag_value FROM tblpackagetags " + "WHERE tag_name = '" + tag + "'";
391
392             try {
393                 // Get the tag value
394 Statement sql = connection.createStatement();
395                 connection.setCurrentActivity("Building: " + servPage + " For: " + servAddr + " Query: " + sqlString);
396                 sql.execute(sqlString);
397                 ResultSet result = sql.getResultSet();
398                 if (result.next()) {
399                     tagStatement = result.getString(1);
400                 } else {
401                     tagStatement = "";
402                 }
403                 result.close();
404                 sql.close();
405             } catch (SQLException e) {
406                 setLastError(e.toString());
407                 setError(true);
408             }
409
410             if (cacheOfPackageTags != null) {
411                 cacheOfPackageTags.incrementHitCount(tag);
412                 CofaxPage packageTagContainer = new CofaxPage();
413                 packageTagContainer.append(tagStatement);
414                 cacheOfPackageTags.addPageToCache(tag, packageTagContainer);
415             }
416         }
417
418         // Replace values matched in glossary to complete the tag's meaning
419 String searchFor;
420
421         // Replace values matched in glossary to complete the tag's meaning
422 String replaceWith;
423         for (Iterator tagArgs = params.keySet().iterator(); tagArgs.hasNext();) {
424
425             searchFor = (String ) tagArgs.next();
426             replaceWith = sqlEscape(params.get(searchFor).toString());
427
428             tagStatement = CofaxUtil.replace(tagStatement, searchFor, replaceWith);
429         }
430
431         return tagStatement;
432     }
433
434     /**
435      * Returns a set of content by tag. Prefixes returned names of values with
436      * the tag name.
437      *
438      * @param tag
439      * Description of the Parameter
440      * @param tagStatement
441      * Description of the Parameter
442      * @param servPage
443      * Description of the Parameter
444      * @param servAddr
445      * Description of the Parameter
446      * @return The packageData value
447      */
448     public final List getPackageData(String tag, String tagStatement, String servPage, String servAddr) {
449
450         ArrayList articles = new ArrayList();
451         int help = 0;
452         setError(false);
453
454         if (tagStatement != null && !tagStatement.equals("")) {
455             Statement sql = null;
456             ResultSet result = null;
457             try {
458                 // load the articles
459 int i;
460
461                 sql = connection.createStatement();
462                 connection.setCurrentActivity("Building: " + servPage + " For: " + servAddr + " Query: " + tagStatement);
463                 sql.execute(tagStatement);
464                 result = sql.getResultSet();
465
466                 /* FX : testing if resultset and metadata are not null */
467                 ResultSetMetaData meta = null;
468                 if (result != null)
469                     meta = result.getMetaData(); // FX : here
470
471                 int cols = 0;
472                 if (meta != null)
473                     cols = meta.getColumnCount(); // FX : here
474
475                 while (result != null && result.next()) { // FX : here
476 HashMap article = new HashMap();
477                     for (i = 1; i <= cols; i++) {
478                         String val = result.getString(meta.getColumnLabel(i));
479                         if (val == null) {
480                             val = "";
481                         }
482                         if (!tag.equals("")) {
483                             article.put(tag + ":" + meta.getColumnLabel(i) + "", val + "");
484                         } else {
485                             article.put(meta.getColumnLabel(i) + "", val + "");
486                         }
487                     }
488                     articles.add(article);
489                 }
490
491                 result.close();
492                 sql.close();
493
494             } catch (SQLException e) {
495                 setLastError(e.toString());
496                 setError(true);
497                 return articles;
498             } finally {
499                 try {
500                     if (result != null) {
501                         result.close();
502                     }
503                     if (sql != null) {
504                         sql.close();
505                     }
506                 } catch (Exception e) {
507                     log("exception while closing stmt and resultset");
508                 }
509             }
510         }
511         return articles;
512     }
513
514     /**
515      * Returns a set of content by tag for stored procedures, inserts, updates,
516      * or selects. Handles clobs both on the way in and the way out.
517      *
518      * @param data
519      * Description of the Parameter
520      * @param tagName
521      * Description of the Parameter
522      * @param tagData
523      * Description of the Parameter
524      * @param init
525      * Description of the Parameter
526      * @return The packageData value
527      */
528     public final List getPackageData(HashMap data, String tagName, String tagData, boolean init) {
529
530         // variable to be set to true if tag needs to be cached and
531 // results have not yet been cached.
532 boolean cacheThisCall = false;
533
534         // determine whether the package tag results should be already cached
535 boolean cacheMe = false;
536         if (init == false) {
537             String ifCache = "0";
538             try {
539                 ifCache = (String ) dbPackageTagsCacheCommand.get(tagData);
540
541             } catch (Exception e) {
542                 log("MySqlDataStore getPackageData ERROR: " + "dbPackageTagsCacheCommand is null: " + e);
543             }
544             if ((ifCache != null) && (ifCache.equals("1"))) {
545                 cacheMe = true;
546             } else {
547                 cacheMe = false;
548             }
549         }
550
551         // create a list to be populated with hashmaps of data
552 List articles = new ArrayList();
553
554         // append a colon to tag if tagname exists so it
555 // will be prepended to column names when resultset is delivered
556 String tag = "";
557         if ((tagName == null) && (!tagName.equals(""))) {
558             tag = tagName + ":";
559         }
560
561         Statement sql = null;
562         ResultSet result = null;
563         ProcedureInterface v_stored = null;
564
565         try {
566             // fulfill package tag with HashMap data
567 tagData = fillToolsTag(data, tagData);
568
569             // check to see if the results are already cached
570 // if they are, return them
571 // if not, make sure that they get cached at the end of this routine
572 if ((init == false) && (cacheMe == true)) {
573                 if (dbPackageTagsCache.get(tagData) != null) {
574                     return ((ArrayList) dbPackageTagsCache.get(tagData));
575                 } else {
576                     cacheThisCall = true;
577                 }
578             }
579
580             int i;
581
582             String psName = getPsName(tagData);
583             if (psName != null) {
584                 // && CofaxToolsServlet.javaProcList.containsKey(psName))
585
586                 // if (targetDataStore.equalsIgnoreCase("mssql")){
587 // in order to get the psname :example we have in
588 // tbltoolspackagetags goon.dbo.s_psname
589 // we want to get only s_psname -> substring(9)
590 // psName = psName.substring(9);
591 // }
592 try {
593                     Class v_class = Class.forName("smile.stored." + psName);
594                     v_stored = (ProcedureInterface) v_class.newInstance();
595                     v_stored.init(data, connection);
596
597                     v_stored.setTargetDataStore("mssql");
598
599                     v_stored.execute();
600                     result = v_stored.getResultSet();
601                 } catch (ClassNotFoundException e) {
602                     log("MySqlDataStore : ERROR loading StoredProcedure class:");
603                 } catch (InstantiationException e) {
604                     log("MySqlDataStore : ERROR loading StoredProcedure class:");
605                 } catch (IllegalAccessException e) {
606                     log("MySqlDataStore : ERROR loading StoredProcedure class:");
607                 }
608             } else {
609                 // initialise le statement
610 // puis execution
611 sql = connection.createStatement();
612                 sql.execute(tagData);
613                 result = sql.getResultSet();
614             }
615
616             ResultSetMetaData meta = null;
617             if (result != null)
618                 meta = result.getMetaData();// FX : checking if query returns a
619 // resultset
620
621             int cols = 0;
622             if (meta != null)
623                 cols = meta.getColumnCount(); // FX : checking if query
624 // returns metadata;
625
626             // The Hashmap is filled only for SELECT statements
627 if (tagData.substring(0, 6).equalsIgnoreCase("select") || tagData.substring(0, 4).equalsIgnoreCase("exec")) {
628                 while (result != null && result.next()) // FX : testing the
629 // result before
630 {
631                     HashMap article = new HashMap();
632                     for (i = 1; i <= cols; i++) {
633                         String val = result.getString(meta.getColumnLabel(i));
634                         if (val == null) {
635                             val = "";
636                         }
637                         if (!tag.equals("")) {
638                             article.put(tag + ":" + meta.getColumnLabel(i).toUpperCase() + "", val + "");
639                         } else {
640                             article.put(meta.getColumnLabel(i).toUpperCase() + "", val + "");
641                         }
642                     }
643                     articles.add(article);
644                 }
645             }
646             // else {
647 // log("[WARNING] MySqlDataStore.getPackageData is not retrieving
648 // recordset for statement '" + tagData + "'.");
649 // }
650
651         } catch (Exception e) {
652             setError(true);
653             setLastError("MySqlDataStore getPackageData ERROR: " + e + " Query = " + tagData);
654             System.err.print(new Date ().toString());
655             e.printStackTrace(System.err);
656         } finally {
657             try {
658                 if (v_stored != null) {
659                     v_stored.close();
660                 }
661                 if (result != null) {
662                     result.close();
663                 }
664                 if (sql != null) {
665                     sql.close();
666                 }
667             } catch (SQLException sqle) {
668                 log("exception while trying to close resultset or statement");
669             }
670         }
671
672         if (cacheThisCall == true) {
673             dbPackageTagsCache.put(tagData, articles);
674         }
675         return articles;
676     }
677
678     // getPackageData
679
680     /**
681      * Free's a connection from the database pool.
682      *
683      * @param dataStoreName
684      * Description of the Parameter
685      * @return Description of the Return Value
686      */
687
688     public final boolean disConnect() {
689
690         // try to disconnect from the data base
691 try {
692             connection.close();
693         } catch (Exception e) {
694             setLastError("Could not disconnect from the database: " + e.toString());
695             return false;
696         }
697         connected = false;
698         setConnected(connected);
699         return true;
700     }
701
702     /**
703      * Description of the Method
704      */
705     public void destroy() {
706
707         poolMgr.release();
708
709     }
710
711     /**
712      * Purpose: The main insertArticle routine.
713      *
714      * @param article
715      * Description of the Parameter
716      * @param approvedBy
717      * Description of the Parameter
718      * @param xmlFileType
719      * Description of the Parameter
720      * @param mappings
721      * Description of the Parameter
722      * @param relatedLinks
723      * Description of the Parameter
724      * @return Description of the Return Value
725      */
726     public final boolean insertArticle(HashMap article, String approvedBy, String xmlFileType, ArrayList mappings, ArrayList relatedLinks) {
727
728         setLastError("");
729         String sqlString = "";
730         Statement sql;
731
732         // check for active connection ;
733 if (!connected) {
734             setLastError("Tried to insert an article with no connection.\n");
735             return false;
736         }
737
738         if (xmlFileType.equals("article")) {
739
740             // Insert new article
741 sqlString = "s_insertArticle " + "'" + CofaxUtil.getString(article, "article:pubName") + "', "
742                     + prepSqlString(CofaxUtil.getString(article, "article:section")) + ", " + prepSqlString(CofaxUtil.getString(article, "article:pubDate"))
743                     + ", " + prepSqlString(CofaxUtil.getString(article, "article:fileName")) + ", " + "'" + approvedBy + "'," + "\n"
744                     + prepSqlString(CofaxUtil.getString(article, "article:headline")) + ", " + "\n"
745                     + prepSqlString(CofaxUtil.getString(article, "article:headline2")) + ", " + "\n"
746                     + prepSqlString(CofaxUtil.getString(article, "article:lead")) + ", " + "\n" + prepSqlString(CofaxUtil.getString(article, "article:byline"))
747                     + ", " + "\n" + prepSqlString(CofaxUtil.getString(article, "article:bycredit")) + ", " + "\n"
748                     + prepSqlString(CofaxUtil.getString(article, "article:dateline")) + ", " + "\n"
749                     + prepSqlString(CofaxUtil.getString(article, "article:body")) + ", " + "\n" + prepSqlString(CofaxUtil.getString(article, "article:memo"))
750                     + ", " + "\n" + prepSqlString(CofaxUtil.getString(article, "article:webHeadline")) + ", " + "\n"
751                     + prepSqlString(CofaxUtil.getString(article, "article:keywords")) + ", " + "\n" + "'', \n"
752                     + prepSqlString(CofaxUtil.getString(article, "article:pubRank")) + ", " + "\n"
753                     + prepSqlString(CofaxUtil.getString(article, "article:pubData")) + ", " + "\n"
754                     + CofaxUtil.getIntegerAsString(article, "article:disableIndex") + ", " + "0, " + "0, "
755                     + prepSqlString(CofaxUtil.getString(article, "article:articleType")) + ", \n"
756                     + CofaxUtil.getIntegerAsString(article, "article:noVersioning") + " \n";
757
758         } else if (xmlFileType.equals("multimediaInfo")) {
759             sqlString = "s_insertRelatedMultimedia " + "'" + CofaxUtil.getString(article, "pubName") + "', "
760                     + prepSqlString(CofaxUtil.getString(article, "section")) + ", " + prepSqlString(CofaxUtil.getString(article, "pubDate")) + ", "
761                     + prepSqlString(CofaxUtil.getString(article, "fileName")) + ", " + prepSqlString(CofaxUtil.getString(article, "type")) + ", " + "\n"
762                     + prepSqlString(CofaxUtil.getString(article, "itemName")) + ", " + "\n" + prepSqlString(CofaxUtil.getString(article, "caption")) + ", "
763                     + "\n" + prepSqlString(CofaxUtil.getString(article, "mimeType")) + ", " + "\n" + CofaxUtil.getString(article, "rank") + "\n";
764
765             System.out.println("\n" + sqlString + "\n");
766
767             return (false);
768         }
769
770         try {
771
772             sql = connection.createStatement();
773             sql.execute(sqlString);
774             ResultSet result = sql.getResultSet();
775             int itemID;
776             if (result.next()) {
777                 itemID = result.getInt(1);
778             } else {
779                 itemID = 0;
780             }
781             result.close();
782
783             sql.close();
784
785             // If we have mappings... insert them
786 if (mappings.size() > 0) {
787                 System.out.println("GOT MAPPINGS FOR ITEMID: " + itemID);
788                 Iterator it = mappings.iterator();
789                 while (it.hasNext()) {
790                     HashMap mapping = (HashMap) it.next();
791                     sqlString = "s_insertMapFromCode " + "'" + CofaxUtil.getString(article, "article:pubName") + "', " + itemID + ", " + "'"
792                             + CofaxUtil.getString(mapping, "map:section") + "', " + CofaxUtil.getString(mapping, "map:rank") + ", " + "'"
793                             + CofaxUtil.getString(mapping, "map:timeStart") + "'," + "'" + CofaxUtil.getString(mapping, "map:timeEnd") + "' \n ";
794
795                     sql = connection.createStatement();
796                     sql.executeUpdate(sqlString);
797                     sql.close();
798                 }
799             }
800
801             // if we have links... insert them
802
803             if (relatedLinks.size() > 0) {
804
805                 System.out.println("GOT LINKS FOR ITEMID: " + itemID);
806
807                 Iterator it = relatedLinks.iterator();
808                 while (it.hasNext()) {
809
810                     HashMap link = (HashMap) it.next();
811                     // First call delete just in case it already exists
812 sqlString = "s_deleteRelatedLinkByItemID " + itemID + ", " + "'" + CofaxUtil.getString(link, "link:url") + "' \n";
813
814                     sql = connection.createStatement();
815
816                     sql.executeUpdate(sqlString);
817
818                     sql.close();
819
820                     // Then insert the link
821 sqlString = "s_insertRelatedLinkByItemID " + itemID + ", " + "'" + CofaxUtil.getString(link, "link:url") + "', " + "'"
822                             + CofaxUtil.getString(link, "link:linkText") + "', " + CofaxUtil.getString(link, "link:rank") + "\n";
823
824                     sql = connection.createStatement();
825
826                     sql.executeUpdate(sqlString);
827
828                     sql.close();
829
830                 }
831
832             }
833
834         } catch (SQLException e) {
835             setLastError("There was an SQL error inserting article: \n" + e.toString() + "\n");
836             return (false);
837         }
838
839         return (true);
840     }
841
842     /**
843      * Logs to a file or to System.out - basic function is to concentrate error
844      * checking.
845      *
846      * @param input
847      * Description of the Parameter
848      */
849     private static void log(String input) {
850         input = "smile.stored.MySqlDataStore - " + new Date ().toString() + " - " + input;
851         if (DataStore.dataLog.equals("1")) {
852             if (!DataStore.dataLogLocation.equals("")) {
853                 try {
854                     if (DataStore.dataLogMaxSize > 0) {
855                         File file = new File(DataStore.dataLogLocation);
856                         long fileSize = file.length();
857                         if (fileSize > DataStore.dataLogMaxSize) {
858                             file.delete();
859                         }
860                     }
861                     PrintWriter fileOutputStream = new PrintWriter(new FileWriter(DataStore.dataLogLocation, true));
862                     fileOutputStream.println(input);
863                     fileOutputStream.close();
864                 } catch (IOException e) {
865                     log(e.toString());
866                 }
867             } else {
868                 System.out.println("ODS log: " + input);
869             }
870         }
871     }
872
873     /**
874      * Populates A SQL String with query values from a HashMap when String is
875      * formatted as: select req:value from etc. Duplex method exists for package
876      * tags that have fulfillable values.
877      *
878      * @param ht
879      * Description of the Parameter
880      * @param tag
881      * Description of the Parameter
882      * @return Description of the Return Value
883      */
884     public static String fillToolsTag(HashMap ht, String tag) {
885
886         Iterator keys = ht.keySet().iterator();
887         // First, replace all values that are provided in the hash
888 while (keys.hasNext()) {
889             String key = "";
890             String value = "";
891
892             try {
893                 key = (String ) keys.next();
894                 // log("MySqlDataStore fillToolsTag, line 834: key = " + key);
895 value = ht.get(key).toString();
896                 // log("MySqlDataStore fillToolsTag, line 837: value = " +
897 // value);
898 if (value == null) {
899                     value = "";
900                 }
901             } catch (Exception e) {
902                 log("MySqlDataStore fillToolsTag encountered an error in HashMap ht: " + e);
903             }
904
905             int firstPos = 0;
906             int lastPos = 0;
907
908             // check to see which index of space, ", or ' is closest to the tag
909 HashMap hti = checkInstance(tag, key);
910             firstPos = Integer.parseInt((String ) hti.get("firstPos"));
911             lastPos = Integer.parseInt((String ) hti.get("lastPos"));
912             while (firstPos > -1) {
913
914                 // we never found an ending character for particluar key,
915 // because it wasn't inluded in tag
916 if (firstPos == -1) {
917                     log("searching for key " + key);
918                 }
919
920                 String first = tag.substring(0, firstPos);
921                 String last = tag.substring(lastPos);
922                 value = CofaxUtil.replace(value, "'", "''");
923                 tag = first + value + last;
924
925                 HashMap hto = checkInstance(tag, key);
926                 firstPos = Integer.parseInt((String ) hto.get("firstPos"));
927                 lastPos = Integer.parseInt((String ) hto.get("lastPos"));
928             }
929         }
930
931         // Any remaining values in the tag? Kill them.
932 int firstPos = tag.indexOf("req:");
933         while (firstPos >= 0) {
934             int nextPos = 0;
935             // check to see which index of space, ", or ' is closest to the tag
936 int whichPos1 = tag.indexOf(" ", firstPos + 1);
937             int whichPos2 = tag.indexOf("\"", firstPos + 1);
938             int whichPos3 = tag.indexOf("\'", firstPos + 1);
939             int whichPos4 = tag.indexOf(",", firstPos + 1);
940             int whichPos5 = tag.indexOf(")", firstPos + 1);
941             int whichPos6 = tag.indexOf(";", firstPos + 1);
942             int whichPos7 = tag.indexOf("\r", firstPos + 1);
943             if (whichPos1 == -1) {
944                 whichPos1 = 1000000;
945             }
946             if (whichPos2 == -1) {
947                 whichPos2 = 1000000;
948             }
949             if (whichPos3 == -1) {
950                 whichPos3 = 1000000;
951             }
952             if (whichPos4 == -1) {
953                 whichPos4 = 1000000;
954             }
955             if (whichPos5 == -1) {
956                 whichPos5 = 1000000;
957             }
958             if (whichPos6 == -1) {
959                 whichPos6 = 1000000;
960             }
961             if (whichPos7 == -1) {
962                 whichPos7 = 1000000;
963             }
964
965             // set the next position to split on to that character
966 if ((whichPos1 < whichPos2) && (whichPos1 < whichPos3) && (whichPos1 < whichPos4) && (whichPos1 < whichPos5) && (whichPos1 < whichPos6)
967                     && (whichPos1 < whichPos7)) {
968                 nextPos = whichPos1;
969             } else if ((whichPos2 < whichPos1) && (whichPos2 < whichPos3) && (whichPos2 < whichPos4) && (whichPos2 < whichPos5) && (whichPos2 < whichPos6)
970                     && (whichPos2 < whichPos7)) {
971                 nextPos = whichPos2;
972             } else if ((whichPos3 < whichPos1) && (whichPos3 < whichPos2) && (whichPos3 < whichPos4) && (whichPos3 < whichPos5) && (whichPos3 < whichPos6)
973                     && (whichPos3 < whichPos7)) {
974                 nextPos = whichPos3;
975             } else if ((whichPos4 < whichPos1) && (whichPos4 < whichPos2) && (whichPos4 < whichPos3) && (whichPos4 < whichPos5) && (whichPos4 < whichPos6)
976                     && (whichPos4 < whichPos7)) {
977                 nextPos = whichPos4;
978             } else if ((whichPos5 < whichPos1) && (whichPos5 < whichPos2) && (whichPos5 < whichPos3) && (whichPos5 < whichPos4) && (whichPos5 < whichPos6)
979                     && (whichPos5 < whichPos7)) {
980                 nextPos = whichPos5;
981             } else if ((whichPos6 < whichPos1) && (whichPos6 < whichPos2) && (whichPos6 < whichPos3) && (whichPos6 < whichPos4) && (whichPos6 < whichPos5)
982                     && (whichPos6 < whichPos7)) {
983                 nextPos = whichPos6;
984             } else if ((whichPos7 < whichPos1) && (whichPos7 < whichPos2) && (whichPos7 < whichPos3) && (whichPos7 < whichPos4) && (whichPos7 < whichPos5)
985                     && (whichPos7 < whichPos6)) {
986                 nextPos = whichPos7;
987             } else {
988                 // malformed tag - we never found an ending character
989 log("ERROR at CofaxToolsDbUtils setTag route 2 = " + "indexEndBoundary: Tag malformed" + tag);
990             }
991             // end if()...
992 String first = tag.substring(0, firstPos);
993             String last = tag.substring(nextPos);
994             tag = first + last;
995             firstPos = tag.indexOf("req:");
996         }
997         return (tag.trim());
998     }
999
1000    /**
1001     * Description of the Method
1002     *
1003     * @param tag
1004     * Description of the Parameter
1005     * @param key
1006     * Description of the Parameter
1007     * @return Description of the Return Value
1008     */
1009    public static HashMap checkInstance(String tag, String key) {
1010        int firstPos = -1;
1011        String temp = "";
1012        int firstPos1 = tag.indexOf("req:" + key + " ");
1013        int firstPos2 = tag.indexOf("req:" + key + "\"");
1014        int firstPos3 = tag.indexOf("req:" + key + "\'");
1015        int firstPos4 = tag.indexOf("req:" + key + ",");
1016        int firstPos5 = tag.indexOf("req:" + key + ")");
1017        int firstPos6 = tag.indexOf("req:" + key + ";");
1018        int firstPos7 = tag.indexOf("req:" + key + "\r");
1019
1020        if (firstPos1 > -1) {
1021            firstPos = firstPos1;
1022            temp = "req:" + key + " ";
1023        } else if (firstPos2 > -1) {
1024            firstPos = firstPos2;
1025            temp = "req:" + key + "\"";
1026        } else if (firstPos3 > -1) {
1027            firstPos = firstPos3;
1028            temp = "req:" + key + "\'";
1029        } else if (firstPos4 > -1) {
1030            firstPos = firstPos4;
1031            temp = "req:" + key + ",";
1032        } else if (firstPos5 > -1) {
1033            firstPos = firstPos5;
1034            temp = "req:" + key + ")";
1035        } else if (firstPos6 > -1) {
1036            firstPos = firstPos6;
1037            temp = "req:" + key + ";";
1038        } else if (firstPos7 > -1) {
1039            firstPos = firstPos7;
1040            temp = "req:" + key + "\r";
1041        }
1042        HashMap ht = new HashMap();
1043        int lastPos = firstPos + temp.length() - 1;
1044        ht.put("lastPos", String.valueOf(lastPos));
1045        ht.put("firstPos", String.valueOf(firstPos));
1046        return ht;
1047    }
1048
1049    /**
1050     * Gets the psName attribute of the MySqlDataStore object
1051     *
1052     * @param tagStatement
1053     * Description of the Parameter
1054     * @return The psName value
1055     */
1056    public String getPsName(String tagStatement) {
1057        StringTokenizer v_st = new StringTokenizer(tagStatement, " ");
1058        String firstToken = "";
1059        String psName = null;
1060        if (v_st.hasMoreTokens()) {
1061            firstToken = v_st.nextToken();
1062        }
1063        if (v_st.hasMoreTokens() && firstToken.equalsIgnoreCase("exec")) {
1064            psName = v_st.nextToken();
1065        }
1066        return psName;
1067    }
1068
1069    /**
1070     * Gets the poolConnectionStats attribute of the MySqlDataStore object
1071     *
1072     * @param name
1073     * Description of the Parameter
1074     * @return The poolConnectionStats value
1075     */
1076    public String getPoolConnectionStats(String name) {
1077
1078        return poolMgr.getConnectionStats(name);
1079    }
1080
1081    /**
1082     * Sets the uRL attribute of the MySqlDataStore object
1083     *
1084     * @param in
1085     * The new uRL value
1086     */
1087    public void setURL(String in) {
1088        poolMgr.setURL(in);
1089    }
1090
1091    /**
1092     * Sets the user attribute of the MySqlDataStore object
1093     *
1094     * @param in
1095     * The new user value
1096     */
1097    public void setUser(String in) {
1098        poolMgr.setUser(in);
1099    }
1100
1101    /**
1102     * Sets the password attribute of the MySqlDataStore object
1103     *
1104     * @param in
1105     * The new password value
1106     */
1107    public void setPassword(String in) {
1108        poolMgr.setPassword(in);
1109    }
1110
1111    /**
1112     * Sets the maxConns attribute of the MySqlDataStore object
1113     *
1114     * @param in
1115     * The new maxConns value
1116     */
1117    public void setMaxConns(int in) {
1118        poolMgr.setMaxConns(in);
1119    }
1120
1121    /**
1122     * Sets the initConns attribute of the MySqlDataStore object
1123     *
1124     * @param in
1125     * The new initConns value
1126     */
1127    public void setInitConns(int in) {
1128        poolMgr.setInitConns(in);
1129    }
1130
1131    /**
1132     * Sets the timeOut attribute of the MySqlDataStore object
1133     *
1134     * @param in
1135     * The new timeOut value
1136     */
1137    public void setTimeOut(int in) {
1138        poolMgr.setTimeOut(in);
1139    }
1140
1141    /**
1142     * Sets the conUsageLimit attribute of the MySqlDataStore object
1143     *
1144     * @param in
1145     * The new conUsageLimit value
1146     */
1147    public void setConUsageLimit(int in) {
1148        poolMgr.setConUsageLimit(in);
1149    }
1150
1151    /**
1152     * Sets the killTime attribute of the MySqlDataStore object
1153     *
1154     * @param in
1155     * The new killTime value
1156     */
1157    public void setKillTime(long in) {
1158        poolMgr.setKillTime(in);
1159    }
1160
1161    /**
1162     * Gets the uRL attribute of the MySqlDataStore object
1163     *
1164     * @return The uRL value
1165     */
1166    public String getURL() {
1167        return poolMgr.getURL();
1168    }
1169
1170    /**
1171     * Gets the user attribute of the MySqlDataStore object
1172     *
1173     * @return The user value
1174     */
1175    public String getUser() {
1176        return poolMgr.getUser();
1177    }
1178
1179    /**
1180     * Gets the password attribute of the MySqlDataStore object
1181     *
1182     * @return The password value
1183     */
1184    public String getPassword() {
1185        return poolMgr.getPassword();
1186    }
1187
1188    /**
1189     * Gets the maxConns attribute of the MySqlDataStore object
1190     *
1191     * @return The maxConns value
1192     */
1193    public int getMaxConns() {
1194        return poolMgr.getMaxConns();
1195    }
1196
1197    /**
1198     * Gets the initConns attribute of the MySqlDataStore object
1199     *
1200     * @return The initConns value
1201     */
1202    public int getInitConns() {
1203        return poolMgr.getInitConns();
1204    }
1205
1206    /**
1207     * Gets the timeOut attribute of the MySqlDataStore object
1208     *
1209     * @return The timeOut value
1210     */
1211    public int getTimeOut() {
1212        return poolMgr.getTimeOut();
1213    }
1214
1215    /**
1216     * Gets the conUsageLimit attribute of the MySqlDataStore object
1217     *
1218     * @return The conUsageLimit value
1219     */
1220    public int getConUsageLimit() {
1221        return poolMgr.getConUsageLimit();
1222    }
1223
1224    /**
1225     * Gets the killTime attribute of the MySqlDataStore object
1226     *
1227     * @return The killTime value
1228     */
1229    public long getKillTime() {
1230        return poolMgr.getKillTime();
1231    }
1232
1233    public HashMap searchArticles(HashMap htParams) {
1234        StringBuffer thisSearch = new StringBuffer ();
1235        StringBuffer SQLState = new StringBuffer ();
1236        String startDate = (String ) htParams.get("startDate");
1237        String stopDate = (String ) htParams.get("stopDate");
1238        String section = (String ) htParams.get("section");
1239        String mapping = (String ) htParams.get("mapping");
1240        String field1 = (String ) htParams.get("field1");
1241        String condition1 = (String ) htParams.get("condition1");
1242        String fieldtwo1 = (String ) htParams.get("fieldtwo1");
1243        String field2 = (String ) htParams.get("field2");
1244        String condition2 = (String ) htParams.get("condition2");
1245        String fieldtwo2 = (String ) htParams.get("fieldtwo2");
1246        String field3 = (String ) htParams.get("field3");
1247        String condition3 = (String ) htParams.get("condition3");
1248        String fieldtwo3 = (String ) htParams.get("fieldtwo3");
1249        String results = (String ) htParams.get("results");
1250        String orderBy = (String ) htParams.get("orderBy");
1251        String startDatemonth = (String ) htParams.get("startDatemonth");
1252        String startDateday = (String ) htParams.get("startDateday");
1253        String startDateyear = (String ) htParams.get("startDateyear");
1254        String stopDatemonth = (String ) htParams.get("stopDatemonth");
1255        String stopDateday = (String ) htParams.get("stopDateday");
1256        String stopDateyear = (String ) htParams.get("stopDateyear");
1257        String publications[] = (String []) htParams.get("publications");
1258
1259        thisSearch.append("&startDatemonth=" + startDatemonth + "&startDateday=" + startDateday + "&startDateyear=" + startDateyear + "&stopDatemonth="
1260                + stopDatemonth + "&stopDateday=" + stopDateday + "&stopDateyear=" + stopDateyear + "&SECTION=" + section + "&mapping=" + mapping + "&field1="
1261                + field1 + "&condition1=" + condition1 + "&fieldtwo1=" + fieldtwo1 + "&field2=" + field2 + "&condition2=" + condition2 + "&fieldtwo2="
1262                + fieldtwo2 + "&field3=" + field3 + "&condition3=" + condition3 + "&fieldtwo3=" + fieldtwo3 + "&results=" + results + "&orderBy=" + orderBy);
1263
1264        SQLState.append("SELECT tblarticles.disableArticle, tblarticles.disableIndex, CONCAT(tblarticles.pubName, '/', ");
1265        SQLState.append("DATE_FORMAT(tblarticles.pubDate, '%Y/%m/%d/'), ");
1266        SQLState
1267                .append("tblsections.sectionName) as virtualFolder, tblarticles.section, tblarticles.pubName, tblarticles.pubDate, tblarticles.itemID, tblarticles.headline, tblarticles.fileName, tblarticles.versionNum, tblsections.sectionDesc, tblarticles.lead, tblarticles.byline, tblarticles.workflow_state FROM tblarticles, tblsections, tblarticleorder \n");
1268
1269        // deal with publication
1270 StringBuffer pubPart = new StringBuffer ();
1271        boolean complex = false;
1272        for (int i = 0; i < publications.length; i++) {
1273            if (i == 0) {
1274                pubPart.append("tblarticles.pubName = \'" + publications[i] + "\' \n");
1275                thisSearch.append("&publications=" + publications[i]);
1276            } else if (i > 0) {
1277                pubPart.append("OR tblarticles.pubName = \'" + publications[i] + "\' \n");
1278                thisSearch.append("&publications=" + publications[i]);
1279                complex = true;
1280            }
1281        }
1282        if (complex == true) {
1283            pubPart.insert(0, "WHERE tblarticles.itemID = tblarticleorder.itemID AND tblarticleorder.mappingCode = tblsections.mappingCode AND (");
1284            pubPart.append(")");
1285        } else if (complex == false) {
1286            pubPart.insert(0, "WHERE tblarticles.itemID = tblarticleorder.itemID AND tblarticleorder.mappingCode = tblsections.mappingCode AND ");
1287        }
1288
1289        SQLState.append(pubPart);
1290
1291        // deal with date
1292 if (stopDate.equals(startDate)) {
1293            try {
1294                SQLState.append("AND tblarticles.pubDate = \'" + CofaxUtil.dateToMySql(stopDate) + "\'\n");
1295            } catch (java.text.ParseException e) {
1296                log("CofaxToolsUtil searchArticle DATE FORMAT ERROR: " + e);
1297            }
1298        } else {
1299            try {
1300                SQLState.append("AND (tblarticles.pubDate >= \'" + CofaxUtil.dateToMySql(startDate) + "\' AND tblarticles.pubDate <= \'"
1301                        + CofaxUtil.dateToMySql(stopDate) + "\' )\n");
1302            } catch (java.text.ParseException e) {
1303                log("CofaxToolsUtil searchArticle DATE FORMAT ERROR: " + e);
1304            }
1305        }
1306
1307        // deal with section
1308 if (!section.equals("")) {
1309            SQLState.append("AND tblarticles.section = \'" + section + "\' \n");
1310        }
1311
1312        // deal with mapping code
1313 if (!mapping.equals("")) {
1314            SQLState.append("AND tblarticles.mappingCode = \'" + mapping + "\' \n");
1315        }
1316
1317        // deal with remainder of queries
1318 if ((!field1.equals("")) && (!condition1.equals("")) && (!fieldtwo1.equals(""))) {
1319            if (field1.equals("BODY")) {
1320                if ((condition1.equals("LIKE")) || (condition1.equals("="))) {
1321                    SQLState.append("AND tblarticles.BODY LIKE \'%" + fieldtwo1 + "%\' ");
1322                }
1323
1324                if ((condition1.equals("NOT LIKE")) || (condition1.equals("!="))) {
1325                    SQLState.append("AND BODY !LIKE \'%" + fieldtwo1 + "%\' ");
1326                }
1327            } else {
1328                if (condition1.indexOf("LIKE") > -1) {
1329                    fieldtwo1 = ("%" + fieldtwo1.toUpperCase() + "%");
1330                    SQLState.append("AND upper(" + field1 + ") " + condition1 + "\'" + fieldtwo1 + "\' ");
1331                } else {
1332                    SQLState.append("AND " + field1 + " " + condition1 + "\'" + fieldtwo1 + "\' ");
1333                }
1334            }
1335        }
1336
1337        if ((!field2.equals("")) && (!condition2.equals("")) && (!fieldtwo2.equals(""))) {
1338            if (field2.equals("BODY")) {
1339                if ((condition2.equals("LIKE")) || (condition2.equals("="))) {
1340                    SQLState.append("AND BODY LIKE \'%" + fieldtwo2 + "%\' ");
1341                }
1342
1343                if ((condition2.equals("NOT LIKE")) || (condition2.equals("!="))) {
1344                    SQLState.append("AND BODY !LIKE \'%" + fieldtwo2 + "%\' ");
1345                }
1346            } else {
1347                if (condition2.indexOf("LIKE") > -1) {
1348                    fieldtwo2 = ("%" + fieldtwo2.toUpperCase() + "%");
1349                    SQLState.append("AND upper(" + field2 + ") " + condition2 + "\'" + fieldtwo2 + "\' ");
1350                } else {
1351                    SQLState.append("AND " + field2 + " " + condition2 + "\'" + fieldtwo2 + "\' ");
1352                }
1353            }
1354        }
1355
1356        if ((!field3.equals("")) && (!condition3.equals("")) && (!fieldtwo3.equals(""))) {
1357            if (field3.equals("BODY")) {
1358                if ((condition3.equals("LIKE")) || (condition3.equals("="))) {
1359                    SQLState.append("AND BODY LIKE \'%" + fieldtwo3 + "%\' ");
1360                }
1361
1362                if ((condition3.equals("NOT LIKE")) || (condition3.equals("!="))) {
1363                    SQLState.append("AND BODY !LIKE \'%" + fieldtwo3 + "%\' ");
1364                }
1365            } else {
1366                if (condition3.indexOf("LIKE") > -1) {
1367                    fieldtwo3 = ("%" + fieldtwo3.toUpperCase() + "%");
1368                    SQLState.append("AND upper(" + field3 + ") " + condition3 + "\'" + fieldtwo3 + "\' ");
1369                } else {
1370                    SQLState.append("AND upper(" + field3 + ") " + condition3 + "\'" + fieldtwo3 + "\' ");
1371                }
1372            }
1373        }
1374
1375        SQLState.append("ORDER BY " + orderBy + " LIMIT " + results + "\n");
1376        HashMap htReturn = new HashMap();
1377        htReturn.put("SQLState", SQLState);
1378        htReturn.put("thisSearch", thisSearch);
1379        return htReturn;
1380    }
1381
1382    public String selectPackagetags() {
1383        return ("select TAG_NAME, TAG_VALUE from tbltoolspackagetags");
1384    }
1385
1386    public String selectPackagetagsCache() {
1387        return ("select TAG_VALUE, CACHE from tbltoolspackagetags");
1388    }
1389}
1390

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags