Custom Types Tutorial - hudec/sql-processor GitHub Wiki
The next control directives have been changed: pojo to is-pojo, table to is-table, procedure to is-procedure, function to is-function.
Introduction
Custom META types
In the version 1.3 the SQL Processor engine can also handle custom META types. For the basic Simple Sample Tutorial, please see the Simple Tutorial. For the basic Simple CRUD Tutorial, please see the CRUD Tutorial. Next only the custom META types are described.
Sometimes the internal META types can't handle all types of possible transformations between the input/output values and database column types. For example we can have a special Java class to hold phone numbers, like the next one:
public final class PhoneNumber {
private final int area; // area code (3 digits)
private final int exch; // exchange (3 digits)
private final int ext; // extension (4 digits)
public PhoneNumber(int area, int exch, int ext) {
this.area = area;
this.exch = exch;
this.ext = ext;
}
public int getArea() {
return area;
}
public int getExch() {
return exch;
}
public int getExt() {
return ext;
}
// how you're supposed to implement equals
public boolean equals(Object y) {
if (y == this) {
return true;
}
if (y == null) {
return false;
}
if (y.getClass() != this.getClass()) {
return false;
}
PhoneNumber a = this;
PhoneNumber b = (PhoneNumber) y;
return (a.area == b.area) && (a.exch == b.exch) && (a.ext == b.ext);
}
// 0 for padding with leading 0s
public String toString() {
return String.format("%03d-%03d-%04d", area, exch, ext);
}
// satisfies the hashCode contract
public int hashCode() {
return 10007 * (area + 1009 * exch) + ext;
}
On the other side, these phone numbers are stored in the database in the format aaa-bbb-cccc, where aaa is the three digit area code, bbb is the three digit exchange and cccc is the four digit extension. In all queries or CRUD statements there must be special Java plumbing code, which provides the transformation between the Java class based phone numbers, and the phone numbers stored in the database.
For this purposes the SQL Processor enables, in version 1.3, the registration of custom META types. The custom META type is any Java class, which extends SqlTaggedMetaType. There are the next methods, which can be overriden:
-
getClassTypes- returns the list of Java classes, which can hold the custom data types. In the previous example it's PhoneNumber.class. -
getMetaTypes- returns the list of names, which are assigned to this META type, and can be used in the META SQL queries or statements. In the previous example it's phone. -
getProviderSqlType- returns the type provided by the stack on top of which the SQL Processor works. In the previous example it's the meta type instance. -
addScalar- declares a scalar query result, which is an SQL query execution output value. Here are the next parameters:-
typeFactory- the SQL Type factory, like theJdbcTypeFactory -
query- the SQL Engine query, an adapter or proxy to the internal JDBC or ORM staff -
dbName- the name of a database query output, which can be the column name or the alias name -
attributeTypes- the Java types of the attribute in the result class
-
-
setResult- provides the transformation from a database value to an output value. Here are the next parameters:-
runtimeCtx- the public SQLP runtime context -
resultInstance- the instance of the result class, from which the output attribute is taken -
attributeName- the name of the output attribute, which will hold the output value -
resultValue- the output value from the SQL query or the statement execution -
ingoreError- in the case of any problem anSqlRuntimeExceptioncan be thrown, or only the error log can be done
-
-
setParameter- provides the transformation from an input value to a database value. Here are the next parameters:-
runtimeCtx- the public SQLP runtime context -
query- the SQL Engine query, an adapter or proxy to the internal JDBC or ORM staff -
paramName- the name of the parameter for the query/statement execution -
inputValue- the input value, which will be put into the query/statement parameter -
ingoreError- in the case of any problem anSqlRuntimeExceptioncan be thrown, or only the error log can -
inputTypes- the Java types of the input value. This can help in the case the input value is null. be done
-
The example of the custom META type for the phone number is the next one:
package org.sqlproc.engine.type;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.sqlproc.engine.SqlQuery;
import org.sqlproc.engine.SqlRuntimeContext;
import org.sqlproc.engine.SqlRuntimeException;
import org.sqlproc.engine.type.SqlTaggedMetaType;
import org.sqlproc.engine.type.SqlTypeFactory;
import org.sqlproc.sample.simple.model.PhoneNumber;
public class PhoneNumberType implements SqlTaggedMetaType {
/**
* The internal slf4j logger.
*/
final Logger logger = LoggerFactory.getLogger(getClass());
static Pattern pattern = Pattern.compile("^\\(?(\\d{3})\\)?[- ]?(\\d{3})[- ]?(\\d{4})$");
@Override
public Class<?>[] getClassTypes() {
return new Class[] { PhoneNumber.class };
}
@Override
public String[] getMetaTypes() {
return new String[] { "phone" };
}
@Override
public Object getProviderSqlType() {
return this;
}
@Override
public void addScalar(SqlTypeFactory typeFactory, SqlQuery query, String dbName, Class<?>... attributeTypes) {
query.addScalar(dbName, Types.VARCHAR);
}
@Override
public void setResult(SqlRuntimeContext runtimeCtx, Object resultInstance, String attributeName, Object resultValue,
boolean ingoreError) throws SqlRuntimeException {
if (resultValue == null) {
if (runtimeCtx.simpleSetAttribute(resultInstance, attributeName, null, PhoneNumber.class))
return;
if (ingoreError) {
logger.error("There's no getter for " + attributeName + " in " + resultInstance
+ ", META type is PhoneNumberType");
return;
} else {
throw new SqlRuntimeException("There's no setter for " + attributeName + " in " + resultInstance
+ ", META type is PhoneNumberType");
}
}
if (!(resultValue instanceof String)) {
if (ingoreError) {
logger.error("Incorrect result value type " + resultValue + ", it should be a string");
return;
} else {
throw new SqlRuntimeException("Incorrect result value type " + resultValue + ", it should be a string");
}
}
String sPhoneNumber = (String) resultValue;
Matcher matcher = pattern.matcher(sPhoneNumber);
if (!matcher.matches()) {
if (ingoreError) {
logger.error("Incorrect result phone number format '" + sPhoneNumber + "'");
return;
} else {
throw new SqlRuntimeException("Incorrect result phone number format '" + sPhoneNumber + "'");
}
}
int area = Integer.parseInt(matcher.group(1));
int exch = Integer.parseInt(matcher.group(2));
int ext = Integer.parseInt(matcher.group(3));
if (runtimeCtx.simpleSetAttribute(resultInstance, attributeName, new PhoneNumber(area, exch, ext),
PhoneNumber.class))
return;
if (ingoreError) {
logger.error("There's no getter for " + attributeName + " in " + resultInstance
+ ", META type is PhoneNumberType");
return;
} else {
throw new SqlRuntimeException("There's no setter for " + attributeName + " in " + resultInstance
+ ", META type is PhoneNumberType");
}
}
@Override
public void setParameter(SqlRuntimeContext runtimeCtx, SqlQuery query, String paramName, Object inputValue,
boolean ingoreError, Class<?>... inputTypes) throws SqlRuntimeException {
if (inputValue == null) {
query.setParameter(paramName, inputValue, Types.VARCHAR);
} else {
if (inputValue instanceof Collection) {
List<String> phoneNumbers = new ArrayList<String>();
for (Iterator iter = ((Collection) inputValue).iterator(); iter.hasNext();) {
Object o = iter.next();
if (o != null) {
if (!(o instanceof PhoneNumber)) {
if (ingoreError) {
logger.error("Incorrect input value type " + o + ", it should be a PhoneNumber");
continue;
} else {
throw new SqlRuntimeException(
"Incorrect input value type " + o + ", it should be a PhoneNumber");
}
}
String sPhoneNumber = ((PhoneNumber) o).toString();
}
}
query.setParameterList(paramName, phoneNumbers.toArray(), Types.VARCHAR);
} else {
if (!(inputValue instanceof PhoneNumber)) {
if (ingoreError) {
logger.error("Incorrect input value type " + inputValue + ", it should be a PhoneNumber");
return;
} else {
throw new SqlRuntimeException(
"Incorrect input value type " + inputValue + ", it should be a PhoneNumber");
}
}
PhoneNumber phoneNumber = (PhoneNumber) inputValue;
String sPhoneNumber = String.format("%03d-%03d-%04d", phoneNumber.getArea(), phoneNumber.getExch(),
phoneNumber.getExt());
query.setParameter(paramName, sPhoneNumber, Types.VARCHAR);
}
}
}
}
This custom META type has to be registered in the SQL Processor:
JdbcEngineFactory sqlFactory = new JdbcEngineFactory();
sqlFactory.setMetaFilesNames("statements.meta");
sqlFactory.setFilter(SqlFeature.HSQLDB); // the optional database type to improve the SQLP behaviour
sqlFactory.addCustomType(new PhoneNumberType());
To use it, the table CONTACT from the CRUD Tutorial has to be extended:
CREATE TABLE CONTACT
(
ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL
, PERSON_ID BIGINT NOT NULL
, ADDRESS VARCHAR(100) NOT NULL
, PHONE_NUMBER VARCHAR(100)
);
and the class Contact has to have an attribute of type PhoneNumber:
public class Contact {
...
private PhoneNumber homePhone;
...
public PhoneNumber getHomePhone() {
return homePhone;
}
public void setHomePhone(PhoneNumber homePhone) {
this.homePhone = homePhone;
}
...
}
To insert a new contact with the home phone number, we can use the following META SQL statement:
INSERT_CONTACT_CUSTOM(CRUD)=
insert into CONTACT (id, person_id, address, phone_number)
{= values (:id(idgen=SIMPLE_IDENTITY,id=ID), :personId, :address, :homePhone(type=phone)) }
;
We can see there's a new input/output value modifier (type=phone). This is the name we used in PhoneNumberType.getMetaTypes(). In fact the modifier (type=xxxx) assigns the META type xxxx to the related input/output value. The following usage is straightforward:
SqlCrudEngine sqlInsertContact = sqlFactory.getCrudEngine("INSERT_CONTACT_CUSTOM");
Connection connection = DriverManager.getConnection("jdbc:hsqldb:mem:sqlproc", "sa", "");
SqlSessionFactory sessionFactory = new JdbcSessionFactory(connection);
SqlSession session = sessionFactory.getSqlSession();
Contact contact = new Contact("Pepa address 1", new PhoneNumber(111, 222, 3333));
contact.setPersonId(1);
sqlInsertContact.insert(session, contact);
The usage of the custom META types in the queries is a similar one:
ALL_PEOPLE_AND_CONTACTS_CUSTOM(QRY)=
select p.ID @id(id), p.NAME @name, c.ID @contacts.id(id), c.PERSON_ID @contacts.personId, c.ADDRESS @contacts.address,
c.PHONE_NUMBER @contacts.homePhone(type=phone)
from PERSON p left join CONTACT c on p.ID = c.PERSON_ID
{= where
{& c.ID=:id}
{& c.PHONE_NUMBER = :homePhone(type=phone)}
}
{#1 order by p.NAME, c.ADDRESS}
;
We can see there's again the META type usage utilizing the modifier (type=phone).
The sample Simple is available in GIT https://github.com/hudec/sql-processor/tree/master/sql-samples/simple-jdbc.