Discussion in 'Spigot Plugin Development' started by Realm, Jun 8, 2017.

  1. What is so beneficial about it? What even is it? I've never used it, but I presume it's an API designed to make dealing with SQL easier? If so, how exactly does it make it easier as opposed to using regular Java?

    Sent from my iPhone using Tapatalk
  2. First you use regular Java. Second it allows connection caching.
  3. Mas


  4. I'm not sure if you know what connection pooling is, or not, but I'll presume you don't and write this anyhow. You could just be asking about HikariCP vs C3P0 or something, I'm not too sure.

    To start, let's write without HikariCP:

    Say you want to get all the user data at once, so you want to run

    Code (SQL):
    SELECT * FROM users
    If it takes 10 milliseconds to establish a connection to an SQL server, then for every query you'd like to execute you need to establish a new connection every time. You're now wasting that time (which, to be honest, is probably a lot shorter than 10ms) for every query. Either that... or you need to pass a single connection around your program, and be very careful to avoid using it for two things at once.

    Imagine implementing that! It's a sort of hot-potato with a connection instance in your program, and it's a decent amount of work to implement. The other big problem with this, is you still have one single connection to the database, so you're going to only be able to perform SQL sequentially (if you needed to perform 10 thousand separate queries, you need to send those queries, and wait for each reply, IN SEQUENCE! It'd take forever...).

    Okay, but we might be on to something with this hot potato game. Let's create a system, where there's a sort of 'pool' of connections that we do this hot-potato with. For example, if I ask for a connection, one is retrieved from a pool of open connections, given exclusively to me for my business, and then returned to the pool so long as I didn't do something stupid- like cause an error, or close it. This... this might just work.

    It'd also be cool to add some things like a pool that auto expands and contracts. So if there's a lot of querying going on, the pool grows how many connections it keeps open, and then shrinks that when it thinks I'm done.

    This is, generally speaking, what HikariCP will do for you. It's a place to store a set of open connections, and it includes mechanisms that make your life easier. For example, this is a bit of code that you might write using HikariCP:

    Code (Java):

    Connection c = pool.getConnection();
    c.executeQue... //sql stuff here
    Notice I closed the connection at the end. HikariCP actually does a not-so-hack'y hack to make close() return the connection to the pool. Now, another thing I failed to mention, is that you should have wrapped that in a try/catch block. The connection between close and try/catch is the idea of a try-with-resource block. Because hikariCP uses the method close() to return a connection to the pool, you can use this cool in-built java thing to simplify your code. Look at this:

    Code (Java):

    try (Connection c = pool.getConnection()) {
    } catch (SQLException e) {
    as opposed to
    Code (Java):

    Connection c;
    try {
        c = pool.getConnection()
    } catch (SQLException e) {
    } finally {
    Hopefully I got nothing super important wrong, and hopefully that provides a good primer on sql connection pools.
    #4 Twister915, Jun 8, 2017
    Last edited: Jun 9, 2017
    • Like Like x 4
    • Informative Informative x 4
  5. Excellent explanation and very detailed!
    • Friendly Friendly x 1
  6. Now that's a quality forum post right there. ;)
    • Friendly Friendly x 1