org.springframework.jdbc.support.lob.LobCreator Java Examples

@Override
public final Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
	LobCreator lobCreator = this.lobHandler.getLobCreator();
	try {
		setValues(ps, lobCreator);
		return ps.executeUpdate();
	}
	finally {
		lobCreator.close();
	}
}
 

default void upload(UploadBase64FileModification file, String digest, Map<String, String> attributes) {
    LobHandler lobHandler = new DefaultLobHandler();

    NamedParameterJdbcTemplate jdbc = getNamedParameterJdbcTemplate();

    jdbc.getJdbcOperations().execute("insert into file_blob (id, name, content_size, content, content_type, attributes) values(?, ?, ?, ?, ?, ?)",
        new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
            @Override
            protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
                ps.setString(1, digest);
                ps.setString(2, file.getName());
                ps.setLong(3, file.getFile().length);
                lobCreator.setBlobAsBytes(ps, 4, file.getFile());
                ps.setString(5, file.getType());
                ps.setString(6, Json.GSON.toJson(attributes));
            }
        });
}
 

@Override
public void saveImage(Long bookId, File image) {

    try (InputStream imageInStream = new FileInputStream(image)) {

        jdbcTemplate.execute(
                "INSERT INTO book_image (book_id, filename, blob_image) VALUES (?, ?, ?)",
                new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
                    protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
                        ps.setLong(1, 1L);
                        ps.setString(2, image.getName());
                        lobCreator.setBlobAsBinaryStream(ps, 3, imageInStream, (int) image.length());
                    }
                }
        );

    } catch (IOException e) {
        e.printStackTrace();
    }

}
 

/**
 * This implementation delegates to nullSafeSetInternal,
 * passing in a transaction-synchronized LobCreator for the
 * LobHandler of this type.
 * @see #nullSafeSetInternal
 */
@Override
@Deprecated
public final void nullSafeSet(PreparedStatement st, Object value, int index)
		throws HibernateException, SQLException {

	if (this.lobHandler == null) {
		throw new IllegalStateException("No LobHandler found for configuration - " +
			"lobHandler property must be set on LocalSessionFactoryBean");
	}

	LobCreator lobCreator = this.lobHandler.getLobCreator();
	try {
		nullSafeSetInternal(st, index, value, lobCreator);
	}
	catch (IOException ex) {
		throw new HibernateException("I/O errors during LOB access", ex);
	}
	LobCreatorUtils.registerTransactionSynchronization(lobCreator, this.jtaTransactionManager);
}
 

@Override
protected void nullSafeSetInternal(
		PreparedStatement ps, int index, Object value, LobCreator lobCreator)
		throws SQLException, IOException {

	if (value != null) {
		ByteArrayOutputStream baos = new ByteArrayOutputStream(OUTPUT_BYTE_ARRAY_INITIAL_SIZE);
		ObjectOutputStream oos = new ObjectOutputStream(baos);
		try {
			oos.writeObject(value);
			oos.flush();
			lobCreator.setBlobAsBytes(ps, index, baos.toByteArray());
		}
		finally {
			oos.close();
		}
	}
	else {
		lobCreator.setBlobAsBytes(ps, index, null);
	}
}
 

