Using Spring JDBC Template

Categories: Java; Tagged with: Java • JDBC • JDBCTemplate • Spring; @ June 25th, 2012 21:48

JDBCTemplate simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results.
http://static.springsource.org/spring/docs/2.0.x/api/org/springframework/jdbc/core/JdbcTemplate.html

Here are two methods, the 2nd one is using JDBCTemplate from Srping, you can see the code are more simple and clear:

	/**
	 * LoadUsers using JDBC.
	 * @param dataSource
	 * @return
	 */
	public List loadUserJDBC(DataSource dataSource) {
		List users = new ArrayList();
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement preparedStatement = conn.prepareStatement("SELECT ID, EMAIL, LOGIN_NAME, NAME FROM ACCT_USER");
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				User user = new User();
				users.add(user);
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				System.out.println(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			if(conn != null) {
				
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return users;
	}
	
	/**
	 * Load users using Spring JDBCTemplate.
	 * @param jdbcTemplate
	 * @return
	 */
	public java.util.List doJDBCTempQuery(JdbcTemplate jdbcTemplate) {
		String queryUsers = "SELECT ID, EMAIL, LOGIN_NAME, NAME FROM ACCT_USER";
		java.util.List users = jdbcTemplate.query(queryUsers, new ParameterizedRowMapper() {

			@Override
			public User mapRow(ResultSet rs, int arg1) throws SQLException {
				User user = new User();
				user.setId(rs.getInt(1));
				user.setName(rs.getString(4));
				return user;
			}
		});
		
		return users;
	}

And here is a example to define the template bean:


<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost/mini-liguoliang.com" />
    <property name="username" value="root" />
    <property name="password" value="pw" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >
<constructor-arg ref="dataSource"></constructor-arg>
</bean>

Spirng3.1 + Hibernate4.1 HelloWorld <-> Transaction management using Spring and Hibernate