Java+servlet+JSP+MySQL实现存储过程
笔者只实现了查询的存储过程:
白色部分用的是Navicat for MySQL命令行写的存储过程函数:
- create procedure 函数名(参数1,参数2……)
- 这里的简单查询(select * from 表名)没有用到参数,如果实现插入的话,是需要参数的;
- 最后在Java中用下面的代码调用存储过程;用{call 存储过程函数名()}的形式;
CallableStatement cs = conn.prepareCall("{call find_stu()}");
ResultSet rs = cs.executeQuery();
完整代码:servlet(.java)
package mysql;
import java.sql.*;
import java.io.IOException;
import javax.servlet.RequestDispatcher;
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("/Link_mysql")
public class Link_mysql extends HttpServlet {
private static final long serialVersionUID = -1450812728074523352L;
/**
*
*/
Connection conn=null;//连接
Statement stat=null;//建立状态
PreparedStatement ps=null;//准备状态
ResultSet rs=null;//返回结果
public Link_mysql() {
super();
try{ //加载jdbc驱动程序
Class.forName("com.mysql.jdbc.Driver").newInstance();
System.out.println("驱动程序加载成功!");
}catch(Exception e){
System.out.println("找不到驱动程序!");
}
try {
//建立连接
conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","123456");//book指连接的数据库名称,
System.out.println("连接成功!");
}catch(Exception e){
System.out.println("连接失败!");
}
}
public void release() {
try {
if(rs!=null) {
rs.close();
}
if(stat!=null) {
stat.close();
}
if(conn!=null) {
conn.close();
}
}catch(SQLException e) {
e.printStackTrace();
}
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=utf-8");
int id=Integer.parseInt(request.getParameter("id"));
try {
CallableStatement cs = conn.prepareCall("{call find_stu()}");
ResultSet rs = cs.executeQuery();
while(rs.next()) {//集合不为空时,实例化
int num=rs.getInt("id");
String stu_name=rs.getString("name");
String stu_sex=rs.getString("sex");
String stu_profession=rs.getString("profession");
String stu_classis=rs.getString("classis");
int stu_age=rs.getInt("age");
request.setAttribute("num", num);
request.setAttribute("stu_name", stu_name);
request.setAttribute("stu_sex",stu_sex);
request.setAttribute("stu_profession", stu_profession);
request.setAttribute("stu_age", stu_age);
request.setAttribute("stu_classis", stu_classis);
while(num==id) {//条件查询
RequestDispatcher requestDispatcher =request.getRequestDispatcher("userview.jsp");
requestDispatcher.forward(request,response);
release();
}
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
JSP文件:
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="Link_mysql" method="get">
<h2 align="center">按学号查询学生信息</h2>
<table border="1" width="600px" height="70px" cellspacing="0" cellpadding="10px" bgcolor="skyblue" bordercolor="#FFFFFF" align="center">
<tr align="center">
<td>学号:</td>
<td align="center"><input type="text" name="id" /><br><br></td>
<td><input type="submit" value="查询"/></td>
</tr>
</table>
</form>
<table border="1" width="600px" height="12s0px" cellspacing="0" cellpadding="10px" bgcolor="skyblue" bordercolor="#FFFFFF" align="center">
<tr align="center">
<td>学号</td>
<td>姓名</td>
<td>性别</td>
<td>年龄</td>
<td>专业</td>
<td>班级</td>
</tr>
<tr align="center">
<td>${num }</td>
<td>${stu_name}</td>
<td>${stu_sex}</td>
<td>${stu_profession}</td>
<td>${stu_age}</td>
<td>${stu_classis}</td>
</tr>
</table>
</body>
</html>
效果图: