SQL

Bundled SQL library

SimplixCore offers you to use a customizable library for SQL operations. It's extensible and offers support for connection pools like HikariCP. This library is inspired by xkuyax and is not working asynchronously. Please run queries and updates asynchronously yourself.

Dependencies required

At first you need to add the following dependency to your pom file.

<dependency>  
  <groupId>dev.simplix.core</groupId>  
  <artifactId>simplixcore-database-sql</artifactId>  
  <version>1.0-SNAPSHOT</version>  
  <scope>provided</scope>  
</dependency>

On Spigot & BungeeCord you don't need to shade this into your jar or load this as a library. It's bundled within the SimplixCore binary. Otherwise for standalone applications since there is no SimplixCore binary to load.

Getting started

At first, you need to obtain an instance of SqlDatabaseConnection. You create such an instance using the following static factory method:

SqlDatabaseConnection.hikari(host, user, password, port, database);

This will create a pooled connection to the database using HikariCP. If you want to customize some options you can instantiate the SqlDatabaseConnection using its constructor:

new SqlDatabaseConnection(dataSource, host, user, password, port, database, connectionHandler);

Where dataSource needs to be an instance of javax.sql.DataSource and connectionHandler an instance of dev.simplix.core.database.sql.handlers.SqlConnectionHandler.

ResultSetTransformer

The library provides an easy way to convert a ResultSet to any object you want to have. For example storing an instance of ResultSetTransformer<T> in a field:

private static final ResultSetTransformer<Car> CAR_TRANSFORMER = resultSet -> new Car.CarBuilder()
  .model(resultSet.getString("model"))  
  .vendor(resultSet.getString("vendor"))  
  .horsePower(resultSet.getInt("hp"))  
  .build();

This transformer uses a given ResultSet and returns a new Object of type Car. You can use this transformer for every query where you want to get a Car object:

public Car carById(int id) {  
  return databaseConnection.query("SELECT * FROM cars WHERE id=?", ps -> {  
    ps.setInt(1, id);  
  }, CAR_TRANSFORMER);  
}

How to use

The SqlDatabaseConnection class provides multiple methods to interact with the database.

prepare()

The prepare-method is the easiest of them. It just executes a statement and does nothing return.

databaseConnection.prepare("UPDATE example SET name=? WHERE uuid=?", ps -> {  
  ps.setString(1, name);  
  ps.setString(2, uuid.toString());  
});

query()

The query method is used to retrieve data from the database.

Map<String, Object> data = databaseConnection.query("SELECT * FROM example WHERE name=?",   
 ps -> ps.setString(1, name), resultSet -> {  
  Map<String, Object> out = new HashMap<>();  
  out.put("uniqueId", resultSet.getString("uuid"));  
  out.put("lastJoin", resultSet.getTimestamp("lastJoin"));  
  out.put("firstJoin", resultSet.getTimestamp("firstJoin"));  
  return out;  
});

As you can see, this method returns the object you will return using the ResultSetTransformer. You can do this with every type.

queryList()

Sometimes you want to query multiple objects. The method queryList will automatically return a list of objects transformed using the ResultSetTransformer. For example getting all cars from a specific vendor:

public List<Car> carsByVendor(String vendor) {  
  return databaseConnection.queryList("SELECT * FROM cars WHERE vendor=?", ps -> {  
    ps.setString(1, vendor);  
  }, CAR_TRANSFORMER);  
}

The used CAR_TRANSFORMER is the same as showed above in the section about ResultSetTransformers. Notice: Your ResultSetTransformer should never return a Collection! Collecting is done by the SQL library using the queryList() method.

queryCollection()

Behaves the same as queryList() but additionally needs a Supplier<T extends Collection> for creating a collection. This method allows you to use Sets for collecting instead of Lists. Example usage with the example above:

public Set<Car> carsByVendor(String vendor) {  
  return databaseConnection.queryCollection("SELECT * FROM cars WHERE vendor=?", ps -> {  
    ps.setString(1, vendor);  
  }, CAR_TRANSFORMER, HashSet::new);  
}

keyInsert()

A conventional table should always have a primary key. When you are using primary keys with automatic values, you can return the generated value of that primary key when inserting a new tuple into the table. For example:

public int insertCar(Car car) {  
  return databaseConnection.keyInsert("INSERT INTO cars (vendor,model,hp) VALUES (?,?,?)", ps -> {  
    ps.setString(1, car.vendor());  
    ps.setString(2, car.model());  
    ps.setInt(3, car.horsePower());  
  });
}

This will return the id of the inserted car. Please be aware that only integer values are supported.