Detect if a mysql database column in jdbc has changed

[hybrid narwhal]

hi, this is a very basic question, but i want to know what's the best way to detect if a String has changed. Basically i want to check if a value in a column has changed in my database and execute something if it did. what is the best and most memory friendly way of doing that?

[torn lodge]

<@&987246584574140416> please have a look, thanks.

[torn lodge]

While you are waiting for getting help, here are some tips to improve your experience:

Code is much easier to read if posted with syntax highlighting and proper formatting.

If nobody is calling back, that usually means that your question was not well asked and hence nobody feels confident enough answering. Try to use your time to elaborate, provide details, context, more code, examples and maybe some screenshots. With enough info, someone knows the answer for sure.

Don't forget to close your thread using the command </help-thread close:1027500463647621170> when your question has been answered, thanks.

[proper trout]

depends on ur database

strings itself, as far as java is concerned, cannot change

strings are immutable

so ur looking at some database specific way to get notified when a column changed

not when a java-string changed

so u have to give us more details about what DB ur using and what DB framework ur using and how its setup in code

for example, are u using jpa? hibernate? just jdbc? ...

[hybrid narwhal]

no, just jdbc

also mb for saying that i want to detect if a string changed

[proper trout]

and which db is it?

[hybrid narwhal]

mysql

[proper trout]

no worries 🙂

[hybrid narwhal]

😄

is there like a built in event that gets called?

[proper trout]

i dont think so. im currently checking if mysql has sth for that, but i doubt

are the changes done by ur java app or also from elsewhere?

[hybrid narwhal]

ye

[proper trout]

if its former, u could emit such events urself within java

if latter, ur probably out of luck

(other than checking every couple of seconds urself)

[hybrid narwhal]

and is using for example like a while(true) loop a bad idea?

basically, i have a minecraft plugin that puts all of the online player's names in the database and i want to display all the online players in a dc channel (with a dc bot)

[proper trout]

bad idea, yes

[hybrid narwhal]

and i need to delete the "<player> is online" message every time the player leaves and send a message when they join

[proper trout]

can u please answer whether the DB is only changed by ur java code or also by someone else

[hybrid narwhal]

only from my java code

[proper trout]

then just go to where ur doing the string change and send out the event there

if ur in charge of when the change is happening, u can just notify/trigger the part of ur code that needs to be triggered

[hybrid narwhal]

no, actually not

[hybrid narwhal]

.

[proper trout]

okay. sure u need to act on a DB change? u just said its when a player joins/leaves etc. minecraft gives u events for that already, no?

[hybrid narwhal]

ye it does

that code is already finished

now u just have to scan of theres a change in the column

[proper trout]

so this is unrelated?

or can that only happen when users join/leave?

[hybrid narwhal]

ye

[proper trout]

yes to which?

[hybrid narwhal]

it only changes when a player leaves/joins

[proper trout]

then just do the check there

when a user joins/leaves, read the column and act if its different to before

[hybrid narwhal]

but again, the mc plugin and the discord bot are 2 different programms

so the only information i have from the plugin is the database itself

Detect if a column in mysql in ldbc has changed

Detect if a column in mysql in jdbc has changed

[proper trout]

ah. u have two apps and they are supposed to work with each other?

[hybrid narwhal]

Detect if a mysql database column in jdbc has changed

[hybrid narwhal]

yea, exactly

[proper trout]

well, i would probably setup some sort of event system so that ur plugin can send events to the discord bot

but thats probably a major redesign

so let me instead give u sth quick and dirty that works well for today

[hybrid narwhal]

alr

[proper trout]

u previously mentioned "while (true) check db ..."

thats called active-polling and its generally not good bc

• u put a lot of stress on the system, even though 99% of the time it wont find a change
• ur missing the actual moment it changed by a few millis if ur check-rate isnt small enough

however, this general idea does work

and its sth u can do

the proper way to do this in java is via a scheduled executor service

looks sth like

[hybrid narwhal]

i mean, i dont really need to know when exactly a player joined/left the server, it can be delayed by a few milliseconds or maybe like 1-2 seconds

[proper trout]

ScheduledExecutorService service = Executors.newSingleThreadScheduledExecutor();

...

servive.scheduleAtFixedRate(this::checkDB, 0, 200, TimeUnit.MILLISECONDS);

...

private void checkDB() {
  ... // read db, do ur thing
}

with that, ur reading the db 5 times per second, which it will laugh at. so thats no problem at all. stress-wise

and ur reaction will be at max 200ms delayed

which is still quite fast

[hybrid narwhal]

okay

so this is the most efficient way of like detecting a change?

[proper trout]

via active-polling

which is always the last option to consider, cause its the worst

better would be if u can get ur plugin to notify u instead

i.e. react on-event

instead of checking urself

[hybrid narwhal]

so but there isnt an event for a db change in jdbc?

