Example usage for java.sql PreparedStatement setNull

List of usage examples for java.sql PreparedStatement setNull

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

Introduction

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

Prototype

void setNull(int parameterIndex, int sqlType) throws SQLException;

Source Link

Document

Sets the designated parameter to SQL NULL.

Usage

From source file:Main.java

public static PreparedStatement createFieldsInsert(Connection conn, int layerId, String name,
        String description, String fieldId, String fieldType, String sid, String sname, String sdesc,
        boolean indb, boolean enabled, boolean namesearch, boolean defaultlayer, boolean intersect,
        boolean layerbranch, boolean analysis, boolean addToMap) throws SQLException {
    // TOOD slightly different statement if sdesc is null...

    PreparedStatement stFieldsInsert = conn.prepareStatement(
            "INSERT INTO fields (name, id, \"desc\", type, spid, sid, sname, sdesc, indb, enabled, last_update, namesearch, defaultlayer, \"intersect\", layerbranch, analysis, addtomap)"
                    + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
    stFieldsInsert.setString(1, name);//w w w.ja  va2 s .  co  m
    stFieldsInsert.setString(2, fieldId);
    stFieldsInsert.setString(3, description);
    stFieldsInsert.setString(4, fieldType);
    stFieldsInsert.setString(5, Integer.toString(layerId));
    stFieldsInsert.setString(6, sid);
    stFieldsInsert.setString(7, sname);

    if (sdesc == null || sdesc.isEmpty()) {
        stFieldsInsert.setNull(8, Types.VARCHAR);
    } else {
        stFieldsInsert.setString(8, sdesc);
    }

    stFieldsInsert.setBoolean(9, indb);
    stFieldsInsert.setBoolean(10, enabled);
    stFieldsInsert.setTimestamp(11, new Timestamp(System.currentTimeMillis()));
    stFieldsInsert.setBoolean(12, namesearch);
    stFieldsInsert.setBoolean(13, defaultlayer);
    stFieldsInsert.setBoolean(14, intersect);
    stFieldsInsert.setBoolean(15, layerbranch);
    stFieldsInsert.setBoolean(16, analysis);
    stFieldsInsert.setBoolean(17, addToMap);

    return stFieldsInsert;
}

From source file:com.example.querybuilder.server.Jdbc.java

public static void initializeParameters(PreparedStatement preparedStatement, Object... parameters) {
    for (int columnOffset = 0, columnNumber = 1; columnOffset < parameters.length; columnOffset++, columnNumber++) {
        Object value = parameters[columnOffset];
        try {//from  www  . jav a2s  .co  m
            if (value == null) {
                int parameterType = preparedStatement.getParameterMetaData().getParameterType(columnNumber);
                preparedStatement.setNull(columnNumber, parameterType);
            } else {
                preparedStatement.setObject(columnNumber, value);
            }
        } catch (SQLException e) {
            throw new SqlRuntimeException(
                    e.toString() + "\n" + "columnNumber=" + columnNumber + ", value=" + value);
        }
    }
}

From source file:mitll.xdata.dataset.kiva.ingest.KivaIngest.java

public static int executePreparedStatement(PreparedStatement statement, List<String> types, List<String> values)
        throws Exception {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss Z");
    try {/*from   w w  w.j  av  a 2  s  . c om*/
        for (int i = 0; i < types.size(); i++) {
            String type = TYPE_TO_DB.get(types.get(i).toUpperCase());
            String value = values.get(i);
            if (value != null && value.trim().length() == 0) {
                value = null;
            }
            if (type.equalsIgnoreCase("INT")) {
                if (value == null) {
                    statement.setNull(i + 1, java.sql.Types.INTEGER);
                } else {
                    statement.setInt(i + 1, Integer.parseInt(value, 10));
                }
            } else if (type.equalsIgnoreCase("DOUBLE")) {
                if (value == null) {
                    statement.setNull(i + 1, java.sql.Types.DOUBLE);
                } else {
                    statement.setDouble(i + 1, Double.parseDouble(value));
                }
            } else if (type.equalsIgnoreCase("BOOLEAN")) {
                if (value == null) {
                    statement.setNull(i + 1, java.sql.Types.BOOLEAN);
                } else {
                    statement.setBoolean(i + 1, Boolean.parseBoolean(value));
                }
            } else if (type.equalsIgnoreCase("VARCHAR")) {
                statement.setString(i + 1, value);
            } else if (type.equalsIgnoreCase("TIMESTAMP")) {
                if (value == null) {
                    statement.setNull(i + 1, java.sql.Types.TIMESTAMP);
                } else {
                    statement.setTimestamp(i + 1, new Timestamp(sdf.parse(value).getTime()));
                }
            }
        }
    } catch (Throwable e) {
        System.out.println("types = " + types);
        System.out.println("values = " + values);
        System.out.println("types.size() = " + types.size());
        System.out.println("values.size() = " + values.size());
        e.printStackTrace();
        System.out.println(e.getMessage());
        throw new Exception(e);
    }
    return statement.executeUpdate();
}

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

public static PreparedStatement prepareStatement(Connection connection, String sql, Object[] args) {
    PreparedStatement preparedStatement;
    if (connection != null && sql != null) {
        try {/*from  w  w w.j a va2 s. c o m*/
            preparedStatement = connection.prepareStatement(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);
                        preparedStatement.setNull(i + 1, java.sql.Types.NULL);
                    } else if (args[i] instanceof EnumTipoDatoSQL) {
                        EnumTipoDatoSQL tipoDatoSQL = (EnumTipoDatoSQL) args[i];
                        preparedStatement.setNull(i + 1, tipoDatoSQL.intValue());
                    } else {
                        preparedStatement.setObject(i + 1, args[i]);
                    }
                }
            }
            return preparedStatement;
        } catch (SQLException ex) {
            Bitacora.logFatal(ex);
        }
    }
    return null;
}

