20160406javaweb 之JDBC简单案例

前几天写的user注册登录注销案例，没有用到数据库，现在做出改动，使用数据库存储信息：

一、首先我们需要建立一个数据库：

如下图：

创建数据库的代码如下：

-- 导出 database02 的数据库结构
CREATE DATABASE IF NOT EXISTS `database02` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `database02`;


-- 导出  表 database02.users 结构
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(50) NOT NULL,
  `nickname` varchar(40) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、创建包和类

1.创建一个接口

package com.dzq.dao;

import com.dzq.domian.User;

public interface UserDao {
   
    /**
     * 根据用户名查找用户
     * @param username 用户名
     * @return 根据用户名找到的用户信息，如果没找到，返回null
     */
    public User findUserByUserName(String username);
    
    /**
     * 添加用户
     * @param user 要添加用户信息的bean
     */
    public void addUser(User user);
    /**
     * 根据用户名密码查找用户信息
     * @param username 用户名
     * @param password 密码
     * @return 找到的用户，找不到返回null 
     */
    public User findUserByUNAndPWD(String username,String password);
}

2.创建操作数据库的工具类：

package com.dzq.util;

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils {
    
   private static  Properties prop=null;
   
   private JDBCUtils(){
    
   }
   
   /**
    * 静态代码块，加载数据库配置文件
    */
   static{
       try{
        prop=new Properties();
        prop.load(new FileReader(JDBCUtils.class.getClassLoader().getResource("config.properties").getPath()));
       }catch(Exception e){
           e.printStackTrace();
           throw new RuntimeException();
       }
   }
   
   /**
    * 获取连接
    */
   public static Connection getConn() throws Exception{
      
       
       Class.forName(prop.getProperty("driver"));
       return DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("user"), prop.getProperty("password"));
   }
   
   /**
    * 关闭连接
    */
   public static void close(ResultSet rs,Statement stat,Connection conn){
       if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                rs=null;
            }
        }
        if(stat!=null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                stat=null;
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                conn=null;
            }
        }
   }
}

3.创建异常处理类

package com.dzq.exception;

public class MsgException extends Exception {
public MsgException(){

}

public MsgException(String msg){
    super(msg);
}
}

4.创建javabean类

package com.dzq.domian;

import java.io.Serializable;

import com.dzq.exception.MsgException;

public class User implements Serializable{
    private int id;
private String username;
private String password;
private String password2;
private String nickname;
private String email;


public int getId() {
    return id;
}
public void setId(int id) {
    this.id = id;
}
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;
}
public String getPassword2() {
    return password2;
}
public void setPassword2(String password2) {
    this.password2 = password2;
}
public String getNickname() {
    return nickname;
}
public void setNickname(String nickname) {
    this.nickname = nickname;
}
public String getEmail() {
    return email;
}
public void setEmail(String email) {
    this.email = email;
}

public User(){
    
}
public User(String username, String password, String password2,
        String nickname, String email) {
    
    this.username = username;
    this.password = password;
    this.password2 = password2;
    this.nickname = nickname;
    this.email = email;
}
@Override
public String toString() {
    return username+":"+password;
}
public void checkValue() throws MsgException{
    if(username==null||"".equals(username)){
        throw new MsgException("用户名不能为空");
    }
    if(password==null||"".equals(password)){
        throw new MsgException("密码不能为空");
    }
    if(password2==null||"".equals(password2)){
        throw new MsgException("密码不能为空");
    }
    if(!password.equals(password2)){
        throw new MsgException("两次密码输入不一致");
    }
    if(nickname==null||"".equals(nickname)){
        throw new MsgException("昵称不能为空");
    }
    if(email==null||"".equals(email)){
        throw new MsgException("邮箱不能为空");
    }
}

}

5.实现上述接口

package com.dzq.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.dzq.domian.User;
import com.dzq.util.JDBCUtils;

public class MySqlUserDao implements UserDao{
    
