public static List getRecords(Dao dao, String sql, Class t) {
Sql s = Sqls.create(sql);
s.setCallback(Sqls.callback.entities());
s.setEntity(dao.getEntity(t));
dao.execute(s);
return s.getList(Record.class);
}
private void resourceRelationFull(Resource resource) {
List<Tag> tags;
Sql sql_resource =
Sqls.create(
"select * from tag as t, resource_tag as rt where rt.resource_id = "
+ resource.getId()
+ " and rt.tag_id = t.id");
sql_resource.setCallback(Sqls.callback.entities());
sql_resource.setEntity(dao.getEntity(Tag.class));
dao.execute(sql_resource);
tags = sql_resource.getList(Tag.class);
List<UserInfo> user = dao.query(UserInfo.class, Cnd.where("id", "=", resource.getAuthor()));
if (user.size() > 0) {
resource.setUserInfo(user.get(0));
} else {
System.out.print("resourceservice:用户id无效");
}
String tagString = ""; // 资源tag回填
for (int i = 0; i < tags.size(); i++) {
if (i < (tags.size() - 1)) {
tagString = tagString + tags.get(i).getName() + ",";
} else {
tagString = tagString + tags.get(i).getName();
}
}
resource.setTags(tagString);
resource.setTagEntityList(tags);
}
/**
* 无and关系的tag查询
*
* @param tagIds 标签ids
* @param order
* @param pager
* @return
*/
public List<Resource> resourceTagSearch(Integer tagId, String order, Pager pager) {
List<Resource> query = null;
if (tagId != null) {
Sql sql_resource =
Sqls.create(
"select * from resource as r, resource_tag as rt where rt.resource_id = r.id and rt.tag_id = "
+ tagId);
sql_resource.setCallback(Sqls.callback.entities());
sql_resource.setEntity(dao.getEntity(Resource.class));
dao.execute(sql_resource);
query = sql_resource.getList(Resource.class);
if (pager != null) {
pager.setRecordCount(query.size());
}
} else {
System.out.print("resourceservice: tagid无效");
}
for (Resource resource : query) {
resourceRelationFull(resource);
}
return query;
}
@Override
public List<Item> listWithSkuByOrder(long orderId) {
String exp =
"select i.id as 'i.id', i.sku_id as 'i.sku_id', i.sku_more_id as 'i.sku_more_id'\n"
+ " , i.dprice as 'i.dprice', i.dcount as 'i.dcount', i.payment as 'i.payment'\n"
+ " , i.return_time as 'i.return_time', i.return_reason as 'i.return_reason'"
+ " , i.return_desc as 'i.return_desc'\n"
+ " , i.state as 'i.state', i.order_id as 'i.order_id'\n"
+ " , g.cate_code as 's.cate_code', g.gname as 's.gname', sku.img as 's.img'\n"
+ " , sku.model as 's.model', sku.sprice as 's.sprice', sm.size as 's.size'\n"
+ "from t_item i\n"
+ "inner join t_sku sku on sku.id = i.sku_id\n"
+ "inner join t_sku_more sm on sm.id = i.sku_more_id\n"
+ "inner join t_goods g on g.id = sku.goods_id\n"
+ "where i.order_id = @orderId";
Sql sql = Sqls.queryRecord(exp);
sql.params().set("orderId", orderId);
dao.execute(sql);
List<Record> list = sql.getList(Record.class);
List<Item> itemList = new ArrayList<Item>();
for (Record re : list) {
Item item = re.toEntity(dao.getEntity(Item.class), "i.");
Sku4Item skuInfo = re.toEntity(dao.getEntity(Sku4Item.class), "s.");
item.setSku(skuInfo);
itemList.add(item);
}
return itemList;
}
public static List<Record> getRecords(Dao dao, String sql, int page, int size) {
Sql s = Sqls.create(sql);
s.setCallback(Sqls.callback.entities());
s.setPager(dao.createPager(page, size));
s.setEntity(dao.getEntity(Record.class));
dao.execute(s);
return (List<Record>) s.getList(Record.class);
}
/** 查询sql并把结果放入传入的class组成的List中 */
public static <T> List<T> query(
Dao dao, Class<T> classOfT, String sql, Condition cnd, Pager pager) {
Sql sql2 = Sqls.queryEntity(sql);
sql2.setEntity(dao.getEntity(classOfT));
sql2.setCondition(cnd);
sql2.setPager(pager);
dao.execute(sql2);
return sql2.getList(classOfT);
}
/**
* 查询某一次考试的科目名称
*
* @param testid
* @return 对应考试id的科目信息
*/
public List<Subject> querysubject(int testid) {
Sql sql =
Sqls.create(
"SELECT ts.* FROM t_subject ts JOIN t_test_subject tts ON tts.subject_id=ts.id where tts.test_id=@tid ");
sql.params().set("tid", testid);
sql.setCallback(Sqls.callback.entities());
sql.setEntity(dao().getEntity(Subject.class));
dao().execute(sql);
List<Subject> list = sql.getList(Subject.class);
return list;
}
/**
* 用没有处理过的Nutz.sql带分页
*
* @param dao
* @param s
* @param page
* @param size
* @return
*/
public static Record getRecordsByNutSql(Dao dao, Sql s, int page, int size) {
int total = getRecordSize(dao, s.toString());
s.setCallback(Sqls.callback.entities());
s.setPager(dao.createPager(page, size));
s.setEntity(dao.getEntity(Record.class));
dao.execute(s);
Record rd = new Record();
List<Record> records = s.getList(Record.class);
rd.put("total", total);
rd.put("rows", records);
return rd;
}
/**
* 按照tag分页查找 resource
*
* @param tagId 标签id
* @param order
* @param pager
* @return
*/
public List<Resource> resourceTagSearchAnd(String tagIds, String order, Pager pager) {
List<Resource> query = null;
// 将tagids字符串拆分成id数组进行and查询
String[] ids = tagIds.split(",");
if (ids.length > 0) {
String sqlTagsResource =
"select * from resource as r, resource_tag as rt where (rt.resource_id = r.id and rt.tag_id = "
+ Integer.parseInt(ids[0])
+ ")";
for (int j = 1; j < ids.length; j++) {
sqlTagsResource =
sqlTagsResource
+ " and (r.id in (select r"
+ j
+ ".id from resource as r"
+ j
+ ", resource_tag as rt"
+ j
+ " where (rt"
+ j
+ ".resource_id = r"
+ j
+ ".id and rt"
+ j
+ ".tag_id = "
+ Integer.parseInt(ids[j])
+ ")))";
}
Sql sql_resource = Sqls.create(sqlTagsResource);
sql_resource.setCallback(Sqls.callback.entities());
sql_resource.setEntity(dao.getEntity(Resource.class));
dao.execute(sql_resource);
query = sql_resource.getList(Resource.class);
if (pager != null) {
pager.setRecordCount(query.size());
}
for (Resource resource : query) {
resourceRelationFull(resource);
}
} else {
System.out.print("resourservice: tagids无效");
}
return query;
}
/**
* 获取tagNames
*
* @param tagIds 标签ids
* @return
*/
public ArrayList<Tag> getTagNames(String tagIds) {
ArrayList<Tag> tagNames = new ArrayList<Tag>();
String[] ids = tagIds.split(",");
for (String id : ids) {
String sqlTags = "select * from tag as t where t.id=" + id;
Sql sql_resource = Sqls.create(sqlTags);
sql_resource.setCallback(Sqls.callback.entities());
sql_resource.setEntity(dao.getEntity(Tag.class));
dao.execute(sql_resource);
List<Tag> tags = sql_resource.getList(Tag.class);
if (tags.size() > 0) {
tags.get(0).setQuery(tagIds);
tagNames.add(tags.get(0));
} else {
System.out.print("resourceservice: tagid无效");
}
}
return tagNames;
}
/**
* 根据用户id资源查询
*
* @param userId 用户id
* @param order
* @param pager
* @return
*/
public List<Resource> userResourceSearch(Integer userId, String order, Pager pager) {
List<Resource> query = null;
if (userId != null) {
Sql sql_resource = Sqls.create("select * from resource as r where r.author = " + userId);
sql_resource.setCallback(Sqls.callback.entities());
sql_resource.setEntity(dao.getEntity(Resource.class));
dao.execute(sql_resource);
query = sql_resource.getList(Resource.class);
if (pager != null) {
pager.setRecordCount(query.size());
}
} else {
System.out.print("resourceservice: userid无效");
}
for (Resource resource : query) {
resourceRelationFull(resource);
}
return query;
}
/**
* 根据sql查询特定的记录,并转化为指定的类对象
*
* @param dao Dao实例
* @param klass Pojo类
* @param sql_str sql语句
* @return 查询结果
*/
public static <T> List<T> queryList(Dao dao, Class<T> klass, String sql_str) {
Sql sql =
Sqls.create(sql_str).setCallback(Sqls.callback.entities()).setEntity(dao.getEntity(klass));
dao.execute(sql);
return sql.getList(klass);
}