Assemble the SQL

Mybatis provides a SQL class to support the convenience of writing SQL statements. For example, the SQL object new below and the output statement

private static SQL example1() { return new SQL() {{ SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME"); SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON"); FROM("PERSON P"); FROM("ACCOUNT A"); INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID"); INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID"); WHERE("P.ID = A.ID"); WHERE("P.FIRST_NAME like ?" ); OR(); WHERE("P.LAST_NAME like ?" ); GROUP_BY("P.ID"); HAVING("P.LAST_NAME like ?" ); OR(); HAVING("P.FIRST_NAME like ?" ); ORDER_BY("P.ID"); ORDER_BY("P.FULL_NAME"); }}; } public static void main(String[] args) { System.out.println(example1().toString()); }Copy the code

SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON FROM PERSON P, ACCOUNT A INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID INNER JOIN COMPANY C on D.COMPANY_ID = C.ID WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) OR (P.LAST_NAME like ?) GROUP BY P.ID HAVING (P.LAST_NAME like ?) OR (P.FIRST_NAME like ?) ORDER BY P.ID, P.FULL_NAME

The SQL object is AbstractSQL, which is an internal static class called SQLStatement. SQLStatement provides an implementation method for SQL assembly.

Public T SELECT (String columns) {/ / write generic SQL () statementType = SQLStatement. StatementType. SELECT; SQL ().select.add(columns); return getSelf(); } public String sql(Appendable a) { SafeAppendable builder = new SafeAppendable(a); if (statementType == null) { return null; } String answer; Switch (statementType) {case DELETE: answer = deleteSQL(Builder); break; case INSERT: answer = insertSQL(builder); break; case SELECT: answer = selectSQL(builder); break; case UPDATE: answer = updateSQL(builder); break; default: answer = null; } return answer; } private String selectSQL(SafeAppendable Builder) {if (distinct) {sqlClause(builder, "SELECT DISTINCT", select, "", "", ", "); } else { sqlClause(builder, "SELECT", select, "", "", ", "); } sqlClause(builder, "FROM", tables, "", "", ", "); joins(builder); / / assembled keywords "WHERE" + "(" + of + loop" AND "+") "sqlClause (builder," WHERE ", WHERE, "(",") ", "AND"); sqlClause(builder, "GROUP BY", groupBy, "", "", ", "); sqlClause(builder, "HAVING", having, "(", ")", " AND "); sqlClause(builder, "ORDER BY", orderBy, "", "", ", "); limitingRowsStrategy.appendClause(builder, offset, limit); return builder.toString(); }Copy the code

Create a Connection

MysqlDataSource ds = new MysqlDataSource();
Connection conn = ds.getConnection("username", "pwd");
Copy the code

ONE, called ScriptRunner

Reader reader = Resources.getResourceAsReader(example1().toString());
ScriptRunner sr = new ScriptRunner(conn);
sr.runScript(reader);
Copy the code
ScriptRunner
  1. Execution method
Public void runScript(Reader Reader) {// Call setAutoCommit() and set the transaction to automatically commit setAutoCommit(). If (sendFullScript) {// If (sendFullScript) {// If true, call executeFullScript() to read all contents of the SQL script file, Execute (reader) all SQL statements in the script at once by calling Statement execute() in JDBC. } else {// If the value is false, call the executeLineByLine() method to read the SQL script file line by line, with a semicolon as the end of each SQL statement, and execute the SQL statement line by line; } } finally { rollbackConnection(); }}Copy the code
  1. Execute all SQL content at once
private void executeFullScript(Reader reader) { StringBuilder script = new StringBuilder(); try { BufferedReader lineReader = new BufferedReader(reader); String line; while ((line = lineReader.readLine()) ! = null) { script.append(line); script.append(LINE_SEPARATOR); } String command = script.toString(); println(command); Create Statement 2. Invoke the Statement execute() method in JDBC to execute all SQL statements in the script 3. Based on the returned result executeStatement(command); commitConnection(); } catch (Exception e) { String message = "Error executing: " + script + ". Cause: " + e; printlnError(message); throw new RuntimeSqlException(message, e); }}Copy the code
  1. The branch executes the relevant SQL
private void executeLineByLine(Reader reader) { StringBuilder command = new StringBuilder(); try { BufferedReader lineReader = new BufferedReader(reader); String line; while ((line = lineReader.readLine()) ! HandleLine (command, line); } commitConnection(); checkForMissingLineTerminator(command); } catch (Exception e) { String message = "Error executing: " + command + ". Cause: " + e; printlnError(message); throw new RuntimeSqlException(message, e); } } private void handleLine(StringBuilder command, String line) throws SQLException { String trimmedLine = line.trim(); If (lineIsComment(trimmedLine)) {// If this line is a comment, skip Matcher Matcher = delimiter_pattern.matcher (trimmedLine); if (matcher.find()) { delimiter = matcher.group(5); } println(trimmedLine); } else if (commandReadyToExecute(trimmedLine)) {if (commandReadyToExecute(trimmedLine)) {if (commandReadyToExecute(trimmedLine)) { Create Statement 2. Invoke the Statement execute() method in JDBC to execute all SQL statements 3 in the script. Append (line, 0, line.lastIndexof (delimiter)); command.append(LINE_SEPARATOR); println(command); executeStatement(command.toString()); command.setLength(0); Trimmedline.length () > 0) {if (trimmedLine.length() > 0) {if (trimmedLine.length() > 0); command.append(LINE_SEPARATOR); }}Copy the code

TWO– SqlRunner

Calling SqlRunner can also execute related SQL;

MysqlDataSource ds = new MysqlDataSource(); Connection conn = ds.getConnection("username", "pwd"); SqlRunner exec = new SqlRunner(conn); exec.run(example1().toString()); List<Map<String, Object>> rows = exec. SelectAll ("SELECT * FROM BLAH");Copy the code
Public void run(String SQL) throws SQLException {// Create Statement, Implement the execute method try (Statement STMT = connection. The createStatement ()) {STMT. Execute (SQL); ExecuteQuery public List<Map<String, Object>> selectAll(String SQL, Object... args) throws SQLException { try (PreparedStatement ps = connection.prepareStatement(sql)) { setParameters(ps, args); try (ResultSet rs = ps.executeQuery()) { return getResults(rs); }}}Copy the code

The selectOne method, which also calls selectAll, determines whether the final result returned is equal to 1