Databases

In todo4teams custom databases are used to store solution-related structured data, such as the collection of spam sender addresses, customer data, etc.

Access to this data is extremely fast and you can determine the structure of the tables yourself. The Apache Derby database used here supports indexes, referential integrity and all other features that you would expect from an SQL database.

To use a custom database in scripts, please follow these steps:

  • Select the "Server Properties" tab.
  • Click "New Property" to store a new property
  • Enter the name of the new database as the key - here, for example, MyCustomDB (see below)
  • As the 'Value' specify the name of the database and the directory in which the database is to be saved (see example):
    Please note that the database directory - here: /home/databases - must exist and be writable by the executing user of the Tomcat application server!
  • Then use the new database in your scripts as follows: The database name to use here is the one you entered above as the "key". Use the database like any other JDBC database in Java. This is a Derby database. You can find more Derby-specific information here: https://db.apache.org/derby/docs/10.0/manuals/reference/sqlj02.html

image-20241217152959-2.png

To directly control your new database select 'Databases' in the main menu and select your new database ('MyCustomDB') from the drop down menu:

image-20241217152752-1.png

Copy the following lines into the text area and click 'execute':

CREATE TABLE Student (
   Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
   Age INT NOT NULL,
   FirstName VARCHAR(255),
   LastName VARCHAR(255),
  PRIMARY KEY (Id)
);

You just created a first table in your database!

Script code to store new rows in this table mght look like this:

var con  = helper.getCustomDB("MyCustomDB");
var stmt = con.prepareStatement("INSERT INTO Student (age, FirstName, LastName) VALUES (?,?, ?)");
stmt.setString(1, 23);
stmt.setString(2, "Max");
stmt.setString(3, "Meier");

try{
 stmt.executeUpdate();
if (!con.getAutoCommit()){
   c.commit();
 }

}
catch(de){
 // collision!
  println(de.getMessage());
}
finally{
 stmt.close();
}

Check the contents of your table using the 'Databases' tab: Enter 'SELECT * FROM Student;' and press 'Execute' to list all rows of table 'Student':

image-20241217154137-3.png

Don't forget to backup the directory containing your databases!