Examples with JdbcTemplate org.springframework.jdbc.core.JdbcTemplate used on opensource projects

Example 86 with JdbcTemplate

use of org.springframework.jdbc.core.JdbcTemplate 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();
}

Example 87 with JdbcTemplate

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

the class TimeTableIntegration method test_mysql.

@Test
public void test_mysql() {
    DbMediaSource dbMediaSource = new DbMediaSource();
    dbMediaSource.setId(10L);
    dbMediaSource.setDriver("oracle.jdbc.OracleDriver");
    dbMediaSource.setUsername("otter1");
    dbMediaSource.setPassword("jonathan");
    dbMediaSource.setUrl("jdbc:oracle:thin:@127.0.0.1:1521:ointest");
    dbMediaSource.setEncode("UTF-8");
    dbMediaSource.setType(DataMediaType.ORACLE);
    final DbDialect dbDialect = dbDialectFactory.getDbDialect(2L, dbMediaSource);
    // want.object(dbDialect).clazIs(MysqlDialect.class);
    Table table = dbDialect.findTable("otter2", "test_time");
    System.out.println(table);
    final SqlTemplate sqlTemplate = dbDialect.getSqlTemplate();
    final JdbcTemplate jdbcTemplate = dbDialect.getJdbcTemplate();
    final TransactionTemplate transactionTemplate = dbDialect.getTransactionTemplate();
    final int[] pkColumnTypes = { Types.INTEGER };
    final int[] columnTypes = { Types.TIMESTAMP, Types.TIMESTAMP, Types.DATE, Types.TIME, Types.INTEGER, Types.INTEGER };
    transactionTemplate.execute(new TransactionCallback() {

        public Object doInTransaction(TransactionStatus status) {
            int affect = 0;
            String sql = null;
            // 执行insert
            sql = sqlTemplate.getInsertSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            return null;
        // throw new RuntimeException("rollback");
        }
    });
}

Example 88 with JdbcTemplate

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

the class AbstractDbDialectTest method testFindTable.

@Test
public void testFindTable() throws Exception {
    DataSource dataSource = createDataSource("jdbc:oracle:thin:@127.0.0.1:1521:OINTEST", "otter1", "jonathan", "oracle.jdbc.OracleDriver", DataMediaType.ORACLE, "utf-8");
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    Table table = DdlUtils.findTable(jdbcTemplate, "otter1".toUpperCase(), "otter1".toUpperCase(), "wytable3".toUpperCase());
    System.out.println("the tablename = " + table.getSchema() + "." + table.getName());
    Column[] columns = table.getColumns();
    for (Column column : columns) {
        System.out.println("columnName = " + column.getName() + ",columnType = " + column.getTypeCode() + ",isPrimary = " + column.isPrimaryKey() + ",nullable = " + column.isRequired());
    }
}

Example 89 with JdbcTemplate

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

the class BitTableIntegration method test_mysql.

@Test
public void test_mysql() throws UnsupportedEncodingException {
    DbMediaSource dbMediaSource = new DbMediaSource();
    dbMediaSource.setId(10L);
    dbMediaSource.setDriver("com.mysql.jdbc.Driver");
    dbMediaSource.setUsername("xxxxx");
    dbMediaSource.setPassword("xxxxx");
    dbMediaSource.setUrl("jdbc:mysql://127.0.0.1:3306");
    dbMediaSource.setEncode("UTF-8");
    dbMediaSource.setType(DataMediaType.MYSQL);
    final DbDialect dbDialect = dbDialectFactory.getDbDialect(2L, dbMediaSource);
    want.object(dbDialect).clazIs(MysqlDialect.class);
    Table table = dbDialect.findTable(SCHEMA_NAME, TABLE_NAME);
    System.out.println(table);
    final SqlTemplate sqlTemplate = dbDialect.getSqlTemplate();
    final JdbcTemplate jdbcTemplate = dbDialect.getJdbcTemplate();
    final TransactionTemplate transactionTemplate = dbDialect.getTransactionTemplate();
    final int[] pkColumnTypes = { Types.INTEGER };
    final int[] columnTypes = { Types.BIT, Types.BIT };
    transactionTemplate.execute(new TransactionCallback() {

        public Object doInTransaction(TransactionStatus status) {
            int affect = 0;
            String sql = null;
            // 执行insert
            sql = sqlTemplate.getInsertSql(SCHEMA_NAME, TABLE_NAME, pkColumns, columns);
            System.out.println(sql);
            affect = (Integer) jdbcTemplate.execute(sql, new PreparedStatementCallback() {

                public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                    doPreparedStatement(ps, dbDialect, toTypes(columnTypes, pkColumnTypes), toValues(columnValues, pkColumnValues));
                    return ps.executeUpdate();
                }
            });
            want.number(affect).isEqualTo(1);
            return null;
        // throw new RuntimeException("rollback");
        }
    });
}

Example 90 with JdbcTemplate

use of org.springframework.jdbc.core.JdbcTemplate in project vladmihalcea.wordpress.com by vladmihalcea.

the class HibernateSQLStatementCountTest method test.

@Test
public void test() {
    transactionTemplate.execute(new TransactionCallback<Void>() {

        @Override
        public Void doInTransaction(TransactionStatus transactionStatus) {
            Company company = new Company();
            company.setName("TV Company");
            entityManager.persist(company);
            Product product1 = new Product("tvCode");
            product1.setName("TV");
            product1.setCompany(company);
            WarehouseProductInfo warehouseProductInfo1 = new WarehouseProductInfo();
            warehouseProductInfo1.setQuantity(101);
            product1.addWarehouse(warehouseProductInfo1);
            Product product2 = new Product("cdCode");
            product2.setName("CD");
            product2.setCompany(company);
            WarehouseProductInfo warehouseProductInfo2 = new WarehouseProductInfo();
            warehouseProductInfo2.setQuantity(50);
            product2.addWarehouse(warehouseProductInfo2);
            entityManager.persist(product1);
            entityManager.persist(product2);
            entityManager.flush();
            final JdbcTemplate otherDataSourceJdbcTemplate = new JdbcTemplate(otherDataSource);
            List<Map<String, Object>> versions = otherDataSourceJdbcTemplate.queryForList(" select * from version ");
            assertTrue(versions.isEmpty());
            return null;
        }
    });
    try {
        SQLStatementCountValidator.reset();
        warehouseProductInfoService.findAllWithNPlusOne();
        SQLStatementCountValidator.assertSelectCount(1);
    } catch (SQLSelectCountMismatchException e) {
        assertEquals(3, e.getRecorded());
    }
    SQLStatementCountValidator.reset();
    warehouseProductInfoService.findAllWithFetch();
    SQLStatementCountValidator.assertSelectCount(1);
    SQLStatementCountValidator.reset();
    warehouseProductInfoService.newWarehouseProductInfo();
    SQLStatementCountValidator.assertSelectCount(1);
    SQLStatementCountValidator.assertInsertCount(2);
}