SQL - Redstonecrafter0/RedstoneAPI GitHub Wiki
The RedstoneAPI provides an interface for sql drivers. By default there are MySQL and SQLite.
// Use a MySQL connection
SQL sql = new MySQL("localhost", 3306, "database", "username", "password");
// Or use a SQLite3 file.
SQL sql = new SQLite("database.db");
// Or use a SQLite3 database in memory (for example for testing)
SQL sql = new SQLite(SQLite.MEMORY);The SQL-interface provides 4 methods to control the sql database.
The update method is only recommended to by used for creating and deleting tables. It is NOT recommended to use it to insert, delete or update values.
// Create a new table
sql.update("CREATE TABLE IF NOT EXISTS table (uuid text, username text)");The query method is NOT recommended to query for a specific rows. It is recommended for query an entire table.
ResultSet rs = sql.query("SELECT * FROM table");This method is used for both updates and querys.
To insert rows you can use following example. This works the same for updates and other update requests.
// Use a '?' as placeholder for your values
PreparedStatement ps = sql.prepareStatement("INSERT INTO table VALUES (?, ?)");
// Set the values for the update with index starting at 1
ps.setString(1, "uuid");
ps.setString(2, "username");
// Execute the update
ps.executeUpdate();To query rows you can use following example.
// Use a '?' as placeholder for your values
PreparedStatement ps = sql.prepareStatement("SELECT * FROM table WHERE uuid = ?");
// Set the values for the update with index starting at 1
ps.setString(1, "uuid");
// Execute the query
ResultSet rs = ps.executeQuery();This method is used to safely close the database connection.
sql.close();If you don't like to write SQL commands on your own you can leave them alone. With orm you need to define a class for every table which must be set up a bit. You can see the table classes as tables on your database and the instances of the classes as the entries of the table. If you want to catch every exception from ORM you can catch the ORMException.
To define a table class you must extend TableBase and add a public constructor without any parameters which executes init().
Execute init() also on every other contructor as first if you make some.
The tablename is the classname.
Columns are defined by annotation where one primarykey is needed which must be of type Int.
NotNull and unique are self explaining and from the normal SQL syntax.
public class TestTable extends TableBase {
public TestTable() {
init();
}
@Column(primaryKey = true)
public Int id = new Int();
@Column
public Text value = new Text();
}Because of the WebServer you can use this for apis or other things.
TestTable entry;
JSONObject obj = entry.toJsonObject();Thery are some types that are normal SQL types but also some wrapped ones.
The BigInt represents a java Long. The Blob represents a java byte[]. The Bool represents a java Boolean. The Int represents a java Integer. The SQLDouble represents a java Double. The SQLFloat represents a java Float. The SQLUUID represents a java UUID. The Text represents a java String. The VarChar represents a java String with a specified maximal length. ```public VarChar value = new VarChar(10);
To interact at any point with the database using ORM you need the session.
// Create a SQL instance (e.g. with SQLite)
SQL sql = new SQLite("data.db");
// Create a Session
Session session = new Session(sql);
// Create table
session.addTable(TestTable.class);
// Flush changes to the database.
session.commit();
// To add an entry just create the object and set the values if necessary.
TestTable entry = new TestTable();
entry.value.setValue("string");
// Add entry to the database.
session.add(entry);
session.commit();
// Querying the whole table on the database. For querying commiting is useless.
Query<TestTable> query = session.query(TestTable.class);
// Querying using a semantic filter. The first parameter of the filters are just to identify the column and the value is ignored because the value to compare to is in the second parameter.
Query<TestTable> query = sessiong.query(TestTable.class, Filter.equals(entry.value, "string").or(Filter.equals(entry.id, 1)).and(Filter.equalsNot(entry.value, "i know this filter is useless but just to show the chain")));
// It is allow once to get data from the query. Every more times an exception is raised.
TestTable entry = query.getFirst(); // Get the first entry
TestTable[] entries = query.getAll(); // Get all matching the filter or without filter the whole table.
List<TestTable> entries = query.getAllAsList(); // Get all matching the filter or without filter the whole table but as List<>.
// Because the RedstoneAPI has a WebServer it is possible to get a JSONObject or JSONArray from it. This can be used for apis.
JSONArray arr = query.getAsJsonArray(); // Get every match as JSONObject collected in a JSONArray.
JSONObject obj = query.getAsJsonObject(entry.id); // The same as with the JSONArray but JSONObjects have keys und you cen define which column to use for the key.
// Updating entries is very simple but you might leave the primary key where it is since it is used to identify the entry.
TestTable entry = session.query(TestTable.class).getFirst(); // First get something you can update. and don't use not queryied entries for updating.
entry.value.setValue(entry.value.getValue() + " lol");
session.update(entry);
session.commit();
// You can delety a queryied entry or by filters. If you want to delete everything in the table find a filter which is always matching like equals or equalsNot.
session.delete(entry);
session.delete(TestTable.class, Filter.like(entry.value, "%lol%"));
session.commit();
// There is also a count query which is special since there is no need for a Query<> object.
int count = session.count(TestTable.class);
int count = session.count(TestTable.class, Filter.less(entry.id, 5));