tags:

views:

4334

answers:

4
+1  Q: 

Querying data from Oracle database using java servlet with Netbeans.

From index.jsp code, 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 ("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;)

Index.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

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);

}
 }
+1  A: 

You have access to the request in a JSP. So if your JSP where to be accessed like this:

test.jsp?q=userinput

You could get to it like this in the JSP:

request.getParameter('userinput');

You should convert your JSP code to at least use a preparedStatement when you do this:

PreparedStatement ps = connection.prepareStatement("select * from fus where tester_num like ?");
ps.setString(1, "%" + request.getParameter('userinput') + "%");
ResultSet resultSet = ps.executeQuery();
carson  2008-11-27 12:14:05
Maybe slap a caveat on this answer that this is not a recommended best practice...
James Schek  2008-12-02 16:50:54
A: 

You could do as @carson suggests, but then you'd just have another poorly designed app that has it's database access code strewn hither and yon across your view logic. Sun recommends that you use a Model-View-Controller pattern -- with your JSP as your view engine -- when working with JSP. Moving to this would take a lot longer than fixing your immediate problem, but it would be worth it in the end if this application is important to your organization and/or will need to be maintained for a significant period of time. What I suggest in the mean time is that your data access code be put in some common backend data access class (or set of classes). Start building it to solve this problem. Going forward, I suggest you look at Spring for MVC and Hibernate (or another ORM) for data access, respectively.

tvanfosson  2008-11-27 13:10:32
How does this help him?
carson  2008-11-27 13:20:41
It helps him learn to write good code rather than crap. Give a man fish, he eats for a day. Teach a man to fish, he'll never go hungry.
tvanfosson  2008-11-27 13:22:33
He was looking for a solution not philosophy. Of course it is bad form to put code in the JSP but it is already there. There isn't enough room here to properly describe the best way to do this vs a way to do it as is.
carson  2008-11-27 13:32:01
A: 

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.

 2008-11-27 13:16:21
+1  A: 

If you 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.

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.

With JSTL, you could replace all of the scriptlets with something similar to:

<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>
James Schek  2008-12-02 16:43:43

related questions

Java Time Zone is messed up
Eclipse on win64
Automate builds for Java RCP for deployment with JNLP
Why are professors or schools picking Java over C++ to teach to students?
Is there a real benefit of using J#?
Public/Popular Websites using JavaServer Faces
Why can't I use a try block around my super() call?
Accessing post variables using Java Servlets
Personal Linux web server
Is this really widening vs autoboxing?
How can I Java webstart multiple, dependent, native libraries?
Why can't I call toString() on a Java primitive?
How do I use Java to read from a file that is actively being written?
What code analysis tools do you use for your Java projects?
IllegalArgumentException or NullPointerException for a null parameter?
How do I configure and communicate with a serial port?
What is the best way to parse strings in Java
Getting started with a custom JXTA PeerGroup
Creating a custom button in Java
How to get started "writing" a code coverage tool?
Which Build-/Configuration Management Tool?
What is the difference between an int and an Integer in Java/C#?
What is the meaning of the type safety warning in certain Java generics casts?
How would you access Object properties from within an object method?
Converting CSV File to XML in Java