Example usage for java.sql PreparedStatement setBytes

List of usage examples for java.sql PreparedStatement setBytes

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setBytes

Introduction

In this page you can find the example usage for java.sql PreparedStatement setBytes.

Prototype

void setBytes(int parameterIndex, byte x[]) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java array of bytes.

Usage

From source file:org.getobjects.eoaccess.EOAdaptorChannel.java

protected PreparedStatement _prepareStatementWithBinds(final String _sql,
        final List<Map<String, Object>> _binds) {
    boolean isDebugOn = log.isDebugEnabled();
    if (_sql == null || _sql.length() == 0)
        return null;

    final PreparedStatement stmt = this._createPreparedStatement(_sql);
    if (stmt == null)
        return null;
    if (_binds == null) {
        if (isDebugOn)
            log.debug("statement to prepare has no binds ..");
        return stmt; /* hm, statement has no binds */
    }/*ww w .  j a va 2  s  . c  o m*/

    /* fill in parameters */

    if (isDebugOn)
        log.debug("prepare binds: " + _binds);

    try {
        /* Fill statement with bindg values */
        for (int i = 0; i < _binds.size(); i++) {
            /* a dictionary with such keys:
             *   BindVariableAttributeKey - the EOAttribute of the value
             *   BindVariableValueKey     - the actual value
             */
            final Map<String, Object> bind = _binds.get(i);

            final EOAttribute attribute = (EOAttribute) bind.get(EOSQLExpression.BindVariableAttributeKey);

            final Object value = bind.get(EOSQLExpression.BindVariableValueKey);

            int sqlType = this.sqlTypeForValue(value, attribute);

            if (isDebugOn) {
                log.debug("  bind attribute: " + attribute);
                log.debug("           value: " + value + " / " + (value != null ? value.getClass() : "[NULL]"));
                log.debug("            type: " + sqlType);
            }

            if (value == null)
                stmt.setNull(i + 1, sqlType);
            else {
                switch (sqlType) {
                case java.sql.Types.NULL:
                    stmt.setNull(i + 1, java.sql.Types.VARCHAR); // CRAP
                    break;

                // TODO: customize value processing for types
                case java.sql.Types.VARCHAR:
                case java.sql.Types.TIMESTAMP:
                case java.sql.Types.DATE:
                case java.sql.Types.INTEGER:
                case java.sql.Types.BIGINT:
                case java.sql.Types.BOOLEAN:
                default:
                    if (value instanceof String)
                        stmt.setString(i + 1, (String) value);
                    else if (value instanceof Boolean)
                        stmt.setBoolean(i + 1, (Boolean) value);
                    else if (value instanceof Integer)
                        stmt.setInt(i + 1, (Integer) value);
                    else if (value instanceof Double)
                        stmt.setDouble(i + 1, (Double) value);
                    else if (value instanceof BigDecimal)
                        stmt.setBigDecimal(i + 1, (BigDecimal) value);
                    else if (value instanceof Long)
                        stmt.setLong(i + 1, (Long) value);
                    else if (value instanceof java.util.Date) {
                        // TBD: shouldn't we use setDate with a proper Calendar?
                        stmt.setTimestamp(i + 1, new java.sql.Timestamp(((Date) value).getTime()));
                    } else if (value instanceof java.util.Calendar) {
                        // TBD: shouldn't we use setDate with a proper Calendar?
                        final Date vd = ((Calendar) value).getTime();
                        stmt.setTimestamp(i + 1, new java.sql.Timestamp(vd.getTime()));
                    } else if (value instanceof java.sql.Date) {
                        /* Note: this is just the DATE component, no TIME */
                        stmt.setDate(i + 1, (java.sql.Date) value);
                    } else if (value instanceof byte[])
                        stmt.setBytes(i + 1, (byte[]) value);
                    else if (value instanceof EOQualifierVariable) {
                        log.error("detected unresolved qualifier variable: " + value);
                        this._releaseResources(stmt, null);
                        return null;
                    } else {
                        log.warn("using String column for value: " + value + " (" + value.getClass() + ")");
                    }
                }
            }
        }
    } catch (NullPointerException e) {
        this.lastException = e;
        log.error("could not apply binds to prepared statement (null ptr): " + _sql, e);
        this._releaseResources(stmt, null);
        return null;
    } catch (SQLException e) {
        this.lastException = e;
        log.error("could not apply binds to prepared statement: " + _sql, e);
        this._releaseResources(stmt, null);
        return null;
    }

    return stmt;
}

From source file:org.siphon.jssql.SqlExecutor.java

