Start preparing to generate SQL

In the previous article, we got the information we needed to generate SQL statements. In this article, we start generating the SQL we need based on class. Let’s just confirm a few things before we do that

We use placeholders for arguments in SQL.

The PreparedStatement in JDBC is used, and the parameter in SQL is? In the form.

It goes something like this:
```
Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("select * from `user` where `status` = ? ;");
preparedStatement.setObject(1.0);
ResultSet resultSet = preparedStatement.executeQuery();
Copy the code
```
However, we would need to hand-write the tedious code for executing SQL each time we execute, so I chose to use the JdbcTemplte in Spring-JDBC. Then I just need to generate the SQL and execute it using the methods in JdbcTemplte.
We only generate a single table add, delete, change query, do not involve complex SQL.
Do not post the complete code, mainly to explain the idea.

After all, this is already written code and the address is: github.com/hjx60149632… . All code can be found here.

Analysis of SQL

We mainly solve the problem of adding and deleting the query, so we first write how to generate a new SQL.

Let’s take a look at what SQL generally consists of. Here’s an example:

insert

INSERT INTO user (name.id, create_date, age, mark, status)
VALUES(? ,? ,? ,? ,? ,?) ;Copy the code

delete

DELETE
FROM user
WHERE id=?Copy the code

update

UPDATE user
SET name=? .id=? , create_date = ? , age = ? .status=?WHERE id=?Copy the code

select

SELECT name.id, create_date, age, mark, status
FROM user
WHERE id=?Copy the code

Looking at the SQL above, you can see that there are some commonalities:

All have table names.
Basically all contain the names of the fields in the table.
And parameters.
All this is nonsense; -)

Next, you can create SQL for each type of SQL.

Action object

All the objects below are this user.java


import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import java.util.Date;


@Table(name = "user")
public class User {

    @Column(name = "name")
    private String name;

    @Id
    @Column(name = "id")
    private int id;

    @Column(name = "age")
    private int age;

    @Column(name = "mark")
    private String mark;

    @Column(name = "create_date")
    private Date createDate;

    @Column(name = "status")
    private int status;

// getter setter toString
}
Copy the code

Let’s start with some tool code

Mainly used to manipulate strings


import java.util.Collection;
import java.util.Iterator;

/ * * *@author hjx
 */
public class StringUtils {

    public static final String SPACE = "";

    public static final String BLANK = "";

    public static final String COMMA = ",";


