NutzCN Logo
分享 nutz动态sql分享
发布于 2426天前 作者 qq_322b2e5d 2272 次浏览 复制 上一个帖子 下一个帖子
标签:

一离职哥们基于nutz搭建了一系统,走时对框架自己都理不清楚。
看着nutz的动态sql,非常不爽,文档里说是什么小巧的设计,我看着都郁闷,看官看下:

/* insertUser */

insert into tbl_user (uname,uage) values (@uname, @uage)

丫的,用注释代替sql名称,谁丫设计的,服了,如果是复杂sql,一战友随便写个注释,傻子才能看懂。
随对整个dao层进行改造。哎,很多复杂的sql,没有文档,我对逐一猜测,然后添加汉语注释。一个字:服。
不能跟风选择框架。血的教训。改造:
1:定义sql格式:

<?xml version="1.0" encoding="UTF-8"?>
<!-- SQL Dynamic Statement Mapping DTD.

<!DOCTYPE dynamic-sql-statement>
-->

<!--
	The document root.
 -->

<!ELEMENT dynamic-sql-statement (
	(sql-query)*
)>
							<!-- default: none -->

<!-- The sql-query element declares a named SQL query string -->

<!ELEMENT sql-query (#PCDATA)>
	<!ATTLIST sql-query name CDATA #REQUIRED>

2:定义一个sql解析接口类吧;

interface DynamicSqlAssembleBuilder {

    /**
     * sql语句map
     *
     * @return
     */
    Map<String, String> getNamedSQLQueries();

    /**
     * 初始化
     *
     * @throws IOException
     */
    void init() throws IOException;
}

3:实现类:

public class DefaultDynamicSqlAssembleBuilder implements
        DynamicSqlAssembleBuilder {
    private static final Logger LOGGER = LoggerFactory
            .getLogger(DefaultDynamicSqlAssembleBuilder.class);
    private Map<String, String> namedSQLQueries;
    /**
     * 资源文件路径
     */
    private String[] fileNames = new String[0];
    /**
     * 查询语句名称缓存,不允许重复
     */
    private Set<String> nameCache = new HashSet<>();

    /**
     * 资源文件路径
     *
     * @param fileNames
     */
    public void setFileNames(String[] fileNames) {
        this.fileNames = fileNames;
    }

    @Override
    public Map<String, String> getNamedSQLQueries() {
        return namedSQLQueries;
    }

    @Override
    public void init() throws IOException {
        namedSQLQueries = new ConcurrentHashMap<>();
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(Thread.currentThread().getContextClassLoader());
        for (String file : fileNames) {
            Resource[] resources = resolver.getResources(file);
            buildMap(resources);
        }
        // clear name cache
        nameCache.clear();
    }

    private void buildMap(Resource[] resources) throws IOException {
        if (resources == null) {
            return;
        }
        for (Resource resource : resources) {
            buildMap(resource);
        }
    }


    public XmlDocument readMappingDocument(InputSource source, Origin origin) {
        SAXReader saxReader = new SAXReader();
        Document document;
        try {
            document = saxReader.read(source);
            return new XmlDocumentImpl(document, origin.getType(), origin.getName());
        } catch (Exception orm2Problem) {
            orm2Problem.printStackTrace();
        }
        return null;
    }

    private void buildMap(Resource resource) {
        InputSource inputSource = null;
        try {
            inputSource = new InputSource(resource.getInputStream());

            XmlDocument metadataXml = readMappingDocument(inputSource,
                    new OriginImpl("file", resource.getFilename()));
            if (isDynamicStatementXml(metadataXml)) {
                final Document doc = metadataXml.getDocumentTree();
                final Element dynamicHibernateStatement = doc.getRootElement();
                //域
                String zone = null;
                Attribute zoneAttr = dynamicHibernateStatement.attribute("name");
                if (zoneAttr != null) {
                    zone = zoneAttr.getText();
                }
                Iterator<?> rootChildren = dynamicHibernateStatement
                        .elementIterator();
                while (rootChildren.hasNext()) {
                    final Element element = (Element) rootChildren.next();
                    final String elementName = element.getName();
                    if ("sql-query".equals(elementName)) {
                        putStatementToCacheMap(zone, resource, element,
                                namedSQLQueries);
                    }
                }
            }
        } catch (Exception e) {
            LOGGER.error(e.toString());
            //throw new RuntimeException(e);
            e.printStackTrace();
        } finally {
            if (inputSource != null && inputSource.getByteStream() != null) {
                try {
                    inputSource.getByteStream().close();
                } catch (IOException e) {
                    LOGGER.error(e.toString());
                    throw new RuntimeException(e);
                }
            }
        }

    }

    private void putStatementToCacheMap(String zone, Resource resource,
                                        final Element element, Map<String, String> statementMap)
            throws IOException {
        String sqlQueryName = element.attribute("name").getText();
        if (StringUtils.isNotBlank(zone)) {
            sqlQueryName = zone + "." + sqlQueryName;
        }
        Validate.notEmpty(sqlQueryName);
        if (nameCache.contains(sqlQueryName)) {
            throw new RuntimeException("重复的sql-query语句定义在文件:"
                    + resource.getURI() + "中,必须保证name的唯一.");
        }
        nameCache.add(sqlQueryName);
        String queryText = element.getText();
        statementMap.put(sqlQueryName, queryText);
    }

    private static boolean isDynamicStatementXml(XmlDocument xmlDocument) {
        return "sql".equals(xmlDocument
                .getDocumentTree().getRootElement().getName());
    }
}

4:基于nutz扩展,找个FileSqlManager改造下吧

public class DynamicSqlManager implements SqlManager {

    private static final Log log = Logs.get();

    Map<String, String> sqls = Collections.synchronizedMap(new LinkedHashMap<String, String>());

    protected String[] paths;

    protected boolean allowDuplicate = true;


    public DynamicSqlManager(String... paths) {
        this.paths = paths;
        refresh();
    }

    protected String processTemplate(Template statementTemplate,
                                     Map<String, ?> parameters) {
        StringWriter stringWriter = new StringWriter();
        try {
            statementTemplate.process(parameters, stringWriter);
        } catch (Exception e) {
            log.error("处理DAO查询参数模板时发生错误:" + e.toString());
            throw new RuntimeException(e);
        }
        return stringWriter.toString();
    }

    public void refresh() {
        DefaultDynamicSqlAssembleBuilder dynamicAssembleBuilder = new DefaultDynamicSqlAssembleBuilder();
        try {
            dynamicAssembleBuilder.setFileNames(paths);
            //初始化资源
            dynamicAssembleBuilder.init();

            Map<String, String> namedSQLQueries = dynamicAssembleBuilder
                    .getNamedSQLQueries();

            Configuration configuration = new Configuration();
            configuration.setNumberFormat("#");
            StringTemplateLoader stringLoader = new StringTemplateLoader();
            for (Map.Entry<String, String> entry : namedSQLQueries.entrySet()) {
                stringLoader.putTemplate(entry.getKey(), entry.getValue());
                String sql = processTemplate((new Template(entry.getKey(), new StringReader(entry.getValue()),
                        configuration)),Maps.newConcurrentMap());
                sqls
                        .put(entry.getKey(), sql);
            }
            configuration.setTemplateLoader(stringLoader);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public String get(String key) throws SqlNotFoundException {
        String sql = sqls.get(key);
        if (sql == null)
            throw new SqlNotFoundException(key);
        return sql;
    }

    public Sql create(String key) throws SqlNotFoundException {
        return Sqls.create(get(key));
    }

    public List<Sql> createCombo(String... keys) {
        if (keys.length == 0)
            keys = keys();
        List<Sql> list = new ArrayList<Sql>(keys.length);
        for (String key : keys) {
            list.add(create(key));
        }
        return list;
    }

    public int count() {
        return sqls.size();
    }

    public String[] keys() {
        Set<String> keys = sqls.keySet();
        return keys.toArray(new String[keys.size()]);
    }

    public synchronized void addSql(String key, String value) {
        log.debugf("key=[%s], sql=[%s]", key, value);
        if (!isAllowDuplicate() && sqls.containsKey(key))
            throw new DaoException("Duplicate sql key=[" + key + "]");
        sqls.put(key, value);
    }

    public void remove(String key) {
        sqls.remove(key);
    }

    public void setAllowDuplicate(boolean allowDuplicate) {
        this.allowDuplicate = allowDuplicate;
    }

    public boolean isAllowDuplicate() {
        return allowDuplicate;
    }

    /**
     * 废弃的方法,仅为兼容性保留空方法
     */
    @Deprecated
    public String getRegex() {
        return null;
    }

    @Deprecated
    public DynamicSqlManager setRegex(String regex) {
        log.warn("SqlManager regex is Deprecated!! it will be ignore!!");
        return this;
    }
}

5:项目中使用:
dao.js里添加:

 sqlSource: {
        type: "cn.phenix.plugins.dynamicsql.DynamicSqlManager",
        args: ["classpath*:sql/**/*.xml"]
    },

6:使用:

<?xml version="1.0" encoding="utf-8"?>
<!--<![CDATA[ ]]>-->
<sql>
    <!--查询用户菜单-->
    <sql-query name="user.getMenus">
       基于freemarker的sql
    </sql-query>
</sql>

看着好多了,丫的好多sql都得改造。

添加回复
请先登陆
回到顶部