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 = "\u7684\u4e00\u4e86\u662f\u6211\u4e0d\u5728\u4eba\u4eec\u6709\u6765\u4ed6\u8fd9\u4e0a\u7740\u4e2a\u5730\u5230\u5927\u91cc\u8bf4\u5c31\u53bb\u5b50\u5f97\u4e5f\u548c\u90a3\u8981\u4e0b\u770b\u5929\u65f6\u8fc7\u51fa\u5c0f\u4e48\u8d77\u4f60\u90fd\u628a\u597d\u8fd8\u591a\u6ca1\u4e3a\u53c8\u53ef\u5bb6\u5b66\u53ea\u4ee5\u4e3b\u4f1a\u6837\u5e74\u60f3\u751f\u540c\u8001\u4e2d\u5341\u4ece\u81ea\u9762\u524d\u5934\u9053\u5b83\u540e\u7136\u8d70\u5f88\u50cf\u89c1\u4e24\u7528\u5979\u56fd\u52a8\u8fdb\u6210\u56de\u4ec0\u8fb9\u4f5c\u5bf9\u5f00\u800c\u5df1\u4e9b\u73b0\u5c71\u6c11\u5019\u7ecf\u53d1\u5de5\u5411\u4e8b\u547d\u7ed9\u957f\u6c34\u51e0\u4e49\u4e09\u58f0\u4e8e\u9ad8\u624b\u77e5\u7406\u773c\u5fd7\u70b9\u5fc3\u6218\u4e8c\u95ee\u4f46\u8eab\u65b9\u5b9e\u5403\u505a\u53eb\u5f53\u4f4f\u542c\u9769\u6253\u5462\u771f\u5168\u624d\u56db\u5df2\u6240\u654c\u4e4b\u6700\u5149\u4ea7\u60c5\u8def\u5206\u603b\u6761\u767d\u8bdd\u4e1c\u5e2d\u6b21\u4eb2\u5982\u88ab\u82b1\u53e3\u653e\u513f\u5e38\u6c14\u4e94\u7b2c\u4f7f\u5199\u519b\u5427\u6587\u8fd0\u518d\u679c\u600e\u5b9a\u8bb8\u5feb\u660e\u884c\u56e0\u522b\u98de\u5916\u6811\u7269\u6d3b\u90e8\u95e8\u65e0\u5f80\u8239\u671b\u65b0\u5e26\u961f\u5148\u529b\u5b8c\u5374\u7ad9\u4ee3\u5458\u673a\u66f4\u4e5d\u60a8\u6bcf\u98ce\u7ea7\u8ddf\u7b11\u554a\u5b69\u4e07\u5c11\u76f4\u610f\u591c\u6bd4\u9636\u8fde\u8f66\u91cd\u4fbf\u6597\u9a6c\u54ea\u5316\u592a\u6307\u53d8\u793e\u4f3c\u58eb\u8005\u5e72\u77f3\u6ee1\u65e5\u51b3\u767e\u539f\u62ff\u7fa4\u7a76\u5404\u516d\u672c\u601d\u89e3\u7acb\u6cb3\u6751\u516b\u96be\u65e9\u8bba\u5417\u6839\u5171\u8ba9\u76f8\u7814\u4eca\u5176\u4e66\u5750\u63a5\u5e94\u5173\u4fe1\u89c9\u6b65\u53cd\u5904\u8bb0\u5c06\u5343\u627e\u4e89\u9886\u6216\u5e08\u7ed3\u5757\u8dd1\u8c01\u8349\u8d8a\u5b57\u52a0\u811a\u7d27\u7231\u7b49\u4e60\u9635\u6015\u6708\u9752\u534a\u706b\u6cd5\u9898\u5efa\u8d76\u4f4d\u5531\u6d77\u4e03\u5973\u4efb\u4ef6\u611f\u51c6\u5f20\u56e2\u5c4b\u79bb\u8272\u8138\u7247\u79d1\u5012\u775b\u5229\u4e16\u521a\u4e14\u7531\u9001\u5207\u661f\u5bfc\u665a\u8868\u591f\u6574\u8ba4\u54cd\u96ea\u6d41\u672a\u573a\u8be5\u5e76\u5e95\u6df1\u523b\u5e73\u4f1f\u5fd9\u63d0\u786e\u8fd1\u4eae\u8f7b\u8bb2\u519c\u53e4\u9ed1\u544a\u754c\u62c9\u540d\u5440\u571f\u6e05\u9633\u7167\u529e\u53f2\u6539\u5386\u8f6c\u753b\u9020\u5634\u6b64\u6cbb\u5317\u5fc5\u670d\u96e8\u7a7f\u5185\u8bc6\u9a8c\u4f20\u4e1a\u83dc\u722c\u7761\u5174\u5f62\u91cf\u54b1\u89c2\u82e6\u4f53\u4f17\u901a\u51b2\u5408\u7834\u53cb\u5ea6\u672f\u996d\u516c\u65c1\u623f\u6781\u5357\u67aa\u8bfb\u6c99\u5c81\u7ebf\u91ce\u575a\u7a7a\u6536\u7b97\u81f3\u653f\u57ce\u52b3\u843d\u94b1\u7279\u56f4\u5f1f\u80dc\u6559\u70ed\u5c55\u5305\u6b4c\u7c7b\u6e10\u5f3a\u6570\u4e61\u547c\u6027\u97f3\u7b54\u54e5\u9645\u65e7\u795e\u5ea7\u7ae0\u5e2e\u5566\u53d7\u7cfb\u4ee4\u8df3\u975e\u4f55\u725b\u53d6\u5165\u5cb8\u6562\u6389\u5ffd\u79cd\u88c5\u9876\u6025\u6797\u505c\u606f\u53e5\u533a\u8863\u822c\u62a5\u53f6\u538b\u6162\u53d4\u80cc\u7ec6"; 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>
