数据库连接操作(一)

来源：互联网发布：cnc铣外圆怎么手工编程编辑：程序博客网时间：2024/05/09 16:09

用纯JSP写的连接数据库操，具体包括数据查询、删除、修改，数据的添加在数据库里操作，数据库为MySQL

文件包括login.jsp,do_login.jsp(处理登录信息),index.jsp(显示登录用户名、密码和编辑删除操作),edit.jsp,do_edit.jsp,delete.jsp

登录页面

<%@ page language="java" pageEncoding="utf-8"%><html>  <head>    <title>登录页面</title>  </head>   <body>    <%     request.setCharacterEncoding("gb2312");     response.setContentType("text/html;charset=gb2312");   %>  <center>  <h3>欢迎注册该网站</h3>  <form action="do_login.jsp" method="post">  <table>  <tr>      <td>用户名</td>      <td><input type="text" name="username"></td>  </tr>  <tr>      <td>密码</td>      <td><input type="password" name="password"></td>  </tr>  <tr>      <td></td>      <td>          <input type="submit" value="登录">          <input type="reset" value="取消">      </td>     </tr>  </table>  </form>  </center>  </body></html>

do_login.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%><html>  <head>      <title>登录处理页面</title>  </head>   <body>    <%     request.setCharacterEncoding("gb2312");     response.setContentType("text/html;charset=gb2312");     String user=request.getParameter("username");     String pwd=request.getParameter("password");          final String DBDriver="com.mysql.jdbc.Driver";     final String DBUrl="jdbc:mysql://localhost:3306/mqsqltest";     final String DBUser="root";     final String DBPassword="686175";     Connection conn=null;     Statement stmt=null;     ResultSet rs=null;     Class.forName(DBDriver);     conn=DriverManager.getConnection(DBUrl,DBUser,DBPassword);     String sql="select * from user where username='"+user+"'&&password='"+pwd+"'";     stmt=conn.createStatement();     rs=stmt.executeQuery(sql);     if(rs.next()){        out.print("登录成功!");        response.setHeader("refresh","2;url=index.jsp");     }     else{        out.println("您登录的账号或密码错误!");        response.setHeader("refresh","2;url=login.jsp");     }     stmt.close();     conn.close();  %>   </body></html>

index.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%><html>  <head>    <title>数据列表</title>  </head>  <body>  <%     request.setCharacterEncoding("gb2312");     response.setContentType("text/html;charset=gb2312");          final String DBDriver="com.mysql.jdbc.Driver";     final String DBUrl="jdbc:mysql://localhost:3306/mqsqltest";     final String DBUser="root";     final String DBPassword="686175";     Connection conn=null;     Statement stmt=null;     ResultSet rs=null;     Class.forName(DBDriver);     conn=DriverManager.getConnection(DBUrl,DBUser,DBPassword);     String sql="select * from user";     stmt=conn.createStatement();     rs=stmt.executeQuery(sql);  %>      <table border="1">     <tr>       <td>编号</td>       <td>帐号</td>       <td>密码</td>       <td>编辑</td>       <td>删除</td>     </tr>     <%      while(rs.next()){     %>     <tr>     <td><%=rs.getInt(1) %></td>     <td><%=rs.getString(2) %></td>     <td><%=rs.getString(3) %></td>     <td><a href="edit.jsp?id=<%=rs.getInt(1)%>">更新</a></td>     <td><a href="delete.jsp?id=<%=rs.getInt(1)%>">删除</a></td>     </tr>     <%      }      stmt.close();     conn.close();      %>       </table>  </body></html>

编辑页面

编辑后

edit.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%><html>  <head>    <title>编辑页面</title>  </head>    <body>   <%     request.setCharacterEncoding("gb2312");     response.setContentType("text/html;charset=gb2312");     int id=Integer.parseInt(request.getParameter("id"));     session.setAttribute("id", id);          final String DBDriver="com.mysql.jdbc.Driver";     final String DBUrl="jdbc:mysql://localhost:3306/mqsqltest";     final String DBUser="root";     final String DBPassword="686175";     Connection conn=null;     Statement stmt=null;     ResultSet rs=null;     Class.forName(DBDriver);     conn=DriverManager.getConnection(DBUrl,DBUser,DBPassword);     String sql="select * from user where id='"+id+"'";     stmt=conn.createStatement();     rs=stmt.executeQuery(sql);     rs.next();    %>   <center>  <h3>编辑页面</h3>  <form action="do_edit.jsp" method="post">  <table>  <tr>      <td>帐号</td>      <td><input type="text" name="username" value=<%=rs.getString(2)%>></td>  </tr>  <tr>      <td>密码</td>      <td><input type="password" name="password" value=<%=rs.getString(3)%>></td>  </tr>  <tr>      <td></td>      <td>          <input type="submit" value="保存">          <input type="reset" value="取消">      </td>     </tr>  </table>  </form>  </center>  </body></html>

处理编辑页面

do_edit.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%><%@ page import="java.util.*" %><html>  <head>      <title>编辑保存页面</title>  </head>   <body>   <%     request.setCharacterEncoding("gb2312");     response.setContentType("text/html;charset=gb2312");     int id=Integer.parseInt(session.getAttribute("id").toString());     String user=request.getParameter("username");     String pwd=request.getParameter("password");          final String DBDriver="com.mysql.jdbc.Driver";     final String DBUrl="jdbc:mysql://localhost:3306/mqsqltest";     final String DBUser="root";     final String DBPassword="686175";     Connection conn=null;     Statement stmt=null;     ResultSet rs=null;     Class.forName(DBDriver);     conn=DriverManager.getConnection(DBUrl,DBUser,DBPassword);     stmt=conn.createStatement();     if(user!=null&&pwd!=null){      String sql="update user set username='"+user+"',password='"+pwd+"' where id='"+id+"'";      stmt.executeUpdate(sql);      out.print("修改成功!");      response.setHeader("refresh","2;url=index.jsp");     }else{      out.print("帐号或密码不能为空!");      response.setHeader("refresh","2;url=edit.jsp");     }   %>  </body></html>

删除页面

delete.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%><html>  <head>     <title>删除页面</title>  </head>   <body>   <%     request.setCharacterEncoding("gb2312");     response.setContentType("text/html;charset=gb2312");     int id=Integer.parseInt(request.getParameter("id"));          final String DBDriver="com.mysql.jdbc.Driver";     final String DBUrl="jdbc:mysql://localhost:3306/mqsqltest";     final String DBUser="root";     final String DBPassword="686175";     Connection conn=null;     Statement stmt=null;     Class.forName(DBDriver);     conn=DriverManager.getConnection(DBUrl,DBUser,DBPassword);     String sql="delete from user where id='"+id+"'";     stmt=conn.createStatement();     stmt.executeUpdate(sql);     stmt.close();     conn.close();     out.print("删除成功!");     response.setHeader("refresh","2;url=index.jsp");   %>   </body></html>

在编译运行前还的将MySQL的驱动包导入WebRoot/WEB-INF/lib下

0 0