組裝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底層主要是一個內(nèi)部靜態(tài)類SQLStatement依鸥，SQLStatement提供了相關(guān)的sql拼裝實現(xiàn)方法，以select為例：
public T SELECT(String columns) {
//寫入通用類型
sql().statementType = SQLStatement.StatementType.SELECT;
//將對應(yīng)的查詢列表寫入公共list中
sql().select.add(columns);
return getSelf();
}
public String sql(Appendable a) {
SafeAppendable builder = new SafeAppendable(a);
if (statementType == null) {
return null;
}
String answer;
//根據(jù)上述對應(yīng)的statementType判斷調(diào)用哪個方法
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;
}
//根據(jù)實際調(diào)用方法拼裝語句
private String selectSQL(SafeAppendable builder) {
if (distinct) {
sqlClause(builder, "SELECT DISTINCT", select, "", "", ", ");
} else {
sqlClause(builder, "SELECT", select, "", "", ", ");
}
sqlClause(builder, "FROM", tables, "", "", ", ");
joins(builder);
//拼裝關(guān)鍵詞"WHERE" + "(" + 循環(huán)條件 +"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();
}
創(chuàng)建Connection
MysqlDataSource ds = new MysqlDataSource();
Connection conn = ds.getConnection("username", "pwd");
ONE--調(diào)用ScriptRunner
Reader reader = Resources.getResourceAsReader(example1().toString());
ScriptRunner sr = new ScriptRunner(conn);
sr.runScript(reader);
ScriptRunner
- 執(zhí)行方法
public void runScript(Reader reader) {
//調(diào)用setAutoCommit()方法，根據(jù)autoCommit屬性的值設(shè)置事務(wù)是否自動提交
setAutoCommit();
try {
//判斷sendFullScript屬性值
if (sendFullScript) {
//如果值為true茶敏，調(diào)用executeFullScript()方法讀取SQL腳本文件的所有內(nèi)容缚俏，然后調(diào)用JDBC中Statement的execute()方法一次性執(zhí)行腳本中的所有SQL語句
executeFullScript(reader);
} else {
//如果值為false忧换，調(diào)用executeLineByLine()方法逐行讀取SQL腳本文件，以分號作為每條SQL語句結(jié)束的標(biāo)志亚茬，逐條執(zhí)行SQL語句
executeLineByLine(reader);
}
} finally {
rollbackConnection();
}
}
- 一次性執(zhí)行所有sql內(nèi)容
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.創(chuàng)建Statement 2.調(diào)用JDBC中Statement的execute()方法執(zhí)行腳本中的所有SQL語句 3.根據(jù)返回的result
executeStatement(command);
commitConnection();
} catch (Exception e) {
String message = "Error executing: " + script + ". Cause: " + e;
printlnError(message);
throw new RuntimeSqlException(message, e);
}
}
- 分行執(zhí)行相關(guān)sql
private void executeLineByLine(Reader reader) {
StringBuilder command = new StringBuilder();
try {
BufferedReader lineReader = new BufferedReader(reader);
String line;
while ((line = lineReader.readLine()) != null) {
//按行讀取葡兑，判斷是否執(zhí)行
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)) {
//如果這行內(nèi)容是注釋，跳過
Matcher matcher = DELIMITER_PATTERN.matcher(trimmedLine);
if (matcher.find()) {
delimiter = matcher.group(5);
}
println(trimmedLine);
} else if (commandReadyToExecute(trimmedLine)) {
// commandReadyToExecute方法判斷最后一個字符是否是分號
//如果這行內(nèi)容最一個字符是分號吆鹤，該sql讀取完畢
//1.創(chuàng)建Statement 2.調(diào)用JDBC中Statement的execute()方法執(zhí)行腳本中的所有SQL語句 3.根據(jù)返回的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) {
//如果最后一個字符不是分號疑务，繼續(xù)往后拼裝sql語句
command.append(line);
command.append(LINE_SEPARATOR);
}
}
TWO-- SqlRunner
調(diào)用SqlRunner也同樣可以執(zhí)行相關(guān)sql梗醇；
MysqlDataSource ds = new MysqlDataSource();
Connection conn = ds.getConnection("username", "pwd");
//調(diào)用SqlRunner.run方法
SqlRunner exec = new SqlRunner(conn);
exec.run(example1().toString());
//調(diào)用SqlRunner.selectAll方法
List<Map<String, Object>> rows = exec.selectAll("SELECT * FROM BLAH");
//通用方法
public void run(String sql) throws SQLException {
//創(chuàng)建Statement，執(zhí)行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方法温鸽，也是調(diào)用selectAll涤垫，判斷最終返回結(jié)果是否等于1
