1.使用druid连接池 

 

使用properties配置文件：

driverClassName= com.mysql.cj.jdbc.Driver
url= jdbc:mysql://localhost:3306/book?useSSL=true&setUnicode=true&charsetEncoding=UTF-8&serverTimezone=GMT%2B8
username= root
password= 123456
#初始化链接数量
initialSize=5
#最大链接数
maxActive=10
#最大等待时间
maxWait=3000

工具类：

package com.util;


import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


public class JDBCUtils {
    //定义成员变量
    private static DataSource ds = null;
    //定义静态代码块
    static {
        try {
            //加载配置文件
            Properties pro = new Properties();
            pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            //获取DataSource
            ds = DruidDataSourceFactory.createDataSource(pro);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取链接
    public static Connection getConnection() throws SQLException{
        return ds.getConnection();
    }
    //释放资源
    public static void close(Statement stmt,Connection conn){
        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    public static void close(ResultSet rs,Statement stmt,Connection conn){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        close(stmt,conn);
    }
    //获取链接池方法
    public static  DataSource getDataSource(){
        return ds;
    }
}

Dao接口： 

 

 dao实现类：

查询：

 //定义sql
            String sql = "select * from user where username = ? and password = ?";
//执行sql
            User user1 = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), user.getUsername(), user.getPassword());
            

 增加：

 //定义sql
String sql ="insert into user values(null,?,?)";
//执行sql
int update = template.update(sql, user.getUsername(), user.getPassword());

删除：

 //定义sql
        String sql = "delete from shopping where id = ?";
 //执行sql
        int update = template.update(sql, id);

修改：

//定义sql
        String sql ="update books set img = ? , name = ? , price = ? , classly = ? , detail = ? where id = ?";
        //执行sql
        int update = template.update(sql, books.getImg(), books.getName(), books.getPrice(), books.getClassly(), books.getDetail(), books.getId());
       

 

package com.dao;

import com.domain.Admin;
import com.domain.Books;
import com.domain.Shopping;
import com.domain.User;
import com.util.JDBCUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.concurrent.TimeoutException;

public class DaoImpl implements Dao {
    //使用JdbcTemplate
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    //用户登录
    @Override
    public User login(User user) {
        try {
            //定义sql
            String sql = "select * from user where username = ? and password = ?";
            //执行sql
            User user1 = template.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), user.getUsername(), user.getPassword());
            //返回结果
            return user1;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }
    //用户注册
    @Override
    public int addUser(User user) {
        try {
            //定义sql
            String sql ="insert into user values(null,?,?)";
            //执行sql
            int update = template.update(sql, user.getUsername(), user.getPassword());
            return update;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return 0;
        }
    }
    //遍历图书
    @Override
    public List<Books> selectBooks(String classly) {
        //定义sql
        String sql = "select * from books where classly = ?";
        //执行sql
        List<Books> query = template.query(sql, new RowMapper<Books>() {
            @Override
            public Books mapRow(ResultSet resultSet, int i) throws SQLException {
                Books b = new Books();
                b.setImg(resultSet.getString("img"));
                b.setName(resultSet.getString("name"));
                b.setPrice(resultSet.getInt("price"));
                b.setDetail(resultSet.getString("detail"));
                return b;
            }
        }, classly);
        return query;
    }
    //添加图书
    @Override
    public int addBook(Books books) {
        try {
            //定义sql
            String sql = "insert into books values(null,?,?,?,?,?)";
            //执行sql
            int update = template.update(sql, books.getImg(),
                    books.getName(), books.getPrice(),
                    books.getClassly(), books.getDetail());
            //返回值
            return update;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return 0;
        }
    }
    //查询图书详情
    @Override
    public Books selectDetail(String name) {
        //定义sql
        String sql ="select * from books where name = ?";
        //执行sql
        Books query = template.queryForObject(sql, new BeanPropertyRowMapper<Books>(Books.class), name);
        //返回
        return query;
    }
    //加入购物车
    @Override
    public int addShopping(Shopping shopping) {
        try {
            //定义sql
            String sql = "insert into shopping values(null,?,?,?,?,?)";
            int update = template.update(sql,shopping.getImg(), shopping.getUsername(), shopping.getName(), shopping.getNumber(), shopping.getPrice());
            //返回值
            return update;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return 0;
        }
    }
    //遍历购物车
    @Override
    public List<Shopping> selectShopping(String username) {
        //定义sql
        String sql = "select * from shopping where username = ?";
        List<Shopping> query = template.query(sql, new RowMapper<Shopping>() {
            @Override
            public Shopping mapRow(ResultSet resultSet, int i) throws SQLException {
                Shopping shopping = new Shopping();
                shopping.setId(resultSet.getInt("id"));
                shopping.setImg(resultSet.getString("img"));
                shopping.setName(resultSet.getString("name"));
                shopping.setNumber(resultSet.getInt("number"));
                shopping.setPrice(resultSet.getInt("price"));
                return shopping;
            }
        }, username);
        return query;
    }
    //删除购物车
    @Override
    public int deleteShopping(int id) {
        //定义sql
        String sql = "delete from shopping where id = ?";
        //执行sql
        int update = template.update(sql, id);
        //返回执行结果
        return update;
    }
    //清空购物车
    @Override
    public int deleteS(String username) {
        //定义sql
        String sql = "delete from shopping where username = ?";
        //执行sql
        int update = template.update(sql, username);
        //返回执行结果
        return update;
    }
    //管理员登录
    @Override
    public Admin admin(Admin admin) {
        try {
            //定义sql
            String sql = "select * from admin where username = ? and password = ?";
            //执行sql
            Admin admin1 = template.queryForObject(sql, new BeanPropertyRowMapper<Admin>(Admin.class), admin.getUsername(), admin.getPassword());
            return admin1;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }
    //遍历图书
    @Override
    public List<Books> BOOKS_LIST() {
        //定义sql
        String sql ="select * from books";
        //执行sql
        List<Books> query = template.query(sql, new RowMapper<Books>() {
            @Override
            public Books mapRow(ResultSet resultSet, int i) throws SQLException {
                Books b = new Books();
                b.setId(resultSet.getInt("id"));
                b.setImg(resultSet.getString("img"));
                b.setName(resultSet.getString("name"));
                b.setPrice(resultSet.getInt("price"));
                b.setClassly(resultSet.getString("classly"));
                b.setDetail(resultSet.getString("detail"));
                return b;
            }
        });
        return query;
    }
    //删除图书
    @Override
    public int deleteBooks(String name) {
        //定义sql
        String sql = "delete from books where name = ?";
        //执行sql
        int update = template.update(sql, name);
        //返回
        return update;
    }
    //修改图书
    @Override
    public int modifyBook(Books books) {
        //定义sql
        String sql ="update books set img = ? , name = ? , price = ? , classly = ? , detail = ? where id = ?";
        //执行sql
        int update = template.update(sql, books.getImg(), books.getName(), books.getPrice(), books.getClassly(), books.getDetail(), books.getId());
        //返回
        return update;
    }
    //遍历用户
    @Override
    public List<User> selectUser() {
        //定义sql
        String sql ="select * from user";
        //执行sql
        List<User> query = template.query(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                User u = new User();
                u.setId(resultSet.getInt("id"));
                u.setUsername(resultSet.getString("username"));
                u.setPassword(resultSet.getString("password"));
                return u;
            }
        });
        return query;
    }
    //修改用户
    @Override
    public int modifyUser(User user) {
        //定义sql
        String sql = "update user set username = ? , password = ? where id = ?";
        //执行sql
        int update = template.update(sql, user.getUsername(), user.getPassword(), user.getId());
        //返回
        return update;
    }

    @Override
    public int deleteUser(int id) {
        //定义sql
        String sql ="delete from user where id =?";
        //执行sql
        int update = template.update(sql, id);
        return update;
    }
}

