Updated April 17, 2023
What is JDBC SQL Server Connection String?
It connects the application to a database stored in a Microsoft SQL server. This connect string has the details like:
a. Server Instance where the database resides,
b. Database name,
c. Software driver name to be used,
d. login credentials to access data.
Connection string is also known as URL that helps to establish connectivity with the database as well as retrieve/update the records from the database.
The connection string parameters are normally stored in a configuration file in text format for applications to connect to the database. This text is encrypted to prevent hacking. This configuration file can be edited on the fly when the application is moved from the development stage to the Quality and Production environment.
How to use JDBC SQL Server connection?
The following steps will have to be carried out in the Java application to use SQL server connection.
a. Ensure that Database is created in the target location, be it in development or Quality or Production server
b. User id and passwords to access data in the above database should have been created.
c. Java language SQL packages like Connection, Driver manager, Statement creation enabler, Holders of a result set, Exception handlers should be included in the code.
d. URL will have been created with database details and placeholders for attributes whatever that need to be substituted from the configuration file
e. Connection should be established through driver manager and URL
f. Statement will have to be created using statement method, it has been executed and the result will have to be accumulated in the result set and should get consumed.
g. Exceptions have to be managed.
Java JDBC Connection String Examples
1. With Mysql Database (Shaded portions are the actual java codes)
Mysql is a traditional, Relational database used in e-commerce, data warehousing applications.
a. Importing Java packages: All the relevant Java database connectivity (JDBC) classes that are required for database operations should be included. A general java.sql.* would be enough for accessing data
import java.sql.*b. Establishing a Connection to database: A connection object using getConnection() method in the DriverManager Class should be created to get a physical connection to the database. A connection string with database name and location is defined as an URL and it is used along with username and password to establish the connection.
Connection string, user name, password and the actual query that will have to be executed
are defined initially in the program before the execution starts. The program starts with a declaration of a class
public class MYSQLTESTJDBC {
static final string CON_URL = "jdbc:mysql://localhost/MYTEST";
static final string TUSR = "test";
static final string TPASS = "test@123";
static final string QRY = "select emp_no, name, salary from ETab";
// Execution starts from here and connection is established
public static void main(String[] args {
try(Connection tcon =DriverManager.getConnection(CON_URL,TUSR,TPASS);c. Execution of query: A statement object to hit the database and query the records need to be created using createStatement method. Using this object records are fetched from the database and stored in result set rst.
Statement tstmt = tcon.createStatement();
ResultSet rs = tstmt.executeQuery(QRY);d. Cull out the data from the extracted result set and display them: getint() or getString() method should be used to cull out the data from the result set obtained by executing the query.
{
while rsnext() {
System.out.print("EMP no: " + rs.getint("emp_no") );
System.out.print("Name: "+rs.getString("name") );
System.out.print("Salary: "+rs.getint("salary") ); }
}e. Handling the exception If any error occurs it has to be managed and the exception should be displayed for the developers to handle it.
catch (SQLException x) { x.printStackTrace(); }
} }2. With Oracle Database
a. Defining Driver class: For connecting to the Oracle database we need to define the driver class in the program
import java.sql.*;
class OracleJDBC {
public static void main(String[] args {
try {
class.forName("oracle.jdbc.driver.OracleDriver");b. Connection String (URL): This consists of driver definition, server name, port number, Oracle service name which is available tsname.ora file in the installation. User names and passwords are also provided in the connection string. (Testuser is the user name and tst@12 is the password.
Connection tcon = DriverManager.getConnection("jdbc:oracle:xxxx:@localhost:9999:sr","Testuser","tst@12") ;Jdbc – API name , Oracle – Databsae, xxxx is the driver name, @localhost is the server name (IP number can also be used instead of name), 9999 – Port number, Testuser is the user name and tst@12 is the password
c. Database query: The object to hit the database has to be created, and using the object database has to be queried. The results are stored in the result set.
Statement tstm = con.createStatement();
Resultset rs = tstm.executeQuery("Select * from Tablexyz");
while ( rs.next() )
System.out.println(rs.getString(1)+" "+rs.getInt(2));d. Exception: Exceptional errors will have to be captured and displayed to the user.
}
catch (Exception x) {System.out.println(x);}
} }3. With SQL Server
The steps were already explained in the earlier paragraph on how to achieve JDBC for SQL servers. The example is
Importing the packages
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;To create connect string and establish connection
public class SQLSERVJDBC {
public static void main(String[] args {
String Consqsurl = "jdbc:sqlserver://<server>:<port>;databaseName=Test;user=<user>;password-<password>";
try (Connection ccc = DriverManager.getConnection(Consqsurl);
Statement stst = ccc.create.Statement();)Server name, port number, user name, and password will be substituted with the data from the configuration file when the line gets executed
Hit the database and fetch the data
{ String SQL1 = "select * from tablexyz";
ResultSet rs = stst.executeQuery(SQL1);Display the data fetched
while (rs.next()) {
System.out.println(rs.getString("Name") + " " + rs.getString("Address") ); } }Exception
catch (SQLException x) { x.printStackTrac(); }
} }With DB2
Importing the packages
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;Connection string creation and establish connectivity with the database
public class DB2JDBCCON{
public static void main(String[] args {
String db2url = "jdbc:db2://servername:1234/DB";
// 1234 port nnumber DB - Database name
try { connection = DriverManager.getConnection(db2url,"user","password");The rest of the things are the same as other databases.
Conclusion
The importance of JDBC connectivity and its attributes were explained in detail above. The way it works in SQL server was dealt with fully and for the other databases like MySQL, DB2, Oracle the java code structure was also provided.
Recommended Articles
This is a guide to JDBC SQL Server Connection String. Here we discuss the What is JDBC SQL Server Connection String?, How to use JDBC SQL Server? with examples for better understanding. You may also have a look at the following articles to learn more –
