Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JDBC Connection using different files

Tags:

java

methods

jdbc

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.

like image 540
thclpr Avatar asked Jun 20 '12 17:06

thclpr


2 Answers

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;
}
like image 91
Sergey Kalinichenko Avatar answered Oct 05 '22 11:10

Sergey Kalinichenko


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.

like image 36
Simon Dorociak Avatar answered Oct 05 '22 12:10

Simon Dorociak