SqlUpdate in Spring JDBC
March 04, 2023
On this page, we will learn to use SqlUpdate
in our Spring JDBC application.
1. The
SqlUpdate
is reusable operation object representing an SQL update.
2. The
SqlUpdate
is a concrete class and provides update
method. The update
method is a convenient method to execute an update.
3. To add a custom update, we need to create a subclass of
SqlUpdate
.
4. In the subclass constructor, we set sql query, sql parameters and call
compile()
method of SqlUpdate
.
5. Finally
update
method is called to update the rows for the given input data.
Complete Example
CompanySqlUpdate.javapackage com.concretepage; import java.sql.Types; import javax.sql.DataSource; import org.springframework.jdbc.core.SqlParameter; import org.springframework.jdbc.object.SqlUpdate; public class CompanySqlUpdate extends SqlUpdate { public CompanySqlUpdate(DataSource ds) { setDataSource(ds); setSql("update company set location = ? where id = ?"); declareParameter(new SqlParameter("location", Types.VARCHAR)); declareParameter(new SqlParameter("id", Types.NUMERIC)); compile(); } public int execute(int id, String location) { return update(location, id); } }
package com.concretepage.dao; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; import com.concretepage.CompanySqlUpdate; @Repository public class CompanyDao { private CompanySqlUpdate compSqlUpdate; @Autowired public void setDataSource(DataSource dataSource) { this.compSqlUpdate = new CompanySqlUpdate(dataSource); } public int updateCompany(int id) { return compSqlUpdate.execute(1, "Bhadohi"); } }
package com.concretepage; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.concretepage.dao.CompanyDao; public class SpringTest { public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("spring.xml"); CompanyDao companyDao = (CompanyDao)context.getBean("companyDao"); int i = companyDao.updateCompany(1); System.out.println(i); } }
CREATE TABLE `company` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NULL DEFAULT NULL, `location` VARCHAR(50) NULL DEFAULT NULL, `no_of_emp` INT(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) )