Example usage for java.sql CallableStatement setNull

List of usage examples for java.sql CallableStatement setNull

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. CallableStatement
  6. setNull

Introduction

In this page you can find the example usage for java.sql CallableStatement setNull.

Prototype

void setNull(String parameterName, int sqlType) throws SQLException;

Source Link

Document

Sets the designated parameter to SQL NULL.

Usage

From source file:com.medlog.webservice.util.ToneAnalyzerExample.java

public static void processTone(DbConnection dbc, ToneAnalysis tone, int diaryID) {
    CallableStatement cs = null;
    String cat_id = "";
    try {/*from  www  .j  a  va2 s . co m*/
        //category , tone , sentance,score,text
        List<ToneCategory> to = tone.getDocumentTone().getTones();
        Connection conn = dbc.getConnnection();

        conn.prepareCall(new StringBuilder().append("{call spDiaryTextScoreInsert(").append(diaryID)
                .append(",?,?,?,?,?)}").toString());
        conn.setAutoCommit(false);
        cs.setInt(3, 0);
        cs.setNull(5, java.sql.Types.NVARCHAR);// cat_id);

        for (ToneCategory docTC : to) {
            cat_id = docTC.getId();
            cs.setString(1, cat_id);
            for (ToneScore s : docTC.getTones()) {
                cs.setString(2, s.getId());
                cs.setDouble(4, s.getScore());
                cs.addBatch();
            }
            int[] docRes = cs.executeBatch();
            cs.clearBatch();
            System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() result --- "
                    + ArrayUtils.toString(docRes));
        }
        System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() Process "
                + tone.getSentencesTone().size() + " sentances.");
        for (SentenceTone sentT : tone.getSentencesTone()) {
            to = sentT.getTones();
            cs.setInt(3, sentT.getId());
            cs.setString(5, toS(sentT.getText()).trim());
            for (ToneCategory docTC : to) {
                cat_id = docTC.getId();
                cs.setString(1, cat_id);
                for (ToneScore s : docTC.getTones()) {
                    cs.setString(2, s.getId());
                    cs.setDouble(4, s.getScore());
                    cs.addBatch();
                }
                int[] docRes = cs.executeBatch();
                cs.clearBatch();
                System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() result["
                        + sentT.getId() + "] " + ArrayUtils.toString(docRes));
            }
        }

    } catch (SQLException ex) {
        Logger.getLogger(ToneAnalyzerExample.class.getName()).log(Level.SEVERE, null, ex);
    }
}

From source file:ips1ap101.lib.core.db.util.DB.java

public static CallableStatement prepareCall(Connection connection, String sql, Object[] args,
        EnumTipoResultadoSQL resultType, EnumTipoDatoSQL dataType) {
    CallableStatement callableStatement;
    if (connection != null && sql != null) {
        try {//from w  w  w.ja va  2 s  . c o  m
            callableStatement = connection.prepareCall(sql);
            int n = args == null ? 0 : args.length;
            if (n > 0) {
                for (int i = 0; i < n; i++) {
                    if (args[i] == null) {
                        //                          callableStatement.setNull(i + 1, java.sql.Types.OTHER);
                        callableStatement.setNull(i + 1, java.sql.Types.NULL);
                    } else if (args[i] instanceof EnumTipoDatoSQL) {
                        EnumTipoDatoSQL tipoDatoSQL = (EnumTipoDatoSQL) args[i];
                        callableStatement.setNull(i + 1, tipoDatoSQL.intValue());
                    } else {
                        callableStatement.setObject(i + 1, args[i]);
                    }
                }
            }
            if (EnumTipoResultadoSQL.SIMPLE.equals(resultType) && dataType != null) {
                callableStatement.registerOutParameter(n + 1, dataType.intValue());
            }
            return callableStatement;
        } catch (SQLException ex) {
            Bitacora.logFatal(ex);
        }
    }
    return null;
}

From source file:com.medlog.webservice.services.tone.ToneProcessorFactory.java

