Commit on jdbcTemplate or DataSource

jdbctemplate autocommit
spring boot jdbctemplate transaction
spring declarative transaction management annotation example
org/springframework jdbc datasource datasourcetransactionmanager
transactiontemplate
spring boot transaction logging
jdbctemplate batchupdate rollback
spring transaction management example program

I wanted to do commit and rollback using jdbcTemplate.

My question is based on this thread

How do I commit or rollback, should I do it on jdbcTemplate like

jdbcTemplate.commit();
jdbcTemplate.rollback();

Or there are some other ways to achieve commit and rollback functionality using jdbcTemplate.

To call commit or rollback at will set the transactional boundaries programmatically and not declaratively.

For that reason you have to get hold of the PlatformTransactionManager - inject it that is in your DAO and perform the commit/ rollback operation yourself.

Sample code:

@Autowired private JdbcTemplate jdbcTemplate;
@Autowired private PlatformTransactionManager platformTransactionManager;

 //..

public void daoMethod(params) {
  DefaultTransactionDefinition paramTransactionDefinition = new    DefaultTransactionDefinition();

  TransactionStatus status=platformTransactionManager.getTransaction(paramTransactionDefinition );
try{
  String sqlQuery = "query";
  jdbcTemplate.update(sqlQuery, params);
  platformTransactionManager.commit(status);
}catch (Exception e) {
  platformTransactionManager.rollback(status);
}

Another approach is to get hold of the TransactionTemplate

Sample code:

@Autowired private JdbcTemplate jdbcTemplate;
@Autowired private TransactionTemplate transactionTemplate;

//..


//for operations where query does not return like delete
public void daoMethod(params) {
  transactionTemplate.execute(new TransactionCallbackWithoutResult() {
    protected void doInTransactionWithoutResult(TransactionStatus paramTransactionStatus) {
    try{
      String sqlQuery = "query";
      jdbcTemplate.update(query, params);
    }catch (Exception e) {
      paramTransactionStatus.setRollbackOnly();
    }
    }
  });
}

//for operations where query does return like insert
public int daoMethod(params) {
return  transactionTemplate.execute(new TransactionCallback<Integer>() {
  public Integer doInTransaction(TransactionStatus paramTransactionStatus) {
    String sqlQuery = "query";
    Object[] params = params;
    int[] types = myTypes;
    return jdbcTemplate.update(sqlQuery,params,types);
   }
 });
}}

AutoCommit Issue while using JDBCTemplate (Spring forum at , AutoCommit Issue while using JDBCTemplate. Post by: Yeah don't do that Show me your datasource configuration in your XML. Post by:  Java JDBC DataSource – Database Setup. Before we get into our example programs, we need some database setup with table and sample data. Installation of MySQL or Oracle database is out of scope of this tutorial, so I will just go ahead and setup table with sample data.

Use @Transactional. But of course, before of that, you will have to create bean definition for DataSourceTransactionManager:

// Your DataSource bean definition
@Bean
public DataSource dataSource() {
    ....
}

// Transaction manager bean definition
@Bean
public DataSourceTransactionManager dataSourceTransactionManager(DataSource dataSource) {
    DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
    dataSourceTransactionManager.setDataSource(dataSource);

    return dataSourceTransactionManager;
}

And then you can use @Transactional. Example of service:

@Service
public class MyServiceImpl {

    @Autowired
    private MyDAO myDAO;

    @Transactional
    public void insert(Entity entity) {
       myDAO.insert(entity);
    }
}

JdbcTemplate - How to commit and rollback?, The object I'm using is the JdbcTemplate, which has the encapsulated datasource inside it. I would like to know how to start new transactions with JdbcTemplate  As i understand, the jdbcTemplate.update() method will automatically commit the transaction and close the connection. I would be interested to find out if i can by some means set the auto commit to false.

the easiest way of managing transactions in spring is @Transactional annotation, so your code will look very simple:

@Transactional(rollbackFor = Exception.class)
public void doSomething(...) {
    ...
}

read more: http://docs.spring.io/spring/docs/current/spring-framework-reference/html/transaction.html

Spring Transaction Management Example JDBC, The transaction should commit only when all the JDBC statements execute JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);  Spring Transaction Management Example JDBC, @Transactional annotation, JdbcTemplate, DataSourceTransactionManager, DriverManagerDataSource, Rollback example

if you have configured the spring transaction manager / jdbcTemplate correctly , then you could always use the @Transactional annotations provided by spring in order to define when you want a transaction to be rolled back or not. But even if you have defined a rollback and your jdbc driver or your database do not allow transactions (check TRANSACTION_ISOLATION over JdbcConnection), then spring will log that is using transactions but the database will simply ignore those points.

Spring Transaction Management Example with JDBC Example , Since the current example is about JDBC with a single data source, we will be that we get a precise control on when to commit or rollback the transaction. The implementation will depend on JdbcTemplate helper class to  Since JdbcTemplate is configured with a DataSource, which is in turn configured with a pool of Connections, there is no guarantee that the same Connection object will be returned to the JdbcTemplate in subsequent calls.

