How do you store and manage SQL statements more efficiently in Java?

Is the "editor's note" Still worrying about managing SQL statements in Java code? Let Zemian help you out of trouble! This article is compiled and organized by OneAPM engineers

Note: using Java.util.properties#loadfromxml is actually easier!

If you are using a generic Java JDBC that does not have any external class libraries, then you have to manage the SQL statements yourself. Unfortunately, Java String does not support multi-line structures, so developers must use many quotation marks + connectors to stitch statements, which makes it very difficult to read and manage SQL statements. It also makes maintenance and testing (trying to Copy an SQL statement from Java code to run from a SQL client) more difficult. If you can guarantee the whole SQL statement intact, and avoid the interference of Java, then how good!

Here's a quick solution to store SQL query statements in the CDATA XML:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><sqlMap>    <sqls>        <entry>            <key>getUser</key>            <value><![CDATA[SELECT *FROM USERSWHERE ID = ?            ]]></value>        </entry>        <entry>            <key>getSpecialCodeByUserId</key>            <value><![CDATA[SELECT u.EMAIL, p.ID as PROFILEID, p.SPECIALCODE, a.MANAGERIDFROM USERS u  LEFT JOIN PROFILE p ON p.USERID = u.ID  LEFT JOIN ACCOUNT a ON a.PROFILEID = p.IDWHERE u.ID = ?  ]]></value>        </entry>  </sqls></sqlMap>

If you read the SQL statement now, developers can take advantage of the built-in JAXB.

import javax.xml.bind.annotation.XmlRootElement;import java.util.HashMap;import java.util.Map;@XmlRootElementpublic class SqlMap {    Map<String, String> sqls = new HashMap<>();    public Map<String, String> getSqls() {        return sqls;    }    public void setSqls(Map<String, String> sqls) {        this.sqls = sqls;    }    public String getSql(String name) {        return sqls.get(name);    }    public static SqlMap load(String name) throws Exception {        String xml = Utils.loadString(name);        SqlMap sqlMap = unmarshallXML(xml );        return sqlMap;    }}

Original link: How to Store and Manage SQL statements + effectively with Java

OneAPM for Java is able to perform application performance management and monitoring within all Java applications, including visibility of code-level performance issues, rapid identification and traceability of performance bottlenecks, real user experience monitoring, server monitoring, and end-to-end application management. To read more technical articles, please visit the OneAPM official blog.

How do you store and manage SQL statements more efficiently in Java?