Search in sources :

Example 26 with BatchPreparedStatementSetter

use of org.springframework.jdbc.core.BatchPreparedStatementSetter in project disconf by knightliao.

the class GenericDao method update.

/**
 * 批量更新
 *
 * @param entities
 *
 * @return
 */
public int update(List<ENTITY> entities) {
    if (entities == null || entities.size() == 0) {
        recordLog(" param entity is null!");
        return 0;
    }
    final List<Query> queries = queryGenerator.getUpdateQuery(entities);
    String sql = queries.get(0).getSql();
    jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

        public void setValues(PreparedStatement stmt, int index) throws SQLException {
            int i = 1;
            List<Object> params = queries.get(index).getParams();
            for (Object o : params) {
                stmt.setObject(i++, o);
            }
        }

        public int getBatchSize() {
            return queries.size();
        }
    });
    return entities.size();
}
Also used : Query(com.baidu.unbiz.common.genericdao.operator.Query) SQLException(java.sql.SQLException) BatchPreparedStatementSetter(org.springframework.jdbc.core.BatchPreparedStatementSetter) PreparedStatement(java.sql.PreparedStatement) ArrayList(java.util.ArrayList) List(java.util.List) BaseObject(com.github.knightliao.apollo.db.bo.BaseObject)

Example 27 with BatchPreparedStatementSetter

use of org.springframework.jdbc.core.BatchPreparedStatementSetter in project thingsboard by thingsboard.

the class PsqlLatestInsertTsRepository method saveOrUpdate.

@Override
public void saveOrUpdate(List<TsKvLatestEntity> entities) {
    transactionTemplate.execute(new TransactionCallbackWithoutResult() {

        @Override
        protected void doInTransactionWithoutResult(TransactionStatus status) {
            String batchUpdateQuery = updateByLatestTs ? BATCH_UPDATE_BY_LATEST_TS : BATCH_UPDATE;
            String insertOrUpdateQuery = updateByLatestTs ? INSERT_OR_UPDATE_BY_LATEST_TS : INSERT_OR_UPDATE;
            int[] result = jdbcTemplate.batchUpdate(batchUpdateQuery, new BatchPreparedStatementSetter() {

                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    TsKvLatestEntity tsKvLatestEntity = entities.get(i);
                    ps.setLong(1, tsKvLatestEntity.getTs());
                    if (tsKvLatestEntity.getBooleanValue() != null) {
                        ps.setBoolean(2, tsKvLatestEntity.getBooleanValue());
                    } else {
                        ps.setNull(2, Types.BOOLEAN);
                    }
                    ps.setString(3, replaceNullChars(tsKvLatestEntity.getStrValue()));
                    if (tsKvLatestEntity.getLongValue() != null) {
                        ps.setLong(4, tsKvLatestEntity.getLongValue());
                    } else {
                        ps.setNull(4, Types.BIGINT);
                    }
                    if (tsKvLatestEntity.getDoubleValue() != null) {
                        ps.setDouble(5, tsKvLatestEntity.getDoubleValue());
                    } else {
                        ps.setNull(5, Types.DOUBLE);
                    }
                    ps.setString(6, replaceNullChars(tsKvLatestEntity.getJsonValue()));
                    ps.setObject(7, tsKvLatestEntity.getEntityId());
                    ps.setInt(8, tsKvLatestEntity.getKey());
                    if (updateByLatestTs) {
                        ps.setLong(9, tsKvLatestEntity.getTs());
                    }
                }

                @Override
                public int getBatchSize() {
                    return entities.size();
                }
            });
            int updatedCount = 0;
            for (int i = 0; i < result.length; i++) {
                if (result[i] == 0) {
                    updatedCount++;
                }
            }
            List<TsKvLatestEntity> insertEntities = new ArrayList<>(updatedCount);
            for (int i = 0; i < result.length; i++) {
                if (result[i] == 0) {
                    insertEntities.add(entities.get(i));
                }
            }
            jdbcTemplate.batchUpdate(insertOrUpdateQuery, new BatchPreparedStatementSetter() {

                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    TsKvLatestEntity tsKvLatestEntity = insertEntities.get(i);
                    ps.setObject(1, tsKvLatestEntity.getEntityId());
                    ps.setInt(2, tsKvLatestEntity.getKey());
                    ps.setLong(3, tsKvLatestEntity.getTs());
                    ps.setLong(9, tsKvLatestEntity.getTs());
                    if (updateByLatestTs) {
                        ps.setLong(15, tsKvLatestEntity.getTs());
                    }
                    if (tsKvLatestEntity.getBooleanValue() != null) {
                        ps.setBoolean(4, tsKvLatestEntity.getBooleanValue());
                        ps.setBoolean(10, tsKvLatestEntity.getBooleanValue());
                    } else {
                        ps.setNull(4, Types.BOOLEAN);
                        ps.setNull(10, Types.BOOLEAN);
                    }
                    ps.setString(5, replaceNullChars(tsKvLatestEntity.getStrValue()));
                    ps.setString(11, replaceNullChars(tsKvLatestEntity.getStrValue()));
                    if (tsKvLatestEntity.getLongValue() != null) {
                        ps.setLong(6, tsKvLatestEntity.getLongValue());
                        ps.setLong(12, tsKvLatestEntity.getLongValue());
                    } else {
                        ps.setNull(6, Types.BIGINT);
                        ps.setNull(12, Types.BIGINT);
                    }
                    if (tsKvLatestEntity.getDoubleValue() != null) {
                        ps.setDouble(7, tsKvLatestEntity.getDoubleValue());
                        ps.setDouble(13, tsKvLatestEntity.getDoubleValue());
                    } else {
                        ps.setNull(7, Types.DOUBLE);
                        ps.setNull(13, Types.DOUBLE);
                    }
                    ps.setString(8, replaceNullChars(tsKvLatestEntity.getJsonValue()));
                    ps.setString(14, replaceNullChars(tsKvLatestEntity.getJsonValue()));
                }

                @Override
                public int getBatchSize() {
                    return insertEntities.size();
                }
            });
        }
    });
}
Also used : TsKvLatestEntity(org.thingsboard.server.dao.model.sqlts.latest.TsKvLatestEntity) BatchPreparedStatementSetter(org.springframework.jdbc.core.BatchPreparedStatementSetter) TransactionStatus(org.springframework.transaction.TransactionStatus) PreparedStatement(java.sql.PreparedStatement) ArrayList(java.util.ArrayList) List(java.util.List) TransactionCallbackWithoutResult(org.springframework.transaction.support.TransactionCallbackWithoutResult)

