/** Update the specified Todo entry with new information */
public Todo update(String todoId, String body) throws TodoServiceException {
Todo todo = new Gson().fromJson(body, Todo.class);
String sql =
"UPDATE item SET title = :title, done = :done, created_on = :createdOn WHERE item_id = :itemId ";
try (Connection conn = db.open()) {
// Update the item
conn.createQuery(sql)
.bind(todo) // one-liner to map all Todo object fields to query parameters :title etc
.addParameter("itemId", Integer.parseInt(todoId))
.executeUpdate();
// Verify that we did indeed update something
if (getChangedRows(conn) != 1) {
logger.error(
String.format(
"TodoService.update: Update operation did not update rows. Incorrect id(?): %s",
todoId));
throw new TodoServiceException(
String.format(
"TodoService.update: Update operation did not update rows. Incorrect id(?): %s",
todoId),
null);
}
} catch (Sql2oException ex) {
logger.error(
String.format("TodoService.update: Failed to update database for id: %s", todoId), ex);
throw new TodoServiceException(
String.format("TodoService.update: Failed to update database for id: %s", todoId), ex);
}
return find(todoId);
}
public SQLCount countKuponUses(Offer offer) {
try (Connection c = sql2o.open()) {
return c.createQuery(countKuponUsesQuery)
.addParameter("oid", offer.getId())
.executeAndFetchFirst(SQLCount.class);
}
}
/**
* 插入一条记录
*
* @param sql
* @param params
* @return
*/
public static int insert(String sql, Object... params) {
StringBuffer sqlBuf = new StringBuffer(sql);
sqlBuf.append(" values (");
int start = sql.indexOf("(") + 1;
int end = sql.indexOf(")");
String a = sql.substring(start, end);
String[] fields = a.split(",");
Map<String, Object> map = new HashMap<String, Object>();
int i = 0;
for (String name : fields) {
sqlBuf.append(":" + name.trim() + " ,");
map.put(name.trim(), params[i]);
i++;
}
String newSql = sqlBuf.substring(0, sqlBuf.length() - 1) + ")";
Connection con = sql2o.open();
Query query = con.createQuery(newSql);
executeQuery(query, map);
int res = query.executeUpdate().getResult();
con.close();
return res;
}
public static <T> List<T> getList(String sql, Class<T> clazz, Map<String, Object> params) {
Connection con = sql2o.open();
Query query = con.createQuery(sql);
executeQuery(query, params);
List<T> list = query.executeAndFetch(clazz);
con.close();
return list;
}
public static List<Map<String, Object>> getMapList(String sql, Map<String, Object> params) {
Connection con = sql2o.open();
Query query = con.createQuery(sql);
executeQuery(query, params);
List<Map<String, Object>> t = query.executeAndFetchTable().asList();
con.close();
return t;
}
/**
* 带参数更新
*
* @param sql
* @param params
* @return
*/
public static int update(String sql, Map<String, Object> params) {
Connection con = sql2o.open();
Query query = con.createQuery(sql);
executeQuery(query, params);
int res = query.executeUpdate().getResult();
con.close();
return res;
}
@SuppressWarnings("unchecked")
public static Map<String, Object> getMap(String sql, Map<String, Object> params) {
Connection con = sql2o.open();
Query query = con.createQuery(sql);
executeQuery(query, params);
Map<String, Object> t = (Map<String, Object>) query.executeScalar();
con.close();
return t;
}
/** Create a new Todo entry. */
public void createNewTodo(String body) throws TodoServiceException {
Todo todo = new Gson().fromJson(body, Todo.class);
String sql =
"INSERT INTO item (title, done, created_on) "
+ " VALUES (:title, :done, :createdOn)";
try (Connection conn = db.open()) {
conn.createQuery(sql).bind(todo).executeUpdate();
} catch (Sql2oException ex) {
logger.error("TodoService.createNewTodo: Failed to create new entry", ex);
throw new TodoServiceException("TodoService.createNewTodo: Failed to create new entry", ex);
}
}
/**
* Fetch all todo entries in the list
*
* @return List of all Todo entries
*/
public List<Todo> findAll() throws TodoServiceException {
String sql = "SELECT * FROM item";
try (Connection conn = db.open()) {
List<Todo> todos =
conn.createQuery(sql)
.addColumnMapping("item_id", "id")
.addColumnMapping("created_on", "createdOn")
.executeAndFetch(Todo.class);
return todos;
} catch (Sql2oException ex) {
logger.error("TodoService.findAll: Failed to query database", ex);
throw new TodoServiceException("TodoService.findAll: Failed to query database", ex);
}
}
/**
* Find a todo entry given an Id.
*
* @param id The id for the Todo entry
* @return The Todo corresponding to the id if one is found, otherwise null
*/
public Todo find(String id) throws TodoServiceException {
String sql = "SELECT * FROM item WHERE item_id = :itemId ";
try (Connection conn = db.open()) {
return conn.createQuery(sql)
.addParameter("itemId", Integer.parseInt(id))
.addColumnMapping("item_id", "id")
.addColumnMapping("created_on", "createdOn")
.executeAndFetchFirst(Todo.class);
} catch (Sql2oException ex) {
logger.error(String.format("TodoService.find: Failed to query database for id: %s", id), ex);
throw new TodoServiceException(
String.format("TodoService.find: Failed to query database for id: %s", id), ex);
}
}
/**
* Construct the model with a pre-defined datasource. The current implementation also ensures that
* the DB schema is created if necessary.
*
* @param dataSource
*/
public TodoService(DataSource dataSource) throws TodoServiceException {
db = new Sql2o(dataSource);
// Create the schema for the database if necessary. This allows this
// program to mostly self-contained. But this is not always what you want;
// sometimes you want to create the schema externally via a script.
try (Connection conn = db.open()) {
String sql =
"CREATE TABLE IF NOT EXISTS item (item_id INTEGER PRIMARY KEY AUTOINCREMENT, "
+ " title TEXT, done BOOLEAN, created_on TIMESTAMP)";
conn.createQuery(sql).executeUpdate();
} catch (Sql2oException ex) {
logger.error("Failed to create schema at startup", ex);
throw new TodoServiceException("Failed to create schema at startup", ex);
}
}
/** Delete the entry with the specified id */
public void delete(String todoId) throws TodoServiceException {
String sql = "DELETE FROM item WHERE item_id = :itemId";
try (Connection conn = db.open()) {
// Delete the item
conn.createQuery(sql).addParameter("itemId", Integer.parseInt(todoId)).executeUpdate();
// Verify that we did indeed change something
if (getChangedRows(conn) != 1) {
logger.error(
String.format(
"TodoService.delete: Delete operation did not delete rows. Incorrect id(?): %s",
todoId));
throw new TodoServiceException(
String.format(
"TodoService.delete: Delete operation did not delete rows. Incorrect id(?): %s",
todoId),
null);
}
} catch (Sql2oException ex) {
logger.error(String.format("TodoService.update: Failed to delete id: %s", todoId), ex);
throw new TodoServiceException(
String.format("TodoService.update: Failed to delete id: %s", todoId), ex);
}
}
@RequestMapping(path = "/system/sync")
public ResponseEntity<RestResponse> sync(
Authentication authentication, HttpServletRequest request) throws Throwable {
String json = org.apache.commons.io.IOUtils.toString(request.getInputStream(), "UTF-8");
Sync sync = this.gson.fromJson(json, Sync.class);
if (sync == null) {
sync = new Sync();
}
List<String> pageIds = new ArrayList<>();
List<String> restIds = new ArrayList<>();
try (Connection connection = sql2o.open()) {
if (sync.getPages() != null && !sync.getPages().isEmpty()) {
for (Page clientPage : sync.getPages()) {
Query query =
connection.createQuery(
"select groovy.java_class as javaClass, page.path as mountPath, groovy.groovy_id as groovyId, page.page_id as pageId, html as serverHtml, html_crc32 as serverHtmlCrc32, groovy.script as serverGroovy, groovy.script_crc32 as serverGroovyCrc32 from page inner join groovy on page.groovy_id = groovy.groovy_id where page.page_id = :pageId");
query.addParameter("pageId", clientPage.getPageId());
Page serverPage = query.executeAndFetchFirst(Page.class);
boolean groovyConflicted =
!clientPage.getServerGroovyCrc32().equals(serverPage.getServerGroovyCrc32());
boolean htmlConflicted =
!clientPage.getServerHtmlCrc32().equals(serverPage.getServerHtmlCrc32());
pageIds.add(clientPage.getPageId());
clientPage.setGroovyConflicted(groovyConflicted);
clientPage.setHtmlConflicted(htmlConflicted);
String path = StringUtils.replaceChars(serverPage.getJavaClass(), '.', '/');
clientPage.setHtmlPath(path + ".html");
clientPage.setGroovyPath(path + ".groovy");
if (!groovyConflicted
&& !htmlConflicted
&& Strings.isNullOrEmpty(clientPage.getClientGroovyCrc32())
&& Strings.isNullOrEmpty(clientPage.getClientHtmlCrc32())) {
// delete command
clientPage.setServerGroovyCrc32(null);
clientPage.setServerGroovy(null);
clientPage.setServerHtmlCrc32(null);
clientPage.setServerHtml(null);
classLoader.removeSourceCache(serverPage.getGroovyId());
classLoader.removeClassCache(serverPage.getJavaClass());
connection
.createQuery("delete from page where page_id = :page_id")
.addParameter("page_id", serverPage.getPageId())
.executeUpdate();
connection
.createQuery("delete from groovy where groovy_id = :groovy_id")
.addParameter("groovy_id", serverPage.getGroovyId())
.executeUpdate();
Application.get().unmount(serverPage.getMountPath());
Application.get().getMarkupSettings().getMarkupFactory().getMarkupCache().clear();
} else {
if (!groovyConflicted) {
// update command
classLoader.removeSourceCache(serverPage.getGroovyId());
classLoader.removeClassCache(serverPage.getJavaClass());
GroovyCodeSource source =
new GroovyCodeSource(
Strings.isNullOrEmpty(clientPage.getClientGroovy())
? serverPage.getServerGroovy()
: clientPage.getClientGroovy(),
serverPage.getGroovyId(),
"/groovy/script");
source.setCachable(true);
Class<?> pageClass = classLoader.parseClass(source, true);
Application.get()
.mountPage(
serverPage.getMountPath(),
(Class<? extends org.apache.wicket.Page>) pageClass);
connection
.createQuery(
"update groovy set script = :script, script_crc32 = :script_crc32, java_class = :java_class where groovy_id = :groovy_id")
.addParameter(
"script",
Strings.isNullOrEmpty(clientPage.getClientGroovy())
? serverPage.getServerGroovy()
: clientPage.getClientGroovy())
.addParameter("script_crc32", clientPage.getClientGroovyCrc32())
.addParameter("java_class", pageClass.getName())
.addParameter("groovy_id", serverPage.getGroovyId())
.executeUpdate();
clientPage.setServerGroovyCrc32(clientPage.getClientGroovyCrc32());
clientPage.setServerGroovy(
Strings.isNullOrEmpty(clientPage.getClientGroovy())
? serverPage.getServerGroovy()
: clientPage.getClientGroovy());
} else {
clientPage.setServerGroovyCrc32(serverPage.getServerGroovyCrc32());
clientPage.setServerGroovy(serverPage.getServerGroovy());
if (Strings.isNullOrEmpty(clientPage.getClientGroovy())
&& Strings.isNullOrEmpty(clientPage.getClientGroovyCrc32())) {
clientPage.setGroovyConflicted(false);
}
}
if (!htmlConflicted) {
// update command
connection
.createQuery(
"update page set html = :html, html_crc32 = :html_crc32 where page_id = :page_id")
.addParameter(
"html",
Strings.isNullOrEmpty(clientPage.getClientHtml())
? serverPage.getServerHtml()
: clientPage.getClientHtml())
.addParameter("html_crc32", clientPage.getClientHtmlCrc32())
.addParameter("page_id", serverPage.getPageId())
.executeUpdate();
Application.get().getMarkupSettings().getMarkupFactory().getMarkupCache().clear();
clientPage.setServerHtmlCrc32(clientPage.getClientHtmlCrc32());
clientPage.setServerHtml(
Strings.isNullOrEmpty(clientPage.getClientHtml())
? serverPage.getServerHtml()
: clientPage.getClientHtml());
} else {
clientPage.setServerHtmlCrc32(serverPage.getServerHtmlCrc32());
clientPage.setServerHtml(serverPage.getServerHtml());
}
}
}
}
if (sync.getRests() != null && !sync.getRests().isEmpty()) {
for (Rest clientRest : sync.getRests()) {
restIds.add(clientRest.getRestId());
Query query =
connection.createQuery(
"select groovy.java_class as javaClass, groovy.groovy_id as groovyId, rest.rest_id as restId, groovy.script as serverGroovy, groovy.script_crc32 as serverGroovyCrc32 from rest inner join groovy on rest.groovy_id = groovy.groovy_id where rest.rest_id = :restId");
query.addParameter("restId", clientRest.getRestId());
Rest serverRest = query.executeAndFetchFirst(Rest.class);
boolean groovyConflicted =
!clientRest.getServerGroovyCrc32().equals(serverRest.getServerGroovyCrc32());
clientRest.setGroovyConflicted(groovyConflicted);
String path = StringUtils.replaceChars(serverRest.getJavaClass(), '.', '/');
clientRest.setGroovyPath(path + ".groovy");
if (!groovyConflicted && Strings.isNullOrEmpty(clientRest.getClientGroovyCrc32())) {
// delete command
clientRest.setServerGroovy(null);
clientRest.setServerGroovyCrc32(null);
classLoader.removeSourceCache(serverRest.getGroovyId());
classLoader.removeClassCache(serverRest.getJavaClass());
connection
.createQuery("delete from rest where rest_id = :rest_id")
.addParameter("rest_id", serverRest.getRestId())
.executeUpdate();
connection
.createQuery("delete from groovy where groovy_id = :groovy_id")
.addParameter("groovy_id", serverRest.getGroovyId())
.executeUpdate();
} else {
if (!groovyConflicted) {
// update command
classLoader.removeSourceCache(serverRest.getGroovyId());
classLoader.removeClassCache(serverRest.getJavaClass());
GroovyCodeSource source =
new GroovyCodeSource(
Strings.isNullOrEmpty(clientRest.getClientGroovy())
? serverRest.getServerGroovy()
: clientRest.getClientGroovy(),
serverRest.getGroovyId(),
"/groovy/script");
source.setCachable(true);
Class<?> serviceClass = classLoader.parseClass(source, true);
connection
.createQuery(
"update groovy set script = :script, script_crc32 = :script_crc32, java_class = :java_class where groovy_id = :groovy_id")
.addParameter(
"script",
Strings.isNullOrEmpty(clientRest.getClientGroovy())
? serverRest.getServerGroovy()
: clientRest.getClientGroovy())
.addParameter("script_crc32", clientRest.getClientGroovyCrc32())
.addParameter("java_class", serviceClass.getName())
.addParameter("groovy_id", serverRest.getGroovyId())
.executeUpdate();
clientRest.setServerGroovyCrc32(clientRest.getClientGroovyCrc32());
clientRest.setServerGroovy(
Strings.isNullOrEmpty(clientRest.getClientGroovy())
? serverRest.getServerGroovy()
: clientRest.getClientGroovy());
} else {
clientRest.setServerGroovyCrc32(serverRest.getServerGroovyCrc32());
clientRest.setServerGroovy(serverRest.getServerGroovy());
}
}
}
}
List<Page> serverPages;
if (!pageIds.isEmpty()) {
Query query =
connection.createQuery(
"select groovy.java_class as javaClass, page.page_id as pageId, html as serverHtml, html_crc32 as serverHtmlCrc32, groovy.script as serverGroovy, groovy.script_crc32 as serverGroovyCrc32 from page inner join groovy on page.groovy_id = groovy.groovy_id where page.system = false and page.page_id not in (:pageId)");
query.addParameter("pageId", pageIds);
serverPages = query.executeAndFetch(Page.class);
} else {
Query query =
connection.createQuery(
"select groovy.java_class as javaClass, page.page_id as pageId, html as serverHtml, html_crc32 as serverHtmlCrc32, groovy.script as serverGroovy, groovy.script_crc32 as serverGroovyCrc32 from page inner join groovy on page.groovy_id = groovy.groovy_id where page.system = false");
serverPages = query.executeAndFetch(Page.class);
}
if (serverPages != null && !serverPages.isEmpty()) {
for (Page serverPage : serverPages) {
String path = StringUtils.replaceChars(serverPage.getJavaClass(), '.', '/');
serverPage.setHtmlPath(path + ".html");
serverPage.setGroovyPath(path + ".groovy");
serverPage.setGroovyConflicted(false);
serverPage.setHtmlConflicted(false);
sync.addPage(serverPage);
}
}
List<Rest> serverRests;
if (!restIds.isEmpty()) {
Query query =
connection.createQuery(
"select groovy.java_class as javaClass, rest.rest_id as restId, groovy.script as serverGroovy, groovy.script_crc32 as serverGroovyCrc32 from rest inner join groovy on rest.groovy_id = groovy.groovy_id where rest.system = false and rest.rest_id not in (:restId)");
query.addParameter("restId", restIds);
serverRests = query.executeAndFetch(Rest.class);
} else {
Query query =
connection.createQuery(
"select groovy.java_class as javaClass, rest.rest_id as restId, groovy.script as serverGroovy, groovy.script_crc32 as serverGroovyCrc32 from rest inner join groovy on rest.groovy_id = groovy.groovy_id where rest.system = false");
serverRests = query.executeAndFetch(Rest.class);
}
if (serverRests != null && !serverRests.isEmpty()) {
for (Rest restPage : serverRests) {
String path = StringUtils.replaceChars(restPage.getJavaClass(), '.', '/');
restPage.setGroovyPath(path + ".groovy");
restPage.setGroovyConflicted(false);
sync.addRest(restPage);
}
}
}
RestResponse response = new RestResponse();
response.setData(sync);
response.setResultCode(HttpStatus.OK.value());
response.setResultMessage(HttpStatus.OK.getReasonPhrase());
return ResponseEntity.ok(response);
}