Application: gvSIG desktop

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2020 gvSIG Association.

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 3

 * of the License, or (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.fmap.dal.store.jdbc2.spi;

import java.math.BigDecimal;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Time;

import java.sql.Timestamp;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

import java.util.Objects;

import org.cresques.cts.IProjection;

import org.gvsig.expressionevaluator.ExpressionBuilder.Parameter;

import org.gvsig.expressionevaluator.GeometryExpressionBuilderHelper.GeometrySupportType;

import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor;

import org.gvsig.fmap.dal.feature.FeatureReference;

import org.gvsig.fmap.dal.feature.FeatureType;

import org.gvsig.fmap.dal.feature.spi.FeatureProvider;

import org.gvsig.fmap.dal.feature.spi.FeatureReferenceProviderServices;

import org.gvsig.fmap.dal.feature.spi.SQLBuilderBase;

import org.gvsig.fmap.dal.store.jdbc2.JDBCConnection;

import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper;

import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryLocator;

import org.gvsig.fmap.geom.GeometryManager;

import org.gvsig.fmap.geom.aggregate.MultiLine;

import org.gvsig.fmap.geom.aggregate.MultiPoint;

import org.gvsig.fmap.geom.aggregate.MultiPolygon;

import org.gvsig.fmap.geom.exception.CreateGeometryException;

import org.gvsig.fmap.geom.primitive.Primitive;

import org.gvsig.fmap.geom.type.GeometryType;

import org.gvsig.tools.ToolsLocator;

import org.gvsig.tools.dataTypes.DataType;

import org.gvsig.tools.dataTypes.DataTypes;

import org.gvsig.tools.dataTypes.DataTypesManager;

import org.gvsig.tools.dispose.Disposable;

@SuppressWarnings("UseSpecificCatch")

public class JDBCSQLBuilderBase extends SQLBuilderBase {

    private GeometryManager geometryManager = null;

    protected final JDBCHelper helper;

    public JDBCSQLBuilderBase(JDBCHelper helper) {

        super();

        this.helper = helper;

    }

    public JDBCHelper getHelper() {

        return helper;

    }

    protected GeometryManager getGeometryManager() {

        if (this.geometryManager == null) {

            this.geometryManager = GeometryLocator.getGeometryManager();

        }

        return this.geometryManager;

    }

    @Override

    public Object srs_id(IProjection projection) {

        JDBCConnection conn = null;

        try {

            conn = this.helper.getConnection();

            SRSSolver solver = this.helper.getSRSSolver();

            Object srscode = solver.getDatabaseCode(conn, projection);

//            logger.debug("database code srs {}, type {}, srssolver {}.",

//                new Object[] {

//                    srscode,

//                    srscode==null? "null":srscode.getClass().getSimpleName(),

//                    solver

//                }

//            );

            return srscode;

        } catch (Exception ex) {

            throw new RuntimeException("Can't locate database code for SRS '"+projection.getAbrev()+"'.", ex);

        } finally {

            conn.closeQuietly();

        }

    }

    public void setParameters(PreparedStatement st) {

        try {

            int columnIndex = 1;

            for (Parameter parameter : this.parameters()) {

                st.setObject(columnIndex++, parameter.value());

            }

        } catch (Exception ex) {

            String p = "unknow";

            try {

                p =  this.parameters().toString();

            } catch (Exception ex2) {

                // Do nothing

            }

            throw new RuntimeException("Can't set parameters to prepared statement from parameters (" + p + ")", ex);

        }

    }

    public List<Object> getParameters(FeatureProvider feature) {

        return getParameters(feature, null);

    }

    public List<Object> getParameters(FeatureProvider feature, List<Integer> types) {

        try {

            DataTypesManager dataTypesManager = ToolsLocator.getDataTypesManager();

            FeatureType type = feature.getType();

            List<Object> values = new ArrayList<>();

            Object value;

            for (Parameter parameter : this.parameters()) {

                if (parameter.is_constant()) {

                    value = parameter.value();

                    values.add(value);

                    if(types != null) {

                        if (value == null) {

                            types.add(org.gvsig.fmap.dal.DataTypes.OBJECT);

                        } else {

                            DataType dataType = dataTypesManager.getDataType(value.getClass());

                            types.add(dataType.getType());

                        }

                    }

                } else {

                    String name = parameter.name();

                    value = feature.get(name);

                    FeatureAttributeDescriptor attrDesc = type.getAttributeDescriptor(name);

                    switch( attrDesc.getType() ) {

                    case org.gvsig.fmap.dal.DataTypes.DATE:

                        if( value == null ) {

                            values.add(null);

                        } else {

                            values.add(new java.sql.Date(((Date)value).getTime()));

                        }

                        if (types != null) {

                            types.add(org.gvsig.fmap.dal.DataTypes.DATE);

                        }

                        break;

                    case org.gvsig.fmap.dal.DataTypes.GEOMETRY:

                        Geometry geom = this.forceGeometryType(

                            attrDesc.getGeomType(),

                            (Geometry)value

                        );

                        values.add(geom);

                        if (types != null) {

                            types.add(org.gvsig.fmap.dal.DataTypes.GEOMETRY);

                        }

                        break;

                    default:

                        values.add(value);

                        if (types != null) {

                            types.add(attrDesc.getDataType().getType());

                        }

                        break;

                    }

                }

            }

            return  values;

        } catch (Exception ex) {

            String f = "unknow";

            try {

                f = feature.toString();

            } catch (Exception ex2) {

                // Do nothing

            }

            throw new RuntimeException("Can't get parameters to prepared statement from the feature (" + f + ")", ex);

        }

    }

    public Disposable setParameters(PreparedStatement st, FeatureProvider feature) {

        try {

            List<Integer> types = new ArrayList<>();

            List<Object> values = this.getParameters(feature, types);

            return this.setStatementParameters(st, values, types, this.geometry_support_type());

        } catch (Exception ex) {

            String f = "unknow";

            try {

                f = feature.toString();

            } catch (Exception ex2) {

                // Do nothing

            }

            throw new RuntimeException("Can't set parameters to prepared statement from the feature (" + f + ")", ex);

        }

    }

    protected Geometry forceGeometryType(GeometryType geomtype, Geometry geom) throws CreateGeometryException {

        if( geom == null ) {

            return null;

        }

        switch( geomtype.getType() ) {

        case Geometry.TYPES.MULTIPOLYGON:

            if( geom.getType()==Geometry.TYPES.POLYGON ) {

                MultiPolygon x = getGeometryManager().createMultiPolygon(geomtype.getSubType());

                x.addPrimitive((Primitive) geom);

                geom = x;

            }

            break;

        case Geometry.TYPES.MULTILINE:

            if( geom.getType()==Geometry.TYPES.LINE ) {

                MultiLine x = getGeometryManager().createMultiLine(geomtype.getSubType());

                x.addPrimitive((Primitive) geom);

                geom = x;

            }

            break;

        case Geometry.TYPES.MULTIPOINT:

            if( geom.getType()==Geometry.TYPES.POINT ) {

                MultiLine x = getGeometryManager().createMultiLine(geomtype.getSubType());

                x.addPrimitive((Primitive) geom);

                geom = x;

            }

            break;

        case Geometry.TYPES.POLYGON:

            if( geom.getType()==Geometry.TYPES.MULTIPOLYGON ) {

                MultiPolygon x = (MultiPolygon) geom;

                if( x.getPrimitivesNumber()==1 ) {

                    geom = x.getPrimitiveAt(0);

                }

            }

            break;

        case Geometry.TYPES.LINE:

            if( geom.getType()==Geometry.TYPES.MULTILINE ) {

                MultiLine x = (MultiLine) geom;

                if( x.getPrimitivesNumber()==1 ) {

                    geom = x.getPrimitiveAt(0);

                }

            }

            break;

        case Geometry.TYPES.POINT:

            if( geom.getType()==Geometry.TYPES.MULTIPOINT ) {

                MultiPoint x = (MultiPoint) geom;

                if( x.getPrimitivesNumber()==1 ) {

                    geom = x.getPrimitiveAt(0);

                }

            }

        }

        return geom;

    }

    public Disposable setParameters(PreparedStatement st, FeatureReference reference) {

        try {

            List<Object> values = new ArrayList<>();

            for (Parameter parameter : this.parameters()) {

                if (parameter.is_constant()) {

                    values.add(parameter.value());

                } else {

                    String name = parameter.name();

                    values.add(((FeatureReferenceProviderServices)reference).getKeyValue(name));

                }

            }

            return this.setStatementParameters(st, values, null, this.geometry_support_type());

        } catch (Exception ex) {

            String f = "unknow";

            try {

                f = reference.toString();

            } catch (Exception ex2) {

                // Do nothing

            }

            throw new RuntimeException("Can't set parameters to prepared statement from the feature (" + f + ")", ex);

        }

    }

    public Disposable setStatementParameters(

        PreparedStatement st,

        List values,

        GeometrySupportType geometrySupportType) throws SQLException {

        return setStatementParameters(st, values, null, geometrySupportType);

    }

    public Disposable setStatementParameters(

        PreparedStatement st,

        List values,

        List<Integer> types, //Can be null

        GeometrySupportType geometrySupportType) throws SQLException {

        if (values == null) {

            return new Disposable() {

                @Override

                public void dispose() {

                }

            };

        }

        if( LOGGER.isDebugEnabled() ) {

            debug_setStatementParameters(st, values, types, geometrySupportType);

        }

        byte[] bytes;

        int columnIndex = 1;

        try {

            for (Object value : values) {

                if (value instanceof Geometry) {

                    switch(geometrySupportType) {

                        case WKT:

                            value = ((Geometry) value).convertToWKT();

                            st.setObject(columnIndex, value);

                            break;

                        case NATIVE:

                            bytes = getNativeBytes(st.getConnection(),(Geometry) value);

                            st.setBytes(columnIndex, bytes);

                            break;

                        case WKB:

                            bytes = ((Geometry) value).convertToWKB();

                            st.setBytes(columnIndex, bytes);

                            break;

                        case EWKB:

                            bytes = ((Geometry) value).convertToEWKB();

                            st.setBytes(columnIndex, bytes);

                            break;

                    }

                } else {

                    if(types == null){

                        st.setObject(columnIndex, value);

                    } else {

                        setStatementValue(st, columnIndex, types.get(columnIndex-1), value);

                    }

                }

                columnIndex++;

            }

            return new Disposable() {

                @Override

                public void dispose() {

                }

            };

        } catch(Exception ex) {

            throw new SQLException("Can't set values for the prepared statement.", ex);

        }

    }

    protected byte[] getNativeBytes(Connection conn, Geometry geometry) throws Exception {

        byte[] bytes = geometry.convertToWKB();

        return bytes;

    }

    protected void debug_setStatementParameters(

        PreparedStatement st,

        List values,

        List<Integer> types, //Can be null

        GeometrySupportType geometrySupportType) throws SQLException {

        StringBuilder debug = new StringBuilder();

        debug.append("[");

        debug.append(JDBCUtils.getConnId(st));

        debug.append("] st.set(");

        try {

            byte[] bytes;

            int columnIndex = 1;

            for (Object value : values) {

                if (value instanceof Geometry) {

                    switch(geometrySupportType) {

                        case WKT:

                            value = ((Geometry) value).convertToWKT();

                            debug.append("/*");

                            debug.append(columnIndex);

                            debug.append("*/ ");

                            debug.append(as_string(value));

                            debug.append(", ");

                            break;

                        case NATIVE:

                        case WKB:

                            bytes = ((Geometry) value).convertToWKB();

                            debug.append("/*");

                            debug.append(columnIndex);

                            debug.append("*/ ");

                            debug.append(as_string(bytes));

                            debug.append(", ");

                            break;

                        case EWKB:

                            bytes = ((Geometry) value).convertToEWKB();

                            debug.append("/*");

                            debug.append(columnIndex);

                            debug.append("*/ ");

                            debug.append(as_string(bytes));

                            debug.append(", ");

                            break;

                    }

                } else {

                    debug.append("/*");

                    debug.append(columnIndex);

                    debug.append("*/ ");

                    if( value instanceof String ) {

                        debug.append(as_string(value));

                    } else if( value instanceof Boolean ) {

                        debug.append( ((Boolean)value)? constant_true:constant_false );

                    } else {

                        debug.append(value);

                    }

                    debug.append(", ");

                }

                columnIndex++;

            }

            debug.append(")");

            LOGGER.debug(debug.toString());

        } catch(Exception ex) {

        }

    }

    protected void setStatementValue(PreparedStatement st, int columnIndex, int type, Object value) throws SQLException {

        switch (type){

            case DataTypes.BOOLEAN:

                if(value == null){

                    st.setNull(columnIndex, java.sql.Types.BIT);

                } else {

                    st.setBoolean(columnIndex, (boolean) value);

                }

                break;

            case DataTypes.INTEGER:

                if(value == null){

                    st.setNull(columnIndex, java.sql.Types.INTEGER);

                } else {

                    st.setInt(columnIndex, (int) value);

                }

                break;

            case DataTypes.BYTE:

                if(value == null){

                    st.setNull(columnIndex, java.sql.Types.TINYINT);

                } else {

                    st.setByte(columnIndex, (byte) value);

                }

                break;

            case DataTypes.LONG:

                if(value == null){

                    st.setNull(columnIndex, java.sql.Types.BIGINT);

                } else {

                    st.setLong(columnIndex, (long) value);

                }

                break;

            case DataTypes.FLOAT:

                if(value == null){

                    st.setNull(columnIndex, java.sql.Types.REAL);

                } else {

                    st.setFloat(columnIndex, (float) value);

                }

                break;

            case DataTypes.DOUBLE:

                if(value == null){

                    st.setNull(columnIndex, java.sql.Types.DOUBLE);

                } else {

                    st.setDouble(columnIndex, (double) value);

                }

                break;

            case DataTypes.DECIMAL:

                if(value == null){

                    st.setNull(columnIndex, java.sql.Types.DECIMAL);

                } else {

                    st.setBigDecimal(columnIndex, (BigDecimal) value);

                }

                break;

            case DataTypes.URL:

            case DataTypes.URI:

            case DataTypes.FILE:

                value = Objects.toString(value, null);

            case DataTypes.STRING:

                if(value == null){

                    st.setNull(columnIndex, java.sql.Types.VARCHAR);

                } else {

                    st.setString(columnIndex, (String) value);

                }

                break;

            case DataTypes.TIMESTAMP:

                if(value == null){

                    st.setNull(columnIndex, java.sql.Types.TIMESTAMP);

                } else {

                    st.setTimestamp(columnIndex, (Timestamp) value);

                }

                break;

            case DataTypes.TIME:

                if(value == null){

                    st.setNull(columnIndex, java.sql.Types.TIME);

                } else {

                    st.setTime(columnIndex, (Time) value);

                }

                break;

            case DataTypes.DATE:

                if(value == null){

                    st.setNull(columnIndex, java.sql.Types.DATE);

                } else {

                    st.setDate(columnIndex, (java.sql.Date)value);

                }

                break;

            case DataTypes.BYTEARRAY:

                if(value == null){

                    st.setNull(columnIndex, java.sql.Types.BINARY);

                } else {

                    st.setBytes(columnIndex, (byte[]) value);

                }

                break;

            default:

                st.setObject(columnIndex, value);

                LOGGER.debug("Attention, using a statement.setObject");

        }

    }

}