How to put a lock and transaction on table using spring 4 or above using jdbcTemplate and annotations like @Transactional? -
i trying put lock on table while writing on table , if happened in between roll-back .
trying convert below code
lock table test_g1 read; lock table test_g write; -- begin; start transaction; insert test_g1 values(143); insert test_g values(145); select * test_g1; select * test_g; rollback; select * test_g; unlock tables; how convert above code @transactional spring jdbctemplate code?
@transactional(rollbackfor={dataaccessexception.class}) public void test(){ jdbctemplate.execute("insert test1 (id, nam) values (4, 'a')"); throw new dataaccessexception("error") { }; } here trying throw error, insert statement should rollback it's not happening .
thanks
edit-1 attaching code , doing
in jdbcdaoimpl.java , mentioned problem comment above test() .
app.java
package com.cgiri.javabrains.spring4; import org.springframework.beans.factory.annotation.autowired; import org.springframework.context.applicationcontext; import org.springframework.context.annotation.annotationconfigapplicationcontext; import org.springframework.transaction.annotation.transactional; public class app { public static void main( string[] args ) { annotationconfigapplicationcontext ctx = new annotationconfigapplicationcontext(appconfig.class); app2 app2 = ctx.getbean("app2",app2.class); app2.call(); } } app2.java
package com.cgiri.javabrains.spring4; import org.springframework.beans.beansexception; import org.springframework.beans.factory.annotation.autowired; import org.springframework.context.applicationcontext; import org.springframework.context.annotation.annotationconfigapplicationcontext; import org.springframework.stereotype.component; import org.springframework.transaction.annotation.transactional; @component public class app2 { @autowired private applicationcontext ctx = null; jdbcdaoimpl jdbcdaoimpl ; public void call( ) { jdbcdaoimpl = ctx.getbean("jdbcdaoimpl",jdbcdaoimpl.class); system.out.println(jdbcdaoimpl.getcount()); try{ jdbcdaoimpl.test(); }catch(exception e) { } system.out.println(jdbcdaoimpl.getcount()); } public void setapplicationcontext(applicationcontext context) throws beansexception { this.ctx = context; } } jdbcdaoimpl.java
package com.cgiri.javabrains.spring4; import java.sql.sqlexception; import javax.sql.datasource; import org.springframework.beans.factory.annotation.autowired; import org.springframework.dao.dataaccessexception; import org.springframework.jdbc.core.jdbctemplate; import org.springframework.stereotype.component; import org.springframework.transaction.annotation.transactional; @component @transactional public class jdbcdaoimpl { private jdbctemplate jdbctemplate; private datasource datasource; public datasource getdatasource() { return datasource; } @autowired public void setdatasource(datasource datasource) { this.jdbctemplate = new jdbctemplate(datasource); } public int getcount() { string sql = "select count(*) test1"; // jdbctemplate.setdatasource(getdatasource()); return jdbctemplate.queryforobject(sql, integer.class); } public void cratetable() { string sql = "create table if not exists test1 (id integer, nam char(50))"; jdbctemplate.execute(sql); jdbctemplate.execute("insert test1 (id, nam) values (1, 'a')"); int count = jdbctemplate.queryforobject("select count(*) test1",integer.class); system.out.println(count); } /**** point trying rollback insert query ,but it's not happening , instead it's inserting data table , throwing exception , rollback not happeneing ****/ @transactional(rollbackfor={dataaccessexception.class}) public void test(){ jdbctemplate.execute("insert test1 (id, nam) values (4, 'a')"); throw new dataaccessexception("error") { }; } } appconfig.java
package com.cgiri.javabrains.spring4; import javax.sql.datasource; import org.apache.commons.dbcp.basicdatasource; import org.springframework.beans.factory.annotation.autowired; import org.springframework.context.annotation.bean; import org.springframework.context.annotation.componentscan; import org.springframework.context.annotation.configuration; import org.springframework.context.annotation.propertysource; import org.springframework.core.env.environment; import org.springframework.jdbc.core.jdbctemplate; import org.springframework.jdbc.core.namedparam.namedparameterjdbctemplate; import org.springframework.jdbc.core.simple.simplejdbccall; import org.springframework.jdbc.datasource.datasourcetransactionmanager; import org.springframework.transaction.platformtransactionmanager; import org.springframework.transaction.transactiondefinition; import org.springframework.transaction.transactionexception; import org.springframework.transaction.transactionstatus; import org.springframework.transaction.support.transactiontemplate; @configuration @componentscan({ "com.cgiri.javabrains.spring4" }) @propertysource("classpath:db.properties") public class appconfig { private jdbctemplate jdbctemplate; private transactiontemplate transactiontemplate; @autowired private environment env; @bean public basicdatasource getbasicdatasource() { basicdatasource dao = new basicdatasource(); dao.setdriverclassname(env.getproperty("db.driverclassname")); dao.seturl(env.getproperty("db.url")); dao.setusername(env.getproperty("db.username")); dao.setpassword(env.getproperty("db.password")); dao.setinitialsize(2); dao.setmaxactive(5); return dao; } @bean public datasourcetransactionmanager gettransactionmanager(basicdatasource datasource) { datasourcetransactionmanager manager = new datasourcetransactionmanager(datasource); return manager; } @autowired public void setdatasource(datasource datasource) { this.jdbctemplate = new jdbctemplate(datasource); } } and locking table during dml query, if 2 or more people updating simultaneously on table , locking mechanism taken care mysql server or have configure separately way doing transactions ?
thanks
if don't want use @transactional try use transactiontemplate , this:
import org.springframework.jdbc.core.jdbctemplate; import org.springframework.stereotype.component; import org.springframework.beans.factory.annotation.autowired; import org.springframework.transaction.transactionstatus; import org.springframework.transaction.support.transactioncallbackwithoutresult; import org.springframework.transaction.support.transactiontemplate; @component public simpledao { @autowired private jdbctemplate jdbctemplate; @autowired private transactiontemplate transactiontemplate; private void executetransactionwithoutresult(dbtransactiontask dbtask) { transactiontemplate.execute(new transactioncallbackwithoutresult() { @override protected void dointransactionwithoutresult(transactionstatus transactionstatus) { dbtask.executetask(); } }); } public void test() { dbtransactiontask dbtask = new dbtransactiontask() { @override public void executetask() { try { jdbctemplate.execute("lock tables entry write;"); jdbctemplate.execute("..."); jdbctemplate.execute("..."); jdbctemplate.execute("unlock tables;") } catch (exception e) { // cause rollback of transaction throw new runtimeexception("reverting db operations: " + e.getclass().getsimplename() + " - " + e.getmessage(), e); } } }; executetransactionwithoutresult(dbtask); } abstract class dbtransactiontask { public abstract void executetask(); } } edit: @transactional try this:
import org.springframework.beans.factory.annotation.autowired; import org.springframework.boot.springapplication; import org.springframework.boot.autoconfigure.springbootapplication; import org.springframework.context.configurableapplicationcontext; import org.springframework.transaction.annotation.transactional; import dao.entrydao; @springbootapplication public class springtransactional { private configurableapplicationcontext springcontext; @autowired private entrydao dao; public void init() { springcontext = springapplication.run(springtransactional.class); springcontext.getautowirecapablebeanfactory().autowirebean(this); } public static void main(string[] args) { springtransactional st = new springtransactional(); try { st.init(); dao.db_transaction_test(); } catch (runtimeexception e) { e.printstacktrace(); } { st.springcontext.close(); } } } where entrydao is:
package dao; import java.sql.resultset; import java.sql.sqlexception; import java.util.list; /** * db creation , schema: * create database db_name; * create user db_username; * <p> * use db_name; * grant on db_name.* db_username; * <p> * set password spz = password('username123'); * flush privileges; * <p> * create table entry ( * entry_id int not null auto_increment, * name text not null, * <p> * primary key (entry_id) * ); */ @component public class entrydao { /** * application.properties: * spring.datasource.driver-class-name = com.mysql.jdbc.driver * spring.datasource.url = jdbc:mysql://localhost:3306/db_name?usessl=false&servertimezone=utc * spring.datasource.username = db_username * spring.datasource.password = username123 */ @autowired private jdbctemplate jdbctemplate; @transactional public void db_transaction_test() { jdbctemplate.execute("lock tables entry write;"); (int = 0; < 10; i++) { try { int entry_name = getentryid("entry_" + i); system.out.println("created entry id=" + entry_name); } catch (entrydao.daoexception e) { e.printstacktrace(); } if (i == 5) { throw new runtimeexception("testing data upload procedure break."); } } jdbctemplate.execute("unlock tables;") } public int getentryid(string entryname) throws daoexception { list<dbentry> dbentries = retrieveentriesfor(entryname); if (dbentries.size() == 1) { return dbentries.get(0).getentry_id(); } else if (dbentries.size() == 0) { string sqlinsert = "insert entry (name) values (?)"; jdbctemplate.update(sqlinsert, entryname); dbentries = retrieveentriesfor(entryname); if (dbentries.size() == 1) { return dbentries.get(0).getentry_id(); } else { throw new daoexception("invalid results amount received after creating new (" + dbentries.size() + ") when getting entry name: " + entryname); } } else { throw new daoexception("invalid results amount received (" + dbentries.size() + ") when getting entry name: " + entryname); } } private list<dbentry> retrieveentriesfor(string entryname) { return jdbctemplate.query("select * entry name=?;", (resultset result, int rownum) -> unmarshal(result), entryname); } private dbentry unmarshal(resultset result) throws sqlexception { dbentry dbentry = new dbentry(); dbentry.setentry_id(result.getint("entry_id")); dbentry.setname(result.getstring("name")); return dbentry; } public class dbentry { private int entry_id; private string name; int getentry_id() { return entry_id; } void setentry_id(int entry_id) { this.entry_id = entry_id; } public string getname() { return name; } public void setname(string name) { this.name = name; } } public class daoexception extends throwable { daoexception(string err_msg) { super(err_msg); } } }
Comments
Post a Comment