void setArg(PreparedStatement ps, int index, Object arg) throws SQLException, SqlExecutorException,
        UnsupportedDataTypeException, NoSuchMethodException, ScriptException {
    boolean output = false;
    int outputParameterType = 0;
    CallableStatement cs = null;/*from   ww w . j  a va 2 s . c o m*/
    if (ps instanceof CallableStatement) {
        cs = (CallableStatement) ps;
        if (arg instanceof ScriptObjectMirror && ((ScriptObjectMirror) arg).containsKey("OUT")) {
            ScriptObjectMirror jsarg = ((ScriptObjectMirror) arg);
            outputParameterType = (int) jsarg.get("JDBC_TYPE");
            arg = jsarg.get("VALUE");
            output = true;
        }
    }
    if (output) {
        cs.registerOutParameter(index + 1, outputParameterType);
        if (JsTypeUtil.isNull(arg) || (arg instanceof Double && Double.isNaN((Double) arg))) {
            return;
        }
    }

    if (JsTypeUtil.isNull(arg)) {
        ps.setObject(index + 1, null);
    } else if (arg instanceof CharSequence) {
        ps.setString(index + 1, arg.toString());
    } else if (arg instanceof NativeString) {
        ps.setString(index + 1, arg.toString());
    } else if (arg instanceof Double) { // js number always be
        // Doublebut if its came from
        // JSON.parse since JSON is jdk
        // given global object, it will
        // make Integer and ...
        double d = ((Double) arg).doubleValue();
        if (d == (int) d) {
            ps.setInt(index + 1, (int) d);
        } else if (d == (long) d) {
            ps.setLong(index + 1, (long) d);
        } else {
            ps.setBigDecimal(index + 1, new BigDecimal(d));
        }
    } else if (arg instanceof Integer) {
        ps.setInt(index + 1, (Integer) arg);
    } else if (arg instanceof Long) {
        ps.setLong(index + 1, (Long) arg);
    } else if (arg instanceof Float) {
        ps.setFloat(index + 1, (Float) arg);
    } else if (jsTypeUtil.isNativeDate(arg)) {
        ps.setTimestamp(index + 1, parseDate(arg));
    } else if (arg instanceof ZonedDateTime) {
        ZonedDateTime zdt = (ZonedDateTime) arg;
        ps.setTimestamp(index + 1, new Timestamp(zdt.toInstant().toEpochMilli()));
    } else if (arg instanceof Boolean) {
        ps.setBoolean(index + 1, JsTypeUtil.isTrue(arg));
    } else if (arg instanceof ScriptObjectMirror || arg instanceof ScriptObject) {
        String attr = null;
        Object value = null;
        if (arg instanceof ScriptObjectMirror) {
            ScriptObjectMirror atm = (ScriptObjectMirror) arg;
            if (atm.keySet().contains("toJavaObject")) {
                Object obj = atm.callMember("toJavaObject");
                setArg(ps, index, obj);
                return;
            }

            attr = atm.keySet().iterator().next();
            value = atm.get(attr);
        } else {
            ScriptObject obj = (ScriptObject) arg;
            if (obj.containsKey("toJavaObject")) {
                ScriptObjectMirror atm = (ScriptObjectMirror) jsTypeUtil.toScriptObjectMirror(obj);
                Object result = atm.callMember("toJavaObject");
                setArg(ps, index, result);
                return;
            }
            String[] arr = obj.getOwnKeys(false);
            if (arr.length == 0) {
                throw new SqlExecutorException("js argument " + arg + " (" + arg.getClass() + ") at " + index
                        + " is an empty js object");
            }
            attr = arr[0];
            value = obj.get(attr);
        }

        if ("STRING".equals(attr)) {
            ps.setString(index + 1, String.valueOf(value));
        } else if ("DECIMAL".equals(attr)) {
            if (value instanceof Double) {
                ps.setBigDecimal(index + 1, new BigDecimal((Double) value));
            } else {
                ps.setBigDecimal(index + 1, new BigDecimal(value + ""));
            }
        } else if ("INT".equals(attr)) {
            if (value instanceof Double) {
                if (((Double) value).isNaN()) {
                    ps.setObject(index + 1, null);
                } else {
                    ps.setInt(index + 1, ((Double) value).intValue());
                }
            } else {
                ps.setInt(index + 1, new Integer(value + ""));
            }
        } else if ("BOOLEAN".equals(attr)) {
            ps.setBoolean(index + 1, JsTypeUtil.isTrue(arg));
        } else if ("DOUBLE".equals(attr)) {
            if (value instanceof Double) {
                if (((Double) value).isNaN()) {
                    ps.setObject(index + 1, null);
                } else {
                    ps.setDouble(index + 1, (double) value);
                }
            } else {
                ps.setDouble(index + 1, new Double(value + ""));
            }
        } else if ("FLOAT".equals(attr)) {
            if (value instanceof Double) {
                if (((Double) value).isNaN()) {
                    ps.setObject(index + 1, null);
                } else {
                    ps.setFloat(index + 1, (float) (double) value);
                }
            } else {
                ps.setFloat(index + 1, new Float(value + ""));
            }
        } else if ("DATE".equals(attr)) {
            ps.setTimestamp(index + 1, parseDate(value));
        } else if ("TIME".equals(attr)) {
            ps.setTimestamp(index + 1, parseTime(value));
        } else if ("BINARY".equals(attr)) {
            ps.setBytes(index + 1, parseBinary(value));
        } else if ("CLOB".equals(attr)) {
            Clob clob = ps.getConnection().createClob();
            clob.setString(1, String.valueOf(value));
            ps.setClob(index + 1, clob);
        } else if ("LONG".equals(attr)) {
            if (value instanceof Double) {
                if (((Double) value).isNaN()) {
                    ps.setObject(index + 1, null);
                } else {
                    ps.setLong(index + 1, ((Double) value).longValue());
                }
            } else {
                ps.setLong(index + 1, new Long(value + ""));
            }
        } else if ("OUTCURSOR".equals(attr)) {
            // cs.registerOutParameter(i+1, OracleTypes.CURSOR);
            cs.registerOutParameter(index + 1, -10);
        } else if ("ARRAY".equals(attr)) {
            if (value instanceof NativeArray) {
                ps.setArray(index + 1, createSqlArray(ps.getConnection(), (NativeArray) value));
            } else {
                setArg(ps, index, value); // value is {ARRAY : ['int', e1, e2, ...]}
            }
            // ps.setObject(i+1, createSqlArray(ps.getConnection(),
            // (NativeArray) value));
        } else if ("JSON".equals(attr) || "JSONB".equals(attr)) {
            PGobject obj = new PGobject();
            obj.setType(attr.toLowerCase());
            obj.setValue(this.JSON.tryStringify(value));
            ps.setObject(index + 1, obj);
        } else if ("UUID".equals(attr)) {
            if (value != null) {
                ps.setObject(index + 1, UUID.fromString(value.toString()));
            } else {
                ps.setObject(index + 1, null);
            }
        } else {
            if (this.defaultJsonDbType != null) {
                PGobject obj = new PGobject();
                obj.setType(this.defaultJsonDbType);
                obj.setValue(this.JSON.tryStringify(arg));
                ps.setObject(index + 1, obj);
            } else {
                throw new SqlExecutorException("js argument " + arg + " (" + arg.getClass() + ") not support");
            }
        }
    } else {
        throw new SqlExecutorException(
                "js argument " + arg + " (" + arg.getClass() + ") at " + index + " not support");
    }
}