From source file:org.apache.phoenix.coprocessor.TaskRegionObserver.java

public static void addTask(PhoenixConnection conn, TaskType taskType, String tenantId, String schemaName,
        String tableName, boolean accessCheckEnabled) throws IOException {
    PreparedStatement stmt = null;
    try {/*from w  w  w. ja  v a  2  s.  c o m*/
        stmt = conn.prepareStatement("UPSERT INTO " + PhoenixDatabaseMetaData.SYSTEM_TASK_NAME + " ( "
                + PhoenixDatabaseMetaData.TASK_TYPE + ", " + PhoenixDatabaseMetaData.TENANT_ID + ", "
                + PhoenixDatabaseMetaData.TABLE_SCHEM + ", " + PhoenixDatabaseMetaData.TABLE_NAME
                + " ) VALUES(?,?,?,?)");
        stmt.setByte(1, taskType.getSerializedValue());
        if (tenantId != null) {
            stmt.setString(2, tenantId);
        } else {
            stmt.setNull(2, Types.VARCHAR);
        }
        if (schemaName != null) {
            stmt.setString(3, schemaName);
        } else {
            stmt.setNull(3, Types.VARCHAR);
        }
        stmt.setString(4, tableName);
    } catch (SQLException e) {
        throw new IOException(e);
    }
    mutateSystemTaskTable(conn, stmt, accessCheckEnabled);
}

From source file:com.act.lcms.db.model.Plate.java

protected static void bindInsertOrUpdateParameters(PreparedStatement stmt, String name, String description,
        String barcode, String location, String plateType, String solvent, Integer temperature,
        CONTENT_TYPE contentType) throws SQLException {
    stmt.setString(DB_FIELD.NAME.getInsertUpdateOffset(), trimAndComplain(name));
    if (description != null) {
        stmt.setString(DB_FIELD.DESCRIPTION.getInsertUpdateOffset(), trimAndComplain(description));
    } else {// w w  w.jav a 2 s  . c  o  m
        stmt.setNull(DB_FIELD.DESCRIPTION.getInsertUpdateOffset(), Types.VARCHAR);
    }
    if (barcode != null) {
        stmt.setString(DB_FIELD.BARCODE.getInsertUpdateOffset(), trimAndComplain(barcode));
    } else {
        stmt.setNull(DB_FIELD.BARCODE.getInsertUpdateOffset(), Types.VARCHAR);
    }
    stmt.setString(DB_FIELD.LOCATION.getInsertUpdateOffset(), trimAndComplain(location));
    stmt.setString(DB_FIELD.PLATE_TYPE.getInsertUpdateOffset(), trimAndComplain(plateType));
    if (solvent != null) {
        stmt.setString(DB_FIELD.SOLVENT.getInsertUpdateOffset(), trimAndComplain(solvent));
    } else {
        stmt.setNull(DB_FIELD.SOLVENT.getInsertUpdateOffset(), Types.VARCHAR);
    }
    if (temperature == null) {
        stmt.setNull(DB_FIELD.TEMPERATURE.getInsertUpdateOffset(), Types.INTEGER);
    } else {
        stmt.setInt(DB_FIELD.TEMPERATURE.getInsertUpdateOffset(), temperature);
    }
    stmt.setString(DB_FIELD.CONTENT_TYPE.getInsertUpdateOffset(), contentType.name());
}

From source file:com.act.lcms.db.model.CuratedChemical.java

