DB2iEnvironment.java

package dbfit.environment;

import dbfit.annotations.DatabaseEnvironment;
import dbfit.api.AbstractDbEnvironment;
import dbfit.util.DbParameterAccessor;
import dbfit.util.NameNormaliser;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import dbfit.util.Direction;
import static dbfit.util.Direction.*;

@DatabaseEnvironment(name="DB2i", driver="com.ibm.as400.access.AS400JDBCDriver")
public class DB2iEnvironment extends AbstractDbEnvironment {

    public DB2iEnvironment(String driverClassName) {
        super(driverClassName);
    }

    protected String parseCommandText(String commandText) {
        commandText = commandText.replaceAll(paramNamePattern, "?");
        return super.parseCommandText(commandText);
    }

    private static String paramNamePattern = "[@:]([A-Za-z0-9_]+)";
    private static Pattern paramRegex = Pattern.compile(paramNamePattern);

    public Pattern getParameterPattern() {
        return paramRegex;
    }

    protected String getConnectionString(String dataSource) {
        return "jdbc:as400://" + dataSource;
    }

    protected String getConnectionString(String dataSource, String database) {
        return "jdbc:as400://" + dataSource + "/" + database;
    }

    public Map<String, DbParameterAccessor> getAllColumns(String tableOrViewName)
            throws SQLException {
        String[] qualifiers = NameNormaliser.normaliseName(tableOrViewName)
                .split("\\.");
        String qry = " select name as column_name, coltype as data_type, length, "
                + "'P' as direction from QSYS2.SYSCOLUMNS where ";
        if (qualifiers.length == 2) {
            qry += " lower(dbname)=? and lower(tbname)=? ";
        } else {
            qry += " lower(tbname)=?";
        }
        qry += " order by name";
        return readIntoParams(qualifiers, qry);
    }

    private Map<String, DbParameterAccessor> readIntoParams(
            String[] queryParameters, String query) throws SQLException {
        try (PreparedStatement dc = currentConnection.prepareStatement(query)) {
            for (int i = 0; i < queryParameters.length; i++) {
                dc.setString(i + 1,
                        NameNormaliser.normaliseName(queryParameters[i]));
            }

            ResultSet rs = dc.executeQuery();
            Map<String, DbParameterAccessor> allParams = new HashMap<String, DbParameterAccessor>();
            int position = 0;
            while (rs.next()) {
                String paramName = rs.getString(1);
                if (paramName == null)
                    paramName = "";
                String dataType = rs.getString(2);
                // int length=rs.getInt(3);
                String direction = rs.getString(4);
                Direction paramDirection = getParameterDirection(direction);
                DbParameterAccessor dbp = createDbParameterAccessor(
                        paramName,
                        paramDirection, getSqlType(dataType),
                        getJavaClass(dataType),
                        paramDirection == RETURN_VALUE ? -1 : position++);
                allParams.put(NameNormaliser.normaliseName(paramName), dbp);
            }
            rs.close();
            return allParams;
        }
    }

    private static Direction getParameterDirection(String direction) {
        // iSeries uses QSYS2.SYSPARMS, whose values are:
        //  IN
        //  OUT
        //  INOUT

        // zSeries uses SYSIBM.SYSPARMS, whose values are:
        //  P - Input
        //  O - Output
        //  B - In/Out parameter
        //  C - Result after casting (not applicable for stored procedures)

        if (("P".equals(direction)) ||
           ("IN".equals(direction)))
            return INPUT;
        if (("O".equals(direction)) ||
           ("OUT".equals(direction)))
            return OUTPUT;
        if (("B".equals(direction)) ||
           ("INOUT".equals(direction)))
            return INPUT_OUTPUT;
        if ("C".equals(direction))
            return RETURN_VALUE;
        // todo return val
        throw new UnsupportedOperationException("Direction " + direction
                + " is not supported");
    }

    private static List<String> stringTypes = Arrays.asList(new String[] {
            "VARCHAR", "CHAR", "CHARACTER", "GRAPHIC", "VARGRAPHIC", "CHARACTER VARYING" });
    private static List<String> intTypes = Arrays.asList(new String[] {
            "SMALLINT", "INT", "INTEGER" });
    private static List<String> longTypes = Arrays
            .asList(new String[] { "BIGINT" });
    private static List<String> floatTypes = Arrays
            .asList(new String[] { "FLOAT", "REAL" });
    private static List<String> doubleTypes = Arrays
            .asList(new String[] { "DOUBLE" });
    private static List<String> decimalTypes = Arrays.asList(new String[] {
            "DECIMAL", "DEC", "DECFLOAT", "NUMERIC" });
    private static List<String> dateTypes = Arrays
            .asList(new String[] { "DATE" });
    private static List<String> timestampTypes = Arrays
            .asList(new String[] { "TIMESTAMP", "TIMESTMP" });

    private static String NormaliseTypeName(String dataType) {
        dataType = dataType.toUpperCase().trim();
        return dataType;
    }

    private static int getSqlType(String dataType) {
        dataType = NormaliseTypeName(dataType);

        if (stringTypes.contains(dataType))
            return java.sql.Types.VARCHAR;
        if (decimalTypes.contains(dataType))
            return java.sql.Types.NUMERIC;
        if (intTypes.contains(dataType))
            return java.sql.Types.INTEGER;
        if (floatTypes.contains(dataType))
            return java.sql.Types.FLOAT;
        if (doubleTypes.contains(dataType))
            return java.sql.Types.DOUBLE;
        if (longTypes.contains(dataType))
            return java.sql.Types.BIGINT;
        if (timestampTypes.contains(dataType))
            return java.sql.Types.TIMESTAMP;
        if (dateTypes.contains(dataType))
            return java.sql.Types.DATE;
        throw new UnsupportedOperationException("SQL - Type " + dataType
                + " is not supported");
    }

    public Class<?> getJavaClass(String dataType) {
        dataType = NormaliseTypeName(dataType);
        if (stringTypes.contains(dataType))
            return String.class;
        if (decimalTypes.contains(dataType))
            return BigDecimal.class;
        if (intTypes.contains(dataType))
            return Integer.class;
        if (floatTypes.contains(dataType))
            return Float.class;
        if (dateTypes.contains(dataType))
            return java.sql.Date.class;
        if (doubleTypes.contains(dataType))
            return Double.class;
        if (longTypes.contains(dataType))
            return Long.class;
        if (timestampTypes.contains(dataType))
            return java.sql.Timestamp.class;
        throw new UnsupportedOperationException("Java - Type " + dataType
                + " is not supported");
    }

    public Map<String, DbParameterAccessor> getAllProcedureParameters(
            String procName) throws SQLException {
        // iSeries uses PARMNO, zSeries uses ORDINAL.
        // iSeries uses data_type, zSeries uses TYPENAME.
        // iSeries PARMMODE, zSeries uses ROWTYPE
        String[] qualifiers = NameNormaliser.normaliseName(procName).split(
                "\\.");
        String qry = " select parmname as column_name, data_type as data_type, precision,"
                + "parmmode as direction, parmno from QSYS2.SYSPARMS where ";
        if (qualifiers.length == 2) {
            qry += " lower(specschema)=? and lower(specname)=? ";
        } else {
            qry += " (lower(specname)=?)";
        }
        qry += " order by parmno";
        return readIntoParams(qualifiers, qry);
    }
}