Spigot Plugin Development – 82 – Plugin with MySQL Database and JDBC

Hey what’s up guys welcome back to another video in the spigot series in this video we’re going to make our first plugin that connects to a mysql database young kids are growing up in basements online a whole new generation i’mma make mine all right guys welcome back hopefully

You’re excited for this episode we’re going to be making our first plugin that connects to mysql database and does stuff with it stores data retrieves data updates data all the basic operations that you would need to do so this will get you started on being able to make plugins that work with

Databases sql databases to be exact and then from there we can start to learn how to do other stuff and increase the complexity of our plugins and the sql queries that we write and all that stuff so i’ve already set up a basic plugin here just created one just

Now and the first thing i want to do is go ahead and go to my pom.xml change the java version to 17 because i like using modern versions of java i don’t like living in the past like some of you weirdos and then i’m going to copy this from my

Other screen which is just the the maven plug-in that will compile this plug-in directly to my plugins folder if you haven’t seen that episode make sure to go check it out i’ll show you how to set this up but it’s pretty simple and so click that and believe it or not

That’s all we got to import nothing else we have to do here and like i said in episode 80 jdbc is built directly into java itself so you can do stuff with databases in java core java without having to import anything and the only thing that we needed to import usually

Is the driver which is included in spigot and we’re already importing spigot so it’s included now we need to figure out how to connect our mysql database using jdbc and first of all i recommend this link here i gave this in the presentation as well this is where you find all of the

Reference material for how to do stuff in jdbc it’s really really useful so it tells you how to connect your database how to do queries on a database all the stuff i’m going to be showing you in this episode but with more detail so you

Can use this as a reference if you wish it’s very very helpful okay don’t forget to check it out link in the description below now to connect to the database we’re going to need three things to start with we’re going to need a url a connection url a username and a password

Okay so first thing the most complex thing is the url so this url will specify what specific mysql server you want to connect to on the interwebs and also what specific database within the server you want to connect to all right so if we want to connect to our local you know mysql

Server we can do jdbc colon mysql colon slash local host and if you’re using a different you know sql server such as sqlite or some other stuff um the connection string will be a little bit different so you can just refer to their documentation on what to provide here

But it’s all pretty much the exact same and then after that you put a slash and then you specify what specific database you want to be using so if we go back to phpmyadmin on our wamp server here so we logged in and we can see all of our

Databases here remember we usually refer to mysql as a database itself but i think it’s more proper to call it a server mysql server um and then these are individual databases within the server okay so you can have mysql software running on your computer and that’s just the server and

Then within that server you have individual databases right because it wouldn’t make sense if you would install mysql and only have one database at a time that would kind of suck right so you have multiple databases and multiple tables within each of those databases and that’s how it works all right so we

Want to go ahead and just create a new database here so that’s going to be what stores all of our information for this plugin here our plugin will be using this database to store the information on the players so click new here then we’re going to give it a new database name

We’ll call it stat tracker stat underscore tracker then click create now at this point you could go ahead and create the table for your database if you want to um obviously a database usually has multiple tables for the but for this plugin we’re going to have only

One table just to keep it as simple as possible for now but we’re actually going to be creating the table within the code itself so that well after it connects to the database it’s going to automatically create the table and then it’s going to do everything automatically from there as

Well so automatically adding data to the database everything that it needs to do that’s especially useful because whenever you want someone to install your plugin on their server you don’t want to have to you know make them do a lot of work to set up the database and get everything working

And expect them to have to know how to use mysql and all that stuff okay and now here we can specify the database name that we just created so stat tracker and that will tell jdbc what database we’re talking about and now for the user and password so string user

That’s just going to be root like we used to log in before then the password we haven’t set a password it’s just on the default settings so we’re just going to put empty because there is no password and now to connect to the database we’re going to go ahead

And do driver manager that’s from jdbc or java.sql and then we can do git connection and now we can pass in all that information so url user and password there you go and this returns a connection object that you can use to do stuff to the database this is the most

Important object that we have so we’ll just store that so connection connection is equal to drive for manager.getconnection make sure you import that there you go and now we need to handle the exception that could happen so sql exceptions are very common if you do stuff incorrectly especially as you’re first learning sql

Or even after you’ve been using it for a little bit it’s very common to write your sql queries incorrectly or you know specify some other information incorrectly so you will get sql exceptions that tell you what the problem is so you need to handle those somehow and

We’re just going to handle it by doing try and catch try catch catch sql exception e for exception and then e dot print stack trace so it’ll just print out the information to the console and there you go if you didn’t get any exception at this point then that means that you

Actually connect to the database successfully so we’re just going to print out connected to the stat tracker database and then here we’ll have a message unable to connect to the stat tracker database and now at this point you can then use the connection object here to do stuff

To the database like i said now before we continue and make the table and all that stuff let’s go ahead and make sure that this actually works and it connects to the database so let’s go ahead and run this and it should automatically compile the plug into my plugins folder

And i’ll see you in a second all right cool so i’m going to start the server now okay so the server has started let’s go ahead and find where our messages are from this plugin there we go it says stat tracker enabling stat tracker then it says standard out connected to the

Stat tracker database now let me tell you about what this plugin is so stat tracker is just going to be a plugin that whenever the player does certain things like mine’s a block kill somebody gets killed stuff like that it’ll keep track of those stats so that we can have them

And also it’ll give player money when they do those things and we have to keep track of their money as well so it’s just a small example of something that you could do with the database so we’re keeping track of data on each player and yeah so to do that first we’re going

To create a table for this database so the player stats table within the player stats database so to create a table we’re going to need to execute some sql and to do that with jdbc you can do that with a statement or a prepared statement so statement statement is equal to connection dot

Create statement there you go and then with that statement you can then do statement dot execute and you have all these different types of methods here that you can use to execute sql now if you just want to execute some simple stuff that you know just does

Something then you can just use execute if you know you’re going to be executing a query then you execute query and so we’re going to execute something so just to execute and within here you’re just going to provide the sql that you want to execute so we’re just going to

Make a string string sql is equal to and inside of this we’re going to craft some sql code that’s going to be run on our database so we’re going to do create table if not exist and i’ll explain all this in a second create table if not exist play your stats