Example 28 with BatchPreparedStatementSetter

use of org.springframework.jdbc.core.BatchPreparedStatementSetter in project thingsboard by thingsboard.

the class EventInsertRepository method save.

protected void save(List<EventEntity> entities) {
    transactionTemplate.execute(new TransactionCallbackWithoutResult() {

        @Override
        protected void doInTransactionWithoutResult(TransactionStatus status) {
            jdbcTemplate.batchUpdate(INSERT, new BatchPreparedStatementSetter() {

                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    EventEntity event = entities.get(i);
                    ps.setObject(1, event.getId());
                    ps.setLong(2, event.getCreatedTime());
                    ps.setString(3, replaceNullChars(event.getBody().toString()));
                    ps.setObject(4, event.getEntityId());
                    ps.setString(5, event.getEntityType().name());
                    ps.setString(6, event.getEventType());
                    ps.setString(7, event.getEventUid());
                    ps.setObject(8, event.getTenantId());
                    ps.setLong(9, event.getTs());
                }

                @Override
                public int getBatchSize() {
                    return entities.size();
                }
            });
        }
    });
}
Also used : BatchPreparedStatementSetter(org.springframework.jdbc.core.BatchPreparedStatementSetter) EventEntity(org.thingsboard.server.dao.model.sql.EventEntity) TransactionStatus(org.springframework.transaction.TransactionStatus) PreparedStatement(java.sql.PreparedStatement) TransactionCallbackWithoutResult(org.springframework.transaction.support.TransactionCallbackWithoutResult)

Aggregations

PreparedStatement (java.sql.PreparedStatement)28 BatchPreparedStatementSetter (org.springframework.jdbc.core.BatchPreparedStatementSetter)28 SQLException (java.sql.SQLException)22 JdbcTemplate (org.springframework.jdbc.core.JdbcTemplate)18 TransactionStatus (org.springframework.transaction.TransactionStatus)6 ArrayList (java.util.ArrayList)5 NamedParameterJdbcTemplate (org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate)5 List (java.util.List)4 TransactionCallbackWithoutResult (org.springframework.transaction.support.TransactionCallbackWithoutResult)4 TransactionCallback (org.springframework.transaction.support.TransactionCallback)2 TransactionTemplate (org.springframework.transaction.support.TransactionTemplate)2 ModifyWordbook (zjp.translateit.dto.ModifyWordbook)2 BaseDbTest (com.alibaba.otter.node.etl.BaseDbTest)1 DbDialect (com.alibaba.otter.node.etl.common.db.dialect.DbDialect)1 DbDataMedia (com.alibaba.otter.shared.common.model.config.data.db.DbDataMedia)1 DbMediaSource (com.alibaba.otter.shared.common.model.config.data.db.DbMediaSource)1 NamedThreadFactory (com.alibaba.otter.shared.common.utils.thread.NamedThreadFactory)1 Query (com.baidu.unbiz.common.genericdao.operator.Query)1 BaseObject (com.github.knightliao.apollo.db.bo.BaseObject)1 EmailRecipient (com.serotonin.m2m2.vo.mailingList.EmailRecipient)1