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

What if you could learn how to use MongoDB directly from the experts, on your schedule, for free? We've put together the ultimate guide for learning MongoDBSign up and you'll receive instructions for how to get started!

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;
    }
}




What if you could learn how to use MongoDB directly from the experts, on your schedule, for free? We've put together the ultimate guide for learning MongoDBSign up and you'll receive instructions for how to get started!

Topics:
java ,sql

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}