And so what this will do is just create a table and then it will create the table only if there’s already not a table with this name and this will be the name of the table and then inside of these parentheses here we’re going to specify the different attributes of the table so

Um each attribute along with its data type so we have to think about what kind of data we want to be storing in the database for each player right so let’s store each player’s deaths so deaths and it’s going to be an integer sql has different data types just like

Regular programming languages have data types a lot of them are pretty self-explanatory like integer long double date time and some of them also do vary depending on the dialect of sql that you’re working with or like the server that you’re working with but generally speaking for these very basic

Sql data types they’re all pretty much the exact same okay so def’s integer um kills integer or ins um blocks broken it or long because it can be a you know a lot of blocks that you break over time and whenever you have multiple words just separate them by a underscore i

Think that’s just the common practice and then um balance so like i said we want to store how much money they’ve made so balance that’ll be a double because it’s a decimal point in number and then we want to store their last login last to login which is a date

And then their last to log out which is a date and that’s it right there so you just put a semicolon on the end and this is your entire sql statement that you’re going to run upon the database so this should create a new table for us in the

Database if it works correctly and then we’ll know if it doesn’t work if we get an exception that’s just generally speaking how you know if it works or not so it’s already surrounded by aes co-exception catcher so that’s fine so it should print out any information but

If it does catch the exception it’ll say unable to connect to the stat tracker database so that doesn’t really apply to if it messes up here it applies if it messes up here but not here so we kind of just want to separate these two pieces of code so that we catch them

Separately so one way you could do that is just to take this out here and go ahead and put it here in its own little part catch sql exception e e dot prince sec trace and we’ll say s out unable to create the stats table in the database

Okay cool created the stats table in the database all right and now because of the way that this is set up this is a local variable local to this block here or rather this block here so we cannot access it right here so we’re just going to take this out and

Declare it right there and then now inside of here we’ll just reassign it or assign it for the first time inside of there and then now it should be accessible within here okay and it says variable connection might not have been initialized so we’re just going to initialize it

There you go the only problem that could occur from this is that if this did not work correctly then this will net will still be null right and so if it runs on something that’s no we’ll get another exception but just for the simplicity of what we’re doing here we’ll just pretend

That that’s not a problem okay you may want to check it or something like that redesign it so it works better and now let’s talk about what you’re supposed to do to know how to be able to write these sql queries or sql uh code i guess whatever you want to call it

Like i said before in my presentation i really like w3schools.com they have a really good sql reference so anytime i need to figure out how to do something like create a table i usually cannot remember the syntax of how to do it so i just go to that

Reference and then it’ll tell me how to do it that’s really that simple okay and over time though you’ll start to remember some some of the basic stuff for how to do stuff with sql but it’s okay to go and look at a reference if you don’t know how to do

Something as long as you know where the reference is you can refer to it whenever you want that’s what a reference is all about so that’s how i knew how to write this here okay i’m not just pulling it out of my brain and also besides that after we execute the

Statement here we want to go ahead and close it that’ll free up the resources of the statement that’s just good practice or you can use try with resources but i’m not going to show how to do that for this um so yeah it’ll stay close the statement

And then after that we also want to close the connection at the very end down here connection.close so usually any type of object that you have within sql or jdbc rather are going to be things that you should close when you’re done working with it that’s just something that you

Should do well it looks like this can generate an exception as well that’s kind of annoying so we’ll just go ahead and we’ll just run it here whatever it doesn’t really matter we’re going to be moving this to a different class in a second so that we can do everything in

That separate class not so it’s not populating or so it’s not all jammed into our own enable method so you’ll see but anyway with all that said let’s go ahead and test this out to make sure that it actually works so we know it’s going to connect to the database now but now

Let’s go ahead and see if it actually creates the table within our database and see what it looks like on phpmyadmin there we go so now we’re reloading and now we should see the messages so we’ll know if it worked or not any second now so there we go stat tracker connected to

The stat tracker database and then it says created the stats table in the database great so just go here reload and nice so we got player stats within the stat tracker database awesome so we can go into the table if we want to and we can see that each of the columns

In the table are there so the deaths kills blocks broken balance last login last log out really really cool stuff so we are missing something though usually you want to have an attribute within the table that is unique and a unique identifier or what’s known as the primary key and this uniquely identifies

Each row in the database or in this case each player’s stat individually so that each player stat is different from the other they’re all unique and then you can also grab them with their id if you want to as well so if you think about it what would be a

Good identifier for each row in our table in this case it would actually be the player uuid and that’s a good one because we can then take a player’s uuid in the plugin and then immediately grab their player stats via its id right so we can go here and edit this so do

Uuid which is the name and then the data type so this would just be a string so we’re going to take the player’s uid and convert it to a string and a string in sql is actually a varchar so varchar and then inside the parentheses here you

Want to put the length of the varchar so this is going to be the max length of what the data type can be for this specific field in the uh table so in this case we know that uuids are usually 36 characters so i’ll just set

It at 236 and then we’re going to go after that and do primary key so this will tell sql when you’re creating the table that this is the identifier the the key for the uh each row and the database table but if we go ahead and run this again it’s not going to

Actually change anything because we’ve already created the database table so if we do create table if not exist it already exists right so then it’s not going to run this right so if we were to get rid of this then it would just probably give us an exception because we

Cannot create a table that already exists right so what we want to do is just go to phpmyadmin and just manually delete this table so that it can be recreated with the new schema for that table or the new uh essentially the new way that we want

To model it so to delete this let’s just see where we can find that so we’ll go to the database here and then do drop we want to drop the table click ok and now the table is gone so we can go and rerun this and restart the server and reload

The server and it should recreate the table so there we go says created the stats table in the database if we go back again reload we now have the new table with the new uuid field as well so perfect so now when we create new player stats it’ll also be able to uh

Essentially identify each stat by the player’s uid so anytime you create a table within an sql database you usually always want to have a primary key the unique identifier for that table so now we want to create the java representation of this table here right

So we’ll go to project then we’ll go to new package we’ll call it models or whatever you want to call it our model that’s probably a better name but yeah we’ll call it player stats it’s a new class player stats and this class will represent a single player stat

