Spring之JdbcTemplate练习

package com.maty.jdbc;

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

import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

/** 
* @author maty  e-mail:512181558@qq.com
* @version 创建时间：2018年5月22日 上午11:28:33 
* 类说明 该类用来练习jdbcTemplate
*/
public class JdbcTempleteDemo
{
	
	//增
	@Test
	public void add()
	{
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://localhost/snmp");
		dataSource.setUsername("root");
		dataSource.setPassword("wangxiaowei");
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		String sql = "insert into user values(?,?)";
		int i = jdbcTemplate.update(sql, "tye","99");
		System.out.println("添加成功"+i+"行数据");
		
	}
	
	
	//删
	@Test
	public void del()
	{
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://localhost/snmp");
		dataSource.setUsername("root");
		dataSource.setPassword("wangxiaowei");
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		
		String sql = "delete from user where username = ?";
		int i = jdbcTemplate.update(sql, "jason");
		System.out.println("删除"+i+"行数据");
	}
	
	
	//改
	@Test
	public void update()
	{
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://localhost/snmp");
		dataSource.setUsername("root");
		dataSource.setPassword("wangxiaowei");
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		
		String sql = "update user set password = ? where username = ?";
		int i = jdbcTemplate.update(sql, "38","tye");
		System.out.println("修改了"+i+"行数据");
	}
	
	
	//查
	@Test
	public void select()
	{
		DriverManagerDataSource dataSource = new DriverManagerDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://localhost/snmp");
		dataSource.setUsername("root");
		dataSource.setPassword("wangxiaowei");
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		
		/*//返回整数
		String sql = "select * from user where username = ?";
		Integer i  = jdbcTemplate.queryForObject(sql, Integer.class);
		System.out.println(i);*/
		
		
		/*//返回单个对象
		String sql = "select * from user where username = ?";
		User user = jdbcTemplate.queryForObject(sql, new MyRowMapper(),"tye");
		System.out.println(user);*/
		
		//返回多个对象
		String sql = "select * from user";
		List<User> query = jdbcTemplate.query(sql, new MyRowMapper());
		System.out.println(query);
	}
}

class User
{
	private String username;
	private String password;
	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;
	}
	@Override
	public String toString()
	{
		return "User [username=" + username + ", password=" + password + "]";
	}
	
}


class MyRowMapper implements RowMapper<User>
{

	public User mapRow(ResultSet rs, int rowNum) throws SQLException
	{
		String username = rs.getString("username");
		String password = rs.getString("password");
		User user = new User();
		user.setUsername(username);
		user.setPassword(password);
		System.out.println(rowNum);
		return user;
	}
	
}