10 minutes tutorial
The next control directives have been changed: pojo to is-pojo, table to is-table, procedure to is-procedure, function to is-function.
IDE Setup
Project creation
IDE usage
Change database type
More samples
The SQL Processor (SQLP) is the runtime engine processing the META SQL statements. The SQL Processor Meta Plugin (SQLMEP) enables smart edition of the SQLP primary artifacts, the META SQL statements. The SQL Processor Model Plugin (SQLMOP) enables smart edition of the POJO and DAO models and the Java code generation. Next you can find a tutorial, how to setup a simple JDBC application using SQLP, SQLMEP and SQLMOP in a couple of minutes.
How to setup the IDE can be found at IDE Setup and Coding Standards.
There are two Maven archetypes, we can use to establish the basic application infrastructure - one for JDBC stack and one for Spring stack (for more info please see Stacks Tutorial). Next we are going to create the simple JDBC application:
mvn archetype:generate -DarchetypeGroupId=org.sqlproc -DarchetypeArtifactId=sqlproc-archetype-simple-jdbc -DgroupId=org.sample -DartifactId=simple -Dversion=1.0-SNAPSHOT -Dpackage=org.sample -DarchetypeVersion=3.1.5
Of course, the supplied values can be changed based on the target application. For more info regarding the Maven archetypes, please see http://maven.apache.org/archetype/maven-archetype-plugin/.
Next we should import the created simple application as a Maven Project into IDE (STS). The basic layout is the next one
The key resources are
-
definitions.meta- control directives for SQLMEP -
definitions.model- control directives for SQLMOP (in most cases both files are identical) -
hsqldb.ddl- the DDL script used to establish the DB layout
The rest artifacts are generated from this DB model
-
pojo.model- POJO model and the related Java classes in src-gen:Contact,Person,AnHourBefore,NewPerson,NewPersonRetRs -
dao.model- DAO model and the related Java classes in src-gen:ContactDao,PersonDao,AnHourBeforeDao,NewPersonDao,NewPersonRetRsDao -
statements.meta- META SQL statements - CRUD and SELECT commands
So the only manually written code is Java class Main. All artifacts are described in the Wiki Tutorials located at Tutorials.
We'd like to use the same simple DB layout, but this time in Oracle. The DDL script oracle.ddl should be located in the same directory, as definitions.model:
DROP SEQUENCE SIMPLE_SEQUENCE
DROP TABLE CONTACT CASCADE CONSTRAINTS
DROP TABLE PERSON CASCADE CONSTRAINTS
CREATE SEQUENCE SIMPLE_SEQUENCE INCREMENT BY 1 START WITH 100
CREATE TABLE PERSON (
ID NUMBER(19) NOT NULL,
FIRST_NAME VARCHAR2(100) NOT NULL,
LAST_NAME VARCHAR2(100) NOT NULL,
DATE_OF_BIRTH DATE,
SSN VARCHAR2(100)
)
ALTER TABLE PERSON ADD CONSTRAINT PK_PERSON
PRIMARY KEY (ID)
CREATE INDEX IX_PERSON_LAST_NAME ON PERSON (LAST_NAME)
CREATE TABLE CONTACT (
ID NUMBER(19) NOT NULL,
PERSON_ID NUMBER(19) NOT NULL,
ADDRESS VARCHAR2(100),
PHONE_NUMBER VARCHAR2(100)
)
ALTER TABLE CONTACT ADD CONSTRAINT PK_CONTACT
PRIMARY KEY (ID)
ALTER TABLE CONTACT ADD CONSTRAINT FK_CONTACT_PERSON
FOREIGN KEY (PERSON_ID) REFERENCES PERSON (ID) ON DELETE CASCADE
CREATE OR REPLACE PROCEDURE new_person (newid OUT NUMBER, date_of_birth IN DATE, ssn IN VARCHAR2, first_name IN VARCHAR2, last_name IN VARCHAR2)
IS
BEGIN
-- DECLARE
BEGIN
SELECT simple_sequence.nextval INTO newid FROM dual;
INSERT INTO PERSON (ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, SSN)
VALUES (newid, first_name, last_name, date_of_birth, ssn);
END;
END new_person;
CREATE OR REPLACE FUNCTION NEW_PERSON_RET_RS (date_of_birth IN DATE, ssn IN VARCHAR2, first_name IN VARCHAR2, last_name IN VARCHAR2)
RETURN SYS_REFCURSOR
AS
result_cur SYS_REFCURSOR;
BEGIN
DECLARE
newid NUMBER(19);
BEGIN
SELECT simple_sequence.nextval INTO newid FROM dual;
INSERT INTO PERSON (ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, SSN)
VALUES (newid, first_name, last_name, date_of_birth, ssn);
OPEN result_cur FOR SELECT * FROM PERSON WHERE ID = newid;
RETURN result_cur;
END;
END new_person_ret_rs;
CREATE OR REPLACE FUNCTION AN_HOUR_BEFORE (t IN DATE)
RETURN DATE
AS
BEGIN
RETURN t - INTERVAL '1' HOUR;
END an_hour_before;
We can use for example the local Oracle XE (or any other Oracle instance), as it's described at Catalog (Spring) Sample. The same is true regarding Oracle JDBC driver, which should be installed in local Maven repository.
Next we have to add Oracle JDCB driver to the sample pom.xml:
<properties>
...
<version.oracle>11.2.0.3</version.oracle>
</properties>
<dependencies>
...
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>${version.oracle}</version>
</dependency>
...
</dependencies>
and we have to modify the both definitions.model and definitions.meta (or the second file can be a link to the first one) to create a link from SQLEP to Oracle instance:
resolve-pojo-on;
database-is-online;
database-jdbc-driver oracle.jdbc.OracleDriver;
database-has-url "jdbc:oracle:thin:@127.0.0.1:1521:xe";
database-login-username "simple";
database-login-password "simple";
database-active-schema SIMPLE;
database-ddl-create oracle.ddl; // should be located in the same directory as definitions.model
The username/password/schema can be modified to reflect the real values in your Oracle DB instance. Also, in the case the DB layout is created manually, we can comment the control directive database-ddl-create (in this case the oracle.ddl is not required).
Also, as we are going to use the sequence instead of the identity, we have to add the next snippet to definitions.model
metagen-global-sequence SIMPLE_SEQUENCE;
After all these changes the best approach is to restart the IDE. The basic layout is the next one
Just to verify the DB layout is established, delete the next two rows from definitions.model
is-table contact CONTACT;
is-table person PERSON;
and press CRTL-SPACE and use the template tables to re-create both lines.
Due to another procedures and functions treatment, the next control directives should replace the original ones in definitions.model
is-procedure anHourBefore AN_HOUR_BEFORE;
is-procedure newPerson NEW_PERSON;
is-procedure newPersonRetRs NEW_PERSON_RET_RS;
is-function anHourBefore AN_HOUR_BEFORE;
is-function newPersonRetRs NEW_PERSON_RET_RS;
metagen-function-result AN_HOUR_BEFORE stamp;
metagen-function-result-set NEW_PERSON_RET_RS PERSON;
Next delete in pojo.model everything between package org.sample.model { and the final }, put the cursor before the final }, press CRTL-SPACE and use the template pojogen to re-create POJOs. All related Java classes in src-gen are re-created.
Similarly delete in dao.model everything between package org.sample.dao { and the final }, put the cursor before the final }, press CRTL-SPACE and use the template daogen to re-create DAOs. All related Java classes in src-gen are re-created.
Finally delete in statements.model all the content, press CRTL-SPACE and use the template metagen to re-create META SQL statements.
The next step is to replace in Main Java class the constant values with the next ones
private static final Driver JDBC_DRIVER = new oracle.jdbc.OracleDriver();
private static final String DB_URL = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
private static final String DB_USER = "simple";
private static final String DB_PASSWORD = "simple";
private static final String DB_TYPE = SqlFeature.ORACLE;
private static final String DB_DDL = "oracle.ddl";
private static final String[] DB_CLEAR = new String[] { "delete from contact", "delete from person" };
The username/password/schema should be modified to reflect the real values in your Oracle instance.
###More samples At https://github.com/hudec/sql-processor/tree/master/simple-samples you can find the same simple samples for the next databases- DB2
- H2
- HSQLDB
- Informix
- MSSQL
- MySQL
- Oracle
- PostgreSQL