[proper trout]

nope

some DBs might offer sth for that, but i couldnt find anything for mysql with a quick search

like, i know that u can setup hooks to be executed within some DBs on column changes. but no idea how to connect that to ur java via jdbc

its scary that ur plugin writes to a DB directly anyways

[hybrid narwhal]

wdym?

[proper trout]

there are more concerning issues in ur design than ur current issue

wait, is the mc plugin a server or client plugin?

is the plugin executed on the same machine than the discord bot?

and on the same machine that has the DB?

[hybrid narwhal]

no, both i host externally

lmao chatcringe gave me this answer

[proper trout]

big oof

[hybrid narwhal]

ye

[proper trout]

is it a server or client plugin?

[hybrid narwhal]

server plugin (spigot)

[proper trout]

and the server runs on a different machine than the discord bot?

[hybrid narwhal]

ye

[proper trout]

but both have access to the DB?

[hybrid narwhal]

no, both have access to the db

[proper trout]

okay. so if done "properly", ur discord bot would offer some rest-api endpoint for the plugin to call

that way the plugin could notify the discord bot about events

and for example forward all kinds of events to it

and the bot could then act on it

[hybrid narwhal]

i made the db assessable from anywhere (for testing)

[proper trout]

on the long run, this would be the proper approach

and it would also help u for other situations

cause u could then tell the discord bot every single event

[hybrid narwhal]

nah, but actually its fine of theres a small delay

[proper trout]

but i totally get that this is a bit overkill for today

another funny approach would be to abuse discord webhooks

[hybrid narwhal]

because sending a message is also like random....

[proper trout]

this is actually quite clever

i.e. u could setup a hidden channel in discord

enable webhooks on it

and then let the plugin send messages to that webhook

thats quite simple

[hybrid narwhal]

ohhhhh

[proper trout]

and in ur discord bot u can then react on messages received in this channel

and by that act

so ur plugin could write to that hidden discord channel "player x joined"

ur discord bot reads it with onMessageReceived

and by that do sth

[hybrid narwhal]

i actually never quite really understood what dc webhooks are

[proper trout]

well, its just an url and u send ur text message to it

and discord will then send it as message in that channel

its super simple

that way ur plugin can freely post messages in discord

which ur discord bot can read

[hybrid narwhal]

so i wouldnt need a db??

[gaunt chasm]

make an audit table

with login/logout events and time

thats one way to go about it

[hybrid narwhal]

wdym audit table?

[gaunt chasm]

a table with events

event X occured at time Y

[hybrid narwhal]

so how do u send a message to a channel via a webhook?

[proper trout]

well, DB is still the best way to persist

but u can use the discord webhook to transport messages to ur discord bot

for example events

[proper trout]

its a very simple http post request

4 lines in java with HttpClient

there are also libraries, then its even easier

[hybrid narwhal]

isnt there a built in method in jda?

[proper trout]

i think so. let me check our code base for tj-bot

cause we also use webhooks it for #tjbot-log-info

seems its not in jda

but

implementation 'club.minnced:discord-webhooks:0.8.2'

then u just do

WebhookClient webhookClient = WebhookClient.withUrl(url);

and

webhookClient.send(...);

with either the text or an embed

so its really simple to use with a lib like that

but u can also do it without libs with javas HttpClient

as its just a http post request

[hybrid narwhal]

can i also delete a message via webhook?

[proper trout]

yes

and also edit

but note that everyone who has the webhook url can do that without any extra security checks

so u have to keep the webhook url secret

[hybrid narwhal]

ok

[hybrid narwhal]

whats the maven dep for it?

[proper trout]

for that webhookclient class thing

since its not in jda

[hybrid narwhal]

ye

[proper trout]

either u use that or javas HttpClient

[hybrid narwhal]

no, i wanted to use that

[proper trout]

👍

there is also a website to easily send messages manually

https://discohook.org

u can click here and it shows u the full message:

https://i.imgur.com/PdltbtQ.png

and that u just have to send as a POST request to the url

thats it

but sure, u dont want to write that json urself. so just use the WebhookClient dependency and everything works out of the box

[hybrid narwhal]

alr, ill use that

but whats the maven dep for it?

[proper trout]

for that webhookclient class thing
since its not in jda

not sure which part of that sentence is unclear to u

[hybrid narwhal]

yea that webhook thing

[proper trout]

yeah. so everything clear now?

[hybrid narwhal]

i dont understand....

[proper trout]

WebhookClient webhookClient = WebhookClient.withUrl(url);

...

webhookClient.send(...);

thats the code, right

[hybrid narwhal]

ye

[proper trout]

if u write that now, it wont compile

ur java will say "who the frick is WebhookClient, never heard of it"

u have to add this external code to ur project first

just like u did with JDA

or any other dependency

the dependency u need for this is club.minnced:discord-webhooks:0.8.2

