Ejemplo n.º 1
0
 public static Store find(int id) {
   try (Connection con = DB.sql2o.open()) {
     String sql = "SELECT * FROM stores where id=:id";
     Store store = con.createQuery(sql).addParameter("id", id).executeAndFetchFirst(Store.class);
     return store;
   }
 }
Ejemplo n.º 2
0
 public static Brand find(int id) {
   try (Connection con = DB.sql2o.open()) {
     String sql = "SELECT * FROM brands WHERE id=:id";
     Brand brand = con.createQuery(sql).addParameter("id", id).executeAndFetchFirst(Brand.class);
     return brand;
   }
 }
Ejemplo n.º 3
0
 public List<Brand> getBrands() {
   try (Connection con = DB.sql2o.open()) {
     String sql =
         "SELECT brands.* FROM stores JOIN stores_brands ON (stores_brands.store_id = stores.id) JOIN brands ON (stores_brands.brand_id = brands.id) WHERE store_id=:id";
     return con.createQuery(sql).addParameter("id", id).executeAndFetch(Brand.class);
   }
 }
Ejemplo n.º 4
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);
  }
Ejemplo n.º 5
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;
  }
Ejemplo n.º 6
0
 public SQLCount countKuponUses(Offer offer) {
   try (Connection c = sql2o.open()) {
     return c.createQuery(countKuponUsesQuery)
         .addParameter("oid", offer.getId())
         .executeAndFetchFirst(SQLCount.class);
   }
 }
Ejemplo n.º 7
0
  public UserDTO read(Integer id) {
    String sql = "select * from users where id = :id";

    try (Connection con = Sql2oHolder.SQL_2_O.open()) {
      return con.createQuery(sql).addParameter("id", id).executeAndFetchFirst(UserDTO.class);
    }
  }
Ejemplo n.º 8
0
 public void save() {
   try (Connection con = DB.sql2o.open()) {
     String sql = "INSERT INTO categories (name) VALUES (:name)";
     this.id =
         (int) con.createQuery(sql, true).addParameter("name", this.name).executeUpdate().getKey();
   }
 }
 public void updateName(String new_name) {
   this.name = new_name;
   String sql = "UPDATE clients SET name = :name WHERE id = :id;";
   try (Connection con = DB.sql2o.open()) {
     con.createQuery(sql).addParameter("name", name).addParameter("id", id).executeUpdate();
   }
 }
Ejemplo n.º 10
0
 public static Task find(int id) {
   try (Connection con = DB.sql2o.open()) {
     String sql = "SELECT id, description FROM tasks where id=:id";
     Task task = con.createQuery(sql).addParameter("id", id).executeAndFetchFirst(Task.class);
     return task;
   }
 }
Ejemplo n.º 11
0
 public void complete() {
   try (Connection con = DB.sql2o.open()) {
     String sql = "UPDATE tasks SET is_completed = true WHERE id = :id";
     con.createQuery(sql).addParameter("id", this.getId()).executeUpdate();
     completion = true;
   }
 }
Ejemplo n.º 12
0
 public void update(String name) {
   this.name = name;
   try (Connection con = DB.sql2o.open()) {
     String sql = "UPDATE stores SET name = :name WHERE id = :id";
     con.createQuery(sql).addParameter("name", name).addParameter("id", id).executeUpdate();
   }
 }
Ejemplo n.º 13
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;
 }
Ejemplo n.º 14
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;
 }
Ejemplo n.º 15
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;
 }
 protected void after() {
   try (Connection con = DB.sql2o.open()) {
     String deleteRestaurantsQuery = "DELETE FROM restaurants *;";
     String deleteCuisineQuery = "DELETE FROM cuisine *;";
     con.createQuery(deleteRestaurantsQuery).executeUpdate();
     con.createQuery(deleteCuisineQuery).executeUpdate();
   }
 }
 protected void after() {
   try (Connection con = DB.sql2o.open()) {
     String deletePeopleQuery = "DELETE FROM people *;";
     String deleteWeaponsQuery = "DELETE FROM weapons *;";
     con.createQuery(deletePeopleQuery).executeUpdate();
     con.createQuery(deleteWeaponsQuery).executeUpdate();
   }
 }
 public static Client find(int id) {
   String sql = "SELECT * FROM clients WHERE id = :id;";
   try (Connection con = DB.sql2o.open()) {
     Client client =
         con.createQuery(sql).addParameter("id", id).executeAndFetchFirst(Client.class);
     return client;
   }
 }
