注：删除了原有的数据库工具，，添加了c3p0数据库工具类，添加了c3p0的配置文件，修改了Dao类以及servlet类

二.修改或添加的类

1.C3p0Helper（暂时不了解事务回滚之类的怎么用或者有什么用，只用了连接和关闭）

package helper; import java.sql.Connection; import java.sql.SQLException; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class C3p0Helper { private static String configName = "mysql"; //如果无参，则使用c3p0-config.xml中的default-config,该处使用mysql的配置 private static DataSource ds = new ComboPooledDataSource(configName); /** * 它为null表示没有事务 * 它不为null表示有事务 * 当开启事务时，需要给它赋值 * 当结束事务时，需要给它赋值为null * 并且在开启事务时，让dao的多个方法共享这个Connection */ private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>(); public static DataSource getDataSource() { return ds; } /** * dao使用本方法来获取连接 * @return * @throws SQLException */ public static Connection getConnection() throws SQLException { /* * 如果有事务，返回当前事务的con * 如果没有事务，通过连接池返回新的con */ Connection con = tl.get();//获取当前线程的事务连接 if(con != null) return con; return ds.getConnection(); } /** * 开启事务 * @throws SQLException */ public static void beginTransaction() throws SQLException { Connection con = tl.get();//获取当前线程的事务连接 if(con != null) throw new SQLException("已经开启了事务，不能重复开启！"); con = ds.getConnection();//给con赋值，表示开启了事务 con.setAutoCommit(false);//设置为手动提交 tl.set(con);//把当前事务连接放到tl中 } /** * 提交事务 * @throws SQLException */ public static void commitTransaction() throws SQLException { Connection con = tl.get();//获取当前线程的事务连接 if(con == null) throw new SQLException("没有事务不能提交！"); con.commit();//提交事务 con.close();//关闭连接 con = null;//表示事务结束！ tl.remove(); } /** * 回滚事务 * @throws SQLException */ public static void rollbackTransaction() throws SQLException { Connection con = tl.get();//获取当前线程的事务连接 if(con == null) throw new SQLException("没有事务不能回滚！"); con.rollback(); con.close(); con = null; tl.remove(); } /** * 释放Connection * @param con * @throws SQLException */ public static void releaseConnection(Connection connection) throws SQLException { Connection con = tl.get();//获取当前线程的事务连接 if(connection != con) {//如果参数连接，与当前事务连接不同，说明这个连接不是当前事务，可以关闭！ if(connection != null &&!connection.isClosed()) {//如果参数连接没有关闭，关闭之！ connection.close(); } } } }

2.UserDao

package dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import common.User; import helper.C3p0Helper; public class UserDao { /** * 查询所有用户信息 * @return * @throws SQLException */ public List<User> getAllUser() throws SQLException{ List<User> list = new ArrayList<User>(); Connection conn = C3p0Helper.getConnection();//连接数据库 String sql = "select * from user"; try { PreparedStatement pst = conn.prepareStatement(sql); ResultSet rst = pst.executeQuery(); while (rst.next()) { User user = new User(); user.setId(rst.getInt("id")); user.setName(rst.getString("name")); user.setAge(rst.getInt("age")); list.add(user); } rst.close(); pst.close(); } catch (SQLException e) { e.printStackTrace(); }finally { C3p0Helper.releaseConnection(conn); } return list; } /** * 添加用户 * @param user * @return * @throws SQLException */ public boolean addUser(User user) throws SQLException{ String sql = "INSERT INTO `user`(`name`,`age`) VALUES (?,?)"; Connection conn = C3p0Helper.getConnection(); try { PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, user.getName()); pst.setInt(2, user.getAge()); int count = pst.executeUpdate(); pst.close(); return count>0?true:false; } catch (SQLException e) { e.printStackTrace(); }finally { C3p0Helper.releaseConnection(conn); } return false; } /** * 修改用户信息 * @param user * @return * @throws SQLException */ public boolean updateUser(User user) throws SQLException{ String sql = "UPDATE `user` SET `name`=?,`age`=? WHERE `id` = ?"; Connection conn = C3p0Helper.getConnection(); try { PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, user.getName()); pst.setInt(2, user.getAge()); pst.setInt(3, user.getId()); int count = pst.executeUpdate(); pst.close(); return count>0?true:false; } catch (SQLException e) { e.printStackTrace(); }finally { C3p0Helper.releaseConnection(conn); } return false; } /** * 删除用户 * @param id * @return * @throws SQLException */ public boolean deleteUser(int id) throws SQLException{ String sql = "delete from user where id = ?"; Connection conn = C3p0Helper.getConnection(); try { PreparedStatement pst = conn.prepareStatement(sql); pst.setInt(1, id); int count = pst.executeUpdate(); pst.close(); return count>0?true:false; } catch (SQLException e) { e.printStackTrace(); }finally { C3p0Helper.releaseConnection(conn); } return false; } /** * 根据ID进行查询用户 * @param id * @return * @throws SQLException */ public User selectUserById(int id) throws SQLException{ Connection conn = C3p0Helper.getConnection(); String sql = "select * from user where id = "+id; User user = null; try { PreparedStatement pst = conn.prepareStatement(sql); ResultSet rst = pst.executeQuery(); while (rst.next()) { user = new User(); user.setId(rst.getInt("id")); user.setName(rst.getString("name")); user.setAge(rst.getInt("age")); } rst.close(); pst.close(); } catch (SQLException e) { e.printStackTrace(); }finally { C3p0Helper.releaseConnection(conn); } return user; } }

3.Servlet