[hybrid narwhal]

yes BUT i wanted to know what the dependency of that webhook thing is SO i can use it lmao

[proper trout]

it is club.minnced:discord-webhooks:0.8.2

[hybrid narwhal]

and for maven?

[proper trout]

https://mvnrepository.com/artifact/club.minnced/discord-webhooks/0.8.2

<dependency>
    <groupId>club.minnced</groupId>
    <artifactId>discord-webhooks</artifactId>
    <version>0.8.2</version>
</dependency>

[hybrid narwhal]

finally, lmao ty

[proper trout]

lol 😄

for the future, just copy paste it into google

first result is the mavenrepository i just shared

and there u can click on maven/gradle/...

https://i.imgur.com/7cF2Jfr.png

[hybrid narwhal]

alr

[proper trout]

fun fact, its made by the same author than jda

iirc they moved it out of jda as seperate project

[hybrid narwhal]

fr??

oh

why tho?

[proper trout]

i suspect there are a lot of people who just want to send some webhooks without getting the full jda

[hybrid narwhal]

ahh i see

this would work?

[proper trout]

yes 🙂

now u have it in discord

and ur discord bot can pick it up with its "on message received" event

and do whatever it needs to do

that way u can transport any info from plugin to the bot

but dont spam it too much. there are rate limits if u start sending 100x per second lol

id use the DB as much as possible and just notify the bot via discord for certain events

[proper trout]

sounds like u didnt add it as dependency properly

[hybrid narwhal]

i did

[proper trout]

but maybe thats also a minecraft quirk, no idea how to add dependencies to mc plugins

[torn lodge]

Changed the category to Minecraft.

<@&987246652869971988> please have a look, thanks.

[proper trout]

(^ paper, add a dependency to a server plugin)

[hybrid narwhal]

i added that like i would add a dep to any other app

[proper trout]

maybe u have to take extra steps for mc plugins. i have no idea. a Minecraft-Helper might know

[hybrid narwhal]

but actually

i would like to stick to the db thing

and scan it every 0.5 secs

[proper trout]

sure thing

as long as u dont overdo it, it will work just fine

[hybrid narwhal]

wdym overdo?

[proper trout]

reading the DB 1000x per second

[hybrid narwhal]

oh alr

[proper trout]

everything starts smoking and ur computer goes boom

haha

[hybrid narwhal]

what does the ,0 ,200 stand for?

[proper trout]

if u mousehover it, u should see the javadoc explaining it.

in a nutshell, the first value is the initial delay and the second the delay for every other invocation

as in "execute every 200 millis, start right now"

[hybrid narwhal]

👍

do i have to call that method from the jda startup?

[proper trout]

doesnt matter where

once u call it, it will execute this method every 200ms forever

(until it crashed or u explicitly cancelled it)

[hybrid narwhal]

also should i getconnection to the database in the loop?

[proper trout]

loop? there shouldnt be any loops

are u using a connection pool like hikari?

[hybrid narwhal]

i meant the looped method

[hybrid narwhal]

lmao

[proper trout]

😄

if u have hikari, u can grab a connection from it and it will be cached

if not, u should try to avoid reopening a connection every 200ms

but instead try to keep one open for longer

that said, i doubt u would notice any problems if done wrong

unless ur going crazy and reduce the rate from 200ms

200ms is still slow enough so that mistakes dont really have any impact

[hybrid narwhal]

would this work?

static ResultSet resultSet;
    public static void checkDB() {
        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT player_name FROM player_join_notifications");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        ScheduledExecutorService service = Executors.newSingleThreadScheduledExecutor();

        service.scheduleAtFixedRate(ShowOnlinePlayers::timer ,0,200, TimeUnit.MILLISECONDS);
    }

    private static void timer() {
        if (resultSet.next()) {
            //CHECK THE PLAYERS
        }
    }

i call checkDB() on startup and getConnection() connects to the db via Drivermanager.getConnection()

[proper trout]

u have to do this resultSet = statement.executeQuery("SELECT player_name FROM player_join_notifications"); thing in timer()

u can maybe reuse the connection, but not the rest

[hybrid narwhal]

oh okay

[hybrid narwhal]

like this u mean?

static Statement statement;
    public static void checkDB() {
        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        ScheduledExecutorService service = Executors.newSingleThreadScheduledExecutor();

        service.scheduleAtFixedRate(ShowOnlinePlayers::timer ,0,200, TimeUnit.MILLISECONDS);
    }

    private static void timer() {
        try {
            ResultSet resultSet = statement.executeQuery("SELECT player_name FROM player_join_notifications");
            if (resultSet.next()) {
                //CHECK THE PLAYERS
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }```

[torn lodge]

Detected code, here are some useful tools:

[proper trout]

not sure if u even have to create the statement again each time

been some time i used jdbc

[hybrid narwhal]

alr ill try