java web-----DAO设计模式（数据库访问）

com.javaweb.dao; import java.sql.*; public class DBConnection { private final String DBDRIVER = "com.mysql.jdbc.Driver"; private final String DBURL = "jdbc:mysql://localhost:3306/javaweb"; private final String USER = "root"; private final String PASSWORD = "root"; private Connection conn=null; public DBConnection() { try { Class.forName(DBDRIVER); this.conn = DriverManager.getConnection(DBURL,USER,PASSWORD); } catch (ClassNotFoundException | SQLException e) { // TODO Auto-generated catch block System.out.println("加载驱动失败"); } } public Connection getConnection() { return this.conn; } public void close() { if(this.conn!=null) { try { this.conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block System.out.println("数据库连接关闭失败"); } } } } View Code

　　2.VO类：

package com.javaweb.dao;

public class User
{
    private int userid;
    private String username;
    private String password;
    
    public User()
    {
        // TODO Auto-generated constructor stub
    }
    
    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;
    }
    
    
}

View Code

　　3.DAO接口：

package com.javaweb.dao;
import java.util.List;

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

View Code

　　4.DAO具体实现：

package com.javaweb.dao;

import java.util.ArrayList;
import java.util.List;
import java.sql.*;

public class UserDAOImpl implements UserDAO
{

    @Override
    public void insert(User user) throws Exception
    {
        // TODO Auto-generated method stub
        DBConnection con = null;
        PreparedStatement pstm = null;
        String sql = "insert into user(username,password) values(?,?)";

        try
        {
            con = new DBConnection();
            pstm = con.getConnection().prepareStatement(sql);
            pstm.setString(1, user.getUsername());
            pstm.setString(2, user.getPassword());
            pstm.executeUpdate();
            if (pstm != null)
            {
                pstm.close();
            }
        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            System.out.println("插入数据操作异常");
        } finally
        {
            if (con != null)
            {
                con.close();
            }
        }
    }

    @Override
    public void delete(int userid) throws Exception
    {
        // TODO Auto-generated method stub
        DBConnection con = null;
        PreparedStatement pstm = null;
        String sql = "delete from user where userid=?";

        try
        {
            con = new DBConnection();
            pstm = con.getConnection().prepareStatement(sql);
            pstm.setInt(1, userid);
            pstm.executeUpdate();
            if (pstm != null)
            {
                pstm.close();
            }
        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            System.out.println("删除数据操作异常");
        } finally
        {
            if (con != null)
            {
                con.close();
            }
        }
    }

    @Override
    public void update(User user) throws Exception
    {
        // TODO Auto-generated method stub
        // TODO Auto-generated method stub
        DBConnection con = null;
        PreparedStatement pstm = null;
        String sql = "update user set username=?,password=? where userid=?";
        
        try
        {
            con = new DBConnection();
            pstm = con.getConnection().prepareStatement(sql);
            pstm.setString(1, user.getUsername());
            pstm.setString(2, user.getPassword());
            pstm.setInt(3, user.getUserid());
            pstm.executeUpdate();
            if (pstm != null)
            {
                pstm.close();
            }
        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            System.out.println("更新数据操作异常,sql:"+sql);
        } finally
        {
            if (con != null)
            {
                con.close();
            }
        }
    }

    @Override
    public User querryById(int userid) throws Exception
    {
        // TODO Auto-generated method stub
        DBConnection con = null;
        PreparedStatement pstm = null;
        ResultSet rs=null;
        User user=null;
        String sql = "select * from user where userid=?";

        try
        {
            //获取连接
            con = new DBConnection();
            //预处理语句
            pstm = con.getConnection().prepareStatement(sql);
            //设置参数
            pstm.setInt(1, userid);
            //执行查询
            rs = pstm.executeQuery();
            //获取结果
            while(rs.next())
            {
                user = new User();
                user.setUserid(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
            }
            if (pstm != null)
            {
                pstm.close();
            }
        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            System.out.println("查询数据操作异常   sql:"+sql);
        } finally
        {
            if (con != null)
            {
                con.close();
            }
        }
        return user;
    }

    @Override
    public List<User> querryALL() throws Exception
    {
        // TODO Auto-generated method stub
        DBConnection con = null;
        PreparedStatement pstm = null;
        ResultSet rs=null;
        List<User> lsu=new ArrayList<User>();
        String sql = "select * from user";

        try
        {
            //获取连接
            con = new DBConnection();
            //预处理语句
            pstm = con.getConnection().prepareStatement(sql);
            //执行查询,返回结果rs
            rs = pstm.executeQuery();
            //获取结果
            while(rs.next())
            {
                User user = new User();
                user.setUserid(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                lsu.add(user);
            }
            if (pstm != null)
            {
                pstm.close();
            }
        } catch (Exception e)
        {
            // TODO Auto-generated catch block
            System.out.println("查询数据操作异常");
        } finally
        {
            if (con != null)
            {
                con.close();
            }
        }
        return lsu;
    }

}

View Code

　　5.DAO工厂（有效避免由于DAO实现改变导致的大量代码修改）

package com.javaweb.dao;

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

View Code

 

二，通过jsp调用相关函数即可进行数据库操作，省去了大量重复代码。

　　代码示例：

<%@page import="com.javaweb.dao.*"%>
<%@page import="org.eclipse.jdt.internal.compiler.apt.model.Factory"%>
<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
    <%
        UserDAO userDAO = DAOFactory.getUserDAOInstance();
        User user=new User();
        user.setUsername("lu");
        user.setPassword("123");
        userDAO.insert(user);
    %>
</body>
</html>

View Code

 

java web-----DAO设计模式（数据库访问）

标签：