Java sql connection test connection

CA Release Automation — Release Operations Center (Nolio) CA Release Automation — DataManagement Server (Nolio)

Issue/Introduction

The Release Automation Application Server cannot connect to the remote Oracle Database Server.
The Firewall is open and Telnet indicates that the network connectivity is ok.
However, the Application Server cannot connect to the Database Server and the following error is seen in nolio_dm_all.log.
org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (IO Error: The Network Adapter could not establish the connection) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80) Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (IO Error: The Network Adapter could not establish the connection)

Is there a way to test the JDBC connection to the Oracle Database Server?

Environment

Resolution

The oracle-jdbc-tester is a handy tool that can be used to test JDBC connection to an Oracle Server.

1. Download jdbc-tester-1.0.jar from https://github.com/aimtiaz11/oracle-jdbc-tester
2. Copy it to the folder where Java is installed.
3. Grant execute permissions to jdbc-tester-1.0.jar using ‘chmod +x jdbc-tester-1.0.jar‘
4. Execute the JAR file using the schema name, password and JDBC connection details:
Syntax: java -jar target/jdbc-tester-1.0.jar jdbc:oracle:thin:@//:/

Example 1 (Failed JDBC connection)
[[email protected] jdbctester]$ /opt/releaseautomation/jre/bin/java -jar ./jdbc-tester-1.0.jar RELAUTO password123 jdbc:oracle:thin:@//dbserver:1521/iroc
23:53:00.821 [main] INFO Main — arg 0 = RELAUTO
23:53:00.828 [main] INFO Main — arg 1 = password123
23:53:00.828 [main] INFO Main — arg 2 = jdbc:oracle:thin:@//dbserver:1521/iroc
23:53:00.974 [main] INFO Main — ****** Starting JDBC Connection test *******
23:53:09.157 [main] ERROR Main — Exception occurred connecting to database: IO Error: The Network Adapter could not establish the connection

Example 2 (Successful JDBC connection)
[[email protected] jdbctester]$ /opt/releaseautomation/scripts/jdbctester/jdk1.7.0_181/bin/java -jar ./jdbc-tester-1.0.jar RELAUTO password123 jdbc:oracle:thin:@//dbserver:1521/iroc
23:59:48.241 [main] INFO Main — arg 0 = RELAUTO
23:59:48.248 [main] INFO Main — arg 1 = password123
23:59:48.248 [main] INFO Main — arg 2 = jdbc:oracle:thin:@//dbserver:1521/iroc
23:59:48.399 [main] INFO Main — ****** Starting JDBC Connection test *******
23:59:59.293 [main] INFO Main — Running SQL query: [select sysdate from dual]23:59:59.321 [main] INFO Main — Result of sql Query: [2018-07-08 23:59:59.0]23:59:59.321 [main] INFO Main — JDBC connection test successful!

DISCLAIMER: This KB article is for information purpose only. oracle-jdbc-tester is a 3rd party tool with no link to CA Technologies. Use the tool at your own discretion.

Источник

Database testing with Java

Automated from the UI is good, but it’s better if we can verify and comparing the value on the web UI with the actual value from the database, that helps us to make sure it’s correct and integrity. This article will show you how to read and verify database values with Java code.

I will assume that you have successfully installed XAMPP so we can create the new database, table for testing this code.

1. Database preparation