    /** * repeated string **@param str
     * @param number
     * @return* /
    public static String[] repeat(String str, int number) {
        Assert.notNull(str);
        String[] strings = new String[number];
        for (int i = 0; i < number; i++) {
            strings[i] = str;
        }
        return strings;
    }

    /** * combination string **@param strings
     * @return* /
    public static String append(final Object... strings) {
        StringBuilder builder = new StringBuilder();
        for (Object s1 : strings) {
            if (s1 == null) {
                continue;
            }
            builder.append(s1.toString());
        }
        return builder.toString();
    }

    /** * combination string **@param collection
     * @param separator
     * @return* /
    public static String join(Collection collection, String separator) {
        StringBuffer var2 = new StringBuffer();
        for (Iterator var3 = collection.iterator(); var3.hasNext(); var2.append((String) var3.next())) {
            if(var2.length() ! =0) { var2.append(separator); }}returnvar2.toString(); }}Copy the code

For taking values from objects, use reflection.

/** * The value is **@paramTarget from which object to value *@paramField takes the value of that property of the object *@return* /
public static Object getValue(Object target, Field field) {
    // ignore private
    field.setAccessible(true);
    try {
        return field.get(target);
    } catch (IllegalAccessException e) {
        e.printStackTrace();
    }
    return null;
}
Copy the code

Again, reflection is used to set values for objects.

/** * Set the value **@paramTarget from which object to value *@paramField takes the value of that property of the object *@paramValue Value to be set *@return* /
public static boolean setValue(Object target, Field field, Object value) {
    field.setAccessible(true);
    try {
        field.set(target, value);
        return true;
    } catch (IllegalAccessException e) {
        e.printStackTrace();
    }
    return false;
}
Copy the code

Now you can start to create various SQL

Generate SQL: insert

Train of thought

The new SQL is relatively easy to implement, we need roughly:

Build an object User.
Call the new method, passing User as an argument to the method.
Get all the field names and the values to be saved by parsing the results of the previous article. Generating SQL.
SQL is executed through JdbcTemplate to insert into the database.

implementation

First we need to get all the table field names and corresponding values according to user.java. EntityTableRowMapper

Get the values of the field and class properties

Map<String, Field> columnFieldMapper = entityTableRowMapper.getColumnFieldMapper();
insertColumns = new ArrayList(columnFieldMapper.size());
for (Map.Entry<String, Field> stringFieldEntry : columnFieldMapper.entrySet()) {
    Field field = stringFieldEntry.getValue();
    Object value = EntityUtils.getValue(entity, field);
    if (value == null) {
        continue;
    }
    insertColumns.add(stringFieldEntry.getKey());
    insertColumnValues.add(value);
}
Copy the code

There are two variables:

InsertColumns: the name of a column in SQL.

InsertColumnValues: indicates the value of a field in SQL.

Generate the inserted SQL:

StringBuilder builder = new StringBuilder();
int size = insertColumns.size();
builder.append("INSERT INTO ").append(getTableName()).append(StringUtils.SPACE);
builder.append(StringUtils.append("(", StringUtils.join(insertColumns, ","), ")"));
builder.append("VALUES ");
for (int i = 0; i < insertCount; i++) {
    builder.append("(");
    String[] repeat = StringUtils.repeat("?", size);
    builder.append(StringUtils.join(Arrays.asList(repeat), ","));
    builder.append(")");
    if(i ! = insertCount -1) {
        builder.append(StringUtils.COMMA);
    }
}
builder.append(";");
Copy the code

Generated results:

//user
User user = new User();
user.setId(10);
user.setCreateDate(new Date());
user.setAge(20);
user.setMark("ceshi");
user.setName("heiheihei");
//sql
INSERT INTO user ( name, id, create_date, age, mark, status ) VALUES (? ,? ,? ,? ,? ,?);
//value
[heiheihei, 10, Tue Jan 22 16:33:00 CST 2019.20, ceshi, 0]
Copy the code

Now you can execute with the generated SQL and values

jdbcTemplate.update(sql, insertColumnValues.toArray());
Copy the code

Generate SQL: WHERE

In the previous article, we implemented SQL to generate inserts. Now we will implement SQL statements for UPDATE, delete, and SELECT. But these statements have a bit of a problem: they are usually followed by a WHERE condition, because you can’t execute on all the data in the table.

So here we need SQL that is conditional. It goes something like this:

WHERE id = ? AND name != ? OR age >= ? 
Copy the code

Where arguments continue with “?” Instead. I’m just going to put the values in an ordered set. Similar to insertColumnValues mentioned in the previous article.

Train of thought

Conditions are made up of one another, and we can write a class that describes a condition.
Write a utility class to quickly create conditions.
A complete condition is one that combines conditions with and or OR and adds where at the front.
Finally, turn the condition into a string and store the value of the condition in a collection.

implementation

The first step

Let’s do the first step. Before we do that, let’s look at what constitutes a condition. For example:

1: id = ? AND
2: name != ? OR
3: age >= ? 
Copy the code

Here you can observe that each condition is defined by a field name, a judgment, and ** a placeholder “?” ** AND the following AND OR OR used to join conditions. So we can write a class to hold this information:

Where.java


import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/** * where condition defaults to use and to connect multiple conditions **@author hjx
 */
public class Where {

    protected static final String PLACEHOLDER = "#{COLUMN}";

    static final String AND = "AND ";

    static final String OR = "OR ";

    private String sql;

    private String column;

    private String connect = AND;

    private List<Object> values;

    /** * Whether there is a value (null also means there is a value) */
    private boolean hasValue;

