Solved

How to use combo box for record insert with JDBC in JAVA Servlet

Posted on 2010-11-11
9
2,191 Views
Last Modified: 2013-11-23
Hello,

I would like to know how I go about using a combo box, populated with query results to insert a record in an Oracle database.  I would like the user to be able to choose from existing records so that there are no constraint violations.  Short of displaying all info for each table on each form page, I lack the knowledge to use combo boxes for this type of work.

Can anyone help?

Here is one of the servlet pages for inserting a sale as well as a view of the table itself. (screenshot and code)
Sale Insert Form
Display of Sale Table
The attribute for the SoldTo is an FK to a "Customer" table and the SoldBy is an FK to an "Agent" table. I ahve removed the connection info for privacy (the connection is not the problem).  I have no errors running it, I just want to do it better and lack the experience/know-how.  

import java.sql.*;

import java.io.*;

import javax.servlet.*;

import javax.servlet.http.*;

import java.lang.String.*;



public class SaleInsert extends HttpServlet {



    public static final String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";

    public static final String URL = "jdbc:odbc:rreOracle";

    public static final String username = "<removed>";

    public static final String password = "<removed>";



    public void doGet(HttpServletRequest req, HttpServletResponse res)

            throws ServletException, IOException {



        res.setContentType("text/html");

        PrintWriter out = res.getWriter();



        out.print("<html><head>");

        out.print("<title>Add Sale</title>");

        out.print("<link href=\"style.css\" rel=\"stylesheet\" type=\"text/css\" />");

        out.print("</head>");

        out.print("<body>");

        out.print("<center><h2>Add a Sale</h2></center>");

        out.print("<div><form action=\"");

        out.print(req.getRequestURI());

        out.print("\" method=\"post\">");

        out.print("<center><table class=\"ins_form_tbl\">");

        out.print("<tr><td>SoldWhich Home ID:  </td><td>");

        out.print("<input type=\"text\" name=\"sh\"></td></tr>");

        out.print("<tr><td>SoldTo Customer ID:  </td><td>");

        out.print("<input type=\"text\" name=\"sc\"></td></tr>");

        out.print("<tr><td>SoldBy Agent ID:  </td><td>");

        out.print("<input type=\"text\" name=\"sa\"></td></tr>");

        out.print("<tr><td>Sold Price:  </td><td>");

        out.print("<input type=\"text\" name=\"sold\"></td></tr>");

        out.print("<tr><td>Date of Sale:  </td><td>");

        out.print("<input type=\"text\" name=\"dos\"></td></tr>");

        out.print("<tr><td align=\"center\">" +

                  "<p><br /><input type=\"submit\" value=\"Insert Record\" class=\"btn\"></p></td></tr>");

        out.print("<tr><td align=\"center\">" +

                  "<p><br /><input type=\"reset\" value=\"Clear the Form\" class=\"btn\"></p></td></tr>");

        out.print("</table>");

        out.print("<p><a href=\"gcurrierFA.html\" class=\"links\">Return to Main Page</a></p>");

        out.print("<p><a href=\"SaleDisplay\" class=\"links\">Display Sale Records</a></p>");

        out.print("</center></form></div>");

        out.print("</body></html>");



        out.close();

    }



    public void doPost(HttpServletRequest req, HttpServletResponse res)

