Java Database Connection
Database connection in java is very important process in JDBC. This post is for demonstrating internals of java database connectivity. In this post we will get to know How to connect with database in java, Java database connectivity, internals of database connectivity in java. I will also give a sample example of java database connectivity.
For database connection we need to import some packages and need to use some special methods. Before getting into deep I will explain about these methods and packages. Following is details of how to connect with database.
Step by step explanation of Java database connection-
1. Import Package- For creating database connection you need some functions and classes. These functions and classes are found in a package so we need to import that package using "import java.sql.*".
2. Loading Driver- Driver is required for conversion of sql statements into back end database understandable form and vice versa.
Class.forName("oracle.jdbc.driver.OracleDriver");
Class.forName is used to register the driver.
import java.sql.*;
public class dbConnect
3. Create Connection-
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");
4. Define SQL stmt in the form of String class's object
5. Send the SQL stmt
6. Excecute the query and get the result
Sample example of java database connectivity-
//STEP 1. Import "java.sql" packageimport java.sql.*;
public class dbConnect
{
// JDBC driver name and database URL
static final String DRIVER = "com.mysql.jdbc.Driver";
static final String URL = "jdbc:mysql://localhost/TEST";
// Database credentials
static final String USR = "username";
static final String PWD = "password";
public static void main(String[] args) {
Connection con = null;
Statement stmtt = null;
try
// JDBC driver name and database URL
static final String DRIVER = "com.mysql.jdbc.Driver";
static final String URL = "jdbc:mysql://localhost/TEST";
// Database credentials
static final String USR = "username";
static final String PWD = "password";
public static void main(String[] args) {
Connection con = null;
Statement stmtt = null;
try
{
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database for I/O operation...");
con = DriverManager.getConnection(URL,USR,PWD);
//STEP 4: Execute a query
System.out.println("Statement Creation for I/O operation...");
stmtt = con.createStatement();
String sqlstmt;
sqlstmt = "SELECT id, first, last, age FROM Emp";
ResultSet rs = stmt.executeQuery(sql);
//STEP 5: Extracting the data from result set for next operation
while(rs.next())
//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
//STEP 3: Open a connection
System.out.println("Connecting to database for I/O operation...");
con = DriverManager.getConnection(URL,USR,PWD);
//STEP 4: Execute a query
System.out.println("Statement Creation for I/O operation...");
stmtt = con.createStatement();
String sqlstmt;
sqlstmt = "SELECT id, first, last, age FROM Emp";
ResultSet rs = stmt.executeQuery(sql);
//STEP 5: Extracting the data from result set for next operation
while(rs.next())
{
//Retrieve by column name
int id1 = rs.getInt("id");
int age2 = rs.getInt("age");
String first1 = rs.getString("first");
String last1 = rs.getString("last");
//Display values
System.out.print("ID: " + id1);
System.out.print(", Age: " + age1);
System.out.print(", First Name: " + first1);
System.out.println(", Last Name: " + last1);
}
//Retrieve by column name
int id1 = rs.getInt("id");
int age2 = rs.getInt("age");
String first1 = rs.getString("first");
String last1 = rs.getString("last");
//Display values
System.out.print("ID: " + id1);
System.out.print(", Age: " + age1);
System.out.print(", First Name: " + first1);
System.out.println(", Last Name: " + last1);
}
//STEP 6: Cleaning - up environment
rs.close();
stmtt.close();
con.close();
}
catch(SQLException se)
{
//Handle errors for JDBC
se.printStackTrace();
}
//Handle errors for JDBC
se.printStackTrace();
}
catch(Exception e)
{
//Handle errors for Class.forName
e.printStackTrace();
}
//Handle errors for Class.forName
e.printStackTrace();
}
finally
{
//finally block is used for closing all resources
try
//finally block is used for closing all resources
try
{
if(stmt!=null)
stmtt.close();
}
if(stmt!=null)
stmtt.close();
}
catch(SQLException se2)
{
}
// nothing we can do
try
try
{
if(con!=null)
conn.close();
}
if(con!=null)
conn.close();
}
catch(SQLException se)
{
se.printStackTrace();
}
se.printStackTrace();
}
//end finally try
}
}
//end try
System.out.println("bye!");
System.out.println("bye!");
}
}
OUTPUT-
It is very important to know, how to execute this program so now let's know it.
Type following command in command line.
After execution of this command you will get following result-
Connecting to database First Time...
Statement Creation...
ID: 101, Age: 25, First Name: Vikash, Last Name: Shukla
Id,Age,First Name,Last Name may be different as per records in database.