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;
}
}
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;
}
}
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);
}
}
/** 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);
}
/**
* 插入一条记录
*
* @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 SQLCount countKuponUses(Offer offer) {
try (Connection c = sql2o.open()) {
return c.createQuery(countKuponUsesQuery)
.addParameter("oid", offer.getId())
.executeAndFetchFirst(SQLCount.class);
}
}
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);
}
}
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();
}
}
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;
}
}
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;
}
}
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();
}
}
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;
}
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;
}
}
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;
}
}
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;
}
}
public void delete() {
try(Connection con = DB.sql2o.open()) {
String sql = "DELETE FROM clients WHERE id = :id;";
con.createQuery(sql)
.addParameter("id", id)
.executeUpdate();
}
}
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();
}
}
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();
}
}
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);
}
}
@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;
}
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();
}
}
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();
}
}
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);
}
}