    @Override
    public User findUserByUserName(String username) {
        String sql="select * from users where username='"+username+"'";
        Connection conn=null;
        Statement stat=null;
        ResultSet rs=null;
        try {
            conn=JDBCUtils.getConn();
            stat=conn.createStatement();
            rs=stat.executeQuery(sql);
            if(rs.next()){
                User user=new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setNickname(rs.getString("nickname"));
                user.setEmail(rs.getString("email"));
                return user;
            }else{
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            JDBCUtils.close(rs, stat, conn);
        }
    }

    @Override
    public void addUser(User user) {
    String sql="insert into users values (null,'"+user.getUsername()+"','"+user.getPassword()+"','"+user.getNickname()+"','"+user.getEmail()+"')";
    Connection conn=null;
    Statement stat=null;
    ResultSet rs=null;
    try {
        conn=JDBCUtils.getConn();
        stat=conn.createStatement();
        int count=stat.executeUpdate(sql);
        
    } catch (Exception e) {
        e.printStackTrace();
        throw new RuntimeException(e);
    }finally{
        JDBCUtils.close(rs, stat, conn);
    }
    }

    @Override
    public User findUserByUNAndPWD(String username, String password) {
        String sql="select * from users where username='"+username+"'and password='"+password+"'";
        Connection conn=null;
        Statement stat=null;
        ResultSet rs=null;
        try {
            conn=JDBCUtils.getConn();
            stat=conn.createStatement();
            rs=stat.executeQuery(sql);
            if(rs.next()){
                User user=new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setNickname(rs.getString("nickname"));
                user.setEmail(rs.getString("email"));
                return user;
            }else{
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally{
            JDBCUtils.close(rs, stat, conn);
        }
    }

}

6.创建service类

package com.dzq.service;

import com.dzq.dao.MySqlUserDao;
import com.dzq.dao.XmlUserDao;
import com.dzq.domian.User;
import com.dzq.exception.MsgException;

public class UserService {
    //private  XmlUserDao dao=new XmlUserDao();
    private MySqlUserDao dao=new MySqlUserDao();
    /**
     * 添加用户
     * @param user
     * @throws MsgException 
     */
    public void registUser(User user) throws MsgException{
        if(dao.findUserByUserName(user.getUsername())!=null){
            throw new MsgException("用户名已经存在");
        }
        dao.addUser(user);
    }
    /**
     * 检查用户名是否正确
     * @param username
     * @param password
     */
    public User isUser(String username,String password){
        return dao.findUserByUNAndPWD(username, password);
        
    }
}

7.建立几个servlet

package com.dzq.web;

import java.io.IOException;
import java.net.URLEncoder;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.dzq.domian.User;
import com.dzq.service.UserService;


@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
   
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        String username=request.getParameter("username");
        String password=request.getParameter("password");
        UserService service=new UserService();
        User user=service.isUser(username, password);
        if(user==null){
            request.setAttribute("msg", "用户名或者密码错误");
            request.getRequestDispatcher("/login.jsp").forward(request, response);
        }else{
            request.getSession().setAttribute("user", user);
            if("ok".equals(request.getParameter("remname"))){
                Cookie remNameC=new Cookie("remname",URLEncoder.encode(user.getUsername(),"utf-8"));
                remNameC.setPath(request.getContextPath());
                remNameC.setMaxAge(3600*24*30);
                response.addCookie(remNameC);
            }else{
                Cookie remNameC=new Cookie("remname","");
                remNameC.setPath(request.getContextPath());
                remNameC.setMaxAge(0);
                response.addCookie(remNameC);
            }
            response.sendRedirect(request.getContextPath()+"/index.jsp");
        }
    }

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

}

package com.dzq.web;

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;


@WebServlet("/LogOutServlet")
public class LogOutServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        if(request.getSession(false)!=null&&request.getSession().getAttribute("user")!=null){
            request.getSession().invalidate();
            response.sendRedirect(request.getContextPath()+"/index.jsp");
        }
    }

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

}

package com.dzq.web;

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 org.apache.commons.beanutils.BeanUtils;

import com.dzq.domian.User;
import com.dzq.exception.MsgException;
import com.dzq.service.UserService;


@WebServlet("/RegistServlet")
public class RegistServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
   
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            UserService service=new UserService();
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        //1。检验验证码
        String valistr=request.getParameter("valistr");
        String valistr2=(String) request.getSession().getAttribute("valistr");
        if(valistr==null||valistr2==null||!valistr.equals(valistr2)){
            request.setAttribute("msg", "验证码不正确");
            request.getRequestDispatcher("/regist.jsp").forward(request, response);
            return;
        }
        //2.封装数据，校验数据
        User user=new User();
        BeanUtils.populate(user, request.getParameterMap());
        user.checkValue();
        service.registUser(user);
        request.getSession().setAttribute("user", user);
        response.getWriter().write("恭喜您注册成功，3秒回到主页");
        response.setHeader("refresh", "3;url="+request.getContextPath()+"/index.jsp");
        }catch(MsgException e){
            request.setAttribute("msg",e.getMessage());
            request.getRequestDispatcher("/regist.jsp").forward(request, response);
            return;
        }catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } 
        //3.调用service方法添加用户
        
        //4.提示注册成功，3秒跳转主页
    }

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

}

package com.dzq.web;

import java.awt.Color;
import java.awt.Font;
import java.awt.Graphics2D;
import java.awt.image.BufferedImage;
import java.io.IOException;
import java.util.Random;

import javax.imageio.ImageIO;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