From source file:org.moqui.impl.entity.EntityJavaUtil.java

public static void setPreparedStatementValue(PreparedStatement ps, int index, Object value, FieldInfo fi,
        boolean useBinaryTypeForBlob, EntityFacade efi) throws EntityException {
    try {/* w w w.  jav a2 s  .  co  m*/
        // allow setting, and searching for, String values for all types; JDBC driver should handle this okay
        if (value instanceof CharSequence) {
            ps.setString(index, value.toString());
        } else {
            switch (fi.typeValue) {
            case 1:
                if (value != null) {
                    ps.setString(index, value.toString());
                } else {
                    ps.setNull(index, Types.VARCHAR);
                }
                break;
            case 2:
                if (value != null) {
                    Class valClass = value.getClass();
                    if (valClass == Timestamp.class) {
                        ps.setTimestamp(index, (Timestamp) value, efi.getCalendarForTzLc());
                    } else if (valClass == java.sql.Date.class) {
                        ps.setDate(index, (java.sql.Date) value, efi.getCalendarForTzLc());
                    } else if (valClass == java.util.Date.class) {
                        ps.setTimestamp(index, new Timestamp(((java.util.Date) value).getTime()),
                                efi.getCalendarForTzLc());
                    } else {
                        throw new IllegalArgumentException("Class " + valClass.getName()
                                + " not allowed for date-time (Timestamp) fields, for field " + fi.entityName
                                + "." + fi.name);
                    }
                } else {
                    ps.setNull(index, Types.TIMESTAMP);
                }
                break;
            case 3:
                Time tm = (Time) value;
                // logger.warn("=================== setting time tm=${tm} tm long=${tm.getTime()}, cal=${cal}")
                if (value != null) {
                    ps.setTime(index, tm, efi.getCalendarForTzLc());
                } else {
                    ps.setNull(index, Types.TIME);
                }
                break;
            case 4:
                if (value != null) {
                    Class valClass = value.getClass();
                    if (valClass == java.sql.Date.class) {
                        java.sql.Date dt = (java.sql.Date) value;
                        // logger.warn("=================== setting date dt=${dt} dt long=${dt.getTime()}, cal=${cal}")
                        ps.setDate(index, dt, efi.getCalendarForTzLc());
                    } else if (valClass == Timestamp.class) {
                        ps.setDate(index, new java.sql.Date(((Timestamp) value).getTime()),
                                efi.getCalendarForTzLc());
                    } else if (valClass == java.util.Date.class) {
                        ps.setDate(index, new java.sql.Date(((java.util.Date) value).getTime()),
                                efi.getCalendarForTzLc());
                    } else {
                        throw new IllegalArgumentException("Class " + valClass.getName()
                                + " not allowed for date fields, for field " + fi.entityName + "." + fi.name);
                    }
                } else {
                    ps.setNull(index, Types.DATE);
                }
                break;
            case 5:
                if (value != null) {
                    ps.setInt(index, ((Number) value).intValue());
                } else {
                    ps.setNull(index, Types.NUMERIC);
                }
                break;
            case 6:
                if (value != null) {
                    ps.setLong(index, ((Number) value).longValue());
                } else {
                    ps.setNull(index, Types.NUMERIC);
                }
                break;
            case 7:
                if (value != null) {
                    ps.setFloat(index, ((Number) value).floatValue());
                } else {
                    ps.setNull(index, Types.NUMERIC);
                }
                break;
            case 8:
                if (value != null) {
                    ps.setDouble(index, ((Number) value).doubleValue());
                } else {
                    ps.setNull(index, Types.NUMERIC);
                }
                break;
            case 9:
                if (value != null) {
                    Class valClass = value.getClass();
                    // most common cases BigDecimal, Double, Float; then allow any Number
                    if (valClass == BigDecimal.class) {
                        ps.setBigDecimal(index, (BigDecimal) value);
                    } else if (valClass == Double.class) {
                        ps.setDouble(index, (Double) value);
                    } else if (valClass == Float.class) {
                        ps.setFloat(index, (Float) value);
                    } else if (value instanceof Number) {
                        ps.setDouble(index, ((Number) value).doubleValue());
                    } else {
                        throw new IllegalArgumentException("Class " + valClass.getName()
                                + " not allowed for number-decimal (BigDecimal) fields, for field "
                                + fi.entityName + "." + fi.name);
                    }
                } else {
                    ps.setNull(index, Types.NUMERIC);
                }
                break;
            case 10:
                if (value != null) {
                    ps.setBoolean(index, (Boolean) value);
                } else {
                    ps.setNull(index, Types.BOOLEAN);
                }
                break;
            case 11:
                if (value != null) {
                    try {
                        ByteArrayOutputStream os = new ByteArrayOutputStream();
                        ObjectOutputStream oos = new ObjectOutputStream(os);
                        oos.writeObject(value);
                        oos.close();
                        byte[] buf = os.toByteArray();
                        os.close();

                        ByteArrayInputStream is = new ByteArrayInputStream(buf);
                        ps.setBinaryStream(index, is, buf.length);
                        is.close();
                    } catch (IOException ex) {
                        throw new EntityException(
                                "Error setting serialized object, for field " + fi.entityName + "." + fi.name,
                                ex);
                    }
                } else {
                    if (useBinaryTypeForBlob) {
                        ps.setNull(index, Types.BINARY);
                    } else {
                        ps.setNull(index, Types.BLOB);
                    }
                }
                break;
            case 12:
                if (value instanceof byte[]) {
                    ps.setBytes(index, (byte[]) value);
                    /*
                    } else if (value instanceof ArrayList) {
                        ArrayList valueAl = (ArrayList) value;
                        byte[] theBytes = new byte[valueAl.size()];
                        valueAl.toArray(theBytes);
                        ps.setBytes(index, theBytes);
                    */
                } else if (value instanceof ByteBuffer) {
                    ByteBuffer valueBb = (ByteBuffer) value;
                    ps.setBytes(index, valueBb.array());
                } else if (value instanceof Blob) {
                    Blob valueBlob = (Blob) value;
                    // calling setBytes instead of setBlob
                    // ps.setBlob(index, (Blob) value)
                    // Blob blb = value
                    ps.setBytes(index, valueBlob.getBytes(1, (int) valueBlob.length()));
                } else {
                    if (value != null) {
                        throw new IllegalArgumentException("Type not supported for BLOB field: "
                                + value.getClass().getName() + ", for field " + fi.entityName + "." + fi.name);
                    } else {
                        if (useBinaryTypeForBlob) {
                            ps.setNull(index, Types.BINARY);
                        } else {
                            ps.setNull(index, Types.BLOB);
                        }
                    }
                }
                break;
            case 13:
                if (value != null) {
                    ps.setClob(index, (Clob) value);
                } else {
                    ps.setNull(index, Types.CLOB);
                }
                break;
            case 14:
                if (value != null) {
                    ps.setTimestamp(index, (Timestamp) value);
                } else {
                    ps.setNull(index, Types.TIMESTAMP);
                }
                break;
            // TODO: is this the best way to do collections and such?
            case 15:
                if (value != null) {
                    ps.setObject(index, value, Types.JAVA_OBJECT);
                } else {
                    ps.setNull(index, Types.JAVA_OBJECT);
                }
                break;
            }
        }
    } catch (SQLException sqle) {
        throw new EntityException("SQL Exception while setting value [" + value + "]("
                + (value != null ? value.getClass().getName() : "null") + "), type " + fi.type + ", for field "
                + fi.entityName + "." + fi.name + ": " + sqle.toString(), sqle);
    } catch (Exception e) {
        throw new EntityException(
                "Error while setting value for field " + fi.entityName + "." + fi.name + ": " + e.toString(),
                e);
    }
}