Within our database string uuid private integer deaths private integer kills uh private long blocks broken i’ll call it box broken and then what else private date last login private date last log out from java.util there we go so now this is all the data that

We want to store on each player so each individual player stat will have all this data on some specific player and now let’s just give a constructor and getters and setters for that so generate constructor select all of these here boom now we want some getters and some setters so

Code generate getter and setter select all of these okay by the way let’s go ahead and add one field i forgot to put so we want to do the balance as well so private double balance there we go and then we’ll just go ahead and delete our constructor and recreate

It so code generate constructor select all of these okay and then we’re going to generate a getter insider for that single balance field so get in setter okay and there we go so now we have a regular java class here a plain old java object now the reason

That we have both a player stats class and also the table for the database is because if you think about it we need both because at some point the data is going to have to exist in our code in our plugin whenever we’re working with

The data in java but when we want to store the data persistently then we can use sql to do that and work with the database and all that so anytime we need to do something with java it has to be in java code obviously so that’s why we have this class here to

Essentially represent what a single player stat is for a single player and so you can think of the player stats class as a representation of the table that we have in our database now that you’ll have an idea of how to connect to a mysql database using jdbc and also how

To create the table let’s go ahead and make a separate database class so that we can move this out of our on enable method into a specialized class meant for database stuff because i don’t think it’s good to jam all this stuff into that into your

Main class like that so i’ll make a new package called db and then i’m going to make a new class called database so anytime that we’re working with database stuff we’re going to be referencing this class here because this has all the stuff related to our database

So let’s go ahead and take what we have in this main class here and move it to this class so i’m just going to steal this boom and then i’m going to make a method here called get connection so public connection get connection so i think i told you this before but

You need a connection to to do anything with the database you need a connection to make any type of sql query so what we’re going to do is have this method here that returns a connection and if a connection is not already established it’s going to establish a connection

So what we’re going to do is have this field here also inside of the class so private connection connection and then this will just return that so if connection is not equal to null meaning that we have already established a connection we’re going to do return connection otherwise if the connection is null

Meaning it got past that point then we want to establish a new connection with the database so i’m going to go ahead and paste that and then we’ll just take what we have here just take it out boom boom and then we’ll just take this get rid of

That and then we’ll just do this.connection so it’s referencing the one part of the class and then uh so it’ll set that and then after that we’ll just return the new connection there you go and so with this in mind i have to say something so i don’t know what the best

Practices in terms of how to manage your connection to the database i did some searching and it seems that some people recommend that you keep the connection open so that anytime you need to make a new request to the database or execute some sql code or query

You can just take that connection and do that but the other option is to establish a new connection every time you need to run some sql code on the database i don’t know what the exact best practice is um i think in usually you would want to use something like a

Connection pool um that’s another thing you can do so you have like a pool of connections that are already made and then anytime you need to do something to the database you select one of those connections and then use that and so there’s multiple connections that can be used for different

Uh stuff that you want to do to the database so with that said i think the from the little research that i’ve done i think you do want to leave the connection open and then if the connection ever goes stale or you need to re-establish the connection or

Anything like that then you can go ahead and do that if you have experience with databases and you actually know what you’re talking about then please comment below if you know what the best practice is for this in the future i will show you guys how to do other stuff but this

Is just a basic setup here in the future we will be using libraries that handle all of this stuff for us we don’t have to do all this manually this is very low level compared to the other things but just for now this is how i’m going to do

It and you could do this you can do it this way if you want to or you can just establish a new connection every single time if you want to i just wanted you to be aware of that okay but anyway with that said i removed the

Try and catch so now it’s not going to handle the exception so what i’m going to do i did that on purpose what i want to do is make it so that this throws the sql exception so that you can handle the exception wherever you’re trying to do

Stuff to the database so wherever this function is called or this method is called they have to handle the exception or you know promote it to the uh the signature so that it throws it like we just did here so this is the exact same

Code as before though so uh this time we have a connection here if it’s already established since this is not null then we’re just going to return that if the connection was not established then we’re going to establish it and then we’re going to return that and then set

The connection and so for future use we can just return it instead of having to reestablish it and yeah so that will do that and then now let’s make another method public void initialize database and the responsibility of this method will be to create the table just basic setup stuff

That we have already so we’re just going to take this and then we’ll put it here and now instead of just doing connection this could be nulled right so that’s kind of dangerous to just try accessing it directly like that so we want to use the method to access it so good connection

So getconnection.createstatement doing the same thing as before creating the table using the sql code that we have here but i want to do the same thing though i want to take this out of the try and catch so that any method that calls this has to do the handling

All right so it looks good to me except that we do not want to close the connection now like i said we want to keep the connection open for the life of this plug-in at least until the service reloaded and when the server is reloaded speaking

Of that this will be you know reset right because like i said before memory is not persistent so that’s good though because we have this mechanism here to reestablish the connection if that ever happens so so now inside of our stat tracker we’re just going to make a new database object so database

Db is equal to new database and then we’ll do db.initializedatabase so that will establish the connection automatically and then create the table if it does not already exist so and then we’ll handle it here so try catch x sql exception boom ex dot print stack trace and we’ll just say s out

Unable to connect to database and create tables there you go so that’s much more clean now i hope you agree with that um much more modular as well because we’ve moved it into its own little methods and all that stuff in its own little class so let’s just verify that that still

Works nothing broke here by me doing that there we go connected to the stattracker database and then created the stats table in the database perfect so everything’s working smoothly now let’s go ahead and move on let’s actually do stuff with the player stats table let’s populate it with data and

Get it actually working to do what we want to do so we’re going to make a new package called listeners because when the player does stuff that’s going to change their stats right so listeners new java class uh we’ll call this i don’t know what they call it i’ll just call it listeners

Kind of generic name implements listeners boom all right let’s get one of the basic ones here so event handler public void on block break so under the player whenever the player breaks a block we’re going to update their stats right so block break events e okay so then we’ll get the player so

Player p is equal to e dot get player great and now we need a way to get the player stats okay so let’s think about it this way how do we know if the player already has a stats in the database or not right we need a way to figure that out so

