Jsp connection to postgresql database

[Shanky33]

I am doing a project where i need to use a jsp to connect to a postgresql database and then show the results of a querry in the jsp. please can any1telme the steps and the jsp code ...thanks

[r035198x]

Don't put the database connection code in a JSP. Put it in a normal java class and test that it works properly. Then call that Java class from the JSP when you need to display the results. That way you separate your processing logic from your presentation logic allowing for testable, reusable code which is also easy to read.

[Shanky33]

hey thanx for the idea but can write the codes for it .. i hav been getting a lot of errors ...wil b very helpful

[Shanky33]

import java.sql.*;
2
3    
4
5     public class Connect {
6
7         public static void main(String argv[]) throws Exception {
8
9             // Initialize variables.
10            Connection con = null;
11
12            try {
13
14                // Set the connection URL.
15                String url = "jdbc:postgresql://localhost:5432/source_db"; // db name:  source_db
16
17                Class.forName("org.postgresql.Driver").newInstance();
18
19                // Connect.
20                con = DriverManager.getConnection(url);
21

22               // Report on the results.
23               if (con != null) {
24                   System.out.println("A database connection has been established!");
25               }
26
27           } catch (Exception e) {
28
29               System.out.println("Problem: " + e.toString());
30
31           }
32
33           // Clean up.
34           finally {
35
36              if (con != null) {
37
38                  try {
39                      con.close();
40                  } catch (Exception e) {
41                      System.out.println(e.toString());
42                  }
43                  con = null;
44              }
45          }

// i wrote this code ....can u correct me??

[r035198x]

Use code tags when posting code and don't copy it here with line numbers.
What are the errors that you are getting?

[Shanky33]

i think the connection is not established ..then i made class where i tried to establish a connection to database nly....it is getting compiled properly but not able to run...
the code :

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
 
public class xyz {
    public static void main(String[] args) {
 
        Connection conn = null;
 
        try {
            
            String url = "jdbc:postgresql://localhost:5432/source_db";
            String user = "postgres";
            String password = "NIC@123";
 
            conn1 = DriverManager.getConnection(url, user, password);
            if (conn1 != null) {
                System.out.println("Connected to the database postgres");
            }
 
            }
         catch (SQLException ex) {
            System.out.println("An error occurred. Maybe user/password is invalid");
            ex.printStackTrace();
        }
    }
}

[r035198x]

Again, use code tags and again read the exception message. What does it say?

[Shanky33]

it is now working .... now can u helo me in writing the code for using the connection class (xyz) to fetch querry results from database???

[r035198x]

http://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html

[Shanky33]

but when do i use the class i made ?? no import statement??

[Shanky33]

actually i am a little short on time ...so if u can give the correct code using my class to fetch a 'select * from dir_s' statement...it would be really helpful ... actually I am bit new to java and jsps.

[r035198x]

You would have been done reading that page by now and be able to do it yourself.

[Shanky33]

well i read your doc n it ws very useful just wanted to ask u one thing ....
'public static void viewTable(Conne ction con, String dbName)
throws SQLException ' in ur doc is the connection class ???

[Shanky33]

i have understood the steps aftr 'creating statement'