Spring JDBC

By arunraj

In this section, we will discuss about how to perform (CRUD) operations in relational database using Spring JDBC.

For Example, Lets assume there exists a USERS table in a database with columns such as USER_ID, FIRST_NAME,LAST_NAME,USER_NAME, PASSWORD, COUNTRY, EMAIL AND STATE. Obviously USER_ID is the primary key(unique identifier).

Lets see how to perform DB operations in the USER table one by one.

Define a USERDao interface and USER bean(POJO) as follows

UserDao Interface

package in.techdive.spring.examples;

public interface UserDao
{
long addUser(User usr);
void updateUser(User usr);
void deleteUser(User usr);
List getAllUserList();
}

User Class

/**
* THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
* TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
* PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*/
package in.techdive.spring.examples;

import java.io.Serializable;

public class User implements Serializable
{
private static final long serialVersionUID = 1996315795257623893L;
private long userId;
private String userName;
private String passWord;
private String email;
private String firstName;
private String lastName;
private String country;
private String state;

public User()
{
}

// add getter/setter methods for above member variables here

public boolean equals(Object obj)
{

if (obj instanceof User)
{
User us = (User) obj;
if (us.getUserId() == this.getUserId())
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}

public int hashCode()
{
return super.hashCode() + (int) this.getUserId();
}
}

1. Adding a row to the DB table

To add a row to the USER table, we need a way to create the USER_ID unique identifier. It can be done in two ways,

1. By using the query SELECT max(USER_ID)+1 from USERS to get the max of user_id and adding to it.

UserDaoJdbc Class:

public class UserDaoJdbc extends SimpleJdbcDaoSupport implements UserDao
{
public long addUser(User usr)
{
long userId = usr.getUserId();
if (userId == 0)
{
//User Id was not explicitly set, so try to find an appropriate user id
String sqlCount = "SELECT MAX(ID) + 1 FROM USERS_TAB";
userId = getSimpleJdbcTemplate().queryForInt(sqlCount, new Object[0]);
System.out.println(userId);
}

final String sql = "INSERT INTO USERS(ID, USERNAME, PASSWORD, FIRST_NAME , LAST_NAME, EMAIL, COUNTRY) "
+ "VALUES (?,?,?,?,?,?,?)";

getSimpleJdbcTemplate().update(
sql,
new String[] { Long.toString(userId), usr.getUserName(), usr.getPassWord(), usr.getFirstName(),
usr.getLastName(), usr.getEmail(), usr.getCountry() });
return userId;
}
}

2. Using a DB Sequence

To make use of the DB Sequence to create unique identifier, first of all create the DB sequence. Then add the following bean definition in spring configuration file and a member variable(private DataFieldMaxValueIncrementer userIdIncrementor;) in implementation class

Now the changes to add method are as follows.

Add User Method

public long addUser(User usr)
{
long userId = usr.getUserId();
if (userId == 0)
{
// User Id was not explicitly set, so try to find an appropriate user id
String sqlCount = "SELECT MAX(ID) + 1 FROM USERS_TAB";
userId = getSimpleJdbcTemplate().queryForInt(sqlCount,new Object[0]);
System.out.println(userId);
}

final String sql = "INSERT INTO USERS(ID, USERNAME, PASSWORD, FIRST_NAME, LAST_NAME , EMAIL, COUNTRY) "
+ "VALUES (?,?,?,?,?,?,?)";
getSimpleJdbcTemplate().update(sql,
new String[]{Long.toString(userId),
usr.getUserName(),
usr.getPassWord(),
usr.getFirstName(),
usr.getLastName(),
usr.getEmail(),
usr.getCountry()
});
return userId;
}

Now lets take a closer look at what exactly the add method is doing.

If the user_id is zero in the usr bean then it has to be set using either of the above mentioned two ways. userIdIncrementor.nextIntValue() is used to get the next value from the specified sequence in spring config file. After creating the unique identifier, its time to add the user to the database table. We use the spring jdbc template for add the user record to the db table. We get the template from getSimpleJdbcTemplate() method present in the SimpleJdbcDaoSupport. The jdbcTemplate bean is injected in to SimpleJdbcDaoSupport class along with datasource details. See the complete bean configuration file below

XML Configuration File

<bean id="userIdIncrementor" class="org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer">
<description>Incrementor used for USERS Table, the value for the property
incrementerName is the DB sequence which should be already present in the DB</description>
<property name="dataSource" ref="dataSource"/>
<property name="incrementerName" value="USER_ID_SEQ"/>
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="url" value="<your_url>"/>
<property name="driverClassName" value="<your_db_driver_name>"/>
<property name="username" value="<db_user_name>"/>
<property name="password" value="<db_pass_word>"/>
</bean>
<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate">
<description>JDBC Template class provided by Spring Framework</description>
<constructor-arg ref="dataSource"/>
</bean>
<bean id="userDao" class="in.techdive.spring.dao.impl.UserDaoJdbc">
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>

So once the getSimpleJdbcTemplate().update(sql,
new String[]{Long.toString(userId),
usr.getUserName(),
usr.getPassWord(),
usr.getFirstName(),
usr.getLastName(),
usr.getEmail(),
usr.getCountry()
});
method is called, it inserts the user record in to db table and returns the userId.

2. Update an existing record in DB table

Now lets implement the updateUser(User) method in the UserDao interface.

Update User Method

public void updateUser(User usr) {
final String sql = "UPDATE USERS SET USERNAME = ? , PASSWORD = ?, "
+ "FIRST_NAME = ? WHERE ID = ?";

getSimpleJdbcTemplate().update(sql,
new String[]{
usr.getUserName(),
usr.getPassWord(),
usr.getFirstName(),
Long.toString(usr.getUserId())
});
}

The above method will update the USERNAME, PASSWORD and FIRST_NAME columns in the USERS table for the given user_id.

Delete a record from DB table

Deleting a record in USERS table is simple using the below method.

3. Delete User Method

public void deleteUser(User usr)
{
final String sql = "DELETE FROM USERS_TAB WHERE ID = ?";

getSimpleJdbcTemplate().update(sql,
new String[]{ Long.toString(usr.getUserId())});
}

It uses the same getSimpleJdbcTemplate().update() method of jdbctemplate, which deletes the record of the given userId.

4. Retrieve data from DB table

Now lets see how to get the list of records from the USERS table.

Get All Users

public List getAllUserList()
{
final String sql = "SELECT ID, USERNAME, PASSWORD, FIRST_NAME, LAST_NAME, EMAIL, COUNTRY, STATE " +
"FROM USERS_TAB " + "ORDER BY ID";
return getSimpleJdbcTemplate().query(sql, new UserMapper());
}

public class UserMapper implements ParameterizedRowMapper
{
public User mapRow(ResultSet rs, int rowNum) throws SQLException
{
User usr = new User();
usr.setUserId(rs.getLong("ID"));
usr.setFirstName(rs.getString("FIRST_NAME"));
usr.setLastName(rs.getString("LAST_NAME"));
usr.setUserName(rs.getString("USERNAME"));
usr.setPassWord(rs.getString("PASSWORD"));
usr.setCountry(rs.getString("COUNTRY"));
usr.setEmail(rs.getString("EMAIL"));
usr.setState(rs.getString("STATE"));
return usr;
}
}

Take a look at the getAllUserList() method it uses a select query to retrieve a list of users from DB. UserMapper is an inner class which implements from Spring API ParameterizedRowMapper interface. The implemented method mapRow(..) is used for adding the results retrieved from ResultSet in to an User bean. So the method getSimpleJdbcTemplate().query() will finally return a List as required.

Ok! Implementation done. Its now time to test our code. Use the following class to test the DB operations.

SpringJdbcTest Class

public class SpringJdbcTest
{
public SpringJdbcTest()
{
}

public static void main(String[] args)
{
ApplicationContext ctx = new FileSystemXmlApplicationContext(
new String[] { "classpath*:springJdbc-servlet.xml" });

UserDao usrDao = (UserDao) ctx.getBean("userDao");
User usr = new User();
usr.setCountry("INDIA");
usr.setUserName("Federick");
usr.setPassWord("Federick");
usr.setEmail("fed@gmail.com");
usr.setFirstName("Federick");
usr.setLastName("Todd");
// ADD an user
usrDao.addUser(usr);

// update user details
usr.setState("TAMIL NADU");
usrDao.updateUser(usr);

// ADD another user
usr = new User();
usr.setCountry("Portugal");
usr.setUserName("Cristiano");
usr.setPassWord("Ronaldo");
usr.setEmail("cronaldo@gmail.com");
usr.setFirstName("Cristiano");
usr.setLastName("Ronaldo");
long userId = usrDao.addUser(usr);

// get All Users list
List<User> lstUser = usrDao.getAllUserList();
System.out.println(lstUser);

// Delete the user
usr = new User();
usr.setUserId(userId);
usrDao.deleteUser(usr);
}
}

Technology:

Spring

Search