Let’s actually make a way to retrieve the player stats from the database if they have one we’ll just assume they have one so go back to database and we’re going to make a new method here public player stats because it’s going to be returning their stats there you go

And then uh find player stats by uuid so the name pretty much describes exactly what the method is intended to do and then we’re just going to pass in the uuid of the player there we go we’ll actually just take it as a string so string uuid because that’s how we’re

Going to be storing it remember we can’t store like a custom object as a into a database that doesn’t make any sense so it has to be some sort of primitive or some sort of data type already accepted by sql or mysql to be exact so now we need

To craft a sql query that will retrieve all player stats that have a uuid of the uuid that we provided so we’re going to need a statement right so statements statements that’s how you actually execute sql code and then now we’re going to make a string like we did before string sql is

Equal to make some space and to select stuff or find stuff in a sql database you’re going to use the select uh word so select and then now you want to specify what you’re selecting so in this case all fields or all attributes of the table

And then from and then now you want to specify the table that you want to select stuff from so in this case it’s called player stats that’s what we named the table before and that alone is a valid query you’re going to be selecting all rows of the player stats table but

We don’t want to find all rows we want to find all rows that match or have a key a primary key of our uuid so what i mean by that is if we go back to phpmyadmin we have this you know table here uuid task kills blah blah uuid is our primary

Key right this is how we uniquely identify each uh row in our table so we want to have mysql go through each of these rows here and find the rows that have this uuid and of course because it’s a primary key because it’s a primary key only one row

Or none at all should have this okay it should never be multiple that would make it not a primary key because they have to be unique right that’s the whole point of a primary key it’s a unique identifier all right so move that back so now to specify a condition we want to

Do where uuid is equal to and then we’ll add on to that uuid there we go and by the way you may have noticed i’m using capital letters when i’m doing sql keywords that’s just the convention when you’re writing sql so uh yeah that’s why i do that it kind

Of looks better anyway so that’s how you distinguish like you know custom names from sql keywords um but yeah so now we’re going to go ahead and try executing that so statement dot and then you have all these options here because it’s a query meaning that we’re asking for information that’s a

Way of thinking about it or we’re retrieving information we’re not going to do execute we’re going to execute query there you go pass in sql and then now this will return a result set the result set are the results of the query and now this is basically like an

Iterator you can iterate through it to find um you to go through each of the results in the result set because you know there can be multiple in this case like i said there will be either none or one but in other cases there will be multiple if

You’re trying to do something where you’re not selecting based on the the primary key so if we had multiple results or maybe we didn’t know if we had multiple results the normal way that you would go through each of them is a simple while loop so while results

Dot next that returns true or false if there’s a next another result or not so that will go to the next result or the first one if it’s the first one so results dot and then you can do git and you can get the fields of the results so

Yeah however in this case we know that there’s probably going to be either 0 or none so we’re going to do if results.next there’s no need to do a while loop there if there is a result then inside of here we’re going to create a new player stats and populate

It with those results that we found from the table from the database so we’re going to do player stats player stats is equal to new player stats in this case a single result represents a single row in our table so let’s go back to our table and see what we have to grab

So with that single row that we have we need to grab each of these attributes here or fields whatever you want to call them so we have to grab each of these individually based upon their data type we know what their data types are because we define the table

So that’s what we have to do now so go back here and we’ll just do that one by one so string uuid we already know what that is so we don’t need to grab that one so the first one is deaths that’s going to be equal to results dot get

And now we want to specify the method based upon the data type of the field that we’re trying to grab so in this case it’ll be Desk is an integer right so git int and then now you just specify either the column index we know it’s going to be the second column or we can specify it by the column name or the column label so we’re just going to do it by its name

Just to be safe in case we mess up the indexes so deaths and you just repeat the process so oh it’s not a string obviously it’s going to be an integer hopefully you caught that make sure you specify not you know the variable name but obviously the

Column name not the column name that we set when we define the table up here right so blocks underscore broken now we need a last login and last log out so we stored those as dates in our java object so state date last login is equal to results dot get date

Last login oops like that there you go and then date last log out is equal to results dot get date last log out now we’ll just pass all of that data in so uid we already have that deaths kills balance and then last log in and then last log out

Awesome so we have created a new java player stats object based upon the data that we found from the database pretty cool stuff right so we’re translating between the two things so now we’ll just return that because that’s what we’re asking for in the find player stats by uuid method so return

Player stats there you go and before we return though we want to close the statement to free up the resources so statement dot close there you go and then otherwise if there were no results if this returned false meaning that nothing was found there is no first result

We want to just return null so return no return know if i can spell it correctly there you go and then also close the statement here as well sustainment.close and by the way you may notice that this is you know red and stuff like that and also highlighted that’s just because

Intellij tries to help you and provide syntax highlighting for your sql queries as you’re writing them to help you you know make them and stuff like that and make sure they’re all correct so what you can do if you have intellij ultimate is connect to the database in intellij

And then it’ll know what the schema is of your database aka how your tables are modeled and then it’ll provide you intelligence uh you know syntax highlighting based upon that information that it you know finds out so just ignore this it’s kind of annoying that it does this even though we didn’t tell

It to but i’ll go ahead and show you in the next episode how you can connect to your database within intellij so that’ll help you as you’re working and it’s really cool stuff you’ll see so inside of here we now want to use that method we just created within our database to

Retrieve the player stats on a player or know if there are no player stats okay so we need to obtain an instance of database within this here method so that we can call that method and you don’t want to go ahead and just substantiate a new database object like we did here

Because we don’t want multiple instances of our database right think about it so each class that we have each database object that we have has a connection object right that’s local to that class and so every time that we create a new database object and initialize the database we are establishing a new

Connection and all that stuff right so if we make a new database object then we’re just going to be creating a whole new connection to the database so it’s kind of wait that’s kind of a waste right we want to just use what we already have right so we’re going to go

Ahead and figure out a way to pass in this database object into this other class here or any other class that needs this object okay now the way that you can do that is pretty simple i’ve shown this many many times on this in the series so we’re going to do private database

