Framework for parsing SQL queries out of Java code?

Question

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?

If you use MyBatis, developer put SQL query inside XML file, not inside Java code like Hibernate ORM. — Vy Do, Jan 13 '18 at 02:16
@DoNhuVy I have a ton of Java source code which, among other things, performs a lot of sql queries. These queries are part of the source code, often they are assigned to `string` variables before being executed with standard `jdbc` classes. I would like to find a framework which can parse the sql out of the java source code. For example, an ideal framework would take a java source file as a parameter and return a list of strings of all the queries in the file. — ab11, Jan 13 '18 at 02:30
SQL queries inside Java source code often contains parameters what supplied by Java program. For example: User put search criteria to SQL query, and you quite hard to simulate these user's input parameters. Not easy to bring standalone SQL queries out of Java source code, then execute it success like your imagination. — Vy Do, Jan 13 '18 at 02:33
I agree, I will likely write a custom parser to do this, was hoping to use a framework — ab11, Jan 13 '18 at 02:35

Sanka · Answer 1 · 2018-01-13T02:59:42.100

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(")");

the issue is that the sql is already coupled to the source code. I might be mistaken, but I don't think ibatis provides functionality like: parsing a list of sql queries out of a file of source code? — ab11, Jan 13 '18 at 02:51
if you have list of queries , loop it and create union query of all those using java string proceccing and sent one query to run — Sanka, Jan 13 '18 at 02:57

Framework for parsing SQL queries out of Java code?

1 Answers1