private static ArrayList<Integer> processTone(DbConnection dbc, ToneAnalysis tone, int diaryID) {
    CallableStatement cs = null;
    String cat_id = "";
    ArrayList<Integer> results = new ArrayList<Integer>();
    try {//from   w  w w. j av a  2 s. com
        //category , tone , sentance,score,text
        List<com.ibm.watson.developer_cloud.tone_analyzer.v3.model.ToneCategory> to = tone.getDocumentTone()
                .getTones();
        Connection conn = dbc.getConnnection();

        cs = conn.prepareCall(new StringBuilder().append("{call spDiaryTextScoreInsert(").append(diaryID)
                .append(",?,?,?,?,?)}").toString());
        conn.setAutoCommit(false);
        cs.setInt(3, 0);
        cs.setNull(5, java.sql.Types.NVARCHAR);// cat_id);

        for (com.ibm.watson.developer_cloud.tone_analyzer.v3.model.ToneCategory docTC : to) {
            cat_id = docTC.getId();
            cs.setString(1, cat_id);
            for (ToneScore s : docTC.getTones()) {
                cs.setString(2, s.getId());
                cs.setDouble(4, s.getScore());
                cs.addBatch();
            }

        }
        System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() Process "
                + tone.getSentencesTone().size() + " sentances.");
        int[] docRes = cs.executeBatch();
        List l = Arrays.asList(docRes);
        results.addAll(l);

        cs.clearBatch();
        System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() result --- "
                + ArrayUtils.toString(docRes));
        int[] sentRes = null;
        for (SentenceTone sentT : tone.getSentencesTone()) {
            to = sentT.getTones();
            cs.setInt(3, sentT.getId());
            cs.setString(5, toS(sentT.getText()).trim());
            for (com.ibm.watson.developer_cloud.tone_analyzer.v3.model.ToneCategory docTC : to) {
                cat_id = docTC.getId();
                cs.setString(1, cat_id);

                try {
                    for (ToneScore s : docTC.getTones()) {
                        cs.setString(2, s.getId());
                        cs.setDouble(4, s.getScore());
                        cs.addBatch();
                    }
                    if (DEBUG) {
                        DbUtl.getWarningsFromStatement(cs);
                    }
                    //                        sentRes = cs.executeBatch();
                    //                        List l = Arrays.asList(sentRes);
                    //                        results.addAll(l);
                } catch (SQLException s) {
                    System.err.println(
                            "com.medlog.webservice.services.tone.ToneProcessorFactory.processTone(loop)"
                                    + DbUtl.printJDBCExceptionMsg(s));
                    s.printStackTrace();
                } catch (Exception s) {

                }

                System.out.println("com.medlog.webservice.util.ToneAnalyzerExample.processTone() result["
                        + sentT.getId() + "] " + ArrayUtils.toString(sentRes));
            }
        }
        sentRes = cs.executeBatch();
        try {
            l = Arrays.asList(sentRes);
            results.addAll(l);
            conn.setAutoCommit(true);
            cs.clearBatch();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                conn.setAutoCommit(true);
            } catch (Exception eeee) {
                eeee.printStackTrace();
            }
        }
    } catch (BatchUpdateException ex) {
        Logger.getLogger(ToneAnalyzerExample.class.getName()).log(Level.SEVERE, null, ex);
        System.err.println("com.medlog.webservice.services.tone.ToneProcessorFactory.processTone(batch)"
                + DbUtl.printBatchUpdateException(ex));

    } catch (SQLTimeoutException ex) {

        Logger.getLogger(ToneAnalyzerExample.class.getName()).log(Level.SEVERE, null, ex);
        ex.printStackTrace();
    } catch (SQLException ex) {
        Logger.getLogger(ToneAnalyzerExample.class.getName()).log(Level.SEVERE, null, ex);
        System.err.println("com.medlog.webservice.services.tone.ToneProcessorFactory.processTone(meth)"
                + DbUtl.printJDBCExceptionMsg(ex));

    } finally {
        DbUtl.close(cs);
    }
    return results;
}

From source file:de.whs.poodle.repositories.CourseRepository.java

