How to store and read SQL queries from properties or yml file in spring boot jdbc repository?
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);
}
Answer
I know this doesn't directly address how your ask regarding properties files or yml, but I interpret your question generally as asking about the best way to manage sql statements in a project. Having worked on projects with quite a lot of SQL code I've found MyBatis to hold up without too much complaint. In a nutshell, it already handles externalizing sql to external xml files and can keep the manageability of the sql in the files at a good level as you accumulate more sql.
To set it up you basically need to configure the beans and create two mybatis xml files along with a java interface for the repository. Taking your example, here's the mybatis for the user repository:
public class User {
private Long id;
private String name;
...
}
public interface UserRepository {
List<User> findAll();
User findById( @Param( "id" ) Long userId );
}
The @Param will map the 'id' value to the #{id} expression in the SQL
META-INF/repo/sql/userMapper.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bushcoder.so.app.user.UserRepository">
<resultMap id="user" type="com.bushcoder.so.app.user.User">
<id property="id" column="userId"/>
<result property="name" column="name"/>
</resultMap>
<select id="findAll" resultMap="user">
SELECT id, name FROM user
</select>
<select id="findById" parameterType="long" resultMap="user">
SELECT id, name FROM user WHERE id = #{id}
</select>
</mapper>
Note: #{id} will be supplied the value passed in via the call to userRepository.findById
META-INF/repo/sql/sqlmap-config.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//www.mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd" >
<configuration>
<mappers>
<mapper resource="META-INF/repo/sql/userMapper.xml"/>
</mappers>
</configuration>
The 'META-INF/repo/sql/sqlmap-config.xml' path will be used in the Java Config to setup the beans required by mybatis. So for the configuration you'll need 4 beans: sqlSessionFactory, sqlSessionTemplate, dataSource and the userRepository. These need to be somewhere in a configuration class for Spring to process.
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
sqlSessionFactory.setDataSource(dataSource());
sqlSessionFactory.setConfigLocation( new ClassPathResource( "META-INF/repo/sql/sqlmap-config.xml" ) );
return sqlSessionFactory.getObject();
}
@Bean
public SqlSessionTemplate sqlSessionTemplate() throws Exception {
return new SqlSessionTemplate(sqlSessionFactory());
}
@Bean
public DataSource dataSource() {
EmbeddedDatabaseBuilder builder = new EmbeddedDatabaseBuilder();
EmbeddedDatabase db = builder
.setType( EmbeddedDatabaseType.H2)
.addScript("META-INF/repo/db/ddl/create-database-script.sql")
.addScript("META-INF/repo/db/dml/database-seeder-script.sql")
.build();
return db;
}
@Bean
public UserRepository userRepository() throws Exception {
return sqlSessionTemplate().getMapper( UserRepository.class );
}
In my prototype project I went to the H2 database and am using the EmbeddedDatabaseBuilder to take care of the schema and seed data.
META-INF/repo/db/ddl/create-database-script.sql:
CREATE TABLE if NOT EXISTS user (
id INTEGER PRIMARY KEY,
name VARCHAR(30)
);
META-INF/repo/db/dml/database-seeder-script.sql:
INSERT INTO user (id, name) VALUES (1, 'BOB');
INSERT INTO user (id, name) VALUES (2, 'LARRY');
INSERT INTO user (id, name) VALUES (3, 'FRANK');
INSERT INTO user (id, name) VALUES (4, 'CHARLIE');
INSERT INTO user (id, name) VALUES (5, 'GARRY');
More than likely you'll wire the repository into a service. Might look something like this:
public interface UserService {
List<User> findAll();
User findById(Long userId);
}
@Service
public class UserServiceImpl implements UserService {
@Inject
private UserRepository userRepository;
@Override
public List<User> findAll() {
return userRepository.findAll();
}
@Override
public User findById( Long userId ) {
return userRepository.findById( userId );
}
}
The calling code could be like this:
@SpringBootApplication
@Import ( AppConfig.class )
public class MybatisConfigExampleApplication {
public static void main(String[] args) {
ConfigurableApplicationContext context = SpringApplication.run( MybatisConfigExampleApplication.class, args );
final UserService users = ( UserService ) context.getBean( "userServiceImpl" );
final List<User> allUsers = users.findAll();
System.out.println( "allUsers = " + allUsers );
final User userById_5 = users.findById( 5L );
System.out.println( "userById_5 = " + userById_5 );
}
}
Now, as you start to accumulate more sql, you would create a new repository interface, its matching mapper file, link the mapper xml file via the sqlmap-config xml file by adding a new <mapper> element for it, and then add the new repository as a bean in Spring's config. Moreover, and I didn't show it hear, if userMapper.xml starts to get too large and cumbersome you can break it apart into smaller files and still keep the UserRepository interface.