- JDBC provides a standard library for accessing relational databases.
- JDBC API standardizes, the approach for connecting to databases, the syntax for sending SQL queries and committing transactions and provides the data structure representing the result.
- By using JDBC you can change database hosts, ports, and even database vendors with minimal changes to your code.
7 basic steps in using JDBC
- Load the JDBC driver
- Define the connection URL
- Establish the connection
- Create a Statement object
- Execute a query or update
- Process the results
- Close the connection
Note : Please note that below steps were demonstrated with MySQL Database.
- Load the JDBC driver
- The driver is the software that knows the way to communicate with database server
- To load the driver, load the appropriate class; a static block in the driver class itself automatically makes a driver instance and registers it with the JDBC driver manager
- Class.forName method takes a string representing a fully qualified class name and loads the corresponding class.
public class SQLConnection {
public void mySQLConnection() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
System.out.println(e);
}
}
}
- Specify the location of the database server
- URLs referring to databases use the jdbc: protocol and embed the server host, port, and database name
- The exact format is defined in the documentation that comes with the particular driver
public class SQLConnection {
public void mySQLConnection() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
// "jdbc:mysql//" + host +":" + port + “/" + dbName;
String mySqlURL = "jdbc:mysql://localhost:3306/cgj";
} catch (ClassNotFoundException e) {
System.out.println(e);
}
}
}
3. Establish the connection
- To make the connection with the database, pass the URL, database username, and database password to the getConnection method of the DriverManager class
import java.sql.*;
public class SQLConnection {
public void mySQLConnection() throws SQLException {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
// "jdbc:mysql//" + host +":" + port + “/" + dbName;
String mySqlURL = "jdbc:mysql://localhost:3306/cgj";
Connection con=DriverManager.getConnection(mySqlURL,"root","root");
} catch (ClassNotFoundException e) {
System.out.println(e);
}
}
}
- Used to send queries and commands to the database.
- Most of the database drivers permit multiple concurrent Statement objects to be open on the same connection
import java.sql.*;
public class SQLConnection {
public void mySQLConnection() throws SQLException {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
// "jdbc:mysql//" + host +":" + port + “/" + dbName;
String mySqlURL = "jdbc:mysql://localhost:3306/cgj";
Connection con=DriverManager.getConnection(mySqlURL,"root","root");
Statement stmt=con.createStatement();
} catch (ClassNotFoundException e) {
System.out.println(e);
}
}
}
- You can use Statement object to send SQL queries by using the executeQuery method, which returns an object of type ResultSet
- executeUpdate method is used for UPDATE, INSERT, or DELETE commands, which will return the number of rows affected, which could be zero
import java.sql.*;
public class SQLConnection {
public void mySQLConnection() throws SQLException {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
// "jdbc:mysql//" + host +":" + port + “/" + dbName;
String mySqlURL = "jdbc:mysql://localhost:3306/cgj";
Connection con=DriverManager.getConnection(mySqlURL,"root","root");
Statement stmt=con.createStatement();
String query = "select * from employee";
ResultSet resultSet = stmt.executeQuery(query);
} catch (ClassNotFoundException e) {
System.out.println(e);
}
}
}
6. Process the results
- Use the next method of ResultSet to move through the table a row at a time
- Within a row, ResultSet provides various getXxx methods that take a column name or column index as an argument and return the result in a variety of different Java types
- The first column in a ResultSet row has index 1, not 0
import java.sql.*;
public class SQLConnection {
public void mySQLConnection() throws SQLException {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
// "jdbc:mysql//" + host +":" + port + “/" + dbName;
String mySqlURL = "jdbc:mysql://localhost:3306/cgj";
Connection con=DriverManager.getConnection(mySqlURL,"root","root");
Statement stmt=con.createStatement();
String query = "select * from employee";
ResultSet resultSet = stmt.executeQuery(query);
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
} catch (ClassNotFoundException e) {
System.out.println(e);
}
}
}
- Closing the connection also closes the corresponding Statement and ResultSet objects
- Postpone closing the connection if there's to perform additional database operations
- Reusing existing connections is such an important optimization that the JDBC 2.0 API defines a ConnectionPoolDataSource interface for obtaining pooled connections
import java.sql.*;
public class SQLConnection {
public void mySQLConnection() throws SQLException {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
// "jdbc:mysql//" + host +":" + port + “/" + dbName;
String mySqlURL = "jdbc:mysql://localhost:3306/cgj";
Connection con=DriverManager.getConnection(mySqlURL,"root","root");
Statement stmt=con.createStatement();
String query = "select * from employee";
ResultSet resultSet = stmt.executeQuery(query);
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
con.close();
} catch (ClassNotFoundException e) {
System.out.println(e);
}
}
}
No comments:
Post a Comment