{% extends "base.html" %} {% block title %}Relational databases: JDBC{% endblock %} {% block body %}

Relational databases: JDBC

Siena-JDBC lets you use the Siena API using a relational database as underlaying persistence mechanism.

Generate and update the database schema

Optionally you can generate or update the database schema using siena.jdbc.ddl.DdlGenerator. You must use the following annotations to define the tables, columns and indexes of your database:

The DDL generation is made using Apache DdlUtils so you will need to have Apache DdlUtils downloaded and added to your classpath. The following is an example of how to use DdlGenerator and Apache DdlUtils.

DdlGenerator generator = new DdlGenerator();

// add all your classes
generator.addTable(Employee.class);

// get the Database model
Database database = generator.getDatabase();

Platform platform = PlatformFactory.createNewPlatformInstance("mysql");
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.
	getConnection("jdbc:mysql://localhost/database_name", "user", "pass");

// if you want to print the SQL that will be executed
String sql = platform.getAlterTablesSql(connection, database);
System.out.println(sql);

// this will perform the database changes
platform.alterTables(connection, database, true);

connection.close();

The following is a possible output of the example code:

CREATE TABLE employees
(
    id BIGINT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(200) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    contact_info MEDIUMTEXT NULL,
    boss BIGINT,
    PRIMARY KEY (id)
);

CREATE INDEX boss_index ON employees (boss);

Apache DdlUtils has the ability not only to create the tables but also to synchronize them. So if you add or remove something in your model classes DdlUtils will execute ALTER TABLE statements. But be careful because some changes may cause the data to be lost. For example if you add a mandatory column the data could not be retained. Make always a backup of the important data before synchronizing the database schema.

If you want to add or remove some constraints on the database model you can manipulate the Database object before executing the synchronization. In addition you should know that the addTable() method returns a Table object. And finally you can access a certain table by using ddlGenerator.getTable(tableName).

Note: foreign keys are not supported yet (this will be fixed soon).

{% endblock %}