public void edit(Course course) {
    try {/*from   ww  w  .  j av a2 s  . com*/
        jdbc.update(con -> {
            CallableStatement cs = con.prepareCall("{ CALL update_course(?,?,?,?,?,?) }");
            cs.setInt(1, course.getId());
            cs.setString(2, course.getName());
            cs.setBoolean(3, course.getVisible());
            if (course.getPassword().trim().isEmpty())
                cs.setNull(4, Types.VARCHAR);
            else
                cs.setString(4, course.getPassword());

            Array otherInstructors = con.createArrayOf("int4", course.getOtherInstructorsIds().toArray());
            cs.setArray(5, otherInstructors);
            Array linkedCourses = con.createArrayOf("int4", course.getLinkedCoursesIds().toArray());
            cs.setArray(6, linkedCourses);
            return cs;
        });
    } catch (DuplicateKeyException e) {
        throw new BadRequestException();
    }
}

From source file:es.indaba.jdbc.annotations.impl.GenericWork.java

@SuppressWarnings("unchecked")
@Override//from   w  w  w.  j a va 2s .  co m
public void execute(Connection con) throws SQLException {
    String procedureCall = procedure.value();
    FieldResult[] fields = proceduresResult == null ? new FieldResult[0] : proceduresResult.value();

    CallableStatement st = null;
    try {
        st = con.prepareCall(procedureCall);
        for (SQLParameter p : parameters) {
            int pos = p.getPosition();
            Object val = p.getValue();
            Class type = p.getType();
            Class sqlType = p.getSqlType();
            Integer jdbcType = SQLTypeMapping.getSqlTypeforClass(type);
            if (jdbcType != null) {
                if (val != null) {
                    SQLTypeMapping.setSqlParameter(st, type, sqlType, pos, val);
                } else {
                    st.setNull(pos, jdbcType);
                }
            }
        }
        for (FieldResult field : fields) {
            int position = field.position();
            Class type = field.sqlType();
            if (type == null || type.equals(Object.class)) {
                type = field.type();
            }
            Integer jdbcType = SQLTypeMapping.getSqlTypeforClass(type);
            if (position != FieldResult.RESULTSET) {
                st.registerOutParameter(position, jdbcType);
            }
        }
        st.execute();

        if (!returnType.equals(void.class)) {
            // Return instance
            resultObject = returnType.newInstance();
            ResultSet rs = st.getResultSet();
            for (FieldResult field : fields) {
                String property = field.name();
                Object result = null;
                if (field.position() == FieldResult.RESULTSET) {
                    rs.next();
                    result = SQLTypeMapping.getSqlResultsetResult(rs, field.type(), field.sqlType(), 1);
                } else {
                    result = SQLTypeMapping.getSqlResult(st, field.type(), field.sqlType(), field.position());
                }
                BeanUtils.setProperty(resultObject, property, result);
            }
        }
    } catch (Exception e) {
        logger.log(Level.SEVERE, e.getMessage(), e);
    }
}

From source file:com.netspective.axiom.sql.StoredProcedureParameter.java

/**
 * Apply the IN/OUT parameters of the callable statement object
 *
 * @param vac  the context in which the apply action is being performed
 * @param stmt the statement object//from  ww  w  .  j  a v  a2 s . c  o  m
 */
