Example usage for java.sql Types NUMERIC

List of usage examples for java.sql Types NUMERIC

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Types
  6. NUMERIC

Introduction

In this page you can find the example usage for java.sql Types NUMERIC.

Prototype

int NUMERIC

To view the source code for java.sql Types NUMERIC.

Click Source Link

Document

The constant in the Java programming language, sometimes referred to as a type code, that identifies the generic SQL type NUMERIC.

Usage

From source file:com.glaf.core.jdbc.QueryHelper.java

/**
 * @param conn/*from   ww w  .  j  a  v a 2s  . com*/
 *            ?
 * @param sqlExecutor
 *            
 * @return
 */
@SuppressWarnings("unchecked")
public List<Map<String, Object>> getResultList(Connection conn, SqlExecutor sqlExecutor) {
    if (!DBUtils.isLegalQuerySql(sqlExecutor.getSql())) {
        throw new RuntimeException(" SQL statement illegal ");
    }
    List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
    PreparedStatement psmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    try {
        psmt = conn.prepareStatement(sqlExecutor.getSql());
        if (sqlExecutor.getParameter() != null) {
            List<Object> values = (List<Object>) sqlExecutor.getParameter();
            JdbcUtils.fillStatement(psmt, values);
        }

        rs = psmt.executeQuery();

        if (conf.getBoolean("useMyBatisResultHandler", false)) {

            resultList = this.getResults(rs);

        } else {

            rsmd = rs.getMetaData();

            int count = rsmd.getColumnCount();
            List<ColumnDefinition> columns = new ArrayList<ColumnDefinition>();

            for (int index = 1; index <= count; index++) {
                int sqlType = rsmd.getColumnType(index);
                ColumnDefinition column = new ColumnDefinition();
                column.setIndex(index);
                column.setColumnName(rsmd.getColumnName(index));
                column.setColumnLabel(rsmd.getColumnLabel(index));
                column.setJavaType(FieldType.getJavaType(sqlType));
                column.setPrecision(rsmd.getPrecision(index));
                column.setScale(rsmd.getScale(index));
                if (column.getScale() == 0 && sqlType == Types.NUMERIC) {
                    column.setJavaType("Long");
                }
                column.setName(StringTools.camelStyle(column.getColumnLabel().toLowerCase()));
                columns.add(column);
            }
            int startIndex = 1;
            while (rs.next() && startIndex <= 50000) {
                int index = 0;
                startIndex++;
                Map<String, Object> rowMap = new HashMap<String, Object>();
                Iterator<ColumnDefinition> iterator = columns.iterator();
                while (iterator.hasNext()) {
                    ColumnDefinition column = iterator.next();
                    String columnLabel = column.getColumnLabel();
                    String columnName = column.getColumnName();
                    if (StringUtils.isEmpty(columnName)) {
                        columnName = column.getColumnLabel();
                    }
                    columnName = columnName.toLowerCase();
                    String javaType = column.getJavaType();
                    index = index + 1;
                    if ("String".equals(javaType)) {
                        String value = rs.getString(column.getIndex());
                        if (value != null) {
                            value = value.trim();
                            rowMap.put(columnName, value);
                            rowMap.put(columnLabel, value);
                        }
                    } else if ("Integer".equals(javaType)) {
                        try {
                            Integer value = rs.getInt(column.getIndex());
                            rowMap.put(columnName, value);
                            rowMap.put(columnLabel, value);
                        } catch (Exception e) {
                            String str = rs.getString(column.getIndex());
                            logger.error("integer:" + str);
                            str = StringTools.replace(str, "$", "");
                            str = StringTools.replace(str, "", "");
                            str = StringTools.replace(str, ",", "");
                            NumberFormat fmt = NumberFormat.getInstance();
                            Number num = fmt.parse(str);
                            rowMap.put(columnName, num.intValue());
                            rowMap.put(columnLabel, rowMap.get(columnName));
                            logger.debug("?:" + num.intValue());
                        }
                    } else if ("Long".equals(javaType)) {
                        try {
                            Long value = rs.getLong(column.getIndex());
                            rowMap.put(columnName, value);
                            rowMap.put(columnLabel, rowMap.get(columnName));
                        } catch (Exception e) {
                            String str = rs.getString(column.getIndex());
                            logger.error("long:" + str);
                            str = StringTools.replace(str, "$", "");
                            str = StringTools.replace(str, "", "");
                            str = StringTools.replace(str, ",", "");
                            NumberFormat fmt = NumberFormat.getInstance();
                            Number num = fmt.parse(str);
                            rowMap.put(columnName, num.longValue());
                            rowMap.put(columnLabel, num.longValue());
                            logger.debug("?:" + num.longValue());
                        }
                    } else if ("Double".equals(javaType)) {
                        try {
                            Double d = rs.getDouble(column.getIndex());
                            rowMap.put(columnName, d);
                            rowMap.put(columnLabel, d);
                        } catch (Exception e) {
                            String str = rs.getString(column.getIndex());
                            logger.error("double:" + str);
                            str = StringTools.replace(str, "$", "");
                            str = StringTools.replace(str, "", "");
                            str = StringTools.replace(str, ",", "");
                            NumberFormat fmt = NumberFormat.getInstance();
                            Number num = fmt.parse(str);
                            rowMap.put(columnName, num.doubleValue());
                            rowMap.put(columnLabel, num.doubleValue());
                            logger.debug("?:" + num.doubleValue());
                        }
                    } else if ("Boolean".equals(javaType)) {
                        rowMap.put(columnName, rs.getBoolean(column.getIndex()));
                        rowMap.put(columnLabel, rowMap.get(columnName));
                    } else if ("Date".equals(javaType)) {
                        rowMap.put(columnName, rs.getTimestamp(column.getIndex()));
                        rowMap.put(columnLabel, rowMap.get(columnName));
                    } else if ("Blob".equals(javaType)) {
                        // ignore
                    } else {
                        Object value = rs.getObject(column.getIndex());
                        if (value != null) {
                            if (value instanceof String) {
                                value = (String) value.toString().trim();
                            }
                            rowMap.put(columnName, value);
                            rowMap.put(columnLabel, rowMap.get(columnName));
                        }
                    }
                }
                rowMap.put("startIndex", startIndex);
                resultList.add(rowMap);
            }
        }

        logger.debug(">resultList size=" + resultList.size());
        return resultList;
    } catch (Exception ex) {
        logger.error(ex);
        ex.printStackTrace();
        throw new RuntimeException(ex);
    } finally {
        JdbcUtils.close(psmt);
        JdbcUtils.close(rs);
    }
}

From source file:org.pentaho.di.jdbc.Support.java

/**
 * Convert an existing data object to the specified JDBC type.
 *
 * @param callerReference an object reference to the caller of this method;
 *                        must be a <code>Connection</code>,
 *                        <code>Statement</code> or <code>ResultSet</code>
 * @param x               the data object to convert
 * @param jdbcType        the required type constant from
 *                        <code>java.sql.Types</code>
 * @return the converted data object//ww w .  j  av  a  2s.c o m
 * @throws SQLException if the conversion is not supported or fails
 */
