Example usage for org.springframework.jdbc.core StatementCreatorUtils setParameterValue

Introduction

In this page you can find the example usage for org.springframework.jdbc.core StatementCreatorUtils setParameterValue.

Prototype

public static void setParameterValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName,
        @Nullable Object inValue) throws SQLException 

Source Link

Document

Set the value for a parameter.

Usage

From source file:com.alibaba.otter.node.etl.common.db.DbPerfIntergration.java

@Test
public void test_stack() {
    DbMediaSource dbMediaSource = new DbMediaSource();
    dbMediaSource.setId(1L);//from   w w  w  . ja  v a  2s .  c o  m
    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();
}

---

From source file:anyframe.core.query.impl.QueryServiceImpl.java

/**
 * Dynamic SQL?  Spring JdbcTemplate? batchUpdate   Insert,
 * Update, Delete Batch . ( ?? ? ?? ? ?? ? ,  SQL?
 * isDynamic? true? )/*  w ww . j  a v  a2s . c o m*/
 * 
 * @param sql
 *            dynamic query statement.
 * @param targets
 *            object of class which is matched with specified table in XML
 *            files. is the List type of Object Array.
 * @return an array of the number of rows affected by each statement
 */
protected int[] batchDynamicExecutor(final String sql, final List targets) {
    // NamedParameterUtils   Bind Variables
    // ? Prepared Statement
    //  .
    final ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql);
    return jdbcTemplate.batchUpdate(parsedSql.getNewSql(), new BatchPreparedStatementSetter() {

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

        // Spring JdbcTemplate? ? ?
        // callback method
        public void setValues(PreparedStatement ps, int index) throws SQLException {
            Map properties = new HashMap();
            properties.put("anyframe", targets.get(index));
            // NamedParameterUtils? 
            // inputMap?  dynamic SQL?
            //    Bind Variables? ? 
            //   .
            Object[] args = NamedParameterUtils.buildValueArray(parsedSql,
                    new ExtMapSqlParameterSource(properties));
            // Set the value for the parameter
            for (int i = 0; i < args.length; i++) {
                StatementCreatorUtils.setParameterValue(ps, i + 1, SqlTypeValue.TYPE_UNKNOWN, null, args[i]);
            }
        }
    });
}

---

From source file:anyframe.core.query.impl.QueryServiceImpl.java

/**
 * ? SQL?  Spring JdbcTemplate? batchUpdate   Insert, Update,
 * Delete Batch .  SQL? isDynamic? false? )
 * /* w  w w .  j  a va2s . c  o  m*/
 * @param sql
 *            static query statement.
 * @param targets
 *            object of class which is matched with specified table in XML
 *            files. is the List type of Object Array.
 * @return an array of the number of rows affected by each statement
 */
protected int[] batchStaticExecutor(final String sql, final List targets) {
    return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

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

        // Spring JdbcTemplate? ? ?
        // callback method
        public void setValues(PreparedStatement ps, int index) throws SQLException {
            Object[] args = (Object[]) targets.get(index);
            // Set the value for the parameter
            for (int i = 0; i < args.length; i++) {
                StatementCreatorUtils.setParameterValue(ps, i + 1, SqlTypeValue.TYPE_UNKNOWN, null, args[i]);
            }
        }
    });
}

---