001/*
002 * The MIT License
003 *
004 * Copyright (c) 2012-2016, Ninja Squad
005 *
006 * Permission is hereby granted, free of charge, to any person obtaining a copy
007 * of this software and associated documentation files (the "Software"), to deal
008 * in the Software without restriction, including without limitation the rights
009 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
010 * copies of the Software, and to permit persons to whom the Software is
011 * furnished to do so, subject to the following conditions:
012 *
013 * The above copyright notice and this permission notice shall be included in
014 * all copies or substantial portions of the Software.
015 *
016 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
017 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
018 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
019 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
020 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
021 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
022 * THE SOFTWARE.
023 */
024
025package com.ninja_squad.dbsetup.bind;
026
027import java.math.BigDecimal;
028import java.math.BigInteger;
029import java.sql.Date;
030import java.sql.Time;
031import java.sql.Timestamp;
032import java.sql.Types;
033import java.time.Instant;
034import java.time.LocalDate;
035import java.time.LocalDateTime;
036import java.time.LocalTime;
037import java.time.OffsetDateTime;
038import java.time.OffsetTime;
039import java.time.ZonedDateTime;
040import java.util.Calendar;
041import java.util.TimeZone;
042
043/**
044 * Utility class allowing to get various kinds of binders. The {@link DefaultBinderConfiguration} uses binders
045 * returned by this class, based on the type of the parameter.
046 * @author JB Nizet
047 */
048public final class Binders {
049
050    private static final Binder DEFAULT_BINDER = new DefaultBinder();
051    private static final Binder DATE_BINDER = new DateBinder();
052    private static final Binder TIMESTAMP_BINDER = new TimestampBinder();
053    private static final Binder DECIMAL_BINDER = new DecimalBinder();
054    private static final Binder INTEGER_BINDER = new IntegerBinder();
055    private static final Binder TIME_BINDER = new TimeBinder();
056    private static final Binder STRING_BINDER = new StringBinder();
057
058    private Binders() {
059    }
060
061    /**
062     * Returns the default binder. This binder is normally used for columns of a type that is not handled by the other
063     * binders. It is also used when the metadata are not used and the Insert thus doesn't know the type of the column.
064     * It simply uses <code>stmt.setObject()</code> to bind the parameter, except if the value being bound is of some
065     * some well-known type not handled by JDBC:
066     * <ul>
067     *     <li><code>enum</code>: the name of the enum is bound</li>
068     *     <li><code>java.util.Date</code>: the date is transformed to a <code>java.sql.Timestamp</code></li>
069     *     <li><code>java.util.Calendar</code>: the calendar is transformed to a <code>java.sql.Timestamp</code>,
070     *         and is passed as third argument of
071     *         <code>PreparedStatement.setTimestamp()</code> to pass the timezone</li>
072     *     <li><code>java.time.LocalDate</code>: transformed to a <code>java.sql.Date</code></li>
073     *     <li><code>java.time.LocalTime</code>: transformed to a <code>java.sql.Time</code></li>
074     *     <li><code>java.time.LocalDateTime</code>: transformed to a <code>java.sql.Timestamp</code></li>
075     *     <li><code>java.time.Instant</code>: transformed to a <code>java.sql.Timestamp</code></li>
076     *     <li><code>java.time.ZonedDateTime</code> and <code>OffsetDateTime</code>: transformed to a
077     *         <code>java.sql.Timestamp</code>. The time zone is also used to create a <code>Calendar</code> passed as
078     *         third argument of <code>PreparedStatement.setTimestamp()</code> to pass the timezone</li>
079     *     <li><code>java.time.OffsetTime</code>: transformed to a
080     *         <code>java.sql.Time</code>. The time zone is also used to create a <code>Calendar</code> passed as third
081     *         argument of <code>PreparedStatement.setTime()</code> to pass the timezone</li>
082     * </ul>
083     */
084    public static Binder defaultBinder() {
085        return DEFAULT_BINDER;
086    }
087
088    /**
089     * Returns a binder suitable for columns of type CHAR and VARCHAR. The returned binder supports values of type
090     * <ul>
091     *   <li><code>String</code></li>
092     *   <li><code>enum</code>: the name of the enum is used as bound value</li>
093     *   <li><code>Object</code>: the <code>toString()</code> of the object is used as bound value</li>
094     * </ul>
095     */
096    public static Binder stringBinder() {
097        return STRING_BINDER;
098    }
099
100    /**
101     * Returns a binder suitable for columns of type DATE. The returned binder supports values of type
102     * <ul>
103     *   <li><code>java.sql.Date</code></li>
104     *   <li><code>java.util.Date</code>: the milliseconds of the date are used to construct a
105     *       <code>java.sql.Date</code>.</li>
106     *   <li><code>java.util.Calendar</code>: the milliseconds of the calendar are used to construct a
107     *       <code>java.sql.Date</code>, and the calendar is passed as third argument of
108     *       <code>PreparedStatement.setDate()</code> to pass the timezone
109     *   </li>
110     *   <li><code>String</code>: the string is transformed to a java.sql.Date using the <code>Date.valueOf()</code>
111     *       method</li>
112     *   <li><code>java.time.LocalDate</code>: transformed to a <code>java.sql.Date</code> using
113     *       <code>Date.valueOf()</code></li>
114     *   <li><code>java.time.LocalDateTime</code>: transformed to a LocalDate (and thus ignoring the time),
115     *       and then transformed to a <code>java.sql.Date</code> using <code>Date.valueOf()</code></li>
116     *   <li><code>java.time.Instant</code>the milliseconds of the instant are used to construct a
117     *       <code>java.sql.Date</code>.</li>
118     *   <li><code>java.time.ZonedDateTime</code> and <code>java.time.OffsetDateTime</code>: transformed to an Instant
119     *       and then to a <code>java.sql.Date</code>. The time zone is also used to create a <code>Calendar</code>
120     *       passed as third argument of <code>PreparedStatement.setDate()</code> to pass the timezone</li>
121     * </ul>
122     * If the value is none of these types, <code>stmt.setObject()</code> is used to bind the value.
123     */
124    public static Binder dateBinder() {
125        return DATE_BINDER;
126    }
127
128    /**
129     * Returns a binder suitable for columns of type TIMESTAMP and TIMESTAMP_WITH_TIMEZONE. The returned binder
130     * supports values of type
131     * <ul>
132     *   <li><code>java.sql.Timestamp</code></li>
133     *   <li><code>java.util.Date</code>: the milliseconds of the date are used to construct a
134     *       <code>java.sql.Timestamp</code></li>
135     *   <li><code>java.util.Calendar</code>: the milliseconds of the calendar are used to construct a
136     *       <code>java.sql.Timestamp</code>, and the calendar is passed as third argument of
137     *       <code>PreparedStatement.setTimestamp()</code> to pass the timezone</li>
138     *   <li><code>String</code>: the string is transformed to a <code>java.sql.Timestamp</code> using the
139     *       <code>Timestamp.valueOf()</code> method, or using the <code>java.sql.Date.valueOf()</code> method if the
140     *       string has less than 19 characters</li>
141     *   <li><code>java.time.LocalDateTime</code>: transformed to a <code>java.sql.Timestamp</code> using
142     *       <code>Timestamp.valueOf()</code></li>
143     *   <li><code>java.time.LocalDate</code>: transformed to a LocalDateTime with the time at start of day,
144     *       and then transformed to a <code>java.sql.Timestamp</code> using <code>Timestamp.valueOf()</code></li>
145     *   <li><code>java.time.Instant</code>: transformed to a <code>java.sql.Timestamp</code> using
146     *       <code>Timestamp.from()</code></li>
147     *   <li><code>java.time.ZonedDateTime</code> and <code>java.time.OffsetDateTime</code>: transformed to an Instant
148     *       and then to a <code>java.sql.Timestamp</code> using <code>Timestamp.from()</code>. The time zone is also
149     *       used to create a <code>Calendar</code> passed as third argument of
150     *       <code>PreparedStatement.setTimestamp()</code> to pass the timezone</li>
151     * </ul>
152     * If the value is none of these types, <code>stmt.setObject()</code> is used to bind the value.
153     */
154    public static Binder timestampBinder() {
155        return TIMESTAMP_BINDER;
156    }
157
158    /**
159     * Returns a binder suitable for columns of type TIME or TIME_WITH_TIMEZONE. The returned binder supports values
160     * of type
161     * <ul>
162     *   <li><code>java.sql.Time</code></li>
163     *   <li><code>java.util.Date</code>: the milliseconds of the date are used to construct a
164     *      <code>java.sql.Time</code></li>
165     *   <li><code>java.util.Calendar</code>: the milliseconds of the calendar are used to construct a
166     *      <code>java.sql.Time</code>, and the calendar is passed as third argument of
167     *      <code>PreparedStatement.setTimestamp()</code> to pass the timezone
168     *   </li>
169     *   <li><code>String</code>: the string is transformed to a java.sql.Time using the
170     *       <code>Time.valueOf()</code> method</li>
171     *   <li><code>java.time.LocalTime</code>: transformed to a <code>java.sql.Time</code> using
172     *       <code>Time.valueOf()</code></li>
173     *   <li><code>java.time.OffsetTime</code>: transformed to a <code>LocalTime</code> and then to a
174     *       <code>java.sql.Time</code> using <code>Time.valueOf()</code>. The time zone is also
175     *       used to create a <code>Calendar</code> passed as third argument of
176     *       <code>PreparedStatement.setTime()</code> to pass the timezone</li>
177     * </ul>
178     * If the value is none of these types, <code>stmt.setObject()</code> is used to bind the value.
179     */
180    public static Binder timeBinder() {
181        return TIME_BINDER;
182    }
183
184    /**
185     * Returns a binder suitable for numeric, decimal columns. The returned binder supports values of type
186     * <ul>
187     *   <li><code>String</code>: the string is transformed to a java.math.BigDecimal using its constructor</li>
188     * </ul>
189     * If the value is none of these types, <code>stmt.setObject()</code> is used to bind the value.
190     */
191    public static Binder decimalBinder() {
192        return DECIMAL_BINDER;
193    }
194
195    /**
196     * Returns a binder suitable for numeric, integer columns. The returned binder supports values of type
197     * <ul>
198     *   <li><code>BigInteger</code>: the object is transformed to a String and bound using
199     *       <code>stmt.setObject()</code>, with <code>BIGINT</code> as target type.
200     *   </li>
201     *   <li><code>enum</code>: the enum is transformed into an integer by taking its ordinal</li>
202     *   <li><code>String</code>: the string is bound using <code>stmt.setObject()</code>, with <code>BIGINT</code> as
203     *       target type.
204     *   </li>
205     * </ul>
206     * If the value is none of these types, <code>stmt.setObject()</code> is used to bind the value.
207     */
208    public static Binder integerBinder() {
209        return INTEGER_BINDER;
210    }
211
212    /**
213     * The implementation for {@link Binders#stringBinder()}
214     * @author JB Nizet
215     */
216    private static final class StringBinder implements Binder {
217        @Override
218        public void bind(java.sql.PreparedStatement stmt, int param, Object value) throws java.sql.SQLException {
219            if (value instanceof String) {
220                stmt.setString(param, (String) value);
221            }
222            else if (value instanceof Enum<?>) {
223                stmt.setString(param, ((Enum<?>) value).name());
224            }
225            else if (value == null) {
226                stmt.setObject(param, null);
227            }
228            else {
229                stmt.setString(param, value.toString());
230            }
231        }
232
233        @Override
234        public String toString() {
235            return "Binders.stringBinder";
236        }
237    }
238
239    /**
240     * The implementation for {@link Binders#timeBinder()}
241     * @author JB Nizet
242     */
243    private static final class TimeBinder implements Binder {
244        @Override
245        public void bind(java.sql.PreparedStatement stmt, int param, Object value) throws java.sql.SQLException {
246            if (value instanceof Time) {
247                stmt.setTime(param, (Time) value);
248            }
249            else if (value instanceof java.util.Date) {
250                stmt.setTime(param, new Time(((java.util.Date) value).getTime()));
251            }
252            else if (value instanceof Calendar) {
253                Calendar calendar = (Calendar) value;
254                stmt.setTime(param, new Time(calendar.getTimeInMillis()), calendar);
255            }
256            else if (value instanceof String) {
257                stmt.setTime(param, Time.valueOf((String) value));
258            }
259            else if (value instanceof LocalTime) {
260                stmt.setTime(param, Time.valueOf((LocalTime) value));
261            }
262            else if (value instanceof OffsetTime) {
263                OffsetTime offsetTime = (OffsetTime) value;
264                stmt.setTime(param,
265                             Time.valueOf(offsetTime.toLocalTime()),
266                             Calendar.getInstance(TimeZone.getTimeZone(offsetTime.getOffset())));
267            }
268            else {
269                stmt.setObject(param, value);
270            }
271        }
272
273        @Override
274        public String toString() {
275            return "Binders.timeBinder";
276        }
277    }
278
279    /**
280     * The implementation for {@link Binders#integerBinder()}
281     * @author JB Nizet
282     */
283    private static final class IntegerBinder implements Binder {
284        @Override
285        public void bind(java.sql.PreparedStatement stmt, int param, Object value) throws java.sql.SQLException {
286            if (value instanceof BigInteger) {
287                stmt.setObject(param, value.toString(), Types.BIGINT);
288            }
289            else if (value instanceof Enum<?>) {
290                stmt.setInt(param, ((Enum<?>) value).ordinal());
291            }
292            else if (value instanceof String) {
293                stmt.setObject(param, value, Types.BIGINT);
294            }
295            else {
296                stmt.setObject(param, value);
297            }
298        }
299
300        @Override
301        public String toString() {
302            return "Binders.integerBinder";
303        }
304    }
305
306    /**
307     * The implementation for {@link Binders#decimalBinder()}
308     * @author JB Nizet
309     */
310    private static final class DecimalBinder implements Binder {
311        @Override
312        public void bind(java.sql.PreparedStatement stmt, int param, Object value) throws java.sql.SQLException {
313            if (value instanceof String) {
314                stmt.setBigDecimal(param, new BigDecimal((String) value));
315            }
316            else {
317                stmt.setObject(param, value);
318            }
319        }
320
321        @Override
322        public String toString() {
323            return "Binders.decimalBinder";
324        }
325    }
326
327    /**
328     * The implementation for {@link Binders#timestampBinder()}
329     * @author JB Nizet
330     */
331    private static final class TimestampBinder implements Binder {
332        // the number of chars in yyyy-mm-dd hh:mm:ss
333        private static final int MIN_NUMBER_OF_CHARS_FOR_TIMESTAMP = 19;
334
335        @Override
336        public void bind(java.sql.PreparedStatement stmt, int param, Object value) throws java.sql.SQLException {
337            if (value instanceof Timestamp) {
338                stmt.setTimestamp(param, (Timestamp) value);
339            }
340            else if (value instanceof java.util.Date) {
341                stmt.setTimestamp(param, new Timestamp(((java.util.Date) value).getTime()));
342            }
343            else if (value instanceof Calendar) {
344                stmt.setTimestamp(param, new Timestamp(((Calendar) value).getTimeInMillis()), (Calendar) value);
345            }
346            else if (value instanceof String) {
347                String valueAsString = (String) value;
348                if (valueAsString.length() >= MIN_NUMBER_OF_CHARS_FOR_TIMESTAMP) {
349                    stmt.setTimestamp(param, Timestamp.valueOf(valueAsString));
350                }
351                else {
352                    Date valueAsDate = Date.valueOf(valueAsString);
353                    stmt.setTimestamp(param, new Timestamp(valueAsDate.getTime()));
354                }
355            }
356            else if (value instanceof LocalDateTime) {
357                LocalDateTime localDateTime = (LocalDateTime) value;
358                stmt.setTimestamp(param, Timestamp.valueOf(localDateTime));
359            }
360            else if (value instanceof Instant) {
361                Instant instant = (Instant) value;
362                stmt.setTimestamp(param, Timestamp.from(instant));
363            }
364            else if (value instanceof ZonedDateTime) {
365                ZonedDateTime zonedDateTime = (ZonedDateTime) value;
366                stmt.setTimestamp(param,
367                                  Timestamp.from(zonedDateTime.toInstant()),
368                                  Calendar.getInstance(TimeZone.getTimeZone(zonedDateTime.getZone())));
369            }
370            else if (value instanceof OffsetDateTime) {
371                OffsetDateTime offsetDateTime = (OffsetDateTime) value;
372                stmt.setTimestamp(param,
373                                  Timestamp.from(offsetDateTime.toInstant()),
374                                  Calendar.getInstance(TimeZone.getTimeZone(offsetDateTime.getOffset())));
375            }
376            else if (value instanceof LocalDate) {
377                LocalDate localDate = (LocalDate) value;
378                stmt.setTimestamp(param, Timestamp.valueOf(localDate.atStartOfDay()));
379            }
380            else {
381                stmt.setObject(param, value);
382            }
383        }
384
385        @Override
386        public String toString() {
387            return "Binders.timestampBinder";
388        }
389    }
390
391    /**
392     * The implementation for {@link Binders#dateBinder()}
393     * @author JB Nizet
394     */
395    private static final class DateBinder implements Binder {
396        @Override
397        public void bind(java.sql.PreparedStatement stmt, int param, Object value) throws java.sql.SQLException {
398            if (value instanceof Date) {
399                stmt.setDate(param, (Date) value);
400            }
401            else if (value instanceof java.util.Date) {
402                stmt.setDate(param, new Date(((java.util.Date) value).getTime()));
403            }
404            else if (value instanceof Calendar) {
405                Calendar calendar = (Calendar) value;
406                stmt.setDate(param, new Date(calendar.getTimeInMillis()), calendar);
407            }
408            else if (value instanceof String) {
409                stmt.setDate(param, Date.valueOf((String) value));
410            }
411            else if (value instanceof LocalDate) {
412                LocalDate localDate = (LocalDate) value;
413                stmt.setDate(param, Date.valueOf(localDate));
414            }
415            else if (value instanceof LocalDateTime) {
416                LocalDateTime localDateTime = (LocalDateTime) value;
417                stmt.setDate(param, Date.valueOf(localDateTime.toLocalDate()));
418            }
419            else if (value instanceof Instant) {
420                Instant instant = (Instant) value;
421                stmt.setDate(param, new Date(instant.toEpochMilli()));
422            }
423            else if (value instanceof ZonedDateTime) {
424                ZonedDateTime zonedDateTime = (ZonedDateTime) value;
425                stmt.setDate(param,
426                             new Date(zonedDateTime.toInstant().toEpochMilli()),
427                             Calendar.getInstance(TimeZone.getTimeZone(zonedDateTime.getZone())));
428            }
429            else if (value instanceof OffsetDateTime) {
430                OffsetDateTime offsetDateTime = (OffsetDateTime) value;
431                stmt.setDate(param,
432                             new Date(offsetDateTime.toInstant().toEpochMilli()),
433                             Calendar.getInstance(TimeZone.getTimeZone(offsetDateTime.getOffset())));
434            }
435            else {
436                stmt.setObject(param, value);
437            }
438        }
439
440        @Override
441        public String toString() {
442            return "Binders.dateBinder";
443        }
444    }
445
446    /**
447     * The implementation for {@link Binders#defaultBinder()}
448     * @author JB Nizet
449     */
450    private static final class DefaultBinder implements Binder {
451        @Override
452        public void bind(java.sql.PreparedStatement stmt, int param, Object value) throws java.sql.SQLException {
453            if (value instanceof Enum) {
454                stmt.setString(param, ((Enum) value).name());
455            }
456            else if (value instanceof java.util.Date) {
457                stmt.setTimestamp(param, new Timestamp(((java.util.Date) value).getTime()));
458            }
459            else if (value instanceof Calendar) {
460                Calendar calendar = (Calendar) value;
461                stmt.setTimestamp(param, new Timestamp(calendar.getTime().getTime()), calendar);
462            }
463            else if (value instanceof LocalDate) {
464                stmt.setDate(param, Date.valueOf((LocalDate) value));
465            }
466            else if (value instanceof LocalTime) {
467                stmt.setTime(param, Time.valueOf((LocalTime) value));
468            }
469            else if (value instanceof LocalDateTime) {
470                stmt.setTimestamp(param, Timestamp.valueOf((LocalDateTime) value));
471            }
472            else if (value instanceof Instant) {
473                stmt.setTimestamp(param, Timestamp.from((Instant) value));
474            }
475            else if (value instanceof ZonedDateTime) {
476                ZonedDateTime zonedDateTime = (ZonedDateTime) value;
477                stmt.setTimestamp(param,
478                                  Timestamp.from(zonedDateTime.toInstant()),
479                                  Calendar.getInstance(TimeZone.getTimeZone(zonedDateTime.getZone())));
480            }
481            else if (value instanceof OffsetDateTime) {
482                OffsetDateTime offsetDateTime = (OffsetDateTime) value;
483                stmt.setTimestamp(param,
484                                  Timestamp.from(offsetDateTime.toInstant()),
485                                  Calendar.getInstance(TimeZone.getTimeZone(offsetDateTime.getOffset())));
486            }
487            else if (value instanceof OffsetTime) {
488                OffsetTime offsetTime = (OffsetTime) value;
489                stmt.setTime(param,
490                             Time.valueOf(offsetTime.toLocalTime()),
491                             Calendar.getInstance(TimeZone.getTimeZone(offsetTime.getOffset())));
492            }
493            else {
494                stmt.setObject(param, value);
495            }
496        }
497
498        @Override
499        public String toString() {
500            return "Binders.defaultBinder";
501        }
502    }
503}