@WebServlet("/ValiImg")
public class ValiImg extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
   
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        response.setDateHeader("Expires", -1);
        response.setHeader("Cache-Control", "no-cache");
        response.setHeader("Pragma","no-cache");
        //1.在内存中构建一张图片
        int height=30;
        int width=120;
        int xpyl=5;
        int ypyl=22;
        int bang=20;
        BufferedImage img=new BufferedImage(width, height, BufferedImage.TYPE_INT_BGR);
        //2.获取图像上的画布
        Graphics2D g=(Graphics2D) img.getGraphics();
        //3.设置背景色
        g.setColor(Color.LIGHT_GRAY);
        g.fillRect(0, 0, width, height);
        //4.设置边框
        g.setColor(Color.BLUE);
        g.drawRect(0, 0, width-2, height-2);
        //5.画干扰线
        for(int i=0;i<5;i++){
        g.setColor(Color.RED);
        g.drawLine(randNum(0, width), randNum(0, height), randNum(0, width), randNum(0, height));
        }
        //6.写字
        String base = "u7684u4e00u4e86u662fu6211u4e0du5728u4ebau4eecu6709u6765u4ed6u8fd9u4e0au7740u4e2au5730u5230u5927u91ccu8bf4u5c31u53bbu5b50u5f97u4e5fu548cu90a3u8981u4e0bu770bu5929u65f6u8fc7u51fau5c0fu4e48u8d77u4f60u90fdu628au597du8fd8u591au6ca1u4e3au53c8u53efu5bb6u5b66u53eau4ee5u4e3bu4f1au6837u5e74u60f3u751fu540cu8001u4e2du5341u4eceu81eau9762u524du5934u9053u5b83u540eu7136u8d70u5f88u50cfu89c1u4e24u7528u5979u56fdu52a8u8fdbu6210u56deu4ec0u8fb9u4f5cu5bf9u5f00u800cu5df1u4e9bu73b0u5c71u6c11u5019u7ecfu53d1u5de5u5411u4e8bu547du7ed9u957fu6c34u51e0u4e49u4e09u58f0u4e8eu9ad8u624bu77e5u7406u773cu5fd7u70b9u5fc3u6218u4e8cu95eeu4f46u8eabu65b9u5b9eu5403u505au53ebu5f53u4f4fu542cu9769u6253u5462u771fu5168u624du56dbu5df2u6240u654cu4e4bu6700u5149u4ea7u60c5u8defu5206u603bu6761u767du8bddu4e1cu5e2du6b21u4eb2u5982u88abu82b1u53e3u653eu513fu5e38u6c14u4e94u7b2cu4f7fu5199u519bu5427u6587u8fd0u518du679cu600eu5b9au8bb8u5febu660eu884cu56e0u522bu98deu5916u6811u7269u6d3bu90e8u95e8u65e0u5f80u8239u671bu65b0u5e26u961fu5148u529bu5b8cu5374u7ad9u4ee3u5458u673au66f4u4e5du60a8u6bcfu98ceu7ea7u8ddfu7b11u554au5b69u4e07u5c11u76f4u610fu591cu6bd4u9636u8fdeu8f66u91cdu4fbfu6597u9a6cu54eau5316u592au6307u53d8u793eu4f3cu58ebu8005u5e72u77f3u6ee1u65e5u51b3u767eu539fu62ffu7fa4u7a76u5404u516du672cu601du89e3u7acbu6cb3u6751u516bu96beu65e9u8bbau5417u6839u5171u8ba9u76f8u7814u4ecau5176u4e66u5750u63a5u5e94u5173u4fe1u89c9u6b65u53cdu5904u8bb0u5c06u5343u627eu4e89u9886u6216u5e08u7ed3u5757u8dd1u8c01u8349u8d8au5b57u52a0u811au7d27u7231u7b49u4e60u9635u6015u6708u9752u534au706bu6cd5u9898u5efau8d76u4f4du5531u6d77u4e03u5973u4efbu4ef6u611fu51c6u5f20u56e2u5c4bu79bbu8272u8138u7247u79d1u5012u775bu5229u4e16u521au4e14u7531u9001u5207u661fu5bfcu665au8868u591fu6574u8ba4u54cdu96eau6d41u672au573au8be5u5e76u5e95u6df1u523bu5e73u4f1fu5fd9u63d0u786eu8fd1u4eaeu8f7bu8bb2u519cu53e4u9ed1u544au754cu62c9u540du5440u571fu6e05u9633u7167u529eu53f2u6539u5386u8f6cu753bu9020u5634u6b64u6cbbu5317u5fc5u670du96e8u7a7fu5185u8bc6u9a8cu4f20u4e1au83dcu722cu7761u5174u5f62u91cfu54b1u89c2u82e6u4f53u4f17u901au51b2u5408u7834u53cbu5ea6u672fu996du516cu65c1u623fu6781u5357u67aau8bfbu6c99u5c81u7ebfu91ceu575au7a7au6536u7b97u81f3u653fu57ceu52b3u843du94b1u7279u56f4u5f1fu80dcu6559u70edu5c55u5305u6b4cu7c7bu6e10u5f3au6570u4e61u547cu6027u97f3u7b54u54e5u9645u65e7u795eu5ea7u7ae0u5e2eu5566u53d7u7cfbu4ee4u8df3u975eu4f55u725bu53d6u5165u5cb8u6562u6389u5ffdu79cdu88c5u9876u6025u6797u505cu606fu53e5u533au8863u822cu62a5u53f6u538bu6162u53d4u80ccu7ec6";
        StringBuffer buffer=new StringBuffer();
        
        for(int i=0;i<4;i++){
            g.setColor(new Color(randNum(0, 255),randNum(0, 255),randNum(0, 255)));
            g.setFont(new Font("黑体",Font.BOLD,bang));
            int r=randNum(-45, 45);
            g.rotate(1.0*r/180*Math.PI,xpyl+i*30, ypyl);
            String s=base.charAt(randNum(0, base.length()-1))+"";
            buffer.append(s);
            g.drawString(s, xpyl+i*30, ypyl);
            g.rotate(1.0*-r/180*Math.PI,xpyl+i*30, ypyl);
        }
        request.getSession().setAttribute("valistr", buffer.toString());
        System.out.println(buffer.toString());
        //将图片输出到浏览器
        ImageIO.write(img, "jpg", response.getOutputStream());
        
    }
    private Random rand=new Random();
    private int randNum(int begin,int end){
        
     return rand.nextInt((end-begin)+begin);        
        
    }
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    doGet(request, response);
    }
}