Go to phpMyAdmin (http://localhost/phpmyadmin/) and create the new database (“TESTDB”) with the below SQL statement:

We also need to create the table (“CUSTOMERS”) and insert some values to that table.

Re-check your values with this SQL statement: SELECT * FROM CUSTOMERS;

This is the full SQL statement that you can use:

INSERT INTO CUSTOMERS ( ID , NAME , AGE , ADDRESS , SALARY ) VALUES ( 1 , ‘Ramesh’ , 32 , ‘Ahmedabad’ , 2000.00 ) ;

INSERT INTO CUSTOMERS ( ID , NAME , AGE , ADDRESS , SALARY ) VALUES ( 2 , ‘Khilan’ , 25 , ‘Delhi’ , 1500.00 ) ;

INSERT INTO CUSTOMERS ( ID , NAME , AGE , ADDRESS , SALARY ) VALUES ( 3 , ‘kaushik’ , 23 , ‘Kota’ , 2000.00 ) ;

INSERT INTO CUSTOMERS ( ID , NAME , AGE , ADDRESS , SALARY ) VALUES ( 4 , ‘Chaitali’ , 25 , ‘Mumbai’ , 6500.00 ) ;

INSERT INTO CUSTOMERS ( ID , NAME , AGE , ADDRESS , SALARY ) VALUES ( 5 , ‘Hardik’ , 27 , ‘Bhopal’ , 8500.00 ) ;

INSERT INTO CUSTOMERS ( ID , NAME , AGE , ADDRESS , SALARY ) VALUES ( 6 , ‘Komal’ , 22 , ‘MP’ , 4500.00 ) ;

The results

2. Working with Java code

First, we need to download the “mysql-connector-java” jar at https://dev.mysql.com/downloads/connector/j/5.1.html

Select and download “Platform Independent (Architecture Independent), ZIP Archive”. When the website asks for login, you just need to click on “No thanks, just start my download” if you just want to download it without login.

Unzip this file and import “mysql-connector-java-5.1.43-bin.jar” to your project, the file version should be difference based on the time you download, so do not worry if you see the other number of jar version.

Ok, that’s good for now, we need to create a new Class and use this code, here’s my full code and explanation below:

Источник

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Command line Java app to test JDBC connection to Microsoft SQL Server Database using 9.2.0 SQL Server JDBC driver.

aimtiaz11/ms-sqlserver-jdbc-tester

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

A simple Spring Boot command line application to test JDBC connection to SQL Server database.

Download the JAR from release page or alternatively checkout the code from this repository and run:

Execute the JAR file. Example below:

java -jar target/ms-sqlserver-jdbc-1.0.jar "jdbc:sqlserver://mydatabase.db.windows.net:1433;database=My_Example_DB;user=user@example.com;password=secret123;encrypt=true;trustServerCertificate=true;loginTimeout=90;authentication=ActiveDirectoryPassword"

Security Tip: A good practise is to read the JDBC string into a variable using the read command in Linux to prevent DB credentials in JDBC string to be logged in bash history.

To test the JDBC connection, the app will attempt to connect to SQL Server and execute a single SQL query: SELECT SUSER_SNAME() and close connection.

THE SOFTWARE IS PROVIDED ‘AS IS’, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

About

Command line Java app to test JDBC connection to Microsoft SQL Server Database using 9.2.0 SQL Server JDBC driver.

Источник

soren / JdbcCheck.java

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

import java . io . Console ;

import java . io . File ;

import java . sql . Connection ;

import java . sql . DriverManager ;

import java . sql . ResultSet ;

import java . sql . Statement ;

import java . util . HashMap ;

import java . util . Map ;

public class JdbcCheck

public static void main ( String args [])

if ( args . length != 1 )

System . out . println ( «USAGE: java [-cp driver.jar» + File . pathSeparator +

«.] JdbcCheck ConnectString» );

System . out . println ( » \n Where \n » );

System . out . println ( » ConnectString is the JDBC Connect String. Some examples: \n » );

System . out . println ( » * jdbc:oracle:thin:@//server:1521/sid (Oracle DB)» );

System . out . println ( » * jdbc:postgresql://server:5432/database (PostgreSQL)» );

System . out . println ( » \n driver.jar is JDBC driver for the database specified in ConnectString» );

return ;

String url = args [ 0 ];

Connection con = null ;

String query = «» ;

try

String driver = «» ;

if ( url . contains ( «:oracle:» ))

driver = «oracle.jdbc.driver.OracleDriver» ;

query = «select * from v$version» ;

> else if ( url . contains ( «:postgresql:» ))

driver = «org.postgresql.Driver» ;

query = «select version()» ;

> else

System . err . println ( «Don’t know the driver for » + url );

return ;

System . out . println ( «Loading » + driver );

Class . forName ( driver ). newInstance ();

> catch ( Exception e )

System . err . println ( «Failed to load JDBC driver.» );

return ;

try

Console console = System . console ();

System . out . println ( «Connecting to » + url );

String user = console . readLine ( «User? » );

char [] pass = console . readPassword ( «Password? » );

System . out . println ();

con = DriverManager . getConnection ( url , user , new String ( pass ));

Statement select = con . createStatement ();

ResultSet result = select . executeQuery ( query );

while ( result . next ()) System . out . println ( result . getString ( 1 ));

> catch ( Exception e )

e . printStackTrace ();

> finally

if ( con != null )

try

con . close ();

> catch ( Exception e )

e . printStackTrace ();

Источник

Java sql connection test connection