Cleanest way to build an SQL string in Java

by

First of all consider using query parameters in prepared statements:

PreparedStatement stm = c.prepareStatement("UPDATE user_table SET name=? WHERE id=?");
stm.setString(1, "the name");
stm.setInt(2, 345);
stm.executeUpdate();

The other thing that can be done is to keep all queries in properties file. For example
in a queries.properties file can place the above query:

update_query=UPDATE user_table SET name=? WHERE id=?

Then with the help of a simple utility class:

public class Queries {

    private static final String propFileName = "queries.properties";
    private static Properties props;

    public static Properties getQueries() throws SQLException {
        InputStream is = 
            Queries.class.getResourceAsStream("https://stackoverflow.com/" + propFileName);
        if (is == null){
            throw new SQLException("Unable to load property file: " + propFileName);
        }
        //singleton
        if(props == null){
            props = new Properties();
            try {
                props.load(is);
            } catch (IOException e) {
                throw new SQLException("Unable to load property file: " + propFileName + "\n" + e.getMessage());
            }           
        }
        return props;
    }

    public static String getQuery(String query) throws SQLException{
        return getQueries().getProperty(query);
    }

}

you might use your queries as follows:

PreparedStatement stm = c.prepareStatement(Queries.getQuery("update_query"));

This is a rather simple solution, but works well.

More Related Contents:

  1. Using setDate in PreparedStatement
  2. java.sql.SQLException: Exhausted Resultset
  3. How to enable logging for SQL statements when using JDBC
  4. Getting output from dbms_output.get_lines using JDBC
  5. The most sophisticated way for creating comma-separated Strings from a Collection/Array/List?
  6. JDBC ResultSet get columns with table alias
  7. Getting an exception ORA-00942: table or view does not exist – when inserting into an existing table
  8. MyBatis Batch Insert/Update For Oracle
  9. How to implement a SQL like ‘LIKE’ operator in java?
  10. Java: Find word in string and edit words after the word
  11. Java string to date conversion
  12. Extract digits from a string in Java
  13. How to generate a random alpha-numeric string
  14. How to call a stored procedure from Java and JPA
  15. How to extract numbers from a string and get an array of ints?
  16. Prevent SQL injection attacks in a Java program
  17. Java Date – Insert into database
  18. Is there a method for String conversion to Title Case?
  19. How to check if a string contains only digits in Java
  20. SQL prepared statement how to select via multiple possible menu selections?
  21. Is there any good dynamic SQL builder library in Java? [closed]
  22. == and .equals() not working in java [duplicate]
  23. ojdbc14.jar vs. ojdbc6.jar
  24. How can I detect a SQL table’s existence in Java?
  25. Java: removing numeric values from string
  26. String concatenation with Null
  27. Having a Column name as Input Parameter of a PreparedStatement
  28. How to remove newlines from beginning and end of a string?
  29. Java ByteBuffer to String
  30. Remove a specific string from an array of string

Leave a Comment