JDBC系列教程之六：一个简单用户相关的数据访问层

User.java

package com.hyneng.jdbc.domain;

import java.util.Date;

/**

* 2010-10-07

* @author HuangYineng

*/

public class User {

   private int id;

   private String name;

   private Date birthday;

   private float money;

   ...(setXXX,getXXX)

}

UserDao.java

package com.hyneng.jdbc.dao;

import com.hyneng.jdbc.domain.User;

/**

* 2010-10-07

* @author HuangYineng

*/

public interface UserDao {

   public void addUser(User user);

   public User getUser(int userId);

   public User findUser(String  loginName, String password);

   public void update(User user);

   public void delete(User user);

}

UserDaoJdbcImpl.java

package com.hyneng.jdbc.dao.impl;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import com.hyneng.jdbc.JdbcUtils;

import com.hyneng.jdbc.dao.DaoException;

import com.hyneng.jdbc.dao.UserDao;

import com.hyneng.jdbc.domain.User;

/**

* 2010-10-07

* @author HuangYineng

*/

public class UserDaoJdbcImpl implements UserDao {

   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);

      }

   }

   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);

      }

   }

   public User findUser(String  loginName, String password) {

      Connection conn = null;

      PreparedStatement ps = null;

      ResultSet rs = null;

      User user = null;

      try {

          conn = JdbcUtils.getConnection();

          String sql = "select id, name, money, birthday  from user where name=?";

          ps = conn.prepareStatement(sql);

          ps.setString(1, loginName);

          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;

   }

   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, money, birthday  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;

   }

   private User mappingUser(ResultSet  rs) throws SQLException {

      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;

   }

   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);

      }

   }

}

DaoException.java

package com.hyneng.jdbc.dao;

/**

* 2010-10-07

* @author HuangYineng

*/

public class DaoException extends RuntimeException {

   private static final long serialVersionUID = 1L;

   public DaoException() {

   }

   public DaoException(String message)  {

      super(message);

   }

   public DaoException(Throwable  cause) {

      super(cause);

   }

   public DaoException(String message,  Throwable cause) {

      super(message, cause);

   }

}