JavaWeb中的Dao开发模式学习笔记

1. 创建连接数据库类的工具类JdbcUtils，里面是连接与释放数据库的方法

package com.tanghao.utils;


import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;

    static {
        Properties properties = new Properties();
        InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        try{

            properties.load(is);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            //加载驱动
            Class.forName("driver");

        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        Connection conn = null;
        try{
            //创建连接,获得连接对象
            conn = DriverManager.getConnection(url,username,password);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }
    //  释放
    public static void free(ResultSet rs, Statement st,Connection conn){
        try{
            if (rs != null) rs.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try{
                if (st != null) st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                try {
                    if (conn != null) conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

}

2. 创建JavaBean User类（里面有属性，get与set方法，构造方法）

package com.tanghao.pojo;

public class User {
    private Integer id;
    private String username;
    private String password;
    private String email;

    public User(){

    }

    public User(String username, String password, String email){
        this.username = username;
        this.password = password;
        this.email = email;
    }

    public User(Integer id, String username, String password, String email) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.email = email;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getId() {
        return id;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    public String getEmail() {
        return email;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

3. 创建UserDao(里面是操作数据库对象的一些方法)

package com.tanghao.dao;

import com.tanghao.pojo.User;
import com.tanghao.utils.JdbcUtils;

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 UserDao {

    //向数据库中添加用户记录的方法add()
    public void add(User user){
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = JdbcUtils.getConnection();
            String sql = "INSERT INTO t_user(`id`,`username`,`password`,`email`) VALUES(?,?,?,?);";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,user.getId());
            ps.setString(2, user.getUsername());
            ps.setString(3, user.getPassword());
            ps.setString(4, user.getEmail());
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.free(null,ps,conn);
        }
    }

    //修改数据库用户记录的方法update
    public void update(User user){
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = JdbcUtils.getConnection();
            String sql = "UPDATE t_user set username=?,password=?,email=? where id=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getEmail());
            ps.setInt(4,user.getId());
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.free(null,ps,conn);
        }
    }

    //删除数据库用户记录方法
    public void delete(int id){
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = JdbcUtils.getConnection();
            String sql = "Delete From t_user where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.free(null,ps,conn);
        }
    }

    //根据id查询用户的方法findUserById()
    public User findUserById(int id){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        try{
            conn = JdbcUtils.getConnection();
            String sql = "select * from t_user where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs=ps.executeQuery();
            if (rs.next()){
                user = new User();
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setEmail(rs.getString(4));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.free(rs,ps,conn);
        }
        return user;
    }

    //查询所有用户记录，QueryAll()
    public List<User> QueryAll(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<User> userList = new ArrayList<User>();
        try{
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM t_user";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                User user=new User();
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setEmail(rs.getString(4));
                userList.add(user);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.free(rs,ps,conn);
        }
        return userList;
    }

}

问题记录：在jsp中编写代码调用里面的一些方法时一老出现空指针报错，不知道为什么