Database and then now we’re just going to change this to this dot database is equal to new database and then database dot initialize database and now we’ll just create a gitter for this method here so let’s get rid of this code generate getter database and then now let’s go ahead and

Register our listener here and i’ll show you how you can pass in the instance of this class so new or not new get server doggie plugin manager dot register events new listeners and now we’re going to do this and then now we’re going to do this here

Too in this case it’s giving a warning because we haven’t created a custom constructor for this class here so it’s like why are you trying to pass that in so we’re gonna go ahead and do that so code or first we’re gonna do private final stat tracker plugin

And then now import that of course and then now we can do add constructor parameter refactor and it’s going to create the constructor for us and it’s going to automatically set this field here as we pass it into the constructor it’s really smart so now this goes away and now the

Instance of our main class here is passed in to that listeners class therefore we can go ahead and do plugin or this top plugin to be exact dot get database dot find player stats by uuid p dot get unique id dot tostring there you go that simple and now we know that

This is going to return either null or a player stats objects okay so we’re going to go ahead and assign this to a player size object so player stats stats is equal to that make sure you import player stats and then we’ll have an if statement if stats is equal to null

Then we just want to go ahead and create a new one right if this is the first time that the player has been on the server they don’t have any stats then we’re going to go ahead and give them a new stats and then we’re going to store

That stats store those stats to be exact uh we’ll do stats is equal to new player stats and then now we’ll do uh p unique id dot 2 string and then now we want to establish some default values for each of these uh for each of these values here and then for

The last login we’ll just put right now okay and then new and then last log out um we’ll just set that one to no because they haven’t logged out yet as far as we know and now before we continue we want to take this new player stats that we just created for that

Player that we have and actually create it within the database so that the player has player stats that can be updated right because the whole point of this listener here is to take their current player stats and then update it with the new values if the table doesn’t have any

Values for that player to start with then there’s nothing to update so before we continue so we have two options to be honest so we could either go ahead and make some logic so that it automatically updates the player stats if it was found or we can go ahead and create it with

The new data you know plus one for blocks broken and then you know insert that into the database actually let’s go to blocks broken and change that one to one right because this is their first time breaking a block as far as we know so that should be one to start with as

Their first value right so then we’re going to need a way to insert those new player stats for that player into the database okay so we’re going to go back here now and create a new method called create player stats public void creates player stats or insert player stats whatever you want

To call it it’s going to be throwing in sql exception there we go awesome so what we want to do now is take the player stats and then insert that into the database using an insertion so let’s just go ahead and start by making the query this is one of those

Moments where you want to go to w3schools.com and look up how to insert data into a database table okay so go do that if you want to but obviously you’re watching the video right now so you could just watch me do it but for future reference on future plugins that you

Create then that’s what you would want to do right so i’m going to do insert into now the table name so player stats as you can see it logically makes sense how i’m writing this right you can read this and immediately understand it even if you don’t know sql

Just because how it’s written it’s very uh i guess verbose is the word i don’t know insert into player stats now we want to tell it what fields we’re going to be inserting or what columns we’re going to be inserting into uuid deaths kills blocks broken balance last login

And last slide lost last lost last last logout so that’s the data that we will provide and then now we want to save values and then provide the values for each of those columns in the same order and this is where it gets annoying when you have

To use a regular string like this because you have to type them and then escape out of the string and then add it concatenate it and all that stuff so you would have to do something like you know that and then the first value which is player stats or stats dot get kills

And then comma and then add the next one get dust it would just be a pain to have to type all that out right there’s actually a better way and a more recommended way which is to use something called a prepare statement currently we use just

Statements but we want to use a prepare statement and there’s multiple reasons to use a prepared statement the main two reasons are that it’s actually easier to use like i said for this reason here you have to do all this bullcrap here and also it helps to prevent against sql

Injection which is a technique to essentially do naughty stuff to your database stuff that you don’t intend to be done to your database by injecting values that you don’t want to be injected so this will help parse those things that could be potentially inserted into your data but that’s not really a huge

Concern obviously with you know minecraft plugins but that’s just one of the reasons okay that’s why people say to use that let’s just go ahead and remove this so leave it like that for now it’s not complete yet so we want to make a new prepared statement so prepared statement statements

Is equal to get connection dot prepare statement now inside of here you want to put the sql code that you want to essentially prepare so we’re going to take this cut it and paste it here and now the way that a prepare statement works is anytime that you want to insert

A value into this sql here you want to put a question mark and then later you’ll replace that question mark using methods as you’ll see so what we need to do is pass in question marks for each of these individual columns here right because that’s the values that we’re going to be

Passing in for each of those so values question mark question mark question mark how many other one two three four five six seven so we need seven question marks sevens four five six seven let’s hope i can count so that should be seven and then finish that with a parenthesis closing parenthesis

And here is our sql statement right so now we wanna fill those question marks with actual values because you can’t just put question mark that’d be stupid now to replace the question marks with actual values in java you have to do it one by one for

Each of these question marks okay so if you want to do the first question mark that corresponds to the uu id of the player and we know that has to be a string so then we’ll do statement dot set string and the first parameter is going to be

The index of the question mark that you’re targeting so it’ll be the first one it’s not zero based like other stuff in java and other programming languages it’s just regular numbers so it’s the first one and then the actual value that you want to replace it with so in this

Case it’ll be stats dot get uuid there you go and now we’ll do statement dot set and the next one is going to be an integer for the deaths of the player so deaths or not deaths excuse me two and then stats dot get deaths and just

Repeat the process so statement dot set statement dot set date that’ll be six and then now we have to think okay so um for this here it’s not asking for our java dates it’s asking for a sql date so you may have noticed that if you ever try importing dates

There’s two options you either have a date from java.sql or the date that we always use which is java.util so sql has its own representation of date so we want to convert our regular java utils date into sql dates or jdbc date whatever you want to call it

To that we’ll do new dates make sure it’s the sql one that you’re selecting so press tab and then now we want to get the epoch time of the dates that we have in java so we’ll do stats dot gets last login dot get time and that rep that returns a

Long of the epoch time of that date there you go so that will do that for you and then statements dot set date seven new date stats dot get last logout dot get time actually instead of null we’re going to go ahead and do it as new dates

