Home Original page

ObjectiveSQL Tutorial

1 Preface

ObjectiveSQL is an ORM framework, which is not only the best practice of "Active Record" pattern in Java, but also provides a perfect solution for complex SQL programming. It makes Java codes and SQL statements organic combination, changes the traditional SQL programming model (strings programming model).

The Objective-SQL project contains two parts: one is the dependency of maven objective-sql or objsql-springboot , with the basic ORM features and SQL programming model, and the other is the IntelliJ IDEA plug-in, compatible with Java operator overloading and code completion.

Features:

  • Dynamical codes generations: with one annotation your Class has a fully featured SQL programming capability
  • Java API of SQL: abstracting and modeling the elements of the SQL language, integrating SQL and Java. Easy to encapsulating, programmed and unit testing
  • Expression syntax consistency: Java syntax and SQL syntax equivalent replacement, including: arithmetic, function calls, compared with the logic calculation expression

2 Dependencies installation

2.1 IntelliJ IDEA plug-in installation

Preferences/Settings -> Plugins -> Search with "ObjectiveSql" in market -> Install

2.2 Maven integration

<!-- In standalone -->
<dependency>
    <groupId>com.github.braisdom</groupId>
    <artifactId>objective-sql</artifactId>
    <version>{objsql.version}</version>
</dependency>
<!-- In Spring Boot, you need add spring-jdbc dependency before -->
<dependency>
  <groupId>com.github.braisdom</groupId>
  <artifactId>objsql-springboot</artifactId>
  <version>{objsql.version}</version>
</dependency>

2.3 Maven compiler configration

<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-compiler-plugin</artifactId>
    <version>3.8.0</version>
    <configuration>
        <source>8</source>
        <target>8</target>
        <encoding>UTF-8</encoding>
        <compilerArgs>
            <arg>-Xplugin:JavaOO</arg>
        </compilerArgs>
        <annotationProcessorPaths>
            <path>
                <groupId>com.github.braisdom</groupId>
                <artifactId>objective-sql</artifactId>
                <version>${objsql.version}</version>
            </path>
        </annotationProcessorPaths>
    </configuration>
</plugin>

The latest versions of the ObjectiveSQL and ObjSqlSpringBoot can be found in Maven Central.

3 Database connection injection

3.1 In standalone

Injecting the ConnectionFactory into the ObjectiveSQL, based on MySQL

private static class MySQLConnectionFactory implements ConnectionFactory {

    @Override
    public Connection getConnection(String dataSourceName) throws SQLException {
        try {
            String url = "jdbc:mysql://localhost:4406/objective_sql";
            String user = "root";
            String password = "******";
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            throw e;
        } catch (Exception e) {
            throw new IllegalStateException(e.getMessage(), e);
        }
    }
}
Databases.installConnectionFactory(new MySQLConnectionFactory());

The dataSourceName of method getConnecition is for multiple data sources, you can ignore it if one data source. You don't forget to inject it into Databases.

3.2 In Spring Boot

spring:
  profiles:
    name: objective-sql-example
    active: development

  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:4406/objective_sql
    username: root
    password: ******
    hikari:
      idle-timeout: 10000
      maximum-pool-size: 10
      minimum-idle: 5
      pool-name: Master
    
    # Configurations for multiple databases
    extensions:
      # The name of data source, which will match with @DomainModel definition 
      slave:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:4406/objective_sql
        username: root
        password: ******
        hikari:
          idle-timeout: 10000
          maximum-pool-size: 10
          minimum-idle: 5
          pool-name: Slave

The tag extensions is used in multiple data sources, and the tag slave is name of other data source, which is match with definition in domain model

4 Simple SQL programming guide

4.1 Definition of domain model

@DomainModel
public class Member {
    @Size(min = 5, max = 20)
    private String no;
    
    @Queryable
    private String name;
    private Integer gender;
    private String mobile;
  
    @Transient
    private String otherInfo;

    @Relation(relationType = RelationType.HAS_MANY)
    private List<Order> orders;
}

4.2 Querying

Member.countAll();
Member.count("name = ?", "braisdom");

Member.queryByPrimaryKey(1);
Member.queryFirst("id > ?", 1);
Member.query("id > ?", 1);
Member.queryAll();

4.3 Persistence

Member.create(newMember);
Member.create(newMember, true); // Create a member without validating
Member.create(Member.newInstanceFrom(memberHash));
Member.create(new Member[]{newMember1, newMember2, newMember3}, false);

