How to execute sql file in spring boot?

Question

I have this query in .sql I am very much new to the spring-boot framework. Is it possible to hit this query from JPA?

What other option do we have to execute SQL statement can I run this query as a native query?

I am just aware of spring-data-jpa but here I didn't find anything how to execute the saved SQL file

INSERT INTO user_lookup (
user_no, user_id, user_name, email_id, address
)
SELECT 
user_no, user_id, user_name, email_id, address
FROM(
SELECT
CAST(c.bank AS NUMBER) AS user_no, c.user_id, c.user_name, c.email_id, c.address
FROM user_news c
LEFT JOIN user_sector d
ON d.name = c.user_name
LEFT JOIN user_info e
ON e.name = c.email_id AND
   e.user_id = d.id
LEFT JOIN user_lookup f
ON f.user_no = c.user_no
WHERE f.user_no IS NULL
)

It is old, but I found an answer that might be what you look for. For short: Read the file into a String and use JDBC to run the script. stackoverflow.com/questions/20452831/… — Jetto Martínez
– Jetto Martínez, Commented Feb 15, 2022 at 20:36

Murat · Accepted Answer · 2022-02-15 20:41:30Z

You can use liquibase with spring boot.

Something like:

application.yaml:

spring:   
  datasource:
    url: jdbc:postgresql://localhost:5432/db1
    username: test
    password: test   
  liquibase:
    enabled: true
    change-log: classpath:changelog.xml

changelog.xml:

<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <include file="script1.sql" relativeToChangelogFile="true"/>


</databaseChangeLog>

script1.sql:

INSERT INTO user_lookup (
user_no, user_id, user_name, email_id, address
)
SELECT 
user_no, user_id, user_name, email_id, address
FROM(
SELECT
CAST(c.bank AS NUMBER) AS user_no, c.user_id, c.user_name, c.email_id, c.address
FROM user_news c
LEFT JOIN user_sector d
ON d.name = c.user_name
LEFT JOIN user_info e
ON e.name = c.email_id AND
   e.user_id = d.id
LEFT JOIN user_lookup f
ON f.user_no = c.user_no
WHERE f.user_no IS NULL
)

Note: script1.sql will be executed once on app startup.

Collectives™ on Stack Overflow

How to execute sql file in spring boot?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related