And that’s because in our database here whenever we create a new player stats or updated player stats we call a new date and then do we get the current date dot get time if we try doing get time upon something that’s null then it will get a

No exception so you could you know add an if statement you know run it differently depending on that but i think it makes more sense just to have it as a date here just for simplicity of this episode i think you get the point right

So that is just a quick change i wanted to make so that it won’t give us a no exception in the future there you go so that was kind of a pain to have to type all that out but it’s way better than having to you know put

It directly into a regular string and having to do all that concatenation and all that stuff especially because java does not support template strings because uh why would i ever support that right so statement dots and now you have to choose which one you want so before we did execute

Query that’s just because we were querying or asking for information in this case we’re inserting information or updating the table so we’re going to be doing a execute update okay and that’s actually written in the documentation so if you do control q you’ll see it says executes the sql statement in this

Prepare statement object which must mean sql data blah blah blah blah such as insert update or delete so anytime you want to insert update or delete just use this alright kind of makes sense doesn’t it and this time we don’t have to pass in the sql it’s already prepared statement

And then we’ll do statements dot close after it’s done uh running the execution here and we know that it works if it doesn’t throw an exception so that’s all fun cool so that’s a that was awesome right we just created our first insertion statement so hopefully when we get to

Running this it’ll take a java player stats object and essentially map it into a sql insertion statement so to insert that data into the table as a single entry as a single row as a single tuple so we’ll go back here to listeners and we’ll do this dot plugin dot get database

Dot uh create player stats stats and that will create the player stats and then otherwise if stats was not null this means that we found some stats already on that player so we just want to update their current stats right so that’s easy enough so we’ll do stats dot

Set blocks broken stats dot get blocks broken plus one and then now we want to call this dot plugin dot get database dots update player stats except we don’t have that method so let’s go ahead and create that one okay so go back to database and this one’s

Going to be very similar to this one so we’re just going to steal this one copy it and paste it we’ll call it update player stats so the only thing that’s really different about this one is the actual sql statement itself so we’ll just take this one get rid of it

And again this is where you would actually look at the reference to see how to update something in the sql database that’s at least what i would do because i can’t remember this i don’t use it enough to remember it i usually use mongodb but that’s another that’s a tangent so update

Player stats the table name and now you want to tell it what you’re updating and the values that you’re updating it to so set and then now you’re going to separate by commas each thing that you’re updating in the value so deaths we don’t want to update the uuid we’re

Going to get rid of that in a second because of course that’s just the unique identifier of the of the player stats that doesn’t change right so it doesn’t make sense to update so we’ll skip that one so we’ll do deaths which is the first actually

Changing one so it’s f is equal to question mark because we’re going to replace that with the value comma kills is equal to question mark because we’re going to place that with the value blocks broken is equal to question mark uh balance is equal to question mark last login

Is equal to question mark last log out is equal to question mark and then now you want to give it the condition of where or what rows to update right because if we just do this without any condition that’s like saying update every single row or every single

Player stats within the player stats table and give them the values that we’re trying to pass in that doesn’t make sense we only want to update the one that corresponds to the player the specific player that we’re trying to update it for so we just want to add where

Uuid is equal to question mark so we only want to update it where the uid matches the player stats uid because that just makes sense so now we’ll just rearrange this here we want to take this one add it onto the end because that’s where we’re setting the uid we’re

Passing it as the last question mark and we’ll just change each of these numbers here there you go so hopefully that makes sense kind of the same concept in terms of how to use a prepared statement except that the sql is a little different because that’s how

You use an update so go back here to listeners so this actually works now the only problem is that you see that these are red right that’s just because we’re not handling the sql exceptions that could occur so for now we’re just going to take all of this

Because we’re kind of lazy just put it into a encompassing try and catch okay sql exception boom so before we continue we’re going to be refactoring this code at some point in a second because we have other stuff we need to do is going to get kind of repetitive before we continue though

We’re going to go ahead and actually test this out to make sure it works we did a lot of code here so it’s good to make sure that it actually works before we continue so hopefully you understand actually what’s going on so far um you’re getting a lot of new information here but

Um it’s just how it is you gotta you know take this into your brain and then you’ll apply it by making your own plug-in to you know test all this out on your own and of course the code for this episode will be in the description below

For you to check out in case you forget any of this i often forget how to do all of this so i often use my own code as a reference my previous code as reference okay so anyway let’s go ahead and compile this and throw it into the server and

Let’s see if it works alright so we’re ready to test this out we’re going to break a block to try and trigger something to happen to the database so hopefully in this case since the database table is currently empty we we’re going to create a new entry for

That single player that we have my test player and then it’ll show blocks broken for that player as one because that’s the initial value of that okay so let’s go ahead and see if that happens or not so go here break a block moment of truth see if

There’s any exceptions looks like there is okay it happens so it says unknown column whatever the heck that means in where clause all right so i’m just going to go ahead and check that out i’ll be right back so it looks like this is the issue

Here looks like i uh i did the statement incorrectly so it’s not able to process this last part here but that’s okay we’re just going to use this opportunity to use a prepare statement instead because that’s what you’re supposed to use that’s the best practice so we’re just

Going to go ahead and do a prepare statement prepared statement statement is equal to get connection dots prepare statements and now we’ll just take this and replace it inside of here get rid of this and replace this and then put here a question mark and then under this we’ll do statement

Dot set string 1 uuid looks way better in my opinion anyway uh so now we’ll execute the query same way um as instead we don’t need to pass anything in which is good and then it’ll return the results of that same way as before okay okay so now

Let’s break a block to see if it works boom all right no exceptions that’s good that’s a good sign always so let’s go back here reload and now we have awesome we have our player stats entries or row whatever you want to call it so we got

Uuid that’s our uuid of our player deaths zero kill zero block’s broken one because we just broke a block then we have last login that’s today’s date and then last log out that’s today’s date perfect it works exactly how we want it to except this time let’s break in

Another block and instead of inserting a new row it should update this one to two all right we’ll just break a bunch of them boom boom boom boom boom let’s go back here reload and boom we get 20 blocks have been broken perfect it’s working exactly how

