利用jsp连接mysql进行基本数据库操作

于 2018-04-26 13:46:45 发布
阅读量573 收藏 3
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/Solitude_/article/details/80086127
版权
一：先进行基本的数据库建表，tomcat的环境配置，还有jdbc（mysql.connect.jar）的拷贝在lib文件夹下。
二：理清思路，使用顺序。
三：详细代码


text.jsp

<%@ page contentType="text/html; charset=gb2312" %>   
<%@ page language="java" %>   
<%@ page import="com.mysql.jdbc.Driver" %>   
<%@ page import="java.sql.*" %>   
<%   
//加载驱动程序   
String driverName="com.mysql.jdbc.Driver";   
//数据库信息  
String userName="root";   
//密码   
String userPasswd="123456";   
//数据库名   
String dbName="one";   
//表名   
String tableName="two";   
//将数据库信息字符串连接成为一个完整的url（也可以直接写成url，分开写是明了可维护性强）   
  
String url="jdbc:mysql://localhost/"+dbName+"?user="+userName+"&password="+userPasswd;   
Class.forName("com.mysql.jdbc.Driver").newInstance();   
Connection conn=DriverManager.getConnection(url);   
Statement stmt = conn.createStatement();   
String sql="SELECT * FROM "+tableName;   
ResultSet rs = stmt.executeQuery(sql);   
out.print("age");   
out.print("|");   
out.print("name");   
out.print("|");   
out.print("number");   
out.print("<br>");   
while(rs.next()) {   
out.print(rs.getString(1)+" ");   
out.print("|");   
out.print(rs.getString(2)+" ");   
out.print("|");   
out.print(rs.getString(3));   
out.print("<br>");   
}   
out.print("<br>");   
out.print("ok， Database Query Successd！");   
rs.close();   
stmt.close();   
conn.close();   
%>
//该代码主要进行测试，看看是否能够成功连接数据库，输出数据表的数据





login.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>  
<%  
String path = request.getContextPath();  
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";  
%>  
  
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">  
<html>  
  <head>  
    <base href="<%=basePath%>">  
      
    <title>My JSP 'Feilong_login.jsp' starting page</title>  
      
    <meta http-equiv="pragma" content="no-cache">  
    <meta http-equiv="cache-control" content="no-cache">  
    <meta http-equiv="expires" content="0">      
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">  
    <meta http-equiv="description" content="This is my page">  
    <!-- 
    <link rel="stylesheet" type="text/css" href="styles.css"> 
    -->  
	
  <body>  
    <center>  
    <font face="楷体" size="6" color="#000" >登录界面</font>  
    <%    
    String flag = request.getParameter("errNo");    
    try{  
         if(flag!=null)  
            out.println("用户名不存在或密码错误");  
    }catch(Exception e){  
        e.printStackTrace();  
    }  
   %>  
    <form action = "check.jsp" method="post">  
      <table width="300" height = "180" border="5" bordercolor="#A0A0A0">   
        <tr>  
          <th>账  户：</th>  
          <td><input type="text" name="name"  value = "请输入用户名" maxlength = "16" onfocus = "if(this.value == '请输入用户名') this.value =''"></td>  
        </tr>  
        <tr>  
          <th>密  码：</th>  
          <td><input type="password" name="pwd" maxlength = "20"></td>  
        </tr>  
        <tr>  
          <td colspan = "2" align = "center">  
            <input type="submit" name="submit" value="登       录">  
            <input type="button" value="返       回"  
              οnclick="window.location.href('/webText')">  
          </td>  
        </tr>  
      </table>  
    </form>  
  </center>  
  </body>  
</html>

//该代码为一个登录界面，可以进行登录连接数据库，如下为网页输出界面截图：





check.jsp