    / * * *@paramColumn Specifies the column * to be operated on@paramSQL */ for SQL operations
    public Where(String column, String sql) {
        this.column = column;
        this.sql = sql;
        this.hasValue = false;
        this.values = new ArrayList<>();
    }

    / * * *@paramColumn Specifies the column * to be operated on@paramSQL * for SQL operations@paramValue Indicates the SQL parameter */
    public Where(String column, String sql, Object value) {
        this.sql = sql;
        this.column = column;
        this.values = new ArrayList<>();
        this.values.add(value);
        this.hasValue = true;
    }

    / * * *@paramColumn Specifies the column * to be operated on@paramSQL * for SQL operations@paramValues SQL parameters */
    public Where(String column, String sql, Object[] values) {
        this.sql = sql;
        this.column = column;
        this.values = Arrays.asList(values);
        this.hasValue = true;
    }

    public Where or(a) {
        this.connect = OR;
        return this;
    }

    public Where and(a) {
        this.connect = AND;
        return this;
    }

    /** * gets the join of this condition **@return* /
    public String getConnect(a) {
        return connect;
    }

    protected String getSql(a) {
        return sql;
    }

    protected boolean isHasValue(a) {
        return hasValue;
    }

    protected List<Object> getValues(a) {
        return values;
    }

    public String getColumn(a) {
        returncolumn; }}Copy the code

The constant PLACEHOLDER in the PLACEHOLDER above is used as a PLACEHOLDER, as described below.

Thus, a class is written to hold a single condition. If you have multiple conditions in an SQL, you can simply use an ArrayList to hold those conditions and assemble them into SQL based on certain conditions.

The second step

SQL also has some more common judgments, such as:! =, =, <=, >=, etc. We can create a utility class here to quickly generate the Where class, like this:

Wheres.java


import java.util.Arrays;

/** * query condition *@author hjx
 */
public class Wheres {
    
    public static Where equal(final String columnName, final Object value) {
        return new Where(columnName, Where.PLACEHOLDER + "=? ", value);
    }

