用Spring中的JdbcTemplate操作数据库

User.java:

package com.sikiedu.bean;

public class User {
	private Integer u_id;
	private String u_username;
	private String u_password;
	public Integer getU_id() {
		return u_id;
	}
	public void setU_id(Integer u_id) {
		this.u_id = u_id;
	}
	public String getU_username() {
		return u_username;
	}
	public void setU_username(String u_username) {
		this.u_username = u_username;
	}
	public String getU_password() {
		return u_password;
	}
	public void setU_password(String u_password) {
		this.u_password = u_password;
	}
	@Override
	public String toString() {
		return "User [u_id=" + u_id + ", u_username=" + u_username + ", u_password=" + u_password + "]";
	}
}

UserDao.java:

package com.sikiedu.dao;

import java.util.List;

import com.sikiedu.bean.User;

public interface UserDao {
	
	//	增
	void saveUser(User u);
	//删
	void deleteUserById(Integer id);
	
	//改
	void updateUser(User u);
	//查
	//根据id查找用户
	User selectUserById(Integer id);
	//查找全部用户list
	List<User> selectAllUser();
	//查找用户数量
	Integer selectUserCount();
}

 UserDaolmpl.java:

package com.sikiedu.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.sikiedu.bean.User;

public class UserDaolmpl extends JdbcDaoSupport implements UserDao {
	private static ComboPooledDataSource dataSource;
	
	@Override
	public void saveUser(User u) {
		
		String sql="insert into user values(null,?,?)";
		getJdbcTemplate().update(sql,u.getU_username(),u.getU_password());
	}

	@Override
	public void deleteUserById(Integer id) {
		String sql="delete from user where id=?";
		getJdbcTemplate().update(sql,id);
	}

	@Override
	public void updateUser(User u) {
		String sql="update user set username=?,password=? where id=?";
		getJdbcTemplate().update(sql,u.getU_username(),u.getU_password(),u.getU_id());
		
	}

	@Override
	public User selectUserById(Integer id) {
		
		String sql="select * from user where id=?";
		User user= getJdbcTemplate().queryForObject(sql, new RowMapper<User>(){

			@Override
			public User mapRow(ResultSet rs, int index) throws SQLException {
				User u=new User();
				
				u.setU_id(rs.getInt("id"));
				u.setU_username(rs.getString("username"));
				u.setU_password(rs.getString("password"));
				return u;
			}
			
		},id);
		
		return user;
	}

	@Override
	public List<User> selectAllUser() {
		String sql="select * from user";
		List<User> list=getJdbcTemplate().query(sql, new RowMapper<User>(){

			@Override
			public User mapRow(ResultSet rs, int index) throws SQLException {
				User u=new User();
				
				u.setU_id(rs.getInt("id"));
				u.setU_username(rs.getString("username"));
				u.setU_password(rs.getString("password"));
				return u;
			}
			
		});
		return list;
	}

	@Override
	public Integer selectUserCount() {
		String sql="select count(*) from user";
		return getJdbcTemplate().queryForObject(sql, Integer.class);
	}
}

applicationContext2.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"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">
	
	<context:property-placeholder location="db.properties"/>
	
<!-- 配置dataSource -->
	<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
		<property name="driverClass" value="${jdbc.driverClass}"></property>
		<property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
		<property name="user" value="${jdbc.user}"></property>
		 <!-- 注意：这里name="user",不是“username”！！！切记 -->
		<property name="password" value="${jdbc.password}"></property>
	</bean>

	<bean name="userDao" class="com.sikiedu.dao.UserDaolmpl">
		<property name="dataSource" ref="dataSource"></property>
	</bean>

</beans>

db.properties（这是一个file文件）

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://localhost:3306/web01
jdbc.user=root
jdbc.password=root

测试代码：

JdbcTest.java:

package com.sikiedu.test;

import java.util.List;

import javax.annotation.Resource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.sikiedu.bean.User;
import com.sikiedu.dao.UserDao;
import com.sikiedu.dao.UserDaolmpl;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext2.xml")
public class JdbcTest {
	
	@Resource(name="userDao")
	private UserDao ud;


	@Test
	public void Test1(){
		User u=ud.selectUserById(5);
		System.out.println(u);
	}
	
	@Test
	public void Test2(){
		User u=new User();
		u.setU_username("ased");
		u.setU_password("231");
		ud.saveUser(u);
	}

	@Test
	public void Test3(){
		ud.deleteUserById(12);
	}

	@Test
	public void Test4(){
		User u=new User();
		u.setU_username("王五五w");
		u.setU_password("5555w");
		u.setU_id(5);
		ud.updateUser(u);
	}

	@Test
	public void Test5(){
		 User u = ud.selectUserById(5);
		 System.out.println(u);
	}

	@Test
	public void Test6(){
		 List<User> list = ud.selectAllUser();
		 for(User u:list){
			 System.out.println(u);
		 }
		 
	}

	@Test
	public void Test7(){
		System.out.println(ud.selectUserCount());
	}
	
}