<%@ page language="java" import="java.util.*,java.sql.*,java.net.*" pageEncoding="utf-8"%>  
<%  
String path = request.getContextPath();  
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";  
%>  
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">  
<html>  
  <head>  
    <base href="<%=basePath%>">  
      
    <title>My JSP 'Feilong_loginCh.jsp' starting page</title>  
      
    <meta http-equiv="pragma" content="no-cache">  
    <meta http-equiv="cache-control" content="no-cache">  
    <meta http-equiv="expires" content="0">      
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">  
    <meta http-equiv="description" content="This is my page">  
    <!-- 
    <link rel="stylesheet" type="text/css" href="styles.css"> 
    -->  
  
  </head>  
  <body>  
    <%     //加载驱动程序 
	 try{
 String user = new String(request.getParameter("name").getBytes("ISO-8859-1"),"UTF-8");    
            String pwd = request.getParameter("pwd");			
 String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";  			
String driverName="com.mysql.jdbc.Driver";   
//数据库信息  
String userame="root";   
//密码   
String userasswd="123456";   
//数据库名   
String dbName="one";   
//表名   
String tableName="there";   
//将数据库信息字符串连接成为一个完整的url（也可以直接写成url，分开写是明了可维护性强）   
  
String url="jdbc:mysql://localhost:3306/"+dbName+"?user="+userame+"&password="+userasswd;   
Class.forName("com.mysql.jdbc.Driver").newInstance();   
Connection conn=DriverManager.getConnection(url);   
        String sql="select * from there where userName='"+user+"' and userPasswd='"+ pwd+ "'";  
	   Statement stmt = conn.createStatement();  
            ResultSet rs=stmt.executeQuery(sql);  
if(conn != null){
	  if(rs.next()){  
                  response.sendRedirect("check2.jsp?userName="+URLEncoder.encode(user)); //解决乱码   
                }else{  
                    response.sendRedirect("login.jsp?errNo");//密码不对返回到登陆    
                }          
     rs.close();  
     stmt.close();          
     conn.close();
}	
else{  
            // 输出连接信息  
            out.println("数据库连接失败！");                          
        } 	
	 }
	catch (ClassNotFoundException e) {  
        e.printStackTrace();  
    } catch (SQLException e) {  
        e.printStackTrace();  
    }  
     %>  
  </body>  
</html>

//该代码用来检查登录时的密码与用户名是否正确，注意在检查时它是进行抽取你的数据表中的数据进行检查。而不是你的mysql的登录名于密码。所以建议建立两个数据表，一个用来后面的数据操作，一个充当花名册的作用一样，赋予登录权限。该jsp文件无显示页面，只是用来判断是否有该用户。之后跳转到check2.jsp进行输出检查结果。




check2.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>  
<%  
String path = request.getContextPath();  
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";  
%>  
  
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">  
<html>  
  <head>  
    <base href="<%=basePath%>">  
    <title>Feilong_登录成功</title>  
    <meta http-equiv="pragma" content="no-cache">  
    <meta http-equiv="cache-control" content="no-cache">  
    <meta http-equiv="expires" content="0">      
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">  
    <meta http-equiv="description" content="This is my page">  
    <!-- 
    <link rel="stylesheet" type="text/css" href="styles.css"> 
    -->  
  </head>  
  <body>  
    <center>  
    <%  
     String name = new String(request.getParameter("userName").getBytes("8859_1"));  
     out.println("欢迎你:" + name); 	 
    %><br> 
<form action = "HHH.jsp" method="post">  
      <table width="300" height = "180" border="5" bordercolor="#A0A0A0">    
        <tr>  
          <td colspan = "2" align = "center">  
            <input type="submit" name="submit" value="查看表格数据">    
          </td>  
        </tr>  
      </table>  
    </form>  	
    <a href="login.jsp">重新登陆</a>  
    </center>  
  </body>  
</html>

//当用户名与密码正确时：


之后点击查看便能通过HHH.jsp查看数据表内容：




HHH.jsp

<%@ page language="java"  import="java.sql.*,java.io.*,java.util.*,java.sql.SQLException" %>
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>  
<html>
<style>
body{
background-image:url('2.jpg');
    background-attachment:fixed;
	background-repeat:no-repeat;
	background-color:#000000;

}
</style>
<body style="background-position:center;">
<h1 align="center"><small>玩家基础信息</small></h1>

<%   
//加载驱动程序   
String driverName="com.mysql.jdbc.Driver";   
//数据库信息  
String userName="root";   
//密码   
String userPasswd="123456";   
//数据库名   
String dbName="one";   
//表名   
String tableName="four";   
//将数据库信息字符串连接成为一个完整的url（也可以直接写成url，分开写是明了可维护性强）   
  