protected static void bindInsertOrUpdateParameters(PreparedStatement stmt, String name, String inchi,
        Double mPlusHPlusMass, Integer expectedCollisionVoltage, String referenceUrl) throws SQLException {
    stmt.setString(DB_FIELD.NAME.getInsertUpdateOffset(), name);
    stmt.setString(DB_FIELD.INCHI.getInsertUpdateOffset(), inchi);
    stmt.setDouble(DB_FIELD.MASS.getInsertUpdateOffset(), mPlusHPlusMass);
    if (expectedCollisionVoltage != null) {
        stmt.setInt(DB_FIELD.EXPECTED_COLLISION_VOLTAGE.getInsertUpdateOffset(), expectedCollisionVoltage);
    } else {/*from ww  w.j av  a 2  s  .c om*/
        stmt.setNull(DB_FIELD.EXPECTED_COLLISION_VOLTAGE.getInsertUpdateOffset(), Types.INTEGER);
    }
    stmt.setString(DB_FIELD.REFERENCE_URL.getInsertUpdateOffset(), referenceUrl);
}

From source file:gridool.util.jdbc.JDBCUtils.java

/**
 * Fill the <code>PreparedStatement</code> replacement parameters with 
 * the given objects./*from   w w  w . j av a  2s.c o  m*/
 * 
 * @param params Query replacement parameters; <code>null</code> is a valid value to pass in.
 */
public static void fillStatement(PreparedStatement stmt, Object[] params) throws SQLException {
    if (params == null) {
        return;
    }
    for (int i = 0; i < params.length; i++) {
        if (params[i] != null) {
            stmt.setObject(i + 1, params[i]);
        } else {
            // VARCHAR works with many drivers regardless
            // of the actual column type.  Oddly, NULL and 
            // OTHER don't work with Oracle's drivers.
            stmt.setNull(i + 1, Types.VARCHAR);
        }
    }
}

From source file:org.waarp.common.database.data.AbstractDbData.java

/**
 * Set Value into PreparedStatement/*from   ww  w  .j  av a 2s  . co  m*/
 * 
 * @param ps
 * @param value
 * @param rank
 *            >= 1
 * @throws WaarpDatabaseSqlException
 */
static public void setTrueValue(PreparedStatement ps, DbValue value, int rank)
        throws WaarpDatabaseSqlException {
    try {
        switch (value.type) {
        case Types.VARCHAR:
            if (value.value == null) {
                ps.setNull(rank, Types.VARCHAR);
                break;
            }
            ps.setString(rank, (String) value.value);
            break;
        case Types.LONGVARCHAR:
            if (value.value == null) {
                ps.setNull(rank, Types.LONGVARCHAR);
                break;
            }
            ps.setString(rank, (String) value.value);
            break;
        case Types.BIT:
            if (value.value == null) {
                ps.setNull(rank, Types.BIT);
                break;
            }
            ps.setBoolean(rank, (Boolean) value.value);
            break;
        case Types.TINYINT:
            if (value.value == null) {
                ps.setNull(rank, Types.TINYINT);
                break;
            }
            ps.setByte(rank, (Byte) value.value);
            break;
        case Types.SMALLINT:
            if (value.value == null) {
                ps.setNull(rank, Types.SMALLINT);
                break;
            }
            ps.setShort(rank, (Short) value.value);
            break;
        case Types.INTEGER:
            if (value.value == null) {
                ps.setNull(rank, Types.INTEGER);
                break;
            }
            ps.setInt(rank, (Integer) value.value);
            break;
        case Types.BIGINT:
            if (value.value == null) {
                ps.setNull(rank, Types.BIGINT);
                break;
            }
            ps.setLong(rank, (Long) value.value);
            break;
        case Types.REAL:
            if (value.value == null) {
                ps.setNull(rank, Types.REAL);
                break;
            }
            ps.setFloat(rank, (Float) value.value);
            break;
        case Types.DOUBLE:
            if (value.value == null) {
                ps.setNull(rank, Types.DOUBLE);
                break;
            }
            ps.setDouble(rank, (Double) value.value);
            break;
        case Types.VARBINARY:
            if (value.value == null) {
                ps.setNull(rank, Types.VARBINARY);
                break;
            }
            ps.setBytes(rank, (byte[]) value.value);
            break;
        case Types.DATE:
            if (value.value == null) {
                ps.setNull(rank, Types.DATE);
                break;
            }
            ps.setDate(rank, (Date) value.value);
            break;
        case Types.TIMESTAMP:
            if (value.value == null) {
                ps.setNull(rank, Types.TIMESTAMP);
                break;
            }
            ps.setTimestamp(rank, (Timestamp) value.value);
            break;
        case Types.CLOB:
            if (value.value == null) {
                ps.setNull(rank, Types.CLOB);
                break;
            }
            ps.setClob(rank, (Reader) value.value);
            break;
        case Types.BLOB:
            if (value.value == null) {
                ps.setNull(rank, Types.BLOB);
                break;
            }
            ps.setBlob(rank, (InputStream) value.value);
            break;
        default:
            throw new WaarpDatabaseSqlException("Type not supported: " + value.type + " at " + rank);
        }
    } catch (ClassCastException e) {
        throw new WaarpDatabaseSqlException("Setting values casting error: " + value.type + " at " + rank, e);
    } catch (SQLException e) {
        DbSession.error(e);
        throw new WaarpDatabaseSqlException("Setting values in error: " + value.type + " at " + rank, e);
    }
}