    public static Where notEqual(final String columnName, final Object value) {
        return new Where(columnName, Where.PLACEHOLDER + ! "" =? ", value);
    }

    public static Where not(final String columnName, final Object value) {
        return new Where(columnName, Where.PLACEHOLDER + "< >? ", value);
    }

    public static Where isNotNull(final String columnName) {
        return new Where(columnName, Where.PLACEHOLDER + " IS NOT NULL ");
    }

    public static Where isNull(final String columnName) {
        return new Where(columnName, Where.PLACEHOLDER + " IS NULL ");
    }

    public static Where greater(final String columnName, final Object value, final boolean andEquals) {
        if (andEquals) {
            return new Where(columnName, Where.PLACEHOLDER + "> =? ", value);
        }
        return new Where(columnName, Where.PLACEHOLDER + ">? ", value);
    }

    public static Where less(final String columnName, final Object value, final boolean andEquals) {
        if (andEquals) {
            return new Where(columnName, Where.PLACEHOLDER + "< =? ", value);
        }
        return new Where(columnName, Where.PLACEHOLDER + "
      , value);
    }

    public static Where like(final String columnName, final Object value) {
        return new Where(columnName, Where.PLACEHOLDER + " like ? ", value);
    }

    public static Where betweenAnd(final String columnName, final Object value1st, final Object value2nd) {
        return new Where(columnName, Where.PLACEHOLDER + " between ? and ? ".new Object[]{value1st, value2nd});
    }

    public static Where in(final String columnName, final Object[] values) {
        Object[] sqlVal = values;
        if (sqlVal.length == 0) {
            sqlVal = new Object[]{null};
        }
        StringBuffer inSql = new StringBuffer();
        inSql.append(Where.PLACEHOLDER);
        inSql.append(" IN ( ");
        String[] strings = StringUtils.repeat("?", sqlVal.length);
        inSql.append(StringUtils.join(Arrays.asList(strings), ","));
        inSql.append(")");
        return newWhere(columnName, inSql.toString(), sqlVal); }}Copy the code

This is just a simple list of common criteria. If you have special needs, you can add them.

Here’s the thing about constant PLACEHOLDER:

When generating the SQL, I need to do some field validation. Here, a placeholder is placed in SQL, and the fields that actually participate in the condition are stored in a separate property. This allows you to verify that the fields in the condition are in the table when the SQL is actually generated, and if so, replace the fields with placeholders. And if you use the attribute name, you can also find the field name of the corresponding table based on the name.

The third step

With the above code, we can easily create conditions. Now let’s assemble these conditions into the complete SQL we need.

Note: The code here may not be quite the same as the one on Github, because it’s just about the idea of how to put all the pieces together to make it into a complete project, which is different for everyone. So ~ ~ ~ hey hey.

Here we go:

Let’s use the user.java example we wrote earlier

List<Where> wheres = Arrays.asList(
        Wheres.equal("name"."Uncle Lee"),
        Wheres.notEqual("status".1),
        Wheres.in("age".new Integer[]{1.2.3.4.5}),
        Wheres.greater("age".20.true)); List<Object> sqlValue =new ArrayList<>();
StringBuilder sql = new StringBuilder();
if(wheres.size() ! =0) {
    sql.append("WHERE ");
    for (int i = 0; i < wheres.size(); i++) {
        Where where = wheres.get(i);
        if(i ! =0) {
            sql.append(where.getConnect());
        }
        String column = where.getColumn();
        String whereSql = where.getSql();
        sql.append(
            // Get the real SQL here
            whereSql.replace(Where.PLACEHOLDER, getColumnName(column))
        );
        // Because some conditions may have multiple parameters
        List<Object> values = where.getValues();
        for (int j = 0; j < values.size(); j++) {
            sqlValue.add(values.get(j));
        }
    }
}
System.out.println(sql.toString());
System.out.println(sqlValue.toString());
Copy the code

GetColumnName (String name) getColumnName(String name) getColumnName(String name) getColumnName(String name) Because this condition might be passing in the names of Java properties instead of table field names, it needs to be converted to the field names of the real table. This step is also taken from the previously generated map. You can also verify that this field is in the table. I won’t post this one, it’s on Github.

Output result:

WHERE name = ? AND status ! =? AND age IN ( ? ,? ,? ,? ,?) AND age >= ? [Uncle Li, 1, 1, 2, 3, 4, 5, 20]Copy the code

Here we have a WHERE, and we can get the parameters of the condition.

All that remains is the separate operation to generate the UPDATE, DELETE, select SQL.

Generate SQL: SELECT

The last article talked about how to generate a PART of where SQL, after we have to do a lot of simple things, just as long as the beginning of the same generation of VARIOUS SQL statements is good, as long as we need to add conditions, a complete SQL smoothly done.

Now let’s start writing the SQL that generates the query statement. A query statement looks something like this:

SELECT name.id, create_date, age, mark, status FROM user
Copy the code

As you can see, a basic query statement is basically a SELECT statement followed by the fields to be queried, followed by FROM and the name of the table to be queried. ORDER BY/GROUP BY/LIMIT…. may be added at most Or something like that, because it’s a little bit easier, so I won’t write it here. (If it’s too complicated, just write the SQL directly. I don’t need this operation myself.)

Train of thought

Get the mapping of the property and field names from the previous mapping and concatenate the SQL.
Execute the SQL and extract the results.
Instantiate the class, using reflection to assign values to the attributes of the class.

These steps are still relatively easy to do, the first step is very simple, as written before can be. Because I’m using the JdbcTemplate to execute my SQL, there’s a bit of a hole, which I’ll describe.

A small hole

I encountered this pit when I used the project I had written to make reports for the company. The reason is that some of the database fields are datetime types, which sometimes appear in tables with the value 0000-00-00 00:00:00. (I don’t know where that value came in, but it was the “/” type that did not convert to the Java Date type.) So there’s an error here.

I’ve written a class here that inherits the ColumnMapRowMapper in SpringJdbc, like this:

import org.springframework.jdbc.core.ColumnMapRowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;

/** * Catch value error **@author hjx
 */
public class PlusColumnMapRowMapper extends ColumnMapRowMapper {

