JSP code help needed ...

Hi,

I have the following JSP code.  This is for JSP with MySQL. But i want the code in JSP with ORACLE. I have changed ALL the code EXCEPT the following line:

long order_id = ((org.gjt.mm.mysql.PreparedStatement) stmt).getLastInsertID();

When we are inserting a record into "Orders" table, we are not passing this value (order_id). This is generated by SEQUENCE in ORACLE and a TRIGGER on "Orders" table is used to insert the SEQUENCE value into "Orders" table. Now i want to get the newly inserted value of (order_id) in "Orders" table. The below statement will work JSP with MySQL but not JSP with ORACLE.

long order_id = ((org.gjt.mm.mysql.PreparedStatement) stmt).getLastInsertID();

Can you please provide the code in JSP with ORACLE ?

JSP Code:
========

<%

               Class.forName("org.gjt.mm.mysql.Driver");

               java.sql.Connection cn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost/shop");

               String query = "INSERT INTO orders VALUES ('', ?, ?, ?, ?, ?, ?, ?, ?)";
               java.sql.PreparedStatement stmt = cn.prepareStatement(query);

               stmt.setString(1, request.getParameter("firstname"));
               stmt.setString(2, request.getParameter("surname"));
               stmt.setString(3, request.getParameter("address"));
               stmt.setString(4, request.getParameter("city"));
               stmt.setString(5, request.getParameter("postcode"));
               stmt.setString(6, request.getParameter("card_number"));
               stmt.setString(7, request.getParameter("card_type"));
               stmt.setString(8, request.getParameter("totalvalue"));

               stmt.executeUpdate();

               long order_id = ((org.gjt.mm.mysql.PreparedStatement) stmt).getLastInsertID();

               String order_info_query = "INSERT INTO order_info VALUES ('', ?, ?, ?)";

               Enumeration products = basket.getProducts();

               while (products.hasMoreElements())
               {
                    Product product = (Product) products.nextElement();

                    stmt = cn.prepareStatement(order_info_query);

                    stmt.setLong(1, order_id);
                    stmt.setInt (2, Integer.parseInt(product.getId()));
                    stmt.setInt (3, product.getQuantity());

                    stmt.executeUpdate();
               }

               stmt.close();
               cn.close();

               basket.emptyBasket();

%>

Thanks
milani_lucieAsked:
Who is Participating?
 
Pramod KumarCommented:
You may add below method to get the last id. This will work with all kinds of Database:

and call this method as:
long order_id = getLastInsertID();

public long getLastInsertID(Connection cn) throws Exception
    {
        Statement st = cn.createStatement();
        ResultSet rs = st.executeQuery("select max(order_id) from orders");
        return rs.getLong(1);
    }

Open in new window

0
 
Pramod KumarCommented:
or if you don't want to add method in JSP, the only the code can be used

- - - - -
- - - - -
Statement st = cn.createStatement();
ResultSet rs = st.executeQuery("select max(order_id) from orders");
long order_id = rs.getLong(1);
- - - - -
- - - - -
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.