数据库连接操作(一) - 数据库编程

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

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

登录页面

login.jsp< http://www.2cto.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+PHByZSBjbGFzcz0="brush:sql;"><%@ page language="java" pageEncoding="utf-8"%> <html> 登录页面 <% request.setCharacterEncoding("gb2312"); response.setContentType("text/html;charset=gb2312"); %>

欢迎注册该网站

do_login.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>

    
    登录处理页面
   
  
    <%
     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();
  %>

index.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>

  
    数据列表
  
  
  <%
     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);
  %> 
     
     <% 
     while(rs.next()){
     %>
     
     <% 
     } 
     stmt.close();
     conn.close(); 
     %>  
     
     
       编号
       帐号
       密码
       编辑
       删除
     

     <%=rs.getInt(1) %>
     <%=rs.getString(2) %>
     <%=rs.getString(3) %>
     更新
     删除

编辑页面

编辑后

edit.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>

  
    编辑页面
    
  
   <%
     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();
    %>
   
  编辑页面
  
  
  
      帐号
      >
  
  
      密码
      >

处理编辑页面

do_edit.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<%@ page import="java.util.*" %>

    
    编辑保存页面
   
  
   <%
     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");
     }
   %>

删除页面

delete.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>

   
    删除页面
   
  
   <%
     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");
   %>

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

编号	帐号	密码	编辑	删除
<%=rs.getInt(1) %>	<%=rs.getString(2) %>	<%=rs.getString(3) %>	更新	删除

用户名
密码