String url="jdbc:mysql://localhost/"+dbName+"?user="+userName+"&password="+userPasswd;   
Class.forName("com.mysql.jdbc.Driver").newInstance();   
Connection conn=DriverManager.getConnection(url);   
Statement stmt = conn.createStatement();   
String sql="SELECT * FROM "+tableName;   
ResultSet rs = stmt.executeQuery(sql);   
%>

<table width=500px border=1 align="center">
<form action="delter.jsp" method="POST" target="_blank">
  <tr>
    <td>玩家编号</td>
     <td>玩家名</td>
     <td>玩家年龄</td>
	 <td>玩家描述</td>
	 <td>玩家等级</td>
	 <td colspan="5" style="text-align: center;">
信息操作
</td>

	
  </tr>
 
<%
  //利用while循环输出各条记录
  while(rs.next())
  {
%>
<tr>

     <%String v = rs.getString(1);%>
     <td><input type="checkbox" name="id" value="<%=v%>"/> <%=rs.getString("id") %></td>
   <td><%=rs.getString("userName") %></td>
   <td><%=rs.getString("age") %></td>
   <td><%=rs.getString("userPasswd")%></td>
    <td><%=rs.getString("userIdentity")%></td>
	<%String A = rs.getString(1);%>
	<%String B = rs.getString(2);%>
    <%String C = rs.getString(3);%>
	<%String D = rs.getString(4);%>
	<%String E = rs.getString(5);%>
	<td style="text-align:center;"><a href=transfer_revise.jsp?id=<%=A%>&userName=<%=B%>&age=<%=C%>&userPasswd=<%=D%>&userIdentity=<%=E%>>修改</a></td>
</tr>
<%
}
%>
</table>
<table width=200px border=1 align="center">
<tr>
<td  style="text-align:center;">
<a href="transfer_add.jsp">添加新玩家</a> </td>
</tr>
<tr>
<td  style="text-align:center;"><a><input onClick="return confirm('确认删除?')"  type="submit" value="删除" /></a></td>
</tr>
</form>
</table>
<%
  rs.close();
  stmt.close();
  conn.close();
  %>
</body>
</html>


在这个页面可以进行三种操作（复选框批量删除（delter.jsp)，添加新用户(Add.jsp,transfer_add.jsp)，修改用户信息(revise.jsp,transfer_revise.jsp)）




delter.jsp

<%@ page import="java.sql.DriverManager" %>
<%@ page import="com.mysql.jdbc.Connection" %>
<%@ page import="com.mysql.jdbc.Statement" %>
<%@ page import="com.mysql.jdbc.ResultSetImpl" %>
<%@ page import="com.mysql.jdbc.Driver" %>
<%@ page contentType="text/html" language="java" import="java.sql.*"%>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<html>
<body align="center">
<h1>成功匹配数据</h1>
<%
String [] dat = request.getParameterValues("id");
String lan = request.getParameter("userName");

//加载驱动程序   
String driverName="com.mysql.jdbc.Driver";   
//数据库信息  
String userName="root";   
//密码   
String userPasswd="123456";   
//数据库名   
String dbName="one";   
//表名   
String tableName="four";   
//将数据库信息字符串连接成为一个完整的url（也可以直接写成url，分开写是明了可维护性强）   
  
String url="jdbc:mysql://localhost/"+dbName+"?user="+userName+"&password="+userPasswd;   
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn= (Connection)DriverManager.getConnection(url);   
Statement stmt = (Statement)conn.createStatement();
for(int i=0;i<dat.length;i++)
{ 
stmt.executeUpdate("delete from four where id='"+ dat[i] +"'");
}
stmt.close();
conn.close();
%>
<br/>
<p>The record has been deleted!</p>
<a>已成功删除</a>
<a href="HHH.jsp">返回查询界面</a> 
</body>
</html>





