Hi everyone, I am using Hikari as my connection pool thing (a manager?) with MySQL database. I follo...

# server

[minucha]

Hi everyone, I am using Hikari as my connection pool thing (a manager?) with MySQL database. I followed stack overflow and some other tutorials and final code is as follows :

I created a DataSource class which configures Hikari

public class DataSource {
    private static HikariConfig config = new HikariConfig();
    private static HikariDataSource ds;

    static {
        config.setJdbcUrl("jdbc:<mysql://username>:<mailto:[e-mail]|[e-mail]>:3306/mmylxkukgpzvjvfx");
        config.setUsername("username");
        config.setPassword("password");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.addDataSourceProperty("cachePrepStmts", "true");
        ds = new HikariDataSource(config);
    }

    private DataSource() {
    }

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
}

In all tutorials I followed, they show this code or similer one but they don’t tell where and how to use the getConnection() .
And the question is when, where and how do I call the getConnecion() method?

Am I going to call it in every request I receive (in every api method defined in every single controller) ?

@GetMapping(path = "/all")
    public @ResponseBody Iterable<ExampleEntity> getAllUsers() {
        try {
            Connection connection = DataSource.getConnection();
// what am I gonna do with the connection ?
            return mExampleService.getAll();
        } catch (SQLException ex) {
            System.err.println("Exception Occurred");
        }
    }

[Jukka Siivonen]

Not answer to your question but I would suggest to use more higher level abstractions instead of using connection directly (for example Spring has JdbcTemplate which is still quite low level abstraction but does all the boring stuff for you)

[minucha]

Thanks for the reply, hmm, okay I am gonna check it out. I don’t know what JdbcTempalte is 😄. The thing while learning new technology, there has to be a roadmap or someone who can point you to the right direction telling do’s and don’ts 😄

[fitzoh]

JdbcTemplate is part of the Spring framework, you can find the javadoc for it here: https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html
and some text docs here:
https://docs.spring.io/sspring/docs/current/spring-framework-reference/data-access.html#jdbc

It provides a bunch of functions to make it easier to perform sql queries

[fitzoh]

The main constructor takes a DataSource as an argument

[fitzoh]

It needs to get a connection each time it runs a query/communicates with the DB

[fitzoh]

if you use a HikariDataSource, then it intelligently manages pooling for you, so that connections are reused instead of creating a new one each time, which is expensive

[fitzoh]

Does that help at all @minucha?

[fitzoh]

One other thing I might add...
I'm a big fan of Spring and Spring Boot, but if you're just looking to figure out how to do the data access part you might get lost with the larger spring ecosystem

[fitzoh]

You might want to check out http://jdbi.org/ which is a standalone library that functions similarly to JdbcTemplate

[fitzoh]

Alternatively, you could just go all in on spring: https://spring.io/guides/gs/relational-data-access/

[fitzoh]

So if you did your data access through a spring boot app, you wouldn't really need to configure hikaricp at all

[fitzoh]

it transparently wires all that up for you

[fitzoh]

you just give the connection information, and it gives you a JdbcTemplate with a hikari connection pool under the covers

[nicholasnet]

Even better use R2BC package now GA

[Jukka Siivonen]

Probably better to learn basics first before jumping into reactive stack

[fitzoh]

Even better use R2BC package now GA

eh... If you're just getting started figuring out all the reactive stuff sounds like a lot of overhead

[minucha]

Wow, thanks for the guidance @fitzoh.
I actually didn’t know about the concept of connection pooling 2 days ago till I came accross the limit of Heroku’s JawDB add-on (in free plan). It turns out I am allowed to make max 10 connections in the free plan. After some googling I found Hikari.

@fitzoh Andrew, I followed the links you gave me and converted my project to use JdbcTemplate. However, I couldn’t test the app, because when I uploaded the app to Heroku, it was still saying about the “10 max connection”.
I think, I will need to test it in localhost.

[minucha]

Oh, I didn’t mention it in the post… I am using Heroku and its JawsDB. In the free plan, I am allowed to make max 10 connections

[minucha]

I reached the connection limit somehow, and that’s why I started learning about connection pooling.

[minucha]

I have some questions about JdbcTemplate … I am gonna ask them later after I test couple of stuff 🙂

[Matteo Mirk]

If Heroku’s free plan has that limit, you will have to configure Hikari to create a pool of 10 connections max, then it will manage them for you everytime you ask for a connection

[minucha]

Where do I tell Hikari to create 10 connections max ? in DataSource class (it is in the original post)?

And what am I gonna do with the Connection object ?

[Matteo Mirk]

