CODEWITHSUNDEEP
java
EliOklesh
EliOklesh

Reputation: 31

Best way to access a database

I'm relatively new to Java. I'm tinkering with a small program which uses a JFrame with a few fields to query and insert into a database.

I currently have a class which does nothing but creates the database connection. From there, I have other classes which perform the SELECT or INSERT database functions. Each of these starts by creating a new object for the database connection class. The connection does get closed when finished.

Being new to this, I just wanted to see if this was the best way to access the database, ie: creating and closing a brand new connection each time a button is pressed. It performs the task needed, but if there's a more efficient or sensible way to do this I'd love to know.

I can provide whatever code you may need to see. Thanks!

Upvotes: 2

Views: 893

Answers (2)

tzima
tzima

Reputation: 1295

You can create a connection once and then use it how many times you want. There are two standard ways to do it: singleton and dependency injection. Since singleton is usually not a good solution for multiple reasons, I'd stick with a dependency injection.

Here we go.

  • Database class (pseudo-code):

    public class Database implements Closeable {
    private final DBConnection connection;

    public Database(String url, String db, String user, String password) {
        // TODO: establish connection
    }

    // TOOD implement methods for working with DB, for example:
    public void add(String foo, String bar) {
        // TODO
    }

    @Override
    public void close() throws IOException {
        connection.close();
    }
}

  • GUI working with database:

    public class GUI extends JPanel {
    private Database db;

    public GUI(Database db) {
        this.db = db;
    }

    // TODO implement GUI, use `db` to access the database 
}

  • Initialize the application:

    public static void main(String[] args) {
    Database db = new Database(...);
    GUI gui = new GUI(db);

    // TODO: create JFrame, add GUI to it
}

This might be used for most of the cases. For more complicated or long-running software, where database connection is not used all the time (or you need more of them), it might be better to actually close connection when it's not used etc. For such case, use connection pool as shown by Denis Borovikov: https://stackoverflow.com/a/29395796/2709026

Upvotes: 1

Denis Borovikov
Denis Borovikov

Reputation: 747

Usual way to access database from java is connection pool. I recommend you this one: http://jolbox.com/

In you database interaction code you simply creates connection from pool (actually it borrows connection from the pool) and after requests simply close connection (actually it returns connection to the pool). 

public class DAO {
    private final DataSource ds;

    public void foo(...) {
        Connection connection = ds.getConnection();
        try {
            // do work there
        } finally {
            connection.close();
        }
    }
}


public class MainPanel extends JPanel {
    private final DAO dao;

    public MainPanel(DAO dao) {
        this.dao = dao;
    }

    // ...
}


public class Main {
    public static void main(String[] args) {
        Class.forName("org.hsqldb.jdbcDriver");
        BoneCPDataSource ds = new BoneCPDataSource();
        ds.setJdbcUrl("jdbc:hsqldb:mem:test");
        ds.setUsername("sa");
        ds.setPassword("");

        DAO dao = new DAO(ds);
        MainPanel mainPanel = new MainPanel(dao);
        // ...
    }
}

Upvotes: 1

Related Questions