From source file:org.athenasource.framework.unidbi.Datatypes.java

/**
 * Setting the parameter for the given prepared statement. This method will
 * cast the value to the object expected type (execept BOOLEAN) as
 * {@linkplain #getClass(int)}./* w  ww  .  ja  va 2  s .  c  o m*/
 * <p>INSERT, UPDATE should always call this method to set parameters before 'WHERE' keyword.</p>
 * <p><b>Warining</b>: For parameters after 'WHERE' keyword, always remember 'IS NULL' is not equal to '= NULL'.</p>
 *
 * @param index
 *            the parameter index
 * @param value
 *            the value for the parameter, can be <code>null</code>.
 * @param unidbType
 *            the datatype of the parameter
 * @throws SQLException
 *             in case of SQL problems or type conversion fails.
 */
public static void setParameter(PreparedStatement stmt, int index, Object value, int unidbType)
        throws SQLException {
    // Derby needs special handling.
    boolean isDerby = (stmt instanceof DelegatingPreparedStatement)
            ? ((DelegatingPreparedStatement) stmt).getDelegate().getClass().getName().contains("derby")
            : stmt.getClass().getName().contains("derby");
    if (value == null) {
        if (isDerby) {
            if (unidbType == NCHAR) {
                stmt.setNull(index, Datatypes.getSQLType(CHAR));
            } else if (unidbType == NVARCHAR) {
                stmt.setNull(index, Datatypes.getSQLType(VARCHAR));
            } else {
                stmt.setNull(index, Datatypes.getSQLType(unidbType));
            }
        } else {
            stmt.setNull(index, Datatypes.getSQLType(unidbType));
        }
    } else {
        try {
            switch (unidbType) {
            case BOOLEAN:
                stmt.setByte(index, (byte) (((Number) value).intValue() == 1 ? 1 : 0));
                break;
            case TINYINT:
                stmt.setByte(index, ((Number) value).byteValue());
                break;
            case SMALLINT:
                stmt.setShort(index, ((Number) value).shortValue());
                break;
            case INTEGER:
                stmt.setInt(index, ((Number) value).intValue());
                break;
            case BIGINT:
                stmt.setLong(index, ((Number) value).longValue());
                break;
            case DECIMAL:
                stmt.setBigDecimal(index, ((BigDecimal) value));
                break;
            case REAL:
                stmt.setFloat(index, ((Float) value).floatValue());
                break;
            case DOUBLE:
                stmt.setDouble(index, ((Double) value).doubleValue());
                break;
            case CHAR:
                stmt.setString(index, (String) value);
                break;
            case NCHAR:
                if (isDerby) {
                    stmt.setString(index, (String) value);
                } else {
                    stmt.setNString(index, (String) value);
                }
                break;
            case VARCHAR:
                stmt.setString(index, (String) value);
                break;
            case NVARCHAR:
                if (isDerby) {
                    stmt.setString(index, (String) value);
                } else {
                    stmt.setNString(index, (String) value);
                }
                break;
            case CLOB: // Clob/NClob can be represented as String without any problem. - Oct 16, 2008.
                stmt.setString(index, (String) value); // stmt.setClob(index, ((Clob) value));
                break;
            case NCLOB:
                if (isDerby) {
                    stmt.setString(index, (String) value);
                } else {
                    stmt.setNString(index, (String) value); // stmt.setNClob(index, ((NClob) value));
                }
                break;
            case BLOB:
                stmt.setBlob(index, ((Blob) value));
                break;
            case TIMESTAMP:
                stmt.setTimestamp(index, ((Timestamp) value));
                break;
            default:
                throw new IllegalArgumentException("[!NO SUCH UNIDB DATA TYPE: " + unidbType + "]");
            }
        } catch (ClassCastException cce) {
            throw new SQLException(
                    "Failed to convert " + value + " (" + value.getClass() + ") to " + getName(unidbType));
        }
    }
}