static Object convert(Object callerReference, Object x, int jdbcType, String charSet) throws SQLException {
    try {
        switch (jdbcType) {
        case java.sql.Types.TINYINT:
        case java.sql.Types.SMALLINT:
        case java.sql.Types.INTEGER:
            if (x == null) {
                return INTEGER_ZERO;
            } else if (x instanceof Integer) {
                return x;
            } else if (x instanceof Byte) {
                return new Integer(((Byte) x).byteValue() & 0xFF);
            } else if (x instanceof Number) {
                return new Integer(((Number) x).intValue());
            } else if (x instanceof String) {
                return new Integer(((String) x).trim());
            } else if (x instanceof Boolean) {
                return ((Boolean) x).booleanValue() ? INTEGER_ONE : INTEGER_ZERO;
            }
            break;

        case java.sql.Types.BIGINT:
            if (x == null) {
                return LONG_ZERO;
            } else if (x instanceof Long) {
                return x;
            } else if (x instanceof Byte) {
                return new Long(((Byte) x).byteValue() & 0xFF);
            } else if (x instanceof Number) {
                return new Long(((Number) x).longValue());
            } else if (x instanceof String) {
                return new Long(((String) x).trim());
            } else if (x instanceof Boolean) {
                return ((Boolean) x).booleanValue() ? LONG_ONE : LONG_ZERO;
            }

            break;

        case java.sql.Types.REAL:
            if (x == null) {
                return FLOAT_ZERO;
            } else if (x instanceof Float) {
                return x;
            } else if (x instanceof Byte) {
                return new Float(((Byte) x).byteValue() & 0xFF);
            } else if (x instanceof Number) {
                return new Float(((Number) x).floatValue());
            } else if (x instanceof String) {
                return new Float(((String) x).trim());
            } else if (x instanceof Boolean) {
                return ((Boolean) x).booleanValue() ? FLOAT_ONE : FLOAT_ZERO;
            }

            break;

        case java.sql.Types.FLOAT:
        case java.sql.Types.DOUBLE:
            if (x == null) {
                return DOUBLE_ZERO;
            } else if (x instanceof Double) {
                return x;
            } else if (x instanceof Byte) {
                return new Double(((Byte) x).byteValue() & 0xFF);
            } else if (x instanceof Number) {
                return new Double(((Number) x).doubleValue());
            } else if (x instanceof String) {
                return new Double(((String) x).trim());
            } else if (x instanceof Boolean) {
                return ((Boolean) x).booleanValue() ? DOUBLE_ONE : DOUBLE_ZERO;
            }

            break;

        case java.sql.Types.NUMERIC:
        case java.sql.Types.DECIMAL:
            if (x == null) {
                return null;
            } else if (x instanceof BigDecimal) {
                return x;
            } else if (x instanceof Number) {
                return new BigDecimal(x.toString());
            } else if (x instanceof String) {
                return new BigDecimal((String) x);
            } else if (x instanceof Boolean) {
                return ((Boolean) x).booleanValue() ? BIG_DECIMAL_ONE : BIG_DECIMAL_ZERO;
            }

            break;

        case java.sql.Types.VARCHAR:
        case java.sql.Types.CHAR:
            if (x == null) {
                return null;
            } else if (x instanceof String) {
                return x;
            } else if (x instanceof Number) {
                return x.toString();
            } else if (x instanceof Boolean) {
                return ((Boolean) x).booleanValue() ? "1" : "0";
            } else if (x instanceof Clob) {
                Clob clob = (Clob) x;
                long length = clob.length();

                if (length > Integer.MAX_VALUE) {
                    throw new SQLException(BaseMessages.getString(PKG, "error.normalize.lobtoobig"), "22000");
                }

                return clob.getSubString(1, (int) length);
            } else if (x instanceof Blob) {
                Blob blob = (Blob) x;
                long length = blob.length();

                if (length > Integer.MAX_VALUE) {
                    throw new SQLException(BaseMessages.getString(PKG, "error.normalize.lobtoobig"), "22000");
                }

                x = blob.getBytes(1, (int) length);
            }

            if (x instanceof byte[]) {
                return toHex((byte[]) x);
            }

            return x.toString(); // Last hope!

        case java.sql.Types.BIT:
        case java.sql.Types.BOOLEAN:
            if (x == null) {
                return Boolean.FALSE;
            } else if (x instanceof Boolean) {
                return x;
            } else if (x instanceof Number) {
                return (((Number) x).intValue() == 0) ? Boolean.FALSE : Boolean.TRUE;
            } else if (x instanceof String) {
                String tmp = ((String) x).trim();

                return ("1".equals(tmp) || "true".equalsIgnoreCase(tmp)) ? Boolean.TRUE : Boolean.FALSE;
            }

            break;

        case java.sql.Types.VARBINARY:
        case java.sql.Types.BINARY:
            if (x == null) {
                return null;
            } else if (x instanceof byte[]) {
                return x;
            } else if (x instanceof Blob) {
                Blob blob = (Blob) x;

                return blob.getBytes(1, (int) blob.length());
            } else if (x instanceof Clob) {
                Clob clob = (Clob) x;
                long length = clob.length();

                if (length > Integer.MAX_VALUE) {
                    throw new SQLException(BaseMessages.getString(PKG, "error.normalize.lobtoobig"), "22000");
                }

                x = clob.getSubString(1, (int) length);
            }

            if (x instanceof String) {
                //
                // Strictly speaking this conversion is not required by
                // the JDBC standard but jTDS has always supported it.
                //
                if (charSet == null) {
                    charSet = "ISO-8859-1";
                }

                try {
                    return ((String) x).getBytes(charSet);
                } catch (UnsupportedEncodingException e) {
                    return ((String) x).getBytes();
                }
            } else if (x instanceof UniqueIdentifier) {
                return ((UniqueIdentifier) x).getBytes();
            }

            break;

        case java.sql.Types.TIMESTAMP:
            if (x == null) {
                return null;
            } else if (x instanceof DateTime) {
                return ((DateTime) x).toTimestamp();
            } else if (x instanceof java.sql.Timestamp) {
                return x;
            } else if (x instanceof java.sql.Date) {
                return new java.sql.Timestamp(((java.sql.Date) x).getTime());
            } else if (x instanceof java.sql.Time) {
                return new java.sql.Timestamp(((java.sql.Time) x).getTime());
            } else if (x instanceof java.lang.String) {
                return java.sql.Timestamp.valueOf(((String) x).trim());
            }

            break;

        case java.sql.Types.DATE:
            if (x == null) {
                return null;
            } else if (x instanceof DateTime) {
                return ((DateTime) x).toDate();
            } else if (x instanceof java.sql.Date) {
                return x;
            } else if (x instanceof java.sql.Time) {
                return DATE_ZERO;
            } else if (x instanceof java.sql.Timestamp) {
                synchronized (cal) {
                    cal.setTime((java.util.Date) x);
                    cal.set(Calendar.HOUR_OF_DAY, 0);
                    cal.set(Calendar.MINUTE, 0);
                    cal.set(Calendar.SECOND, 0);
                    cal.set(Calendar.MILLISECOND, 0);
                    // VM1.4+ only              return new java.sql.Date(cal.getTimeInMillis());
                    return new java.sql.Date(cal.getTime().getTime());
                }
            } else if (x instanceof java.lang.String) {
                return java.sql.Date.valueOf(((String) x).trim());
            }

            break;

        case java.sql.Types.TIME:
            if (x == null) {
                return null;
            } else if (x instanceof DateTime) {
                return ((DateTime) x).toTime();
            } else if (x instanceof java.sql.Time) {
                return x;
            } else if (x instanceof java.sql.Date) {
                return TIME_ZERO;
            } else if (x instanceof java.sql.Timestamp) {
                synchronized (cal) {
                    // VM 1.4+ only             cal.setTimeInMillis(((java.sql.Timestamp)x).getTime());
                    cal.setTime((java.util.Date) x);
                    cal.set(Calendar.YEAR, 1970);
                    cal.set(Calendar.MONTH, 0);
                    cal.set(Calendar.DAY_OF_MONTH, 1);
                    // VM 1.4+ only             return new java.sql.Time(cal.getTimeInMillis());*/
                    return new java.sql.Time(cal.getTime().getTime());
                }
            } else if (x instanceof java.lang.String) {
                return java.sql.Time.valueOf(((String) x).trim());
            }

            break;

        case java.sql.Types.OTHER:
            return x;

        case java.sql.Types.JAVA_OBJECT:
            throw new SQLException(BaseMessages.getString(PKG, "error.convert.badtypes", x.getClass().getName(),
                    getJdbcTypeName(jdbcType)), "22005");

        case java.sql.Types.LONGVARBINARY:
        case java.sql.Types.BLOB:
            if (x == null) {
                return null;
            } else if (x instanceof Blob) {
                return x;
            } else if (x instanceof byte[]) {
                return new BlobImpl(getConnection(callerReference), (byte[]) x);
            } else if (x instanceof Clob) {
                //
                // Convert CLOB to BLOB. Not required by the standard but we will
                // do it anyway.
                //
                Clob clob = (Clob) x;
                try {
                    if (charSet == null) {
                        charSet = "ISO-8859-1";
                    }
                    Reader rdr = clob.getCharacterStream();
                    BlobImpl blob = new BlobImpl(getConnection(callerReference));
                    BufferedWriter out = new BufferedWriter(
                            new OutputStreamWriter(blob.setBinaryStream(1), charSet));
                    // TODO Use a buffer to improve performance
                    int c;
                    while ((c = rdr.read()) >= 0) {
                        out.write(c);
                    }
                    out.close();
                    rdr.close();
                    return blob;
                } catch (UnsupportedEncodingException e) {
                    // Unlikely to happen but fall back on in memory copy
                    x = clob.getSubString(1, (int) clob.length());
                } catch (IOException e) {
                    throw new SQLException(BaseMessages.getString(PKG, "error.generic.ioerror", e.getMessage()),
                            "HY000");
                }
            }

            if (x instanceof String) {
                //
                // Strictly speaking this conversion is also not required by
                // the JDBC standard but jTDS has always supported it.
                //
                BlobImpl blob = new BlobImpl(getConnection(callerReference));
                String data = (String) x;

                if (charSet == null) {
                    charSet = "ISO-8859-1";
                }

                try {
                    blob.setBytes(1, data.getBytes(charSet));
                } catch (UnsupportedEncodingException e) {
                    blob.setBytes(1, data.getBytes());
                }

                return blob;
            }

            break;

        case java.sql.Types.LONGVARCHAR:
        case java.sql.Types.CLOB:
            if (x == null) {
                return null;
            } else if (x instanceof Clob) {
                return x;
            } else if (x instanceof Blob) {
                //
                // Convert BLOB to CLOB
                //
                Blob blob = (Blob) x;
                try {
                    InputStream is = blob.getBinaryStream();
                    ClobImpl clob = new ClobImpl(getConnection(callerReference));
                    Writer out = clob.setCharacterStream(1);
                    // TODO Use a buffer to improve performance
                    int b;
                    // These reads/writes are buffered by the undelying blob buffers
                    while ((b = is.read()) >= 0) {
                        out.write(hex[b >> 4]);
                        out.write(hex[b & 0x0F]);
                    }
                    out.close();
                    is.close();
                    return clob;
                } catch (IOException e) {
                    throw new SQLException(BaseMessages.getString(PKG, "error.generic.ioerror", e.getMessage()),
                            "HY000");
                }
            } else if (x instanceof Boolean) {
                x = ((Boolean) x).booleanValue() ? "1" : "0";
            } else if (!(x instanceof byte[])) {
                x = x.toString();
            }

            if (x instanceof byte[]) {
                ClobImpl clob = new ClobImpl(getConnection(callerReference));
                clob.setString(1, toHex((byte[]) x));

                return clob;
            } else if (x instanceof String) {
                return new ClobImpl(getConnection(callerReference), (String) x);
            }

            break;

        default:
            throw new SQLException(
                    BaseMessages.getString(PKG, "error.convert.badtypeconst", getJdbcTypeName(jdbcType)),
                    "HY004");
        }

        throw new SQLException(BaseMessages.getString(PKG, "error.convert.badtypes", x.getClass().getName(),
                getJdbcTypeName(jdbcType)), "22005");
    } catch (NumberFormatException nfe) {
        throw new SQLException(
                BaseMessages.getString(PKG, "error.convert.badnumber", getJdbcTypeName(jdbcType)), "22000");
    }
}

