In this tutorial you are going to learn what the JDBC module is and hopefully you will be able to find use cases after you are done reading it.
Now, let’s create a very simple table that represents an employee.
CREATE TABLE Employee ( ID INT NOT NULL AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, DEPARTMENT VARCHAR(20) NOT NULL, PRIMARY KEY(ID) );
Spring JDBC Frameworks saves us a lot of time and effort as it takes care of the low-level details such as initialization of a connection, executing SQL queries, closing a connection, etc. In that case you might be wondering what is left for us to do. Well, we have to define connection parameters and also specify what SQL query we want to execute and finally, we have to write the logic for all iterations while fetching data from our database.
There are many approaches and already written classes that help us implementing JDBC. In our case, we will stick to the classic JDBC Template class. It will manage all the database communication.
What do you need to know about JDBC Template class? Well, in a nutshell, it catches JDBC exceptions, executes SQL queries and update statements. It is also important to note that all instances of JDBC Template class are thread-safe so that means we can configure an instance of a JDBC Template class and inject it as a shared reference into multiple DAOs safely.
If you are not familiar with what DAO is, you can check my article on the topic.
Now to the fun part. Let;s see how we can implement all of the theory. We are going to be using the table shown in the beginning of that tutorial.
EmployeeDAO.java
package com.tutorialnet;
import java.util.List;
import javax.sql.DataSource;
public interface EmployeeDAO {
public void setDataSource(DataSource ds);
public void create(String name, Integer age, String department);
public Employee getEmployee(Integer id);
public List<Employee> getEmployees();
public void delete(Integer id);
public void update(Integer id, Integer age, String department);
}
This is our DAO interface. It contains all the method declarations and all of these method declarations have to do with the CRUD functionality.
- setDataSource(): establishes database connection.
- create(): will be used to create a new Employee entry in the database.
- getEmployee(): will return an employee based on the ID provided.
- getEmployees(): will return a list of all employees in the database.
- delete(): will delete an employee based on the ID provided.
- update(): will update existing employee.
Employee.java
package com.javatutorial;
public class Employee {
private Integer id;
private String name;
private Integer age;
private String department;
public void setId(Integer id) {
this.id = id;
}
public void setAge(Integer age) {
this.age = age;
}
public void setName(String name) {
this.name = name;
}
public void setDepartment(String department) {
this.department = department;
}
public Integer getId() {
return this.id;
}
public Integer getAge() {
return this.age;
}
public String getName() {
return this.name;
}
public String getDepartment() {
return this.department;
}
}
EmployeeMapper.java
package com.javatutorial;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class EmployeeMapper implements RowMapper<Employee> {
public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
Employee employee = new Employee();
employee.setId(rs.getInt("id"));
employee.setName(rs.getString("name"));
employee.setAge(rs.getInt("age"));
employee.setDepartment(rs.getString("department"));
return employee;
}
}
EmployeeJDBCTemplate.java
package com.javatutorial;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class EmployeeJDBCTemplate implements EmployeeDAO {
private DataSource dataSource;
private JdbcTemplate jdbcTemplateObject;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplateObject = new JdbcTemplate(dataSource);
}
public void create(String name, Integer age, String department) {
String SQL = "insert into Employee (name, age, department) values (?, ?)";
jdbcTemplateObject.update( SQL, name, age, department);
}
public Employee getEmployee(Integer id) {
String SQL = "select * from Employee where id = ?";
Employee employee = jdbcTemplateObject.queryForObject(SQL,
new Object[]{id}, new EmployeeMapper());
return employee;
}
public List<Employee> getEmployees() {
String SQL = "select * from Employee";
List <Employee> employees = jdbcTemplateObject.query(SQL, new EmployeeMapper());
return employees;
}
public void delete(Integer id) {
String SQL = "delete from Employee where id = ?";
jdbcTemplateObject.update(SQL, id);
System.out.println("Deleted Record with ID = " + id );
}
public void update(Integer id, Integer age){
String SQL = "update Employee set age = ? where id = ?";
jdbcTemplateObject.update(SQL, age, id);
}
}
This JDBC class defines all the method declarations from the interface we defined above.
Main.java
package com.javatutorial;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.tutorialspoint.EmployeeJDBCTemplate;
public class Main {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
EmployeeJDBCTemplate employeeJDBCTemplate = (EmployeeJDBCTemplate)context.getBean("employeeJDBCTemplate");
System.out.println("Creating records..." );
employeeJDBCTemplate.create("Jack", 11, "Software engineering");
employeeJDBCTemplate.create("Joanna", 2, "Finance");
employeeJDBCTemplate.create("Derek", 15, "Hardware engineering");
System.out.println("Listing employee entries from the database..");
List<Employee> employees = employeeJDBCTemplate.getEmployees();
for (Employee employee : employees) {
System.out.print("ID: " + employee.getId());
System.out.print("Name: " + employee.getName());
System.out.println("Age: " + employee.getAge());
System.out.println("Age: " + employee.getDepartment());
}
System.out.println("Updating a record with an id of 1");
employeeJDBCTemplate.update(1, 29, "Marketing");
System.out.println("Displaying information about record with an id of 1");
Employee employee = employeeJDBCTemplate.getEmployee(1);
System.out.print("ID: " + employee.getId());
System.out.print("Name : " + employee.getName() );
System.out.println("Age : " + employee.getAge());
ystem.out.println("Department : " + employee.getDepartment());
}
}
Here we are calling all of the methods that we defined in our interface.
The configuration file for Beans.xml:
<?xml version = "1.0" encoding = "UTF-8"?>
<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-3.0.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/DEMO"/>
<property name = "username" value = "root"/>
<property name = "password" value = "admin123"/>
</bean>
<bean id = "employeeJDBCTemplate"
class = "com.javatutorial.EmployeeJDBCTemplate">
<property name = "dataSource" ref = "dataSource" />
</bean>
</beans>