JDBC Daoの実装とテスト

3階層アーキテクチャでは、jdbcはデータ・アクセス・レイヤに属します.Domainなどでデータ転送を行います.各層の間は互いに独立していることが望ましい.オブジェクト(Dao:Data Access Objectデータアクセスオブジェクト)を渡すUserDaoを作成します.

//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とその実装はすでに比較的規範的なコードであり,小さなプログラムで使用できる.