From source file:org.latticesoft.util.resource.dao.Param.java

private Object readValue(ResultSet rs) throws SQLException {
    Object retVal = null;//w  ww. j a  v  a 2 s  .  c om
    switch (this.sqlType) {
    case Types.VARCHAR:
    case Types.CHAR:
        String s = null;
        if (this.getSqlIndex() == 0) {
            s = rs.getString(this.getSqlName());
        } else {
            s = rs.getString(this.getSqlIndex());
        }
        retVal = s;
        break;
    case Types.BOOLEAN:
        boolean b = false;
        if (this.getSqlIndex() == 0) {
            b = rs.getBoolean(this.getSqlName());
        } else {
            b = rs.getBoolean(this.getSqlIndex());
        }
        retVal = new Boolean(b);
        break;
    case Types.INTEGER:
        int i = 0;
        if (this.getSqlIndex() == 0) {
            i = rs.getInt(this.getSqlName());
        } else {
            i = rs.getInt(this.getSqlIndex());
        }
        retVal = new Integer(i);
        break;
    case Types.SMALLINT:
        short ss = 0;
        if (this.getSqlIndex() == 0) {
            ss = rs.getShort(this.getSqlName());
        } else {
            ss = rs.getShort(this.getSqlIndex());
        }
        retVal = new Short(ss);
        break;
    case Types.TINYINT:
        byte bb = 0;
        if (this.getSqlIndex() == 0) {
            bb = rs.getByte(this.getSqlName());
        } else {
            bb = rs.getByte(this.getSqlIndex());
        }
        retVal = new Byte(bb);
        break;
    case Types.BIGINT:
        long l = 0;
        if (this.getSqlIndex() == 0) {
            l = rs.getLong(this.getSqlName());
        } else {
            l = rs.getLong(this.getSqlIndex());
        }
        retVal = new Long(l);
        break;
    case Types.DOUBLE:
        double dd = 0;
        if (this.getSqlIndex() == 0) {
            dd = rs.getDouble(this.getSqlName());
        } else {
            dd = rs.getDouble(this.getSqlIndex());
        }
        retVal = new Double(dd);
        break;
    case Types.FLOAT:
        float f = 0;
        if (this.getSqlIndex() == 0) {
            f = rs.getFloat(this.getSqlName());
        } else {
            f = rs.getFloat(this.getSqlIndex());
        }
        retVal = new Float(f);
        break;
    case Types.NUMERIC:
        BigDecimal bd = null;
        if (this.getSqlIndex() == 0) {
            bd = rs.getBigDecimal(this.getSqlName());
        } else {
            bd = rs.getBigDecimal(this.getSqlIndex());
        }
        retVal = bd;
        break;
    case Types.TIMESTAMP:
        Timestamp ts = null;
        if (this.getSqlIndex() == 0) {
            ts = rs.getTimestamp(this.getSqlName());
        } else {
            ts = rs.getTimestamp(this.getSqlIndex());
        }
        retVal = ts;
        break;
    default:
        if (this.getSqlIndex() == 0) {
            retVal = rs.getObject(this.getSqlName());
        } else {
            retVal = rs.getObject(this.getSqlIndex());
        }
        break;
    }
    if (log.isDebugEnabled()) {
        log.debug(this.getAttribute() + "=" + retVal);
    }
    return retVal;
}

From source file:com.flexive.ejb.beans.structure.AssignmentEngineBean.java

/**
 * {@inheritDoc}/*ww w  .  ja  v a  2  s .  c om*/
 */
