예제 #1
0
  /** 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);
  }
예제 #2
0
 public SQLCount countKuponUses(Offer offer) {
   try (Connection c = sql2o.open()) {
     return c.createQuery(countKuponUsesQuery)
         .addParameter("oid", offer.getId())
         .executeAndFetchFirst(SQLCount.class);
   }
 }
예제 #3
0
  /**
   * 插入一条记录
   *
   * @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;
  }
예제 #4
0
 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;
 }
예제 #5
0
 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;
 }
예제 #6
0
 /**
  * 带参数更新
  *
  * @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;
 }
예제 #7
0
 @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;
 }
예제 #8
0
  /** 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);
    }
  }
예제 #9
0
 /**
  * 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);
   }
 }
예제 #10
0
  /**
   * 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);
    }
  }
예제 #11
0
  /**
   * 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);
    }
  }
예제 #12
0
  /** 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);
    }
  }
예제 #13
0
  @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);
  }