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, Jun 23 '14 at 15:09
`SELECT rs` and then `getString("str")`? You're not selecting the field you're trying to get at. — Marc B, Jun 23 '14 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, Jun 23 '14 at 15:10
No error traces are printed. Just the 3 strings get added to the al. — Nikunj Banka, Jun 23 '14 at 15:14
Check logs, you should use the same name /columnname returns in resultset . Most important close the resources ( connections , statements,resultsets) — Mani, Jun 23 '14 at 15:22
Use Select * From Table and rs.getString("columnName") for each column — Revive, Jun 23 '14 at 15:22
Yes I think I have the problem sorted out now. From the error logs I can see that the class not found exception is getting thrown. (com.mysql.jdbc.Driver). Thanks. — Nikunj Banka, Jun 23 '14 at 15:23

score 3 · Accepted Answer · edited May 23 '17 at 12:10

3

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:

edited May 23 '17 at 12:10

Community

1
1

answered Jun 23 '14 at 15:37

Paul Vargas

41,222
15
102
148

@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. – Mr Morgan Jun 23 '14 at 15:44
It is another way to do it too. Thanks for sharing your thoughts. – Paul Vargas Jun 23 '14 at 15:46

score 1 · Answer 2 · answered Jun 23 '14 at 15:49

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 }

score 1 · Answer 3 · answered Jun 23 '14 at 15:58

1

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

answered Jun 23 '14 at 15:58

Harsha

377
1
8
21

score 1 · Answer 4 · edited Jun 23 '14 at 17:45

1

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.

edited Jun 23 '14 at 17:45

Deepend

4,057
17
60
101

answered Jun 23 '14 at 17:22

BruceRudd

126
5

score 1 · Answer 5 · answered Jun 24 '14 at 08:46

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

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

5 Answers5