CODEWITHSUNDEEP
javamysqldatabasejdbcconnection
user962206
user962206

Reputation: 16117

How to pass a connection code?

I am working with a JDBC, How do I actually pass my Connection code to other objects?? so that it wouldn't be so much of a hassle to keep on coding it and do I need other objects to create if I need to close a database connection?? Here's my code

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import javax.sql.DataSource;

public class DisplayUsers {

    ResultSet resultSet = null;
    Statement statement = null;
    Scanner input = new Scanner(System.in);
    DataSource ds = null;



    public void showAll() {

        System.out.println("Search User: ");
        String user = input.nextLine();

        String query = "Select * from user";
        try {
            resultSet = statement.executeQuery(query);
            ResultSetMetaData metadata = resultSet.getMetaData();
            int columns = metadata.getColumnCount();
            while(resultSet.next()){
                for(int i = 1 ; i<=columns;i++){
                    System.out.printf("%-8s\t",resultSet.getObject(i));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Here's my Connector code

import java.sql.*;

public class Jdbc {

        public void dbConn(){
            final String url = "jdbc:mysql://localhost:3306/payroll";
            Connection conn = null;
            try{
                 Class.forName("com.mysql.jdbc.Driver");
                 conn = DriverManager.getConnection(url,"root","123192");  
            }catch(Exception e){
                e.printStackTrace();
            }
        }
}

Upvotes: 2

Views: 1229

Answers (2)

korifey
korifey

Reputation: 3509

The best way is to use connection pool. If you are coding Java EE application, you can make the pool in configuration (In glassfish you can use admin console). Server will bind this pool (via DataSource) to a JNDI name you specified (say "jndi/mydb").

So getting a connection in code is very simple:

InitialContext ctx = new InitialContext();
//Application Server will automatically bind new InitialContext() call to its 
//own context (where your DataSource is located).
DataSource ds = ctx.lookup("jndi/mydb")

You can pass this DataSource everywhere and getConnection as simple as ds.getConnection(), never closing it (because it's pooled and reused).

P.S. Use of DriverManager as a way of getting connection is appropriate in small console application with no requirements to performance and scalability.

Upvotes: 1

seand
seand

Reputation: 5286

You normally get a connection as needed, do your SQL, and then close it. If you're using a connection pool you usually request it from the pool and return to the pool after you're done.

Most of the time Connection objects are short term, only used for a single transaction. Open connections tie up DB resources and you don't want to keep these open more than needed. There's nothing stopping you from passing these around other objects but be careful...

Upvotes: 2

Related Questions