1 package net.sf.persism;
2
3 import java.util.Scanner;
4 import java.util.regex.Pattern;
5
6 /**
7 * Simple wrapper for SQL String. Mainly to allow for overloads to fetch/query methods.
8 *
9 * @see <a href="https://sproket.github.io/Persism/manual2.html">Using the new Query/Fetch methods</a>
10 */
11 public final class SQL {
12
13 enum SQLType {Select, Where, StoredProc}
14
15 SQLType type;
16
17 final String sql;
18
19 String processedSQL = null;
20
21 private static final Pattern commentPattern = Pattern.compile("/\\*.*?\\*/", Pattern.DOTALL);
22
23 SQL(String sql) {
24 sql = sql.trim();
25
26 if (sql.startsWith("--") || sql.startsWith("/*")) {
27 // trim comments
28
29 // line comments
30 StringBuilder sb = new StringBuilder();
31 try (Scanner scanner = new Scanner(sql)) {
32 while (scanner.hasNextLine()) {
33 String line = scanner.nextLine().trim();
34 if (!line.startsWith("--")) {
35 sb.append(line).append("\n");
36 }
37 }
38 }
39 sql = sb.toString();
40
41 // /* */ comments
42 //Pattern commentPattern = Pattern.compile("/\\*.*?\\*/", Pattern.DOTALL);
43 sql = commentPattern.matcher(sql).replaceAll("").trim();
44 }
45
46 this.sql = sql;
47 this.type = SQLType.Select;
48 }
49
50 SQL(String sql, SQLType type) {
51 this.sql = sql.trim();
52 this.type = type;
53 }
54
55 /**
56 * Method to instantiate a regular SQL string.
57 * <pre>{@code
58 * Contact> contact;
59 * contact = session.fetch(Contact.class,
60 * sql("SELECT * FROM CONTACTS WHERE LAST_NAME = ? AND FIRST_NAME = ?"),
61 * params("Fred", "Flintstone");
62 * }</pre>
63 *
64 * @param sql String
65 * @return new SQL object
66 */
67 public static SQL sql(String sql) {
68 return new SQL(sql);
69 }
70
71 /**
72 * Method used to specify an SQL WHERE clause for an SQL Statement.
73 * The SELECT ... parts would be provided by Persism.
74 * Only here do we allow property names in the query
75 * <pre>{@code
76 * List<Contact> contacts;
77 * contacts = session.query(Contact.class,
78 * where("(:firstname = @name OR :company = @name) and :lastname = @last and :city = @city and :amountOwed > @owe ORDER BY :dateAdded"),
79 * named(Map.of("name", "Fred", "last", "Flintstone", "owe", 10, "city", "Somewhere")));
80 * }</pre>
81 *
82 * @param where String
83 * @return new SQL object
84 */
85 public static SQL where(String where) {
86 return new SQL("WHERE " + where, SQLType.Where);
87 }
88
89 /**
90 * Static initializer for a new SQL stored procedure string.
91 * <pre>{@code
92 * List<CustomerOrder> list;
93 * list = session.query(CustomerOrder.class, proc("[spCustomerOrders](?)"), params("123"));
94 * }</pre>
95 *
96 * @param storedProc String
97 * @return new SQL object
98 */
99 public static SQL proc(String storedProc) {
100 return new SQL(storedProc, SQLType.StoredProc);
101 }
102
103 /**
104 * @hidden
105 */
106 @Override
107 public String toString() {
108 if (processedSQL != null) {
109 return processedSQL;
110 }
111 return sql;
112 }
113 }