@Configuration
public class ConfigurationApp {
    @Bean
    public DataSource dataSourceJdbc() {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
        dataSource.setUrl("jdbc:oracle:thin:@localhost:1521:orcl");
        dataSource.setUsername("hossein");
        dataSource.setPassword("myjava123");
        dataSource.setDefaultAutoCommit(false);
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSourceJdbc());
        return jdbcTemplate;
    }

    @Bean
    public DAOImpl dao() {
        DAOImpl personDAO = new DAOImpl();
        personDAO.setJdbcTemplate(jdbcTemplate());
        return personDAO;
    }


    @Bean
    public PersonService personService() {
        PersonService personService = new PersonService();
        personService.setPersonDAO(dao());
        return personService;
    }
}

//////////////////////////////////////////
public class Person {
    private Integer id;
    private String name;
    private String family;
    private Integer password;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getFamily() {
        return family;
    }

    public void setFamily(String family) {
        this.family = family;
    }

    public Integer getPassword() {
        return password;
    }

    public void setPassword(Integer password) {
        this.password = password;
    }
}
/////////////////////////////////////////
import org.apache.commons.dbcp2.BasicDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public class DAOImpl  {
    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }


    public int add(Person person) {
        String sql = "insert into person(id,name,family,password) values(?,?,?,?)";
        return this.jdbcTemplate.update(sql, person.getId(), person.getName(), person.getFamily(), person.getPassword());
    }

    public void commit(){
        BasicDataSource basicDataSource= (BasicDataSource) jdbcTemplate.getDataSource();
        basicDataSource.setDefaultAutoCommit(true);
    }
  }

///////////////////////////////////
import org.springframework.stereotype.Service;

import java.util.List;
@Service
public class PersonService {
    private DAOImpl personDAO;
    public void setPersonDAO(DAOImpl personDAO){
        this.personDAO=personDAO;
       }
    public void addPerson(Person person) {
       personDAO.add(person);
       this.personDAO.commit();
    }
}
///////////////////////

public class MainApp {
    public static void main(String[] args) {
        Locale.setDefault(Locale.ENGLISH);
        AnnotationConfigApplicationContext ac=new AnnotationConfigApplicationContext(ConfigurationApp.class);
        PersonService person=ac.getBean(PersonService.class);
             Person person1=new Person();
                person1.setId(896);
                person1.setName("vali");
                person1.setFamily("hassanpoor");
                person1.setPassword(12579);
                person.addPerson(person1);
}

Commit on jdbcTemplate or DataSource - spring - html, I wanted to do commit and rollback using jdbcTemplate. My question is based on this thread How do I commit or rollback, should I do it on jdbcTemplate like  We are using JDBCTemplate in our code. Below is the sample code. Issue is that we want to make the autocommit as off/false but it is autocommitting each and every individual statement. Please let me know how to make autocommit as false using JDBCTemplate in Spring JDBC. Also, can we configure this in the XML?

Spring JdbcTemplate with Autocommit, Since JdbcTemplate is configured with a DataSource, which is in turn update() calls in JdbcTemplate and commit() them on completion. You will have to do for each statement that the jdbcTemplate executes. Because for each jdbcTemplate.execute() etc it gets a new connection from the Datasource's connection pool. So you will have to set it for the connection that the connection the jdbcTemplate uses for that query. So you will have to do something like

Programmatic Transaction Management, Now, let us write our Spring JDBC application which will implement simple DataSource; public interface StudentDAO { /** * This is the method to be used to + age); transactionManager.commit(status); } catch (DataAccessException e)  Following is the declaration for org.springframework.jdbc.core.JdbcTemplate class − public class JdbcTemplate extends JdbcAccessor implements JdbcOperations Usage. Step 1 − Create a JdbcTemplate object using a configured datasource. Step 2 − Use JdbcTemplate object methods to make database operations. Example

DataSourceTransactionManager (Spring Framework 5.2.6 , PlatformTransactionManager implementation for a single JDBC DataSource . This class is capable Spring classes such as JdbcTemplate use this strategy implicitly. If not used in Perform an actual commit of the given transaction. protected  Thanks for contributing an answer to Stack Overflow! Please be sure to answer the question. Provide details and share your research! But avoid … Asking for help, clarification, or responding to other answers. Making statements based on opinion; back them up with references or personal experience. To learn more, see our tips on writing great

Comments
  • Use Spring's @Transactional transaction management.
  • Thanks. Do I need to set <property name="defaultAutoCommit" value="false" /> at data source level?
  • Why don't you like calling commits programmatically?
  • no need to configure dataSourceTransactionManager in SpringBoot 2
  • Thanks. Do I need to set <property name="defaultAutoCommit" value="false" /> at data source level?
  • no, you need just configure txManager-bean in you spring context