Define Data sources in application.yaml
app: datasource: oracle-local: url: jdbc:oracle:thin:@localhost:10152/test
username: wrkday_apps
password: bread4all
driver-class: oracle.jdbc.driver.OracleDriver
postgres-local: url: jdbc:postgresql://localhost:5432/
username: postgres
password: postgres
driver-class: org.postgresql.Driver
crate a config file
@Configurationpublic class DatasourceConfig {
@Bean @Primary @ConfigurationProperties("app.datasource.oracle-local")
public DataSourceProperties oracleDataSourceProperties() {
return new DataSourceProperties();
}
@Bean @Primary @ConfigurationProperties("app.datasource.oracle-local")
public DataSource oracleDataSource() {
return oracleDataSourceProperties().initializeDataSourceBuilder().build();
}
@Bean(name = "oracleEntityManagerFactory")
@Primary public LocalContainerEntityManagerFactoryBean oracleEntityManagerFactory(
EntityManagerFactoryBuilder builder) {
return builder
.dataSource(oracleDataSource())
.packages(Audit.class, Audit1.class)
.persistenceUnit("local1")
.build();
}
@Configuration @EnableJpaRepositories(basePackages= "com.jlabs.repo",
entityManagerFactoryRef = "oracleEntityManagerFactory")
public class oracleConfiguration {
}
@Bean @ConfigurationProperties("app.datasource.postgres-local")
public DataSourceProperties localDataSourceProperties() {
return new DataSourceProperties();
}
@Bean(name = "localDataSource")
@ConfigurationProperties("app.datasource.postgres-local")
public DataSource localDataSource() {
return localDataSourceProperties().initializeDataSourceBuilder().build();
}
@Bean(name = "jdbcMaster")
public SimpleJdbcCall masterJdbcTemplate() {
return new SimpleJdbcCall( localDataSource()) .withSchemaName("public")
.withFunctionName("test_pkg");
}
}
to Execute the store proc
@Componentpublic class LocalTestDAO {
private static Logger LOGGER = LoggerFactory.getLogger(LocalTestDAO.class);
@Autowired public SimpleJdbcCall jdbcMaster;
@Transactional public Integer savetoLocalTestPkg() {
LOGGER.info("Saving to TestPkg {}" );
try {
final Map<String, Object> params = new HashMap<>();
params.put("_sno", 8);
params.put("_eid", 104);
params.put("_ename", "jim");
params.put("_sd", new Date());
params.put("_ed", new Date());
params.put("_sid", 150000);
Map<String, Object> out = jdbcMaster.execute(params);
return 1;
} catch (Exception e) {
LOGGER.error("Saving to EDW TestPkg failed {}", e);
return -1;
}
}
}
Steps to create the stored proc
CREATE TABLE app_for_leave
(
sno INTEGER NOT NULL,
eid INTEGER,
ename VARCHAR(20),
sd DATE,
ed DATE,
sid INTEGER,
status BOOLEAN DEFAULT FALSE,
CONSTRAINT pk_snoa PRIMARY KEY (sno)
);
CREATE OR REPLACE FUNCTION insert_into_table(_sno INTEGER, _eid INTEGER, _ename VARCHAR(20), _sd DATE, _ed DATE, _sid INTEGER)
RETURNS void AS
$BODY$
BEGIN
INSERT INTO app_for_leave(sno, eid, ename, sd, ed, sid)
VALUES(_sno, _eid, _ename, _sd, _ed, _sid);
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
To manually execure the proc -
SELECT * FROM insert_into_table(2,102,'Jimmy','2013-08-16','2013-08-17',12);