From source file:org.apache.tajo.catalog.store.AbstractDBStore.java

@Override
public void createTable(final CatalogProtos.TableDescProto table)
        throws UndefinedDatabaseException, DuplicateTableException {

    Connection conn = null;//w  ww.  ja  va 2  s  .  c o m
    PreparedStatement pstmt = null;
    ResultSet res = null;

    final String[] splitted = IdentifierUtil.splitTableName(table.getTableName());
    if (splitted.length == 1) {
        throw new TajoInternalError(
                "createTable() requires a qualified table name, but it is '" + table.getTableName() + "'");
    }
    final String databaseName = splitted[0];
    final String tableName = splitted[1];

    if (existTable(databaseName, tableName)) {
        throw new DuplicateTableException(tableName);
    }
    final int dbid = getDatabaseId(databaseName);

    try {
        conn = getConnection();
        conn.setAutoCommit(false);

        String sql = "INSERT INTO TABLES (DB_ID, " + COL_TABLES_NAME
                + ", TABLE_TYPE, PATH, DATA_FORMAT, HAS_SELF_DESCRIBE_SCHEMA) VALUES(?, ?, ?, ?, ?, ?) ";

        if (LOG.isDebugEnabled()) {
            LOG.debug(sql);
        }

        pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, dbid);
        pstmt.setString(2, tableName);
        if (table.getIsExternal()) {
            pstmt.setString(3, TableType.EXTERNAL.name());
        } else {
            pstmt.setString(3, TableType.MANAGED.name());
        }
        pstmt.setString(4, table.getPath());
        pstmt.setString(5, table.getMeta().getDataFormat());
        pstmt.setBoolean(6, table.getSchema() == null);
        pstmt.executeUpdate();
        pstmt.close();

        String tidSql = "SELECT TID from " + TB_TABLES + " WHERE " + COL_DATABASES_PK + "=? AND "
                + COL_TABLES_NAME + "=?";
        pstmt = conn.prepareStatement(tidSql);
        pstmt.setInt(1, dbid);
        pstmt.setString(2, tableName);
        res = pstmt.executeQuery();

        if (!res.next()) {
            throw new TajoInternalError("There is no TID matched to '" + table.getTableName() + '"');
        }

        int tableId = res.getInt("TID");
        res.close();
        pstmt.close();

        String colSql = "INSERT INTO " + TB_COLUMNS +
        // 1    2            3                 4
                " (TID, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE)" + " VALUES(?, ?, ?, ?) ";

        if (LOG.isDebugEnabled()) {
            LOG.debug(colSql);
        }

        pstmt = conn.prepareStatement(colSql);
        for (int i = 0; i < table.getSchema().getFieldsCount(); i++) {
            ColumnProto col = table.getSchema().getFields(i);
            org.apache.tajo.type.Type type = TypeProtobufEncoder.decode(col.getType());

            pstmt.setInt(1, tableId);
            pstmt.setString(2, extractSimpleName(col.getName()));
            pstmt.setInt(3, i);
            pstmt.setString(4, TypeStringEncoder.encode(type));
            pstmt.addBatch();
            pstmt.clearParameters();
        }
        pstmt.executeBatch();
        pstmt.close();

        if (table.getMeta().hasParams()) {
            String propSQL = "INSERT INTO " + TB_OPTIONS + "(TID, KEY_, VALUE_) VALUES(?, ?, ?)";

            if (LOG.isDebugEnabled()) {
                LOG.debug(propSQL);
            }

            pstmt = conn.prepareStatement(propSQL);
            for (KeyValueProto entry : table.getMeta().getParams().getKeyvalList()) {
                pstmt.setInt(1, tableId);
                pstmt.setString(2, entry.getKey());
                pstmt.setString(3, entry.getValue());
                pstmt.addBatch();
                pstmt.clearParameters();
            }
            pstmt.executeBatch();
            pstmt.close();
        }

        if (table.hasStats()) {

            String statSql = "INSERT INTO " + TB_STATISTICS + " (TID, NUM_ROWS, NUM_BYTES) VALUES(?, ?, ?)";

            if (LOG.isDebugEnabled()) {
                LOG.debug(statSql);
            }

            pstmt = conn.prepareStatement(statSql);
            pstmt.setInt(1, tableId);
            pstmt.setLong(2, table.getStats().getNumRows());
            pstmt.setLong(3, table.getStats().getNumBytes());
            pstmt.executeUpdate();
            pstmt.close();
        }

        if (table.hasPartition()) {
            String partSql = "INSERT INTO PARTITION_METHODS (TID, PARTITION_TYPE, EXPRESSION, EXPRESSION_SCHEMA) VALUES(?, ?, ?, ?)";

            if (LOG.isDebugEnabled()) {
                LOG.debug(partSql);
            }

            pstmt = conn.prepareStatement(partSql);
            pstmt.setInt(1, tableId);
            pstmt.setString(2, table.getPartition().getPartitionType().name());
            pstmt.setString(3, table.getPartition().getExpression());
            pstmt.setBytes(4, table.getPartition().getExpressionSchema().toByteArray());
            pstmt.executeUpdate();
        }

        // If there is no error, commit the changes.
        conn.commit();
    } catch (SQLException se) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                LOG.error(e, e);
            }
        }
        throw new TajoInternalError(se);
    } finally {
        CatalogUtil.closeQuietly(pstmt, res);
    }
}

