[API] DataPub | Sane SQL Management

Plugins Plugin Development
1

#DataPub

GitLab/GitHub | Build | Repo | Docs

Hello! My name is @FerusGrim, and I’ve recently developed DataPub, what I hope to be one of the better libraries (currently, only, AFAIK) for managing SQL in a variety of formats.

Currently Supported:

  • SQLite (Local)
  • MySQL (Remote)
  • Postgre (Remote)

Features:

  • API is Implementation Agnostic.
  • Connections automatically close.
  • No more Exception handling.
  • FauxStatement

Planned Features:

  • DataPub4Sponge
  • DataPub4Bukkit
  • More Database support

Now, I’m sure you’re wondering. What is FauxStatement? Well, keeping security in mind, I wanted to make sure that PreparedStatement was always being employed. Why? Namely, during compilation of the Statement, a PreparedStatement will automatically escape any illegal characters. This is automatic protection against potential injection attacks, on a database.

So, where does FauxStatement come in? Well, it contains a method which is used in the API to prepare a statement, while allowing you to manage a query quite easily. Here’s an example:


private final FauxStatement fauxBanPlayer = new FauxStatement("INSERT INTO banned (uuid, reason, banner) VALUES(?, ?, ?)");

private void banPlayer(UUID uuid, String reason, String whoDunIt) {
    // Some stuff to handle this server-side.
    fauxBanPlayer.setReplacements(uuid, reason, whoDunIt);
    this.database.updateDatabase(fauxBanPlayer);
}

That’s it. It’s literally that simple.

Now, maybe you’re wondering how you retrieve data? Now, remember: I automatically close all connections. This includes Statement and ResultSet. So… how do you read the results of a query, if I’m closing the ResultSet on you? Easy. A map. Too simple, you say? It’s really not.

private final FauxStatement fauxGetBanned = new FauxStatement("SELECT * FROM banned");

private List<UUID> getBannedPlayers() {
    final List<UUID> bannedPlayers = new ArrayList<UUID>();
    final List<Map<String, Object> results = this.database.getResultSet(fauxGetBanned, "uuid");

    for (Map<String, Object> result : results) {
        bannedPlayers.add(UUID.fromString((String) result.get("uuid"));
    }

    return bannedPlayers;
}

So, essentially, the result of Database#getResultSet() acts exactly like a normal ResultSet, but allows me to ensure that the actual ResultSet is closed.

Now, I really can’t go over everything in this topic. Links to more information are at the top. Feel free to check them out.

Please, tell me what you think, if you might use this, and how I might improve it.

EDIT: How to work with DataPub.


<repository>
    <id>FerusGrim-Public</id>
    <url>https://repo.ferusgrim.me/content/groups/public/</url>
    <releases><enabled>true</enabled></releases>
    <snapshots><enabled>true</enabled></snapshots>
</repository>

[...]

<dependency>
    <groupId>me.ferusgrim.datapub</groupId>
    <artifactId>datapub-api</artifactId>
    <scope>provided</scope>
</dependency>
3 Likes
2

Didn’t @MrMysteri0us make something like this?

3

I suppose it’s possible. Could you link me to his project?

4
5

I suppose he did. However, it doesn’t appear that it’s maintained any longing.

I also notice some immediate improvements of my API vs his. Feel free to look at them for yourself.

6

I added Maven details at the bottom of the post. :smile:

7

casual relief that i dont have to create a []ing sql data manager class now

3 Likes
8

Neato, you released it :smiley:

1 Like
9

Yeah. I was taking a few suggestions into account from a couple people that I talked to, and ironed a few more things out. :smile:

1 Like
10

Just looking at the code samples in the first post it looks as if you were trying to mimic some functionality of Spring’s JDBC template.

For my personal usage, this is the issue: If I need some custom JDBC simplifications, I rather use the JDBC template (or Apache Commons DB Utils if a small footprint is required) that is whidly adopted and well tested than a custom solution. Or I directly use JOOQ to replace queries with perfectly type safe Java constructs.

11

Interestingly enough, I’ve never looked at Sping’s JDBC template. :smile:

Indeed, one of the features of the API is ease-of-access to a database. However, if you wish to forgo that, it provides another extremely useful tool. Configuration. I maybe should have covered this in the OP, but I really didn’t want to create a giant mess of code examples.

Rather than every plugin independently collecting login information, a server owner who uses DataPub and compatible plugins could simply do the configuration once.

Any developer wishing to access the login information for, say, MySQL, could easily:

this.host = (String) Setting.MYSQL_HOST.get().get();
this.port = (Integer) Setting.MYSQL_PORT.get().get();
this.username = (String) Setting.MYSQL_USERNAME().get().get();
this.password = (String) Setting.MYSQL_PASSWORD().get().get();

In fact, the above is exactly how I get configuration for the databases. Observe:

    public MySQL(String database) {
        super(DatabaseType.MYSQL, database);

        Optional<Object> host = Setting.MYSQL_HOST.get();
        Optional<Object> port = Setting.MYSQL_PORT.get();
        Optional<Object> username = Setting.MYSQL_USERNAME.get();
        Optional<Object> password = Setting.MYSQL_PASSWORD.get();

        if (!host.isPresent()
                || !port.isPresent()
                || !username.isPresent()
                || !password.isPresent()) {
            DataPub.warning("Host, Port, or Username for MySQL connections was not set in your configuration!");
            DataPub.warning("This is likely to mean a failure when attempting to connect to this database.");
        }

        this.host = host.isPresent() ? (String) host.get() : "localhost";
        this.port = port.isPresent() ? (Integer) port.get() : 3306;
        this.username = username.isPresent() ? (String) username.get() : "";
        this.password = password.isPresent() ? (String) password.get() : "";
    }
12

That is indeed a useful feature, but Sponge already provides such a functionality (although it is, as of writing, not implemented), see here.

13

Does this hooks in to the sponges SQL service?

https://github.com/SpongePowered/SpongeAPI/blob/master/src/main/java/org/spongepowered/api/service/sql/SqlService.java

14

doesn’t look like it does when looking at the remote SQL implementations.
i’ll put up an issue for that

EDIT:
actually, this isn’t necessarily supposed to be for sponge only, isn’t it?
he did say it was implementation-agnostic, so does it have to?

also, this -is- just an API as of now, the planned features say he plans to make the version specifically for sponge. (DataPub4Sponge)

15

DataPub4Sponge will certainly employ SQL Pooling (From SqlService).

1 Like