Member.update(1L, newMember, true); // Update a member by primary key and skip validationg
Member.update("name = ?", "name = ?", newName, oldName);

Member.destroy(1L); // Delete a member by primary key
Member.destroy("name = ?", "Mary");

4.4 Transaction

4.4.1 Transaction based on annotation

// The method will be executed in a database thransaction
@Transactional
public static void makeOrder(Order order, OrderLine... orderLines) throws SQLException {
  Order.create(order, false);
  OrderLine.create(orderLines, false);
}

4.4.2 Transaction manually

// Transaction executing manually
Databases.executeTransactionally(((connection, sqlExecutor) -> {
  	Member.update(1L, newMember, true);
	  Member.update("name = ?", "name = ?", newName, oldName);
    return null;
}));

4.5 Relation querying

Member.queryAll(Member.HAS_MANY_ORDERS);
Member.queryFirst("id > ?", Member.HAS_MANY_ORDERS, 1);
Member.query("id > ?", Member.HAS_MANY_ORDERS, 1);

Member.queryByPrimaryKey(1, Member.HAS_MANY_ORDERS);
Member.queryByName("braisdom", Member.HAS_MANY_ORDERS);

4.6 Paged querying

// Create a Page instance with current page and page size
Page page = Page.create(0, 10);
PagedList<Member> members = Member.pagedQueryAll(page, Member.HAS_MANY_ORDERS);
PagedList<Member> members = Member.pagedQuery(page, "name = ?", "braisdom");

4.7 Customized query with Query interface

Query query = Member.createQuery();
query.project("name").groupBy("name").having("COUNT(*) > 0").orderBy("name DESC");

List<Member> members = query.execute(Member.HAS_MANY_ORDERS);

// Paged querying with querying dynamically
Paginator paginator = Databases.getPaginator();
Page page = Page.create(0, 10);
PagedList<Member> pagedMembers = paginator
  					.paginate(page, query, Member.class, Member.HAS_MANY_ORDERS);

4.8 Validation

The Jakarta Bean Validation is integrated into ObjectiveSQL

See moreļ¼šhttps://beanvalidation.org/

4.8.1 validate manually

Member newMember = new Member()
        .setNo("100")
        .setName("Pamela")
        .setGender(1)
        .setMobile("15011112222");

// Violations occurred in field 'no'
Validator.Violation[] violations = newMember.validate();

4.8.2 validate on object creating

Member newMember = new Member()
        .setNo("100000")
        .setName("Pamela")
        .setGender(1)
        .setMobile("15011112222");

Member.create(newMember); 
Member.create(newMember, true); // Skip validation

4.9 Customized SQL querying

Member.execute("DELETE FROM members WHERE name = ?", "Pamela");

5 Complex SQL programming guide

5.1 JOIN

5.1.1 Implicit join

Member.Table member = Member.asTable();
Order.Table order = Order.asTable();

Select select = new Select();

select.project(member.no, member.name, count().as("order_count"))
        .from(member, order)
        .where(member.id.eq(order.memberId))
        .groupBy(member.no, member.name);

List<Member> members = select.execute(Member.class);
SELECT `T0`.`NO` , `T0`.`name` , COUNT(*) AS `order_count`
FROM `members` AS `T0`, `orders` AS `T1`
WHERE (`T0`.`id` = `T1`.`member_id` )
GROUP BY `T0`.`NO` , `T0`.`name`

5.1.2 Explicit join

Member.Table member = Member.asTable();
Order.Table order = Order.asTable();

Select select = new Select();

select.project(member.no, member.name, count().as("order_count"))
        .from(member)
        .leftOuterJoin(order, order.memberId.eq(member.id))
        .groupBy(member.no, member.name);

List<Member> members = select.execute(Member.class);
SELECT `T0`.`NO` , `T0`.`name` , COUNT(*) AS `order_count`
FROM `members` AS `T0`
LEFT OUTER JOIN `orders` AS `T1` ON (`T1`.`member_id` = `T0`.`id` )
GROUP BY `T0`.`NO` , `T0`.`name`

5.2 Paged querying

Member.Table member = Member.asTable();
Order.Table order = Order.asTable();

Paginator<Member> paginator = Databases.getPaginator();
Page page = Page.create(0, 20);

Select select = new Select();

select.project(member.no, member.name, count().as("order_count"))
        .from(member, order)
        .where(member.id.eq(order.memberId))
        .groupBy(member.no, member.name);

