Member Avatar for SwapnilDD

Hi.. I am trying to connect to my database (MySQL) within a JSP page.
The is compiling without any error, but still I am not getting any output.
Can anybody help??
The code is as follows:

<%@page contentType="text/html" pageEncoding="windows-1252"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head><title>Enter to database</title></head>
<body>
<table>
<%@ page import="java.util.*" %>
<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>
<%
        java.sql.Connection connecton=null;
	java.sql.Statement stmt=null;
	java.sql.PreparedStatement prepStmt=null;
        ResultSet rs = null;
	try
		{
			Class.forName("com.mysql.jdbc.Driver");
			connecton=DriverManager.getConnection("jdbc:mysql://localhost:3306/project_demo","Sops","adminadmin");
			stmt=connecton.createStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
String sql = "select * from test";
try{
//stmt = DBConnection.getStatement();
    rs = stmt.executeQuery(sql);
%>

<%
while( rs.next() ){
%><tr>
<td><%= rs.getString("Question") %></td>
</tr>
<tr>
<td><%= rs.getString("Answer1") %></td>
</tr>
<tr>
<td><%= rs.getString("Answer2") %></td>
</tr>
<tr>
<td><%= rs.getString("Answer3") %></td>
</tr>
<tr>
<td><%= rs.getString("Answer4") %></td>
</tr>
<%
}
%>

<%

}
catch(Exception e){e.printStackTrace();}
finally{
if(rs!=null) rs.close();
if(stmt!=null) stmt.close();
if(connecton!=null) connecton.close();
}

%>
</table>

</body>
</html>
  • 3 Contributors
  • 4 Replies
  • 2K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by SwapnilDD
Member Avatar for javaAddict

Have you looked at the printStackTrace ?

Member Avatar for tyson.crouch

Rather than calling the DB from your JSP page, you should create the following class, then from your .java files call the getConnection() function then follow on with your SQL.


database.java

public class database {

    private Connection connection;
    private static String databaseName = "********";
    private static String databaseUsername = "*******";
    private static String databasePassword = "*******";
    private static String driver = "com.mysql.jdbc.Driver";

    public Connection getConnection() {
        createConnection();
        return connection;
    }

    public void createConnection() {
        if (connection == null) {
            try {
                Class.forName(driver);
                String url = "jdbc:mysql://localhost:3306/" + databaseName;

                connection = DriverManager.getConnection(
                        url, databaseUsername, databasePassword);

            } catch (Exception e) {
                JOptionPane.showMessageDialog(new JFrame(),
                        "Database connection could not be established.  Please" +
                        "check your network connection and restart.",
                        "Error Message",
                        JOptionPane.ERROR_MESSAGE);
                e.printStackTrace();
                connection = null;
            }
        }
    }
}

test.java

public class UCTest{

	public String[] run(){
		
		try{
			Connection con = new database().getConnection();
			Statement stmt = con.createStatement();

			String sql = "SELECT * FROM test";
			ResultSet res = stmt.executeQuery(sql);

                        int count = 0;
                        while(res.next()){
                            
                            count++;
                        }
                        
                        res.beforeFirst();
                        int i = 0;
                        String[] test = new String[count];
			while(res.next()){
                            test[i] = res.getString("Q1");
                            //// Continue code from here for obtaining answers
                        }

                }catch(SQLException e){
                    e.printStackTrace();
                }
	}
}

test.jsp

<%@ page import="UseCase.*" %>
<%
    String[] test = new test().run();
    // Output array from here

%>
<html>
....
</html>

I must apologize, my example is rather brief, but should explain how to fix your problem.

Also, please take a look at http://www.daniweb.com/forums/thread141776.html

This is a very good tutorial for proper DB connections with jsp.

Member Avatar for javaAddict

It is also could to close what you open. And you also forgot to return the array and increase the index 'i' :) . May I post some similar code?

public class UCTest{

	public String[] run() throws SQLException {
		Connection con = null;
                Statement stmt = null;
                ResultSet res = null;
                String[] test = null;
		try{
			con = new database().getConnection();
			stmt = con.createStatement();

			String sql = "SELECT * FROM test";
			res = stmt.executeQuery(sql);

                        int count = 0;
                        while(res.next()){
                            
                            count++;
                        }
                        
                        res.beforeFirst();
                        int i = 0;
                        test = new String[count];
			while(res.next()){
                            test[i] = res.getString("Q1");
                            //// Continue code from here for obtaining answers

i++;
                        }

                }catch(SQLException e){
                    e.printStackTrace();
                } finally {
                   if (rs!=null) rs.close;
                   if (stmt!=null) stmt.close; 
                   if (con!=null) con.close;
                 }
                 return test;
	}
}

And if you want to return more columns you'd better define a class with attributes the columns and return an array of that object.

Edited by javaAddict because: n/a
Member Avatar for SwapnilDD

Hi. Thnx for your replies tyson.crouch and javaAddict . The problem is solved. Actually, I was working in Netbeans, and when I connected to the database from the "Services" tab, the problem was solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.