JDBC Dao的实现与测试

在三层架构中，jdbc属于数据访问层。通过Domain等进行数据传递。

各个层之间最好能相互独立。
建立一个UserDao，通过他来传递对象（Dao：Data Access Object数据访问对象）

//User.java//存储数据的modelpackage cn.itcast.domain;import java.util.Date;public class User {    private int id;    private String name;    private Date birthday;    private float money;    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public Date getBirthday() {        return birthday;    }    public void setBirthday(Date birthday) {        this.birthday = birthday;    }    public float getMoney() {        return money;    }    public void setMoney(float money) {        this.money = money;    }}

//UserDao.java//Dao interface访问数据库package cn.itcast.jdbc.dao;import cn.itcast.domain.User;public interface UserDao {    public void addUser(User user);    public User getUser(int userId);    public User findUser(String loginNames,String password);    public void update(User user);    public void delete(User user);}

//UserDaoJdbcImpl.java//UserDao的实现package cn.itcast.dao.Impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import cn.itcast.domain.User;import cn.itcast.jdbc.JdbcUtils;import cn.itcast.jdbc.dao.UserDao;public class UserDaoJdbcImpl implements UserDao {    //增    @Override    public void addUser(User user) {        Connection conn = null;        PreparedStatement ps = null;        ResultSet rs = null;        try {            conn = JdbcUtils.getConnection();            String sql = "insert into user(name,birthday,money)values(?,?,?)";            ps = conn.prepareStatement(sql);            ps.setString(1, user.getName());            ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));            ps.setFloat(3, user.getMoney());            ps.executeUpdate();        } catch (SQLException e) {            throw new DaoException(e.getMessage(), e);        } finally {            JdbcUtils.free(rs, ps, conn);        }    }    //根据userId查找user    @Override    public User getUser(int userId) {        Connection conn = null;        PreparedStatement ps = null;        ResultSet rs = null;        User user = null;        try {            conn = JdbcUtils.getConnection();            String sql = "select id,name,birthday,money from user where id=?";            ps = conn.prepareStatement(sql);            ps.setInt(1, userId);            rs = ps.executeQuery();            while (rs.next()) {                user = mappingUser(rs);            }        } catch (SQLException e) {            throw new DaoException(e.getMessage(), e);        } finally {            JdbcUtils.free(rs, ps, conn);        }        return user;    }    // 改    @Override    public void update(User user) {        Connection conn = null;        PreparedStatement ps = null;        ResultSet rs = null;        try {            conn = JdbcUtils.getConnection();            String sql = "update user set name=?,birthday=?,money=? where id=?";            ps = conn.prepareStatement(sql);            ps.setString(1, user.getName());            ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));            ps.setFloat(3, user.getMoney());            ps.setInt(4, user.getId());            ps.executeUpdate();        } catch (SQLException e) {            throw new DaoException(e.getMessage(), e);        } finally {            JdbcUtils.free(rs, ps, conn);        }    }    //删    @Override    public void delete(User user) {        Connection conn = null;        Statement st = null;        ResultSet rs = null;        try {            conn = JdbcUtils.getConnection();            st = conn.createStatement();            String sql = "delete from user where id=" + user.getId();            st.executeUpdate(sql);        } catch (SQLException e) {            throw new DaoException(e.getMessage(), e);        } finally {            JdbcUtils.free(rs, st, conn);        }    }    //映射 将结果集映射为User    private User mappingUser(ResultSet rs) throws SQLException {        User user;        user = new User();        user.setId(rs.getInt("id"));        user.setName(rs.getString("name"));        user.setMoney(rs.getFloat("money"));        user.setBirthday(rs.getDate("birthday"));        return user;    }}

上面用到的DaoException

以上Dao及其实现已经是比较规范的代码了，可以在较小的程序中使用。