web实操日记10.12，有关JDBC的CURD以及资源释放的finally块

首先是有关JDBC项目中的DAO，DTO，ENETITY包作用解析

DAO中放JDBC代码

DTO实现数据中转（本篇没用到）

ENETITY实现数据库数据保存

关于DAO

由于注册驱动和建立连接为重复代码，所以在一个类中通过静态方法来实现并抛出异常

有关结构如下

这样再写JDBC有助于减少冗余

还有finally块释放资源，如果不在finally释放资源，可能碰到这样的情况

Connection建立

Statement(PrepareStatement，一般用这个，防止SQL注入，关于SQL注入我的上一篇文章有详细说明)建立

ResultSet建立

中间代码运行

这时，如果发生异常（如sql语句错误引起的异常）导致代码运行终止，那么堆中申请的内存是不会被释放的，（虽然会被JVM的GC机制回收）

而finally不管什么情况都会释放资源，所以很有必要

没什么好写的，展示一下CURD操作

import com.lty.www.test5_close_in_finally.ConFac.ConnectionFactory;
import com.lty.www.test6_DTO_ENTITY_DAO.entity.Userinfo;

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

public class UserinfoDao {
    public void insert(Userinfo userinfo){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("insert into userinfo");
            sql.append("(id,username,password)");
            sql.append("values(idauto.nextval,?,?)");

            conn = ConnectionFactory.getConnection();
            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setString(1, userinfo.getUsername());
            pstmt.setString(2, userinfo.getPassword());
            pstmt.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            ConnectionFactory.close(conn,pstmt);
        }
    }
    public void Update_from_Id(Userinfo userinfo){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("update userinfo ");
            sql.append("set username=?,password=? where id=?");
            System.out.println(sql);
            conn = ConnectionFactory.getConnection();
            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setString(1,userinfo.getUsername());
            pstmt.setString(2,userinfo.getPassword());
            pstmt.setLong(3,userinfo.getId());
            pstmt.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();;
        }finally{
            ConnectionFactory.close(conn,pstmt);
        }
    }
    public void Delete_from_Id(Userinfo userinfo){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("delete from userinfo ");
            sql.append("where id=?");
            System.out.println(sql);
            conn = ConnectionFactory.getConnection();
            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setLong(1,userinfo.getId());
            pstmt.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            ConnectionFactory.close(conn,pstmt);
        }
    }
    public Userinfo select_from_Id(long userid){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Userinfo userinfo = null;
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from userinfo ");
            sql.append("where id=?");
            System.out.println(sql);
            conn=ConnectionFactory.getConnection();
            pstmt = conn.prepareStatement(sql.toString());
            pstmt.setLong(1,userid);
            rs = pstmt.executeQuery();
            while(rs.next()){
                Long id = rs.getLong("id");
                String username = rs.getString("username");
                String password = rs.getString("password");

                userinfo = new Userinfo(id,username,password);
                System.out.println(rs.getString("username")+" "+rs.getString("password"));
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            ConnectionFactory.close(conn,pstmt,rs);
        }
        return userinfo;
    }
    public List<Userinfo> selectAll(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Userinfo>userinfoList = new ArrayList<Userinfo>();
        try{
            StringBuffer sql = new StringBuffer();
            sql.append("select * from userinfo");
            System.out.println(sql);
            conn = ConnectionFactory.getConnection();
            pstmt = conn.prepareStatement(sql.toString());
            rs = pstmt.executeQuery();
            while(rs.next()){
                Long id = rs.getLong("id");
                String username = rs.getString("username");
                String password = rs.getString("password");
                userinfoList.add(new Userinfo(id,username,password));
                System.out.println(id+" "+username+" "+password);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            ConnectionFactory.close(conn,pstmt,rs);
        }
        return userinfoList;
    }
}

通过ENETITY中的类实现参数传递减少 

通过测试类简单的userinfo对象和UserinfoDao对象实现测试CURD操作