(3) 预编译和存储过程
2016-09-25 本文已影响262人
笑笑学生
核心代码：
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>
