{% extends "base.html" %} {% block title %}Relational databases: JDBC{% endblock %} {% block body %}
Siena-JDBC lets you use the Siena API using a relational database as underlaying persistence mechanism.
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:
@NotNull
. A field annotated with @NotNull
will have a restriction of
NOT NULL
in the database. Fields that are primitive types (int
,
boolean
,...) and fields annotated with
@Id
are NOT NULL
by default.@Max
defines the maximum number of characters of a String
field.
@Max(100)
will create a column of type VARCHAR(100)
.@Text
. Fields of type String
must be annotated with @Max
or with @Text
. A field annotated with @Text
will create a
column of type java.sql.Types.LONGVARCHAR
Json
will create columns of type java.sql.Types.LONGVARCHAR
.java.util.Date
fields can be annotated with one of these:
@DateTime
, @SimpleDate
or @Time
. This will create
columns of type DATETIME
, DATE
or TIME
respectively. By default a java.util.Date
field will create a
DATETIME
column.@Index
annotation lets you define indexes. Indexes have a mandatory name.
You can create an index on multiple columns: you just need to put the @Index
annotatio with the same index name in multiple fields. If you need a column to be
in more than one index you have to pass a String
array to the annotation. Exmample:
@Index({"index_1", "index_2"}) public String someColumn;
.@Unique
has the same usage than the @Index
annotation
except that it will generate unique indexes.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 %}