簡單 Struts 應用開發教學 (三)：DAO 資料庫操作物件實作

第四步：建立資料庫操作物件 (DAO)

完成對 myuser、mydep 資料表的增、刪、改、查。

userdao.java

package mystruts;
import java.sql.*;
import ConnectionPool.*;
import java.util.*;

public class userdao {
    getDbConnection db = null;
    Connection con = null;

    public userdao() {}

    // 新增使用者
    public void userInsert(userActionForm uaf1) {
        db = new getDbConnection();
        con = db.getCon();
        PreparedStatement ps = null;
        String sql = "insert into myuser (user_id, user_name, dep, tel) values(?,?,?,?)";
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, uaf1.getUser_id());
            ps.setString(2, uaf1.getUser_name());
            ps.setString(3, uaf1.getDep());
            ps.setString(4, uaf1.getTel());
            ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("sql error");
        } finally {
            try { con.close(); db.d_close(); } catch (Exception e) {}
        }
    }

    // 修改使用者
    public void userUpdate(userActionForm uaf1) {
        db = new getDbConnection();
        con = db.getCon();
        PreparedStatement ps = null;
        String sql = "update myuser set user_name=?, dep=?, tel=? where user_id=?";
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, uaf1.getUser_name());
            ps.setString(2, uaf1.getDep());
            ps.setString(3, uaf1.getTel());
            ps.setString(4, uaf1.getUser_id());
            ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("sql error");
        } finally {
            try { con.close(); db.d_close(); } catch (Exception e) {}
        }
    }

    // 查詢單一使用者 (供編輯回顯)
    public userActionForm getUser(String key) {
        db = new getDbConnection();
        con = db.getCon();
        ResultSet rs = null;
        userActionForm uaf = new userActionForm();
        PreparedStatement ps = null;
        String sql = "select * from myuser where user_id=?";
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, key.trim());
            rs = ps.executeQuery();
            if (rs.next()) {
                uaf.setUser_id(rs.getString("user_id"));
                uaf.setUser_name(rs.getString("user_name"));
                uaf.setDep(rs.getString("dep"));
                uaf.setTel(rs.getString("tel"));
                uaf.setAction("edit");
            }
        } catch (SQLException e) {
            System.out.println("sql error");
        } finally {
            try { con.close(); db.d_close(); } catch (Exception e) {}
        }
        System.out.println("mod" + key);
        return uaf;
    }

    // 刪除使用者
    public void delUser(String key) {
        db = new getDbConnection();
        con = db.getCon();
        PreparedStatement ps = null;
        String sql = "delete from myuser where user_id=?";
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1, key.trim());
            ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("sql error");
        } finally {
            try { con.close(); db.d_close(); } catch (Exception e) {}
        }
        System.out.println("del" + key);
    }

    // 查詢所有使用者 (含部門名稱 Join)
    public Collection getAlluser() {
        db = new getDbConnection();
        con = db.getCon();
        ResultSet rs = null;
        PreparedStatement ps = null;
        ArrayList rslist = new ArrayList();
        String sql = "select * from myuser u, mydep d where u.dep=d.dep_id";
        try {
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                HashMap rscol = new HashMap();
                rscol.put("user_id", rs.getString("user_id"));
                rscol.put("user_name", rs.getString("user_name"));
                rscol.put("dep", rs.getString("dep"));
                rscol.put("dep_name", rs.getString("dep_name"));
                rscol.put("tel", rs.getString("tel"));
                rslist.add(rscol);
            }
        } catch (SQLException e) {
            System.out.println("sql error");
        } finally {
            try { con.close(); db.d_close(); } catch (Exception e) {}
        }
        return rslist;
    }
}

DAO 類別封裝所有 JDBC 操作，使用連線池管理連線，各方法獨立開啟/關閉連線。下一步將實作 Action 類別串聯業務流程。