https://www.youtube.com/watch?v=qKzoZubuBL8&list=UUhwKlOVR041tngjerWxVccw
Click the below Image to Enlarge:
 |
| Spring + JdbcTemplate + Update the rows with multiple parameters with object array example |
CREATE DATABASE org_db;
CREATE TABLE `employee` (
`EMPLOYEE_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR(100) NOT NULL,
`AGE` INT(10) NOT NULL,
`SALARY` INT(10) DEFAULT NULL,
PRIMARY KEY (`EMPLOYEE_ID`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `employee` (`EMPLOYEE_ID`, `NAME`, `AGE`, `SALARY`) VALUES('1','Peter','28','80000');
INSERT INTO `employee` (`EMPLOYEE_ID`, `NAME`, `AGE`, `SALARY`) VALUES('2','John','50','40000');
INSERT INTO `employee` (`EMPLOYEE_ID`, `NAME`, `AGE`, `SALARY`) VALUES('3','David','45','20000');
CREATE TABLE `employee` (
`EMPLOYEE_ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR(100) NOT NULL,
`AGE` INT(10) NOT NULL,
`SALARY` INT(10) DEFAULT NULL,
PRIMARY KEY (`EMPLOYEE_ID`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `employee` (`EMPLOYEE_ID`, `NAME`, `AGE`, `SALARY`) VALUES('1','Peter','28','80000');
INSERT INTO `employee` (`EMPLOYEE_ID`, `NAME`, `AGE`, `SALARY`) VALUES('2','John','50','40000');
INSERT INTO `employee` (`EMPLOYEE_ID`, `NAME`, `AGE`, `SALARY`) VALUES('3','David','45','20000');
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.ram.core</groupId>
<artifactId>SpringDemo</artifactId>
<packaging>jar</packaging>
<version>1.0-SNAPSHOT</version>
<name>SpringDemo</name>
<url>http://maven.apache.org</url>
<properties>
<spring.version>5.0.5.RELEASE</spring.version>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<!-- Spring 5 dependencies -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- MySQL database driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
</dependencies>
</project>
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.ram.core</groupId>
<artifactId>SpringDemo</artifactId>
<packaging>jar</packaging>
<version>1.0-SNAPSHOT</version>
<name>SpringDemo</name>
<url>http://maven.apache.org</url>
<properties>
<spring.version>5.0.5.RELEASE</spring.version>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<!-- Spring 5 dependencies -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<!-- MySQL database driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
</dependencies>
</project>
EmployeeDAO.java
package com.ram.employee.dao;
public interface EmployeeDAO
{
public int updateEmployeeNameBasedOnAge(String name, int age);
}
public interface EmployeeDAO
{
public int updateEmployeeNameBasedOnAge(String name, int age);
}
EmployeeDAOImpl.java
package com.ram.employee.dao.impl;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import com.ram.employee.dao.EmployeeDAO;
public class EmployeeDAOImpl extends JdbcDaoSupport implements EmployeeDAO
{
public int updateEmployeeNameBasedOnAge(String name, int age)
{
String sql = "update Employee set Name=? where AGE=?";
int numberOfRowsAffected = getJdbcTemplate().update(sql,
new Object[] { name, age });
return numberOfRowsAffected;
}
}
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import com.ram.employee.dao.EmployeeDAO;
public class EmployeeDAOImpl extends JdbcDaoSupport implements EmployeeDAO
{
public int updateEmployeeNameBasedOnAge(String name, int age)
{
String sql = "update Employee set Name=? where AGE=?";
int numberOfRowsAffected = getJdbcTemplate().update(sql,
new Object[] { name, age });
return numberOfRowsAffected;
}
}
applicationContext.xml
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/org_db" />
<property name="username" value="root" />
<property name="password" value="root" />
</bean>
<bean id="employeeDAO" class="com.ram.employee.dao.impl.EmployeeDAOImpl">
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/org_db" />
<property name="username" value="root" />
<property name="password" value="root" />
</bean>
<bean id="employeeDAO" class="com.ram.employee.dao.impl.EmployeeDAOImpl">
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
App.java
package com.ram.core;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.ram.employee.dao.EmployeeDAO;
public class App
{
public static void main(String[] args)
{
ApplicationContext context = new ClassPathXmlApplicationContext(
"applicationContext.xml");
EmployeeDAO employeeDAO = (EmployeeDAO) context.getBean("employeeDAO");
int numberOfRowsAffected = employeeDAO
.updateEmployeeNameBasedOnAge("Juli", 28);
System.out.println("numberOfRowsAffected = " + numberOfRowsAffected);
System.out.println("Employee name updated successfully.");
}
}
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.ram.employee.dao.EmployeeDAO;
public class App
{
public static void main(String[] args)
{
ApplicationContext context = new ClassPathXmlApplicationContext(
"applicationContext.xml");
EmployeeDAO employeeDAO = (EmployeeDAO) context.getBean("employeeDAO");
int numberOfRowsAffected = employeeDAO
.updateEmployeeNameBasedOnAge("Juli", 28);
System.out.println("numberOfRowsAffected = " + numberOfRowsAffected);
System.out.println("Employee name updated successfully.");
}
}
Output:
Feb 14, 2019 10:42:47 AM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@35d176f7: startup date [Thu Feb 14 10:42:47 IST 2019]; root of context hierarchy
Feb 14, 2019 10:42:47 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [applicationContext.xml]
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Feb 14, 2019 10:42:48 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Thu Feb 14 10:42:57 IST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
numberOfRowsAffected = 1
Employee name updated successfully.
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@35d176f7: startup date [Thu Feb 14 10:42:47 IST 2019]; root of context hierarchy
Feb 14, 2019 10:42:47 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [applicationContext.xml]
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Feb 14, 2019 10:42:48 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Thu Feb 14 10:42:57 IST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
numberOfRowsAffected = 1
Employee name updated successfully.
Click the below link to download the code:
https://sites.google.com/site/javaspringram2019/java_spring_2019/SpringDemo_update_object_array.zip?attredirects=0&d=1
Github Link:
https://github.com/ramram43210/Java_Spring_2019/tree/master/Spring_2019/SpringDemo_update_object_array
Bitbucket Link:
https://bitbucket.org/ramram43210/java_spring_2019/src/8f8b5563b7f2921ed260908e4906103995d9c6cc/Spring_2019/SpringDemo_update_object_array/?at=master
See also:
