如何在spring boot jdbc存储库中的属性或yml文件中存储和读取SQL查询？

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.

我使用的是spring boot和spring jdbc模板。我想在属性或yml文件中外化SQL查询。我不想将SQL查询存储在java存储库类中。

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);
    }

2 个解决方案

#1

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.

我知道这并没有直接解决你对属性文件或yml的询问，但我将你的问题解释为询问在项目中管理sql语句的最佳方法。在处理了大量SQL代码的项目后，我发现MyBatis没有太多抱怨。简而言之，它已经处理外部化sql到外部xml文件，并且可以在累积更多sql时保持文件中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:

要进行设置，您基本上需要配置bean并创建两个mybatis xml文件以及存储库的java接口。举个例子，这是用户存储库的mybatis：

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

@Param会将'id'值映射到SQL中的＃{id}表达式

META-INF/repo/sql/userMapper.xml:

META-INF /回购/ 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

注意：＃{id}将通过调用userRepository.findById提供传入的值

META-INF/repo/sql/sqlmap-config.xml:

META-INF /回购/ 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.

将在Java Config中使用'META-INF / repo / sql / sqlmap-config.xml'路径来设置mybatis所需的bean。因此，对于配置，您将需要4个bean：s​​qlSessionFactory，sqlSessionTemplate，dataSource和userRepository。这些需要在Spring的一个配置类中进行处理。

  @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.

在我的原型项目中，我访问了H2数据库，并使用EmbeddedDatabaseBuilder来处理模式和种子数据。

META-INF/repo/db/ddl/create-database-script.sql:

META-INF /回购/ DB / DDL /创建数据库，script.sql：

CREATE TABLE if NOT EXISTS user (
  id    INTEGER PRIMARY KEY,
  name  VARCHAR(30)
);

META-INF/repo/db/dml/database-seeder-script.sql:

META-INF /回购/ DB / DML /数据库播种机，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.

现在，当您开始累积更多sql时，您将创建一个新的存储库接口，其匹配的映射器文件，通过为其添加新的 元素，通过sqlmap-config xml文件链接映射器xml文件，然后添加在Spring的配置中将新存储库作为bean。而且，我没有看到它听到，如果userMapper.xml开始变得太大和繁琐，你可以将它分成更小的文件并仍然保留UserRepository接口。

#2

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:

我有一个创建jdbcTemplate bean的@Configuration类，所以我添加了另一个带有StringBuilder类的bean来保存.sql文件的查询。这是我的配置：

@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:

PropertiesUtil看起来像：

@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.

app.properties保存.sql文件的路径。 getSQLQueryFromFile在上下文初始化时读取文件一次。

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

然后我将查询持有者bean（targetQueryTemplate）连接到我的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!

希望这可以帮助！