From source file:com.portfolio.data.provider.MysqlAdminProvider.java

public int updateMysqlFile(String nodeUuid, String lang, String fileName, String type, String extension,
        int size, byte[] fileBytes, int userId) {
    String sql = "";
    PreparedStatement st;

    try {//ww w .  j a  va  2  s  . c  om
        if (dbserveur.equals("mysql")) {
            sql = "REPLACE INTO file_table(node_uuid,lang,name,type,extension,filesize,filecontent,modif_user_id,modif_date) ";
            sql += "VALUES(uuid2bin(?),?,?,?,?,?,?,?,?)";
        } else if (dbserveur.equals("oracle")) {
            sql = "MERGE INTO file_table d USING (SELECT uuid2bin(?) node_uuid,? lang,? name,? type,? extension,? filesize,? filecontent,? modif_user_id,? modif_date FROM DUAL) s ON (d.node_uuid = s.node_uuid AND d.lang = s.lang) WHEN MATCHED THEN UPDATE SET d.name = s.name, d.type = s.type, d.extension = s.extension, d.filesize = s.filesize, d.filecontent = s.filecontent, d.modif_user_id = s.modif_user_id, d.modif_date = s.modif_date WHEN NOT MATCHED THEN INSERT (d.node_uuid, d.lang, d.name, d.type, d.extension, d.filesize, d.filecontent, d.modif_user_id, d.modif_date) VALUES (s.node_uuid, s.lang, s.name, s.type, s.extension, s.filesize, s.filecontent, s.modif_user_id, s.modif_date)";
        }
        st = connection.prepareStatement(sql);
        st.setString(1, nodeUuid);
        st.setString(2, lang);
        st.setString(3, fileName);
        st.setString(4, type);
        st.setString(5, extension);
        st.setInt(6, size);
        st.setBytes(7, fileBytes);
        st.setInt(8, userId);
        if (dbserveur.equals("mysql")) {
            st.setString(9, SqlUtils.getCurrentTimeStamp());
        } else if (dbserveur.equals("oracle")) {
            st.setTimestamp(9, SqlUtils.getCurrentTimeStamp2());
        }

        return st.executeUpdate();

    } catch (Exception ex) {
        //System.out.println("root_node_uuid : "+uuid);
        ex.printStackTrace();
        return -1;
    }
}