登录Servlet控制器：

package com.demo;

import com.dao.Dao;
import com.dao.DaoImpl;
import com.domain.User;
import org.apache.commons.beanutils.BeanUtils;

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 javax.servlet.http.HttpSession;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.Map;

@WebServlet("/loginServlet")
public class LoginServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //设置编码格式
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        //获取数据
        Map<String, String[]> map = request.getParameterMap();
        //封装数据
        User user = new User();
        try {
            BeanUtils.populate(user,map);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        //调用dao
        Dao dao = new DaoImpl();
        User login = dao.login(user);
        if (login != null){
            HttpSession session = request.getSession();
            session.setAttribute("username",user.getUsername());
            response.sendRedirect(request.getContextPath()+"/indexServlet?classly=1");
        }else {
            request.setAttribute("user","账号或密码错误");
            request.getRequestDispatcher("login.jsp").forward(request,response);
        }
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        this.doPost(request, response);
    }
}

简单的过滤器实现：判断用户是否登录，登录就放行

package com.listener;

import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import javax.servlet.http.HttpServletRequest;
import java.io.IOException;

/**
 * 登录验证的过滤器
 */
@WebFilter("/*")
public class LoginFilter implements Filter {
    public void destroy() {
    }

    public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws ServletException, IOException {
        //强制转换
        HttpServletRequest request = (HttpServletRequest) req;
        //1.获取资源请求路径
        String uri = request.getRequestURI();
        //2.判断是否包含登录相关资源路径,要注意排除掉css/图片/js等资源
        if (uri.contains("/login.jsp") || uri.contains("/loginServlet")||
                uri.contains("/login2.jsp")||uri.contains("/login2Servlet") ||
                uri.contains("/css/") || uri.contains("/img/") ||
                uri.contains("adlogin.jsp") || uri.contains("/adLoginServlet")){
            //包含，用户就是想登录，放行
            chain.doFilter(req, resp);
        }else {
            //不包含，需要验证用户是否登录
            //3.从获取session中获取username
            Object username = request.getSession().getAttribute("username");
            if (username != null){
                //登录了，放行
                chain.doFilter(req,resp);
            }else {
                //没有登录，跳转登录页面
                request.setAttribute("user","您尚未登录，请登录");
                request.getRequestDispatcher("/login.jsp").forward(request,resp);
            }

        }
        //chain.doFilter(req, resp);
    }

    public void init(FilterConfig config) throws ServletException {

    }

}