@Override
@TransactionAttribute(TransactionAttributeType.REQUIRED)
public long createProperty(long typeId, FxPropertyEdit property, String parentXPath, String assignmentAlias)
        throws FxApplicationException {
    FxPermissionUtils.checkRole(FxContext.getUserTicket(), Role.StructureManagement);
    Connection con = null;
    PreparedStatement ps = null;
    StringBuilder sql = new StringBuilder(2000);
    long newPropertyId;
    long newAssignmentId;
    try {
        parentXPath = parentXPath.toUpperCase();
        assignmentAlias = assignmentAlias.toUpperCase();
        FxType type = CacheAdmin.getEnvironment().getType(typeId);
        FxAssignment tmp = type.getAssignment(parentXPath);
        if (tmp != null && tmp instanceof FxPropertyAssignment)
            throw new FxInvalidParameterException("ex.structure.assignment.noGroup", parentXPath);
        property.checkConsistency();
        //parentXPath is valid, create the property, then assign it to root
        newPropertyId = seq.getId(FxSystemSequencer.TYPEPROP);
        FxValue defValue = property.getDefaultValue();
        ContentStorage storage = StorageManager.getContentStorage(type.getStorageMode());
        con = Database.getDbConnection();
        if (defValue instanceof FxBinary) {
            storage.prepareBinary(con, (FxBinary) defValue);
        }
        final String _def = defValue == null || defValue.isEmpty() ? null
                : ConversionEngine.getXStream().toXML(defValue);

        if (_def != null && (property.getDefaultValue() instanceof FxReference)) {
            //check if the type matches the instance
            checkReferencedType(con, (FxReference) property.getDefaultValue(), property.getReferencedType());
        }

        // do not allow to add mandatory properties (i.e. min multiplicity > 0) to types for which content exists
        if (storage.getTypeInstanceCount(con, typeId) > 0 && property.getMultiplicity().getMin() > 0) {
            throw new FxCreateException("ex.structure.property.creation.existingContentMultiplicityError",
                    property.getName(), property.getMultiplicity().getMin());
        }

        //create property, no checks for existing names are performed as this is handled with unique keys
        sql.append("INSERT INTO ").append(TBL_STRUCT_PROPERTIES).
        //               1  2    3          4          5               6        7
                append("(ID,NAME,DEFMINMULT,DEFMAXMULT,MAYOVERRIDEMULT,DATATYPE,REFTYPE," +
        //8                9   10             11      12
                        "ISFULLTEXTINDEXED,ACL,MAYOVERRIDEACL,REFLIST,UNIQUEMODE," +
                        //13         14
                        "SYSINTERNAL,DEFAULT_VALUE)VALUES(" + "?,?,?,?,?," + "?,?,?,?,?,?,?,?,?)");
        ps = con.prepareStatement(sql.toString());
        ps.setLong(1, newPropertyId);
        ps.setString(2, property.getName());
        ps.setInt(3, property.getMultiplicity().getMin());
        ps.setInt(4, property.getMultiplicity().getMax());
        ps.setBoolean(5, property.mayOverrideBaseMultiplicity());
        ps.setLong(6, property.getDataType().getId());
        if (property.hasReferencedType())
            ps.setLong(7, property.getReferencedType().getId());
        else
            ps.setNull(7, java.sql.Types.NUMERIC);
        ps.setBoolean(8, property.isFulltextIndexed());
        ps.setLong(9, property.getACL().getId());
        ps.setBoolean(10, property.mayOverrideACL());
        if (property.hasReferencedList())
            ps.setLong(11, property.getReferencedList().getId());
        else
            ps.setNull(11, java.sql.Types.NUMERIC);
        ps.setInt(12, property.getUniqueMode().getId());
        ps.setBoolean(13, false);
        if (_def == null)
            ps.setNull(14, java.sql.Types.VARCHAR);
        else
            ps.setString(14, _def);
        if (!property.isAutoUniquePropertyName())
            ps.executeUpdate();
        else {
            //fetch used property names
            PreparedStatement ps2 = null;
            try {
                ps2 = con.prepareStatement(
                        "SELECT NAME FROM " + TBL_STRUCT_PROPERTIES + " WHERE NAME LIKE ? OR NAME=?");
                ps2.setString(1, property.getName() + "_%");
                ps2.setString(2, property.getName());
                ResultSet rs = ps2.executeQuery();
                int max = -1;
                while (rs.next()) {
                    String last = rs.getString(1);
                    if (last.equals(property.getName()) || last.startsWith(property.getName() + "_")) {
                        if (last.equals(property.getName())) {
                            max = Math.max(0, max);
                        } else if (last.startsWith(property.getName() + "_")) {
                            final String suffix = last.substring(last.lastIndexOf("_") + 1);
                            if (StringUtils.isNumeric(suffix)) {
                                max = Math.max(Integer.parseInt(suffix), max);
                            }
                        }
                    }
                    if (max != -1) {
                        final String autoName = property.getName() + "_" + (max + 1);
                        ps.setString(2, autoName);
                        LOG.info("Assigning unique property name [" + autoName + "] to [" + type.getName() + "."
                                + property.getName() + "]");
                    }
                }
            } finally {
                Database.closeObjects(AssignmentEngineBean.class, ps2);
            }
            ps.executeUpdate();
        }
        Database.storeFxString(new FxString[] { property.getLabel(), property.getHint() }, con,
                TBL_STRUCT_PROPERTIES, new String[] { "DESCRIPTION", "HINT" }, "ID", newPropertyId);
        ps.close();
        sql.setLength(0);
        //calc new position
        sql.append("SELECT COALESCE(MAX(POS)+1,0) FROM ").append(TBL_STRUCT_ASSIGNMENTS)
                .append(" WHERE PARENTGROUP=? AND TYPEDEF=?");
        ps = con.prepareStatement(sql.toString());
        ps.setLong(1, (tmp == null ? FxAssignment.NO_PARENT : tmp.getId()));
        ps.setLong(2, typeId);
        ResultSet rs = ps.executeQuery();
        long pos = 0;
        if (rs != null && rs.next())
            pos = rs.getLong(1);
        ps.close();
        storeOptions(con, TBL_STRUCT_PROPERTY_OPTIONS, "ID", newPropertyId, null, property.getOptions());
        sql.setLength(0);
        //create root assignment
        sql.append("INSERT INTO ").append(TBL_STRUCT_ASSIGNMENTS).
        //               1  2     3       4       5       6       7       8   9     10    11    12          13
                append("(ID,ATYPE,ENABLED,TYPEDEF,MINMULT,MAXMULT,DEFMULT,POS,XPATH,XALIAS,BASE,PARENTGROUP,APROPERTY,"
                        +
                        //14 15
                        "ACL,DEFAULT_VALUE)" + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        ps = con.prepareStatement(sql.toString());
        newAssignmentId = seq.getId(FxSystemSequencer.ASSIGNMENT);
        ps.setLong(1, newAssignmentId);
        ps.setInt(2, FxAssignment.TYPE_PROPERTY);
        ps.setBoolean(3, true);
        ps.setLong(4, typeId);
        ps.setInt(5, property.getMultiplicity().getMin());
        ps.setInt(6, property.getMultiplicity().getMax());
        if (property.getMultiplicity().isValid(property.getAssignmentDefaultMultiplicity())) {
            ps.setInt(7, property.getAssignmentDefaultMultiplicity());
        } else {
            //default is min(min,1).
            ps.setInt(7, property.getMultiplicity().getMin() > 1 ? property.getMultiplicity().getMin() : 1);
        }
        ps.setLong(8, pos);
        if (parentXPath == null || "/".equals(parentXPath))
            parentXPath = "";
        ps.setString(9, type.getName() + XPathElement.stripType(parentXPath) + "/" + assignmentAlias);
        ps.setString(10, assignmentAlias);
        ps.setNull(11, Types.NUMERIC);
        if (tmp == null)
            ps.setLong(12, FxAssignment.NO_PARENT);
        else
            ps.setLong(12, tmp.getId());
        ps.setLong(13, newPropertyId);
        ps.setLong(14, property.getACL().getId());
        ps.setString(15, _def);
        ps.executeUpdate();
        Database.storeFxString(new FxString[] { property.getLabel(), property.getHint() }, con,
                TBL_STRUCT_ASSIGNMENTS, new String[] { "DESCRIPTION", "HINT" }, "ID", newAssignmentId);
        StructureLoader.reloadAssignments(FxContext.get().getDivisionId());
        if (divisionConfig.isFlatStorageEnabled() && divisionConfig.get(SystemParameters.FLATSTORAGE_AUTO)) {
            final FxFlatStorage fs = FxFlatStorageManager.getInstance();
            FxPropertyAssignment pa = (FxPropertyAssignment) CacheAdmin.getEnvironment()
                    .getAssignment(newAssignmentId);
            if (fs.isFlattenable(pa)) {
                fs.flatten(con, fs.getDefaultStorage(), pa);
                StructureLoader.reloadAssignments(FxContext.get().getDivisionId());
            }
        }
        htracker.track(type, "history.assignment.createProperty", property.getName(), type.getId(),
                type.getName());
        if (type.getId() != FxType.ROOT_ID)
            createInheritedAssignments(CacheAdmin.getEnvironment().getAssignment(newAssignmentId), con, sql,
                    type.getDerivedTypes());
    } catch (FxNotFoundException e) {
        EJBUtils.rollback(ctx);
        throw new FxCreateException(e);
    } catch (FxLoadException e) {
        EJBUtils.rollback(ctx);
        throw new FxCreateException(e);
    } catch (SQLException e) {
        final boolean uniqueConstraintViolation = StorageManager.isUniqueConstraintViolation(e);
        EJBUtils.rollback(ctx);
        if (uniqueConstraintViolation)
            throw new FxEntryExistsException("ex.structure.property.exists", property.getName(),
                    (parentXPath.length() == 0 ? "/" : parentXPath));
        throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage());
    } finally {
        Database.closeObjects(AssignmentEngineBean.class, con, ps);
    }
    return newAssignmentId;
}

