Java+servlet+JSP+MySQL实现存储过程

Java+servlet+JSP+MySQL实现存储过程 Java+servlet+JSP+MySQL实现存储过程

笔者只实现了查询的存储过程：

白色部分用的是Navicat for MySQL命令行写的存储过程函数：

1. create procedure 函数名（参数1，参数2……）
2. 这里的简单查询（select * from 表名）没有用到参数，如果实现插入的话，是需要参数的；
3. 最后在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"%>









 
      
       
              学号
              姓名
              性别
              年龄
              专业
              班级
         
       	
          ${num }
          ${stu_name}
          ${stu_sex}
          ${stu_profession}
          ${stu_age}
          ${stu_classis}
      
      	
        

效果图：