Search in sources :

Example 16 with BatchPreparedStatementSetter

use of org.springframework.jdbc.core.BatchPreparedStatementSetter in project otter by alibaba.

the class DbPerfIntergration method test_stack.

@Test
public void test_stack() {
    DbMediaSource dbMediaSource = new DbMediaSource();
    dbMediaSource.setId(1L);
    dbMediaSource.setDriver("com.mysql.jdbc.Driver");
    dbMediaSource.setUsername("otter");
    dbMediaSource.setPassword("otter");
    dbMediaSource.setUrl("jdbc:mysql://127.0.0.1:3306/retl");
    dbMediaSource.setEncode("UTF-8");
    dbMediaSource.setType(DataMediaType.MYSQL);
    DbDataMedia dataMedia = new DbDataMedia();
    dataMedia.setSource(dbMediaSource);
    dataMedia.setId(1L);
    dataMedia.setName("ljhtable1");
    dataMedia.setNamespace("otter");
    final DbDialect dbDialect = dbDialectFactory.getDbDialect(2L, dataMedia.getSource());
    want.object(dbDialect).clazIs(MysqlDialect.class);
    final TransactionTemplate transactionTemplate = dbDialect.getTransactionTemplate();
    // 插入数据准备
    int minute = 5;
    int nextId = 1;
    final int thread = 10;
    final int batch = 50;
    final String sql = "insert into otter.ljhtable1 values(? , ? , ? , ?)";
    final CountDownLatch latch = new CountDownLatch(thread);
    ExecutorService executor = new ThreadPoolExecutor(thread, thread, 60, TimeUnit.SECONDS, new ArrayBlockingQueue(thread * 2), new NamedThreadFactory("load"), new ThreadPoolExecutor.CallerRunsPolicy());
    for (int sec = 0; sec < minute * 60; sec++) {
        // 执行秒循环
        long startTime = System.currentTimeMillis();
        for (int i = 0; i < thread; i++) {
            final int start = nextId + i * batch;
            executor.submit(new Runnable() {

                public void run() {
                    try {
                        transactionTemplate.execute(new TransactionCallback() {

                            public Object doInTransaction(TransactionStatus status) {
                                JdbcTemplate jdbcTemplate = dbDialect.getJdbcTemplate();
                                return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

                                    public void setValues(PreparedStatement ps, int idx) throws SQLException {
                                        int id = start + idx;
                                        StatementCreatorUtils.setParameterValue(ps, 1, Types.INTEGER, null, id);
                                        StatementCreatorUtils.setParameterValue(ps, 2, Types.VARCHAR, null, RandomStringUtils.randomAlphabetic(1000));
                                        // RandomStringUtils.randomAlphabetic()
                                        long time = new Date().getTime();
                                        StatementCreatorUtils.setParameterValue(ps, 3, Types.TIMESTAMP, new Timestamp(time));
                                        StatementCreatorUtils.setParameterValue(ps, 4, Types.TIMESTAMP, new Timestamp(time));
                                    }

                                    public int getBatchSize() {
                                        return batch;
                                    }
                                });
                            }
                        });
                    } finally {
                        latch.countDown();
                    }
                }
            });
        }
        long endTime = System.currentTimeMillis();
        try {
            latch.await(1000 * 60L - (endTime - startTime), TimeUnit.MILLISECONDS);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        if (latch.getCount() != 0) {
            System.out.println("perf is not enough!");
            System.exit(-1);
        }
        endTime = System.currentTimeMillis();
        System.out.println("Time cost : " + (System.currentTimeMillis() - startTime));
        try {
            TimeUnit.MILLISECONDS.sleep(1000L - (endTime - startTime));
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        nextId = nextId + thread * batch;
    }
    executor.shutdown();
}
Also used : SQLException(java.sql.SQLException) NamedThreadFactory(com.alibaba.otter.shared.common.utils.thread.NamedThreadFactory) TransactionTemplate(org.springframework.transaction.support.TransactionTemplate) TransactionStatus(org.springframework.transaction.TransactionStatus) PreparedStatement(java.sql.PreparedStatement) CountDownLatch(java.util.concurrent.CountDownLatch) JdbcTemplate(org.springframework.jdbc.core.JdbcTemplate) Timestamp(java.sql.Timestamp) Date(java.util.Date) TransactionCallback(org.springframework.transaction.support.TransactionCallback) ArrayBlockingQueue(java.util.concurrent.ArrayBlockingQueue) DbDialect(com.alibaba.otter.node.etl.common.db.dialect.DbDialect) BatchPreparedStatementSetter(org.springframework.jdbc.core.BatchPreparedStatementSetter) ExecutorService(java.util.concurrent.ExecutorService) DbMediaSource(com.alibaba.otter.shared.common.model.config.data.db.DbMediaSource) ThreadPoolExecutor(java.util.concurrent.ThreadPoolExecutor) DbDataMedia(com.alibaba.otter.shared.common.model.config.data.db.DbDataMedia) Test(org.testng.annotations.Test) BaseDbTest(com.alibaba.otter.node.etl.BaseDbTest)

