Java Servlet DAO实践(二)
Java Servlet DAO实践(二)
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("数据库连接关闭失败"); } } }
DAO VO类(对应表字段)
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; } }
DAO 接口类 (CURD操作定义)
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; }
DAO 接口实现类(CURD操作实现)
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; } }
DAO工厂类(封装方便调用)
package com.seller.servlets.dao; public class DAOFactory { public static UserDAO getUserDAOInstance(){ return new UserDAOImpl(); } }
Servlet 控制器调用DAO操作
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(); } } }