DBCP封装与使用——数据库增删改查

1、准备

dbcp ： data base connection pool 数据库连接池
在WEB-INF–lib下的加入下面三个包：

下载链接：
链接：https://pan.baidu.com/s/1A5rF_dF31TYJLZG0Yvmh1w
密码：sf7h

2、数据库配置文件

#基本查询的设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://连接名:3306/数据库名?characterEncoding=utf8
username=u_show
password=17100886

3、DBCPUtils.java工具类

public class DBCPUtils {
	
	private static DataSource dataSource;
	//加载配置文件，创建数据库连接池
	static {
		try {
			InputStream is = DBCPUtils.class.getResourceAsStream("/dbcp.properties");
			Properties p = new Properties();
			p.load(is);
			dataSource = BasicDataSourceFactory.createDataSource(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	//返回连接对象
	public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException("数据连接获取失败！");
        }
    }
	
	//返回连接池对象
	public static DataSource getDataSource() {
        return dataSource;
    }
	
	//释放连接，被连接池收回
	public static void releaseConnection(Connection connection, PreparedStatement pstmt, ResultSet rs){
        try {
            if(rs != null ) {
                rs.close();
            }
            if(pstmt != null ) {
                pstmt.close();
            }
            if(connection != null ) {
                connection.close();
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
    //利用main函数测一下是否连接成功
    public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = DBCPUtils.getConnection();
			stmt = conn.prepareStatement("select * from User");
			rs = stmt.executeQuery();
			while (rs.next()) {
				User user = new Lqsjrkqk(rs.getString(1), 	rs.getString(2));
			
				System.out.println(
						rs.getString(1) + " " +
						rs.getString(2) + " " 
						);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBCPUtils.releaseConnection(conn, stmt, rs);
		}
	}
}	

4、数据库增删改查(PreparedStatement )

1、查询

public ArrayList<Gljhlqrs> selectAll() {
		ArrayList<Gljhlqrs> lists = new ArrayList<>();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = DBCPUtils.getConnection();
			stmt = conn.prepareStatement("select * from t_tj_gljhlqrs order by XH");
			rs = stmt.executeQuery();
			while (rs.next()) {
				Gljhlqrs lqrs = new Gljhlqrs(rs.getInt(1), 	rs.getString(2),
						rs.getString(3), rs.getInt(4));
				lists.add(lqrs);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBCPUtils.releaseConnection(conn, stmt, rs);
		}
		return lists;
		
		
	}
    

2、插入、删除、更新

public boolean ChaRu1(User user){
        boolean flag=true;
        Connection conn = null;
		PreparedStatement stmt = null;
        String insertSql="insert into user (name,pwd) values(?,?)";
        String deleteSql ="delete from user where name=?";
        String updateSql = "";
         // getConn()方法是静态的，直接用类调用建立连接。
        try {
            conn = DBCPUtils.getConnection();
			stmt = conn.prepareStatement(insertSql);
			stmt .setString(1, user.getName());
			stmt .setString(2, user.getPwd());
            //这里以插入为例，其他的改一下sql即可，代码一样。
            int i=stmt .executeUpdate();
    
            if(i==0){
                flag=false; //更新失败
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBCPUtils.releaseConnection(conn, stmt, null); 
             //关闭连接，由于插入操作不涉及ResultSet类，故其对象rs无需关闭，用null代替。
        }
        return flag;
    }