Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

JDBC Basics: How to deal with databases in Java

In this article will learn some important concepts about databases in java and will see an example for inserting, deleting, updating and modifying records in databases in Windows environment using java.

A Database Management System (DBMS) provides mechanisms for storing, organizing, retrieving and modifying data from a database. Today, most databases are relational databases. Some popular relational database systems are Microsoft SQL Server, Oracle, Sybase, Mysql etc.

A language called Structured Query Language (SQL) pronounced “sequel” is the standard language used with relational databases to execute queries and to manipulate data. Several applications store their data in a database. Hence, a java program has to communicate with the database to access and manipulate the data. This is possible using the JDBC API. The JDBC API is a Java API that can access any kind of tabular data, especially data stored in a relational database.

JDBC API

The Java Database Connectivity Application Programming Interface is a set of specifications and specifies the process how a Java program can communicate with the database. This API contains set of classes and Interfaces to enable programmers to communicate with a database using java.

To be more specific, it defines how an application opens a connection, communicates with the database, executes SQL statements and retrieves the results. Using JDBC, an application can access virtually any data source in a platform-independent manner.

What does the JDBC API do?

The JDBC API makes it possible to do three things:

  1. Establish a connection with a data source.
  2. Send queries and update statements to the data source.
  3. Process the result.

NOTE: To use JDBC, you need to install a database that is compatible with JDBC. Several databases are available which you can install. For example: postgreSQL, Oracle,MySQL etc.

For each database, the appropriate driver should be installed. The installation procedure and the settings vary from vendor to vendor. Please consult the documentation for this purpose.

JDBC Drivers

A JDBC driver allows the Java application to communicate with the database. It also ensure that the request made by the application are presented to the database in a language understood by the database. It does the following tasks:

  1. The JDBC driver receives the requests from the client.
  2. It converts the request into the format understandable by the database.
  3. It then submits the request to the database.
  4. The JDBC driver receives the response from the database.
  5. It translates the response back to Java data format.
  6. It submits the response to the client application.

Hence, the JDBC driver speaks to both the java and the language understood by the database.

Standard JDBC Drivers and Database URL

Different database vendors provide drivers so that a Java application can communicate with the database. A JDBC driver uses a JDBC URL to identify and connect to a particular database. These URLs are generally of the form:

jdbc:driver:databasename

However, different databases require different information to connect successfully. Hence, the syntax may vary. For example, the oracle JDBC-Thin driver uses a URL of the form

jdbc:oracle:thin:@<HOST>:<PORT>:<DB>

In most of the URL’s you will require the following:

  1. <HOST>: The name of IP address of the database server machine. If the database is running in the same machine, the host name is localhost.
  2. <POST>: The port address where the server listens for incoming connections.
  3. <DB>: The database name you want to connect to.
  4. <SID>: System ID of the database server instance (used with Oracle).

Basic JDBC Steps

All JDBC programs follow some basic steps to communicate with a database. These are:

  1. Load the driver.
  2. Establish the connection.
  3. Create a statement object.
  4. Execute a query.
  5. Process the result.
  6. Close the connection.

Syntax and Example for each step:

1. Loading the driver

Listing 1: Syntax for loading the driver

Class.forName(“driverName”);

Listing 2: Example of loading the driver

Class.forName(“ sun.jdbc.odbc.JdbcOdbcDriver”);

Class.forName(“ org.gjt.mm.mysql.Driver” );

2. Establish a connection

Listing 3: Syntax for establishing a connection

DriverManager.getConnection(“url ”,”user ”,”password ”);

Listing 4: Example of establishing a connection

