1 Preface

ObjectiveSQL is a Java ORM framework. It is not only the application of Active Record mode in Java, but also provides a nearly perfect solution for complex SQL programming, making the organic combination of Java code and SQL statements, java training It changes the traditional SQL programming model (mainly string splicing programming model).

The ObjectiveSQL project is divided into two parts: one is that Maven relies on objective SQL or objectsql springboot during runtime, which mainly implements the basic ORM features and SQL programming model; the other is the IntelliJ IDEA plug-in, which is compatible with Java operator overloading and dynamic code prompts.

ObjectiveSQL mainly solves:

• Dynamic code generation: automatically generate simple SQL programming code based on Domain Model, so that application system development only focuses on its own business characteristics and improves development efficiency
• Programmable SQL: abstract the control primitives, predicates, functions and procedural logic in SQL into high-level types in Java and integrate them with Java, making SQL a real procedural and logical programming language, which can be encapsulated, reused and unit tested
• Expression syntax consistency: equivalent replacement of Java syntax and SQL syntax, including mathematical calculation, function call, comparison and logical calculation expressions. Java expressions can be directly converted to SQL expressions.

2 dependent installation

2.1 installation of IntelliJ idea plug-in

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

2.2 Maven integration

For stand-alone applications, add the following code to dependencies:

<!-- In standalone -->
<dependency>
    <groupId>com.github.braisdom</groupId>
    <artifactId>objective-sql</artifactId>
    <version>{objsql.version}</version>
</dependency>
Copy code

For the Spring Boot integration project, add the following code to the dependencies:

<!-- 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>
Copy code

For the latest version, please visit ObjectiveSQL, ObjSqlSpringBoot

2.3 Maven Compiler parameter configuration

Please add the following code to POM Under / node in XML:

<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>
Copy code

3 database connection injection

3.1 independent application system

Take MySQL as an example, construct data connection acquisition logic based on ConnectionFactory and inject it into Databases.

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());
Copy code

The dataSourceName parameter in the getConnection method is only used in the scenario of multiple data sources. The getConnection method can return different database connections according to different dataSourceName, and this parameter can be ignored in other scenarios.

3.2 integrated Spring Boot

When the application system is developed based on the Spring Boot framework, there is no need to inject data sources manually. Please configure it according to the following methods:

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
 Copy code

The extensions tag needs to be configured only when there are multiple data sources, and the slave, as the data source name, should match the data source name defined in the DomainModel, or through the dynamic data source name in the domainmodeldescriptor.

4 simple SQL Programming Guide

The simple SQL programming provided by ObjectiveSQL is mainly aimed at the use of SQL related to single tables, and provides a convenient development experience for the development of application systems through dynamically generated Java API s.

4.1 naming convention

4.1. 1 class name and table name

By default, ObjectiveSQL converts Java elements to database elements in the form of humps and underscores. Examples are as follows:

1) Java is defined as follows:

class Member {
        private String memberNo;
    private String name;
}
Copy code

2) The database table is defined as follows:

create table members (
        member_no varchar not null,
    name varchar
);
Copy code

Class name: the name of Member in the database is members, and the column name of field name memberNo is member_no, and the field name name does not change

4.1. 1 associated object

1) Java is defined as follows:

class Member {
        private String memberNo;
    private String name;

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

class Order {
        private String no;
    private Long memberId;

    @Relation(relationType = RelationType.BELONGS_TO)
    private Member member;
}
Copy code

2) The database table is defined as follows:

create table members (
        member_no varchar not null,
    name varchar
);

create table members (
        member_no varchar not null,
    member_id int(10) not null,
    name varchar
);
Copy code

Several key features can be seen from the above structure definition:

• Used to carry has_ The instance variable members of many association object is converted from type to complex number, and BELONGS_TO and HAS_ONE is singular
• There is an instance variable memberId corresponding to the foreign key in the Order class, and there is also a member in the table_ ID corresponds to it
• Other rules are consistent with the rules of class and table conversion

Note: when converting all class names to complex ones, follow the rules of English, for example, person corresponds to pepole

4.2 domain model definition

@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;
}
Copy code

ObjectiveSQL will automatically generate basic SQL programming related methods and SQL abstract model definitions according to the above model definitions

