1

I have to execute a block of sql script (typically a function) from spring boot application. I can't keep this function inside my postgres database as i want to maintain at application level. I tried to use schema.sql (from resources folder) and this looks like executing during server startup only.

However i want to call this plsql block everytime when needed through JPA or JDBCTemplate.

Any alternative other than schema.sql which can execute on-demand?

MWiesner
  • 8,868
  • 11
  • 36
  • 70
janasainik
  • 811
  • 5
  • 20
  • 40
  • So why cant you just use JPA or JdbcTemplate then? – Magnus Oct 06 '16 at 12:46
  • Problem is that I need to have this block of plsql in one of the sql file under resources folder and I should to call this sql file from JPA. – janasainik Oct 06 '16 at 14:58
  • So just read the file into a string and execute it with JdbcTemplate. You seem to be overthinking things, or have some misconception about what spring-boot is. – Magnus Oct 06 '16 at 21:17
  • just grab the JDBC connection from your JPA provider and write some JDBC to execute your schema.sql – Neil Stockton Oct 10 '16 at 10:09

2 Answers2

1

In unit tests there is @Sql annotation, that you can use to execute a script:

@RunWith(SpringRunner.class)
@SpringBootTest
@Sql("/reset_sequences.sql")
public class ModuleTemplateRepositoryTests { ... }

For launching custom sql script from spring application we were using ScriptUtils (stackoverflow here)

@Value("classpath:reset_sequences.sql")
Resource script;

@Autowired
JdbcTemplate jdbcTemplate;

...
ScriptUtils.executeSqlScript(
    jdbcTemplate.getDataSource().getConnection(), script);
Evgeny Cheryomushkin
  • 320
  • 2
  • 10
-1

If you have your function stored in the database you can look at this example here.

Alexandru Muscalu
  • 1
  • Sorry, my function isn't stored in database. I should to execute the block of plsql without storing in database. – janasainik Oct 06 '16 at 15:00