Connection conn= DriverManager.getConnection(“jdbc:postgresql://localhost/mydb”, “testuser”, “mypassword”);

Connection conn= DriverManager.getConnection(“jdbc:mysql://192.168.100.4/StudentDB”, “username”, “password”);

3. Create a statement object

Statement:

Listing 5: Example of statement

Statement stmt=conn.createStatement();

ii. PreparedStatement:

Listing 6: Example of prepared statement

String query= “UPDATE authors SET name=? WHERE id=?” ;
PreparedStatement pStmt=conn.prepareCall(“{cal SHOW_Authors}”);

iii. CallableStatement:

Listing 7: Example of Callable Statement

CallableStatement cStmt=conn.prepareCall(“{ call SHOW_Authors}”);

4. Execute queries

Listing 8: Example to execute queries

ResultSet rs=stmt.executeQuery(“SELECT * FROM Student”);

Listing 9: Another example of executing queries

int result = stmt.executeUpdate(“ UPDATE authors SET name= ‘abc’ WHERE id=1”);

Listing 10: Example 3

boolean ans = stmt.execute( “DROP TABLE IF EXISTS test”);

5. Process the result

Listing 11: Syntax for processing the result

resultSetObject.getXXX(String columnName);

resultSetObject.getXXX(int columnNumber);

Listing 12: Example of processing the result

rs.getString(“student_name”);

rs.getString(2);

rs.getInt(“Student_ID”);

rs.getInt(1);

6. Close the connection

Listing 13: Syntax for closing the connection

resultSetObject.close();

statementObject.close();

connectionObject.close();

Complete Database Example:

The following program displays the menu for performing insert, modify, delete, search, and select operation on the Student table stored in Mysql Database.

Listing 14: Code for database:

import java.io.*;
import java.sql.*;

class StudentJdbcMenu {
	public static void main(String args[]) {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		PreparedStatement ps1 = null, ps2 = null;
		BufferedReader br = new BufferedReader(new InputStreamReader(System.in));

		String name;
		int rno, ch;
		float per;
		try {
			/*
			 * Class.forName("org.postgresql.Driver");
			 * con=DriverManager.getConnection
			 * ("jdbc:postgresql:5333","root","redhat2");
			 */
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql:///ashu", "root", "");
			stmt = con.createStatement();
			ps1 = con.prepareStatement("insert into student values(?,?,?)");
			ps2 = con
					.prepareStatement("update student set name=?,percentage=? where roll_no=?");

			if (con != null) {
				do {
					System.out
							.println("1.Insert \n 2.Modify\n 3.Delete \n 4.Search \n 5.View All \n 6.Exit\n");

	System.out.println("Enter the choice:");
	ch = Integer.parseInt(br.readLine());
		switch (ch) {
		case 5:
rs = stmt.executeQuery("select * from student");
			System.out
	.println("Roll Number \t Name \t Percentage\n");

	while (rs.next()) {
	System.out.println(rs.getInt(1) + "\t\t"
		+ rs.getString(2) + "\t\t" + rs.getFloat(3)
	+ "\n");
	}
	break;
			case 1:
			System.out.println("Enter the roll number:");
			rno = Integer.parseInt(br.readLine());
			if (rno < 0)
			throw new Exception("Enter positive number");
			System.out.println("Enter the name:");
			name = br.readLine();
			System.out.println("Enter the percentage:");
			per = Float.parseFloat(br.readLine());
			if (per < 0)
			throw new Exception("Enter positive percentage");
			ps1.setInt(1, rno);
			ps1.setString(2, name);
			ps1.setFloat(3, per);
			ps1.executeUpdate();
			break;
			case 3:
			System.out
			.println("Enter the roll number to be deleted:");
			rno = Integer.parseInt(br.readLine());
			if (rno < 0)
			throw new Exception("Enter positive number");
			rs = stmt
			.executeQuery("select * from student where roll_no="
			+ rno);
			if (rs.next()) {
			stmt.executeUpdate("delete from student where roll_no="
			+ rno);
			System.out.println("Record Deleted");
			} else
			// System.out.println("Record not found");
			throw new Exception("Record Not In Table");
			break;

			case 2:
			System.out.println("Enter the roll number to modify");
			rno = Integer.parseInt(br.readLine());
			if (rno < 0)
			throw new Exception("Enter positive number");
			rs = stmt
			.executeQuery("select * from student where roll_no="
			+ rno);
			if (rs.next()) {
			System.out.println("Enter the name:");
			name = br.readLine();
			System.out.println("Enter the percentage:");
			per = Float.parseFloat(br.readLine());
			if (per < 0)
			throw new Exception("Enter positive percentage");
			ps2.setString(1, name);
			ps2.setInt(3, rno);
			ps2.setFloat(2, per);
			ps2.executeUpdate();
			} else
			throw new Exception("Record not found in table");
			break;

			case 4:
			System.out.println("Enter the roll number to search:");
			rno = Integer.parseInt(br.readLine());
			if (rno < 0)
			throw new Exception("Enter positive number");
			rs = stmt
			.executeQuery("select * from student where roll_no="
			+ rno);
			if (rs.next()) {
			System.out.println("Record Found");
			System.out.println("Roll number=" + rs.getInt(1)
			+ "\tName=" + rs.getString(2)
			+ "\tPercentage=" + rs.getFloat(3));
			} else
			System.out	
			.println("Sorry! Record not Found in table");
			case 6:
			System.exit(0);
			default:
			System.out.println("Wrong choice:");
			}
			} while (ch != 6);
			rs.close();
			stmt.close();
			con.close();
			}
		} catch (NumberFormatException ae) {
			System.out.println("Wrong value Entered");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}

	}// main
}// class

OUTPUT:


1.Insert 
 2.Modify
 3.Delete 
 4.Search 
 5.View All 
 6.Exit

Enter the choice:
5
Roll Number      Name    Percentage

101             ashu            78.8

102             aryan           88.8

103             pankaj          88.0

1.Insert 
 2.Modify
 3.Delete 
 4.Search 
 5.View All 
 6.Exit

Enter the choice:
1
Enter the roll number:
104
Enter the name:
vivek
Enter the percentage:
79.8
1.Insert 
 2.Modify
 3.Delete 
 4.Search 
 5.View All 
 6.Exit

Enter the choice:
5
Roll Number      Name    Percentage

101             ashu            78.8

102             aryan           88.8

103             pankaj          88.0

104             vivek           79.8

1.Insert 
 2.Modify
 3.Delete 
 4.Search 
 5.View All 
 6.Exit

Enter the choice:
2
Enter the roll number to modify
104
Enter the name:
champ
Enter the percentage:
87.6
1.Insert 
 2.Modify
 3.Delete 
 4.Search 
 5.View All 
 6.Exit

Enter the choice:
5
Roll Number      Name    Percentage

101             ashu            78.8

102             aryan           88.8

103             pankaj          88.0

104             champ           87.6

1.Insert 
 2.Modify
 3.Delete 
 4.Search 
 5.View All 
 6.Exit

Enter the choice:
4
Enter the roll number to search:
102
Record Found
Roll number=102 Name=aryan      Percentage=88.8


Showing the output after executing StudentJdbcMenu.java program

Figure 1: Showing the output after executing StudentJdbcMenu.java program.

Conclusion

In this article we learn:

  • Some database concepts about java.
  • Example to perform various operations using JDBC.


Have more than 5 years experience about Java, .Net, C and C++.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login