001package com.hfg.sql.table.field;
002
003import java.sql.PreparedStatement;
004import java.sql.ResultSet;
005import java.sql.SQLException;
006import java.sql.Timestamp;
007import java.sql.Types;
008import java.time.Instant;
009import java.time.ZonedDateTime;
010import java.time.format.DateTimeFormatter;
011import java.util.Date;
012
013import com.hfg.datetime.DateUtil;
014import com.hfg.sql.SQLUtil;
015import com.hfg.sql.jdbc.JDBCException;
016import com.hfg.sql.table.DatabaseCol;
017import com.hfg.sql.table.DatabaseTable;
018import com.hfg.xml.XMLTag;
019
020//------------------------------------------------------------------------------
021/**
022 Database field that manages a date value.
023 <div>
024 @author J. Alex Taylor, hairyfatguy.com
025 </div>
026 */
027//------------------------------------------------------------------------------
028// com.hfg XML/HTML Coding Library
029//
030// This library is free software; you can redistribute it and/or
031// modify it under the terms of the GNU Lesser General Public
032// License as published by the Free Software Foundation; either
033// version 2.1 of the License, or (at your option) any later version.
034//
035// This library is distributed in the hope that it will be useful,
036// but WITHOUT ANY WARRANTY; without even the implied warranty of
037// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
038// Lesser General Public License for more details.
039//
040// You should have received a copy of the GNU Lesser General Public
041// License along with this library; if not, write to the Free Software
042// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
043//
044// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
045// jataylor@hairyfatguy.com
046//------------------------------------------------------------------------------
047
048public class DatabaseDateField extends DatabaseField<Instant>
049{
050   //###########################################################################
051   // CONSTRUCTORS
052   //###########################################################################
053
054   //---------------------------------------------------------------------------
055   public DatabaseDateField(DatabaseCol inCol)
056   {
057      this(inCol, (Object) null);
058   }
059   
060   //---------------------------------------------------------------------------
061   public DatabaseDateField(DatabaseCol inCol, Date inValue)
062   {
063      super(inCol, inValue != null ? inValue.toInstant() : null);
064   }
065
066   //---------------------------------------------------------------------------
067   public DatabaseDateField(DatabaseCol inCol, Instant inValue)
068   {
069      super(inCol, inValue);
070   }
071
072   //---------------------------------------------------------------------------
073   public DatabaseDateField(DatabaseCol inCol, Object inValue)
074   {
075      super(inCol, convertToInstant(inValue));
076   }
077
078   //---------------------------------------------------------------------------
079   public DatabaseDateField(DatabaseCol inCol, ResultSet inResultSet)
080   {
081      super(inCol, inResultSet);
082   }
083
084   //---------------------------------------------------------------------------
085   public DatabaseDateField(XMLTag inXMLTag, DatabaseTable inTable)
086   {
087      super(inXMLTag, inTable);
088   }
089
090   //###########################################################################
091   // PUBLIC METHODS
092   //###########################################################################
093
094   //---------------------------------------------------------------------------
095   protected void setValueFromResultSet(ResultSet inResultSet)
096   {
097      // Retrieve the index for the ResultSet column with the matching name.
098      // If no column with the proper name is present in the ResultSet, don't do anything.
099      Integer index = getColIndex(inResultSet);
100      if (index != null)
101      {
102         try
103         {
104            Timestamp timestamp = inResultSet.getTimestamp(index);
105            setInitialValue(null == timestamp ? null : Instant.ofEpochMilli(timestamp.getTime()));
106         }
107         catch (SQLException e)
108         {
109            try
110            {
111               throw new JDBCException("Problem mapping " + getCol().name() + " value: " + inResultSet.getString(getCol().name()) + "!", e);
112            }
113            catch (SQLException e2)
114            {
115               throw new JDBCException("Problem mapping " + getCol().name() + " value!", e);
116            }
117         }
118      }
119   }
120
121   //---------------------------------------------------------------------------
122   public void setValueInPreparedStatement(PreparedStatement inPreparedStatement, int inIndex)
123   {
124      try
125      {
126         if (isNull())
127         {
128            inPreparedStatement.setNull(inIndex, Types.TIMESTAMP_WITH_TIMEZONE);
129         }
130         else
131         {
132            inPreparedStatement.setTimestamp(inIndex, new Timestamp(getValue().toEpochMilli()));
133         }
134      }
135      catch (SQLException e)
136      {
137         throw new JDBCException("Problem setting column " + getCol().name() + " value into PreparedStatement!", e);
138      }
139   }
140
141   //---------------------------------------------------------------------------
142   public void setValueFromString(String inValue)
143   {
144      setValue(convertToInstant(inValue, getCol()));
145   }
146
147   //---------------------------------------------------------------------------
148   @Override
149   public String getSQLValue()
150   {
151      String valueString = null;
152
153      if (getValue() != null)
154      {
155         DateTimeFormatter formatter = getCol().getTable().getRDBMS().getSQLDateFormatter(getCol().getType());
156
157         valueString = formatter.format(DateUtil.convertToUTCZonedDateTime(getValue()));
158      }
159
160      return SQLUtil.sqlString(valueString);
161   }
162
163   //###########################################################################
164   // PRIVATE METHODS
165   //###########################################################################
166
167   //---------------------------------------------------------------------------
168   private Instant convertToInstant(Object inValue, DatabaseCol inCol)
169   {
170      Instant dateValue = null;
171      if (inValue != null)
172      {
173         dateValue = convertToInstant(inValue);
174         if (null == dateValue
175               && inValue instanceof String)
176         {
177            DateTimeFormatter formatter = inCol.getTable().getRDBMS().getSQLDateFormatter(getCol().getType());
178            try
179            {
180               dateValue = ZonedDateTime.parse((String) inValue, formatter).toInstant();
181            }
182            catch (Exception e)
183            {
184               try
185               {
186                  dateValue = ZonedDateTime.parse((String) inValue, DateTimeFormatter.ISO_OFFSET_DATE_TIME).toInstant();
187               }
188               catch (Exception e2)
189               {
190                   // Ignore
191               }
192            }
193         }
194      }
195
196      return dateValue;
197   }
198
199   //---------------------------------------------------------------------------
200   private static Instant convertToInstant(Object inValue)
201   {
202      Instant instantValue = null;
203      if (inValue != null)
204      {
205         if (inValue instanceof Date)
206         {
207            instantValue = ((Date) inValue).toInstant();
208         }
209         else if (inValue instanceof java.sql.Date)
210         {
211            instantValue = ((java.sql.Date) inValue).toInstant();
212         }
213         else if (inValue instanceof String)
214         {
215            try
216            {
217               instantValue = ZonedDateTime.parse((String) inValue, DateTimeFormatter.ISO_OFFSET_DATE_TIME).toInstant();
218            }
219            catch (Exception e2)
220            {
221               // Ignore
222            }
223         }
224      }
225
226      return instantValue;
227   }
228}