首页 > 代码库 > JSP与oracle数据库交互案例
JSP与oracle数据库交互案例
本案例为咖啡销售情况录入查询系统
一、数据输入系统：
1. 设计输入信息页面
<html>
<head>
</head>
<body>
<center><H1>咖啡管理系统——录入系统</H1></center>
欢迎来到录入系统<br />
<form action="inputaction.jsp" method="post" name="form1" id="form1">
<center>
咖啡名称:<input type="text" style="width:150px" name="cof_name" id="cof_name"><br />
供应商ID:<input type="text" style="width:150px" name="sup_id" id="sup_id"><br />
商品价格:<input type="text" style="width:150px" name="price" id="price"><br />
销售量:<input type="text" style="width:150px" name="sales" id="sales"><br />
总量:<input type="text" style="width:150px" name="total" id="total"><br />
<input type="submit" value=http://www.mamicode.com/"提交数据">
</center>
</form>
</body>
</html>
2. 设计处理oracle数据库输入请求网页inputaciton.jsp
（1）页面如下：
（2）数据库设计如下：
SQL> desc coffees;
Name Null? Type
----------------------------------------- -------- ----------------------------
COF_NAME VARCHAR2(32)
SUP_ID NUMBER(38)
PRICE FLOAT(126)
SALES NUMBER(38)
TOTAL NUMBER(38)
（3）处理数据库输入代码如下：
<%@ page language="java" import="java.util.*" pageEncoding="GBK"%>
<%@ page
import="java.sql.Connection"
import="java.sql.ResultSet"
import="java.sql.SQLException"
import="java.sql.Statement"
%>
<%
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> </head>
<body>
<center><H1>咖啡管理系统——录入系统</H1></center>
正在执行录入<br />
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
String url = "jdbc:oracle:thin:@100.100.100.20:1521:dbsid2";
String user="scott";
String password="tiger";
Connection con=java.sql.DriverManager.getConnection(url, user, password);
//取得登录用户名
String cof_name = request.getParameter("cof_name");
String sup_id =request.getParameter("sup_id");
String price = request.getParameter("price");
String sales = request.getParameter("sales");
String total = request.getParameter("total");
String sql="insert into coffees values( ‘"+cof_name+"‘,"+sup_id+","+price+","+sales+","+total+")";
Statement smt=con.createStatement();
int rs=smt.executeUpdate(sql);
%>
录入成功 <br>
<form action="output.jsp" name="form3" id="form3">
<input type="submit" value=http://www.mamicode.com/"查询结果">
</form>
</body>
</html>
二、查询系统
1. 输入请求页面output.jsp
代码如下：
<html>
<head> </head>
<body>
<center><H1>咖啡管理系统—查询系统</H1></center>
<form action="action.jsp" method="post" name="form2" id="form2">
<center>
咖啡名称:<input type="text" style="width:150px" name="cof_name_1" id="cof_name_1"><br />
<input type="submit" value=http://www.mamicode.com/"提交查询">
</center>
</form>
</body>
</html>
2. 查询请求的处理action.jsp
（1）查询结果页面
（2）处理数据库查询代码如下：
<%@ page language="java" import="java.util.*" pageEncoding="GBK"%>
<%@ page
import="java.sql.Connection"
import="java.sql.ResultSet"
import="java.sql.SQLException"
import="java.sql.Statement"
%>
<%
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> </head>
<body>
<center>
<table border="1">
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
java.sql.DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
String url = "jdbc:oracle:thin:@100.100.100.20:1521:dbsid2";
String user="scott";
String password="tiger";
Connection con=java.sql.DriverManager.getConnection(url, user, password);
//取得登录用户名
String cof_name_1 = request.getParameter("cof_name_1");
// String sql="select * from coffees where cof_name="+"‘" +cof_name_1 +"‘";
String sql="select * from coffees where cof_name="+"‘"+cof_name_1+"‘";
Statement smt=con.createStatement();
ResultSet rs=smt.executeQuery(sql);
while(rs.next()){
String s1=rs.getString(1);
int s2=rs.getInt(2);
float s3=rs.getFloat(3);
int s4=rs.getInt(4);
int s5=rs.getInt(5);
%>
<tr>
<td><%=s1 %></td>
<td><%=s2 %></td>
<td><%=s3 %></td>
<td><%=s4 %></td>
<td><%=s5 %></td>
</tr>
<%} %>
</table>
查询成功 <br>
</center>
</body>
</html>