PagedList<Member> members = paginator.paginate(page, select, Member.class);
-- Counting SQL
SELECT COUNT(*) AS count_
FROM (
	SELECT
		`T0`.`NO`,
		`T0`.`name`,
		COUNT(*) AS `order_count`
	FROM `members` AS `T0`, `orders` AS `T1`
	WHERE (`T0`.`id` = `T1`.`member_id`)
	GROUP BY `T0`.`NO`, `T0`.`name`
) T
-- Querying SQL
SELECT `T0`.`NO`, `T0`.`name`, COUNT(*) AS `order_count`
FROM `members` AS `T0`, `orders` AS `T1`
WHERE (`T0`.`id` = `T1`.`member_id`)
GROUP BY `T0`.`NO`, `T0`.`name`
LIMIT 0, 20

5.3 Querying with complex expression

Order.Table orderTable = Order.asTable();
Select select = new Select();

select.project((sum(orderTable.amount) / sum(orderTable.quantity) * 100).as("unit_amount"))
        .from(orderTable)
        .where(orderTable.quantity > 30 &&
                orderTable.salesAt.between("2020-05-01 00:00:00", "2020-05-02 23:59:59"))
        .groupBy(orderTable.memberId);

List<Order> orders = select.execute(Order.class);
SELECT ((((SUM(`T0`.`amount` ) / SUM(`T0`.`quantity` ) )) * 100)) AS unit_amount
FROM `orders` AS `T0`
WHERE ((`T0`.`quantity` > 30)
	AND `T0`.`sales_at` BETWEEN '2020-05-01 00:00:00' AND '2020-05-02 23:59:59' )
GROUP BY `T0`.`member_id`

5.4 Querying with dynamic arguments

String[] filteredNo = {"202000001", "202000002", "202000003"};
int filteredQuantity = 0;

Order.Table orderTable = Order.asTable();
Select select = new Select();
LogicalExpression eternalExpression = new EternalExpression();

if(filteredNo.length > 0) {
    eternalExpression = eternalExpression.and(orderTable.no.in(filteredNo));
}

if(filteredQuantity != 0) {
    eternalExpression = eternalExpression.and(orderTable > filteredQuantity);
}

select.project((sum(orderTable.amount) / sum(orderTable.quantity) * 100).as("unit_amount"))
        .from(orderTable)
        .where(eternalExpression)
        .groupBy(orderTable.memberId);

List<Order> orders = select.execute(Order.class);
SELECT ((((SUM(`T0`.`amount` ) / SUM(`T0`.`quantity` ) )) * 100)) AS unit_amount
FROM `orders` AS `T0`
WHERE ((1 = 1) AND `T0`.`NO` IN ('202000001', '202000002', '202000003') )
GROUP BY `T0`.`member_id`

6 Advanced usage

6.1 LoggerFactory injection

6.1.1 LoggerFactory implementation

public class ObjLoggerFactoryImpl implements LoggerFactory {

    private class ObjLoggerImpl implements Logger {

        private final org.slf4j.Logger logger;

        public ObjLoggerImpl(org.slf4j.Logger logger) {
            this.logger = logger;
        }

        @Override
        public void debug(long elapsedTime, String sql, Object[] params) {
            logger.debug(createLogContent(elapsedTime, sql, params));
        }

        @Override
        public void info(long elapsedTime, String sql, Object[] params) {
            logger.info(createLogContent(elapsedTime, sql, params));
        }

        @Override
        public void error(String message, Throwable throwable) {
            logger.error(message, throwable);
        }

        private String createLogContent(long elapsedTime, String sql, Object[] params) {
            String[] paramStrings = Arrays.stream(params)
                    .map(param -> String.valueOf(param)).toArray(String[]::new);
            String paramString = String.join(",", paramStrings);
            return String.format("[%dms] %s, with: [%s]",
                    elapsedTime, sql, String.join(",",
                    paramString.length() > 100 ? StringUtil
                            .truncate(paramString, 99) : paramString));
        }
    }

    @Override
    public Logger create(Class<?> clazz) {
        org.slf4j.Logger logger = org.slf4j.LoggerFactory.getLogger(clazz);
        return new ObjLoggerImpl(logger);
    }
}

6.1.1 Injecting in standalone

public class Application {

    public static void main(String[] args) {
        Databases.installLoggerFactory(new ObjLoggerFactoryImpl());
        // others
    }
}

6.1.2 Injecting Spring Boot

@SpringBootApplication
@EnableAutoConfiguration
public class Application {

