accessdao.java

java编写的简易留言本

package anni.gbook;

import java.net.URL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;


/**
 * 实现了IBoardDAO接口的类.
 * 对access进行操作
 *
 * @since 2006-03-15 11:11
 * @author Lingo
 * @version 1.0
 */
public final class AccessDAO implements IBoardDAO {
    /**
     * singleton实例.
     */
    private static AccessDAO instance = new AccessDAO();

    /**
     * 数据库连接.
     */
    private Connection conn = null;

    /**
     * 预编译state.
     */
    private PreparedStatement pState = null;

    /**
     * 普通state.
     */
    private Statement state = null;

    /**
     * 结果集.
     */
    private ResultSet rs = null;

    /**
     * 构造方法，建立数据库连接.
     *
     * @since 2006-03-15 11:15
     * @author Lingo
     */
    private AccessDAO() {
        try {
            URL fileUrl = AccessDAO.class.getResource("/data.mdb");
            String fileName = fileUrl.getFile();
            fileName = fileName.substring(1);
            System.out.println(fileName);

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="
                + fileName;
            conn = DriverManager.getConnection(url);
        } catch (Throwable ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 取得这个Singleton的方法.
     * @since 2006-01-01 18:54
     * @author Lingo
     * @return AccessDAO 返回singleton实例
     */
    public static AccessDAO getInstance() {
        return instance;
    }

    /**
     * 返回所有记录.
     * @return List 记录列表
     */
    public List findAll() {
        System.out.println("findAll : ");

        List all = new ArrayList();

        try {
            String sql = "select * from t_gbook";
            state = conn.createStatement();
            rs = state.executeQuery(sql);

            BoardInfo info;

            while (rs.next()) {
                info = rs2Bean(rs);

                all.add(info);
            }

            //这里要把得到的列表反转
            //这样最新的留言就到了最前面了
            Collections.reverse(all);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            close();
        }

        return all;
    }

    /**
     * ResultSet转换bean.
     * @param resultSet 保存数据的ResultSet
     * @return BoardInfo 返回的BoardInfo
     * @throws SQLException 读取数据的时候可能抛出异常
     */
    private BoardInfo rs2Bean(final ResultSet resultSet)
        throws SQLException {
        BoardInfo info = new BoardInfo();
        info.setId(Integer.parseInt(resultSet.getString(1)));
        info.setName(resultSet.getString(2));
        info.setEmail(resultSet.getString(3));
        info.setPageName(resultSet.getString(4));
        info.setPageUrl(resultSet.getString(5));
        info.setSex(Integer.parseInt(resultSet.getString(6)));
        info.setImg(resultSet.getString(7));
        info.setContent(resultSet.getString(8));
        info.setIp(resultSet.getString(9));
        info.setDateTime(new java.util.Date(resultSet.getTimestamp(10)
                                                     .getTime()));
        info.setReply(resultSet.getString(11));

        return info;
    }

    /**
     * 新添加的方法，根据id返回一个BoardInfo.
     * 如果找不到，就返回null
     * @since 2006-01-02 22:07
     * @author Lingo
     * @param id id
     * @return BoardInfo id对应的实例
     */
    public BoardInfo findById(final int id) {
        System.out.println("findById : " + id);

        BoardInfo info = null;

        try {
            pState = conn.prepareStatement(
                    "select * from t_gbook where i_id =?");
            pState.setInt(1, id);
            rs = pState.executeQuery();

            if (rs.next()) {
                info = rs2Bean(rs);
            }
        } catch (Throwable ex) {
            return null;
        } finally {
            close();
        }

        return info;
    }

    /**
     * 插入新记录.
     * @param info 记录
     */
    public void insert(final BoardInfo info) {
        System.out.println("insert : " + info);

        try {
            String sql = "insert into t_gbook(c_name,c_email,c_page_name,c_page_url,i_sex,c_img,c_content,c_ip,"
                + "c_datetime) values(?,?,?,?,?,?,?,?,?)";
            pState = conn.prepareStatement(sql);
            pState.setString(1, info.getName());
            pState.setString(2, info.getEmail());
            pState.setString(3, info.getPageName());
            pState.setString(4, info.getPageUrl());
            pState.setInt(5, info.getSex());
            pState.setString(6, info.getImg());
            pState.setString(7, info.getContent());
            pState.setString(8, info.getIp());
            pState.setTimestamp(9,
                new Timestamp(info.getDateTime().getTime()));

            pState.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            close();
        }
    }

    /**
     * 修改info的信息.
     * 现在虽然只是用于修改回复信息，
     * 就是只对最后一列数据进行修改，
     * 不过还是做成可以对所有数据进行修改的样子
     * 毕竟这样更像orm的样子
     * @author Lingo
     * @since 2006-01-02 23:26
     * @param info 修改的记录
     */
    public void update(final BoardInfo info) {
        System.out.println("update : " + info);

        try {
            String sql = "update t_gbook set c_reply=? where i_id=?";
            pState = conn.prepareStatement(sql);
            pState.setString(1, info.getReply());
            pState.setString(2, "" + info.getId());
            pState.executeUpdate();
        } catch (Exception ex) {
            System.out.println("error in update() : ex.getMessage() : "
                + ex.getMessage());
            ex.printStackTrace();
        } finally {
            close();
        }
    }

    /**
     * 根据id删除一条记录.
     * @param id id
     */
    public void delete(final int id) {
        System.out.println("delete : " + id);

        try {
            String sql = "delete from t_gbook where i_id=?";
            pState = conn.prepareStatement(sql);
            pState.setInt(1, id);
            pState.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            close();
        }
    }

    /**
     * 关闭连接.
     */
    public void close() {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
        } catch (SQLException ex) {
            System.out.println("error in close() : rs");
            ex.printStackTrace();
        }

        try {
            if (pState != null) {
                pState.close();
                pState = null;
            }
        } catch (SQLException ex) {
            System.out.println("error in close() : pState");
            ex.printStackTrace();
        }

        try {
            if (state != null) {
                state.close();
                state = null;
            }
        } catch (SQLException ex) {
            System.out.println("error in close() : state");
            ex.printStackTrace();
        }
    }
}