JDBC Connection using different files

Question

First of all sorry for the name of the title, but i dont know how to put another one since english is not my native language.

I have the following method to connect to a database:

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

public class PgConnect {
    public  void connect() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:postgresql://pgserver:5432/db", "test","test123");
        } catch (SQLException e) {
            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return;
        }
        if (connection != null) {
            System.out.println("Connection working");
        } else {
            System.out.println("Failed to make connection!");
        }
    }
}

And what I need to do is include the method from the PgConnect in the code below. Basically I need this because I have many types of SQL's calls to database, and changing this to that way would be easy to maintain, since the credentials/host would be on one file only.

I believe the change should be where I have the comment

// i want to change this, for using the method on the first file. 

Please correct me if I'm wrong.

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ReturnResults {

    public static void main(String[] args) {
        Connection connection = null;
        try {
                // i want to change this, for using the method on the first file.
            connection = DriverManager.getConnection("jdbc:postgresql://pgserver:5432/db", "test","test123");

        } catch (SQLException e) {
            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return;
        }
        if (connection != null) {

            String result = null;
            String selectString = "select * from mwp.servers where env='TEST' order by server";
            //result ="iServer\tLabel\n";

            try {

                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery(selectString);

                while (rs.next()) {

                    String iEnv     = rs.getString("env");
                    String iServer  = rs.getString("iserver");
                    String iLabel   = rs.getString("label");
                    String iTitle   = rs.getString("title");
                    String iLogin   = rs.getString("login");

                    result=iEnv+"\t"+ iServer+"\t"+iLabel+"\t"+iTitle+"\t"+iLogin;

                    System.out.println(result);
                }
                stmt.close();
                connection.close();

            } catch(SQLException ex) {
                System.err.println("SQLException: " + ex.getMessage());
            }

        } else {
            System.out.println("Failed to make connection!");
        }
    }
}

I know how to do this on Perl, but I don't have any practice in Java.

Answer 1

Are you saying that you want to pull out getting a connection into its own function, so you can call it every time you need to make a sql call? It seems like that is what you are asking sorry if i am off base....

But if it is what you are asking then you are on the right track since this is a standard thing to do...

Why dont you try this, Ive tried to not only give you an answer but set you up with some best practices to get you started and make you life easier. .... (I assume that PGConnect.java is in the same package, if not resolve your address accordingly. Plenty of other posts on SO will describe that. Its a basic thing in java/most programming languages.) Update your first file to look like this, notice i am making the function to get Connection static and I am passing the exceptions to the calling method, this is so that we can handle those exceptions on a case by case basis. I prefer to even pass them out of the data layer entirly so that you can have good error reporting in your application, but that is very dependent on how you design that application.

package DatabaseCodePackage; //name you package something descriptive, its your call place both files into this package.

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

public class PgConnect {