transfer_add.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>  
<%@ page import="java.sql.DriverManager" %>
<%@ page import="com.mysql.jdbc.Connection" %>
<%@ page import="com.mysql.jdbc.Statement" %>
<%@ page import="com.mysql.jdbc.ResultSetImpl" %>
<%@ page import="com.mysql.jdbc.Driver" %>
<%@ page contentType="text/html" language="java" import="java.sql.*"%>
<%  
String path = request.getContextPath();  
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";  
%>  
  
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">  
<html>  
  <head>  
    <base href="<%=basePath%>">  
      
    <title>My JSP 'Feilong_login.jsp' starting page</title>  
      
    <meta http-equiv="pragma" content="no-cache">  
    <meta http-equiv="cache-control" content="no-cache">  
    <meta http-equiv="expires" content="0">      
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">  
    <meta http-equiv="description" content="This is my page">  
    <!-- 
    <link rel="stylesheet" type="text/css" href="styles.css"> 
    -->  
	
  <body>  
    <center>  
    <font face="楷体" size="6" color="#000" >添加新玩家</font>  
    <%    
    String flag = request.getParameter("errNo");    
    try{  
         if(flag!=null)  
            out.println("格式错误");  
    }catch(Exception e){  
        e.printStackTrace();  
    }  
   %>
<br/>
    <form action = "Add.jsp" method="post">  
      <table width="300" height = "180" border="5" bordercolor="#A0A0A0">   
        <tr>  
          <th>新玩家编号：</th>  
          <td><input type="text" name="a"  maxlength = "20"></td>  
        </tr>  
        <tr>  
          <th>新玩家名：</th>  
           <td><input type="text" name="b"  maxlength = "20"></td> 
        </tr>
 <tr>  
          <th>新玩家年龄：</th>  
          <td><input type="text" name="c" maxlength = "20"></td>  
        </tr>  
 <tr>  
          <th>新玩家描述：</th>  
          <td><input type="text" name="d" maxlength = "20"></td>  
        </tr>  
 <tr>  
          <th>新玩家等级：</th>  
          <td><input type="text" name="e" maxlength = "20"></td>  
        </tr>  		
        <tr>  
          <td colspan = "2" align = "center">  
            <input type="submit" name="submit" value="添       加">
            <a href="HHH.jsp">返回查询界面</a>   			
            <input type="button" value="凑       数"  
              οnclick="window.location.href('/webText')">  
          </td>  
        </tr>  
      </table>  
    </form>  
  </center>  
  </body>  
</html>

//这是一个添加操作的中转代码，用于输入添加玩家的信息，然后把参数传到Add.jsp进行添加。




Add.jsp

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

<html>
<head>

<title>更新用户信息</title>

</head>

<body align="center">
    <%
        String url="jdbc:mysql://localhost:3306/one";
        String user="root";
        String password="123456";
        Connection conn=null;
        try {		
			String a = request.getParameter("a");    
            String b = request.getParameter("b");
            String c = request.getParameter("c");
            String d = request.getParameter("d");
			String e = request.getParameter("e");
            Class.forName("com.mysql.jdbc.Driver");     //加载JDBC驱动
            //out.println("加载驱动类成功");
            conn=DriverManager.getConnection(url,user,password);        //连接MySQL数据库
            //out.println("连接MySQL成功");
            Statement stmt=conn.createStatement();      //创建语句对象Statement
            
			 stmt.executeUpdate("insert into four(id,age,userName,userPasswd,userIdentity)values('"+a+"','"+c+"','"+b+"','"+d+"','"+e+"')");
                stmt.close();
                conn.close();
				out.println("添加玩家信息成功");
        } catch (ClassNotFoundException e) {
            out.println("找不到驱动类");      //处理ClassNotFoundException异常
        }catch(SQLException e){
            out.println("连接MySQL失败");       //处理SQLException异常
        }
    %>
	</br>
	<a href="HHH.jsp">返回查询界面</a> 
</body>
</html>



transfer_revise.jsp

