How to store and read SQL queries from properties or yml file in spring boot jdbc repository?

Question

I am using spring boot and spring jdbc template. I want to externalize the SQL queries in either properties or yml file. I dont want to store the SQL queries in the java repositories classes.

What is the best way to handle this case?

This is how my repository class looks right now.

@Repository
public class UserRepositoryImpl extends BaseRepository implements UserRepository {

    @Override
    public List<User> findAll(){
        String sqlQuery = "SELECT * FROM users";
        return jdbcTemplate.query(sqlQuery,  userMapper);
    }

    @Override
    public User findById(Long userId){
        String sqlQuery = "SELECT * FROM users WHERE id = :userId";
        Map<String, String> namedParameters = new HashMap<String, String>();
        namedParameters.put("userId", String.valueOf(userId));
        return jdbcTemplate.queryForObject(sqlQuery, namedParameters, userMapper);
    }

Solution

I handled as follows:

I have a @Configuration class which creates jdbcTemplate beans so I add an another bean with class of StringBuilder to hold query from .sql file. Here is my configuration:

@Configuration
public class DBManager {

    private static final Logger logger = LoggerFactory.getLogger(DBManager.class);

    @Autowired
    PropertiesUtils propertiesUtils;

    @Bean(name = "targetJdbcTemplate")
    public JdbcTemplate targetJdbcTemplate() throws SQLException {
        Environment environment = propertiesUtils.getEnvironment();
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(environment.getProperty("db.target.url"));
        dataSource.setUsername(environment.getProperty("db.target.username"));
        dataSource.setPassword(environment.getProperty("db.target.password"));

        return new JdbcTemplate(dataSource);
    }

    @Bean(name = "targetQueryTemplate")
    public StringBuilder targetQueryTemplate() {
        return propertiesUtils.getSQLQueryFromFile(DBDirection.TARGET_DB);
    }
}

PropertiesUtil looks like:

@Configuration
@PropertySource(value={"classpath:app.properties"})
public class PropertiesUtils {

    private static final Logger logger = LoggerFactory.getLogger(PropertiesUtils.class);

    @Resource
    private Environment environment;

    public Environment getEnvironment() {
        return environment;
    }

    /**
     * to get sql query from .sql file
     * @param dbDirection which db's query is needed
     * @return a StringBuilder object which holds needed sql query
     */
    public StringBuilder getSQLQueryFromFile(DBDirection dbDirection) {
        String filePath = null;
        StringBuilder sql = null;
        BufferedReader br = null;
        InputStreamReader input = null;
        try {
            if (dbDirection == DBDirection.SOURCE_DB)
                filePath = this.environment.getProperty("db.source.query.file");
            else if (dbDirection == DBDirection.TARGET_DB){
                filePath = this.environment.getProperty("db.target.query.file");

            if(filePath == null || filePath.equals("")) {
                logger.error("filePath cannot be null or empty");
                return sql;
            }

            InputStream in = PropertiesUtils.class.getClassLoader().getResourceAsStream(filePath);
            input = new InputStreamReader(in);
            br = new BufferedReader(input);
            String str;
            sql = new StringBuilder("");
            while ((str = br.readLine()) != null) {
                sql.append(str);
            }
        } catch (IOException e) {
            logger.error("Failed to read query from file", e);
        } finally {
            try {
                if(br != null)
                    br.close();
                if(input != null)
                    input.close();
            } catch (IOException e) {
                logger.error("Failed to close reader", e);
            }
        }
        return sql;
    }
}

app.properties holds .sql file's path. getSQLQueryFromFile reads the file once while context initializing.

Then I wire the query holder bean (targetQueryTemplate) to my repo that's it. Here is my repo:

@Repository
public class TargetRepository implements ITargetRepository {

    private static final Logger logger = LoggerFactory.getLogger(TargetRepository.class);
    private static final String DEFAULT_DATE_FORMAT = "yyyyMMddHHmmss";

    @Autowired
    @Qualifier("targetJdbcTemplate")
    private JdbcTemplate targetJdbcTemplate;

    @Autowired
    @Qualifier("targetQueryTemplate")
    private StringBuilder targetQueryTemplate;

    @Override
    public void testConnection() {
        targetJdbcTemplate.execute("select 1 from dual");
    }

    @Override
    public int[] insert(final ArrayList<Object> list) {
        return targetJdbcTemplate.batchUpdate(this.targetQueryTemplate.toString(), new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                // batch adding
            }

            @Override
            public int getBatchSize() {
                return determineBatchSize(list);
            }
        });
    }
}

Hope this helps!

Comments

[ashishjmeshram]

I have looked into it. It seems to be almost 2.6 yrs old. Since, a lot has changed in spring since, is there any better way to do it now?