0

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
5
Views
7 Years
Discussion Span
Last Post by SwapnilDD
0

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.

1

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: n/a

0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.