Sync SQL
Sync provides a simple and user friendly SQL interface which supports both MySQL and SQLite.
If you are not familiar with SQL query syntax then it is highly recommended to learn the basics before using the library.
Some useful resources:
All SQL related methods can be accessed trough the SyncSQL class.
There are 2 constructors for SyncSQL. First and foremost is the SQLite contstructor.
**public SyncSQL(File f);**
SyncSQL sql = new SyncSQL(f);
This constructor only takes one parameter which is the file where your SQLite database will be created.
Secondly the MySQL constructor.
**public SyncSQL(String host, String database, String username, String password);**
SyncSQL sql = new SyncSQL("localhost", "syncdb", "admin", "1234");
As can be seen the constructor to create a MySQL takes 4 parameters. The host will normally be 'localhost' unless you are using an external database. The database is the name of your database. And username and password as your username and password details for access to the database. The username you choose should preferably be the admin unless you want to restrict the SQL operations which can be made.
Once you have created the class you will need to call the initialise() method which will open a connection to the database. This has the same effect as refreshConnection() which is called on every query to reopen a connection to the database if the existing one is closed.
sql.initialise();
For SQLite when calling initialise() if the file for the database does not exist it will be create automatically for you. However you should always ensure the file and relative directories are created before hand.
If you are not going to be making frequent calls to the database it would save resources to close the connection and then refresh the connection when you need to make another call to the database.
Built into SyncSQL is a method to check if a table exists. To create the table use the standardQuery(String query) method. Demonstrated below.
public void onEnable(){
File f = new File("plugins" + File.separator + "Sync" + File.seperator + "Database.db");
SyncSQL sql = new SyncSQL(f); //Create a new SQLite object.
sql.initialise(); //We need to open a connection before we can do anything!
if(!sql.doesTableExist("sync")){
System.out.println("Table 'sync' does not exist! Creating table...");
sql.standardQuery("CREATE TABLE sync ('id' INTEGER PRIMARY KEY, 'values' VARCHAR(50));");
System.out.println("Table 'sync' created!");
} else {
System.out.println("Table 'sync' already exists!");
}
}
There are two methods for executing SQL statments. One is void 'standardQuery(String query)' and the other returns a ResultSet 'sqlQuery(String query)'.
If you hadn't already guessed sql.standardQuery("my query"); should be used for any queries which do not return a ResultSet such as INSERT, UPDATE, CREATE TABLE etc...
And sql.sqlQuery("my query"); should be used for queries which return a ResultSet such as SELECT. Remember when you are finished processing the data inside a ResultSet you should close it to immediately free up resources. This is incredibly important for SQLite databases which don't allow multiple connections - not so much for MySQL but it is still good practice.
A model example of an SQL query which returns a result set.
ResultSet set = sql.sqlQuery("SELECT * FROM mytable");
while(set.next()){
System.out.println(set.getString("Some Field"));
//do other stuff?
}
set.close(); //We are done with the ResultSet so lets close it.
For standard SQL queries which don't return anything this is not necessary as the statement closes itself after.
A model example of a Standard SQL query.
sql.standardQuery("INSERT INTO mytable (somefield) values ('somevalue');");
//No need for further calls as the statement closes itself.