    public static void main(String[] args) {
        SpringApplication springApplication = new SpringApplication(Application.class);
        springApplication.addListeners(new ApplicationListener<ApplicationReadyEvent>() {

            @Override
            public void onApplicationEvent(ApplicationReadyEvent event) {
                Databases.installLoggerFactory(new ObjLoggerFactoryImpl());
            }
        });
        springApplication.run(args);
    }
}

6.2 Cache objects with SQL

6.1.1 SQLExecutor implementation

public class CacheableSQLExecutor<T> extends DefaultSQLExecutor<T> {

    private static final List<Class<? extends Serializable>> CACHEABLE_CLASSES =
            Arrays.asList(new Class[]{Member.class});
    private static final Integer CACHED_OBJECT_EXPIRED = 60;
    private static final String KEY_SHA = "SHA";

    private Jedis jedis = new Jedis("localhost", 6379);
    private MessageDigest messageDigest;

    public CacheableSQLExecutor() {
        try {
            messageDigest = MessageDigest.getInstance(KEY_SHA);
        } catch (NoSuchAlgorithmException e) {
            throw new IllegalArgumentException(e.getMessage(), e);
        }
    }

    @Override
    public List<T> query(Connection connection, String sql,
                         TableRowAdapter tableRowAdapter, Object... params) 
      throws SQLException {
        Class<?> domainClass = tableRowAdapter.getDomainModelClass();

        if (CACHEABLE_CLASSES.contains(domainClass)) {
            if(!Serializable.class.isAssignableFrom(domainClass)) {
                throw new IllegalArgumentException(String
                                                   .format("The %s cannot be serialized"));
            }

            messageDigest.update(sql.getBytes());

            String hashedSqlId = new BigInteger(messageDigest.digest()).toString(64);
            byte[] rawObjects = jedis.get(hashedSqlId.getBytes());

            if (rawObjects != null) {
                return (List<T>) SerializationUtils.deserialize(rawObjects);
            } else {
                List<T> objects = super.query(connection, sql, tableRowAdapter, params);
                byte[] encodedObjects = SerializationUtils.serialize(objects);
                SetParams expiredParams = SetParams.setParams().ex(CACHED_OBJECT_EXPIRED);

                jedis.set(hashedSqlId.getBytes(), encodedObjects, expiredParams);

                return objects;
            }
        }
        return super.query(connection, sql, tableRowAdapter, params);
    }
}

6.1.1 Injecting

public class Application {

    public static void main(String[] args) {
        Databases.installSqlExecutor(new CacheableSQLExecutor());
        // others
    }
}

6.3 ColumnTransition extension

public class SqlDateTimeTransition<T> implements ColumnTransition<T> {

    @Override
    public Object sinking(DatabaseMetaData databaseMetaData, T object,
                          TableRowAdapter tableRowDescriptor, 
                          String fieldName, FieldValue fieldValue) 
      throws SQLException {
        String databaseName = databaseMetaData.getDatabaseProductName();
        if (fieldValue != null && fieldValue.getValue() != null) {
            if (SQLite.equals(databaseName) || Oracle.equals(databaseName)) {
                return fieldValue;
            } else if (PostgreSQL.equals(databaseName)) {
                if (fieldValue.getValue() instanceof Timestamp) {
                    return fieldValue.getValue();
                } else if (fieldValue.getValue() instanceof Long) {
                    Instant value = Instant.ofEpochMilli((Long) fieldValue.getValue());
                    return Timestamp.from(value);
                } else {
                    return Timestamp.valueOf(String.valueOf(fieldValue.getValue()));
                }
            } else {
                return fieldValue;
            }
        }
        return null;
    }

    @Override
    public Object rising(DatabaseMetaData databaseMetaData, 
                         ResultSetMetaData resultSetMetaData,
                         T object, TableRowAdapter tableRowDescriptor, 
                         String columnName, Object columnValue) throws SQLException {
        String databaseName = databaseMetaData.getDatabaseProductName();
        try {
            if (columnValue != null) {
                if (SQLite.equals(databaseName)) {
                   Instant value = Instant
                     .ofEpochMilli(Long.valueOf(String.valueOf(columnValue)))
                    return Timestamp.from(value);
                } else {
                    return columnValue;
                }
            }
        } catch (DateTimeParseException ex) {
            String message = String.format("Invalid raw DataTime of '%s' from database: %s",
                    columnName, columnValue);
            throw new IllegalArgumentException(message, ex);
        }
        return null;
    }
}