String SQL = "select count(*) from Employee"; int rowCount = jdbcTemplateObject.queryForInt( SQL );Querying for a long:
String SQL = "select count(*) from Employee"; long rowCount = jdbcTemplateObject.queryForLong( SQL );A simple query using a bind variable:
String SQL = "select salary from Employee where empid = ?"; long salary = jdbcTemplateObject.queryForLong(SQL, new Object[]{10000});Querying for a String:
String SQL = "select name from Employee where empid = ?"; String name = jdbcTemplateObject.queryForObject(SQL, new Object[]{10000}, String.class);Querying and returning an object:
String SQL = "select * from Employee where empid = ?"; Employee employee = jdbcTemplateObject.queryForObject(SQL, new Object[]{10000}, new EmployeeMapper()); public class EmployeeMapper implements RowMapper<Employee> { public Employee mapRow(ResultSet rs, int rowNum) throws SQLException { Employee employee = new Employee(); employee.setEmpid(rs.getInt("empid")); employee.setName(rs.getString("name")); employee.setAge(rs.getInt("age")); employee.setSalary(rs.getLong("salary")); return employee; } }Querying and returning multiple objects:
String SQL = "select * from Employee"; List<Employee> employee = jdbcTemplateObject.query(SQL, new EmployeeMapper()); public class EmployeeMapper implements RowMapper<Employee> { public Employee mapRow(ResultSet rs, int rowNum) throws SQLException { Employee employee = new Employee(); employee.setEmpid(rs.getInt("empid")); employee.setName(rs.getString("name")); employee.setAge(rs.getInt("age")); employee.setSalary(rs.getLong("salary")); return employee; } }Inserting a row into the table:
String SQL = "insert into Employee (name, age) values (?, ?)"; jdbcTemplateObject.update( SQL, new Object[]{"Dinesh", 25} );Updating a row into the table:
String SQL = "update Employee set name = ? where empid = ?"; jdbcTemplateObject.update( SQL, new Object[]{"Dinesh", 10000} );Deletng a row from the table:
String SQL = "delete Employee where empid = ?"; jdbcTemplateObject.update( SQL, new Object[]{10000} );Executing DDL Statements: You can use the execute(..) method from jdbcTemplate to execute any SQL statements or DDL statements. Following is an example to use CREATE statement to create a table:
String SQL = "CREATE TABLE Employee( " + "EMPID INT NOT NULL AUTO_INCREMENT, " + "NAME VARCHAR(20) NOT NULL, " + "AGE INT NOT NULL, " + "SALARY BIGINT NOT NULL, " + "PRIMARY KEY (EMPID));" jdbcTemplateObject.execute( SQL );
package com.dineshonjava.sdnext.domain; /** * @author Dinesh Rajput * */ public class Employee { private int empid; private String name; private int age; private long salary; /** * @return the empid */ public int getEmpid() { return empid; } /** * @param empid the empid to set */ public void setEmpid(int empid) { this.empid = empid; } /** * @return the name */ public String getName() { return name; } /** * @param name the name to set */ public void setName(String name) { this.name = name; } /** * @return the age */ public int getAge() { return age; } /** * @param age the age to set */ public void setAge(int age) { this.age = age; } /** * @return the salary */ public long getSalary() { return salary; } /** * @param salary the salary to set */ public void setSalary(long salary) { this.salary = salary; } public String toString(){ return "EMPLOYEE{empid- "+this.empid+" name- "+this.name+ " age- "+this.age+" salary- "+this.salary+"}"; } }Step 4: Create Employee DAO interface for the abstarct functionality with employee table.
package com.dineshonjava.sdnext.dao; import java.util.List; import com.dineshonjava.sdnext.domain.Employee; /** * @author Dinesh Rajput * */ public interface EmpDao { /** * This is the method to be used to create * a record in the Employee table. */ void create(String name, Integer age, Long salary); /** * This is the method to be used to list down * a record from the Employee table corresponding * to a passed Employee id. */ Employee getEmployee(Integer empid); /** * This is the method to be used to list down * all the records from the Employee table. */ List listEmployees(); /** * This is the method to be used to delete * a record from the Employee table corresponding * to a passed Employee id. */ void delete(Integer empid); /** * This is the method to be used to update * a record into the Employee table. */ void update(Integer empid, Integer age); }Step 5: Create Employee DAO implementation for the abstarct functionality of EmpDao.
package com.dineshonjava.sdnext.dao.impl; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Component; import com.dineshonjava.sdnext.dao.EmpDao; import com.dineshonjava.sdnext.domain.Employee; import com.dineshonjava.sdnext.jdbc.utils.EmployeeMapper; /** * @author Dinesh Rajput * */ @Component public class EmployeeDaoImpl implements EmpDao { @Autowired private JdbcTemplate jdbcTemplateObject; /** * @param jdbcTemplateObject the jdbcTemplateObject to set */ public void setJdbcTemplateObject(JdbcTemplate jdbcTemplateObject) { this.jdbcTemplateObject = jdbcTemplateObject; } @Override public void create(String name, Integer age, Long salary) { String SQL = "INSERT INTO Employee (name, age, salary) VALUES (?, ?, ?)"; jdbcTemplateObject.update(SQL, new Object[]{name, age, salary} ); System.out.println("Created Record Name = " + name + " Age = " + age+ " Salary = " + salary); } @Override public Employee getEmployee(Integer empid) { String SQL = "SELECT * FROM Employee WHERE empid = ?"; Employee employee = (Employee) jdbcTemplateObject.queryForObject(SQL, new Object[]{empid}, new EmployeeMapper()); return employee; } @Override public List listEmployees() { String SQL = "SELECT * FROM Employee"; List employees = (List) jdbcTemplateObject.query(SQL, new EmployeeMapper()); return employees; } @Override public void delete(Integer empid) { String SQL = "DELETE FROM Employee WHERE empid = ?"; jdbcTemplateObject.update(SQL, new Object[]{empid}); System.out.println("Deleted Record with EMPID = " + empid ); } @Override public void update(Integer empid, Integer age) { String SQL = "UPDATE Employee SET age = ? WHERE empid = ?"; jdbcTemplateObject.update(SQL, new Object[]{age, empid}); System.out.println("Updated Record with EMPID = " + empid ); } }Step 6: Create EmployeeMapper class which map the row of table the Employee class which implements the org.springframework.jdbc.core.RowMapper interface of the jdbc core API.
package com.dineshonjava.sdnext.jdbc.utils; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; import com.dineshonjava.sdnext.domain.Employee; /** * @author Dinesh Rajput * */ public class EmployeeMapper implements RowMapper { public Employee mapRow(ResultSet rs, int rowNum) throws SQLException { Employee employee = new Employee(); employee.setEmpid(rs.getInt("empid")); employee.setName(rs.getString("name")); employee.setAge(rs.getInt("age")); employee.setSalary(rs.getLong("salary")); return employee; } }Step 7:Create the configuration file for the bean configuratin
<beans xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p" xmlns:security="http://www.springframework.org/schema/security" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans" xsi:schemalocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd http://www.springframework.org/schema/security http://www.springframework.org/schema/security/spring-security-2.0.4.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd"> <context:annotation-config></context:annotation-config> <context:component-scan base-package="com.dineshonjava.sdnext.dao.impl"> </context:component-scan> <bean class="org.apache.commons.dbcp.BasicDataSource" id="dataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://localhost:3306/DAVDB"></property> <property name="username" value="root"></property> <property name="password" value="root"></property> <property name="initialSize" value="2"></property> <property name="maxActive" value="5"></property> </bean> <bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplateObject"> <property name="dataSource" ref="dataSource"></property> </bean> </beans>Step 8: Finally we have to create main class for execution this application
package com.dineshonjava.sdnext.main; import java.util.List; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.dineshonjava.sdnext.dao.EmpDao; import com.dineshonjava.sdnext.domain.Employee; /** * @author Dinesh Rajput * */ public class EmpMainApp { /** * @param args */ public static void main(String[] args) { ApplicationContext context = new ClassPathXmlApplicationContext("spring.xml"); EmpDao empDao = (EmpDao) context.getBean("employeeDaoImpl"); System.out.println("------Records Creation--------" ); empDao.create("Dinesh", 25, 50000l); empDao.create("Anamika", 23, 30000l); empDao.create("Nimmo", 24, 30020l); empDao.create("Adesh", 24, 30011l); empDao.create("Vinesh", 22, 20011l); System.out.println("------Listing Multiple Records--------" ); List employees = empDao.listEmployees(); for (Employee employee : employees) { System.out.print(employee); } System.out.println("----Updating Record with EMPID = 2 -----" ); empDao.update(2, 20); System.out.println("----Listing Record with ID = 2 -----" ); Employee employee = empDao.getEmployee(2); System.out.print(employee); } }Once you are done with creating source and bean configuration files, let us run the application. If everything is fine with your application, this will print the following message on the console:
Labels: Spring3.0