Home Framework for parsing SQL queries out of Java code?
Reply: 1

Framework for parsing SQL queries out of Java code?

ab11
1#
ab11 Published in 2018-01-13 02:12:02Z

I would like to parse my SQL queries out of my Java source code. The queries are of all types and include of a variety of quotation marks and new line characters. They are often assigned to a variable within the source, like:

String query = "Select a from b..."

Is anyone aware of a framework to parse SQL queries from Java code?

Sanka
2#
Sanka Reply to 2018-01-13 02:59:42Z

personally, I used MyBatis for this kind of purpose.

My Batis String Substitution

By default, using the #{} syntax will cause MyBatis to generate PreparedStatement properties and set the values safely against the PreparedStatement parameters (e.g. ?). While this is safer, faster and almost always preferred, sometimes you just want to directly inject an unmodified string into the SQL Statement. For example, for ORDER BY, you might use something like this:

ORDER BY ${columnName}

service layer

@Service
public class RuleService{

    @Autowired
    private RuleDAOMapper ruleDAOMapper;

    @Transactional
    public List<Map<String,Object>> selectAll() throws Exception{   
            Map<String,Object> paramSpecialRules=new HashMap<String,Object>();
            paramSpecialRules.put("query", "Select a from b where id=#{id,jdbcType=VARCHAR}");
            paramSpecialRules.put("id", 1506);
            List<Map<String,Object>> specialRules = new ArrayList<Map<String,Object>>();
            try 
            {                   
                specialRules = ruleDAOMapper.runQuery(paramSpecialRules);
            }
            catch (Exception e) 
            {
                e.printStackTrace();
                throw new Exception("ERROR IN READING");
            }
    }

    }

DAO Layer

@MapperScan
public interface RuleDAOMapper {
    public List<Map<String,Object>> runQuery(Map param);
}

XML Layer

<mapper namespace="com.codexamples.contact.dao.mybatis.mappers.RuleDAOMapper">
<select id="runQuery" parameterType="Map" resultType="java.util.LinkedHashMap"  flushCache="true" useCache="false">
    ${query}
</select>
</mapper>

reference:

When to use $ vs #?

MyBatis String as Parameter

http://www.mybatis.org/mybatis-3/sqlmap-xml.html See the 'String Substitution' section in the MyBatis Docs.

Create one query Using list of SQL

    StringBuilder query=new StringBuilder("select * from (");
    for (Iterator<String> iterator2 = listOfSQLQueries.iterator(); iterator2.hasNext();) {//process exceptional SQL list on by one
        String sqlUnit = (String) iterator2.next();
        query.append(sqlUnit);

        if(iterator2.hasNext())
            query.append(" UNION ");
    }
    query.append(")");
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.309987 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO