How to retrieve data from a database from a jsp file?

Question

I have a .jsp file which basically displays data from a database. The database class is defined separately in a .java file. To get the contents of the database I am calling the getData method of the database. But the function calls made never execute and nothing is returned.

However if I return any pre-computed values from the getData function, then it executes fine.

I want to know how can I access the database from the .jsp file.

I don't want to add the java code directly to the .jsp file. So I want to do it via a method call.

Function from .jsp file:

<% ArrayList<String> al = com.Database.getData(); %>

Java function:

getData(){ al = new ArrayList<String>(); Connection conn = null; Statement stmt = null; ResultSet rs = null; al.add("first"); try{ Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(DB_URL,USER,PASS); stmt = conn.createStatement(); String sql = "SELECT rs FROM DATABASE"; rs = stmt.executeQuery(sql); while(rs.next()){ String str = rs.getString("str"); al.add(str); } }catch(Exception e){ e.printStackTrace(); } al.add("nikunj "); al.add("banka "); return al; }

The contents of the arraylist after the call are {"first", "nikunj", "banka"} and no data from the database.

How can I get the data from the database. I have tried creating a static block that will populate the ArrayList at the start of the program but even this is not working.

You're selecting the field rs from the table DATABASE, and then trying to get the field str out of the ResultSet... — sp00m
– sp00m, Commented Jun 23, 2014 at 15:09
SELECT rs and then getString("str")? You're not selecting the field you're trying to get at. — Marc B
– Marc B, Commented Jun 23, 2014 at 15:09
rs will hold all the data in the database. and I will get the data sequentially and add it to the al. — Nikunj Banka
– Nikunj Banka, Commented Jun 23, 2014 at 15:10
No error traces are printed. Just the 3 strings get added to the al. — Nikunj Banka
– Nikunj Banka, Commented Jun 23, 2014 at 15:14

Community · Accepted Answer · 2017-05-23 12:10:59Z

There are several ways to do this. You can also use JSTL if you want to avoid Java code in your JSP. e.g.:

<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %> <%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %> <sql:setDataSource var="dataSource" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/test" user="root" password="pass123"/> <html> <head> <title>JSTL SQL</title> </head> <body> <sql:query var = "result" dataSource="${dataSource}"> SELECT str FROM table </sql:query> <table border=1> <c:forEach var="row" query="${result.rows}"> <tr> <td><c:out value="${row.str}"/></td> </tr> </c:forEach> </table> </body> </html>

Maybe you want to read:

@Nikunj Banka: Paul Vargas is correct, you should avoid Java code in JSPs. A servlet would be the best thing to use here. Use the appropriate GET or POST method of the servlet, retrieve the data from the database, then write it in the servlet to the request object to display it in the JSP using JSTL tags.
It is another way to do it too. Thanks for sharing your thoughts.

SparkOn · Accepted Answer · 2014-06-23 15:49:47Z

create a jsp have this code:

<% List list = main.myPack.Test.getData(); System.out.println(list); %>

create a static method of a class like this :

public static List getData() { List<String> list = new ArrayList<String>(); try{ System.out.println("invoked"); Class.forName("com.mysql.jdbc.Driver"); System.out.println("making connection"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "welcome"); System.out.println("Connection established"); Statement stmt=con.createStatement(); ResultSet rset=stmt.executeQuery("select * from student"); System.out.println("fetching data"); while(rset.next()) { String name = rset.getString("student_name"); String id = rset.getString("student_id"); list.add(name); list.add(id); System.out.println(name+" "+id); } System.out.println(list); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return list;

then call the jsp thats it what you want }

Harsha · Accepted Answer · 2014-06-23 15:58:13Z

Check your SQL query column with ResultSet.getString() methods dear. If you used SELECT str FROM database_table it's fine.

Deepend · Accepted Answer · 2014-06-23 17:45:45Z

I think @Harsha has pointed you in the right direction. ResultSet.getString() has two overloads.

One takes an Int that represent which column, ordinally, you want to retrieve.
The other, which you used, take a String which would be the column name in the result set.

Since your SELECT statement only selected the column "rs" you have no column "str" in your result set to retrieve.

wrecklez · Accepted Answer · 2014-06-24 08:46:16Z

i think you should check your sql statement you have database error i think!

//String sql = "SELECT rs FROM DATABASE"; String sql = "SELECT str FROM DATABASE"; rs = stmt.executeQuery(sql); while(rs.next()){ String str = rs.getString("str"); //you get "str" field here not "rs" al.add(str); }

Collectives™ on Stack Overflow

How to retrieve data from a database from a jsp file?

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Linked

Related