public void updateProcess(final Process process) {
	super.updateProcess(process);
	if(process.getBytes() != null) {
		template.execute(PROCESS_UPDATE_BLOB, new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
			
			protected void setValues(PreparedStatement ps, LobCreator lobCreator)
					throws SQLException, DataAccessException {
				try {
					lobCreator.setBlobAsBytes(ps, 1, process.getBytes());
					StatementCreatorUtils.setParameterValue(ps, 2, Types.VARCHAR, process.getId());
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}
}
 

public void saveProcess(final Process process) {
	super.saveProcess(process);
	if(process.getBytes() != null) {
		template.execute(PROCESS_UPDATE_BLOB, new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
			
			protected void setValues(PreparedStatement ps, LobCreator lobCreator)
					throws SQLException, DataAccessException {
				try {
					lobCreator.setBlobAsBytes(ps, 1, process.getBytes());
					StatementCreatorUtils.setParameterValue(ps, 2, Types.VARCHAR, process.getId());
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}
}
 

@Transactional
public void storeImage(
    final String name, final InputStream contentStream, final int contentLength, final String description)
    throws DataAccessException {

	getJdbcTemplate().execute(
			"INSERT INTO imagedb (image_name, content, description) VALUES (?, ?, ?)",
			new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
				protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
					ps.setString(1, name);
					lobCreator.setBlobAsBinaryStream(ps, 2, contentStream, contentLength);
					lobCreator.setClobAsString(ps, 3, description);
				}
			}
	);
}
 

@Override
protected void nullSafeSetInternal(
		PreparedStatement ps, int index, Object value, LobCreator lobCreator)
		throws SQLException, IOException {

	if (value != null) {
		ByteArrayOutputStream baos = new ByteArrayOutputStream(OUTPUT_BYTE_ARRAY_INITIAL_SIZE);
		ObjectOutputStream oos = new ObjectOutputStream(baos);
		try {
			oos.writeObject(value);
			oos.flush();
			lobCreator.setBlobAsBytes(ps, index, baos.toByteArray());
		}
		finally {
			oos.close();
		}
	}
	else {
		lobCreator.setBlobAsBytes(ps, index, null);
	}
}
 

/**
 * This implementation delegates to nullSafeSetInternal,
 * passing in a transaction-synchronized LobCreator for the
 * LobHandler of this type.
 * @see #nullSafeSetInternal
 */
@Override
@Deprecated
public final void nullSafeSet(PreparedStatement st, Object value, int index)
		throws HibernateException, SQLException {

	if (this.lobHandler == null) {
		throw new IllegalStateException("No LobHandler found for configuration - " +
			"lobHandler property must be set on LocalSessionFactoryBean");
	}

	LobCreator lobCreator = this.lobHandler.getLobCreator();
	try {
		nullSafeSetInternal(st, index, value, lobCreator);
	}
	catch (IOException ex) {
		throw new HibernateException("I/O errors during LOB access", ex);
	}
	LobCreatorUtils.registerTransactionSynchronization(lobCreator, this.jtaTransactionManager);
}
 

@Before
public void setUp() {
	MockitoAnnotations.initMocks(this);
	preparedStatement = mock(PreparedStatement.class);
	handler = mock(LobHandler.class);
	creator = mock(LobCreator.class);
	given(handler.getLobCreator()).willReturn(creator);
}
 

@Override
protected void nullSafeSetInternal(
		PreparedStatement ps, int index, Object value, LobCreator lobCreator)
		throws SQLException {

	lobCreator.setBlobAsBytes(ps, index, (byte[]) value);
}
 

@Override
protected void nullSafeSetInternal(
		PreparedStatement ps, int index, Object value, LobCreator lobCreator)
		throws SQLException {

	lobCreator.setClobAsString(ps, index, (String) value);
}
 

@Override
protected void nullSafeSetInternal(
		PreparedStatement ps, int index, Object value, LobCreator lobCreator)
		throws SQLException, UnsupportedEncodingException {

	if (value != null) {
		String str = (String) value;
		String encoding = getCharacterEncoding();
		byte[] bytes = (encoding != null ? str.getBytes(encoding) : str.getBytes());
		lobCreator.setBlobAsBytes(ps, index, bytes);
	}
	else {
		lobCreator.setBlobAsBytes(ps, index, null);
	}
}
 

@Override
public final Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
	LobCreator lobCreator = this.lobHandler.getLobCreator();
	try {
		setValues(ps, lobCreator);
		return ps.executeUpdate();
	}
	finally {
		lobCreator.close();
	}
}
 

@Override
protected void nullSafeSetInternal(
		PreparedStatement ps, int index, Object value, LobCreator lobCreator)
		throws SQLException, UnsupportedEncodingException {

	if (value != null) {
		String str = (String) value;
		String encoding = getCharacterEncoding();
		byte[] bytes = (encoding != null ? str.getBytes(encoding) : str.getBytes());
		lobCreator.setBlobAsBytes(ps, index, bytes);
	}
	else {
		lobCreator.setBlobAsBytes(ps, index, null);
	}
}
 

@Override
public final Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
	LobCreator lobCreator = this.lobHandler.getLobCreator();
	try {
		setValues(ps, lobCreator);
		return ps.executeUpdate();
	}
	finally {
		lobCreator.close();
	}
}
 

@Before
public void setUp() {
	MockitoAnnotations.initMocks(this);
	preparedStatement = mock(PreparedStatement.class);
	handler = mock(LobHandler.class);
	creator = mock(LobCreator.class);
	given(handler.getLobCreator()).willReturn(creator);
}
 

default int upload(Integer organizationId, Integer eventId, UploadBase64FileModification file, Map<String, String> attributes) {

        LobHandler lobHandler = new DefaultLobHandler();

        String query = "insert into resource_global (name, content_size, content, content_type, attributes) values(?, ?, ?, ?, ?)";
        if (organizationId != null && eventId != null) {
            query = "insert into resource_event (name, content_size, content, content_type, attributes, organization_id_fk, event_id_fk) values(?, ?, ?, ?, ?, ?, ?)";
        } else if(organizationId != null) {
            query = "insert into resource_organizer (name, content_size, content, content_type, attributes, organization_id_fk) values(?, ?, ?, ?, ?, ?)";
        }

        return getNamedParameterJdbcTemplate().getJdbcOperations().execute(query,
            new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
                @Override
                protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
                    ps.setString(1, file.getName());
                    ps.setLong(2, file.getFile().length);
                    lobCreator.setBlobAsBytes(ps, 3, file.getFile());
                    ps.setString(4, file.getType());
                    ps.setString(5, Json.GSON.toJson(attributes));
                    if (organizationId != null) {
                        ps.setInt(6, organizationId);
                    }
                    if (eventId != null) {
                        ps.setInt(7, eventId);
                    }
                }
            }
        );
    }
 

@Override
protected void nullSafeSetInternal(
		PreparedStatement ps, int index, Object value, LobCreator lobCreator)
		throws SQLException {

	lobCreator.setBlobAsBytes(ps, index, (byte[]) value);
}
 

@Override
protected void nullSafeSetInternal(
		PreparedStatement ps, int index, Object value, LobCreator lobCreator)
		throws SQLException {

	lobCreator.setClobAsString(ps, index, (String) value);
}
 

@Before
public void setUp() {
	MockitoAnnotations.initMocks(this);
	preparedStatement = mock(PreparedStatement.class);
	handler = mock(LobHandler.class);
	creator = mock(LobCreator.class);
	given(handler.getLobCreator()).willReturn(creator);
}
 

@Override
public final Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
	LobCreator lobCreator = this.lobHandler.getLobCreator();
	try {
		setValues(ps, lobCreator);
		return ps.executeUpdate();
	}
	finally {
		lobCreator.close();
	}
}
 

@Override
public final Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
	LobCreator lobCreator = this.lobHandler.getLobCreator();
	try {
		setValues(ps, lobCreator);
		return ps.executeUpdate();
	}
	finally {
		lobCreator.close();
	}
}
 

private LoadResult loadInSingle(TaskWriterContext context, List<T> records, DbDialect dbDialect) {
    //statistic before
    LoadResult loadResult = new LoadResult();
    long startTime = System.currentTimeMillis();

    //do load
    LobCreator lobCreator = dbDialect.getLobHandler().getLobCreator();
    try {
        for (T record : records) {
            try {
                dbDialect.getTransactionTemplate().execute(transactionStatus -> {
                    transactionBegin();
                    JdbcTemplate template = dbDialect.getJdbcTemplate();
                    int i = loadOne(context, record, dbDialect, lobCreator, template);
                    transactionEnd();
                    return 0;
                });
            } catch (Throwable e) {
                if (e instanceof DuplicateKeyException) {
                    logger.warn("DuplicateKeyException for record load :" + e.getMessage());
                } else {
                    throw new RecordLoadException(record, e);
                }
            }
        }
    } finally {
        lobCreator.close();
    }

    //statistic after
    long totalTime = System.currentTimeMillis() - startTime;
    loadResult.setTotalRecords(records.size());
    loadResult.setTotalSqlTime(totalTime);
    loadResult.setAvgSqlTime(totalTime / records.size());

    return loadResult;
}
 

