Java Servlet DAO实践(二)

package com.seller.servlets.dao;

import java.sql.*;

public class DataBaseConnection {
    private final String DBDRIVER = "com.mysql.jdbc.Driver";
    private final String DBURL = "jdbc:mysql://localhost:3306/javaweb";
    private final String DBUSER = "root";
    private final String DBPASSWORD = "toor";
    private Connection conn = null;
    
    public DataBaseConnection(){
        try{
            Class.forName(DBDRIVER);
            this.conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD);
        }
        catch (Exception e){
            System.out.println("驱动加载失败");
        }
    }
    
    public Connection getConnection(){
        return conn;
    }
        
    public void close(){
        try{
            conn.close();
        }
        catch (Exception e){
            System.out.println("数据库连接关闭失败");
        }
    }
}

package com.seller.servlets.dao;

public class User {
    private int userid;
    private String username;
    private String password;
    public int getUserid(){
        return userid;
    }
    
    public void setUserid(int userid){
        this.userid = userid;
    }
    
    public String getUsername(){
        return username;
    }
    
    public void setUsername(String username){
        this.username = username;
    }
    
    public String getPassword(){
        return password;
    }
    
    public void setPassword(String password){
        this.password = password;
    }
}

package com.seller.servlets.dao;
import java.util.*;


public interface UserDAO {
    public void insert(User user) throws Exception;
    public void update(User user) throws Exception;
    public void delete(int userid) throws Exception;
    public User queryById(int userid) throws Exception;
    public List queryAll() throws Exception;
}

package com.seller.servlets.dao;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class UserDAOImpl implements UserDAO {
    
    // 添加操作
    public void insert(User user) throws Exception{
        String sql = "INSERT INTO user(username, password) VALUES(?,?)";
        PreparedStatement pstmt = null;
        DataBaseConnection dbc = null;
        
        try{
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.executeUpdate();
            pstmt.close();
        }catch (Exception e){
            throw new Exception("操作出现异常");
        }
        finally{
            dbc.close();
        }
    }
    
    // 修改操作
    public void update(User user) throws Exception{
        String sql = "UPDATE user SET username=?, password=? WHERE userid=?";
        PreparedStatement pstmt = null;
        DataBaseConnection dbc = null;
        
        try{
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setString(1, user.getUsername());
            pstmt.setString(1, user.getPassword());
            pstmt.setInt(3, user.getUserid());
            pstmt.executeUpdate();
            pstmt.close();
        }
        catch (Exception e){
            throw new Exception("操作出现异常");
        }
    }
    
    // 删除操作
    public void delete(int userid) throws Exception{
        String sql = "DELETE FROM user WHERE userid=?";
        PreparedStatement pstmt = null;
        DataBaseConnection dbc = null;
        try{
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setInt(1, userid);
            pstmt.executeUpdate();
            pstmt.close();
        }catch (Exception e){
            throw new Exception("操作出现异常");
        }
        finally{
            dbc.close();
        }
    }
    
    // 按ID查询
    public User queryById(int userid) throws Exception{
        User user = null;
        String sql = "SELECT * FROM user WHERE userid=?";
        PreparedStatement pstmt = null;
        DataBaseConnection dbc = null;
        try{
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                user = new User();
                user.setUserid(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
            }
            rs.close();
            pstmt.close();
        }catch (Exception e){
            throw new Exception("操作出现异常");
        }
        finally{
            dbc.close();
        }
        return user;
        
    }
    
    
    public List<User> queryAll() throws Exception{
        List<User> all = new ArrayList<User>();
        String sql = "SELECT * FROM user";
        PreparedStatement pstmt = null;
        DataBaseConnection dbc = null;
        
        try{
            dbc = new DataBaseConnection();
            pstmt = dbc.getConnection().prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                User user = new User();
                user.setUserid(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                all.add(user);
            }
            rs.close();
        }catch (Exception e){
            throw new Exception("操作出现异常");
        }
        finally{
            dbc.close();
        }
        return all;
    }
    
}

package com.seller.servlets.dao;

public class DAOFactory {
    public static UserDAO getUserDAOInstance(){
        return new UserDAOImpl();
    }

}

package com.seller.servlets;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.seller.servlets.dao.*;



@WebServlet("/addUser")
public class addUser extends HttpServlet {
    private static final long serialVersionUID = 1L;
       

    public addUser() {
        super();
    }


    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        UserDAO userDAO = DAOFactory.getUserDAOInstance();
        User user = new User();
        user.setUsername("hello");
        user.setPassword("12356");
        try {
            userDAO.insert(user);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}