    public static Connection getConnection(String username, String password) throws SQLException
    {
        return DriverManager.getConnection("jdbc:postgresql://pgserver:5432/db", username, password);
    }

Update the second file to something like this.... Directly typing SQL into JDBC is highly dicouraged if you are using any dynamic search values, which I assume you eventual will do, since almost every application will do that somewhere. see http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html Used prepared statements. Ive rewritten your SQL to use a prepared statment.

package DatabaseCodePackage; //name you package something descriptive, its your call place both files into this package. 

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class ReturnResults {

public static void main(String[] args) {

     Stirng result = null; 
     try{
         result = selectAllWithEnv("TEST");

      // I catch exceptions here because i like to let exception pass entirely out of the
      // data layer, this way the control logic calling for the database information can decide what to do when it 
      // cant get the information it wants. This is especailly good in a MVC type project. 
    } catch (NullPointerException npe){
        result = "Connection Failed! Check output console : " + e.getMessage();
        e.printStackTrace();
        return;
    } catch (SQLException e) {
        result = "SQL failure! Check output console : " + e.getMessage();
        e.printStackTrace();
        return;
    } finally { 
        System.out.println(result); 
    }
}

public static String selectAllWithEnv(String var) throws SQLException, NullPointerException {
    String SQL = "select * from mwp.servers where env=? order by server";
    Connection connection = null;
    StringBuilder sb = new StringBuiler();
    try {
        connection = PgConnect.getConnection();
        PreparedStatement ps = connection.prepareStatement(SQL);
        ps.setObject(1, var);
        ResuletSet rs = ps.executeQuery();

        while (rs.next()) {

            String iEnv     = rs.getString("env");
            String iServer  = rs.getString("iserver");
            String iLabel   = rs.getString("label");
            String iTitle   = rs.getString("title");
            String iLogin   = rs.getString("login");

            sb.append(iEnv + "\t" + iServer + "\t" + iLabel + "\t" + iTitle + "\t" + iLogin + "\n");
         }
    } finally {
        connection.close();
    }
    return sb.toString();
}

Notice also I put the con.close() in a finally block. ALWAYS ALWAYS ALWAYS do this. If you end up throwing an excpetion in the try block, this will ensure that it gets called. If you dont do this, you will have connection that stay alive for very long times and this can have a very negative performance impact. If you are working in a enterprise enviornment, and you dont do this, you will likely have DBA's on you case at some point for not killing your application connections. The stmt.close() is redundant on the con.close() if you are not using multiple statments on the same connection, so there is no reason to call that when calling con.close()

Answer 2

Make your connect() method static and then you can call it like this

Connection con = PgConnect.connect();

Also edit your method connect to Connection, because you need to return Connection not void.

public static Connection connect() throws SQLException {
    try {
        Connection con = DriverManager.getConnection("jdbc:postgresql://pgserver:5432/db", "test","test123");
        // ...
        return con;
    } 
    catch (SQLException e) {
        e.printStackTrace();
        return null;
    }

Also there is another, cleaner approach. Here is example from my old project.

private static DataSource getOracleDBConnection() throws NamingException {
        Context c = new InitialContext();
        return (DataSource) c.lookup("java:comp/env/OracleDBConnection");
    }

    public static Connection getOracleDatabaseConnection() {

        Connection conn = null;
        try {
            conn = OracleDAOFactory.getOracleDBConnection().getConnection();
        } 
        catch (NamingException ex) {
            Logger.getLogger(OracleDAOFactory.class.getName()).log(Level.SEVERE, null, ex);
        }
         catch (SQLException ex) {
            Logger.getLogger(OracleDAOFactory.class.getName()).log(Level.SEVERE, null, ex);
        }
        return conn;
    }

I'm using NetBeans so i don't know how you are able to do this in other IDEs but when you push ALT+Insert, there will be shown little menu and you can choose "Use database..." and create automatic just with a few clicks your Connection to database.

Answer 3

As I understand, you want to call the connect() method from PgConnect.java into ReturnResults.java so that you can use the Connection object.

You can do 2 things -

1. You can extend the PgConnect.java in ReturnResults.java like public class ReturnResults extends PgConnect and then use the connect method.
2. You can make your PgConnect class static and use it like PgConnect.connect() in the ReturnResults class.

Answer 4

You can use the method specified above, instead of return void return the connection:

public class PgConnect {
    //changing the method declaration to return a Connection
    public Connection connect() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection("jdbc:postgresql://pgserver:5432/db", "test","test123");
        } catch (SQLException e) {
            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
            return null;
        }
        if (connection != null) {
            System.out.println("Connection working");
        } else {
            System.out.println("Failed to make connection!");
        }
        return connection;
    }
}



public class ReturnResults {

    public static void main(String[] args) {
        Connection connection = null;
        PgConnect oPgConnect;
        try {
        //this is where you call your method object...
        oPgConnect = new PgConnect();
        //once created, you call the method to get the connection...
        connection = oPgConnect.connect();
        //after get the connection, keep with the method logic...
        if (connection != null) {
            //your logic code...
        }
    }
}

Answer 5

One way to hide the credentials would be making connect a static function returning Connection, like this:

public class PgConnect {
    public static Connection connect() throws SQLException {
    try {
        Connection res = DriverManager.getConnection("jdbc:postgresql://pgserver:5432/db", "test","test123");
        if (res != null) {
            System.out.println("Connection working");
        } else {
            System.out.println("Failed to make connection!");
        }
        return res;
    } catch (SQLException e) {
        System.out.println("Connection Failed! Check output console");
        e.printStackTrace();
        throw e;
    }
}

}

You could then use it like this:

try {
    connection = PgConnect.connect();
} catch (SQLException e) {
    System.out.println("Connection Failed! Check output console");
    e.printStackTrace();
    return;
}