JDBC Dao的实现与测试

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

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

//User.java
//存储数据的model
package 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及其实现已经是比较规范的代码了，可以在较小的程序中使用。