8. jsp页面：

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
</head>
<body>
<h1>我的网站</h1><hr>
<c:if test="${sessionScope.user!=null}">
欢迎回来！${sessionScope.user.username }<a href="${pageContext.request.contextPath }/LogOutServlet">注销</a>
</c:if>

<c:if test="${sessionScope.user==null}">
游客，欢迎你！<a href="${pageContext.request.contextPath }/regist.jsp">注册</a>|<a href="${pageContext.request.contextPath }/login.jsp">登录</a>
</c:if>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
    <%@taglib uri="http://www.dzq.com/UserTag" prefix="UserTag" %>
<html>
<head>

</head>
<body>
<div align="center">
<h1>我的网站_登录</h1><hr>
<font color="red">${msg }</font>
<form action="${pageContext.request.contextPath }/LoginServlet" method="post">
<table border="1">
       <tr>
          <td>用户名：</td>
          
          <td><input type="text" name="username" value="<UserTag:URLDecoder content="${cookie.remname.value }" encode="utf-8"/>"/></td>
       </tr>
       <tr>
          <td>密码：</td>
          <td><input type="password" name="password"/></td>
       </tr>
       <tr>
          <td><input type="submit" value="登录"/></td>
          <td><input type="checkbox" value="ok" name="remname" 
          <c:if test="${cookie.remname!=null }">
          checked="checked"
          </c:if>
          />记住用户名</td>
       </tr>
</table>
</form>
</div>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<html>
<head>
<script type="text/javascript">
function changeImg(img){
    img.src=img.src+"?time="+new Date().getTime();
}
</script>
</head>
 <font color="red" >${msg }</font>
<body>
<div align="center">
<h1>我的网站_注册</h1>
<form action="${pageContext.request.contextPath }/RegistServlet" method="post">
<table border="1">
    <tr>
        <td>用户名：</td>
        <td><input type="text" name="username" value="${param.username }"/></td>
    <tr/>
     <tr>
        <td>密码：</td>
        <td><input type="password" name="password" /></td>
    <tr/>
     <tr>
        <td>确认密码：</td>
        <td><input type="password" name="password2" /></td>
    <tr/>
     <tr>
        <td>昵称：</td>
        <td><input type="text" name="nickname" value="${param.nickname }"/></td>
    <tr/>
     <tr>
        <td>邮箱</td>
        <td><input type="email" name="email" value="${param.email }"/></td>
    <tr/>
     <tr>
        <td>验证码：</td>
        <td><input type="text" name="valistr" /></td>
    <tr/>
     <tr>
        <td><input type="submit" value="注册"/></td>
        <td><img src="${pageContext.request.contextPath }/ValiImg" style="cursor: pointer;" onclick="changeImg(this)"></td>
    <tr/>

</table>
</form>
</div>
</body>
</html>

相关阅读:
eureka的fetch-registry属性解释
 数据结构设计
 typescript let和const区别
 JDK8新特性
 Synchronized的内存可见性
 java实现打印功能
 idea单元测试jpa注入失败问题----来自Spring Cloud微服务实战-idea版的廖师兄的product
eclipse快速生成接口
 读取 Excel 之 Epplus
[转][Dapper]参数化查询慢
原文地址：https://www.cnblogs.com/xiaoduc-org/p/5361116.html