Mysql in Java. How to implement efficient asynchronous queries?

Question

I'm creating a game with a server and multiple clients. I'm using Kryonet for networking and each connection has it's own listener where it receives packets.

There listeners are called on a background Kryonet thread and I can't block them cause it would affect all of the users.

I have created my database, configured a ConnectionPool in a synchronized Singleton class:

private static final BasicDataSource dataSource = new BasicDataSource();

static {
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://111.111.111.111/db");
    dataSource.setUsername("server");
    dataSource.setPassword("serverpass");
}

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

and now I need to execute some queries. Here comes my issue. As we know, query could take long to return a result so it's totally unacceptable to execute it on a 'Kryonet' thread (when packet is received). For example, when user sends his 'RegistrationPacket' I need to make a query to the database and return him a result within a packet. When I receive the packet, I need to put it in background and from there - send result to the client.

Here comes my question: How to handle making database queries in background using Java?

Should I use Executors? Threads? As I know opening a Thread for each connection is a bad idea, (cause 200+ workers).equals(disaster). If someone could help me I would be grateful! :)

If you need to return the result to the client then you need to block the client. Unless you have server push; do you have server push? — Boris the Spider
– Boris the Spider, Commented Oct 8, 2016 at 18:20
Client and server are two separate applications. Client sends a network packet with his data and server needs to query this data to the database. But he can't on a normal thread, cause this would freeze the whole networking :) After the query is done in background, I need to send the result. — user6367252
– user6367252, Commented Oct 8, 2016 at 18:45

oshai · Accepted Answer · 2018-09-06 06:27:05Z

1

With jasync-sql You can do something like this:

// Connect to DB
Connection connection = new MySQLConnection(
  new Configuration(
    "server",
    "111.111.111.111",
    3306,
    "serverpass",
    "db"
  )
);
CompletableFuture<?> connectFuture = connection.connect()
// Wait for connection to be ready   
// ...    
// Execute query
CompletableFuture<QueryResult> future = connection.sendPreparedStatement("select 0");
// Close the connection
connection.disconnect().get()

See more details on the lib itself. It is an async MySQL driver based on Netty.

answered Sep 6, 2018 at 6:27

oshai

15.5k29 gold badges95 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql in Java. How to implement efficient asynchronous queries?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related