Move your sql query to resource file with @Autowire and factory method

on
If you have SQL queries written in java files in your spring project, you might want to move SQL to a separate file (or files). It can be done easily using @Autowired;annotation and factory method. To make our example more realistic, let's imagine the situation when we have an application http://wortschatz-olyv.rhcloud.com. If user clicks 'New Lesson' button, application will fetch some lesson items (they are just words) from database. Actual SQL query is:

 SELECT * from adjectives LIMIT 1;

We will be using NamedParameterJdbcTemplate and for this reason let's update our query to make it look like this:

 SELECT * from adjectives { LIMIT :NUMBER_OF_ADJECTIVES }

Now we need to prepare DataSource bean and declare NamedParameterJdbcTemplate to use it later. Corresponding code is:

 public class JdbcConfig {

    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Bean
    private DataSource dataSource(){
        DriverManagerDataSource dataSource = new DriverManagerDataSource();

        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);

        return dataSource;
    }

    @Bean
    public NamedParameterJdbcTemplate jdbcTemplate() {
        return new NamedParameterJdbcTemplate(dataSource());
    }
 }

Correspondent properties are saved in application.yml file. Note how properties are accessed, only @Value annotation is required since spring is managing all the job to read properties for us.

Now we need some class which will be used for retrieving SQL query from file.

 @Configuration
 public class SqlUtils {

    private static final String SELECT_ADJECTIVES_FILE = "sql/selectAdjectives.sql";

    @Bean
    public String selectAdjectivesSql() {
        return readFileSql(SELECT_ADJECTIVES_FILE);
    }

    private String readFileSql(String pathToFile) {
        StringBuilder sb = null;
        ClassLoader classLoader = getClass().getClassLoader();
        File file = new File(classLoader.getResource(pathToFile).getFile());

        try(BufferedReader br = new BufferedReader(new FileReader(file))) {
            sb = new StringBuilder();
            String line = br.readLine();

            while (line != null) {
                sb.append(line);
                sb.append(System.lineSeparator());
                line = br.readLine();
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return sb.toString();
    }
 }

If you wonder how project structure can look like, sample tree is below.

And final part is to wire together selectAdjectivesSql bean (which is actually factory method) and NamedParameterJdbcTemplate bean. We can do in DAO which is described below.

 @Component
 public class LessonItemsDao {

    private static final String NUMBER_OF_ADJECTIVES_KEY = "NUMBER_OF_ADJECTIVES";
    private static final int NUMBER_OF_ADJECTIVES_VALUE = 1;

    @Autowired
    private String selectAdjectivesSql;

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    public List<Adjective> selectAdjectivesForLesson() {
        return jdbcTemplate.query(
                selectAdjectivesSql,
                of(NUMBER_OF_ADJECTIVES_KEY, NUMBER_OF_ADJECTIVES_VALUE),
                new AdjectiveRowMapper());
    }
 }

queryForList is accepting SQL query, map of parameters to update in SQL query and and row mapper. And this is going to be our next step -- to implement a row mapper 

 public class AdjectiveRowMapper implements RowMapper<Adjective> {

    @Override
    public Adjective mapRow(ResultSet rs, int rowNum) throws SQLException {
        String word = rs.getString("WORD");
        String translation = rs.getString("WORD_TRANSLATION");
        return new Adjective(word, translation);
    }
 }

Now all you need to use selectAdjectivesForLesson() is to autowire LessonItemsDao. For example

 @Autowired
 private LessonItemsDao lessonItemsDao;

 List<Adjective> myResultList = lessonItemsDao.selectAdjectivesForLesson();

Hopefully, described approach will allow you to refine your code and to move related boilerplate SQL queries to separate files.

Comments

Post a Comment