public void apply(StoredProcedureParameters.ValueApplyContext vac, ConnectionContext cc, CallableStatement stmt)
        throws SQLException {
    int paramNum = vac.getNextParamNum();
    int jdbcType = getSqlType().getJdbcType();
    if (getType().getValueIndex() == Type.IN || getType().getValueIndex() == Type.IN_OUT) {
        String text = value.getTextValue(cc);
        switch (jdbcType) {
        case Types.VARCHAR:
            // user override value if it exists
            if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                text = (String) vac.getActiveParamOverrideValue();
            if (allowNull && text == null)
                stmt.setNull(paramNum, Types.VARCHAR);
            else if (text != null)
                stmt.setObject(paramNum, text);
            else
                log.warn("Parameter '" + getName() + "' was not bound. Value = " + text);
            break;

        case Types.CHAR:
            if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                text = (String) vac.getActiveParamOverrideValue();
            if (allowNull && text == null)
                stmt.setNull(paramNum, Types.CHAR);
            else if (text != null)
                stmt.setObject(paramNum, text.substring(0, 1));
            else
                log.warn("Parameter '" + getName() + "' was not bound. Value = " + text);
            break;

        case Types.INTEGER:
            if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                text = vac.getActiveParamOverrideValue() != null ? vac.getActiveParamOverrideValue().toString()
                        : null;
            if (allowNull && text == null)
                stmt.setNull(paramNum, Types.INTEGER);
            else if (text != null)
                stmt.setInt(paramNum, Integer.parseInt(text));
            else
                log.warn("Parameter '" + getName() + "' was not bound. Value = " + text);

            break;

        case Types.DOUBLE:
            if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                text = vac.getActiveParamOverrideValue() != null ? vac.getActiveParamOverrideValue().toString()
                        : null;
            if (allowNull && text == null)
                stmt.setNull(paramNum, Types.DOUBLE);
            else if (text != null)
                stmt.setDouble(paramNum, Double.parseDouble(text));
            else
                log.warn("Parameter '" + getName() + "' was not bound. Value = " + text);

            break;

        case Types.ARRAY:
            // Arrays are quite tricky. Right now, this is supporting String arrays only.
            // TODO: Support integer and float arrays also
            String[] textValues = value.getTextValues(cc);
            if (vac.hasOverrideValues() && vac.hasActiveParamOverrideValue())
                textValues = (String[]) vac.getActiveParamOverrideValue();
            applyInArrayValue(cc, stmt, paramNum, textValues);
            break;
        default:
            log.warn("Unknown JDBC type for parameter '" + getName() + "' (index=" + paramNum
                    + ") of stored procedure '" + parent.getProcedure() + "'.");
            break;
        }
    }
    if (getType().getValueIndex() == Type.OUT || getType().getValueIndex() == Type.IN_OUT) {
        String identifier = getSqlType().getIdentifier();
        // result sets are returned differently for different vendors
        if (identifier.equals(QueryParameterType.RESULTSET_IDENTIFIER))
            stmt.registerOutParameter(paramNum, getVendorSpecificResultSetType(cc));
        else
            stmt.registerOutParameter(paramNum, jdbcType);
    }
}

From source file:it.greenvulcano.gvesb.datahandling.dbo.DBOCallSP.java

/**
 * @param cs//from   www . j a  v a  2  s  . c o  m
 * @throws SQLException
 */
private void setNull(CallableStatement cs, int type) throws SQLException {
    if (useName) {
        cs.setNull(currName, type);
    } else {
        cs.setNull(colIdx, type);
    }
}

From source file:net.sourceforge.msscodefactory.cfensyntax.v2_2.CFEnSyntaxOracle.CFEnSyntaxOracleEnObjectTable.java

