Source code

001/* 
002 * JKNIV, whinstone one contract to access your database.
003 * 
004 * Copyright (C) 2017, the original author or authors.
005 *
006 * This library is free software; you can redistribute it and/or
007 * modify it under the terms of the GNU Lesser General Public
008 * License as published by the Free Software Foundation; either
009 * version 2.1 of the License.
010 * 
011 * This library is distributed in the hope that it will be useful,
012 * but WITHOUT ANY WARRANTY; without even the implied warranty of
013 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
014 * Lesser General Public License for more details.
015 * 
016 * You should have received a copy of the GNU Lesser General Public
017 * License along with this library; if not, write to the Free Software Foundation, Inc., 
018 * 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
019 */
020package net.sf.jkniv.whinstone.jdbc.statement;
021
022import java.sql.PreparedStatement;
023import java.sql.ResultSet;
024import java.sql.ResultSetMetaData;
025import java.sql.SQLException;
026import java.util.Collections;
027import java.util.Iterator;
028import java.util.List;
029import java.util.Map;
030import java.util.Set;
031
032import org.slf4j.Logger;
033
034import net.sf.jkniv.exception.HandlerException;
035import net.sf.jkniv.experimental.TimerKeeper;
036import net.sf.jkniv.reflect.beans.CapitalNameFactory;
037import net.sf.jkniv.reflect.beans.Capitalize;
038import net.sf.jkniv.reflect.beans.ObjectProxy;
039import net.sf.jkniv.reflect.beans.ObjectProxyFactory;
040import net.sf.jkniv.reflect.beans.PropertyAccess;
041import net.sf.jkniv.sqlegance.OneToMany;
042import net.sf.jkniv.sqlegance.RepositoryException;
043import net.sf.jkniv.sqlegance.logger.DataMasking;
044import net.sf.jkniv.whinstone.JdbcColumn;
045import net.sf.jkniv.whinstone.Param;
046import net.sf.jkniv.whinstone.Queryable;
047import net.sf.jkniv.whinstone.ResultRow;
048import net.sf.jkniv.whinstone.ResultSetParser;
049import net.sf.jkniv.whinstone.classification.Groupable;
050import net.sf.jkniv.whinstone.classification.GroupingBy;
051import net.sf.jkniv.whinstone.classification.NoGroupingBy;
052import net.sf.jkniv.whinstone.classification.Transformable;
053import net.sf.jkniv.whinstone.jdbc.DefaultJdbcColumn;
054import net.sf.jkniv.whinstone.jdbc.LoggerFactory;
055import net.sf.jkniv.whinstone.statement.AutoKey;
056import net.sf.jkniv.whinstone.statement.StatementAdapter;
057import net.sf.jkniv.whinstone.types.Convertible;
058import net.sf.jkniv.whinstone.types.NoConverterType;
059
060/**
061 * 
062 * @param <T>
063 * @param <R>
064 * 
065 * @author Alisson Gomes
066 * @since 0.6.0
067 */
068@SuppressWarnings({ "unchecked", "rawtypes" })
069public class JdbcPreparedStatementAdapter<T, R> implements StatementAdapter<T, ResultSet>
070{
071    private static final Logger      LOG     = LoggerFactory.getLogger();
072    private static final Logger      SQLLOG  = net.sf.jkniv.whinstone.jdbc.LoggerFactory.getLogger();
073    private static final DataMasking MASKING = LoggerFactory.getDataMasking();
074    private static final Capitalize  CAPITAL_SETTER  = CapitalNameFactory.getInstanceOfSetter();
075    
076    private final PreparedStatement  stmt;
077    private final HandlerException   handlerException;
078    private int                      index;
079    private ResultRow<T, ResultSet>  resultRow;
080    private Queryable                queryable;
081    private AutoKey                  autoKey;
082    private String[]                 paramNames;
083    
084    public JdbcPreparedStatementAdapter(PreparedStatement stmt, Queryable queryable)
085    {
086        this.stmt = stmt;
087        // FIXME handler exception for default message with custom params
088        this.handlerException = new HandlerException(RepositoryException.class, "Cannot set parameter [%s] value [%s]");
089        this.queryable = queryable;
090        this.paramNames = this.queryable.getParamsNames();
091        this.reset();
092    }
093    
094    @Override
095    public StatementAdapter<T, ResultSet> with(ResultRow<T, ResultSet> resultRow)
096    {
097        this.resultRow = resultRow;
098        return this;
099    }
100    
101    // FIXME Converter/Mapping/Translate values from/to jdbc
102    @Override
103    public StatementAdapter<T, ResultSet> bind(String name, Object value)
104    {
105        log(name, value);
106        try {
107            if (name.toLowerCase().startsWith("in:"))
108            {
109                setValue((Object[]) value);
110            }
111            else
112            {
113                setValue(new Param(value, name, this.index));
114            }
115        }
116        catch (SQLException e)
117        {
118            this.handlerException.handle(e);
119        }
120        return this;
121    }
122    
123    // FIXME Converter/Mapping/Translate values from/to jdbc
124    @Override
125    public StatementAdapter<T, ResultSet> bind(Param param)
126    {
127        log(param);
128        setValue(param);
129        return this;
130    }
131    
132    @Override
133    public StatementAdapter<T, ResultSet> bind(Param... values)
134    {
135        for (int j = 0; j < values.length; j++)
136        {
137            Param v = values[j];
138            bind(v);
139        }
140        return this;
141    }
142    
143    @Override
144    public List<T> rows()
145    {
146        ResultSet rs = null;
147        ResultSetParser<T, ResultSet> rsParser = null;
148        Groupable<T, ?> grouping = new NoGroupingBy<T, T>();
149        List<T> list = Collections.emptyList();
150        try
151        {
152            TimerKeeper.start();
153            rs = stmt.executeQuery();
154            queryable.getDynamicSql().getStats().add(TimerKeeper.clear());
155            
156            JdbcColumn<ResultSet>[] columns = getJdbcColumns(rs.getMetaData());
157            setResultRow(columns);
158            
159            Transformable<T> transformable = resultRow.getTransformable();
160            if (hasGroupingBy())
161            {
162                grouping = new GroupingBy(getGroupingBy(), queryable.getReturnType(), transformable);
163            }
164            rsParser = new ObjectResultSetParser(resultRow, grouping);
165            list = rsParser.parser(rs);
166        }
167        catch (SQLException e)
168        {
169            queryable.getDynamicSql().getStats().add(e);
170            handlerException.handle(e, e.getMessage());
171        }
172        finally
173        {
174            TimerKeeper.clear();
175        }
176        return list;
177    }
178    
179    @Override
180    public void bindKey()
181    {
182        String[] properties = queryable.getDynamicSql().asInsertable().getAutoGeneratedKey().getPropertiesAsArray();
183        ObjectProxy<?> proxy = ObjectProxyFactory.of(queryable.getParams());
184        Iterator<Object> it = autoKey.iterator();
185        for (int i = 0; i < properties.length; i++)
186            setValueOfKey(proxy, properties[i], it.next());
187        /*
188        try
189        {
190            if (!this.queryable.isTypeOfArray() && !this.queryable.isTypeOfCollection())
191            {
192                ObjectProxy<?> proxy = ObjectProxyFactory.newProxy(queryable.getParams());
193                ResultSet generatedKeys = stmt.getGeneratedKeys();
194                
195                if (generatedKeys.next())
196                {
197                    for(int i=0; i<properties.length; i++)
198                        setValue(proxy, properties[i], generatedKeys.getObject(i+1));
199                }
200                while(generatedKeys.next())
201                {
202                    for(int i=0; i<properties.length; i++)
203                        setValue(proxy, properties[i], generatedKeys.getObject(i+1));
204                }
205            }
206            else if(this.queryable.isTypeOfMap())
207            {
208                Map<String, Object> instance = (Map)queryable.getParams();
209                ResultSet generatedKeys = stmt.getGeneratedKeys();
210                if (generatedKeys.next())
211                {
212                    for(int i=0; i<properties.length; i++)
213                        instance.put(properties[i], generatedKeys.getObject(i+1));
214                }
215                while(generatedKeys.next())
216                {
217                    for(int i=0; i<properties.length; i++)
218                        instance.put(properties[i], generatedKeys.getObject(i+1));
219                }                
220            }
221            else
222                handlerException.throwMessage("Cannot set auto generated key for collections or array instance of parameters at query [%s]", queryable.getName());
223        }
224        catch (SQLException sqle)
225        {
226            handlerException.handle(sqle);
227        }
228        */
229    }
230    
231    @Override
232    public StatementAdapter<T, ResultSet> with(AutoKey autoKey)
233    {
234        this.autoKey = autoKey;
235        return this;
236    }
237    
238    public int execute()
239    {
240        int ret = 0;
241        try
242        {
243            TimerKeeper.start();
244            ret = stmt.executeUpdate();
245            queryable.getDynamicSql().getStats().add(TimerKeeper.clear());
246        }
247        catch (SQLException e)
248        {
249            queryable.getDynamicSql().getStats().add(e);
250            handlerException.handle(e, e.getMessage());
251        }
252        return ret;
253    }
254    
255    @Override
256    public int reset()
257    {
258        int before = index;
259        index = 1;
260        return before;
261    }
262    
263    private void setValueOfKey(ObjectProxy<?> proxy, String property, Object value)
264    {
265        Convertible<Object, Object> converter = queryable.getRegisterType().toJdbc(new PropertyAccess(property, proxy.getTargetClass()), proxy);
266        Object parsedValue = value;
267        if (!converter.getType().isInstance(value))
268            parsedValue = converter.toAttribute(value);
269        proxy.invoke(CAPITAL_SETTER.does(property), parsedValue);
270    }
271    
272    private void setValue(Param param)
273    {
274        try
275        {
276            int i = currentIndex();
277            stmt.setObject(i, param.getValueAs());
278        }
279        catch (SQLException e)
280        {
281            this.handlerException.handle(e);
282        }     
283    }
284    
285    private void setValue(Object[] paramsIN) throws SQLException
286    {
287        int j = 0;
288        for (; j < paramsIN.length; j++) {
289            Convertible<Object, Object> convertible = getConverter(this.paramNames[index+j-1]);
290            stmt.setObject(index + j, convertible.toJdbc(paramsIN[j]));
291        }
292    }
293    
294    /**
295     * Retrieve a {@link Convertible} instance to customize the
296     * value of parameter to database field.
297     * @param fieldName name of field
298     * @return A convertible instance if found into class proxy or {@link NoConverterType}
299     * instance when the field or method is not annotated.
300     */
301    private Convertible<Object, Object> getConverter(String fieldName)
302    {
303        Convertible<Object, Object> convertible = NoConverterType.getInstance();
304        if(queryable.getParams() != null &&
305           (queryable.isTypeOfPojo() || queryable.isTypeOfCollectionPojo() || queryable.isTypeOfArrayPojo()))
306        {
307            ObjectProxy<?> proxy = ObjectProxyFactory.of(queryable.getParams());
308            convertible = queryable.getRegisterType().toJdbc(new PropertyAccess(fieldName, queryable.getParams().getClass()), proxy);
309        }
310        return convertible;
311    }
312    
313    private void setResultRow(JdbcColumn<ResultSet>[] columns)
314    {
315        Class<?> returnType = queryable.getReturnType();
316        if (resultRow != null)
317            return;
318        
319        if (queryable.isScalar())
320        {
321            resultRow = new ScalarResultRow(columns);
322        }
323        else if (Map.class.isAssignableFrom(returnType))
324        {
325            resultRow = new MapResultRow(returnType, columns);
326        }
327        else if (Number.class.isAssignableFrom(returnType)) 
328            // FIXME implements for date, calendar, boolean improve design
329        {
330            resultRow = new NumberResultRow(returnType, columns);
331        }
332        else if (String.class.isAssignableFrom(returnType))
333        {
334            resultRow = new StringResultRow(columns);
335        }
336        else if (Boolean.class.isAssignableFrom(returnType))
337        {
338            resultRow = new BooleanResultRow(columns);
339        }
340        else if (!hasOneToMany())
341        {
342            resultRow = new FlatObjectResultRow(returnType, columns);
343        }
344        else
345        {
346            resultRow = new PojoResultRow(returnType, columns, getOneToMany(), queryable.getRegisterType());
347        }
348    }
349    
350    private int currentIndex()
351    {
352        return (index++);
353    }
354    
355    private void log(String name, Object value)
356    {
357        if (SQLLOG.isDebugEnabled())
358            SQLLOG.debug("Setting SQL Parameter from index [{}] with name [{}] with value of [{}] type of [{}]", index,
359                    name, MASKING.mask(name, value), (value == null ? "NULL" : value.getClass()));
360    }
361    
362    private void log(Param param)
363    {
364        //String name = this.paramNames[index-1];
365        if (SQLLOG.isDebugEnabled())
366            SQLLOG.debug("Setting SQL Parameter from index [{}] with name [{}] with value of [{}] type of [{}]", param.getIndex(),
367                    param.getName(), MASKING.mask(param.getName(), param.getValue()), (param.getValue() == null ? "NULL" : param.getValue().getClass()));
368    }
369    
370    /**
371     * Summarize the columns from SQL result in binary data or not.
372     * @param metadata  object that contains information about the types and properties of the columns in a <code>ResultSet</code> 
373     * @return Array of columns with name and index
374     * @throws SQLException Errors that occurs when access {@code ResultSetMetaData} methods.
375     */
376    private JdbcColumn<ResultSet>[] getJdbcColumns(ResultSetMetaData metadata) throws SQLException
377    {
378        JdbcColumn<ResultSet>[] columns = new JdbcColumn[metadata.getColumnCount()];
379        for (int i = 0; i < columns.length; i++)
380        {
381            int columnNumber = i + 1;
382            String columnName = getColumnName(metadata, columnNumber);
383            int columnType = metadata.getColumnType(columnNumber);
384            columns[i] = new DefaultJdbcColumn(columnNumber, columnName, columnType, queryable.getRegisterType(), queryable.getReturnType());
385        }
386        return columns;
387    }
388    
389    private String getColumnName(ResultSetMetaData metaData, int columnIndex) throws SQLException
390    {
391        try
392        {
393            return metaData.getColumnLabel(columnIndex);
394        }
395        catch (SQLException e)
396        {
397            return metaData.getColumnName(columnIndex);
398        }
399    }
400    
401    @Override
402    public void close()
403    {
404        if (this.stmt != null)
405        {
406            try
407            {
408                this.stmt.close();
409            }
410            catch (SQLException e)
411            {
412                LOG.warn("Cannot close prepared statement from query [{}]", this.queryable.getName(), e);
413            }
414        }
415    }
416    
417    @Override
418    public void setFetchSize(int rows)
419    {
420        try
421        {
422            this.stmt.setFetchSize(rows);
423        }
424        catch (SQLException e)
425        {
426            // TODO handler exception for Statement setFetchSize exception
427            this.handlerException.handle(e);
428        }
429    }
430    
431    private boolean hasOneToMany()
432    {
433        return !queryable.getDynamicSql().asSelectable().getOneToMany().isEmpty();
434    }
435    
436    private Set<OneToMany> getOneToMany()
437    {
438        return queryable.getDynamicSql().asSelectable().getOneToMany();
439    }
440    
441    private boolean hasGroupingBy()
442    {
443        return !queryable.getDynamicSql().asSelectable().getGroupByAsList().isEmpty();
444    }
445    
446    private List<String> getGroupingBy()
447    {
448        return queryable.getDynamicSql().asSelectable().getGroupByAsList();
449    }
450}