Ejemplo n.º 19
0
 public static Cuisine find(int id) {
   try (Connection con = DB.sql2o.open()) {
     String sql = "SELECT * FROM cuisines WHERE id=:id";
     Cuisine cuisine =
         con.createQuery(sql).addParameter("id", id).executeAndFetchFirst(Cuisine.class);
     return cuisine;
   }
 }
Ejemplo n.º 20
0
 public static Category find(int id) {
   try (Connection con = DB.sql2o.open()) {
     String sql = "SELECT * FROM categories where id=:id";
     Category Category =
         con.createQuery(sql).addParameter("id", id).executeAndFetchFirst(Category.class);
     return Category;
   }
 }
Ejemplo n.º 21
0
 public void delete() {
   try(Connection con = DB.sql2o.open()) {
     String sql = "DELETE FROM clients WHERE id = :id;";
     con.createQuery(sql)
       .addParameter("id", id)
       .executeUpdate();
   }
 }
Ejemplo n.º 22
0
 protected void after() {
   try (Connection con = DB.sql2o.open()) {
     String deleteBrandQuery = "DELETE FROM brands *;";
     String deleteStoreQuery = "DELETE FROM stores *;";
     con.createQuery(deleteBrandQuery).executeUpdate();
     con.createQuery(deleteStoreQuery).executeUpdate();
   }
 }
Ejemplo n.º 23
0
 public void update(String description) {
   try (Connection con = DB.sql2o.open()) {
     String sql = "UPDATE tasks SET description = :description WHERE id = :id";
     con.createQuery(sql)
         .addParameter("description", description)
         .addParameter("id", id)
         .executeUpdate();
   }
 }
Ejemplo n.º 24
0
 public List<Category> getCategories() {
   try (Connection con = DB.sql2o.open()) {
     String sql =
         "SELECT categories.id, categories.name FROM tasks INNER JOIN categories_tasks AS c_t ON tasks.id = c_t.task_id INNER JOIN categories ON categories.id = c_t.category_id WHERE c_t.task_id = :task_id";
     return con.createQuery(sql)
         .addParameter("task_id", this.getId())
         .executeAndFetch(Category.class);
   }
 }
Ejemplo n.º 25
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;
 }
Ejemplo n.º 26
0
 public void updateBrandName(String brand_name) {
   try (Connection con = DB.sql2o.open()) {
     String sql = "UPDATE brands SET brand_name = :brand_name WHERE id = :id";
     con.createQuery(sql)
         .addParameter("brand_name", brand_name)
         .addParameter("id", id)
         .executeUpdate();
   }
 }
Ejemplo n.º 27
0
 public void addBrand(Brand brand) {
   try (Connection con = DB.sql2o.open()) {
     String sql = "INSERT INTO stores_brands (store_id, brand_id) VALUES (:store_id, :brand_id)";
     con.createQuery(sql)
         .addParameter("store_id", this.getId())
         .addParameter("brand_id", brand.getId())
         .executeUpdate();
   }
 }
Ejemplo n.º 28
0
  public void delete() {
    try (Connection con = DB.sql2o.open()) {
      String deleteQuery = "DELETE FROM stores WHERE id = :id;";
      con.createQuery(deleteQuery).addParameter("id", id).executeUpdate();

      String joinDeleteQuery = "DELETE FROM stores_brands WHERE store_id = :store_id";
      con.createQuery(joinDeleteQuery).addParameter("store_id", this.getId()).executeUpdate();
    }
  }
 public static int delete(int organizationId) {
   try (Connection con = DB.sql2o.open()) {
     String sql =
         "DELETE FROM farmer_organization_experience WHERE organization_id = :organizationId";
     return con.createQuery(sql)
         .addParameter("organizationId", organizationId)
         .executeUpdate()
         .getResult();
   }
 }
 public static FarmerOrganizationExperience selectOne(int organizationId) {
   try (Connection con = DB.sql2o.open()) {
     String sql =
         "SELECT organization_id AS organizationId, farmer_id AS farmerId, type, name, position, year_joined AS yearJoined, activity_description AS description "
             + "FROM farmer_organization_experience WHERE organization_id = :organizationId";
     return con.createQuery(sql)
         .addParameter("organizationId", organizationId)
         .executeAndFetchFirst(FarmerOrganizationExperience.class);
   }
 }