Java > Open Source Codes > org > compiere > model > MQuery


1 /******************************************************************************
2  * The contents of this file are subject to the Compiere License Version 1.1
3  * ("License"); You may not use this file except in compliance with the License
4  * You may obtain a copy of the License at http://www.compiere.org/license.html
5  * Software distributed under the License is distributed on an "AS IS" basis,
6  * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
7  * the specific language governing rights and limitations under the License.
8  * The Original Code is Compiere ERP & CRM Business Solution
9  * The Initial Developer of the Original Code is Jorg Janke and ComPiere, Inc.
10  * Portions created by Jorg Janke are Copyright (C) 1999-2002 Jorg Janke, parts
11  * created by ComPiere are Copyright (C) ComPiere, Inc.; All Rights Reserved.
12  * Contributor(s): ______________________________________.
13  *****************************************************************************/
14 package org.compiere.model;
15
16 import java.util.*;
17 import java.sql.*;
18
19 import org.compiere.util.*;
20 import java.io.*;
21
22 /**
23  * Query Descriptor.
24  * Maintains restrictions (WHERE clause)
25  *
26  * @author Jorg Janke
27  * @version $Id: MQuery.java,v 1.2 2003/02/15 06:32:50 jjanke Exp $
28  */
29 public class MQuery implements Serializable
30 {
31     /**
32      * Constructor w/o table name
33      */
34     public MQuery ()
35     {
36     } // MQuery
37
38     /**
39      * Constructor
40      * @param TableName Table Name
41      */
42     public MQuery (String TableName)
43     {
44         m_TableName = TableName;
45     } // MQuery
46
47     /**
48      * Constructor get TableNAme from Table
49      * @param AD_Table_ID Table_ID
50      */
51     public MQuery (int AD_Table_ID)
52     {
53         String sql = "SELECT TableName FROM AD_Table WHERE AD_Table_ID=?";
54         try
55         {
56             PreparedStatement pstmt = DB.prepareStatement(sql);
57             pstmt.setInt (1, AD_Table_ID);
58             ResultSet rs = pstmt.executeQuery();
59             if (rs.next())
60                 m_TableName = rs.getString(1);
61             else
62                 Log.error("MQuery - Did not find AD_Table_ID=" + AD_Table_ID);
63             rs.close();
64             pstmt.close();
65         }
66         catch (SQLException e)
67         {
68             Log.error("MQuery", e);
69         }
70     } // MQuery
71
72
73     /** Table Name */
74     private String m_TableName = "";
75     /** List of Restrictions */
76     private ArrayList m_list = new ArrayList();
77
78
79     /*************************************************************************/
80
81     public static final String EQUAL = "=";
82     public static final int EQUAL_INDEX = 0;
83     public static final String NOT_EQUAL = "!=";
84     public static final String LIKE = " LIKE ";
85     public static final String NOT_LIKE = " NOT LIKE ";
86     public static final String GREATER = ">";
87     public static final String GREATER_EQUAL = ">=";
88     public static final String LESS = "<";
89     public static final String LESS_EQUAL = "<=";
90     public static final String BETWEEN = " BETWEEN ";
91     public static final int BETWEEN_INDEX = 8;
92
93     public static final ValueNamePair[] OPERATORS = new ValueNamePair[] {
94         new ValueNamePair (EQUAL, " = "), // 0
95 new ValueNamePair (NOT_EQUAL, " != "),
96         new ValueNamePair (LIKE, " ~ "),
97         new ValueNamePair (NOT_LIKE, " !~ "),
98         new ValueNamePair (GREATER, " > "),
99         new ValueNamePair (GREATER_EQUAL, " >= "), // 5
100 new ValueNamePair (LESS, " < "),
101         new ValueNamePair (LESS_EQUAL, " <= "),
102         new ValueNamePair (BETWEEN, " >-< ") // 8
103 };
104
105     /*************************************************************************/
106
107     /**
108      * Add Restriction
109      * @param ColumnName ColumnName
110      * @param Operator Operator, e.g. = != ..
111      * @param Code Code, e.g 0, All%
112      * @param InfoName Display Name
113      * @param InfoDisplay Display of Code (Lookup)
114      */
115     public void addRestriction (String ColumnName, String Operator,
116         Object Code, String InfoName, String InfoDisplay)
117     {
118         Restriction r = new Restriction (ColumnName, Operator,
119             Code, InfoName, InfoDisplay);
120         m_list.add(r);
121     } // addRestriction
122
123     /**
124      * Add Restriction
125      * @param ColumnName ColumnName
126      * @param Operator Operator, e.g. = != ..
127      * @param Code Code, e.g 0, All%
128      */
129     public void addRestriction (String ColumnName, String Operator,
130         Object Code)
131     {
132         Restriction r = new Restriction (ColumnName, Operator,
133             Code, null, null);
134         m_list.add(r);
135     } // addRestriction
136
137     /**
138      * Add Restriction
139      * @param ColumnName ColumnName
140      * @param Operator Operator, e.g. = != ..
141      * @param Code Code, e.g 0
142      */
143     public void addRestriction (String ColumnName, String Operator,
144         int Code)
145     {
146         Restriction r = new Restriction (ColumnName, Operator,
147             new Integer (Code), null, null);
148         m_list.add(r);
149     } // addRestriction
150
151     /**
152      * Add Range Restriction (BETWEEN)
153      * @param ColumnName ColumnName
154      * @param Code Code, e.g 0, All%
155      * @param Code_to Code, e.g 0, All%
156      * @param InfoName Display Name
157      * @param InfoDisplay Display of Code (Lookup)
158      * @param InfoDisplay_to Display of Code (Lookup)
159      */
160     public void addRangeRestriction (String ColumnName,
161         Object Code, Object Code_to,
162         String InfoName, String InfoDisplay, String InfoDisplay_to)
163     {
164         Restriction r = new Restriction (ColumnName, Code, Code_to,
165             InfoName, InfoDisplay, InfoDisplay_to);
166         m_list.add(r);
167     } // addRestriction
168
169     /**
170      * Add Range Restriction (BETWEEN)
171      * @param ColumnName ColumnName
172      * @param Code Code, e.g 0, All%
173      * @param Code_to Code, e.g 0, All%
174      */
175     public void addRangeRestriction (String ColumnName,
176         Object Code, Object Code_to)
177     {
178         Restriction r = new Restriction (ColumnName, Code, Code_to,
179             null, null, null);
180         m_list.add(r);
181     } // addRestriction
182
183     /**
184      * Add Restriction
185      * @param r Restriction
186      */
187     protected void addRestriction (Restriction r)
188     {
189         m_list.add(r);
190     } // addRestriction
191
192     /**
193      * Add Restriction
194      * @param whereClause SQL WHERE clause
195      */
196     public void addRestriction (String whereClause)
197     {
198         if (whereClause == null || whereClause.trim().length() == 0)
199             return;
200         Restriction r = new Restriction (whereClause);
201         m_list.add(r);
202     } // addRestriction
203
204     /*************************************************************************/
205
206     /**
207      * Create the resulting Query WHERE Clause
208      * @return Where Clause
209      */
210     public String getWhereClause ()
211     {
212         return getWhereClause(false);
213     } // getWhereClause
214
215     /**
216      * Create the resulting Query WHERE Clause
217      * @param fullyQualified fully qualified Table.ColumnName
218      * @return Where Clause
219      */
220     public String getWhereClause (boolean fullyQualified)
221     {
222         boolean qualified = fullyQualified;
223         if (qualified && (m_TableName == null || m_TableName.length() == 0))
224             qualified = false;
225         //
226 StringBuffer sb = new StringBuffer ();
227         for (int i = 0; i < m_list.size(); i++)
228         {
229             Restriction r = (Restriction)m_list.get(i);
230             if (i != 0)
231                 sb.append(r.andCondition ? " AND " : " OR ");
232             if (qualified)
233                 sb.append(r.getSQL(m_TableName));
234             else
235                 sb.append(r.getSQL(null));
236         }
237         return sb.toString();
238     } // getWhereClause
239
240     /**
241      * Create Query WHERE Clause.
242      * Not fully qualified
243      * @param index restriction index
244      * @return Where Clause or "" if not valid
245      */
246     public String getWhereClause (int index)
247     {
248         StringBuffer sb = new StringBuffer ();
249         if (index >= 0 && index < m_list.size())
250         {
251             Restriction r = (Restriction)m_list.get(index);
252             sb.append(r.getSQL(null));
253         }
254         return sb.toString();
255     } // getWhereClause
256
257     /**
258      * Get Restriction Count
259      * @return number of restricctions
260      */
261     public int getRestrictionCount()
262     {
263         return m_list.size();
264     } // getRestrictionCount
265
266     /**
267      * Is Query Active
268      * @return true if number of restricctions > 0
269      */
270     public boolean isActive()
271     {
272         return m_list.size() != 0;
273     } // isActive
274
275     /**
276      * Get Table Name
277      * @return Table Name
278      */
279     public String getTableName ()
280     {
281         return m_TableName;
282     } // getTableName
283
284     /**
285      * Set Table Name
286      * @param TableName Table Name
287      */
288     public void setTableName (String TableName)
289     {
290         m_TableName = TableName;
291     } // setTableName
292
293     /*************************************************************************/
294
295     /**
296      * Get ColumnName of index
297      * @param index index
298      * @return ColumnName
299      */
300     public String getColumnName (int index)
301     {
302         if (index < 0 || index >= m_list.size())
303             return null;
304         Restriction r = (Restriction)m_list.get(index);
305         return r.ColumnName;
306     } // getColumnName
307
308     /**
309      * Set ColumnName of index
310      * @param index index
311      * @param ColumnName new column name
312      */
313     protected void setColumnName (int index, String ColumnName)
314     {
315         if (index < 0 || index >= m_list.size())
316             return;
317         Restriction r = (Restriction)m_list.get(index);
318         r.ColumnName = ColumnName;
319     } // setColumnName
320
321     /**
322      * Get Operator of index
323      * @param index index
324      * @return Operator
325      */
326     public String getOperator (int index)
327     {
328         if (index < 0 || index >= m_list.size())
329             return null;
330         Restriction r = (Restriction)m_list.get(index);
331         return r.Operator;
332     } // getOperator
333
334     /**
335      * Get Restriction Display of index
336      * @param index index
337      * @return Restriction Display
338      */
339     public String getInfoDisplay (int index)
340     {
341         if (index < 0 || index >= m_list.size())
342             return null;
343         Restriction r = (Restriction)m_list.get(index);
344         return r.InfoDisplay;
345     } // getOperator
346
347     /**
348      * Get TO Restriction Display of index
349      * @param index index
350      * @return Restriction Display
351      */
352     public String getInfoDisplay_to (int index)
353     {
354         if (index < 0 || index >= m_list.size())
355             return null;
356         Restriction r = (Restriction)m_list.get(index);
357         return r.InfoDisplay_to;
358     } // getOperator
359
360     /**
361      * Get Info Name
362      * @param index index
363      * @return Info Name
364      */
365     public String getInfoName(int index)
366     {
367         if (index < 0 || index >= m_list.size())
368             return null;
369         Restriction r = (Restriction)m_list.get(index);
370         return r.InfoName;
371     } // getInfoName
372
373     /**
374      * Get Info Operator
375      * @param index index
376      * @return info Operator
377      */
378     public String getInfoOperator(int index)
379     {
380         if (index < 0 || index >= m_list.size())
381             return null;
382         Restriction r = (Restriction)m_list.get(index);
383         return r.getInfoOperator();
384     } // getInfoOperator
385
386     /**
387      * Get Display with optional To
388      * @param index index
389      * @return info display
390      */
391     public String getInfoDisplayAll (int index)
392     {
393         if (index < 0 || index >= m_list.size())
394             return null;
395         Restriction r = (Restriction)m_list.get(index);
396         return r.getInfoDisplayAll();
397     } // getInfoDisplay
398
399     /**
400      * String representation
401      * @return info
402      */
403     public String toString()
404     {
405         if (isActive())
406             return getWhereClause(true);
407         return "MQuery[" + m_TableName + "]";
408     } // toString
409
410     /**
411      * Clone Query
412      * @return Query
413      */
414     public MQuery deepCopy()
415     {
416         MQuery newQuery = new MQuery(m_TableName);
417         for (int i = 0; i < m_list.size(); i++)
418             newQuery.addRestriction((Restriction)m_list.get(i));
419         return newQuery;
420     } // clone
421
422     /*************************************************************************/
423
424     /**
425      * Create simple Equal Query.
426      * Creates columnName=value or columnName='value'
427      * @param columnName columnName
428      * @param value value
429      * @return quary
430      */
431     public static MQuery getEqualQuery (String columnName, Object value)
432     {
433         MQuery query = new MQuery();
434         query.addRestriction(columnName, EQUAL, value);
435         return query;
436     } // getEqualQuery
437
438     /**
439      * Create simple Equal Query.
440      * Creates columnName=value
441      * @param columnName columnName
442      * @param value value
443      * @return quary
444      */
445     public static MQuery getEqualQuery (String columnName, int value)
446     {
447         MQuery query = new MQuery();
448         query.addRestriction(columnName, EQUAL, new Integer (value));
449         return query;
450     } // getEqualQuery
451
452 } // MQuery
453
454 /*****************************************************************************/
455
456 /**
457  * Query Restriction
458  */
459 class Restriction implements Serializable
460 {
461     /**
462      * Restriction
463      * @param ColumnName ColumnName
464      * @param Operator Operator, e.g. = != ..
465      * @param Code Code, e.g 0, All%
466      * @param InfoName Display Name
467      * @param InfoDisplay Display of Code (Lookup)
468      */
469     Restriction (String ColumnName, String Operator,
470         Object Code, String InfoName, String InfoDisplay)
471     {
472         this.ColumnName = ColumnName.trim();
473         if (InfoName != null)
474             this.InfoName = InfoName;
475         else
476             this.InfoName = this.ColumnName;
477         //
478 this.Operator = Operator;
479         // clean code
480 this.Code = Code;
481         if (this.Code instanceof String )
482         {
483             if (this.Code.toString().startsWith("'"))
484                 this.Code = this.Code.toString().substring(1);
485             if (this.Code.toString().endsWith("'"))
486                 this.Code = this.Code.toString().substring(0, this.Code.toString().length()-2);
487         }
488         if (InfoDisplay != null)
489             this.InfoDisplay = InfoDisplay.trim();
490         else if (this.Code != null)
491             this.InfoDisplay = this.Code.toString();
492     } // Restriction
493
494     /**
495      * Range Restriction (BETWEEN)
496      * @param ColumnName ColumnName
497      * @param Code Code, e.g 0, All%
498      * @param Code_to Code, e.g 0, All%
499      * @param InfoName Display Name
500      * @param InfoDisplay Display of Code (Lookup)
501      * @param InfoDisplay_to Display of Code (Lookup)
502      */
503     Restriction (String ColumnName,
504         Object Code, Object Code_to,
505         String InfoName, String InfoDisplay, String InfoDisplay_to)
506     {
507         this (ColumnName, MQuery.BETWEEN, Code, InfoName, InfoDisplay);
508
509         // Code_to
510 this.Code_to = Code_to;
511         if (this.Code_to instanceof String )
512         {
513             if (this.Code_to.toString().startsWith("'"))
514                 this.Code_to = this.Code_to.toString().substring(1);
515             if (this.Code_to.toString().endsWith("'"))
516                 this.Code_to = this.Code_to.toString().substring(0, this.Code_to.toString().length()-2);
517         }
518         // InfoDisplay_to
519 if (InfoDisplay_to != null)
520             this.InfoDisplay_to = InfoDisplay_to.trim();
521         else if (this.Code_to != null)
522             this.InfoDisplay_to = this.Code_to.toString();
523     } // Restriction
524
525     /**
526      * Create Restriction with dircet WHERE clause
527      * @param whereClause SQL WHERE Clause
528      */
529     Restriction (String whereClause)
530     {
531         DircetWhereClause = whereClause;
532     } // Restriction
533
534     /** Direct Where Clause */
535     protected String DircetWhereClause = null;
536     /** Column Name */
537     protected String ColumnName;
538     /** Name */
539     protected String InfoName;
540     /** Operator */
541     protected String Operator;
542     /** SQL Where Code */
543     protected Object Code;
544     /** Info */
545     protected String InfoDisplay;
546     /** SQL Where Code To */
547     protected Object Code_to;
548     /** Info To */
549     protected String InfoDisplay_to;
550     /** And/Or Condition */
551     protected boolean andCondition = true;
552
553     /**
554      * Return SQL construct for this restriction
555      * @param tableName optional table name
556      * @return SQL WHERE construct
557      */
558     public String getSQL (String tableName)
559     {
560         if (DircetWhereClause != null)
561             return DircetWhereClause;
562         //
563 StringBuffer sb = new StringBuffer ();
564         if (tableName != null && tableName.length() > 0)
565         {
566             // Assumes - REPLACE(INITCAP(variable),'s','X') or UPPER(variable)
567 int pos = ColumnName.lastIndexOf('(')+1; // including (
568 int end = ColumnName.indexOf(')');
569             // We have a Function in the ColumnName
570 if (pos != -1 && end != -1)
571                 sb.append(ColumnName.substring(0, pos))
572                     .append(tableName).append(".").append(ColumnName.substring(pos, end))
573                     .append(ColumnName.substring(end));
574             else
575                 sb.append(tableName).append(".").append(ColumnName);
576         }
577         else
578             sb.append(ColumnName);
579         //
580 sb.append(Operator);
581         if (Code instanceof String )
582             sb.append(DB.TO_STRING(Code.toString()));
583         else if (Code instanceof Timestamp)
584             sb.append(DB.TO_DATE((Timestamp)Code));
585         else
586             sb.append(Code);
587         // Between
588 // if (Code_to != null && InfoDisplay_to != null)
589 if (MQuery.BETWEEN.equals(Operator))
590         {
591             sb.append(" AND ");
592             if (Code_to instanceof String )
593                 sb.append(DB.TO_STRING(Code_to.toString()));
594             else if (Code_to instanceof Timestamp)
595                 sb.append(DB.TO_DATE((Timestamp)Code_to));
596             else
597                 sb.append(Code_to);
598         }
599         return sb.toString();
600     } // getSQL
601
602     /**
603      * Get String Representation
604      * @return info
605      */
606     public String toString()
607     {
608         return getSQL(null);
609     } // toString
610
611     /**
612      * Get Info Name
613      * @return Info Name
614      */
615     public String getInfoName()
616     {
617         return InfoName;
618     } // getInfoName
619
620     /**
621      * Get Info Operator
622      * @return info Operator
623      */
624     public String getInfoOperator()
625     {
626         for (int i = 0; i < MQuery.OPERATORS.length; i++)
627         {
628             if (MQuery.OPERATORS[i].getValue().equals(Operator))
629                 return MQuery.OPERATORS[i].getName();
630         }
631         return Operator;
632     } // getInfoOperator
633
634     /**
635      * Get Display with optional To
636      * @return info display
637      */
638     public String getInfoDisplayAll()
639     {
640         if (InfoDisplay_to == null)
641             return InfoDisplay;
642         StringBuffer sb = new StringBuffer (InfoDisplay);
643         sb.append(" - ").append(InfoDisplay_to);
644         return sb.toString();
645     } // getInfoDisplay
646
647 } // Restriction
648

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags