retrieve of data from oracle

Question

I have a table in oracle database with name category and inside this category table i have column c_nm and data are category1, category2, category3, ......and so on I want all this data to store in an array or String on my jsp page. Example:

<%@ include file"connection.jsp"%> // for connecting to data base 
<%! String S1[]= new String[]%> declare a string

<%
rs=stat.executeQuery("select * from category ");
while(rs.next()){
s1[]=rs.getString(c_nm);
} 
%>

Is this right coding or should i choose another method to do this?

Just to understand it right. You got a database named "category" and inside that you got a table named "category" which has a column named "c_nm" with the values "category1", "category2", ...? — totoro, May 07 '16 at 14:59
i am not getting all categories in the array is this something wrong in declaring of array — Rizwan, May 07 '16 at 15:16
Do **not** put SQL (or Java) code into a JSP. And do not initiate database connections from within a JSP. This is really bad coding style. — , May 08 '16 at 21:26
And it's important to close your result set when you're done with it. Use `try (ResultSet rs = stat.executeQuery("select * from category")) { /* ...process results... */ }`. (This is called a "try-with-resources block", and it ensures that the result set's `close` method is called at the end — even if an exception is thrown.) — Wyzard, May 08 '16 at 22:09

score 0 · Accepted Answer · edited May 23 '17 at 11:59

If you want your list of categories, You need to loop through your results, and add it to a List. You would do a list, because you do not need to know beforehand how many categories you have.

rs=stat.executeQuery("select * from category ");
List<String> categories = new ArrayList<>();
while (rs.next()) {
  categories.add(rs.getString(c_nm));
}

Then you can convert, if you really need to, your list into an array:

s1 = categories.toArray(new String[0]);

If you are confused as to why I pass a zero length array, please research further: What to pass to the Arrays instance method toArray(T[] a) method?.

Other things to be careful for:

Java is case sensitive, S1 is not the same as s1, see your %! section.
not s1[]= but just simply s1= - see How to initialize an array in Java?
you need to specify the size of the array when creating one... that is = new String[10] vs new String []
I believe that statements in your <%! ... %> still need to end with a semicolon (;).

Anyway with all those corrections, it should be probably be something like this:

<%! String [] s1 = new String[0]; %>

We could have assigned null instead, but I do not know if the rest of the code will be ok. This seemed just the most prudent thing to do for now.

Now also, because you are using List and ArrayList, you will require import sections for it:

<%@ page import="java.util.List" %>
<%@ page import="java.util.ArrayList" %>

Then if you actually want to do something with those categories, you would want to write something similar to this:

<% for (String category:categories) { %>
<p><%=category%></p>
<% } %>

Note that I did not even use s1 or S1 (whatever you called the native array), instead I used the categories List directly (so there is really no need to do the conversion).

Note that there is much to be said about the solution I provided you, but I though it is the simplest one that directly answers to your questions.

retrieve of data from oracle

1 Answers1