We want it to and i’m very proud pretty awesome stuff right that’s really really really really cool now you can uh you know really get started making your plugins persistent with a database and all that stuff but we’re not done though i’m just saying that’s awesome and i like to see it

Right now we have only a listener for the block break event but we’re going to be tracking player stats on all kinds of different events so let’s go ahead and make those event listeners for those other things that we want to listen for so first one event handler public void on player death

How about that player death events e so this one will actually represent two of the stats it’ll represent when a player dies so that’s the death stat and also the player kill stat because there’s no event for when a player kills another player but you can grab who

Killed another player inside of this listener here that’s one of the pieces of information that you can grab from this which is pretty cool to do that you’re going to do e dot get entity dot get killer and this returns the player that killed this entity if there was no

Player if the entity was not killed by a player the entity could have been killed by someone else obviously it’ll just return null right so we want to make sure it’s not null so take this if that is not equal to null or how about if it is equal to null

Then we’ll just go ahead and return from here so it does not continue executing any code update the death stats of the player okay but after that if that is not null this this means that the player was killed by another player so we want to go ahead and update

The kill stats as well okay so let’s go ahead and get the killer so player killer is equal to e.g into t dot get killer so actually just take this move it here do this so we’re not repeating code and we’ll also get the player involved the

Player who died rather so player p is equal to e dot get entity which returns a player object so it’s a the name is a little misleading so now whatever we want to do here we want to do update the stats of both the killer and the killee

I don’t even i don’t even know if that’s a word or not but yeah we want to update both of those so let’s get the stats for each of those people that’s the first thing we should do so instead of having to copy this code from here where you know we have to

Check to see if the player has stats and make a new stats if they don’t have one already and all that fun stuff let’s go ahead and make a single method that does all of that so we can reuse it for each of these listeners that we’re going to

Be creating okay we’ll do private player stats is equal to get player stats from database kind of a long name right player p throws xql exception all right so it’s out of here we want to do the same thing we’re doing up here so first get the player stats so

We’ll take this i’ll just take this whole thing actually boom and so if the player stats does not exist we’re going to create a new one except that we want to be zero here because it’s going to be used for any of the other listeners as well and then we’re going

To create a new stats within the database based upon the new object that we created right otherwise then we’re just going to go ahead and return Stats because we didn’t have to create a new one because it already existed so that’ll get the player stats from the database if it exists or it’s just going to create a new one and then add it to the database and then return it we forgot to return it here so return

Stats so now here we can take this we can do gets player stats from db pass and p and do stats dot set blocks broken stats dot get blocks broken plus one and then also we can update their balance right let’s just reward them for breaking a block so stat set balance

Equal to stats.getbalance plus 0.5 cents so every time they break a block we update their blocks broken as well as their balance to reflect those changes and then we need to submit those changes to the database so database or how about this dot plugin dot git database dots

Update player stats and then pass on stats there you go so that’s much more concise right we removed all that code put into its own method and now it’s much more concise and better looking and modular and all sexy all sexiness and all that fun stuff so so now we’ll do the same

Thing here so try player stats killer stats is equal to get player stats from database killer let’s just change this to p that doesn’t it looks very annoying so we’ll do killer stats dot set kills and we’ll just increment that so killer stats dot get kills plus one and then

Update the person who died so p stats dot set deaths p stats dot get desk plus one boom and then finally let’s also update their balances so you know whenever you get a kill you get money if whenever you die you lose money so that’s so killer stats dot set balance

Killer stats dot get balance plus why does it keep doing that plus one all right and then finally we’re going to go ahead and update that in the database so this dot plugin dot get database dot update player stats killer stats this dot plugin the database update player stats

Pstats oh that’s a lot of code all right then catch sql exception ex whatever you want to call it ex dot print stack trace okay there you go i’m freaking sweating right now but let’s go ahead and do this one now so update the death stats of the

Player so so we can really just take the p stats and move it up here because no matter what the player did die so there’s no reason to rewrite that code and have multiple code multiple times so that’s kind of uh that’s kind of stupid to do that

So we’ll just take over all of our p stats and combine them so p stats p stats now we’ll copy the entire chunk of it put this together and then move it here boom and uh now we don’t need this it’ll just happen automatically first thing it’ll return if there’s nothing

Else to do and then it’ll update the killer stats if there is a killer that killed that player and then now we’ll take this and go ahead and call it up here and then finally we will just take this entire trunk and move inside the try and catch so the exceptions are caught

There you go so now we have a listener for the player when they die and that’ll update their stats if they die or if they’re killed or if they do kills and let’s go ahead and do the last two ones which are much easier which is when a

Player logs in and logs out and there you go i just pasted it because you don’t need to see me type the whole thing out that’s kind of a waste because we already did it multiple times already so what we have here is the exact same thing we’re getting the players who

Joined getting their stats updating their stats uh and all that and then exception is catched if there is an exception same thing for quit updating their last logout stats and yeah pretty really simple and yeah let’s go ahead and test it out on the server now to see if it actually

Works and updates the database with our stats okay so here’s the current state of the database um just me currently with 20 blocks broken what i’m going to do now is invite other people to join the server and you know wreak havoc so break blocks kill each other all that

Fun stuff and let’s see what the database looks like after all that fun okay look at this beautifulness i’ve given this guy a sword and told him to kill him it’s just so beautiful oh it didn’t work fail die die die die okay i need help i need psychological

Help thanks to all you guys who helped me test this out and here’s all the data looks so freaking beautiful i don’t know about you but who needs sex when you have data just beautiful beautiful data look at all these beautiful uuids oh they’re so sexy all right i’m a calm

Down i’ma calm down but yeah we got all the stats here random stats here for each player really cool stuff if you guys want me to show you how you can order these like so that you can get the player with the highest defs or the player with the highest kills and all

That stuff let me know i’ll show you how to do some specialized queries for grabbing such information um hit that like button hit that like button but yeah we’re pretty much done the last thing i want to do is well first of all you can edit this stuff if

You want to you can remove players by clicking delete um that’ll do that um you can also you know edit the players the individual stats rather and you can you know set the data and or make it null to null it all that fun stuff but