private LoadResult loadInBatch(TaskWriterContext context, List<T> records, DbDialect dbDialect) {
    //statistic before
    LoadResult loadResult = new LoadResult();
    long startTime = System.currentTimeMillis();

    //do load
    final String sql = getSql(records.get(0), context);//一个batch内的sql都是相同的,取第一个即可
    dbDialect.getTransactionTemplate().execute((transactionStatus -> {
        final LobCreator lobCreator = dbDialect.getLobHandler().getLobCreator();
        try {
            transactionBegin();
            JdbcTemplate template = dbDialect.getJdbcTemplate();
            int[] result = template.batchUpdate(sql, new BatchPreparedStatementSetter() {

                public void setValues(PreparedStatement ps, int idx) throws SQLException {
                    fillPreparedStatement(ps, lobCreator, records.get(idx), dbDialect, context);
                }

                public int getBatchSize() {
                    return records.size();
                }
            });
            transactionEnd();
            return result;
        } finally {
            lobCreator.close();
        }
    }));

    //statistic after
    long totalTime = System.currentTimeMillis() - startTime;
    loadResult.setTotalRecords(records.size());
    loadResult.setTotalSqlTime(totalTime);
    loadResult.setAvgSqlTime(totalTime / records.size());

    return loadResult;
}
 

@Before
public void setUp() {
	MockitoAnnotations.initMocks(this);
	preparedStatement = mock(PreparedStatement.class);
	handler = mock(LobHandler.class);
	creator = mock(LobCreator.class);
	given(handler.getLobCreator()).willReturn(creator);
}
 

@Test
default void clobSelect() {
    getJdbcOperations().execute("INSERT INTO clob_test VALUES (?)", new AbstractLobCreatingPreparedStatementCallback(new DefaultLobHandler()) {

        @Override
        protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
            lobCreator.setClobAsString(ps, 1, "test-value");
        }

    });

    // CLOB defaults to String
    Mono.from(getConnectionFactory().create())
        .flatMapMany(connection -> Flux.from(connection

            .createStatement("SELECT * from clob_test")
            .execute())
            .flatMap(result -> result
                .map((row, rowMetadata) -> row.get("value")))

            .concatWith(close(connection)))
        .as(StepVerifier::create)
        .expectNext("test-value").as("value from select")
        .verifyComplete();

    // CLOB consume as Clob
    Mono.from(getConnectionFactory().create())
        .flatMapMany(connection -> Flux.from(connection

            .createStatement("SELECT * from clob_test")
            .execute())
            .flatMap(result -> result
                .map((row, rowMetadata) -> row.get("value", Clob.class)))
            .flatMap(clob -> Flux.from(clob.stream())
                .reduce(new StringBuilder(), StringBuilder::append)
                .map(StringBuilder::toString)
                .concatWith(discard(clob)))

            .concatWith(close(connection)))
        .as(StepVerifier::create)
        .expectNext("test-value").as("value from select")
        .verifyComplete();
}
 

