Before we delve into the details of Spring's way of communicating with database, we would require some sample data to quick start our work. This mysql script will do that job for you
To connect with a database we can either adopt one of the two techniques i.e. Datasource or the DriverManager. Here we are using datasource way to communicate with database. The DriverManager is older facility, DataSource is newer. It is recommended to use the new DataSource facility to connect to databases and other resources. DataSource facility has several advantages over DriverManager facility like DataSource increases portability, enables connection pooling and distributed transactions, the DriverManager does not allow such techniques.
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3306/apu"></property>
<property name="username" value="root"></property>
<property name="password" value=""></property>
</bean>
NOTE! Dont forget to change the username and password in the above bean accordingly.
Spring JdbcTemplate is a class that takes care of all the boilerplate code required for creating a database connection and releasing the resources. It makes our life a lot easier by saving the effort and development time. To initialize the object of JdbcTemplate, we will use the datasource bean defined above. Once created, we could easily inject its reference to any class that wants to communicate with database. For example: UserDao.java
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="userDao" class="springjdbc.simpledbconnectivity.UserDao">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
Spring JdbcTemplate exposes many helpful methods for performing CRUD operations on database. Following are most common methods that we use from JdbcTemplate.
| Method Name | Use |
|---|---|
| execute(String sql) | Issue a single SQL execute, typically a DDL statement. |
| queryForList(String sql, Object[] args) | Query given SQL to create a prepared statement from SQL and a list of arguments to bind to the query, expecting a result list. |
| update(String sql) | Issue a single SQL update operation (such as an insert, update or delete statement). |
Now lets bring our JdbcTemplate to action. We will use the JdbcTemplate bean in our DAO class or any class that will communicate with our database. Lets say we have a UserDao class that is responsible for inserting and deleting a user
/**
* @author achauhan
*/
public class UserDao implements IUserDao {
private JdbcTemplate jdbcTemplate;
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public void deleteUser(int uid) {
String delQuery = "delete from users where id = ?";
int count = jdbcTemplate.update(delQuery, new Object[] { uid });
if(count!=0)
System.out.println("User deleted successfully.");
else
System.out.println("Couldn't delete user with given id as it doesn't exist");
}
public int insertUser(User user) {
String inserQuery = "insert into users (username, password, enabled , id) values (?, ?, ?, ?) ";
Object[] params = new Object[] { user.getUserName(),
user.getPassword(), user.isEnabled(), user.getId() };
int[] types = new int[] { Types.VARCHAR, Types.VARCHAR, Types.BIT,
Types.INTEGER };
return jdbcTemplate.update(inserQuery, params, types);
}
Finally below is our Test class that will load the above defined beans from a xml file. It will get the userDao object and use it for performing CRUD operations
public static void main(String[] args) throws ClassNotFoundException {
ApplicationContext applicationContext = new ClassPathXmlApplicationContext(
"springjdbc/simpledbconnectivity/jdbcContext.xml");
IUserDao dao = applicationContext.getBean("userDao", IUserDao.class);
int userId = generateId();
User user = new User(userId, "apurav", "12345", false);
dao.insertUser(user);
System.out.println("User inserted with id= " + userId);
dao.deleteUser(984);
}