4.3 data query

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

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

4.4 data update

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");
Copy code

4.5 affairs

4.5. 1 Annotation based transactions

// 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);
}
Copy code

4.5. 2 manual transaction management

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

4.6 associated object query

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);
Copy code

Member in the above code HAS_ MANY_ The orders attribute is automatically generated by ObjectiveSQL. In special cases, it can be based on COM github. braisdom. objsql. relation. Relationship defines the logic for building association relationships.

4.7 paging query

// 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");
Copy code

4.8 Query interface programming

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);
Copy code

Grouping and sorting in SQL need to be completed through the Query interface. At the same time, the Query interface can also perform paging and associated object Query.

4.9 Validation

ObjectiveSQL Validation internally integrates Jakarta Bean Validation

Please refer to: beanvalidation.org/

4.9. 1. Manually call the validate method

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

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

4.9. 2. validate when creating an object

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

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

4.10 custom SQL

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

5 complex SQL Programming Guide

The complex SQL programming provided by ObjectiveSQL is actually an abstraction and modeling of SQL syntax. It interacts with each other in the form of Java API, so that complex SQL no longer appears in Java in the form of string, so that dynamic SQL becomes clear and easy to understand. Different business systems can also re abstract and model their own business based on ObjectiveSQL, Realize the reuse of SQL logic.

5.1 JOIN query

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);
Copy code

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`
Copy code

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);
Copy code

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`
Copy code

5.2 paging query

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);
Copy code

-- 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
 Copy code

-- 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
 Copy code

5.3 complex expression query

ObjectiveSQL enables Expression to participate in the calculation of various operators through operator re domain technology, which makes Java code easy to understand, rather than calculation through various operator methods. ObjectiveSQL expressions cannot match the SQL Expression completely during calculation. By default, all expressions can perform arithmetic operations, and complete reminders cannot be given in IntelliJ IDEA. For example, JoinExpression can also perform arithmetic operations. At this time, reminders of syntax errors will not appear in IntelliJ IDEA, However, unsupported arithmetalexception will be thrown during operation, which is a subclass of RuntimeException.

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);
Copy code

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`
Copy code

5.4 dynamic query

The so-called dynamic query actually means that the construction process of the expression changes with the presence or absence of parameters. Based on this use scenario, ObjectiveSQL designs an eternal logical expression. The eternal expression is a clever design in the program, which makes the code logic clearer, even if all parameters are not assigned, The whole expression will also have a permanent expression to ensure the normal operation of the final SQL statement.

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);
Copy code

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`
Copy code

6 advanced use

6.1 log integration

Because ObjectiveSQL cannot determine which log framework the application system uses, ObjectiveSQL does not integrate any third-party log framework. Confirm to use JDK's own log framework. If the application system needs to use its own log framework and inject ObjectiveSQL after the system is started, please integrate in the following way (taking Slf4j as an example).

6.1. 1. Loggerfactory extension 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);
    }
}
Copy code

6.1. 2 ordinary application injection mode

public class Application {

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

6.1.3 Spring Boot application injection mode

@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);
    }
}
Copy code

6.2 object cache based on SQL statement

In the application system, data with weak timeliness will be cached. Usually, the data will be cached in Redis. For these features, the SQLExecutor interface of ObjectiveSQL can be extended for easy implementation.

6.2. 1. Sqlexecutor extension 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);
    }
}
Copy code

6.2. 2 injection mode

public class Application {

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

The injection method of Spring Boot is the same as that of LogFactory

6.3 ColumnTransition extension

ColumnTransition is an extension interface for data type conversion provided by ObjectiveSQL. For the detailed definition of this interface, please refer to: ColumnTransition Java, take the date form as an example to introduce the extension of ColumnTransition.

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;
    }
}
Copy code

The singing method is to convert the value in Java into a value acceptable to the database, and rising is to convert the value in the database into a value acceptable to Java.

Author: IT Xiaoshang
Link: https://juejin.cn/post/7041016816293904420
Source: rare earth Nuggets
The copyright belongs to the author. For commercial reprint, please contact the author for authorization, and for non-commercial reprint, please indicate the source.