Example 17 with BatchPreparedStatementSetter

use of org.springframework.jdbc.core.BatchPreparedStatementSetter in project otter by alibaba.

the class AbstractOperationInterceptor method init.

private void init(final JdbcTemplate jdbcTemplate, final String markTableName, final String markTableColumn) {
    int count = jdbcTemplate.queryForInt(MessageFormat.format(checkDataSql, markTableName, GLOBAL_THREAD_COUNT - 1));
    if (count != GLOBAL_THREAD_COUNT) {
        if (logger.isInfoEnabled()) {
            logger.info("Interceptor: init " + markTableName + "'s data.");
        }
        TransactionTemplate transactionTemplate = new TransactionTemplate();
        transactionTemplate.setTransactionManager(new DataSourceTransactionManager(jdbcTemplate.getDataSource()));
        // 注意这里强制使用非事务,保证多线程的可见性
        transactionTemplate.setPropagationBehavior(TransactionDefinition.PROPAGATION_NOT_SUPPORTED);
        transactionTemplate.execute(new TransactionCallback() {

            public Object doInTransaction(TransactionStatus status) {
                jdbcTemplate.execute(MessageFormat.format(deleteDataSql, markTableName));
                String batchSql = MessageFormat.format(updateSql, new Object[] { markTableName, markTableColumn });
                jdbcTemplate.batchUpdate(batchSql, new BatchPreparedStatementSetter() {

                    public void setValues(PreparedStatement ps, int idx) throws SQLException {
                        ps.setInt(1, idx);
                        ps.setInt(2, 0);
                    // ps.setNull(3, Types.VARCHAR);
                    }

                    public int getBatchSize() {
                        return GLOBAL_THREAD_COUNT;
                    }
                });
                return null;
            }
        });
        if (logger.isInfoEnabled()) {
            logger.info("Interceptor: Init EROSA Client Data: " + updateSql);
        }
    }
}
Also used : TransactionCallback(org.springframework.transaction.support.TransactionCallback) BatchPreparedStatementSetter(org.springframework.jdbc.core.BatchPreparedStatementSetter) TransactionTemplate(org.springframework.transaction.support.TransactionTemplate) TransactionStatus(org.springframework.transaction.TransactionStatus) PreparedStatement(java.sql.PreparedStatement) DataSourceTransactionManager(org.springframework.jdbc.datasource.DataSourceTransactionManager)

Example 18 with BatchPreparedStatementSetter

use of org.springframework.jdbc.core.BatchPreparedStatementSetter in project ma-core-public by infiniteautomation.

the class MailingListDao method saveRelationalData.