<%@ page import="com.mysql.jdbc.ResultSetImpl" %>
<%@ page import="com.mysql.jdbc.Driver" %>
<%@ page contentType="text/html" language="java" import="java.sql.*"%>
<%  
String path = request.getContextPath();  
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";  
%>  
  
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">  
<html>  
  <head>  
    <base href="<%=basePath%>">  
      
    <title>My JSP 'Feilong_login.jsp' starting page</title>  
      
    <meta http-equiv="pragma" content="no-cache">  
    <meta http-equiv="cache-control" content="no-cache">  
    <meta http-equiv="expires" content="0">      
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">  
    <meta http-equiv="description" content="This is my page">  
    <!-- 
    <link rel="stylesheet" type="text/css" href="styles.css"> 
    -->  
	
  <body>  
    <center>  
    <font face="楷体" size="6" color="#000" >修改界面</font>  
    <%    
    String flag = request.getParameter("errNo");    
    try{  
         if(flag!=null)  
            out.println("格式错误");  
    }catch(Exception e){  
        e.printStackTrace();  
    }  
   %>
   <h1>原始数据</h1>
<%
String A = request.getParameter("id");
String B = request.getParameter("userName");
String C = request.getParameter("age");
String D = request.getParameter("userPasswd");
String E = request.getParameter("userIdentity");
out.println("id:" + A);
out.println("</br>");
out.println("userName:" +B);
out.println("</br>");
out.println("age:" + C);
out.println("</br>");
out.println("userPasswd:" + D);
out.println("</br>");
out.println("userIdentity：" + E); 
%>
<br/>
    <form action = "revise.jsp" method="post">  
      <table width="300" height = "180" border="5" bordercolor="#A0A0A0">   
        <tr>  
          <th>更改玩家编号：</th>  
          <td><input type="text" name="a"  maxlength = "20"></td>  
        </tr>  
        <tr>  
          <th>更改玩家名：</th>  
           <td><input type="text" name="b"  maxlength = "20"></td> 
        </tr>
 <tr>  
          <th>更改玩家年龄：</th>  
          <td><input type="text" name="c" maxlength = "20"></td>  
        </tr>  
 <tr>  
          <th>更改玩家描述：</th>  
          <td><input type="text" name="d" maxlength = "20"></td>  
        </tr>  
 <tr>  
          <th>更改玩家等级：</th>  
          <td><input type="text" name="e" maxlength = "20"></td>  
        </tr>  		
        <tr>  
          <td colspan = "2" align = "center">  
            <input type="submit" name="submit" value="修       改">
            <a href="HHH.jsp">返回查询界面</a>   			
            <input type="button" value="凑       数"  
              οnclick="window.location.href('/webText')">  
          </td>  
        </tr>  
      </table>  
    </form>  
  </center>  
  </body>  
</html>

//这是一个添加操作的中转代码，用于输入添加玩家的信息，然后把参数传到Add.jsp进行添加。





revise.jsp

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

<html>
<head>

<title>更新用户信息</title>

</head>

<body align="center">
    <%
        String url="jdbc:mysql://localhost:3306/one";
        String user="root";
        String password="123456";
        Connection conn=null;
        try {		
			String a = request.getParameter("a");    
            String b = request.getParameter("b");
            String c = request.getParameter("c");
            String d = request.getParameter("d");
			String e = request.getParameter("e");
            Class.forName("com.mysql.jdbc.Driver");     //加载JDBC驱动
            //out.println("加载驱动类成功");
            conn=DriverManager.getConnection(url,user,password);        //连接MySQL数据库
            //out.println("连接MySQL成功");
            Statement stmt=conn.createStatement();      //创建语句对象Statement
            
			String sql="update four set userName='"+b+"',id='"+a+"',userPasswd='"+d+"',age='"+c+"',userIdentity='"+e+"' where id='"+a+"'"; 
            stmt.executeUpdate(sql);     //执行更新  
                stmt.close();
                conn.close();
				out.println("修改用户信息成功");
        } catch (ClassNotFoundException e) {
            out.println("找不到驱动类");      //处理ClassNotFoundException异常
        }catch(SQLException e){
            out.println("连接MySQL失败");       //处理SQLException异常
        }
    %>
	</br>
	<a href="HHH.jsp">返回查询界面</a> 
</body>
</html>

//所有操作在完成之后均会返回HHH.jsp界面，进行数据刷新。

注意：

1：注意将数据表与数据库的名称改正确

2：在表中添加参数时，一定要注意参数的类型，这里我在建立表的时候全部设成了字符串的格式，所以在之后传递参数时，我就没有进行参数格式的转换。

3：并且文中代码暂时只是支持英文的参数传递，使用中文会导致乱码。


nzstfyzjhm
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值