web项目 maven下 使用servlet调用 mysql数据库

在登录界面的基础下，需要先配置 mysql 数据库，然后修改 Verify.java 就可以了，登陆界面项目：点击打开链接

首先建立简单的数据库和表：

 

CREATE TABLE users (  
userId INT PRIMARY KEY,  
username VARCHAR(20),  
passwd VARCHAR(20),  
grade INT);  
  
INSERT INTO `users` (`userId`, `username`, `passwd`, `grade`) VALUES('1','admin','123','1');  
INSERT INTO `users` (`userId`, `username`, `passwd`, `grade`) VALUES('2','hou','123','2');  
INSERT INTO `users` (`userId`, `username`, `passwd`, `grade`) VALUES('3','test','123','3');

然后通过登录界面，调用数据库判断，如果是数据库中的用户名，就在welcome页面输出用户名和密码。

 

 

package com.busymonkey;

import javax.servlet.ServletException;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;

public class Verify extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    public Verify() {
        super();
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    	Connection ct = null;
    	Statement sm = null;
    	ResultSet rs = null;
    	try {
        	String u=request.getParameter("username");
        	String p=request.getParameter("passwd");
        	
        	//数据库连接
        	Class.forName("com.mysql.jdbc.Driver");
        	//得到连接
        	ct = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db_hou", "root", "123456");
        	//创建Statement
        	sm = ct.createStatement();
        	rs = sm.executeQuery("select * from users where username='"+u
        			+"' and passwd='"+p+"'");
        	
        	if (rs.next()) {
        		HttpSession hs = request.getSession(true);
        		hs.setMaxInactiveInterval(20);
        		hs.setAttribute("pass", "ok");
        		response.sendRedirect("welcome?uname=" + u + "&upass=" + p);
        	}
        	else {
        		response.sendRedirect("login");
        	}
        }
        catch (Exception ex) {
        	ex.printStackTrace();
        }finally{
        	try {
        		if (rs!=null) rs.close();
        		if (sm!=null) sm.close();
        		if (ct!=null) ct.close();
        	}
        	catch (Exception ex) {
        		ex.printStackTrace();
        	}
        }
    }

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

}

然后maven pom文件包括依赖

 

 

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.31</version>
</dependency>

 

这里注意一个问题，通常情况下maven自动下载的依赖会放到项目的 target 目录的项目文件夹下，但是在 tomcat 服务器启动是在 tomcat 安装目录下的 webapps 目录下，而在maven项目  install 的时候，是把 src 目录下的 WEB-INI 文件同步到 tomcat 安装文件夹下的 webapps 目录中，所以如果 tomcat 的 webapps 目录下，相对应启动的项目中没有同步lib依赖，那就把maven 项目中target中的lib依赖拷贝到src文件夹的main文件夹中的lib中，再进行maven install。这样就可以同步依赖文件了。

 

 

对于sql注入漏洞，修改的verify.java 代码如下：

 

package com.busymonkey;

import javax.servlet.ServletException;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;

public class Verify extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    public Verify() {
        super();
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    	Connection ct = null;
    	Statement sm = null;
    	ResultSet rs = null;
    	try {
        	String u=request.getParameter("username");
        	String p=request.getParameter("passwd");
        	
        	//数据库连接
        	Class.forName("com.mysql.jdbc.Driver");
        	//得到连接
        	ct = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db_hou", "root", "123456");
        	//创建Statement
        	sm = ct.createStatement();
        	rs = sm.executeQuery("select * from users where username='"+u+"' and passwd='"+p+"'");
        	
        	if (rs.next()) {//next能进来说明用户是存在的
        		String dbPasswd = rs.getString("passwd");
        		if (dbPasswd.equals(p)) {
	        		HttpSession hs = request.getSession(true);
	        		hs.setMaxInactiveInterval(20);
	        		hs.setAttribute("pass", "ok");
	        		response.sendRedirect("welcome?uname=" + u + "&upass=" + p); 			
        		}
        		else
        		{
        			response.sendRedirect("login");
        		}
        	}
        	else {
        		response.sendRedirect("login");
        	}
        }
        catch (Exception ex) {
        	ex.printStackTrace();
        }finally{
        	try {
        		if (rs!=null) rs.close();
        		if (sm!=null) sm.close();
        		if (ct!=null) ct.close();
        	}
        	catch (Exception ex) {
        		ex.printStackTrace();
        	}
        }
    }

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

}

 

如果要用servlet显示图片，则在wel页面加入一句话即可，然后图片放到 tomcat下webapps下的项目文件夹中的WEB-INF文件夹下，新建一个imgs的文件夹，将图片放在里面即可：

 

 

package com.busymonkey;

import javax.servlet.ServletException;
import javax.servlet.http.*;
import java.io.*;

public class WelCome extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    public WelCome() {
        super();
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        HttpSession hs = request.getSession(true);
        String val = (String) hs.getAttribute("pass");//非法登陆返回空
        if ( val == null )
        {
        	try {
        		response.sendRedirect("login");
        	}
        	catch (Exception ex) {
        		ex.printStackTrace();
        	}
        }
    	String u = request.getParameter("uname");
        String p = request.getParameter("upass");
    	try {
        	PrintWriter pw = response.getWriter();
        	pw.println("<img src=./imgs/1.GIF ><br>");
        	pw.println("Welcome!!!! " + u + " pass=" + p);
        }
        catch (Exception ex) {
        	ex.printStackTrace();
        }
    }

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

}