@Override
protected void fillPreparedStatement(PreparedStatement ps, LobCreator lobCreator, RdbEventRecord record, DbDialect dbDialect, TaskWriterContext context) throws SQLException {
    EventType type = record.getEventType();
    // 注意insert/update语句对应的字段数序都是将主键排在后面
    List<EventColumn> columns = new ArrayList<EventColumn>();
    if (type.isInsert()) {
        columns.addAll(record.getColumns()); // insert为所有字段
        columns.addAll(record.getKeys());
    } else if (type.isDelete()) {
        columns.addAll(record.getKeys());
    } else if (type.isUpdate()) {
        boolean existOldKeys = !CollectionUtils.isEmpty(record.getOldKeys());
        boolean hasAutoIncrementNotKeyColumns = dbDialect.hasAutoIncrementNotKeyColumns(record.getSchemaName(), record.getTableName());
        if (hasAutoIncrementNotKeyColumns) {
            columns.addAll(record.getUpdatedColumns());// 只更新带有isUpdate=true的字段
        } else {
            columns.addAll(record.getColumns());// update、upsert都更新所有字段
        }
        columns.addAll(record.getKeys());
        if (existOldKeys) {
            columns.addAll(record.getOldKeys());
        }
    }

    // 获取一下当前字段名的数据是否必填
    boolean isSyncAutoAddColumn = context.getWriterParameter().isSyncAutoAddColumn();
    Map<String, Boolean> isRequiredMap = buildRequiredMap(dbDialect, record, columns, isSyncAutoAddColumn);
    Table table = dbDialect.findTable(record.getSchemaName(), record.getTableName());

    for (int i = 0; i < columns.size(); i++) {
        int paramIndex = i + 1;
        EventColumn column = columns.get(i);
        Boolean isRequired = isRequiredMap.get(StringUtils.lowerCase(column.getColumnName()));

        int sqlType = getSqlType(column, table, record);
        Object param = null;
        if (dbDialect instanceof MysqlDialect
                && (sqlType == Types.TIME || sqlType == Types.TIMESTAMP || sqlType == Types.DATE)) {
            // 解决mysql的0000-00-00 00:00:00问题，直接依赖mysql
            // driver进行处理，如果转化为Timestamp会出错
            param = column.getColumnValue();
        } else {
            param = SqlUtils.stringToSqlValue(column.getColumnValue(),
                    sqlType,
                    isRequired,
                    dbDialect.isEmptyStringNulled());
        }

        try {
            switch (sqlType) {
                case Types.CLOB:
                    lobCreator.setClobAsString(ps, paramIndex, (String) param);
                    break;

                case Types.BLOB:
                    lobCreator.setBlobAsBytes(ps, paramIndex, param instanceof String ? ((String) param).getBytes() : (byte[]) param);
                    break;
                case Types.TIME:
                case Types.TIMESTAMP:
                case Types.DATE:
                    // 只处理mysql的时间类型，oracle的进行转化处理
                    if (dbDialect instanceof MysqlDialect) {
                        // 解决mysql的0000-00-00 00:00:00问题，直接依赖mysql
                        // driver进行处理，如果转化为Timestamp会出错
                        ps.setObject(paramIndex, param);
                    } else {
                        StatementCreatorUtils.setParameterValue(ps, paramIndex, sqlType, null, param);
                    }
                    break;
                case Types.BIT:
                    // 只处理mysql的bit类型，bit最多存储64位，所以需要使用BigInteger进行处理才能不丢精度
                    // mysql driver将bit按照setInt进行处理，会导致数据越界
                    if (dbDialect instanceof MysqlDialect) {
                        StatementCreatorUtils.setParameterValue(ps, paramIndex, Types.DECIMAL, null, param);
                    } else if (dbDialect instanceof SqlServerDialect) {
                        StatementCreatorUtils.setParameterValue(ps, paramIndex, sqlType, null, param.toString());
                    } else {
                        StatementCreatorUtils.setParameterValue(ps, paramIndex, sqlType, null, param);
                    }
                    break;
                default:
                    StatementCreatorUtils.setParameterValue(ps, paramIndex, sqlType, null, param);
                    break;
            }
        } catch (SQLException ex) {
            logger.error("SetParam error , [mappingId={}, sqltype={}, value={}]",
                    RecordMeta.mediaMapping(record).getId(), sqlType, param);
            throw ex;
        }
    }
}
 

private int loadOne(TaskWriterContext context, T record, DbDialect dbDialect, LobCreator lobCreator, JdbcTemplate template) {
    return template.update(getSql(record, context), ps -> {
        fillPreparedStatement(ps, lobCreator, record, dbDialect, context);
    });
}