    /** * If the database type is time, 0000-00-00 00:00:00 * will return an error, so override this method and return null **@param rs
     * @param index
     * @return
     * @throws SQLException
     */
    @Override
    protected Object getColumnValue(ResultSet rs, int index) throws SQLException {
        Object columnValue = null;
        try {
            columnValue = super.getColumnValue(rs, index);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        returncolumnValue; }}Copy the code

Exactly where this class is used is explained below.

implementation

Now let’s talk about how to implement the above idea. First of all, because the first step is relatively easy, I won’t write it. I’ll just start with step two.

Execute the SQL and extract the results.

Here I’m using the JdbcTemplate method, which gives us a method:
```
<T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper)
Copy the code
```
The first two parameters are easier to understand, one is SQL, and the other is the parameter in SQL. RowMapper (RowMapper, RowMapper, RowMapper, RowMapper

Here’s a method:
```
T mapRow(ResultSet rs, int rowNum) throws SQLException
Copy the code
```
The first argument is the result of the query, the second argument is the result of the current row, and the return value is what object you want to return. Here we need to rewrite this method to convert the result of the query into the object we want. We can write this:
```
/** * Convert the result of a database query to an object **@param resultSet
 * @param rowNum
 * @return
 * @throws SQLException
 */
@Override
public T mapRow(ResultSet resultSet, int rowNum) throws SQLException { Map<String, Object> resultMap = columnMapRowMapper.mapRow(resultSet, rowNum); .Copy the code
```
ColumnMapRowMapper (PlusColumnMapRowMapper) converts the query result to a Map

. Among them:
,>

Key: indicates the name of a table field.

Object: indicates the value of the field.

PlusColumnMapRowMapper returns null if an exception occurs while fetching a value.

Now that we have the result of executing the SQL in this step, we need to transform the result into the class we need.

Convert the result to class

In the previous step, we got the result Map, now we just need to walk through the Map, and then instantiate the Java object, using reflection to set the attributes in one by one according to the mapping relationship between the fields and attributes. Now paste the full code for the previous step:

public T mapRow(ResultSet resultSet, int rowNum) throws SQLException {
    Map<String, Object> resultMap = columnMapRowMapper.mapRow(resultSet, rowNum);
    T instance = getInstance(tableClass);
    for (Map.Entry<String, Object> entry : resultMap.entrySet()) {
        // Database field name
        String key = entry.getKey();
        if(! columnFieldMapper.containsKey(key)) {continue;
        }
        Field declaredField = columnFieldMapper.get(key);
        if (declaredField == null) {
            continue;
        }
        // Database field value
        Object value = entry.getValue();
        setFieldValue(instance, declaredField, value);
    }
    return instance;
}
Copy the code

ColumnFieldMapper is a Map

. Key is the name of a table field. Value is an attribute of the corresponding class.
,>

Here is the code for setFieldValue:

boolean setFieldValue(T t, Field field, Object value) {
    field.setAccessible(true);
    try {
        if(value ! =null) {
            field.set(t, value);
            return true; }}catch (IllegalAccessException e) {
        e.printStackTrace();
    }
    return false;
}
Copy the code

This way, we can transform the results of the query into the desired class based on the mapping.

The rest of the

If you need to add a condition to a query, you can use the tool that generated the condition to concatenate the condition after the SQL. Accordingly, the parameters in the WHERE should be added to the array in order.

The same is true for adding ORDER BY/GROUP BY/LIMIT. It really depends on how your code is designed. I only use ORDER BY and LIMIT. You can find it on my Github. The address is here: github.com/hjx60149632… .

Generate SQL: delete

Train of thought

Gee, that’s too easy. I won’t write it

Look at what I’ve written before, analyze it, think about it, and then write each step, bit by bit.

~ ~ ~

implementation

You write it yourself.

Generate SQL: update

The last part. I’m almost done. Writing is really tired ~ ~ ~

Train of thought

Update the SQL statement is easier to do, because the conditions behind the SQL has already written where this article, so here only write SQL where left part of the left part. Let’s look at the **update ** statement:

UPDATE user SET name=? .id=? , create_date = ? , age = ? .status=?WHERE id=?Copy the code

UPDATE table_name SET table_name =? It looks like this. (because we don’t write WHERE right now)

So the specific idea is:

Assemble SQL according to the mapping.

There may be an option here to update an attribute to NULL if the value is null.
Get the value to be updated.
Execute SQL.

implementation

Get all the attributes from the map.

This step of the code will not put ~ ~ ~, and the previous written no difference.

Get the name and value of the property to be updated.

Here we need three parameters:

1: indicates whether null attributes should be ignored during updates. boolean ignoreNull

2: An ordered collection of fields that need to be updated. List updataColumn

3: Saves an ordered collection of the values of the fields to be updated. List values

The code looks like this:

List<String> columnNames = new ArrayList<>(entityTableRowMapper.getColumnNames());
Map<String, Field> columnFieldMapper = entityTableRowMapper.getColumnFieldMapper();
List<Object> values = new ArrayList<>();
for (int i = 0; i < columnNames.size(); i++) {
    String columnName = columnNames.get(i);
    if(! sqlColumns.contains(columnName)) {continue;
    }
    Field field = columnFieldMapper.get(columnName);
    Object value = EntityUtils.getValue(entity, field);
    // If the class value is null and null is set to be ignored, skip
    if (ignoreNull && value == null) {
        continue;
    }
    updataColumn.add(columnName);
    values.add(value);
}
Copy the code

Assemble SQL according to the data you get

Once we have the data we need above, we also need to get the name of the table, which can be taken directly from the mapping. The following is the code for assembling SQL:

StringBuilder sql = new StringBuilder();
sql.append("UPDATE ").append(getTableName()).append(StringUtils.SPACE);
sql.append("SET ");
for (int i = 0; i < updataColumn.size(); i++) {
    String column = updataColumn.get(i);
    if (i == 0) {
        sql.append(StringUtils.append(column, "=? "));
    } else {
        sql.append(StringUtils.append(",", column, "=? ")); }}Copy the code

That’s it. It goes something like this:

UPDATE user SET name=? .id=? , create_date = ? , age = ? .status=?Copy the code

If it is conditional, it can be generated using where written earlier, and the value of where is appended to the set values.

Execute SQL.

Too simple, I won’t write ~

The last

Finally finished.

Or to say, because the code has been on Github, so I did not write all the code above, mainly to explain the idea. In addition, when you just start to write a blog, some of the expressions may not be very clear. Eat the loss of no culture ~ ~ ~

This project has many functions that can be implemented but have not been implemented, such as some more complex queries, execution functions and so on. I didn’t write it. First, I don’t need it, because it is mainly used for exporting reports. Second, WHEN I write my own projects, I don’t use it at all. I write everything in Java in Java. As for the database, it is good for me to save a data, and the data processing is better done by Java.

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Writing a Java ORM Framework (4)

Start preparing to generate SQL

Analysis of SQL

Action object

Let’s start with some tool code

Generate SQL: insert

Train of thought

implementation

Generate SQL: WHERE

Train of thought

implementation

The first step

The second step

The third step

Generate SQL: SELECT

Train of thought

A small hole

implementation

The rest of the

Generate SQL: delete

Train of thought

implementation

Generate SQL: update

Train of thought

implementation

The last

finished

Writing a Java ORM Framework (4)

Start preparing to generate SQL

Analysis of SQL

Action object

Let’s start with some tool code

Generate SQL: insert

Train of thought

implementation

Generate SQL: WHERE

Train of thought

implementation

The first step

The second step

The third step

Generate SQL: SELECT

Train of thought

A small hole

implementation

The rest of the

Generate SQL: delete

Train of thought

implementation

Generate SQL: update

Train of thought

implementation

The last

finished

Related Posts

Common design errors in MySQL

What is RESTful API design?

Summary of a wave of Tencent, Ali, Bytedance asked Redis interview questions, collect.