From source file:org.wso2.carbon.apimgt.impl.dao.ApiMgtDAO.java

/**
 * Add a Subscription level throttling policy to database
 *
 * @param policy policy object defining the throttle policy
 * @throws APIManagementException//from   w w  w  . j  a  v a 2 s  .c  o  m
 */
public void addSubscriptionPolicy(SubscriptionPolicy policy) throws APIManagementException {
    Connection conn = null;
    PreparedStatement policyStatement = null;
    boolean hasCustomAttrib = false;

    try {
        if (policy.getCustomAttributes() != null) {
            hasCustomAttrib = true;
        }
        conn = APIMgtDBUtil.getConnection();
        conn.setAutoCommit(false);
        String addQuery = SQLConstants.INSERT_SUBSCRIPTION_POLICY_SQL;
        if (hasCustomAttrib) {
            addQuery = SQLConstants.INSERT_SUBSCRIPTION_POLICY_WITH_CUSTOM_ATTRIB_SQL;
        }
        policyStatement = conn.prepareStatement(addQuery);
        setCommonParametersForPolicy(policyStatement, policy);
        policyStatement.setInt(12, policy.getRateLimitCount());
        policyStatement.setString(13, policy.getRateLimitTimeUnit());
        policyStatement.setBoolean(14, policy.isStopOnQuotaReach());
        policyStatement.setString(15, policy.getBillingPlan());
        if (hasCustomAttrib) {
            policyStatement.setBytes(16, policy.getCustomAttributes());
        }
        policyStatement.executeUpdate();

        conn.commit();
    } catch (SQLException e) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException ex) {

                // Rollback failed. Exception will be thrown later for upper exception
                log.error("Failed to rollback the add Subscription Policy: " + policy.toString(), ex);
            }
        }
        handleException("Failed to add Subscription Policy: " + policy, e);
    } finally {
        APIMgtDBUtil.closeAllConnections(policyStatement, conn, null);
    }
}

