Java Code Examples for java.sql.Types#BLOB

/**
 * Validate the parameters passed to an execute method based on declared parameters.
 * Subclasses should invoke this method before every {@code executeQuery()}
 * or {@code update()} method.
 * @param parameters parameters supplied (may be {@code null})
 * @throws InvalidDataAccessApiUsageException if the parameters are invalid
 */
protected void validateParameters(@Nullable Object[] parameters) throws InvalidDataAccessApiUsageException {
	checkCompiled();
	int declaredInParameters = 0;
	for (SqlParameter param : this.declaredParameters) {
		if (param.isInputValueProvided()) {
			if (!supportsLobParameters() &&
					(param.getSqlType() == Types.BLOB || param.getSqlType() == Types.CLOB)) {
				throw new InvalidDataAccessApiUsageException(
						"BLOB or CLOB parameters are not allowed for this kind of operation");
			}
			declaredInParameters++;
		}
	}
	validateParameterCount((parameters != null ? parameters.length : 0), declaredInParameters);
}
 

/**
 * Convert metadata type to java.sql.Types (used on client side, JDBC driver).
 * @param type
 * @return the mapped value
 */
public static int sqlDataTypeToJdbcType(String type) {
    switch (type) {
        case "string":
            return Types.VARCHAR;
        case "long":
            return Types.BIGINT;
        case "integer":
            return Types.INTEGER;
        case "bytearray":
            return Types.BLOB;
        case "timestamp":
            return Types.TIMESTAMP;
        case "null":
            return Types.NULL;
        case "double":
            return Types.DOUBLE;
        case "boolean":
            return Types.BOOLEAN;
        default:
            return Types.OTHER;
    }
}
 

public static int tajoTypeToSqlType(DataType type) throws SQLException {
  switch (type.getType()) {
  case BIT:
    return Types.BIT;
  case BOOLEAN:
    return Types.BOOLEAN;
  case INT1:
    return Types.TINYINT;
  case INT2:
    return Types.SMALLINT;
  case INT4:
    return Types.INTEGER;
  case INT8:
    return Types.BIGINT;
  case FLOAT4:
    return Types.FLOAT;
  case FLOAT8:
    return Types.DOUBLE;
  case NUMERIC:
    return Types.NUMERIC;
  case DATE:
    return Types.DATE;
  case TIMESTAMP:
    return Types.TIMESTAMP;
  case TIME:
    return Types.TIME;
  case CHAR:
  case VARCHAR:
  case TEXT:
    return Types.VARCHAR;
  case BLOB:
    return Types.BLOB;
  case RECORD:
    return Types.STRUCT;
  case NULL_TYPE:
    return Types.NULL;
  default:
    throw new SQLException("Unrecognized column type: " + type);
  }
}
 

@Override
public DataType getDataType(int jdbcType, String name, int size) {
	// Doesn't support DISTINCT over LOB types
	if (jdbcType == Types.CLOB || "NCLOB".equals(name)) {
		return new SQLCharacterStringVarying(name, false);
	}
	if (jdbcType == Types.BLOB) {
		return new SQLBinary(name, false);
	}
	
	// HSQLDB 2.2.8 reports INTERVAL types as VARCHAR 
	if (jdbcType == Types.VARCHAR && name.startsWith("INTERVAL")) {
		return new SQLInterval(name);
	}
	
	// HSQLDB supports NaN and +/-INF in DOUBLE
	if (jdbcType == Types.DOUBLE || jdbcType == Types.FLOAT || jdbcType == Types.REAL) {
		return new HSQLDBCompatibilityDoubleDataType();
	}
	
   	// OTHER in HSQLDB 2.2.8 is really JAVA_OBJECT
	if (jdbcType == Types.OTHER) {
		return new UnsupportedDataType(jdbcType, name);
	}

	return super.getDataType(jdbcType, name, size);
}
 

@DataProvider(name = "columnClassNames")
private Object[][] columnClassNames() {
    return new Object[][]{
        {Types.CHAR, "java.lang.String"},
        {Types.NCHAR, "java.lang.String"},
        {Types.VARCHAR, "java.lang.String"},
        {Types.NVARCHAR, "java.lang.String"},
        {Types.LONGVARCHAR, "java.lang.String"},
        {Types.LONGNVARCHAR, "java.lang.String"},
        {Types.NUMERIC, "java.math.BigDecimal"},
        {Types.DECIMAL, "java.math.BigDecimal"},
        {Types.BIT, "java.lang.Boolean"},
        {Types.TINYINT, "java.lang.Byte"},
        {Types.SMALLINT, "java.lang.Short"},
        {Types.INTEGER, "java.lang.Integer"},
        {Types.FLOAT, "java.lang.Double"},
        {Types.DOUBLE, "java.lang.Double"},
        {Types.BINARY, "byte[]"},
        {Types.VARBINARY, "byte[]"},
        {Types.LONGVARBINARY, "byte[]"},
        {Types.DATE, "java.sql.Date"},
        {Types.TIME, "java.sql.Time"},
        {Types.TIMESTAMP, "java.sql.Timestamp"},
        {Types.CLOB, "java.sql.Clob"},
        {Types.BLOB, "java.sql.Blob"}

    };

}
 

