使用带有 Netbeans 的 java servlet 从 Oracle 数据库查询数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/323567/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Querying data from Oracle database using java servlet with Netbeans
提问by Shiel
From index.jsp code,
从 index.jsp 代码，
statement.executeQuery("select * from fus where tester_num like 'hf60' ") ;
Example I want "hf60" to be a variable(userinput), wherein USER must input/write data from input text then submit and get the data so that the result will be
示例我希望“hf60”是一个变量（用户输入），其中用户必须从输入文本输入/写入数据，然后提交并获取数据，以便结果为
("select * from fus where tester_num like 'userinput' ")
Where should I insert that code, Is it in InsertServlet .java or in Index.jsp.? or make another filename.java code? Please help. Thanks;)
我应该在哪里插入该代码，是在 InsertServlet .java 中还是在 Index.jsp 中？或制作另一个 filename.java 代码？请帮忙。谢谢;）
Index.jsp
索引.jsp
<%@ page import="java.sql.*" %>
<% Class.forName("oracle.jdbc.driver.OracleDriver"); %>
<HTML>
<HEAD>
<TITLE>SHIFT REPORT </TITLE>
</HEAD>
<BODY BGCOLOR=##342D7E>
<CENTER>
<H2><FONT COLOR="#ECD672" FACE="Verdana" >SHIFT REPORT</FONT></H2></CENTER>
<hr>
<%
Connection connection=DriverManager.getConnection ("jdbc:oracle:thin:@oradev2.*****.com:1521:RPADB","shift_admin", //
"shift_admin"
);
Statement statement = connection.createStatement() ;
//**Should I input the codes here?**
ResultSet resultset =
statement.executeQuery("select * from fus where tester_num like 'hf60") ;
%>
<TABLE BORDER="1" BGCOLOR="CCFFFF" width='200%' cellspacing='1' cellpadding='0' bordercolor="black" border='1'>
<TR>
<TH bgcolor='#DAA520'> <font size='2'>RECORD NUMBER</TH>
<TH bgcolor='#DAA520'><font size='2'>TESTER NUMBER</TH>
<TH bgcolor='#DAA520'><font size='2'>DATE</TH>
<TH bgcolor='#DAA520'><font size='2'>TIME</TH>
<TH bgcolor='#DAA520'><font size='2'>SYSTEM TYPE</TH>
<TH bgcolor='#DAA520'><font size='2'>PACKAGE</TH>
<TH bgcolor='#DAA520'><font size='2'>SOCKETS</TH>
<TH bgcolor='#DAA520'><font size='2'>VALIDATED BY</TH>
</TR>
<% while(resultset.next()){ %>
<TR>
<TD> <font size='2'><center><%= resultset.getLong(1) %></center></TD>
<TD> <font size='2'><center><%= resultset.getString(2) %></center></TD>
<TD> <font size='2'><center><%= resultset.getDate(3) %></center></TD>
<TD> <font size='2'><center><%= resultset.getString(4) %></center></TD>
<TD> <font size='2'><center><%= resultset.getString(5) %></center></TD>
<TD> <font size='2'><center><%= resultset.getString(6) %></center></TD>
<TD> <font size='2'><center><%= resultset.getString(7) %></center></TD>
<TD> <font size='2'><center><%= resultset.getString(8) %></center></TD>
</TR>
<% } %>
</TABLE>
</BODY>
</HTML>
InsertServlet.java
插入Servlet.java
package fusion.shift.servlets.db;
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class InsertServlet extends HttpServlet {
public void init(ServletConfig config) throws ServletException {
super.init(config);
}
public void destroy() {
}
public boolean processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
String rec_num = request.getParameter("rec_num");
String tester_num = request.getParameter("tester_num");
String t_date = request.getParameter("t_date");
String t_time = request.getParameter("t_time");
String sys_type = request.getParameter("sys_type");
String packages = request.getParameter("package");
String sockets = request.getParameter("sockets");
String sockets = request.getParameter("val");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
PreparedStatement ps = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@oradev2.*****.com:1521:RPADB","shift_admin", //
"shift_admin"
);
String sql;
sql = "INSERT INTO fusion_shiftrpt(RECORD_NUM, TESTER_NUM, T_DATE, T_TIME, SYSTEM_TYPE, PACKAGE, SOCKETS,VAL) VALUES (?,?,?,?,?,?,?,?)";
ps = con.prepareStatement(sql);
stmt = con.createStatement();
ps.setString(1, rec_num);
.0+ ps.setString(2, tester_num);
ps.setString(3, t_date);
ps.setString(4, t_time);
ps.setString(5, sys_type);
ps.setString(6, packages);
ps.setString(7, sockets);
ps.setString(8, val);
ps.executeUpdate();
} catch (SQLException e) {
throw new ServletException(e);
} catch (ClassNotFoundException e) {
throw new ServletException(e);
} finally {
try {
if(rs != null)
rs.close();
if(stmt != null)
stmt.close();
if(ps != null)
ps.close();
if(con != null)
con.close();
} catch (SQLException e) {}
}
return(true);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request,response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request,response);
//String url = request.getRequestURI();
//System.out.println(url);
}
}
回答by carson
You have access to the request in a JSP. So if your JSPwere to be accessed like this:
您可以访问JSP. 因此，如果您JSP要像这样访问：
test.jsp?q=userinput
You could get to it like this in the JSP:
你可以像这样在JSP：
request.getParameter('userinput');
You should convert your JSPcode to at least use a preparedStatementwhen you do this:
执行此操作时，您应该将JSP代码转换为至少使用 a preparedStatement：
PreparedStatement ps = connection.prepareStatement("select * from fus where tester_num like ?");
ps.setString(1, "%" + request.getParameter('userinput') + "%");
ResultSet resultSet = ps.executeQuery();
回答by carson
As tvanfosson said, you should remove all database access code from your view logic (JSP). You should just show the info in your JSP, let the Servlet do all the processing. I also strongly recommend you to use an OMR framework like Hibernate.
正如 tvanfosson 所说，您应该从视图逻辑 (JSP) 中删除所有数据库访问代码。您应该只在 JSP 中显示信息，让 Servlet 完成所有处理。我还强烈建议您使用像 Hibernate 这样的 OMR 框架。
回答by James Schek
Ifyou insist on staying with this design, I would suggest that you use JSTL. This provides a set of tags for accessing data, controlling logic, and performing SQL access.
如果您坚持使用这种设计，我建议您使用JSTL。这提供了一组用于访问数据、控制逻辑和执行 SQL 访问的标记。
See the Sun Tutorial on the Standard Tag Library and the SQL tags. This is a much better approach than embedding scriptlets into your JSP. That said, I would recommend this approach (or scriplets) only be used for prototypes or as a very-temporary fix.
请参阅有关标准标记库和SQL 标记的 Sun 教程。这是比将 scriptlet 嵌入 JSP 更好的方法。也就是说，我建议这种方法（或脚本）仅用于原型或作为非常临时的修复程序。
With JSTL, you could replace all of the scriptlets with something similar to:
使用 JSTL，您可以将所有 scriptlet 替换为类似于以下内容的内容：
<sql:query var="rows" >
select * from fus where tester_num like ?
<sql:param value="${param.user_input}" />
</sql:query>
<table>
<c:forEach var="row" items="${rows}">
<tr>
<td>${row.column1name}</td>
<td>${row.column2name}</td>
<td>${row.column3name}</td>
</tr>
</c:forEach>
</table>
