How to connect java database connection to many jFrames?

Question

I have created a class called DBConnection to connect the sql database. I also have a jFrame called AdminLogin. What I want to know is, how to pass the database connection to the AdminLogin class without having to type the whole database connection codes.

Here is my DBConnection class

package ltdasystem;

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



public class DBConnection {

    private Connection conn = null;
    private String query = null;
    private ResultSet rs;
    private final String url=("jdbc:sqlserver://localhost:1433;databaseName=LeisureToursDatabase");
    private final String usrName = "sa";
    private final String pwd= "p@ssword13";

    public DBConnection() {
        try{
            conn = DriverManager.getConnection(url, usrName, pwd);
            query = "SELECT * from UserATable";
            PreparedStatement stm = conn.prepareStatement(query);
            rs = stm.executeQuery();
            while (rs.next()){
                String userName = rs.getString("Username");
                String password = rs.getString("Password");

                System.out.println("Username : " + userName + "\n" + "Password : " + password); 

            }
            conn.close();

        }catch (SQLException e){

          System.out.println(e.getMessage());

        }

    }

  }

Here is my AdminLogin. The way I have done it now works fine but I want to do it without having to type all the database connection codes.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.JOptionPane;


/**
 *
 * @author Yasitha
 */
public class AdminLogin extends javax.swing.JFrame {

     Connection conn = null;
     String query = null;
     ResultSet rs;
    private final String url=("jdbc:sqlserver://localhost:1433;databaseName=LeisureToursDatabase");
    private final String usrName = "sa";
    private final String pwd= "p@ssword13";


    /**
     * Creates new form AdminLogin
     */
    public AdminLogin() {
        initComponents();
    }



    private void LoginButtonActionPerformed(java.awt.event.ActionEvent evt) {                                            
        //DBConnection db = new DBConnection();

       try{

             conn = DriverManager.getConnection(url, usrName, pwd);
             query = "SELECT * FROM UserATable WHERE Username = '" + userName.getText()
                    + "' AND Password = " + password.getText();


          PreparedStatement stm = conn.prepareStatement(query);
            rs = stm.executeQuery();
            if(rs.next()){
                dispose();
                String x = rs.getString("Username");
               AdminMenu admenu = new AdminMenu();
               admenu.setVisible(true);
               admenu.generate(x);

       }

       }catch (SQLException ex) {

           JOptionPane.showMessageDialog(null, ex);
           //return null;

        } finally {


        }   
    }                                           


        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new AdminLogin().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify                     
    private javax.swing.JButton BackButton;
    private javax.swing.JButton LoginButton;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JPasswordField password;
    private javax.swing.JTextField userName;
    // End of variables declaration                   
}

Answer 1

Just want to add some change points here... isValidUser can be a static method since there is no state involved. Hence "UserQueryManager userQuertManager = new UserQueryManager()" new object creation on every button click will go...

Also dbconnection can be injected into UserQueryManager as a interface member. Also to the prime questioner..if you look at these classes with a MVC kind of perspective then the break up of code would be nicer if you look at the

AdminLogin which is a frame as a View component ...hence it is not very clean to see DBConnection or QueryManager objects as member variables of the this component.

Answer 2

Change your design. DBConnection should be a factory/manger, which wraps the core functionality and management of the database connection and querying (or at least forms the bases of)

public interface DBConnection {

    public Connection getConnection() throws SQLException;

}

Then you could create query management classes or methods to handle the actually heavy lifting....

public class UserQueryManager {

    public boolean isValidUser(DBConnection dBConnection, String name, String password) throws SQLException {

        boolean isValidUser = false;
        try (Connection con = dBConnection.getConnection()) {

            try (PreparedStatement stmt = con.prepareStatement("select count(*) from UserATable where name=? and password=?")) {

                try (ResultSet rs = stmt.executeQuery()) {

                    if (rs.next()) {

                        int count = rs.getInt(1);
                        isValidUser = count > 0; // Maybe count == 1 would be more valid

                    }

                }

            }

        }

        return isValidUser;

    }

}

Now, we need a implementation of the DBConnection interface, something like...

public class DefaultDBConnection implements DBConnection {

    private final String url;
    private final String userName;
    private final String password;

    public DefaultDBConnection(String url, String userName, String password) {
        this.url = url;
        this.userName = userName;
        this.password = password;
    }

    @Override
    public Connection getConnection() throws SQLException {

        return DriverManager.getConnection(url, userName, password);

    }
}

Then you would create an instance of the DBConnection...

DefaultDBConnection dBConnection = new DefaultDBConnection("jdbc:sqlserver://localhost:1433;databaseName=LeisureToursDatabase", "sa", "p@ssword13");

And pass it to your frame (or anybody else that might like to use it)

AdminLogin adminLogin = new AdminLogin(dBConnection);

You will then need to modify the AdminLogin to accept the parameter via the constructor...

public class AdminLogin extends javax.swing.JFrame {

    private DBConnection dBConnection;

    /**
     * Creates new form AdminLogin
     */
    public AdminLogin(DBConnection dBConnection) {
        this.dBConnection = dBConnection;
        initComponents();
    }

And the LoginButtonActionPerformed to setup the UserQueryManager and call the isValidUser...

private void LoginButtonActionPerformed(java.awt.event.ActionEvent evt) {
    try {
        UserQueryManager userQuertManager = new UserQueryManager();
        if (userQuertManager.isValidUser(dBConnection, userName.getText(), password.getText())) {
            dispose();
            AdminMenu admenu = new AdminMenu();
            admenu.setVisible(true);
            admenu.generate(userName.getText());
        }
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(null, ex);
        //return null;
    }
}

Beware, you really shouldn't be passing passwords around as Strings, but should be using char[] arrays.

Also, you might consider having a look at The Use of Multiple JFrames, Good/Bad Practice? and How to Use CardLayout