How to set-up a client-server program and a database together in Java?

Question

I need to create a program that has a client, server, and a database The client needs to input data into the database or query it via the server, I am using MySQL and JDBC to connect MySQL to my java code. I was wondering what the ideal set-up is.

For example, I am connecting to my database using the following code:

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

public class test2 {

    public static void main(String[] args) {
        
        String url = "jdbc:mysql://localhost:3306/CovidPreventation";
        String username = "test";
        String password = "test";

        System.out.println("Connecting database...");

        try (Connection connection = DriverManager.getConnection(url, username, password)) {
            System.out.println("Database connected!");
        } catch (SQLException e) {
            throw new IllegalStateException("Cannot connect the database!", e);
        }
    
    }

}

I was wondering where this should really go, shall I have this in the server class or in a separate class that is connected to the server. It is the first time I do such a thing and I want my practices to be good so help is appreciated.

Answer 1

DataSource

The SQL package defines the DataSource interface. An object of that type is where you keep your database connection info such as database server address, database user name, password, and so on.

To quote the documentation:

A factory for connections to the physical data source that this DataSource object represents. An alternative to the DriverManager facility, a DataSource object is the preferred means of getting a connection.

Choose an implementation of that interface. A JDBC driver usually comes with one or more implementations specific to that particular database. You need an implementation specific to that database because various databases have various features and settings.

Return an object of the more general type, the interface, rather than the more specific type, the concrete implementation. Doing so gives you the flexibility of swapping out the concrete class for another without breaking any calling code.

    private DataSource configureDataSource ( )
    {
        System.out.println( "INFO - `configureDataSource` method. " + Instant.now() );

        com.mysql.cj.jdbc.MysqlDataSource dataSource = Objects.requireNonNull( new com.mysql.cj.jdbc.MysqlDataSource() );  // Implementation of `DataSource`.
        dataSource.setServerName( "db-mysql-lon3-433-do-user-89673-1.x.db.ondigitalocean.com" );
        dataSource.setPortNumber( 24_090 );
        dataSource.setDatabaseName( "defaultdb" );
        dataSource.setUser( "scott" );
        dataSource.setPassword( "tiger" );
        return dataSource;
    }

Retain that DataSource object in your app. When needing a database connection, call DataSource#getConnection.

        String sql = "SELECT name_ from  user_ ; " ;
        try (
                Connection conn = myDataSource.getConnection() ;
                Statement statement = conn.createStatement() ;
                ResultSet resultSet = statement.executeQuery( sql ) ;
        )
        {
            while ( resultSet.next() )
            {
                String userName = resultSet.getString( "name_" );
                System.out.println( username ) ;
            }
        }
        catch ( SQLException e )
        {
            e.printStackTrace();
        }

Be sure to close your open resources such as Connection, Statement, PreparedStatement, and ResultSet. Generally best to use the try-with-resources syntax to automatically close your resources.

Notice a DataSource is never “opened”, so we never need close it. A DataSource merely holds the bits of info needed to ask the database server for a connection. The DataSource object itself is not a resource.

JNDI

Eventually you may want to use JNDI to obtain a DataSource object. This enables you to externalize the database configuration through a Jakarta EE server, LDAP server, etc. rather than hard-code the username, password, etc.

Under this approach, when your database changes its address or passwords, you can update the external configuration without needing to recompile your code and redeploy your app.

To quote the documentation:

An object that implements the DataSource interface will typically be registered with a naming service based on the Java Naming and Directory (JNDI) API.

For more info

See also:

• Implementation of JDBC DataSource in MySQL Connector/J 8
• Tutorial by Oracle, free of cost
• Why do we use a DataSource instead of a DriverManager?

Answer 2

I guess you are asking about the design/architecture of the application. If that is the case then you should follow MVC architectural pattern.

** What is MVC ?**

MVC has three main components:

• Model
• View
• Controller

each of them has specific responsibilities.

Model
Model is responsible or maintaining data. It is actually connected to DB. So any data manipulation i.e. ADD/Update/Delete/Insert you want to do is done in model. Basically Models only talks with Controller and gives the needed data to controller. i.e. Model never communicates with Data directly.

View
Data representation i.e. UI (Html/CSS part) is done by View. Controller gives data to View which will be show to end user. i.e. View only speaks to controller.

Controller
Controller enables interaction between views and model. It does not have to worry about business logic. It will tell the model what should be done. After receiving Data from the Model it sends it to View.

Answer 3

all you need a jdbc driver, you can get it here: https://mvnrepository.com/artifact/mysql/mysql-connector-java

and add jar file to your ide

after you need to call it at your code like this:

public static Connection getConnection(String dbURL, String userName, 
            String password) {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(dbURL, userName, password);
            System.out.println("connect successfully!");
        } catch (Exception ex) {
            System.out.println("connect failure!");
            ex.printStackTrace();
        }
        return conn;
    }