Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Store and Manage SQL Statements More Effectively With Java

DZone's Guide to

How to Store and Manage SQL Statements More Effectively With Java

Struggling with managing SQL statements in your Java code? Let Zemian give you away out!

· Database Zone ·
Free Resource

MariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.

NOTE: use java.util.Properties#loadFromXML is easier!


If you work with plain Java JDBC without any external libraries, you will need to manage your own SQL statements. Unfortunately Java String does not support muti-lines construct, and you have to use many "quotes" + "concatenation" and makes the SQL very hard to read and manage. This makes it hard to maintain and test (try to copy a SQL from Java code into your SQL client). It would be so nice to keep the entire SQL block of text intact without these Java noise.


Here is a quick solution. Store your SQL queries in XML inside CDATA:

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


Now you just need to read it. You can do this quickly with built-in JAXB

import javax.xml.bind.annotation.XmlRootElement;
import java.util.HashMap;
import java.util.Map;

@XmlRootElement
public 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;
    }
}




MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.

Topics:
java ,sql

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}