7Facette uses the standard database connectivity API (JDBC) to provide easy database access. You can create use prepared statements, which represents the SqlStatement object or you can read and parse your sql files.

Holds the basic Database configuration parameter:

• url - Database url
• driver - Database driver
• use - Database user
• password - Database password
• autoCommit - Auto commit after execution, default value is true
• stopOnError - Stops execution when error occurs, default value is false

The configuration in the YML file looks like the following for a DB2 example.

sevenFacette:
  database:
    db2:
      url: jdbc:h2:mem:demo;DB_CLOSE_DELAY=-1
      driver: org.h2.Driver
      user: username
      password: '[[ Password_as_Environment_Variable ]]'
      autoCommit: false
      stopOnError: true

// And then you can create the database connection in this way: 
Database database = DFactory.createDatabase("db2");

DatabaseConfig dbConfig = new DatabaseConfig(url, driver, user, password, autoCommit, stopOnError);
Database database = DFactory.createDatabase(dbConfig)

The SqlStatement is a form of database statement that is precompiled in the database system without values and only needs to be provided with the desired values. This has the advantage that repeated statements, especially in loops, can be executed considerably faster.

// Create a new basic SqlStatement
SqlStatement sqlStatement = new SqlStatement("SELECT * FROM person WHERE name = ? AND age = ?");
// Replace the placeholder, pay attention to the order!
sqlStatement.replaceAllePlaceholder("Peter", 25);
// Validate if the statement contains any placeholder: 
sqlStatement.validatePreparedStatement()

You can create a statement object and replace all placeholder within one line.

// Create a new SqlStatement and replace all placeholder
SqlStatement sqlStatement = new SqlStatement("SELECT * FROM person WHERE name = ? AND age = ?", "Peter", 25);

Execute a sql query without getting back a result set. Like Insert, Update and Delete statement

database.executeSqlStatement(sqlStatement);

or you can get back a result set as Entity or as JSON when you execute a Select statement.

// Resultset as Entity 
Person persons = database.executeSqlStatement(sqlStatement, Person.class);

// Resultset as JSON 
JSONArray json = database.executeSqlStatement(sqlStatement);

The sql ScriptReader is used to generate DbStatements objects out of sql files. To generate a DbStatements object, initialize ScriptReader with the respective path of the SQL script and generate the DbStatement object by executing the getStatements(sqlScript: String) method. Pass the full name of the script here as a parameter.

DbStatements sqlStatements = new DbStatements();
sqlStatements = new ScriptReader().getStatements("Path to SQL-File");