            throws ServletException, IOException {



        res.setContentType("text/html");

        PrintWriter out = res.getWriter();

        

        //upon successful record insert

        out.print("<html><body>");

        out.print("<link href=\"style.css\" rel=\"stylesheet\" type=\"text/css\" />");

        out.print("<head><center><h2>Record Added Successfully!</h2></center></head>");

        out.print("<div><center><br /><table cellpadding=\"5\">");

        out.print("<tr><td><a href=\"SaleInsert\" class=\"links\">Add Another Sale Record</a>");

        out.print("  <a href=\"SaleUpdate\" class=\"links\">Update Sale Records</a>");

        out.print("  <a href=\"SaleDelete\" class=\"links\">Delete Sale Record</a></td></tr>");

        out.print("<tr align=\"center\"><td><a href=\"SaleDisplay\" class=\"links\">Display Sale Records</a>");

        out.print("  <a href=\"gcurrierFA.html\" class=\"links\">Back to Main Page</a></td></tr>");



        // receiving parameters

        String sh = req.getParameter("sh").trim();

        String sc = req.getParameter("sc").trim();

        String sa = req.getParameter("sa").trim();

        String sold = req.getParameter("sold").trim();

        String dos = req.getParameter("dos").trim();

        

                

        boolean proceed = false;





        if(sc != null && sa != null){

            if(sc.length() > 0 && sa.length() > 0){                    

                  proceed = true;

            }

        }

        // connecting to database

        Connection con = null;

        Statement stmt = null;

        ResultSet rs = null;

        PreparedStatement ps = null;



        try {

            Class.forName(DRIVER);

            con = DriverManager.getConnection(URL, username, password);



            String sql;

            sql = "INSERT INTO sale (sale_home_id,sale_cust_id,sale_agent_id,actual_amount,sale_date) VALUES (?,?,?,?,?)";

            ps = con.prepareStatement(sql);

            stmt = con.createStatement();



            // inserting records



            if (!proceed) {

                out.print("No records inserted.  Go back to try again.");

              }else if (proceed){

                ps.setString(1, sh);

                ps.setString(2, sc);

                ps.setString(3, sa);

                ps.setString(4, sold);

                ps.setString(5, dos);

                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.commit();

                    con.close();

                }

            } catch (SQLException e) {

            }

        }

        

        out.print("</table></center></div>");

        out.print("</body></html>");

        out.close();

    }

}

Open in new window


I appreciate the assistance...thanks
0
Comment
Question by:g_currier
  • 5
  • 3
9 Comments
 
LVL 4

Accepted Solution

by:
darkyin87 earned 250 total points
ID: 34112671
You will need to use addItem function of combo box

http://download.oracle.com/javase/7/docs/api/javax/swing/JComboBox.html#addItem%28java.lang.Object%29

rs = stmt.executeQuery("SELECT Id,Label FROM SomeTable ORDER BY Id");
while (rs.next()){
    comboBox.addItem(newObject(rs.getInt(1),rs.getString(2).trim()));
}
rs.close();
0
 

Author Comment

by:g_currier
ID: 34115673
Can you explain please?  I mean I can create the method
[code]
public ResultSet getHomeData() throws SQLException{
        ResultSet rs1 = null;
        Statement stmt1 = null;
        JComboBox HomeIns = new JComboBox();
        try{
            rs1 = stmt1.executeQuery("SELECT home_id,address_street,address_city,purchase_price FROM Home ORDER BY home_id");
            while (rs1.next()){
            HomeIns.addItem(HomeObj(rs1.getInt("home_id"),rs1.getString("address_street"),rs1.getString("address_city"),rs1.getString("purchase_price").trim()));
            }
            rs1.close();
        }
        catch(SQLException e){}
        return rs1;
    }
[/code]

But how does that integrate into the HTML written in the rest of the servlet?  I'm sorry, I'm not very good at this yet (I can't even figure out how to get the HomeObj Object instantiated.

Thanks
0
 

Author Comment

by:g_currier
ID: 34115985
Or Maybe something like this

but then, what does HomeInfo look like?
 public ResultSet HomeData() throws SQLException{

        

        JComboBox HomeIns = new JComboBox();

        

        try{

            String sql = "SELECT home_id,address_street,address_city,purchase_price FROM Home ORDER BY home_id";

            ResultSet rs1 = null;

            Statement stmt1 = null;

            rs1 = stmt1.executeQuery(sql);

            

            Integer home_id;

            String address_street = "";

            String address_city = "";

            String purchase_price = "";

            

            while (rs1.next()){

                home_id = rs1.getInt("home_id");

                address_street = rs1.getString("address_street");

                address_city = rs1.getString("address_city");

                purchase_price = rs1.getString("purchase_price");

                //Create homedata object

                HomeInfo HomeObj = new HomeInfo(home_id,address_street,address_city,purchase_price);

                HomeIns.addItem(HomeObj);

                

                

            }

            rs1.close();

        }

        catch(SQLException e){}

        return rs1;

    }

Open in new window

0
 
LVL 10

Assisted Solution

by:Pramod Kumar
Pramod Kumar earned 250 total points
ID: 34118178
SERVER
======
String sql = "select id from customer";
....
....
run the query and fill the result in an array
....
String soldTo[] = {};
request.setAttribute("soldTo",soldTo);

String sql = "select id from agent";
....
....
run the query and fill the result in an array
....
String soldBy[] = {};
request.setAttribute("soldBy",soldBy);



CLIENT (JSP)
============
String soldTo[] = (String[])request.getAttribute("soldTo");
String soldBy[] = (String[])request.getAttribute("soldBy");

<!--fill the drop-down in form-->

<form .. . . . . >

SOLD TO <select name=soldtobox>
<%
      for(String item : soldTo)
      {
            out.println("<option value="+item+"</option>");
      }
%>
</select>


SOLD BY <select name=soldbybox>
<%
      for(String item : soldBy)
      {
            out.println("<option value="+item+"</option>");
      }
%>
</select>


</form>

flow.JPG
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Expert Comment

by:Pramod Kumar
ID: 34118186
Please correct the line:
 out.println("<option value="+item+">"+item+"</option>");
0
 
LVL 10

Expert Comment

by:Pramod Kumar
ID: 34118195
And if you want to use SERVLET only in place of JSP, then you may implement the same logic of JSP into your SERVLET
0
 

Author Comment

by:g_currier
ID: 34119857
OK,
I think I have this mostly right.  But in the for each statement, the soldWhich var is not declared, yet it is delcared in the try block.  I'm trying, but there is something I'm missing.  I just don't know what...

public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {



        res.setContentType("text/html");

        PrintWriter out = res.getWriter();

         

        

        out.print("<html><head>");

        out.print("<title>Add Sale</title>");

        out.print("<link href=\"style.css\" rel=\"stylesheet\" type=\"text/css\" />");

        out.print("</head>");

        out.print("<body>");

        out.print("<center><h2>Add a Sale</h2></center>");

        out.print("<div><form action=\"");

        out.print(req.getRequestURI());

        out.print("\" method=\"post\">");

        out.print("<center><table class=\"ins_form_tbl\">");

//        out.print("<input type=\"text\" name=\"sh\"></td></tr>");

        

        out.print("<tr><td>SoldWhich Home ID:  </td><td>");



        

        JComboBox HomeIns = new JComboBox();

        Object HomeObj = new Object();



        try{

            String sh = null;

            String[] soldWhich =(String[])req.getAttribute(sh);

            String sql = "SELECT home_id FROM Home ORDER BY home_id";

            ResultSet rs1 = null;

            Statement stmt1 = null;

            rs1 = stmt1.executeQuery(sql);

            

            while (rs1.next()){

                req.setAttribute(sh,soldWhich);

            }

            rs1.close();

        }

        catch(Exception e){}

        

        out.print("<select name=soldWhichBox>");

        for(String sh: soldWhich){

            out.print("<option value="+ sh +">"+ sh +"</option>");

        }

        out.print("</select>");

        

        out.print("</td></tr>");

        out.print("<tr><td>SoldTo Customer ID:  </td><td>");

        out.print("<input type=\"text\" name=\"sc\"></td></tr>");

        out.print("<tr><td>SoldBy Agent ID:  </td><td>");

        out.print("<input type=\"text\" name=\"sa\"></td></tr>");

        out.print("<tr><td>Sold Price:  </td><td>");

        out.print("<input type=\"text\" name=\"sold\"></td></tr>");

        out.print("<tr><td>Date of Sale:  </td><td>");

        out.print("<input type=\"text\" name=\"dos\"></td></tr>");

        out.print("<tr><td align=\"center\">" +

                  "<p><br /><input type=\"submit\" value=\"Insert Record\" class=\"btn\"></p></td></tr>");

        out.print("<tr><td align=\"center\">" +

                  "<p><br /><input type=\"reset\" value=\"Clear the Form\" class=\"btn\"></p></td></tr>");

        out.print("</table>");

        out.print("<p><a href=\"gcurrierFA.html\" class=\"links\">Return to Main Page</a></p>");

        out.print("<p><a href=\"SaleDisplay\" class=\"links\">Display Sale Records</a></p>");

        out.print("</center></form></div>");

        out.print("</body></html>");



        out.close();

    }

Open in new window

0
 

Author Comment

by:g_currier
ID: 34120195
Or maybe something like this?
out.print("<select name=soldWhichBox>");

        for(String sh : soldWhich){

            if (!sh.isEmpty()){

            out.print("<option value="+ sh +">"+ sh +"</option>");

            }

        }

        out.print("</select>");

Open in new window

0
 

Author Closing Comment

by:g_currier
ID: 34123769
Needed follow up help...moved to different question
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Windows Tool to Build Android and iOS App 3 54
xampp tool 12 28
'G_F01' is not a procedure or is undefined 3 12
servlet example issue 6 32
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now