001// Copyright 2004, 2005 The Apache Software Foundation
002//
003// Licensed under the Apache License, Version 2.0 (the "License");
004// you may not use this file except in compliance with the License.
005// You may obtain a copy of the License at
006//
007//     http://www.apache.org/licenses/LICENSE-2.0
008//
009// Unless required by applicable law or agreed to in writing, software
010// distributed under the License is distributed on an "AS IS" BASIS,
011// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
012// See the License for the specific language governing permissions and
013// limitations under the License.
014
015package org.apache.tapestry.contrib.jdbc;
016
017import java.sql.Connection;
018import java.sql.SQLException;
019import java.sql.Timestamp;
020import java.util.ArrayList;
021import java.util.Calendar;
022import java.util.Date;
023import java.util.List;
024
025/**
026 *  Class for creating and executing JDBC statements.  Allows statements to be assembled
027 *  incrementally (like a {@link StringBuffer}), but also tracks parameters, shielding
028 *  the developer from the differences between constructing and 
029 *  using a JDBC 
030 *  {@link java.sql.Statement} and 
031 *  a JDBC {@link java.sql.PreparedStatement}.  This class is somewhat skewed towards
032 *  Oracle, which works more efficiently with prepared staments than
033 *  simple SQL.
034 * 
035 *  <p>In addition, implements {@link #toString()} in a useful way (you can see the
036 *  SQL and parameters), which is invaluable when debugging.
037 * 
038 *  <p>{@link #addParameter(int)} (and all overloaded versions of it for scalar types)
039 *  adds a "?" to the statement and records the parameter value.
040 * 
041 *  <p>{@link #addParameter(Integer)} (and all overloaded version of it for wrapper
042 *  types) does the same ... unless the value is null, in which case "NULL" is
043 *  inserted into the statement.
044 * 
045 *  <p>{@link #addParameterList(int[], String)} (and all overloaded versions of it)
046 *  simply invokes the appropriate {@link #addParameter(int)}, adding the
047 *  separator in between parameters.
048 *
049 *  @author Howard Lewis Ship
050 *
051 **/
052
053public class StatementAssembly
054{
055    private StringBuffer _buffer = new StringBuffer();
056
057    private static final String NULL = "NULL";
058
059    public static final String SEP = ", ";
060
061    /**
062     *  List of {@link IParameter}
063     * 
064     **/
065
066    private List _parameters;
067
068    private int _lineLength;
069    private int _maxLineLength = 80;
070    private int _indent = 5;
071
072    /**
073     *  Default constructor; uses a maximum line length of 80 and an indent of 5.
074     *
075     **/
076
077    public StatementAssembly()
078    {
079    }
080
081    public StatementAssembly(int maxLineLength, int indent)
082    {
083        _maxLineLength = maxLineLength;
084        _indent = indent;
085    }
086
087    /**
088     *  Clears the assembly, preparing it for re-use.
089     * 
090     *  @since 1.0.7
091     * 
092     **/
093
094    public void clear()
095    {
096        _buffer.setLength(0);
097        _lineLength = 0;
098
099        if (_parameters != null)
100            _parameters.clear();
101    }
102
103    /**
104     *  Maximum length of a line.
105     *
106     **/
107
108    public int getMaxLineLength()
109    {
110        return _maxLineLength;
111    }
112
113    /**
114     *  Number of spaces to indent continuation lines by.
115     *
116     **/
117
118    public int getIndent()
119    {
120        return _indent;
121    }
122
123    /**
124     *  Adds text to the current line, unless that would make the line too long, in
125     *  which case a new line is started (and indented) before adding the text.
126     *
127     *  <p>Text is added as-is, with no concept of quoting.  To add arbitrary strings
128     *  (such as in a where clause), use {@link #addParameter(String)}.
129     *
130     *
131     **/
132
133    public void add(String text)
134    {
135        int textLength;
136
137        textLength = text.length();
138
139        if (_lineLength + textLength > _maxLineLength)
140        {
141            _buffer.append('\n');
142
143            for (int i = 0; i < _indent; i++)
144                _buffer.append(' ');
145
146            _lineLength = _indent;
147        }
148
149        _buffer.append(text);
150        _lineLength += textLength;
151    }
152    
153    public void add(short value)
154    {
155        add(Short.toString(value));
156    }
157    
158    public void add(int value)
159    {
160        add(Integer.toString(value));
161    }
162    
163    public void add(long value)
164    {
165        add(Long.toString(value));
166    }
167    
168    public void add(float value)
169    {
170        add(Float.toString(value));
171    }
172    
173    public void add(double value)
174    {
175        add(Double.toString(value));
176    }
177
178    /**
179     *  Adds a date value to a {@link StatementAssembly} converting
180     *  it to a {@link java.sql.Timestamp} first.
181     *
182     **/
183
184    public void addParameter(Date date)
185    {
186        if (date == null)
187        {
188            add("NULL");
189            return;
190        }
191
192        Calendar calendar = Calendar.getInstance();
193
194        calendar.setTime(date);
195        calendar.set(Calendar.MILLISECOND, 0);
196
197        Date adjusted = calendar.getTime();
198
199        Timestamp timestamp = new Timestamp(adjusted.getTime());
200
201        addParameter(timestamp);
202    }
203
204    /** 
205     *  Adds a separator (usually a comma and a space) to the current line, regardless
206     *  of line length.  This is purely aesthetic ... it just looks odd if a separator
207     *  gets wrapped to a new line by itself.
208     *
209     **/
210
211    public void addSep(String text)
212    {
213        _buffer.append(text);
214        _lineLength += text.length();
215    }
216
217    /**
218     *  Starts a new line, without indenting.
219     *
220     **/
221
222    public void newLine()
223    {
224        if (_buffer.length() != 0)
225            _buffer.append('\n');
226
227        _lineLength = 0;
228    }
229
230    /**
231     * Starts a new line, then adds the given text.
232     *
233     **/
234
235    public void newLine(String text)
236    {
237        if (_buffer.length() != 0)
238            _buffer.append('\n');
239
240        _buffer.append(text);
241
242        _lineLength = text.length();
243    }
244
245    public void addList(String[] items, String separator)
246    {
247        for (int i = 0; i < items.length; i++)
248        {
249            if (i > 0)
250                addSep(separator);
251
252            add(items[i]);
253        }
254    }
255
256    public void addParameterList(int[] items, String separator)
257    {
258        for (int i = 0; i < items.length; i++)
259        {
260            if (i > 0)
261                addSep(separator);
262
263            addParameter(items[i]);
264        }
265    }
266
267    public void addParameterList(Integer[] items, String separator)
268    {
269        for (int i = 0; i < items.length; i++)
270        {
271            if (i > 0)
272                addSep(separator);
273
274            addParameter(items[i]);
275        }
276    }
277
278    public void addParameterList(long[] items, String separator)
279    {
280        for (int i = 0; i < items.length; i++)
281        {
282            if (i > 0)
283                addSep(separator);
284
285            addParameter(items[i]);
286        }
287    }
288
289    public void addParameterList(Long[] items, String separator)
290    {
291        for (int i = 0; i < items.length; i++)
292        {
293            if (i > 0)
294                addSep(separator);
295
296            addParameter(items[i]);
297        }
298    }
299
300    public void addParameterList(String[] items, String separator)
301    {
302        for (int i = 0; i < items.length; i++)
303        {
304            if (i > 0)
305                addSep(separator);
306
307            addParameter(items[i]);
308        }
309    }
310
311    public void addParameterList(double[] items, String separator)
312    {
313        for (int i = 0; i < items.length; i++)
314        {
315            if (i > 0)
316                addSep(separator);
317
318            addParameter(items[i]);
319        }
320    }
321
322    public void addParameter(Object value)
323    {
324        if (value == null)
325            add(NULL);
326        else
327            addParameter(new ObjectParameter(value));
328    }
329
330    public void addParameter(Timestamp timestamp)
331    {
332        if (timestamp == null)
333            add(NULL);
334        else
335            addParameter(new TimestampParameter(timestamp));
336    }
337
338    public void addParameter(String value)
339    {
340        if (value == null)
341            add(NULL);
342        else
343            addParameter(new StringParameter(value));
344    }
345
346    public void addParameter(int value)
347    {
348        addParameter(new IntegerParameter(value));
349    }
350
351    public void addParameter(Integer value)
352    {
353        if (value == null)
354            add(NULL);
355        else
356            addParameter(value.intValue());
357    }
358
359    public void addParameter(long value)
360    {
361        addParameter(new LongParameter(value));
362    }
363
364    public void addParameter(Long value)
365    {
366        if (value == null)
367            add(NULL);
368        else
369            addParameter(value.longValue());
370    }
371
372    public void addParameter(float value)
373    {
374        addParameter(new FloatParameter(value));
375    }
376
377    public void addParameter(Float value)
378    {
379        if (value == null)
380            add(NULL);
381        else
382            addParameter(value.floatValue());
383    }
384
385    public void addParameter(double value)
386    {
387        addParameter(new DoubleParameter(value));
388    }
389
390    public void addParameter(Double value)
391    {
392        if (value == null)
393            add(NULL);
394        else
395            addParameter(value.doubleValue());
396    }
397
398    public void addParameter(short value)
399    {
400        addParameter(new ShortParameter(value));
401    }
402
403    public void addParameter(Short value)
404    {
405        if (value == null)
406            add(NULL);
407        else
408            addParameter(value.shortValue());
409    }
410
411    public void addParameter(boolean value)
412    {
413        addParameter(value ? BooleanParameter.TRUE : BooleanParameter.FALSE);
414    }
415
416    public void addParameter(Boolean value)
417    {
418        if (value == null)
419            add(NULL);
420        else
421            addParameter(value.booleanValue());
422    }
423
424    private void addParameter(IParameter parameter)
425    {
426        if (_parameters == null)
427            _parameters = new ArrayList();
428
429        _parameters.add(parameter);
430
431        add("?");
432    }
433
434    /**
435     *  Creates and returns an {@link IStatement} based on the SQL and parameters
436     *  acquired.
437     *
438     **/
439
440    public IStatement createStatement(Connection connection) throws SQLException
441    {
442        String sql = _buffer.toString();
443
444        if (_parameters == null || _parameters.isEmpty())
445            return new SimpleStatement(sql, connection);
446
447        return new ParameterizedStatement(sql, connection, _parameters);
448    }
449
450    public String toString()
451    {
452        StringBuffer buffer = new StringBuffer("StatementAssembly@");
453
454        buffer.append(Integer.toHexString(hashCode()));
455        buffer.append("[SQL=\n<");
456        buffer.append(_buffer);
457        buffer.append("\n>");
458
459        if (_parameters != null)
460        {
461            int count = _parameters.size();
462            for (int i = 0; i < count; i++)
463            {
464                Object parameter = _parameters.get(i);
465
466                buffer.append(" ?");
467                buffer.append(i + 1);
468                buffer.append('=');
469
470                buffer.append(parameter);
471            }
472        }
473
474        buffer.append(']');
475
476        return buffer.toString();
477    }
478}