(3) 预编译和存储过程

核心代码：
pre_insert.jsp
<%@ page import="java.sql.*"%>
<%@ page import="java.util.Date,java.text.SimpleDateFormat"%>

  <body>
    <%
        String JDriver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String connectDB="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=shopSystem";
        try{
            Class.forName(JDriver);
        }catch(ClassNotFoundException e){
            System.out.println("加载数据库失败");
            System.exit(0);
        }
        
        try{
            String user="sa";
            String password="xuelong";
            Connection con=DriverManager.getConnection(connectDB, user, password);
            System.out.println("数据库连接成功");
            
            //设置编码格式，处理中文乱码
            request.setCharacterEncoding("UTF-8");
            response.setContentType("text/html;charset=UTF-8");
  
            String p_id=request.getParameter("p_id");
            String p_type=request.getParameter("p_type");
            String p_name=request.getParameter("p_name");
            float p_price=Float.parseFloat(request.getParameter("p_price"));
            int p_quantity=Integer.parseInt(request.getParameter("p_quantity"));
            String p_image=request.getParameter("p_image");
            String p_description=request.getParameter("p_description");
            //获取时间
            Date date = new Date();
            SimpleDateFormat time = new SimpleDateFormat("yyyy-MM-dd");
            String p_time=time.format(date);
            
            //预编译插入数据
            String strSql="insert into product values(?,?,?,?,?,?,?,?)";
            PreparedStatement pstmt=con.prepareStatement(strSql);
            pstmt.setString(1,p_id);
            pstmt.setString(2,p_type);
            pstmt.setString(3,p_name);
            pstmt.setFloat(4, p_price);
            pstmt.setInt(5, p_quantity);
            pstmt.setString(6, p_image);
            pstmt.setString(7, p_description);
            pstmt.setString(8, p_time);
            
            int intTemp=pstmt.executeUpdate();
            if(intTemp!=0){
                out.println("商品添加成功");
            }else{
                out.println("商品添加失败");
            }
            
            //预编译查询
            String strSql2="SELECT TOP 5 p_id,p_type,p_name,p_price,p_quantity FROM product WHERE p_type=? order by p_time asc";
            PreparedStatement pstmt2 = con.prepareStatement(strSql2);
            pstmt2.setString(1, "电视机系列");
            ResultSet rs=pstmt2.executeQuery();
    %>
    <table border="1" align="center">
        <tr>
            <th>商品编号</th>
            <th>商品类别</th>
            <th>商品名称</th>
            <th>商品单价</th>
            <th>商品数量</th>
        </tr>
        <%while(rs.next()){ %>
        <tr bgcolor="blue">
            <td><%=rs.getString("p_id") %></td>
            <td><%=rs.getString("p_type") %></td>
            <td><%=rs.getString("p_name") %></td>
            <td><%=rs.getFloat("p_price") %></td>
            <td><%=rs.getInt("p_quantity") %></td>
        </tr>
        <%}%>
    </table>
    
    <%
/*      创建存储过程
        CREATE PROCEDURE sp_count
            @iSum bigint output
        AS
        SELECT @iSum=sum(p_price*p_quantity) FROM product
        GO 
*/
        //调用存储过程计算总价
        CallableStatement cstmt=con.prepareCall("{call sp_count(?)}");
        cstmt.registerOutParameter(1, Types.INTEGER);
        cstmt.executeUpdate();
        int iCount=cstmt.getInt(1);
        out.println("商品总价为:"+iCount);
    %>
    <%
        cstmt.close();
        pstmt.close();
        pstmt2.close();
        con.close();
        }catch(Exception e){
            out.println(e.toString());
        } 
    %>
  </body>
(3) 预编译和存储过程

推荐阅读更多精彩内容