/**
 * {@inheritDoc}
 */
@Override
public InputStream getAsciiStream(int columnIndex) throws SQLException {
  if (columnIndex > 0 && columnIndex <= this.numColumns) {
    final TableMetaData metadata = getMetaData();
    final int colType = metadata.getColumnType(columnIndex);
    switch (colType) {
      case Types.CHAR:
      case Types.VARCHAR:
      case Types.LONGVARCHAR:
      case Types.CLOB: // Embedded and JCC extension
        break;

      // JDBC says to support these, we match JCC by returning the raw bytes.
      case Types.BINARY:
      case Types.VARBINARY:
      case Types.LONGVARBINARY:
      case Types.BLOB:
        return getBinaryStream(columnIndex);

      default:
        throw dataConversionException(
            metadata.getColumnTypeName(columnIndex), "InputStream(ASCII)",
            columnIndex);
    }

    final Reader reader = getCharacterStream(columnIndex);
    if (reader != null) {
      return new ReaderToAscii(reader);
    }
    else {
      return null;
    }
  }
  else {
    throw invalidColumnException(columnIndex);
  }
}
 

/**
 * Get the value of a column in current row in ResultSet as a string.
 * 
 * For binary data this returns hex-encoded string of the bytes.
 * 
 * For a java object this returns hex-encoded string of the serialized bytes
 * for the object.
 */
public static final String getColumnAsString(ResultSet rs, int columnIndex,
    int jdbcType) throws SQLException {
  byte[] bytes;
  switch (jdbcType) {
    case Types.BINARY:
    case Types.BLOB:
    case Types.LONGVARBINARY:
    case Types.VARBINARY:
      // convert to hex for binary columns
      bytes = rs.getBytes(columnIndex);
      if (bytes != null) {
        return toHexString(bytes, 0, bytes.length);
      }
      else {
        return null;
      }
    case Types.JAVA_OBJECT:
      // for java object type, serialize and then convert to hex
      Object v = rs.getObject(columnIndex);
      if (v != null) {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        DataOutputStream dos = new DataOutputStream(bos);
        try {
          DataSerializer.writeObject(v, dos);
          dos.flush();
        } catch (IOException ioe) {
          // not expected to happen
          throw new SQLException(ioe.getMessage(), "XJ001", 0, ioe);
        }
        bytes = bos.toByteArray();
        return toHexString(bytes, 0, bytes.length);
      }
      else {
        return null;
      }
    default:
      return rs.getString(columnIndex);
  }
}
 

/**
 * {@inheritDoc}
 */
@Override
public InputStream getAsciiStream(int columnIndex) throws SQLException {
  if (columnIndex > 0 && columnIndex <= this.numColumns) {
    final TableMetaData metadata = getMetaData();
    final int colType = metadata.getColumnType(columnIndex);
    switch (colType) {
      case Types.CHAR:
      case Types.VARCHAR:
      case Types.LONGVARCHAR:
      case Types.CLOB: // Embedded and JCC extension
        break;

      // JDBC says to support these, we match JCC by returning the raw bytes.
      case Types.BINARY:
      case Types.VARBINARY:
      case Types.LONGVARBINARY:
      case Types.BLOB:
        return getBinaryStream(columnIndex);

      default:
        throw dataConversionException(
            metadata.getColumnTypeName(columnIndex), "InputStream(ASCII)",
            columnIndex);
    }

    final Reader reader = getCharacterStream(columnIndex);
    if (reader != null) {
      return new ReaderToAscii(reader);
    }
    else {
      return null;
    }
  }
  else {
    throw invalidColumnException(columnIndex);
  }
}
 

@DataProvider(name = "columnClassNames")
private Object[][] columnClassNames() {
    return new Object[][]{
        {Types.CHAR, "java.lang.String"},
        {Types.NCHAR, "java.lang.String"},
        {Types.VARCHAR, "java.lang.String"},
        {Types.NVARCHAR, "java.lang.String"},
        {Types.LONGVARCHAR, "java.lang.String"},
        {Types.LONGNVARCHAR, "java.lang.String"},
        {Types.NUMERIC, "java.math.BigDecimal"},
        {Types.DECIMAL, "java.math.BigDecimal"},
        {Types.BIT, "java.lang.Boolean"},
        {Types.TINYINT, "java.lang.Byte"},
        {Types.SMALLINT, "java.lang.Short"},
        {Types.INTEGER, "java.lang.Integer"},
        {Types.FLOAT, "java.lang.Double"},
        {Types.DOUBLE, "java.lang.Double"},
        {Types.BINARY, "byte[]"},
        {Types.VARBINARY, "byte[]"},
        {Types.LONGVARBINARY, "byte[]"},
        {Types.DATE, "java.sql.Date"},
        {Types.TIME, "java.sql.Time"},
        {Types.TIMESTAMP, "java.sql.Timestamp"},
        {Types.CLOB, "java.sql.Clob"},
        {Types.BLOB, "java.sql.Blob"}

    };

}
 

@Override
public DataType getDataType(int jdbcType, String name, int size) {
	
	// Doesn't support DISTINCT over LOB types
	if (jdbcType == Types.CLOB || "NCLOB".equals(name)) {
		return new SQLCharacterStringVarying(name, false);
	}
	if (jdbcType == Types.BLOB) {
		return new SQLBinary(name, false);
	}
	
	DataType standard = super.getDataType(jdbcType, name, size);
	if (standard != null) return standard;

	// Special handling for TIMESTAMP(x) WITH LOCAL TIME ZONE
	if (name.contains("WITH LOCAL TIME ZONE") || "TIMESTAMPLTZ".equals(name)) {
		return new OracleCompatibilityTimeZoneLocalDataType(name);
	}
	
	// Special handling for TIMESTAMP(x) WITH TIME ZONE
	if(name.contains("WITH TIME ZONE") || "TIMESTAMPTZ".equals(name)) {
		return new OracleCompatibilityTimeZoneDataType(name);
	}
	
	// Oracle-specific character string types
	if ("VARCHAR2".equals(name) || "NVARCHAR2".equals(name)) {
		return new SQLCharacterStringVarying(name, true);
	}

	// Oracle-specific floating point types
   	if ("BINARY_FLOAT".equals(name) || "BINARY_DOUBLE".equals(name)) {
   		return new SQLApproximateNumeric(name);
   	}
   	
	// Oracle binary file pointer
	// TODO: We could at least support reading from BFILE, although querying for them seems hard
   	if ("BFILE".equals(name)) {
   		return new UnsupportedDataType(jdbcType, name);
   	}

   	return null;
}
 

public int[] sqlTypes(Mapping mapping) throws MappingException {
	return new int[] { Types.BLOB };
}
 

/**
 * Set the value of a parameter in prepared statement given a string
 * representation as returned by
 */
public static void setParameters(PreparedStatement pstmt, String[] params,
    ArrayList<Integer> paramTypes) throws Exception {
  for (int index = 0; index < params.length; index++) {
    String param = params[index];
    int paramIndex = index + 1;
    int paramType = paramTypes.get(index);
    byte[] bytes;

    if (param == null) {
      pstmt.setNull(paramIndex, paramType);
      continue;
    }
    switch (paramType) {
      case Types.BIGINT:
        final long longVal = Long.parseLong(param);
        pstmt.setLong(paramIndex, longVal);
        break;
      case Types.BIT:
      case Types.BOOLEAN:
        final boolean boolVal;
        if ("1".equals(param)) {
          boolVal = true;
        }
        else if ("0".equals(param)) {
          boolVal = false;
        }
        else {
          boolVal = Boolean.parseBoolean(param);
        }
        pstmt.setBoolean(paramIndex, boolVal);
        break;
      case Types.DATE:
        final java.sql.Date dateVal = java.sql.Date.valueOf(param);
        pstmt.setDate(paramIndex, dateVal);
        break;
      case Types.DECIMAL:
      case Types.NUMERIC:
        final BigDecimal decimalVal = new BigDecimal(param);
        pstmt.setBigDecimal(paramIndex, decimalVal);
        break;
      case Types.DOUBLE:
        final double doubleVal = Double.parseDouble(param);
        pstmt.setDouble(paramIndex, doubleVal);
        break;
      case Types.FLOAT:
      case Types.REAL:
        final float floatVal = Float.parseFloat(param);
        pstmt.setFloat(paramIndex, floatVal);
        break;
      case Types.INTEGER:
      case Types.SMALLINT:
      case Types.TINYINT:
        final int intVal = Integer.parseInt(param);
        pstmt.setInt(paramIndex, intVal);
        break;
      case Types.TIME:
        final java.sql.Time timeVal = java.sql.Time.valueOf(param);
        pstmt.setTime(paramIndex, timeVal);
        break;
      case Types.TIMESTAMP:
        final java.sql.Timestamp timestampVal = java.sql.Timestamp
            .valueOf(param);
        pstmt.setTimestamp(paramIndex, timestampVal);
        break;
      case Types.BINARY:
      case Types.BLOB:
      case Types.LONGVARBINARY:
      case Types.VARBINARY:
        bytes = ClientSharedUtils.fromHexString(param, 0, param.length());
        pstmt.setBytes(paramIndex, bytes);
        break;
      case Types.JAVA_OBJECT:
        bytes = ClientSharedUtils.fromHexString(param, 0, param.length());
        ByteArrayDataInput in = new ByteArrayDataInput();
        in.initialize(bytes, null);
        pstmt.setObject(paramIndex, DataSerializer.readObject(in));
        break;
      default:
        pstmt.setString(paramIndex, param);
        break;
    }
  }
}
 

