dbcp

首页下载以下jar：

 

spring-dbcp.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"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd">
	
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://172.19.59.48:3306,172.19.59.50:3306/mysql?autoReconnect=true" />
		<property name="username" value="root" />
		<property name="password" value="123456" />
		<property name="initialSize" value="5" />
		<property name="maxActive" value="50" />
		<property name="maxIdle" value="60" />
		<property name="minIdle" value="5" />
		<property name="maxWait" value="60000" />
	</bean>
</beans>

 

测试代码：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestDbcpBySpring {

	public static void main(String[] args) {
		// 更新
		addUser("f", 6);
		
		// 查询
		List<User> list = getAllUser();
		for(User user : list){
			System.out.println("name:" + user.getName() + "\t age:" + user.getAge());
		}
	}

	/**
	 * 添加用户
	 * 
	 * @param name
	 *            用户名
	 * @param age
	 *            用户年龄
	 * @return
	 */
	@SuppressWarnings("resource")
	public static boolean addUser(String name, int age) {
		boolean result = false;// 返回值默认为false
		Connection conn = null;// 创建数据库连接对象Connection
		PreparedStatement pstmt = null;// 创建预处理对象PreparedStatement
		
		try {
			ApplicationContext ac = new ClassPathXmlApplicationContext("mysql/spring-dbcp.xml");
			BasicDataSource dataSource = (BasicDataSource) ac.getBean("dataSource");
			conn = dataSource.getConnection();
			
			String sql = "insert into student (name, age) values(?, ?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setInt(2, age);
			int count = pstmt.executeUpdate(); // 更新数据库并返回受影响行数
			if (count > 0) {
				result = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		System.out.println("addUser result:" + result);
		return result;
	}

	/**
	 * 查询所有用户
	 * 
	 * @return List<User>
	 */
	@SuppressWarnings("resource")
	public static List<User> getAllUser() {
		List<User> userList = new ArrayList<User>();
		Connection conn = null;
		Statement stmt = null;
		
		try {
			ApplicationContext ac = new ClassPathXmlApplicationContext("mysql/spring-dbcp.xml");
			BasicDataSource dataSource = (BasicDataSource) ac.getBean("dataSource");
			conn = dataSource.getConnection();
			
			String sql = "select * from student";
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				User user = new User();
				user.setName(rs.getString("name"));
				user.setAge(rs.getInt("age"));
				userList.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return userList;
	}

}