DataSourceUtils.getConnection(jdbcTemplate.getDataSource()).commit(); Not working i.e, unable to commit

Question

I am new to JDBC template and am trying to use prepared statement for inserting data into database using auto commit mode off for achieving high performance but at the end i'm not able to commit the transaction. Please suggest some correct approach or reference that might solve my problem.

Thanks in advance...

SpringjdbcApplication.java

@SpringBootApplication
public class SpringjdbcApplication 
{
    public static void main(String[] args) 
    {
        ApplicationContext context  = SpringApplication.run(SpringjdbcApplication.class, args);
        SampleService service = context.getBean(SampleService.class);

        List<Batch> batchList = new ArrayList<>();
        batchList.add(new Batch("A","B"));
        batchList.add(new Batch("B","B"));
        batchList.add(new Batch("C","B"));
        batchList.add(new Batch("D","B"));
        batchList.add(new Batch("E","B"));
        System.err.println("The number of rows inserted = "+service.singleInsert(batchList));
        System.err.println("The count of batch class is = "+service.getCount());
    }

}

SampleConfiguration.java

@Configuration
public class SampleConfiguration 
{
    @Bean
    public DataSource mysqlDataSource() 
    {
        HikariConfig config= new HikariConfig();
        config.setDriverClassName("ClassName");
        config.setJdbcUrl("URL");
        config.setUsername("User");
        config.setPassword("Password");
        config.setMinimumIdle(600);
        config.setMaximumPoolSize(30);
        config.setConnectionTimeout(251);
        config.setMaxLifetime(250);
        config.setAutoCommit(false);
        return new HikariDataSource(config);
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource)
    {
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(dataSource);
        return jdbcTemplate;
    }

}

Batch.java

@Entity
public class Batch implements Serializable 
{

    private static final long serialVersionUID = -5687736664713755991L;

    @Id
    @Column(name="field1")
    private String field1;

    @Column(name="field2")
    private String field2;

        ....... getter, setter and constructor
}

SampleService.java

@Service
public interface SampleService 
{
    public int singleInsert(List<Batch> batchList);
}

SampleServiceImpl.java

@Service
public class SampleServiceImpl implements SampleService 
{

    @Autowired
    JdbcTemplate jdbcTemplate;
    @Override
    public int singleInsert(List<Batch> batchList) 
    {
        for(Batch i:batchList)
        {
            jdbcTemplate.update("insert into batch values(?,?)",i.getField1(),i.getField2());
        }

        try
        {
            DataSourceUtils.getConnection(jdbcTemplate.getDataSource()).commit();
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return 1;
    }

}

Possible duplicate of [Commit on jdbcTemplate or DataSource](https://stackoverflow.com/questions/31983352/commit-on-jdbctemplate-or-datasource) — Ori Marko, Jun 10 '19 at 09:35
I don't want to use transactional because my application may have to call insert method multiple times let's say 10,000 times. Now adding transactional annotation means 10k commits which i don't want. I want a single commit at the end only — Anand, Jun 10 '19 at 10:17
Actually my example might be somewhat misleading and i should make another method which should be dedicated for performing commit only. — Anand, Jun 10 '19 at 10:19
@Anand That depends on the scope of where you put `@Transactional`. If you put `@Transactional` on the method with the loop, it will commit only once. — Mark Rotteveel, Jun 10 '19 at 15:44
@MarkRotteveel what if i call the method 10k times. Just to give a summary of what i'm trying to ask. Please break the method SingleInsert into 2 parts( first part inserts the data and Second part commits the changes made by first part). As of now both the parts are clubbed in a single method. Now consider my requirement says to call the insert 10k times or so which means 10k commits. But the ideal approach should be 10k times insertion followed by single commit. I want loose coupling between the two parts i.e, insertion and commit should be totally independent of each other. — Anand, Jun 10 '19 at 18:36
I repeat, you use a method annotated with `@Transactional` that contains all the logic that comprises those 10K inserts (which can be in other method invocations). When the final outer `@Transactional` method completes normally, the transaction will be committed. So `@Transactional method1() { for (i = 0; i < 10_000, i ++) { method2(i); } }`. Where `method2` contains the insert — Mark Rotteveel, Jun 11 '19 at 13:36

DataSourceUtils.getConnection(jdbcTemplate.getDataSource()).commit(); Not working i.e, unable to commit

0 Answers0