组装SQL
mybatis中提供了一个SQL类可以支持方便的写sql语句。如：下方new的SQL对象以及输出语句
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());
}
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
SQL对象继承AbstractSQL，AbstractSQL底层主要是一个内部静态类SQLStatement，SQLStatement提供了相关的sql拼装实现方法，以select为例：
public T SELECT(String columns) {
//写入通用类型
sql().statementType = SQLStatement.StatementType.SELECT;
//将对应的查询列表写入公共list中
sql().select.add(columns);
return getSelf();
}
public String sql(Appendable a) {
SafeAppendable builder = new SafeAppendable(a);
if (statementType == null) {
return null;
}
String answer;
//根据上述对应的statementType判断调用哪个方法
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);
//拼装关键词"WHERE" + "(" + 循环条件 +"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();
}
创建Connection
MysqlDataSource ds = new MysqlDataSource();
Connection conn = ds.getConnection("username", "pwd");
ONE--调用ScriptRunner
Reader reader = Resources.getResourceAsReader(example1().toString());
ScriptRunner sr = new ScriptRunner(conn);
sr.runScript(reader);
ScriptRunner
- 执行方法
public void runScript(Reader reader) {
//调用setAutoCommit()方法，根据autoCommit属性的值设置事务是否自动提交
setAutoCommit();
try {
//判断sendFullScript属性值
if (sendFullScript) {
//如果值为true，调用executeFullScript()方法读取SQL脚本文件的所有内容，然后调用JDBC中Statement的execute()方法一次性执行脚本中的所有SQL语句
executeFullScript(reader);
} else {
//如果值为false，调用executeLineByLine()方法逐行读取SQL脚本文件，以分号作为每条SQL语句结束的标志，逐条执行SQL语句
executeLineByLine(reader);
}
} finally {
rollbackConnection();
}
}
- 一次性执行所有sql内容
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);
//1.创建Statement 2.调用JDBC中Statement的execute()方法执行脚本中的所有SQL语句 3.根据返回的result
executeStatement(command);
commitConnection();
} catch (Exception e) {
String message = "Error executing: " + script + ". Cause: " + e;
printlnError(message);
throw new RuntimeSqlException(message, e);
}
}
- 分行执行相关sql
private void executeLineByLine(Reader reader) {
StringBuilder command = new StringBuilder();
try {
BufferedReader lineReader = new BufferedReader(reader);
String line;
while ((line = lineReader.readLine()) != null) {
//按行读取，判断是否执行
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)) {
//如果这行内容是注释，跳过
Matcher matcher = DELIMITER_PATTERN.matcher(trimmedLine);
if (matcher.find()) {
delimiter = matcher.group(5);
}
println(trimmedLine);
} else if (commandReadyToExecute(trimmedLine)) {
// commandReadyToExecute方法判断最后一个字符是否是分号
//如果这行内容最一个字符是分号，该sql读取完毕
//1.创建Statement 2.调用JDBC中Statement的execute()方法执行脚本中的所有SQL语句 3.根据返回的result
command.append(line, 0, line.lastIndexOf(delimiter));
command.append(LINE_SEPARATOR);
println(command);
executeStatement(command.toString());
command.setLength(0);
} else if (trimmedLine.length() > 0) {
//如果最后一个字符不是分号，继续往后拼装sql语句
command.append(line);
command.append(LINE_SEPARATOR);
}
}
TWO-- SqlRunner
调用SqlRunner也同样可以执行相关sql；
MysqlDataSource ds = new MysqlDataSource();
Connection conn = ds.getConnection("username", "pwd");
//调用SqlRunner.run方法
SqlRunner exec = new SqlRunner(conn);
exec.run(example1().toString());
//调用SqlRunner.selectAll方法
List<Map<String, Object>> rows = exec.selectAll("SELECT * FROM BLAH");
//通用方法
public void run(String sql) throws SQLException {
//创建Statement，执行execute方法
try (Statement stmt = connection.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);
}
}
}
selectOne方法，也是调用selectAll，判断最终返回结果是否等于1
