java [30] JDBCTemplate配合DBCP连接池使用

只需要传入sql语句和参数即可，不需要获取连接和关闭连接。

package com.us.spring;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
/*
 * 
 * dbcp实现了database 接口对数据源进行了封装
 * JdbcTemplate 类实现了*/
import com.us.sqltable.userInfo;

import datasource.DBCPUtils;

public class JdbcTemnpleteTest {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
	/*	userInfo user = findUser1("ok");
		System.out.println("user " + user);
		List users = findUsers(4);
		System.out.println(users);
		int i = getUsrCount();
		System.out.println("count:" + i);*/
		Map map = getData(1);
		System.out.println(map);
		System.out.println(map.get("username"));
	}
	
	//获取主键
	static int addUser(userInfo user) {
		JdbcTemplate jdbc = new JdbcTemplate( );
		jdbc.setDataSource((DataSource)DBCPUtils.getDataSource());
		jdbc.execute(new ConnectionCallback() {

			@Override
			public Object doInConnection(Connection con) throws SQLException, DataAccessException {
				String sql = "insert into userInfo(username,passwd,type,birthday,money) values (?,?,?,?,?)";
				PreparedStatement ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				ps = con.prepareStatement(sql);
				ps.setString(1, user.getUsername());
				ps.setString(2, user.getPasswd());
				ps.setString(3, user.getType());
				ps.setDate(4, new Date(user.getBirthday().getTime()));
				ps.setFloat(5, user.getMoney());
				ps.executeUpdate();
				ResultSet rs = ps.getGeneratedKeys();
				if (rs.next()) {
					user.setId(rs.getInt(1));
				}
				return null;
			}
		});
		
		
		
		return user.getId();
	}
	
	static Map getData(int id){
		JdbcTemplate jdbc = new JdbcTemplate( );
		jdbc.setDataSource((DataSource)DBCPUtils.getDataSource());
		String sql = "select username,passwd as password,type from userInfo where id = " + id;
		return jdbc.queryForMap(sql);
		
	}
	
	static String getUsrName(int id) {
		JdbcTemplate jdbc = new JdbcTemplate( );
		jdbc.setDataSource((DataSource)DBCPUtils.getDataSource());
		String sql = "select name from userInfo where id = " + id;
		String name = jdbc.queryForObject(sql, String.class);
		return name;
	}
	
	@SuppressWarnings("deprecation")
	static int getUsrCount() {
		JdbcTemplate jdbc = new JdbcTemplate( );
		jdbc.setDataSource((DataSource)DBCPUtils.getDataSource());
		String sql = "select count(*) from userInfo";
		return jdbc.queryForInt(sql);
	}
	
	//获取列表多条记录
	static List  findUsers(int id) {
		//jdbc可添加数据源
		JdbcTemplate jdbc = new JdbcTemplate( );
		jdbc.setDataSource((DataSource)DBCPUtils.getDataSource());
		String sql = "select username,passwd,type,birthday,money from userInfo where id<?";
		//假如数据库中的名字跟java类中的名字对应不起来，可以使用别名同样实现
		//String sql = "select user_name,passwd,type,birthday,money from userInfo where username=?";
		Object[] args = new Object[] {id};
		//userInfo user=new userInfo();
		List users = jdbc.query(sql, args, new BeanPropertyRowMapper(userInfo.class));
		return users;
	}
	//查询一条记录 如果是多条会报错
	static userInfo findUser1(String name) {
		//jdbc可添加数据源
		JdbcTemplate jdbc = new JdbcTemplate( );
		jdbc.setDataSource((DataSource)DBCPUtils.getDataSource());
		String sql = "select username,passwd,type,birthday,money from userInfo where username=?";
		//假如数据库中的名字跟java类中的名字对应不起来，可以使用别名同样实现
		//String sql = "select user_name,passwd,type,birthday,money from userInfo where username=?";
		Object[] args = new Object[] {name};
		//userInfo user=new userInfo();
		Object user = jdbc.queryForObject(sql, args, new BeanPropertyRowMapper<userInfo>(userInfo.class));
		return (userInfo)user;
	}
	static userInfo findUser(String name) {
		//jdbc可添加数据源
		JdbcTemplate jdbc = new JdbcTemplate( );
		jdbc.setDataSource((DataSource)DBCPUtils.getDataSource());
		String sql = "select username,passwd,type,birthday,money from userInfo where username=?";
		Object[] args = new Object[] {name};
		userInfo user=new userInfo();
		jdbc.queryForObject(sql, args, new RowMapper<Object>() {

			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				user.setUsername(rs.getString("username"));
				user.setPasswd(rs.getString("passwd"));
				user.setType(rs.getString("type"));
				user.setBirthday(rs.getDate("birthday"));
				user.setMoney(rs.getFloat("money"));
				return user;
			}
		});
		return (userInfo)user;
	}

}

方式一：Object user = jdbc.queryForObject(sql, args, new BeanPropertyRowMapper<userInfo>(userInfo.class));能够直接返回对象，自己内部完成，不需要对对象进行操作。

方式二：jdbc.queryForObject(sql, args, new RowMapper<Object>()需要对rowmapper进行重写。