Hopefully this is really cool to you and you you like what you see and this is something that you can do for your own plugins as well now you can populate your plugins with data or the databases for your plugins with data to store important information about uh whatever

Your plugin is doing but anyway let’s go back here to our plugin real quick and let’s just do one more thing before i leave you guys i want to show you guys how you can delete a player from the database like i just showed you you know you can

Delete a player manually if you want to through phpmyadmin but obviously number one you can’t expect you know the the server owner to know how to do that they may not have technical skills like you you’re a code god but also it’s important to be able to do

That within your code because your plugin may have a feature where you need for some reason to delete entries from the database you know not all database not all database tables have data permanently sometimes the data goes into the database sometimes it comes out sometimes it’s changed sometimes it’s

Removed sometimes it’s you know whatever man but yeah let me show you how you can do that okay so down here we’re just gonna make a new method public void delete player data player stats rather player stats and we’re going to pass in the uid for the player that the stats for the

Players stats that we want to delete does that make sense i think you get the point so we’ll just do string uuid this one’s very simple so we’ll just do delete from and then the table name player stats where uuid is equal to question mark

And then you know what to do now so we’re going to do tape statement dot set string the first string or the first question mark rather and then the value of that question mark will be the uuid that we passed in so this is pretty simple and actually very

Similar to what we did to for our selection statement here so in this case we were finding the player stats that match that uuid in this case we want to delete the player stats that matches that uuid okay before you do this though you want to

Make sure that there is a player stats with that uuid that’s something you usually want to do but if there isn’t then nothing will happen it’s just not going to delete anything so it’s up to you how you want to do that though but yeah that’s also throws sql exception

And then we’ll do statement dot execute update remember before that this counts as one of the updates part of the documentation and the statement.close boom awesome okay so that’s just the general crud functionality i wanted to show you crud meaning crate crud crate read update delete so that’s like common

Functionality that you want to start with when you have a database or any type of thing so creates is to create stuff we use is to read stuff or find stuff in the database updates to update stuff and then at least delete stuff with these basic methods here you can

Then use that information to make more complex uh queries more complex updates and all that fun stuff okay and we’re going to see that in the future episodes but you should be able to figure out how to do some of that stuff on your own that’s a part of being a developer okay

Before i go there’s one more thing i want to do so before i told you that of course our database class here has a local connection to it right a connection that’s part of the field of the class and this will be set whenever we call initialized database or call

Call any of these methods right because if there’s no connection it’ll create a connection to the database um and then i told you that when the plugin reloads or the server stops then this will be essentially reset right because data in a plug-in is not persistent you know variables are not persistent so

With that said i just want to make a little correction here i’m going to add back the on enable method on or on disable rather on disable and inside of here i want to properly close the connection to the database i think that’s the best thing to do just

To make sure everything’s settled and everything everything’s closed and all that stuff we want to close the connection manually when the plugin stops or is reloaded okay i think that’s the best practice and what’s proper so we’re going to do that by well i guess there’s a few ways we can

Do we can just do simply this dot database dot get connection dot close so if there’s ever a connection it will be closed at that point if there wasn’t a connection when this was run then it’ll just create a connection right before closing it so that doesn’t make much sense so we’re

Actually going to go back to database and make a specific method for closing the connection so we’re going to do public void close db or just close connection rather there we go and then for this we’re just going to handle the exception inside of this because we don’t really need that

Information outside of this if it accepts if there’s an exception then whatever at this point because the plug-in’s shutting down anyway so we’ll do if this dot connection is equal to null or how about not equal to null that makes sense then we’ll do this.connection.close and then everything is settled at that

Point if it’s equal to node then do nothing because there’s nothing to close and we don’t want to create a new connection just to close it that doesn’t make any sense at all now just go back here and take this this database dot close connection and boom now the connection’s closed

Everything’s done everything’s proper boom boom boom hopefully that makes sense okay so that’s it for this episode i showed you everything that i wanted to show you for this episode um there’s still a lot of unknowns and uh there’s so there’s still a lot of things i do

Want to show you and we’ll get plenty of experience in the future the next thing i’m probably going to do is of course show you how to connect your database within intellij and then from there i’ll show you how to use an h2 database and also a sqlite database and we’ll get

Some more practice doing some more complex stuff as well but hopefully you found this tutorial interesting i you know i like to be as thorough as possible but sometimes you can only go so far and hopefully you will take this information and apply it to your plugins and then whenever you

Come across something you don’t know how to do you will use the references that i gave you the jenkov tutorials for jdbc as well as the sql tutorials from w3schools and other resources on the interwebs of course so if you have experience with databases and you think there’s something i missed or maybe

There’s something that you want to add to this then drop a comment below and help out your fellow community and all that fun stuff all right and that’s all i got for you guys thanks for watching and peace all right so that’s it for this video thanks for watching in the

Description below i’ll leave a link to the code for this episode so you can check it out you can bookmark it come back to later if you forget any concepts or you just want to review the concepts i taught in this video i’ll mark everything up with comments so you can

Come back and read the code without having to re-watch the video although your views are greatly appreciated so yeah i’ll leave a link for that in the description below so make sure to check it out and another thing is i’ll leave a link to our discord server it’s a big

Community for programmers you can ask for help on your program projects if you’re stuck on something or maybe you can get some new friends if you don’t have any friends there’s lots of people here it’s growing really fast you can get you can find lots of people who are

Passionate about the same things as you for example if you like minecraft spigot development you can find people lots of people who like that if you like c plus plus you like java if you like web development it’s a really really big programming community so feel free to join there’s a link for

That in the description below and the last thing i want to tell you is that if you want to support this channel you can click the join button below this video and you can join this channel as a member for as low as 99 cents a month

And you can cancel at any time you get some cool perks like early access to all of my new videos a cool rank on my discord server like you see right here on the side youtube members and also you get to see yourself on the screen like

You see right now so if that sounds cool to you feel free to join if you don’t want to that’s fine if you can’t that’s okay too um i really just appreciate you watching the video anyway and thank you thanks a lot and that’s it so if you

Like this video leave a like if you want to see more subscribe and peace