OW2 Consortium telosys

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
13 lguerin 1 
package org.objectweb.telosys.dal.dao;
2 
 
3 
/**
4 
 * Each instance this class holds all the SQL requets required by a standard DAO <br>
5 
 * to acces a table.
6 
 *
7 
 * There's an instance of SqlRequests in each DAO.
8 
 *
9 
 * @author Laurent GUERIN
10 
 *
11 
 */
12 
public class SqlRequests
13 
{
14 
    private String  _table           = null;
15 
 
16 
    private String  _keyColumns[]    = null;
17 
 
18 
    private String  _dataColumns[]   = null;
19 
 
20 
    private String  _sAutoIncColumn  = null;
21 
 
22 
    //--- SQL requests
23 
    private String  _sqlSelect       = null;
24 
 
25 
    private String  _sqlExists       = null;
26 
 
27 
    private String  _sqlInsert       = null;
28 
 
29 
    private String  _sqlUpdate       = null;
30 
 
31 
    private String  _sqlDelete       = null;
32 
 
33 
    //--- Pre-built clauses
34 
    private String  _allColumnsList  = null;
35 
 
36 
    private String  _whereCriteria   = null;
37 
 
38 
    //--- Join management
39 
    private boolean _bSelectWithJoin = false;
40 
 
41 
    private String  _sJoinTables     = null;
42 
 
43 
    private String  _sJoinColumns    = null;
44 
 
45 
    private String  _sJoinCriteria   = null;
46 
 
47 
    //============================================================================
48 
    //  CONSTRUCTORS
49 
    //============================================================================
50 
    private void init(String table, String keyColumns[], String dataColumns[], String sAutoInc) // v 0.9.9
51 
    {
52 
        if (table == null || keyColumns == null || dataColumns == null)
53 
        {
54 
            return;
55 
        }
56 
 
57 
        //--- Init the input parameters
58 
        _table = table.trim();
59 
        if ( sAutoInc != null )
60 
        {
61 
            _sAutoIncColumn = sAutoInc.trim() ;
62 
        }
63 
        else
64 
        {
65 
            _sAutoIncColumn = null ;
66 
        }
67 
 
68 
        _keyColumns = (String[]) keyColumns.clone(); // clone to avoid exposing mutable object
69 
        _dataColumns = (String[]) dataColumns.clone(); // clone to avoid exposing mutable object
70 
 
71 
        //--- Build the 4 SQL Request
72 
        _sqlSelect = buildSqlSelect();
73 
        _sqlExists = buildSqlSelectCount();
74 
        _sqlInsert = buildSqlInsert();
75 
        _sqlUpdate = buildSqlUpdate();
76 
        _sqlDelete = buildSqlDelete();
77 
 
78 
        //--- Pre-built clauses parts
79 
        _allColumnsList = columnsAll(true);
80 
        _whereCriteria = whereCriteria(true);
81 
    }
82 
 
83 
    /**
84 
     * Constructor
85 
     * @param table the table
86 
     * @param keyColumns the columns of the Primary Key
87 
     * @param dataColumns the other columns of the table ( not in the Primary Key )
88 
     */
89 
    public SqlRequests(String table, String keyColumns[], String dataColumns[])
90 
    {
91 
        init(table, keyColumns, dataColumns, null); // v 0.9.9
92 
    }
93 
 
94 
    /**
95 
     * Constructor for tables with an "auto incremented" column
96 
     * @param table the table
97 
     * @param keyColumns the columns of the Primary Key
98 
     * @param dataColumns the other columns of the table ( not in the Primary Key )
99 
     * @param sAutoInc the name the "auto incremented" column
100 
     */
101 
    public SqlRequests(String table, String keyColumns[], String dataColumns[], String sAutoInc ) // v 0.9.9
102 
    {
103 
        init(table, keyColumns, dataColumns, sAutoInc);
104 
    }
105 
 
106 
    //============================================================================
107 
    //  PRIVATE METHODS
108 
    //============================================================================
109 
    /**
110 
     * Determines if a column is auto-incremented
111 
     * @param sCol
112 
     * @return true if the given column is the auto-incremented column of the table
113 
     */
114 
    private boolean isAutoInc ( String sCol ) // v 0.9.9
115 
    {
116 
        if ( _sAutoIncColumn != null )
117 
        {
118 
            return _sAutoIncColumn.equals( sCol ) ;
119 
        }
120 
        return false ;
121 
    }
122 
 
123 
    /**
124 
     * Build a list of coma separated colums with ALL the columns <br>
125 
     * (like standard columnsAll ) except for the AUTO_INC column <br>
126 
     * ie : "tab.col1, tab.col2, tab.col3"
127 
     * @param bPrefix
128 
     * @return
129 
     */
130 
    private String columnsWithoutAutoInc(boolean bPrefix) // v 0.9.9
131 
    {
132 
        StringBuffer sb = new StringBuffer(200);
133 
        int iNbCol = 0;
134 
        //--- Data Columns
135 
        for ( int c = 0 ; c < _dataColumns.length ; c++ )
136 
        {
137 
            if ( ! isAutoInc ( _dataColumns[c] ) ) // Check if the column is auto inc
138 
            {
139 
	            if (iNbCol > 0)
140 
	            {
141 
	                sb.append(", ");
142 
	            }
143 
	            if (bPrefix)
144 
	            {
145 
	                sb.append(_table + ".");
146 
	            }
147 
	            sb.append(_dataColumns[c]);
148 
	            iNbCol++;
149 
            }
150 
        }
151 
        //--- Key Columns
152 
        for ( int c = 0 ; c < _keyColumns.length ; c++ )
153 
        {
154 
            if ( ! isAutoInc ( _keyColumns[c] ) ) // Check if the column is auto inc
155 
            {
156 
	            if (iNbCol > 0)
157 
	            {
158 
	                sb.append(", ");
159 
	            }
160 
	            if (bPrefix)
161 
	            {
162 
	                sb.append(_table + ".");
163 
	            }
164 
	            sb.append(_keyColumns[c]);
165 
	            iNbCol++;
166 
            }
167 
        }
168 
        return sb.toString();
169 
    }
170 
 
171 
    /**
172 
     * Build a list of coma separated colums (for ALL the columns) ie : "tab.col1, tab.col2, tab.col3"
173 
     *
174 
     * @return
175 
     */
176 
    private String columnsAll(boolean bPrefix)
177 
    {
178 
        StringBuffer sb = new StringBuffer(200);
179 
        int iNbCol = 0;
180 
 
181 
        //--- Data Columns
182 
        for ( int c = 0 ; c < _dataColumns.length ; c++ )
183 
        {
184 
            if (iNbCol > 0)
185 
            {
186 
                sb.append(", ");
187 
            }
188 
            if (bPrefix)
189 
            {
190 
                sb.append(_table + ".");
191 
            }
192 
            sb.append(_dataColumns[c]);
193 
            iNbCol++;
194 
        }
195 
 
196 
        //--- Key Columns
197 
        for ( int c = 0 ; c < _keyColumns.length ; c++ )
198 
        {
199 
            if (iNbCol > 0)
200 
            {
201 
                sb.append(", ");
202 
            }
203 
            if (bPrefix)
204 
            {
205 
                sb.append(_table + ".");
206 
            }
207 
            sb.append(_keyColumns[c]);
208 
            iNbCol++;
209 
        }
210 
        return sb.toString();
211 
    }
212 
 
213 
    /**
214 
     * Build a list of coma separated "?" ( for ALL the columns ) ie : "?, ?, ?"
215 
     *
216 
     * @return
217 
     */
218 
    private String values()
219 
    {
220 
        StringBuffer sb = new StringBuffer(200);
221 
        int iNbCol = 0;
222 
 
223 
        //--- Data Columns
224 
        for ( int c = 0 ; c < _dataColumns.length ; c++ )
225 
        {
226 
            if (iNbCol > 0)
227 
            {
228 
                sb.append(", ");
229 
            }
230 
            sb.append("?");
231 
            iNbCol++;
232 
        }
233 
 
234 
        //--- Key Columns
235 
        for ( int c = 0 ; c < _keyColumns.length ; c++ )
236 
        {
237 
            if (iNbCol > 0)
238 
            {
239 
                sb.append(", ");
240 
            }
241 
            sb.append("?");
242 
            iNbCol++;
243 
        }
244 
        return sb.toString();
245 
    }
246 
 
247 
    private String valuesWithoutAutoInc() // v 0.9.9
248 
    {
249 
        int iNbCol = _dataColumns.length + _keyColumns.length ;
250 
        //--- AutoInc = 1 col less
251 
        if ( iNbCol > 0 ) iNbCol-- ;
252 
 
253 
        StringBuffer sb = new StringBuffer(200);
254 
        //--- Columns
255 
        for ( int c = 0 ; c < iNbCol ; c++ )
256 
        {
257 
            if (c > 0)
258 
            {
259 
                sb.append(", ");
260 
            }
261 
            sb.append("?");
262 
        }
263 
        return sb.toString();
264 
    }
265 
 
266 
    /**
267 
     * Build the where criteria ( for KEY columns only) <br>
268 
     * ie : "tab.col1 = ? and tab.col2 = ?"
269 
     *
270 
     * @return
271 
     */
272 
    private String whereCriteria(boolean bPrefix)
273 
    {
274 
        StringBuffer sb = new StringBuffer(200);
275 
        //--- Key Columns
276 
        for ( int c = 0 ; c < _keyColumns.length ; c++ )
277 
        {
278 
            if (c > 0)
279 
            {
280 
                sb.append(" and ");
281 
            }
282 
            if (bPrefix)
283 
            {
284 
                sb.append(_table + ".");
285 
            }
286 
            sb.append(_keyColumns[c] + " = ?");
287 
        }
288 
        return sb.toString();
289 
    }
290 
 
291 
    /**
292 
     * Build the set column value clause (for DATA columns only) ie : "tab.col1 = ?, tab.col2 = ?"
293 
     *
294 
     * @return
295 
     */
296 
    private String setDataValues(boolean bPrefix)
297 
    {
298 
        StringBuffer sb = new StringBuffer(200);
299 
        //--- Data Columns
300 
        for ( int c = 0 ; c < _dataColumns.length ; c++ )
301 
        {
302 
            if (c > 0)
303 
            {
304 
                sb.append(", ");
305 
            }
306 
            if (bPrefix)
307 
            {
308 
                sb.append(_table + ".");
309 
            }
310 
            sb.append(_dataColumns[c] + " = ?");
311 
        }
312 
        return sb.toString();
313 
    }
314 
 
315 
    /**
316 
     * Build the SQL SELECT request
317 
     *
318 
     * @return
319 
     */
320 
    private String buildSqlSelect()
321 
    {
322 
        //return "select " + allColumns(true) + " from " + _table + " where " + whereCriteria(true);
323 
        return "select " + columnsAll(false) + " from " + _table + " where " + whereCriteria(false);
324 
    }
325 
 
326 
    /**
327 
     * Build the SQL COUNT request
328 
     *
329 
     * @return
330 
     */
331 
    private String buildSqlSelectCount()
332 
    {
333 
        return "select count(*) from " + _table + " where " + whereCriteria(false);
334 
    }
335 
 
336 
    /**
337 
     * Build the SQL INSERT request
338 
     *
339 
     * @return
340 
     */
341 
//  v 0.9.9
342 
//    private String buildSqlInsert()
343 
//    {
344 
//        return "insert into " + _table + " ( " + columnsAll(false) + " ) values ( " + values() + " )";
345 
//    }
346 
 
347 
    private String buildSqlInsert() // v 0.9.9
348 
    {
349 
        if ( _sAutoIncColumn != null )
350 
        {
351 
            //--- Special INSERT request without the AUTO-INC column
352 
            return "insert into " + _table + " ( " + columnsWithoutAutoInc(false) + " ) values ( " + valuesWithoutAutoInc() + " )";
353 
        }
354 
        else
355 
        {
356 
            //--- Standard INSERT request with all the columns
357 
            return "insert into " + _table + " ( " + columnsAll(false) + " ) values ( " + values() + " )";
358 
        }
359 
    }
360 
 
361 
    /**
362 
     * Build the SQL UPDATE request
363 
     *
364 
     * @return
365 
     */
366 
    private String buildSqlUpdate()
367 
    {
368 
        return "update " + _table + " set " + setDataValues(false) + " where " + whereCriteria(false);
369 
    }
370 
 
371 
    /**
372 
     * Build the SQL DELETE request
373 
     *
374 
     * @return
375 
     */
376 
    private String buildSqlDelete()
377 
    {
378 
        return "delete from " + _table + " where " + whereCriteria(false);
379 
    }
380 
 
381 
    //============================================================================
382 
    //============================================================================
383 
    /**
384 
     * Creates a ListQuery
385 
     * @param iKeySize number of parameters for the query ( the number of '?' in the string : 0 to N )
386 
     * @param sQueryCriteria the SQL criteria for the WHERE clause
387 
     * @param sQueryEndOfSelect the additional SQL clause ( e.g. : "order by ..." )
388 
     * @return
389 
     */
390 
    public ListQuery createQuery(int iKeySize, String sQueryCriteria, String sQueryEndOfSelect) //throws TelosysException
391 
    {
392 
        if (_bSelectWithJoin)
393 
        {
394 
            //--- The SELECT request has a JOIN => build the query with the JOIN
395 
            return createQueryWithJoin(iKeySize, sQueryCriteria, sQueryEndOfSelect);
396 
        }
397 
        else
398 
        {
399 
            //--- No JOIN in the SELECT => build a normal query
400 
            return createQueryWithoutJoin(iKeySize, sQueryCriteria, sQueryEndOfSelect);
401 
        }
402 
    }
403 
 
404 
    private ListQuery createQueryWithoutJoin(int iKeySize, String sQueryCriteria, String sQueryEndOfSelect)
405 
    {
406 
        String sWhere = SqlBuilder.buildSqlWhere(sQueryCriteria);
407 
        String sEnd = SqlBuilder.buildSqlComplement(sQueryEndOfSelect);
408 
 
409 
        String sSelect = "select " + columnsAll(true) + " from " + _table + sWhere + sEnd;
410 
        String sCount = "select count(*) from " + _table + sWhere;
411 
        String sDelete = "delete from " + _table + sWhere;
412 
        return new StandardQuery(iKeySize, sSelect, sCount, sDelete);
413 
    }
414 
 
415 
    private ListQuery createQueryWithJoin(int iKeySize, String sQueryCriteria, String sQueryEndOfSelect)
416 
    {
417 
        //--- Build the SQL fragments
418 
        String sColumns = SqlBuilder.buildSqlColumns(columnsAll(true), _sJoinColumns);
419 
        String sTables = SqlBuilder.buildSqlTables(_table, _sJoinTables);
420 
        String sWhere = SqlBuilder.buildSqlWhere(_sJoinCriteria, sQueryCriteria);
421 
        String sEnd = SqlBuilder.buildSqlComplement(sQueryEndOfSelect);
422 
 
423 
        String sSelect = "select " + sColumns + " from " + sTables + sWhere + sEnd;
424 
        String sCount = "select count(*) from " + sTables + sWhere;
425 
        String sDelete = "delete from " + _table + SqlBuilder.buildSqlWhere(sQueryCriteria);
426 
        return new StandardQuery(iKeySize, sSelect, sCount, sDelete);
427 
    }
428 
 
429 
    /**
430 
     * Changes the SQL SELECT by adding a JOIN
431 
     *
432 
     * @param sJoinTables
433 
     * @param sJoinColumns
434 
     * @param sJoinCriteria
435 
     */
436 
    public void alterSqlSelectWithJoin(String sJoinTables, String sJoinColumns, String sJoinCriteria)
437 
    {
438 
        //--- Build the SQL fragments
439 
        String sColumns = SqlBuilder.buildSqlColumns(columnsAll(true), sJoinColumns);
440 
        String sTables = SqlBuilder.buildSqlTables(_table, sJoinTables);
441 
        String sWhere = SqlBuilder.buildSqlWhere(sJoinCriteria, whereCriteria(true));
442 
 
443 
        //--- Set the new SELECT request
444 
        _sqlSelect = "select " + sColumns + " from " + sTables + sWhere;
445 
 
446 
        //--- Keep the Join characteristics
447 
        _bSelectWithJoin = true;
448 
        _sJoinTables = sJoinTables;
449 
        _sJoinColumns = sJoinColumns;
450 
        _sJoinCriteria = sJoinCriteria;
451 
    }
452 
 
453 
    //============================================================================
454 
    //  GETTERS
455 
    //============================================================================
456 
 
457 
    /**
458 
     * Returns the SQL SELECT request
459 
     *
460 
     * @return the SQL Select clause
461 
     */
462 
    public String getSqlSelect()
463 
    {
464 
        return _sqlSelect;
465 
    }
466 
 
467 
    /**
468 
     * Returns the SQL SELECT request adding the optional clause at the end
469 
     *
470 
     * @param sOptionalClause
471 
     * @return the SQL Select clause
472 
     */
473 
    public String getSqlSelect(String sOptionalClause)
474 
    {
475 
        if (sOptionalClause != null)
476 
        {
477 
            return _sqlSelect + " " + sOptionalClause;
478 
        }
479 
        else
480 
        {
481 
            return _sqlSelect;
482 
        }
483 
    }
484 
 
485 
    /**
486 
     * Returns the SQL SELECT COUNT request
487 
     *
488 
     * @return
489 
     */
490 
    public String getSqlExists()
491 
    {
492 
        return _sqlExists;
493 
    }
494 
 
495 
    /**
496 
     * Returns the SQL INSERT request
497 
     *
498 
     * @return
499 
     */
500 
    public String getSqlInsert()
501 
    {
502 
        return _sqlInsert;
503 
    }
504 
 
505 
    /**
506 
     * Returns the SQL UPDATE request
507 
     *
508 
     * @return
509 
     */
510 
    public String getSqlUpdate()
511 
    {
512 
        return _sqlUpdate;
513 
    }
514 
 
515 
    /**
516 
     * Returns the SQL DELETE request
517 
     *
518 
     * @return
519 
     */
520 
    public String getSqlDelete()
521 
    {
522 
        return _sqlDelete;
523 
    }
524 
 
525 
    /**
526 
     * Returns the TABLE NAME
527 
     *
528 
     * @return
529 
     */
530 
    public String getTableName()
531 
    {
532 
        return _table;
533 
    }
534 
 
535 
    /**
536 
     * Returns the 'Auto-Inc' column if any (or NULL)
537 
     * @return
538 
     */
539 
    public String getAutoIncColumn()
540 
    {
541 
        return _sAutoIncColumn;
542 
    }
543 
 
544 
    /**
545 
     * Returns true if the table has an 'Auto-Inc' column
546 
     * @return
547 
     */
548 
    public boolean hasAutoIncColumn()
549 
    {
550 
        return _sAutoIncColumn != null ;
551 
    }
552 
 
553 
    /**
554 
     * Returns the list of all the columns (coma separated)
555 
     *
556 
     * @return
557 
     */
558 
    public String getAllColumnsList()
559 
    {
560 
        return _allColumnsList;
561 
    }
562 
 
563 
    /**
564 
     * Returns the where criteria of the requests
565 
     *
566 
     * @return
567 
     */
568 
    public String getWhereCriteria()
569 
    {
570 
        return _whereCriteria;
571 
    }
572 
 
573 
    /* (non-Javadoc)
574 
     * @see java.lang.Object#toString()
575 
     */
576 
    public String toString()
577 
    {
578 
        return "'"+ _table + "' requests :\n"
579 
        + ". " + _sqlSelect + "\n"
580 
        + ". " + _sqlInsert + "\n"
581 
        + ". " + _sqlUpdate + "\n"
582 
        + ". " + _sqlDelete + "\n"
583 
        + ". " + _sqlExists + "\n"
584 
        ;
585 
    }
586 
}