Oh ok, you’re still totally new to JDBC I suppose? 🙂 So, the HikariConfig class has this property you can set setMaximumPoolSize(int maxPoolSize) , that should do the trick. Interesting fact is that if you look at the source (https://github.com/openbouquet/HikariCP/blob/master/src/main/java/com/zaxxer/hikari/HikariConfig.java#L110) it has a default value of 10… weird, it should work out of the box for your case! 🤔 Anyway it’s better to set it explicitly.
When you gey a Connection object, from then on it’s all basic JDBC handling: https://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html

[Matteo Mirk]

With reference to your controller snippet, you probably don’y want to use a jdbc connection inside the controller, but rather inside your ExampleService

[minucha]

Yeah, I am totally new to backend let alone JDBC 😄 It has been like three weeks since I started. Okay, checking those links… thanks.

[Matteo Mirk]

Don’t worry we all started somewhere. Good luck for your learning path!

[minucha]

Thanks a lot 🙂

[nkiesel]

to answer the original Q: yes, you have to call getConnection in every request (which will return a connection from the pool managed by Hikari). And you have to close it again!!! So do yourself a big favor and use "try expressions":

try (Connection connection = DataSource.getConnection()) {
    return mExampleService.getAll(connection);
}

[minucha]

Oh, I am doing, and looks awfull 😄

public void addBook(ExampleBook book) {
        String name = book.getName();
        String author = book.getAuthor();
        String description = book.getDescription();

        String sqlQuery = "insert into books (`name`,`author`,`description`)" +
                " values('" + name + "', '" + author + "', '" + description + "');";
        Statement statement = null;
        try {
            Connection connection = DataSource.getConnection();
            statement = connection.createStatement();
            int rs = statement.executeUpdate(sqlQuery);
            L.e("hop", "int : " + rs);
        } catch (SQLException ex) {
            L.e("Exception occurred");
            ex.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

There are two problems I think:
1. Two try catches
2. SQL statement
(3) too long and jibberish method

[Jukka Siivonen]

Biggest problem is that you allow SQL injection

[minucha]

Can’t we make it like this:

@Autowired
private ExampleBookRepo mBookRepo;

public void addBook2(ExampleBook book) {
        mBookRepo.save(book);
}

here ExampleBookRepo is an interface that is extended from CrudRepository and it was awesome working with it. So clean and readable.

[Jukka Siivonen]

For that you need to look into Spring Data JPA

[minucha]

SQL injection

looked it up, wow, never thought about it.

For that you need to look into Spring Data JPA

Can’t we use both HikariCP with its getConnection() and Spring Data JPA ?

[nkiesel]

you should move the `int rs = statement.executeUpdate(sqlQuery);`from the try expression into the block. You only include things in the try expression for which you need to call close on. SQL injection is prevented by using prepared statements. JDBC is pretty low-level (look at Spring template or jdbi for nicer APIs). But a "pure JDBC" would look like this (note that with try expressions you no longer need the finally block):

public void addBook(ExampleBook book) {
        String sqlQuery = "insert into books (`name`,`author`,`description`)" +
                " values(?, ?, ?);";
        try (
            Connection connection = DataSource.getConnection();
            PreparedStatement statement = connection.prepareStatement(sqlQuery)) {
            statement.setString(1, book.getName());
            statement.setString(2, book.getAuthor());
            statement.setString(3, book.getDescription());
            int rs = statement.executeUpdate();
            L.e("hop", "int : " + rs);
        } catch (SQLException ex) {
            L.e("Exception occurred");
            ex.printStackTrace();
        }
    }

[minucha]

@nkiesel wow, this did the trick. I was doing two try statements, and it was super ugly. Now it is much much better.
And the statement.setString(1, book.getName()); woosh, mindblowing. Thank you, I learned a great deal of staff today.

[Matteo Mirk]

All JDBC stuff, theory and practice is very good but… guys, why are you writing and discussing Java code on kotlinlang space? 🤔

[minucha]

@Matteo Mirk Haha, yeah… I couldn’t find any Java slack community that discusses server side programming. 😅 and I know that all Kotliners are former Java’ers 😄

[Matteo Mirk]

Hehe yeah I can see your point, yes there doesn’t seem to be any official one. This could be a nice Java community: https://www.javaspecialists.eu/slack/

[nkiesel]

Fair enough, so here is the Kotlin version:

fun addBook(ds: DataSource, book: ExampleBook): Int {
        val sqlQuery = "insert into books (name, author, description) values(?, ?, ?)"
        ds.connection.use { con ->
            con.prepareStatement(sqlQuery).use { statement ->
                statement.setString(1, book.name)
                statement.setString(2, book.author)
                statement.setString(3, book.description)
                return statement.executeUpdate()
            }
        }
    }