From source file:l2next.gameserver.model.Player.java

/**
 * Update L2Player stats in the characters table of the database.
 *//* w w w  .j ava  2s.c  o m*/
public void store(boolean fast) {
    if (!_storeLock.tryLock()) {
        return;
    }

    try {
        Connection con = null;
        PreparedStatement statement = null;
        try {
            con = DatabaseFactory.getInstance().getConnection();
            statement = con.prepareStatement(
                    "UPDATE characters SET face=?,hairStyle=?,hairColor=?,sex=?,x=?,y=?,z=?,karma=?,pvpkills=?,pkkills=?,rec_have=?,rec_left=?,rec_bonus_time=?,clanid=?,deletetime=?,title=?,accesslevel=?,online=?,leaveclan=?,deleteclan=?,nochannel=?,onlinetime=?,pledge_type=?,pledge_rank=?,lvl_joined_academy=?,apprentice=?,key_bindings=?,pcBangPoints=?,char_name=?,fame=?,bookmarks=? WHERE obj_Id=? LIMIT 1");
            statement.setInt(1, getFace());
            statement.setInt(2, getHairStyle());
            statement.setInt(3, getHairColor());
            statement.setInt(4, getSex());
            if (_stablePoint == null) {
                statement.setInt(5, getX());
                statement.setInt(6, getY());
                statement.setInt(7, getZ());
            } else {
                statement.setInt(5, _stablePoint.x);
                statement.setInt(6, _stablePoint.y);
                statement.setInt(7, _stablePoint.z);
            }
            statement.setInt(8, getKarma());
            statement.setInt(9, getPvpKills());
            statement.setInt(10, getPkKills());
            statement.setInt(11, getRecomHave());
            statement.setInt(12, getRecomLeft());
            statement.setInt(13, getRecomBonusTime());
            statement.setInt(14, getClanId());
            statement.setInt(15, getDeleteTimer());
            statement.setString(16, _title);
            statement.setInt(17, _accessLevel);
            statement.setInt(18, isOnline() && !isInOfflineMode() ? 1 : 0);
            statement.setLong(19, getLeaveClanTime() / 1000L);
            statement.setLong(20, getDeleteClanTime() / 1000L);
            statement.setLong(21, _NoChannel > 0 ? getNoChannelRemained() / 1000 : _NoChannel);
            statement.setInt(22,
                    (int) (_onlineBeginTime > 0
                            ? (_onlineTime + System.currentTimeMillis() - _onlineBeginTime) / 1000L
                            : _onlineTime / 1000L));

            // long totalOnlineTime = (int) (_onlineBeginTime > 0 ?
            // (_onlineTime + System.currentTimeMillis() - _onlineBeginTime)
            // / 1000L : _onlineTime / 1000L);

            if (_onlineBeginTime > 0L) {
                WorldStatisticsManager.getInstance().updateStat(this, CategoryType.TIME_PLAYED,
                        (System.currentTimeMillis() - _onlineBeginTime) / 1000);
            }

            statement.setInt(23, getPledgeType());
            statement.setInt(24, getPowerGrade());
            statement.setInt(25, getLvlJoinedAcademy());
            statement.setInt(26, getApprentice());
            statement.setBytes(27, getKeyBindings());
            statement.setInt(28, getPcBangPoints());
            statement.setString(29, getName());
            statement.setInt(30, getFame());
            statement.setInt(31, bookmarks.getCapacity());
            statement.setInt(32, getObjectId());

            statement.executeUpdate();
            GameStats.increaseUpdatePlayerBase();

            if (!fast) {
                EffectsDAO.getInstance().insert(this);
                CharacterGroupReuseDAO.getInstance().insert(this);
                CharactersPremiumItemsDAO.getInstance().insert(this);
                storeDisableSkills();
                storeBlockList();
            }

            storeCharSubClasses();
            bookmarks.store();
        } catch (Exception e) {
            _log.error("Could not store char data: " + this + "!", e);
        } finally {
            DbUtils.closeQuietly(con, statement);
        }
    } finally {
        _storeLock.unlock();
    }
}