@Override
public int getSqlType() {
	return Types.BLOB;
}
 

public boolean verifyColumn(ColumnInfo info)
{
    if (info.isNullable() != this.isNullable()) return false;
    return info.getType() == Types.VARBINARY || info.getType() == Types.LONGVARBINARY || info.getType() == Types.BLOB;
}
 

/**
 * Test updates of the specified types in the action statement.
 * @param type
 * @throws SQLException
 * @throws IOException
 */
private void actionTypesUpdateTest(String type)
    throws SQLException, IOException
{
    int jdbcType = DatabaseMetaDataTest.getJDBCType(type);
    int precision = DatabaseMetaDataTest.getPrecision(jdbcType, type);

    // BUG DERBY-2349 - need insert case to work first
    if (jdbcType == Types.BLOB)
        return;
    
    Statement s = createStatement();
    s.executeUpdate("UPDATE T_MAIN SET V = NULL WHERE ID = 2");
    s.close();
    commit();
    actionTypesCompareMainToActionForUpdate(type, 2);

    Random r = new Random();
    
    PreparedStatement ps = prepareStatement(
        (jdbcType == JDBC.SQLXML
            ? "UPDATE T_MAIN SET V = " +
              "XMLPARSE(DOCUMENT CAST (? AS CLOB) PRESERVE WHITESPACE)"
            : "UPDATE T_MAIN SET V = ?")
        + " WHERE ID >= ? AND ID <= ?");
    
    // Single row update of row 3
    setRandomValue(r, ps, 1, jdbcType, precision);
    ps.setInt(2, 3);
    ps.setInt(3, 3);
    assertUpdateCount(ps, 1);
    commit();
    actionTypesCompareMainToActionForUpdate(type, 3);
    
    // Bug DERBY-2358 - skip multi-row updates for streaming input.
    switch (jdbcType) {
    case Types.BLOB:
    case Types.CLOB:
    case Types.LONGVARBINARY:
    case Types.LONGVARCHAR:
        ps.close();
        return;
    }
    
    // multi-row update of 4,5
    setRandomValue(r, ps, 1, jdbcType, precision);
    ps.setInt(2, 4);
    ps.setInt(3, 5);
    assertUpdateCount(ps, 2);
    commit();
    actionTypesCompareMainToActionForUpdate(type, 4);
    actionTypesCompareMainToActionForUpdate(type, 5);

    ps.close();
    
}
 

