jdbcTemplate简单使用

废话少说，直接上例子
1.搭建环境
    就是导包和一些配置文件，这里就不多说了，可以参考附件。
    注意：mysql的innodb引擎才知道事务回滚
2.编码

public class User {
	private int id;
	private String username;
	private String password;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	
}

dao接口

public interface UserDAO {
	public User findById(int id) throws Exception;
	public void addUser(User user) throws Exception;
	public void deleteUser(User user);
	public void updateUser(User user);
	public void batchAddUser(List<User> users);
	public void createTable(String sql);
	public void test(User user);
}

dao实现

@Transactional
@Component("UserDAO")
public class UserDAOImpl implements UserDAO {
	@Resource
	private JdbcTemplate jdbcTemplate;
	@Override
	public User findById(int id) throws Exception {
		String sql = "select id,username from user where id = ?";
		Object[] params = new Object[] { new Integer(id) };
		User user = jdbcTemplate.queryForObject(sql, params, new UserRowMapper());
		return user;

		}
		
		class UserRowMapper implements RowMapper<User> {

			@Override
			public User mapRow(ResultSet rs, int rowNumber) throws SQLException {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				return user;
			}
		}


	@Override
	@Transactional//(rollbackFor=Exception.class)默认为runtimeException才回滚
	public void addUser(User user) throws Exception {
		String sql = "insert into user(username,password) values(?,?)";
		List<String> list = new ArrayList<String>();
		list.add(user.getUsername());
		list.add(user.getPassword());
		jdbcTemplate.update(sql, list.toArray());
		//throw new Exception("error");

	}

	@Override
	public void deleteUser(User user) {
		String sql = "delete from user where id=?";
		List<String> list = new ArrayList<String>();
		list.add(user.getId()+"");
		jdbcTemplate.update(sql, list.toArray());

	}
	@Transactional(rollbackFor=Exception.class)
	public void test(User user){
		String sql = "delete from user where id="+user.getId();
		jdbcTemplate.update(sql);
		sql = "update user set username='aa',password='bb' where id="+user.getId();
		jdbcTemplate.update(sql);
	}
	@Override
	@Transactional//(rollbackFor=Exception.class)
	public void updateUser(User user) {
		String sql = "update user set username=?,password=? where id=?";
		List<String> list = new ArrayList<String>();
		list.add(user.getUsername());
		list.add(user.getPassword());
		list.add(user.getId()+"");
		jdbcTemplate.update(sql, list.toArray());

	}

	@Override
	@Transactional//(rollbackFor=Exception.class)
	public void batchAddUser(final List<User> users) {
		 String sql = "insert into user (username,password) values(?,?)";
		  BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
		  public int getBatchSize() {
		    return users.size();    //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size()；
		   }
		   public void setValues(java.sql.PreparedStatement ps, int i)
		     throws SQLException {
			   User user = users.get(i);
			   ps.setString(1, user.getUsername());
			   ps.setString(2, user.getPassword());
			   
		   }
		  };
		  jdbcTemplate.batchUpdate(sql, setter);
	}
	@Override
	public void createTable(String sql) {
		jdbcTemplate.execute(sql);
		
	}

}

测试类

package test.dao;

import java.util.ArrayList;
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 test.model.User;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "classpath:lms-admin.xml" })
public class UserDAOImpl {
	//private static BeanFactory factory = new ClassPathXmlApplicationContext("lms-admin.xml");
	//UserDAO userDAO = (UserDAO) factory.getBean("UserDAO");
	@Resource
	UserDAO userDAO;
	
	@Test
	public void testCreate() throws Exception{
		String sql = "create table user(id int(20) primary key auto_increment,username varchar(20),password varchar(20));";
		userDAO.createTable(sql);
	}
	
	@Test
	public void testSearch() throws Exception{
		User user = userDAO.findById(6);
		System.out.println(user.getUsername());
	}
	@Test
	public void testSave() throws Exception{
		User user = new User();
		//user.setId(2);
		user.setPassword("aaa");
		user.setUsername("zs");
		userDAO.addUser(user);
	}
	@Test
	public void testDelete() throws Exception{
		User u = userDAO.findById(5);
		userDAO.deleteUser(u);
	}
	@Test
	public void testUpdate() throws Exception{
		User u = userDAO.findById(5);
		u.setPassword("lkasjdflkasd");
		u.setUsername("asdofjiweif");
		userDAO.updateUser(u);
	}
	@Test
	public void testBatchSave() throws Exception{
		List<User> users = new ArrayList<User>();
		User user1 = new User();
		user1.setPassword("aaa");
		user1.setUsername("zs");
		User user2 = new User();
		user2.setPassword("aaa");
		user2.setUsername("张三");
		users.add(user1);
		users.add(user2);
		userDAO.batchAddUser(users);
	}
	@Test
	public void testRoll() throws Exception{
		User user = userDAO.findById(5);
		userDAO.test(user);
		
	}
}