I am dealing with high traffic in my Spring Boot project and my goal is serving clients as much fast as possible. In this case, I have more than 500 requests per second. In each rest endpoint call, I should connect my schema and gather multiple information from multiple tables. To be able to do that, should I create new connection for each eendpoint call or create & close before each db query?

I wrote a JDBC connection class but I am not sure that it is a good way. Maybe you can give me some opinion.

JDBC Connection Class

@PropertySource({"classpath:application.properties"})

@Configuration

public class FraudJDBConfiguration {

    private final Logger LOGGER = LogManager.getLogger(FraudJDBConfiguration.class);



    private final Environment env;



    @Autowired

    public FraudJDBConfiguration(Environment env) {

        this.env = env;

    }



    @Bean

    public Connection getFraudConnection() {

        // Step 1: Loading or

        // registering Oracle JDBC driver class

        String connectionClass = env.getProperty("fraud.db.driver-class-name");

        try {

            Class.forName(connectionClass);

        } catch (ClassNotFoundException cnfex) {

            LOGGER.error(cnfex.getMessage());

            throw new RuntimeException("JDBC driver class'ı bulunamadı");

        }



        // Step 2: Opening database connection

        try {

            String environmentType = env.getProperty("environment");

            if (environmentType == null) {

                LOGGER.error("environment Tip Hatası (TEST - UAT - LIVE)");

                throw new RuntimeException("environment Tip Hatası (TEST - UAT - LIVE)");

            } else {

                String connectionString = null;

                String username = null;

                String password = null;

                switch (environmentType.toLowerCase()) {

                    case "dev":

                        connectionString = env.getProperty(/*someurl*/);

                        username = env.getProperty(/*someusername*/);

                        password = env.getProperty(/*somepassword*/);

                        break;

                    case "tst":

                        connectionString = env.getProperty(/*someurl*/);

                        username = env.getProperty(/*someusername*/);

                        password = env.getProperty(/*somepassword*/);

                        break;

                    case "liv":

                        connectionString = env.getProperty(/*someurl*/);

                        username = env.getProperty(/*someusername*/);

                        password = env.getProperty(/*somepassword*/);

                        break;

                    case "uat":

                        connectionString = env.getProperty(/*someurl*/);

                        username = env.getProperty(/*someusername*/);

                        password = env.getProperty(/*somepassword*/);

                        break;

                }

                // Step 2.A: Create and

                // get connection using DriverManager class

                if (connectionString == null) {

                    LOGGER.error("fraud şeması için connection string bulunamadı");

                    throw new RuntimeException("fraud şeması için connection string bulunamadı");

                }

                return DriverManager.getConnection(connectionString, username, password);

            }

        } catch (SQLException e) {

            LOGGER.error(e.getMessage());

        }

        return null;

    }

}

DAO

@Component

public interface FraudCommTransactionsDao {

    Long count();

}

DAO IMPL

@Service

public class FraudCommTransactionsDaoImpl implements FraudCommTransactionsDao {

    private final FraudJDBConfiguration fraudJDBConfiguration;



    @Autowired

    public FraudCommTransactionsDaoImpl(FraudJDBConfiguration fraudJDBConfiguration) {

        this.fraudJDBConfiguration = fraudJDBConfiguration;

    }



    @Override

    public Long count() {

        try(Connection connection = fraudJDBConfiguration.getFraudConnection()) {

            Statement stmt = connection.createStatement();

            ResultSet rs = stmt.executeQuery(/*some query*/);



            if (rs.next()) {

                return rs.getLong("transaction_id");

            } else {

                return 0L;

            }

        } catch (SQLException ex) {

            ex.printStackTrace();

        }

        return null;

    }

}

No, establishing a new physical connection to a database server is costly. It involves multiple steps: user authorization, establishing session defaults, allocating memory on both client and server, etc. This overhead should not be added to every single request.

It's a common practice to create a connection pool to share the physical connections between application threads. This introduces a concept of logical connections e.g. a Connection object created with DriverManager.getConnection() is a physical connection while DataSource.getConnection() returns a logical connection which is a proxy.

There are multiple database connection pooling libraries for Java that you can use e.g. HikariCP. Don't write your own, this is not simple.

Get fast data and deliver to client could be possible using the simplest way of using application.properties file. You may use this to get database connection to your datasource.