com.ajaxjs.website.section.TreeLikeoDao Maven / Gradle / Ivy

Go to download
package com.ajaxjs.website.section;

import java.util.List;

import com.ajaxjs.sql.annotation.Delete;
import com.ajaxjs.sql.annotation.Select;
import com.ajaxjs.sql.annotation.TableName;
import com.ajaxjs.sql.orm.IBaseDao;

@TableName(value = "common_catalog", beanClass = TreeNode.class)
public interface TreeLikeoDao extends IBaseDao {
	/**
	 * 左连接分类表，实体简写必须为 e
	 */
	public final static String LEFT_JOIN_CATALOG = " LEFT JOIN common_catalog gc ON gc.id = e.catalogId ";

	/**
	 * 关联分类表以获取分类名称，即增加了 catalogName 字段。另外如果前台不需要显示的话，只是后台的话，可以用 map 显示
	 */
	public final static String SELECT_CATALOGNAME = "SELECT e.*, gc.name catalogName FROM ${tableName} e" + LEFT_JOIN_CATALOG + WHERE_REMARK_ORDER;

	/**
	 * 获取 pid 下面的所有子节点，无论下面有多少级
	 * 
	 * @param pid
	 * @return
	 */
	@Select(value = "SELECT * FROM common_catalog WHERE `path` LIKE (CONCAT ((SELECT `path` FROM common_catalog WHERE id = ?) , '/%')) ORDER BY pid ", 
			sqliteValue = "SELECT * FROM common_catalog WHERE `path` LIKE ((SELECT `path` FROM common_catalog WHERE id = ?) || '/%')")
	List getAllChildren(int pid);

	/**
	 * 删除所有，包括子分类 如果子查询的 from 子句和更新、删除对象使用同一张表，会出现错误。
	 * 
	 * @param id
	 * @return
	 */
	@Delete("DELETE FROM ${tableName} WHERE id in ( SELECT n.id FROM ("
			+ "(SELECT id FROM ${tableName} WHERE `path` LIKE ( CONCAT ( (SELECT `path` FROM common_catalog WHERE id = ?) , '%')))) AS n)")
	public boolean deleteAll(int id);
}