SqlUpdate Spring JDBC Example
March 04, 2023
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.java
package 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`) )
