Reputation: 33
Unable to connect to database from springboot application
I am able to connect to an oracle database from IntelliJ db console but I cant connect from my application. This is my application property
spring.datasource.url=jdbc:oracle:thin:@192.168.00.00:1521:ORCL
spring.datasource.username=
spring.datasource.password=password
***@Autowired
private JdbcTemplate jdbcTemplate;
@GetMapping("/api/bears/{empId}")
public int BearsCall(@PathVariable String empId) {
final int[] r = {0}; // Making r effectively final using an array
// active , CWR , A : user_status, person_status, person_type
// replace name with unique_identifier
String userQuery = "SELECT extended1, extended2, extended3 " +
"FROM identityiq.spt_identity " +
"WHERE name = ?";
String hasRole = "SELECT COUNT(*) AS role_count " +
"FROM identityiq.spt_identity_assigned_roles i " +
"JOIN identityiq.spt_bundle b ON i.bundle = b.id " +
"JOIN identityiq.spt_identity u ON i.identity_id = u.id " +
"WHERE u.name = ? " +
"AND b.name = 'Role - Baseline Standard Access User'";
try {
// Query for user
Object[] params = {empId};
jdbcTemplate.query(userQuery, params, (ResultSet rs) -> {
if (rs.next()) {
String adStatus = rs.getString("extended2") != null ? rs.getString("extended2") : "Unknown";
if (!adStatus.equalsIgnoreCase("unknown")) {
// Query for role count
int roleCount = jdbcTemplate.queryForObject(hasRole, Integer.class, empId);
if (roleCount > 0) {
r[0] = 2;
}
} else {
r[0] = 1;
}
} else {
r[0] = 0;
}
});
} catch (Exception e) {
// Handle exceptions
e.printStackTrace();
}
return r[0];
}***
Error:
java.sql.SQLTimeoutException: ORA-12170: Cannot connect. TCP connect timeout of 30000ms for host 192.168.00.00 port 1521. (CONNECTION_ID=h6LYhOpKQ6SXelYm+4==) https://docs.oracle.com/error-help/db/ora-12170/ at oracle.jdbc.driver.T4CConnection.handleLogonNetException(T4CConnection.java:1382) ~[ojdbc8-23.3.0.23.09.jar:23.3.0.23.09] at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:975) ~[ojdbc8-23.3.0.23.09.jar:23.3.0.23.09] at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:1126) ~[ojdbc8-23.3.0.23.09.jar:23.3.0.23.09] at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:104) ~[ojdbc8-23.3.0.23.09.jar:23.3.0.23.09] at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:825) ~[ojdbc8-23.3.0.23.09.jar:23.3.0.23.09] at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:651) ~[ojdbc8-23.3.0.23.09.jar:23.3.0.23.09] at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-4.0.3.jar:na] at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) ~[HikariCP-4.0.3.jar:na] at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-4.0.3.jar:na] at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476) ~[HikariCP-4.0.3.jar:na] at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) ~[HikariCP-4.0.3.jar:na] at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:115) ~[HikariCP-4.0.3.jar:na] at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) ~[HikariCP-4.0.3.jar:na] at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:159) ~[spring-jdbc-5.3.15.jar:5.3.15] at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:117) ~[spring-jdbc-5.3.15.jar:5.3.15] at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80) ~[spring-jdbc-5.3.15.jar:5.3.15] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:646) ~[spring-jdbc-5.3.15.jar:5.3.15] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713) ~[spring-jdbc-5.3.15.jar:5.3.15] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:744) ~[spring-jdbc-5.3.15.jar:5.3.15] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:773) ~[spring-jdbc-5.3.15.jar:5.3.15] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:784) ~[spring-jdbc-5.3.15.jar:5.3.15] at com.zephon.WebApp.controller.myController.BearsCall(myController.java:301) ~[classes/:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na] at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na] at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na] at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-5.3.15.jar:5.3.15] at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) ~[spring-web-5.3.15.jar:5.3.15] at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) ~[spring-webmvc-5.3.15.jar:5.3.15] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) ~[spring-webmvc-5.3.15.jar:5.3.15] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) ~[spring-webmvc-5.3.15.jar:5.3.15] at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.3.15.jar:5.3.15] at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067) ~[spring-webmvc-5.3.15.jar:5.3.15] at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963) ~[spring-webmvc-5.3.15.jar:5.3.15] at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.3.15.jar:5.3.15] at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.3.15.jar:5.3.15] at javax.servlet.http.HttpServlet.service(HttpServlet.java:655) ~[tomcat-embed-core-9.0.56.jar:4.0.FR] at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.3.15.jar:5.3.15] at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) ~[tomcat-embed-core-9.0.56.jar:4.0.FR] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.56.jar:9.0.56] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.3.15.jar:5.3.15] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.15.jar:5.3.15] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.3.15.jar:5.3.15] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.15.jar:5.3.15] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.15.jar:5.3.15] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.15.jar:5.3.15] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.56.jar:9.0.56] at java.base/java.lang.Thread.run(Thread.java:840) ~[na:na] Caused by: oracle.net.ns.NetException: ORA-12170: Cannot connect. TCP connect timeout of 30000ms for host 192.168.00.00 port 1521. (CONNECTION_ID=h6LYhOpKQ6SXelYm==)
Upvotes: 0
Views: 73
Answers (1)
Reputation: 293
- When using JdbcTemplate, DataSource or DB Connection meta information is missing.
- When using JdbcTemplate, please perform Dependency Injection of DataSource.
- DataSource is an object that contains DB-related connection information and is responsible for connecting to the DB server.
- Spring Boot retrieves DB-related information from meta information files such as application.properties and creates a DataSource bean.
- One additional note: It is not a good idea to perform all logic in the Controller layer.
- Please refer to 3-Layer Architecture.
- An example code with modifications to the relevant part is attached below.
// @Autowired
private final JdbcTemplate jdbcTemplate;
// Constructor DI
public JdbcTemplate(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
@GetMapping("/api/bears/{empId}")
public int BearsCall(@PathVariable String empId) {
final int[] r = {0}; // Making r effectively final using an array
// active , CWR , A : user_status, person_status, person_type
// replace name with unique_identifier
String userQuery = "SELECT extended1, extended2, extended3 " +
"FROM identityiq.spt_identity " +
"WHERE name = ?";
String hasRole = "SELECT COUNT(*) AS role_count " +
"FROM identityiq.spt_identity_assigned_roles i " +
"JOIN identityiq.spt_bundle b ON i.bundle = b.id " +
"JOIN identityiq.spt_identity u ON i.identity_id = u.id " +
"WHERE u.name = ? " +
"AND b.name = 'Role - Baseline Standard Access User'";
try {
// Query for user
Object[] params = {empId};
jdbcTemplate.query(userQuery, params, (ResultSet rs) -> {
if (rs.next()) {
String adStatus = rs.getString("extended2") != null ? rs.getString("extended2") : "Unknown";
if (!adStatus.equalsIgnoreCase("unknown")) {
// Query for role count
int roleCount = jdbcTemplate.queryForObject(hasRole, Integer.class, empId);
if (roleCount > 0) {
r[0] = 2;
}
} else {
r[0] = 1;
}
} else {
r[0] = 0;
}
});
} catch (Exception e) {
// Handle exceptions
e.printStackTrace();
}
return r[0];
}
I hope it will be of help :)
Upvotes: 0
Related Questions
- FallbackExecutor : No fallback method match found
- Field 'id' doesn't have a default value because of generated value
- java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1) error
- Hibernate: PSQLException in a OneToMany relationship
- Thymleaf ->Error during execution of processor 'org.thymeleaf.spring5.processor.SpringInputGeneralFieldTagProcessor'
- GraphQL Scalar for java.time.LocalDate parses dates as StringValue
- I am trying to use the Amazon SNS service but getting Unable to load credentials from any chain exception