JDBC provides Java applications with access to most database systems via SQL. The architecture and API closely resemble Microsoft’s ODBC. JDBC classes are contained within the java.sql package. There are few classes and several interfaces. [Download Java Programming with Oracle JDBC]

JDBC is an interface to communicate with a relational database. It treats database tables/rows/columns as Java objects. JDBC driver is an implementation of the JDBC interface. It communicates with a particular database.

You will need to use SQL(Structured Query Language) to communicate with database systems. There are lots of fancy things you can do with SQL, we will just look at simple stuff…

Java code calls JDBC library. JDBC loads a driver. Driver talks to a particular database.

JDBC API and Drivers

  • JDBC API is generic: java.sql package
  • A Database system needs to supply a driver.
  • A Java program need to use a specific driver.

JDBC Object Classes

  • DriverManager: Loads, chooses drivers
  • Driver: Connects to actual database
  • Connection: A series of SQL statements to and from the DB
  • Statement: A single SQL statement
  • ResultSet: The records returned from a Statement

Seven Steps

1. Load the driver

Load JDBC driver using Class.forName("com.mysql.jdbc.Driver").newInstance();

2. Define the connection URL

The connection is defined by URL. The URL has the following form:

jdbc:driver:databasename 

Examples:

  • jdbc:odbc:MyOdbcDatabase
  • jdbc:postgres:WebsiteDatabase
  • jdbc:oracle:CustomerInfo

URL Format:  “jdbc:<subprotocol>:<subname>”

jdbc:mysql://128.100.53.33/GROUPNUMBER?user=USER&password=PASSWORD

3. Establish the connection

You need to make connection as given below.

Connection conn=DriverManager.getConnection(url);

If connection is successful, then create statement and execute query

4. Create a Statement object

After connection, you need to create statement.

Statement stmt = conn.createStatement();

stmt object sends SQL commands to database

5. Execute a query using the Statement

After creating statement you will use executeQuery()  for SELECT statements. Similarly, use executeUpdate()  for INSERT, UPDATE, DELETE, statements.

stmt.executeQuery("SELECT …");
stmt.executeUpdate("INSERT …");

The Statement interface defines two methods for executing SQL against the database.

public ResultSet executeQuery(String sql)
public int executeUpdate(String sql)
  • ExecuteQuery returns a ResultSet
    • All rows and columns which match the query are contained within the ResultSet
    • The developer navigates through the ResultSet and uses the data as required.
  • ExecuteUpdate returns the number of rows changed by the update statement
    • This is used for insert statements, update statements and delete statements

6. Process the result

Result of a SELECT statement (rows/columns) returned as a ResultSet object.

ResultSet rs = stmt.executeQuery("SELECT * FROM users");

Step through each row in the result

rs.next()

Get column values in a row

String userid = rs.getString(“userid”);
int type = rs.getInt(“type”);
ResultSet rs = stmt.executeQuery("SELECT * FROM users");
while (rs.next()) {
        String userid = rs.getString(1);
        String firstname = rs.getString(“firstname”);
        String lastname = rs.getString(“lastname”);
        String password = rs.getString(4);
        int type = rs.getInt(“type”);
        System.out.println(userid + ” ” + firstname + ” ” + lastname + ” ” + password + ” ” + type);
}

7. Close the connection

Close the ResultSet object

   rs.close();

Close the Statement object

   stmt.close();

Close the connection

   conn.close();

Example:

import java.sql.*;
public class Tester {
	public static void main(String[] args) {
		try {			// Load JDBC driver
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			// Make connection
			String url =
			“jdbc:mysql://localhost:3306/employee”
			Connection conn = DriverManager.getConnection(url,”root,”””);
			// Create statement
			Statement stmt = conn.createStatement();
			// Print the users table
		ResultSet rs = stmt.executeQuery("SELECT * FROM users");
			while (rs.next()) {------}
				rs.close(); stmt.close(); conn.close();
	} catch (Exception e) {
			System.out.println("exception " + e);	}}

Example Code:

Connection aConnection;
try{
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException x)
{
	System.out.println("Cannot find driver class.  Check CLASSPATH");
	return;
}
try{
	aConnection = DriverManager.getConnection("jdbc:odbc:MyDatabase",
											"Username", "Password");
}
catch(SQLException x)
{
	System.out.println("Exception connecting to database:" + x);
	return;
}
try{
	Statement aStmt = aConnection.createStatement();
	StringBuffer sb = new StringBuffer("SELECT Employee_id, Employee_Name");
	sb.append(" FROM Employee WHERE EmployeeId>100");
	ResultSet rs = aStmt.executeQuery(sb.toString());
	while(rs.next())
	{
		int employeeId = rs.getInt(1);
		String employeeName = rs.getString(2);
		System.out.println("Id:" + employeeId + "\nName:" + employeeName);
	}
	rs.close();
	aStmt.close();
}
catch(SQLException x){
	System.out.println("Exception while executing query:" + x);
}

Another Example:

// A driver for imaginary1
Class.forName("ORG.img.imgSQL1.imaginary1Driver");
// A driver for imaginary2
Driver driver = new ORG.img.imgSQL2.imaginary2Driver();
DriverManager.registerDriver(driver);
//A driver for oracle
Class.forName("oracle.jdbc.driver.OracleDriver");

Connecting to the Database

Connection con = DriverManager.getConnection("jdbc:imaginaryDB1");

Transactions

  • Transaction = more than one statement which must all succeed (or all fail) together
  • If one fails, the system must reverse all previous actions
  • Also can’t leave DB in inconsistent state halfway through a transaction
  • COMMIT = complete transaction
  • ROLLBACK = abort

SQL-Java Type Mapping

Tagged with: ClassesJAVAJAVA GUIObject OrientedProgramming
 

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 

Looking for something?

Use the form below to search the site:


Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!

Related News Feeds

Set your Twitter account name in your settings to use the TwitterBar Section.