public DataType getDataType(int jdbcType, String name, int size) {
	// TODO: These are in java.sql.Types as of Java 6 but not yet in Java 1.5
	if ("NCHAR".equals(name) || "NVARCHAR".equals(name) || "NCLOB".equals(name)) {
		return new SQLCharacterStringVarying(name, true);
	}

	
	switch (jdbcType) {
	case Types.CHAR:
		return new SQLCharacterString(name, true);

	case Types.VARCHAR:
	case Types.LONGVARCHAR:
	case Types.CLOB:
		return new SQLCharacterStringVarying(name, true);
		
	case Types.BOOLEAN:
		return new SQLBoolean(name);

	case Types.BINARY:
	case Types.VARBINARY:
	case Types.LONGVARBINARY:
	case Types.BLOB:
		return new SQLBinary(name, true);
		
	case Types.BIT:
		return new SQLBit(name);

	case Types.NUMERIC:
	case Types.DECIMAL:
	case Types.TINYINT:
	case Types.SMALLINT:
	case Types.INTEGER:
	case Types.BIGINT:
		return new SQLExactNumeric(name, jdbcType, false);
		
	case Types.REAL:
	case Types.FLOAT:
	case Types.DOUBLE:
		return new SQLApproximateNumeric(name);
	
	case Types.DATE:
		return new SQLDate(name);
		
	case Types.TIME:
		return new SQLTime(name);
		
	case Types.TIMESTAMP:
		return new SQLTimestamp(name);

	case Types.ARRAY:
	case Types.JAVA_OBJECT:
		return new UnsupportedDataType(jdbcType, name);
		
	// TODO: What about the remaining java.sql.Types?
	case Types.DATALINK:
	case Types.DISTINCT:
	case Types.NULL:
	case Types.OTHER:
	case Types.REF:
	}
	
	return null;
}
 

/**
 * @see org.hibernate.usertype.UserType#sqlTypes()
 */
public int[] sqlTypes() {
    return new int[] { Types.BLOB };
}
 

/**
 * Converts an ODA data type code to the Java class
 * of its corresponding Data Engine ODI data type. <br><br>
 * <b>ODA Data Type -> ODI Type Class</b><br>
 * <i>Integer -> java.lang.Integer<br>
 * Double -> java.lang.Double<br>
 * Character -> java.lang.String<br>
 * Decimal -> java.math.BigDecimal<br>
 * Date -> java.sql.Date<br>
 * Time -> java.sql.Time<br>
 * Timestamp -> java.sql.Timestamp<br>
 * Blob -> java.sql.Blob<br>
 * Clob -> java.sql.Clob<br>
    * Boolean -> java.lang.Boolean<br>
    * JavaObject -> java.lang.Object<br>
    * </i>
 * @param odaDataTypeCode   an ODA data type code
 * @return  the ODI type class that corresponds with 
 *          the specified ODA data type
 * @throws BirtException if the specified ODA data type is not a supported type
 */
public static Class toOdiTypeClass( int odaDataTypeCode )
		throws BirtException
{
	if ( odaDataTypeCode != Types.CHAR
			&& odaDataTypeCode != Types.INTEGER
			&& odaDataTypeCode != Types.DOUBLE
			&& odaDataTypeCode != Types.DECIMAL
			&& odaDataTypeCode != Types.DATE
			&& odaDataTypeCode != Types.TIME
			&& odaDataTypeCode != Types.TIMESTAMP
			&& odaDataTypeCode != Types.BLOB
			&& odaDataTypeCode != Types.CLOB
               && odaDataTypeCode != Types.BOOLEAN
               && odaDataTypeCode != Types.JAVA_OBJECT
			&& odaDataTypeCode != Types.NULL )
	{
		throw new CoreException( ResourceConstants.INVALID_TYPE);
	}

	Class fieldClass = null;
	switch ( odaDataTypeCode )
	{
		case Types.CHAR :
			fieldClass = String.class;
			break;

		case Types.INTEGER :
			fieldClass = Integer.class;
			break;

		case Types.DOUBLE :
			fieldClass = Double.class;
			break;

		case Types.DECIMAL :
			fieldClass = BigDecimal.class;
			break;

		case Types.DATE :
			fieldClass = java.sql.Date.class;
			break;

		case Types.TIME :
			fieldClass = Time.class;
			break;

		case Types.TIMESTAMP :
			fieldClass = Timestamp.class;
			break;

		case Types.BLOB :
			fieldClass = Blob.class;
			break;

		case Types.CLOB :
			fieldClass = Clob.class;
			break;

           case Types.BOOLEAN :
               fieldClass = Boolean.class;
               break;

           case Types.JAVA_OBJECT :
               fieldClass = Object.class;
               break;

		case Types.NULL :
			fieldClass = null;
			break;
	}

	return fieldClass;
}
 

/**
    * Returns true if the column is a binary type
    *
    * @param columnType
    *            the sql column type
    * @param columnName
    *            the sql column name
    * @param columnTable
    *            the table name of the column
    * @return true if it's a binary type
    */
   private static boolean isBinaryColumn(int columnType) {
if (columnType == Types.BINARY || columnType == Types.VARBINARY
	|| columnType == Types.LONGVARBINARY
	|| columnType == Types.BLOB) {
    return true;
} else {
    return false;
}
   }
 

/**
 *
 *
 * @return
 */
@Override
public int getSqlType() {
	return Types.BLOB;
}