void saveRelationalData(final MailingList ml) {
    // Save the inactive intervals.
    ejt.update("delete from mailingListInactive where mailingListId=?", new Object[] { ml.getId() });
    // Save what is in the mailing list object.
    final List<Integer> intervalIds = new ArrayList<Integer>(ml.getInactiveIntervals());
    ejt.batchUpdate(MAILING_LIST_INACTIVE_INSERT, new BatchPreparedStatementSetter() {

        public int getBatchSize() {
            return intervalIds.size();
        }

        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setInt(1, ml.getId());
            ps.setInt(2, intervalIds.get(i));
        }
    });
    // Delete existing entries
    ejt.update("delete from mailingListMembers where mailingListId=?", new Object[] { ml.getId() });
    // Save what is in the mailing list object.
    final List<EmailRecipient> entries = ml.getEntries();
    ejt.batchUpdate(MAILING_LIST_ENTRY_INSERT, new BatchPreparedStatementSetter() {

        public int getBatchSize() {
            return entries.size();
        }

        public void setValues(PreparedStatement ps, int i) throws SQLException {
            EmailRecipient e = entries.get(i);
            ps.setInt(1, ml.getId());
            ps.setInt(2, e.getRecipientType());
            ps.setInt(3, e.getReferenceId());
            ps.setString(4, e.getReferenceAddress());
        }
    });
}
Also used : EmailRecipient(com.serotonin.m2m2.vo.mailingList.EmailRecipient) SQLException(java.sql.SQLException) BatchPreparedStatementSetter(org.springframework.jdbc.core.BatchPreparedStatementSetter) ArrayList(java.util.ArrayList) PreparedStatement(java.sql.PreparedStatement)

Example 19 with BatchPreparedStatementSetter

use of org.springframework.jdbc.core.BatchPreparedStatementSetter in project SSM by Intel-bigdata.

the class ActionDao method update.

public int[] update(final ActionInfo[] actionInfos) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String sql = "UPDATE " + TABLE_NAME + " SET " + "result = ?, " + "log = ?, " + "successful = ?, " + "create_time = ?, " + "finished = ?, " + "finish_time = ?, " + "exec_host = ?, " + "progress = ? " + "WHERE aid = ?";
    return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setString(1, actionInfos[i].getResult());
            ps.setString(2, actionInfos[i].getLog());
            ps.setBoolean(3, actionInfos[i].isSuccessful());
            ps.setLong(4, actionInfos[i].getCreateTime());
            ps.setBoolean(5, actionInfos[i].isFinished());
            ps.setLong(6, actionInfos[i].getFinishTime());
            ps.setString(7, actionInfos[i].getExecHost());
            ps.setFloat(8, actionInfos[i].getProgress());
            ps.setLong(9, actionInfos[i].getActionId());
        }

        public int getBatchSize() {
            return actionInfos.length;
        }
    });
}
Also used : SQLException(java.sql.SQLException) BatchPreparedStatementSetter(org.springframework.jdbc.core.BatchPreparedStatementSetter) PreparedStatement(java.sql.PreparedStatement) NamedParameterJdbcTemplate(org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate) JdbcTemplate(org.springframework.jdbc.core.JdbcTemplate)

Example 20 with BatchPreparedStatementSetter

use of org.springframework.jdbc.core.BatchPreparedStatementSetter in project SSM by Intel-bigdata.

the class ActionDao method batchDeleteCmdletActions.

public int[] batchDeleteCmdletActions(final List<Long> cids) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    final String sql = "DELETE FROM " + TABLE_NAME + " WHERE cid = ?";
    return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ps.setLong(1, cids.get(i));
        }

        public int getBatchSize() {
            return cids.size();
        }
    });
}
Also used : SQLException(java.sql.SQLException) BatchPreparedStatementSetter(org.springframework.jdbc.core.BatchPreparedStatementSetter) PreparedStatement(java.sql.PreparedStatement) NamedParameterJdbcTemplate(org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate) JdbcTemplate(org.springframework.jdbc.core.JdbcTemplate)

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