public CFEnSyntaxEnObjectBuff readBuffByUNameIdx(CFEnSyntaxAuthorization Authorization, Long ScopeId,
        String Name) {/*from  w ww  .  j  a va2  s .co  m*/
    final String S_ProcName = "readBuffByUNameIdx";
    ResultSet resultSet = null;
    Connection cnx = schema.getCnx();
    CallableStatement stmtReadBuffByUNameIdx = null;
    try {
        stmtReadBuffByUNameIdx = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".rd_enobjbyunameidx( ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtReadBuffByUNameIdx.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtReadBuffByUNameIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByUNameIdx.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtReadBuffByUNameIdx.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtReadBuffByUNameIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByUNameIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        if (ScopeId != null) {
            stmtReadBuffByUNameIdx.setLong(argIdx++, ScopeId.longValue());
        } else {
            stmtReadBuffByUNameIdx.setNull(argIdx++, java.sql.Types.BIGINT);
        }
        stmtReadBuffByUNameIdx.setString(argIdx++, Name);
        stmtReadBuffByUNameIdx.execute();
        resultSet = (ResultSet) stmtReadBuffByUNameIdx.getObject(1);
        if (resultSet == null) {
            return (null);
        }
        try {
            if (resultSet.next()) {
                CFEnSyntaxEnObjectBuff buff = unpackEnObjectResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                return (buff);
            } else {
                return (null);
            }
        } catch (SQLException e) {
            return (null);
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtReadBuffByUNameIdx != null) {
            try {
                stmtReadBuffByUNameIdx.close();
            } catch (SQLException e) {
            }
            stmtReadBuffByUNameIdx = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfensyntax.v2_2.CFEnSyntaxOracle.CFEnSyntaxOracleEnHeadTable.java

public CFEnSyntaxEnHeadBuff readBuffByUNameIdx(CFEnSyntaxAuthorization Authorization, Long ScopeId,
        String Name) {//from   w ww.j av  a  2s.com
    final String S_ProcName = "readBuffByUNameIdx";
    ResultSet resultSet = null;
    Connection cnx = schema.getCnx();
    CallableStatement stmtReadBuffByUNameIdx = null;
    try {
        stmtReadBuffByUNameIdx = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".rd_enheadbyunameidx( ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtReadBuffByUNameIdx.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtReadBuffByUNameIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByUNameIdx.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtReadBuffByUNameIdx.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtReadBuffByUNameIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByUNameIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        if (ScopeId != null) {
            stmtReadBuffByUNameIdx.setLong(argIdx++, ScopeId.longValue());
        } else {
            stmtReadBuffByUNameIdx.setNull(argIdx++, java.sql.Types.BIGINT);
        }
        stmtReadBuffByUNameIdx.setString(argIdx++, Name);
        stmtReadBuffByUNameIdx.execute();
        resultSet = (ResultSet) stmtReadBuffByUNameIdx.getObject(1);
        if (resultSet == null) {
            return (null);
        }
        try {
            if (resultSet.next()) {
                CFEnSyntaxEnHeadBuff buff = unpackEnHeadResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                return (buff);
            } else {
                return (null);
            }
        } catch (SQLException e) {
            return (null);
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtReadBuffByUNameIdx != null) {
            try {
                stmtReadBuffByUNameIdx.close();
            } catch (SQLException e) {
            }
            stmtReadBuffByUNameIdx = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfensyntax.v2_2.CFEnSyntaxOracle.CFEnSyntaxOracleEnWordTable.java

public CFEnSyntaxEnWordBuff readBuffByUNameIdx(CFEnSyntaxAuthorization Authorization, Long ScopeId,
        String Name) {//from ww  w  .j  av  a2  s  . c om
    final String S_ProcName = "readBuffByUNameIdx";
    ResultSet resultSet = null;
    Connection cnx = schema.getCnx();
    CallableStatement stmtReadBuffByUNameIdx = null;
    try {
        stmtReadBuffByUNameIdx = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".rd_enwordbyunameidx( ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtReadBuffByUNameIdx.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtReadBuffByUNameIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByUNameIdx.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtReadBuffByUNameIdx.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtReadBuffByUNameIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtReadBuffByUNameIdx.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        if (ScopeId != null) {
            stmtReadBuffByUNameIdx.setLong(argIdx++, ScopeId.longValue());
        } else {
            stmtReadBuffByUNameIdx.setNull(argIdx++, java.sql.Types.BIGINT);
        }
        stmtReadBuffByUNameIdx.setString(argIdx++, Name);
        stmtReadBuffByUNameIdx.execute();
        resultSet = (ResultSet) stmtReadBuffByUNameIdx.getObject(1);
        if (resultSet == null) {
            return (null);
        }
        try {
            if (resultSet.next()) {
                CFEnSyntaxEnWordBuff buff = unpackEnWordResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                return (buff);
            } else {
                return (null);
            }
        } catch (SQLException e) {
            return (null);
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtReadBuffByUNameIdx != null) {
            try {
                stmtReadBuffByUNameIdx.close();
            } catch (SQLException e) {
            }
            stmtReadBuffByUNameIdx = null;
        }
    }
}