From source file:net.sourceforge.msscodefactory.cfacc.v2_0.CFAccMySql.CFAccMySqlAccountEntryTable.java

public void createAccountEntry(CFAccAuthorization Authorization, CFAccAccountEntryBuff Buff) {
    final String S_ProcName = "createAccountEntry";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*from w w w .j a v a 2  s .c o m*/
    ResultSet resultSet = null;
    try {
        long TenantId = Buff.getRequiredTenantId();
        long AccountId = Buff.getRequiredAccountId();
        UUID SplitEntryId = Buff.getOptionalSplitEntryId();
        Calendar EntryStamp = Buff.getRequiredEntryStamp();
        String Description = Buff.getRequiredDescription();
        Long TransferTenantId = Buff.getOptionalTransferTenantId();
        Long TransferAccountId = Buff.getOptionalTransferAccountId();
        Short DebitCurrencyId = Buff.getOptionalDebitCurrencyId();
        BigDecimal Debit = Buff.getOptionalDebit();
        Short CreditCurrencyId = Buff.getOptionalCreditCurrencyId();
        BigDecimal Credit = Buff.getOptionalCredit();
        short ConvertedCurrencyId = Buff.getRequiredConvertedCurrencyId();
        BigDecimal ConvertedAmount = Buff.getRequiredConvertedAmount();
        short BalanceCurrencyId = Buff.getRequiredBalanceCurrencyId();
        BigDecimal Balance = Buff.getRequiredBalance();

        UUID EntryId = UUID.randomUUID();
        Connection cnx = schema.getCnx();
        String sql = "call " + schema.getLowerSchemaDbName() + ".sp_create_ac_entry( ?, ?, ?, ?, ?, ?" + ", "
                + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "timestamp( ? )" + ", " + "?" + ", " + "?"
                + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?"
                + ", " + "?" + ", " + "?" + " )";
        if (stmtCreateByPKey == null) {
            stmtCreateByPKey = cnx.prepareStatement(sql);
        }
        int argIdx = 1;
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtCreateByPKey.setString(argIdx++, "ACNY");
        stmtCreateByPKey.setLong(argIdx++, TenantId);
        stmtCreateByPKey.setLong(argIdx++, AccountId);
        stmtCreateByPKey.setString(argIdx++, EntryId.toString());
        if (SplitEntryId != null) {
            stmtCreateByPKey.setString(argIdx++, SplitEntryId.toString());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR);
        }
        stmtCreateByPKey.setString(argIdx++, CFAccMySqlSchema.getTimestampString(EntryStamp));
        stmtCreateByPKey.setString(argIdx++, Description);
        if (TransferTenantId != null) {
            stmtCreateByPKey.setLong(argIdx++, TransferTenantId.longValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.BIGINT);
        }
        if (TransferAccountId != null) {
            stmtCreateByPKey.setLong(argIdx++, TransferAccountId.longValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.BIGINT);
        }
        if (DebitCurrencyId != null) {
            stmtCreateByPKey.setShort(argIdx++, DebitCurrencyId.shortValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        if (Debit != null) {
            stmtCreateByPKey.setBigDecimal(argIdx++, Debit);
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.NUMERIC);
        }
        if (CreditCurrencyId != null) {
            stmtCreateByPKey.setShort(argIdx++, CreditCurrencyId.shortValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        if (Credit != null) {
            stmtCreateByPKey.setBigDecimal(argIdx++, Credit);
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.NUMERIC);
        }
        stmtCreateByPKey.setShort(argIdx++, ConvertedCurrencyId);
        stmtCreateByPKey.setBigDecimal(argIdx++, ConvertedAmount);
        stmtCreateByPKey.setShort(argIdx++, BalanceCurrencyId);
        stmtCreateByPKey.setBigDecimal(argIdx++, Balance);
        try {
            resultSet = stmtCreateByPKey.executeQuery();
        } catch (SQLException e) {
            if (e.getErrorCode() != 1329) {
                throw e;
            }
            resultSet = null;
        }
        if ((resultSet != null) && resultSet.next()) {
            CFAccAccountEntryBuff createdBuff = unpackAccountEntryResultSetToBuff(resultSet);
            if ((resultSet != null) && resultSet.next()) {
                resultSet.last();
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
            }
            Buff.setRequiredTenantId(createdBuff.getRequiredTenantId());
            Buff.setRequiredAccountId(createdBuff.getRequiredAccountId());
            Buff.setRequiredEntryId(createdBuff.getRequiredEntryId());
            Buff.setOptionalSplitEntryId(createdBuff.getOptionalSplitEntryId());
            Buff.setRequiredEntryStamp(createdBuff.getRequiredEntryStamp());
            Buff.setRequiredDescription(createdBuff.getRequiredDescription());
            Buff.setOptionalTransferTenantId(createdBuff.getOptionalTransferTenantId());
            Buff.setOptionalTransferAccountId(createdBuff.getOptionalTransferAccountId());
            Buff.setOptionalDebitCurrencyId(createdBuff.getOptionalDebitCurrencyId());
            Buff.setOptionalDebit(createdBuff.getOptionalDebit());
            Buff.setOptionalCreditCurrencyId(createdBuff.getOptionalCreditCurrencyId());
            Buff.setOptionalCredit(createdBuff.getOptionalCredit());
            Buff.setRequiredConvertedCurrencyId(createdBuff.getRequiredConvertedCurrencyId());
            Buff.setRequiredConvertedAmount(createdBuff.getRequiredConvertedAmount());
            Buff.setRequiredBalanceCurrencyId(createdBuff.getRequiredBalanceCurrencyId());
            Buff.setRequiredBalance(createdBuff.getRequiredBalance());
            Buff.setRequiredRevision(createdBuff.getRequiredRevision());
            Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
            Buff.setCreatedAt(createdBuff.getCreatedAt());
            Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
            Buff.setUpdatedAt(createdBuff.getUpdatedAt());
        } else {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "Expected a single-record response, " + resultSet.getRow() + " rows selected");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfacc.v2_0.CFAccDb2LUW.CFAccDb2LUWAccountEntryTable.java

public void createAccountEntry(CFAccAuthorization Authorization, CFAccAccountEntryBuff Buff) {
    final String S_ProcName = "createAccountEntry";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*from   w  w w.ja  va2 s  .  c om*/
    ResultSet resultSet = null;
    try {
        long TenantId = Buff.getRequiredTenantId();
        long AccountId = Buff.getRequiredAccountId();
        UUID SplitEntryId = Buff.getOptionalSplitEntryId();
        Calendar EntryStamp = Buff.getRequiredEntryStamp();
        String Description = Buff.getRequiredDescription();
        Long TransferTenantId = Buff.getOptionalTransferTenantId();
        Long TransferAccountId = Buff.getOptionalTransferAccountId();
        Short DebitCurrencyId = Buff.getOptionalDebitCurrencyId();
        BigDecimal Debit = Buff.getOptionalDebit();
        Short CreditCurrencyId = Buff.getOptionalCreditCurrencyId();
        BigDecimal Credit = Buff.getOptionalCredit();
        short ConvertedCurrencyId = Buff.getRequiredConvertedCurrencyId();
        BigDecimal ConvertedAmount = Buff.getRequiredConvertedAmount();
        short BalanceCurrencyId = Buff.getRequiredBalanceCurrencyId();
        BigDecimal Balance = Buff.getRequiredBalance();

        UUID EntryId = UUID.randomUUID();
        Connection cnx = schema.getCnx();
        final String sql = "CALL sp_create_ac_entry( ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?"
                + ", " + "?" + ", " + "TIMESTAMP_FORMAT( ?, 'YYYY-MM-DD-HH24.MI.SS' )" + ", " + "?" + ", " + "?"
                + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?"
                + ", " + "?" + ", " + "?" + " )";
        if (stmtCreateByPKey == null) {
            stmtCreateByPKey = cnx.prepareStatement(sql);
        }
        int argIdx = 1;
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtCreateByPKey.setString(argIdx++, "ACNY");
        stmtCreateByPKey.setLong(argIdx++, TenantId);
        stmtCreateByPKey.setLong(argIdx++, AccountId);
        stmtCreateByPKey.setString(argIdx++, EntryId.toString());
        if (SplitEntryId != null) {
            stmtCreateByPKey.setString(argIdx++, SplitEntryId.toString());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR);
        }
        stmtCreateByPKey.setString(argIdx++, CFAccDb2LUWSchema.getTimestampString(EntryStamp));
        stmtCreateByPKey.setString(argIdx++, Description);
        if (TransferTenantId != null) {
            stmtCreateByPKey.setLong(argIdx++, TransferTenantId.longValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.BIGINT);
        }
        if (TransferAccountId != null) {
            stmtCreateByPKey.setLong(argIdx++, TransferAccountId.longValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.BIGINT);
        }
        if (DebitCurrencyId != null) {
            stmtCreateByPKey.setShort(argIdx++, DebitCurrencyId.shortValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        if (Debit != null) {
            stmtCreateByPKey.setBigDecimal(argIdx++, Debit);
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.NUMERIC);
        }
        if (CreditCurrencyId != null) {
            stmtCreateByPKey.setShort(argIdx++, CreditCurrencyId.shortValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        if (Credit != null) {
            stmtCreateByPKey.setBigDecimal(argIdx++, Credit);
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.NUMERIC);
        }
        stmtCreateByPKey.setShort(argIdx++, ConvertedCurrencyId);
        stmtCreateByPKey.setBigDecimal(argIdx++, ConvertedAmount);
        stmtCreateByPKey.setShort(argIdx++, BalanceCurrencyId);
        stmtCreateByPKey.setBigDecimal(argIdx++, Balance);
        resultSet = stmtCreateByPKey.executeQuery();
        if (resultSet.next()) {
            CFAccAccountEntryBuff createdBuff = unpackAccountEntryResultSetToBuff(resultSet);
            if (resultSet.next()) {
                resultSet.last();
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
            }
            Buff.setRequiredTenantId(createdBuff.getRequiredTenantId());
            Buff.setRequiredAccountId(createdBuff.getRequiredAccountId());
            Buff.setRequiredEntryId(createdBuff.getRequiredEntryId());
            Buff.setOptionalSplitEntryId(createdBuff.getOptionalSplitEntryId());
            Buff.setRequiredEntryStamp(createdBuff.getRequiredEntryStamp());
            Buff.setRequiredDescription(createdBuff.getRequiredDescription());
            Buff.setOptionalTransferTenantId(createdBuff.getOptionalTransferTenantId());
            Buff.setOptionalTransferAccountId(createdBuff.getOptionalTransferAccountId());
            Buff.setOptionalDebitCurrencyId(createdBuff.getOptionalDebitCurrencyId());
            Buff.setOptionalDebit(createdBuff.getOptionalDebit());
            Buff.setOptionalCreditCurrencyId(createdBuff.getOptionalCreditCurrencyId());
            Buff.setOptionalCredit(createdBuff.getOptionalCredit());
            Buff.setRequiredConvertedCurrencyId(createdBuff.getRequiredConvertedCurrencyId());
            Buff.setRequiredConvertedAmount(createdBuff.getRequiredConvertedAmount());
            Buff.setRequiredBalanceCurrencyId(createdBuff.getRequiredBalanceCurrencyId());
            Buff.setRequiredBalance(createdBuff.getRequiredBalance());
            Buff.setRequiredRevision(createdBuff.getRequiredRevision());
            Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
            Buff.setCreatedAt(createdBuff.getCreatedAt());
            Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
            Buff.setUpdatedAt(createdBuff.getUpdatedAt());
        } else {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "Expected a single-record response, " + resultSet.getRow() + " rows selected");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
    }
}

From source file:org.apache.synapse.mediators.db.AbstractDBMediator.java

/**
 * Return a Prepared statement for the given Statement object, which is ready to be executed
 *
 * @param stmnt  SQL stataement to be executed
 * @param con    The connection to be used
 * @param msgCtx Current message context
 * @return a PreparedStatement/* ww  w. j  ava2 s .co m*/
 * @throws SQLException on error
 */
protected PreparedStatement getPreparedStatement(Statement stmnt, Connection con, MessageContext msgCtx)
        throws SQLException {

    SynapseLog synLog = getLog(msgCtx);

    if (synLog.isTraceOrDebugEnabled()) {
        synLog.traceOrDebug("Getting a connection from DataSource " + getDSName()
                + " and preparing statement : " + stmnt.getRawStatement());
    }

    if (con == null) {
        String msg = "Connection from DataSource " + getDSName() + " is null.";
        log.error(msg);
        throw new SynapseException(msg);
    }

    if (dataSource instanceof BasicDataSource) {

        BasicDataSource basicDataSource = (BasicDataSource) dataSource;
        int numActive = basicDataSource.getNumActive();
        int numIdle = basicDataSource.getNumIdle();
        String connectionId = Integer.toHexString(con.hashCode());

        DBPoolView dbPoolView = getDbPoolView();
        if (dbPoolView != null) {
            dbPoolView.setNumActive(numActive);
            dbPoolView.setNumIdle(numIdle);
            dbPoolView.updateConnectionUsage(connectionId);
        }

        if (synLog.isTraceOrDebugEnabled()) {
            synLog.traceOrDebug("[ DB Connection : " + con + " ]");
            synLog.traceOrDebug("[ DB Connection instance identifier : " + connectionId + " ]");
            synLog.traceOrDebug("[ Number of Active Connection : " + numActive + " ]");
            synLog.traceOrDebug("[ Number of Idle Connection : " + numIdle + " ]");
        }
    }

    PreparedStatement ps = con.prepareStatement(stmnt.getRawStatement());

    // set parameters if any
    List<Statement.Parameter> params = stmnt.getParameters();
    int column = 1;

    for (Statement.Parameter param : params) {
        if (param == null) {
            continue;
        }
        String value = (param.getPropertyName() != null ? param.getPropertyName()
                : param.getXpath().stringValueOf(msgCtx));

        if (synLog.isTraceOrDebugEnabled()) {
            synLog.traceOrDebug("Setting as parameter : " + column + " value : " + value + " as JDBC Type : "
                    + param.getType() + "(see java.sql.Types for valid " + "types)");
        }

        switch (param.getType()) {
        // according to J2SE 1.5 /docs/guide/jdbc/getstart/mapping.html
        case Types.CHAR:
        case Types.VARCHAR:
        case Types.LONGVARCHAR: {
            if (value != null && value.length() != 0) {
                ps.setString(column++, value);
            } else {
                ps.setString(column++, null);
            }
            break;
        }
        case Types.NUMERIC:
        case Types.DECIMAL: {
            if (value != null && value.length() != 0) {
                ps.setBigDecimal(column++, new BigDecimal(value));
            } else {
                ps.setBigDecimal(column++, null);
            }
            break;
        }
        case Types.BIT: {
            if (value != null && value.length() != 0) {
                ps.setBoolean(column++, Boolean.parseBoolean(value));
            } else {
                ps.setNull(column++, Types.BIT);
            }
            break;
        }
        case Types.TINYINT: {
            if (value != null && value.length() != 0) {
                ps.setByte(column++, Byte.parseByte(value));
            } else {
                ps.setNull(column++, Types.TINYINT);
            }
            break;
        }
        case Types.SMALLINT: {
            if (value != null && value.length() != 0) {
                ps.setShort(column++, Short.parseShort(value));
            } else {
                ps.setNull(column++, Types.SMALLINT);
            }
            break;
        }
        case Types.INTEGER: {
            if (value != null && value.length() != 0) {
                ps.setInt(column++, Integer.parseInt(value));
            } else {
                ps.setNull(column++, Types.INTEGER);
            }
            break;
        }
        case Types.BIGINT: {
            if (value != null && value.length() != 0) {
                ps.setLong(column++, Long.parseLong(value));
            } else {
                ps.setNull(column++, Types.BIGINT);
            }
            break;
        }
        case Types.REAL: {
            if (value != null && value.length() != 0) {
                ps.setFloat(column++, Float.parseFloat(value));
            } else {
                ps.setNull(column++, Types.REAL);
            }
            break;
        }
        case Types.FLOAT: {
            if (value != null && value.length() != 0) {
                ps.setDouble(column++, Double.parseDouble(value));
            } else {
                ps.setNull(column++, Types.FLOAT);
            }
            break;
        }
        case Types.DOUBLE: {
            if (value != null && value.length() != 0) {
                ps.setDouble(column++, Double.parseDouble(value));
            } else {
                ps.setNull(column++, Types.DOUBLE);
            }
            break;
        }
        // skip BINARY, VARBINARY and LONGVARBINARY
        case Types.DATE: {
            if (value != null && value.length() != 0) {
                ps.setDate(column++, Date.valueOf(value));
            } else {
                ps.setNull(column++, Types.DATE);
            }
            break;
        }
        case Types.TIME: {
            if (value != null && value.length() != 0) {
                ps.setTime(column++, Time.valueOf(value));
            } else {
                ps.setNull(column++, Types.TIME);
            }
            break;
        }
        case Types.TIMESTAMP: {
            if (value != null && value.length() != 0) {
                ps.setTimestamp(column++, Timestamp.valueOf(value));
            } else {
                ps.setNull(column++, Types.TIMESTAMP);
            }
            break;
        }
        // skip CLOB, BLOB, ARRAY, DISTINCT, STRUCT, REF, JAVA_OBJECT
        default: {
            String msg = "Trying to set an un-supported JDBC Type : " + param.getType() + " against column : "
                    + column + " and statement : " + stmnt.getRawStatement()
                    + " used by a DB mediator against DataSource : " + getDSName()
                    + " (see java.sql.Types for valid type values)";
            handleException(msg, msgCtx);
        }
        }
    }

    if (synLog.isTraceOrDebugEnabled()) {
        synLog.traceOrDebug("Successfully prepared statement : " + stmnt.getRawStatement()
                + " against DataSource : " + getDSName());
    }
    return ps;
}

From source file:net.sourceforge.msscodefactory.cfacc.v2_0.CFAccMSSql.CFAccMSSqlAccountEntryTable.java

public void createAccountEntry(CFAccAuthorization Authorization, CFAccAccountEntryBuff Buff) {
    final String S_ProcName = "createAccountEntry";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }//  w  ww. ja v  a 2  s . com
    ResultSet resultSet = null;
    try {
        long TenantId = Buff.getRequiredTenantId();
        long AccountId = Buff.getRequiredAccountId();
        UUID SplitEntryId = Buff.getOptionalSplitEntryId();
        Calendar EntryStamp = Buff.getRequiredEntryStamp();
        String Description = Buff.getRequiredDescription();
        Long TransferTenantId = Buff.getOptionalTransferTenantId();
        Long TransferAccountId = Buff.getOptionalTransferAccountId();
        Short DebitCurrencyId = Buff.getOptionalDebitCurrencyId();
        BigDecimal Debit = Buff.getOptionalDebit();
        Short CreditCurrencyId = Buff.getOptionalCreditCurrencyId();
        BigDecimal Credit = Buff.getOptionalCredit();
        short ConvertedCurrencyId = Buff.getRequiredConvertedCurrencyId();
        BigDecimal ConvertedAmount = Buff.getRequiredConvertedAmount();
        short BalanceCurrencyId = Buff.getRequiredBalanceCurrencyId();
        BigDecimal Balance = Buff.getRequiredBalance();

        UUID EntryId = UUID.randomUUID();
        Connection cnx = schema.getCnx();
        String sql = "exec sp_create_ac_entry ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + ", "
                + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?"
                + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?";
        if (stmtCreateByPKey == null) {
            stmtCreateByPKey = cnx.prepareStatement(sql);
        }
        int argIdx = 1;
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtCreateByPKey.setString(argIdx++, "ACNY");
        stmtCreateByPKey.setLong(argIdx++, TenantId);
        stmtCreateByPKey.setLong(argIdx++, AccountId);
        stmtCreateByPKey.setString(argIdx++, EntryId.toString());
        if (SplitEntryId != null) {
            stmtCreateByPKey.setString(argIdx++, SplitEntryId.toString());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR);
        }
        stmtCreateByPKey.setString(argIdx++, CFAccMSSqlSchema.getTimestampString(EntryStamp));
        stmtCreateByPKey.setString(argIdx++, Description);
        if (TransferTenantId != null) {
            stmtCreateByPKey.setLong(argIdx++, TransferTenantId.longValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.BIGINT);
        }
        if (TransferAccountId != null) {
            stmtCreateByPKey.setLong(argIdx++, TransferAccountId.longValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.BIGINT);
        }
        if (DebitCurrencyId != null) {
            stmtCreateByPKey.setShort(argIdx++, DebitCurrencyId.shortValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        if (Debit != null) {
            stmtCreateByPKey.setBigDecimal(argIdx++, Debit);
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.NUMERIC);
        }
        if (CreditCurrencyId != null) {
            stmtCreateByPKey.setShort(argIdx++, CreditCurrencyId.shortValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        if (Credit != null) {
            stmtCreateByPKey.setBigDecimal(argIdx++, Credit);
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.NUMERIC);
        }
        stmtCreateByPKey.setShort(argIdx++, ConvertedCurrencyId);
        stmtCreateByPKey.setBigDecimal(argIdx++, ConvertedAmount);
        stmtCreateByPKey.setShort(argIdx++, BalanceCurrencyId);
        stmtCreateByPKey.setBigDecimal(argIdx++, Balance);
        stmtCreateByPKey.execute();
        boolean moreResults = true;
        resultSet = null;
        while (resultSet == null) {
            try {
                moreResults = stmtCreateByPKey.getMoreResults();
            } catch (SQLException e) {
                throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
            }
            if (moreResults) {
                try {
                    resultSet = stmtCreateByPKey.getResultSet();
                } catch (SQLException e) {
                }
            } else if (-1 == stmtCreateByPKey.getUpdateCount()) {
                break;
            }
        }
        if (resultSet == null) {
            throw CFLib.getDefaultExceptionFactory().newNullArgumentException(getClass(), S_ProcName, 0,
                    "resultSet");
        }
        if (resultSet.next()) {
            CFAccAccountEntryBuff createdBuff = unpackAccountEntryResultSetToBuff(resultSet);
            if (resultSet.next()) {
                resultSet.last();
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
            }
            Buff.setRequiredTenantId(createdBuff.getRequiredTenantId());
            Buff.setRequiredAccountId(createdBuff.getRequiredAccountId());
            Buff.setRequiredEntryId(createdBuff.getRequiredEntryId());
            Buff.setOptionalSplitEntryId(createdBuff.getOptionalSplitEntryId());
            Buff.setRequiredEntryStamp(createdBuff.getRequiredEntryStamp());
            Buff.setRequiredDescription(createdBuff.getRequiredDescription());
            Buff.setOptionalTransferTenantId(createdBuff.getOptionalTransferTenantId());
            Buff.setOptionalTransferAccountId(createdBuff.getOptionalTransferAccountId());
            Buff.setOptionalDebitCurrencyId(createdBuff.getOptionalDebitCurrencyId());
            Buff.setOptionalDebit(createdBuff.getOptionalDebit());
            Buff.setOptionalCreditCurrencyId(createdBuff.getOptionalCreditCurrencyId());
            Buff.setOptionalCredit(createdBuff.getOptionalCredit());
            Buff.setRequiredConvertedCurrencyId(createdBuff.getRequiredConvertedCurrencyId());
            Buff.setRequiredConvertedAmount(createdBuff.getRequiredConvertedAmount());
            Buff.setRequiredBalanceCurrencyId(createdBuff.getRequiredBalanceCurrencyId());
            Buff.setRequiredBalance(createdBuff.getRequiredBalance());
            Buff.setRequiredRevision(createdBuff.getRequiredRevision());
            Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
            Buff.setCreatedAt(createdBuff.getCreatedAt());
            Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
            Buff.setUpdatedAt(createdBuff.getUpdatedAt());
        } else {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "Expected a single-record response, " + resultSet.getRow() + " rows selected");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfacc.v2_0.CFAccPgSql.CFAccPgSqlAccountEntryTable.java

public void createAccountEntry(CFAccAuthorization Authorization, CFAccAccountEntryBuff Buff) {
    final String S_ProcName = "createAccountEntry";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*from   w w  w.  ja  v  a  2 s  .  c o m*/
    ResultSet resultSet = null;
    try {
        long TenantId = Buff.getRequiredTenantId();
        long AccountId = Buff.getRequiredAccountId();
        UUID SplitEntryId = Buff.getOptionalSplitEntryId();
        Calendar EntryStamp = Buff.getRequiredEntryStamp();
        String Description = Buff.getRequiredDescription();
        Long TransferTenantId = Buff.getOptionalTransferTenantId();
        Long TransferAccountId = Buff.getOptionalTransferAccountId();
        Short DebitCurrencyId = Buff.getOptionalDebitCurrencyId();
        BigDecimal Debit = Buff.getOptionalDebit();
        Short CreditCurrencyId = Buff.getOptionalCreditCurrencyId();
        BigDecimal Credit = Buff.getOptionalCredit();
        short ConvertedCurrencyId = Buff.getRequiredConvertedCurrencyId();
        BigDecimal ConvertedAmount = Buff.getRequiredConvertedAmount();
        short BalanceCurrencyId = Buff.getRequiredBalanceCurrencyId();
        BigDecimal Balance = Buff.getRequiredBalance();

        UUID EntryId = UUID.randomUUID();
        Connection cnx = schema.getCnx();
        String sql = "select * from " + schema.getLowerSchemaDbName() + ".sp_create_ac_entry( ?, ?, ?, ?, ?, ?"
                + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", "
                + "cast( to_timestamp( ?, 'YYYY-MM-DD HH24:MI:SS' ) as timestamp )" + ", " + "?" + ", " + "?"
                + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?"
                + ", " + "?" + ", " + "?" + " )";
        if (stmtCreateByPKey == null) {
            stmtCreateByPKey = cnx.prepareStatement(sql);
        }
        int argIdx = 1;
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtCreateByPKey.setString(argIdx++, "ACNY");
        stmtCreateByPKey.setLong(argIdx++, TenantId);
        stmtCreateByPKey.setLong(argIdx++, AccountId);
        stmtCreateByPKey.setString(argIdx++, EntryId.toString());
        if (SplitEntryId != null) {
            stmtCreateByPKey.setString(argIdx++, SplitEntryId.toString());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR);
        }
        stmtCreateByPKey.setString(argIdx++, CFAccPgSqlSchema.getTimestampString(EntryStamp));
        stmtCreateByPKey.setString(argIdx++, Description);
        if (TransferTenantId != null) {
            stmtCreateByPKey.setLong(argIdx++, TransferTenantId.longValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.BIGINT);
        }
        if (TransferAccountId != null) {
            stmtCreateByPKey.setLong(argIdx++, TransferAccountId.longValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.BIGINT);
        }
        if (DebitCurrencyId != null) {
            stmtCreateByPKey.setShort(argIdx++, DebitCurrencyId.shortValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        if (Debit != null) {
            stmtCreateByPKey.setBigDecimal(argIdx++, Debit);
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.NUMERIC);
        }
        if (CreditCurrencyId != null) {
            stmtCreateByPKey.setShort(argIdx++, CreditCurrencyId.shortValue());
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.SMALLINT);
        }
        if (Credit != null) {
            stmtCreateByPKey.setBigDecimal(argIdx++, Credit);
        } else {
            stmtCreateByPKey.setNull(argIdx++, java.sql.Types.NUMERIC);
        }
        stmtCreateByPKey.setShort(argIdx++, ConvertedCurrencyId);
        stmtCreateByPKey.setBigDecimal(argIdx++, ConvertedAmount);
        stmtCreateByPKey.setShort(argIdx++, BalanceCurrencyId);
        stmtCreateByPKey.setBigDecimal(argIdx++, Balance);
        resultSet = stmtCreateByPKey.executeQuery();
        if (resultSet.next()) {
            CFAccAccountEntryBuff createdBuff = unpackAccountEntryResultSetToBuff(resultSet);
            if (resultSet.next()) {
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Did not expect multi-record response");
            }
            Buff.setRequiredTenantId(createdBuff.getRequiredTenantId());
            Buff.setRequiredAccountId(createdBuff.getRequiredAccountId());
            Buff.setRequiredEntryId(createdBuff.getRequiredEntryId());
            Buff.setOptionalSplitEntryId(createdBuff.getOptionalSplitEntryId());
            Buff.setRequiredEntryStamp(createdBuff.getRequiredEntryStamp());
            Buff.setRequiredDescription(createdBuff.getRequiredDescription());
            Buff.setOptionalTransferTenantId(createdBuff.getOptionalTransferTenantId());
            Buff.setOptionalTransferAccountId(createdBuff.getOptionalTransferAccountId());
            Buff.setOptionalDebitCurrencyId(createdBuff.getOptionalDebitCurrencyId());
            Buff.setOptionalDebit(createdBuff.getOptionalDebit());
            Buff.setOptionalCreditCurrencyId(createdBuff.getOptionalCreditCurrencyId());
            Buff.setOptionalCredit(createdBuff.getOptionalCredit());
            Buff.setRequiredConvertedCurrencyId(createdBuff.getRequiredConvertedCurrencyId());
            Buff.setRequiredConvertedAmount(createdBuff.getRequiredConvertedAmount());
            Buff.setRequiredBalanceCurrencyId(createdBuff.getRequiredBalanceCurrencyId());
            Buff.setRequiredBalance(createdBuff.getRequiredBalance());
            Buff.setRequiredRevision(createdBuff.getRequiredRevision());
            Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
            Buff.setCreatedAt(createdBuff.getCreatedAt());
            Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
            Buff.setUpdatedAt(createdBuff.getUpdatedAt());
        } else {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "Expected a single-record response, " + resultSet.getRow() + " rows selected");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
    }
}

From source file:org.jumpmind.symmetric.service.impl.IncomingBatchService.java

public void insertIncomingBatch(ISqlTransaction transaction, IncomingBatch batch) {
    if (batch.isPersistable()) {
        batch.setLastUpdatedHostName(clusterService.getServerId());
        batch.setLastUpdatedTime(new Date());
        transaction.prepareAndExecute(getSql("insertIncomingBatchSql"),
                new Object[] { batch.getBatchId(), batch.getNodeId(), batch.getChannelId(),
                        batch.getStatus().name(), batch.getNetworkMillis(), batch.getFilterMillis(),
                        batch.getDatabaseMillis(), batch.getFailedRowNumber(), batch.getFailedLineNumber(),
                        batch.getByteCount(), batch.getStatementCount(), batch.getFallbackInsertCount(),
                        batch.getFallbackUpdateCount(), batch.getIgnoreCount(), batch.getMissingDeleteCount(),
                        batch.getSkipCount(), batch.getSqlState(), batch.getSqlCode(),
                        FormatUtils.abbreviateForLogging(batch.getSqlMessage()), batch.getLastUpdatedHostName(),
                        batch.getLastUpdatedTime() },
                new int[] { Types.NUMERIC, Types.VARCHAR, Types.VARCHAR, Types.CHAR, Types.NUMERIC,
                        Types.NUMERIC, Types.NUMERIC, Types.NUMERIC, Types.NUMERIC, Types.NUMERIC,
                        Types.NUMERIC, Types.NUMERIC, Types.NUMERIC, Types.NUMERIC, Types.NUMERIC,
                        Types.NUMERIC, Types.VARCHAR, Types.NUMERIC, Types.VARCHAR, Types.VARCHAR,
                        Types.TIMESTAMP });
    }/*from   w ww.  java2  s.  c o m*/
}