From source file:lineage2.gameserver.model.Player.java

/**
 * Method store./*www.  ja v a  2 s .  co  m*/
 * @param fast boolean
 */
public void store(boolean fast) {
    if (!_storeLock.tryLock()) {
        return;
    }
    try {
        Connection con = null;
        PreparedStatement statement = null;
        try {
            con = DatabaseFactory.getInstance().getConnection();
            statement = con.prepareStatement(
                    "UPDATE characters SET face=?,hairStyle=?,hairColor=?,sex=?,x=?,y=?,z=?,karma=?,pvpkills=?,pkkills=?,rec_have=?,rec_left=?,rec_bonus_time=?,clanid=?,deletetime=?,title=?,accesslevel=?,online=?,leaveclan=?,deleteclan=?,nochannel=?,onlinetime=?,pledge_type=?,pledge_rank=?,lvl_joined_academy=?,apprentice=?,key_bindings=?,pcBangPoints=?,char_name=?,fame=?,bookmarks=? WHERE obj_Id=? LIMIT 1");
            statement.setInt(1, getFace());
            statement.setInt(2, getHairStyle());
            statement.setInt(3, getHairColor());
            statement.setInt(4, getSex());
            if (_stablePoint == null) {
                statement.setInt(5, getX());
                statement.setInt(6, getY());
                statement.setInt(7, getZ());
            } else {
                statement.setInt(5, _stablePoint.x);
                statement.setInt(6, _stablePoint.y);
                statement.setInt(7, _stablePoint.z);
            }
            statement.setInt(8, getKarma());
            statement.setInt(9, getPvpKills());
            statement.setInt(10, getPkKills());
            statement.setInt(11, getRecomHave());
            statement.setInt(12, getRecomLeft());
            statement.setInt(13, getRecomBonusTime());
            statement.setInt(14, getClanId());
            statement.setInt(15, getDeleteTimer());
            statement.setString(16, _title);
            statement.setInt(17, _accessLevel);
            statement.setInt(18, isOnline() && !isInOfflineMode() ? 1 : 0);
            statement.setLong(19, getLeaveClanTime() / 1000L);
            statement.setLong(20, getDeleteClanTime() / 1000L);
            statement.setLong(21, _NoChannel > 0 ? getNoChannelRemained() / 1000 : _NoChannel);
            statement.setInt(22,
                    (int) (_onlineBeginTime > 0
                            ? ((_onlineTime + System.currentTimeMillis()) - _onlineBeginTime) / 1000L
                            : _onlineTime / 1000L));

            if (_onlineBeginTime > 0L)
                WorldStatisticsManager.getInstance().updateStat(this, CategoryType.TIME_PLAYED,
                        (System.currentTimeMillis() - _onlineBeginTime) / 1000);

            statement.setInt(23, getPledgeType());
            statement.setInt(24, getPowerGrade());
            statement.setInt(25, getLvlJoinedAcademy());
            statement.setInt(26, getApprentice());
            statement.setBytes(27, getKeyBindings());
            statement.setInt(28, getPcBangPoints());
            statement.setString(29, getName());
            statement.setInt(30, getFame());
            statement.setInt(31, bookmarks.getCapacity());
            statement.setInt(32, getObjectId());
            statement.executeUpdate();
            GameStats.increaseUpdatePlayerBase();
            if (!fast) {
                EffectsDAO.getInstance().insert(this);
                CharacterGroupReuseDAO.getInstance().insert(this);
                storeDisableSkills();
                storeBlockList();
            }
            storeCharSubClasses();
            bookmarks.store();
            DbUtils.closeQuietly(con, statement);
            con = DatabaseFactory.getInstance().getConnection();
            statement = con.prepareStatement("UPDATE `vitality_points` SET `points`=? WHERE `account_name`=?");
            statement.setInt(1, getVitality());
            statement.setString(2, getAccountName());
            statement.execute();
        } catch (Exception e) {
            _log.error("Could not store char data: " + this + "!", e);
        } finally {
            DbUtils.closeQuietly(con, statement);
        }
    } finally {
        _storeLock.unlock();
    }
}