What is SQLiteLib?
Many users on spigot have used my tutorial How to: SQLite. I noticed I had a ton of users adding me on Discord looking for help. They found the code too difficult to use or simply didnt understand how it worked. This library is aimed to fix this. SQLiteLib gives the basic programmer all the tools they need to:
- Create Databases
- Query Single Values
- Query List of values
- Query Map of lists
- Execute SQL statements
Commands:
/sqlite or /sl - base command
/sl execute {database} {statement} - Execute a statement on database
/sl init {database} {initial_statement} - Create/Initiate database
/sl queryvalue {database} {row} {statement} - Print the queried value.
/sl queryRow {database} {row} {statement} - Print all queried values.
Permissions:
sqlite.use - Use SQLiteLib commands
Storage:
All database files are stored in the SQLiteLib folder within your plugins folder. You can manually edit/delete databases from there.
Suggestions & Support:
If you have any suggestions, bugs, or need help, please post in the discussion of the resource! You may also join my discord for instant support:
https://discord.gg/DpNFSQu
Hooking into SQLiteLib:
Simply download the jar anywhere on your PC and add it to your project's build path. Once added, you can gain access to the library by using the following:
Code:
public SQLiteLib sqlLib;
@Override
public void onEnable() {
sqlLib = SQLiteLib.hookSQLiteLib();
}Initializing Databases:
Once you've hooked into SQLiteLib, you can start using all the tools. First, we need to make sure we create/initialize a database.
Code:
sqlLib.initializeDatabase("database_name", "CREATE TABLE IF NOT EXISTS table_name");NEW AS OF 1.3:
This will force SQLiteLib to create the database file in your plugins folder.
Code:
sqlLib.initializeDatabase(pluginInstance, "database_name", "CREATE TABLE IF NOT EXISTS table_name");You can see, it requires 2 arguments. The database name & initial statement. The initial statement should be used to create your tables/rows/columns. This is just a basic create statement however they can be much more advanced if you understand SQL. This code should be ran every time your plugin requires a database. This method will create a database if it does not exist, but will also load the database if it does exist. It is required to use any other database methods. If the database isnt initialized, nothing else will work.
Executing Statements:
There may come a time during your SQL experience which requires you to execute ANY statement possible. I did not want to limit the developers using SQLiteLib, so I added this handy method in case you need to execute special statements which aren't usable in other methods.
There may come a time during your SQL experience which requires you to execute ANY statement possible. I did not want to limit the developers using SQLiteLib, so I added this handy method in case you need to execute special statements which aren't usable in other methods.
Code:
if (sqlLib.getDatabase("name").executeStatement("statement")){
// Executed statement successfully
}else{
// Execution failure.
}Querying single values:
You can easily query for any singular value using the following code:
You can easily query for any singular value using the following code:
Code:
String testValue = (String)sqlLib.getDatabase("test").queryValue("statement", "row");Notice how I am casting the returned statement as a string, as each returned value is an Object. As long as you cast properly, and don't attempt to cast random objects to other objects, you should be fine.
Here's an example of a database and query statement.
test.db:
-------
| ID |
-------
| 1 |
| 2 |
| 3 |
-------
You can see this is a very simple table. It has 1 column called ID which contains their keys. 1-3. This can be casted to an Integer, Double, String, whatever you need basically. Anyways, here's my code to retrieve a single value.
Code:
String test = (String)sqlLib.getDatabase("test").queryValue("SELECT * FROM test WHERE id = 1", "ID");You can see, I specify a statement that returns 1 value. I also specify the row I would like to read from the result, in this case, ID. You could also simply put "SELECT * FROM test, however, it would only return the first item in the list. Try to make your statements capture only 1 value, rather than a list.
Querying Row:
If you'd like to query a list of results in a single row, you can use the following code. This will return List<Object>.
If you'd like to query a list of results in a single row, you can use the following code. This will return List<Object>.
Code:
List<Object> results = sqlLib.getDatabase("test").queryRow("SELECT * FROM test", "ID");
for (Object obj : results){
String ID = (String)obj;
System.out.println("ID: "+ID);
}Query Map of Rows:
If you'd like to gather several values from rows, you can use queryMultipleRows. This will put together a map, which contains the row name, and a list of values. Map<row_name, List<Object>>.
If you'd like to gather several values from rows, you can use queryMultipleRows. This will put together a map, which contains the row name, and a list of values. Map<row_name, List<Object>>.
Code:
Map<String, List<Object>> rows = sqlLib.getDatabase("test").queryMultipleRows("SELECT * FROM test", "row1", "row 2", "row 3" );
String ID = (String)rows.get("test").get(0);If you need more help, join this server! https://discord.gg/DpNFSQu
