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

How to Use JDBC With Console App and Type1 Driver

In this article, we will discuss the different components of Java database connectivity or JDBC, an important system to connect programs written in Java to the data in popular databases

Java Database Connectivity (JDBC) is mainly an application program interface (API) specification to connect Java based programs to that with popular databases. The application program interface lets us encode access request statements in Structured Query Language (SQL) that are then passed to the program that interacts with the database, it also returns the result through a similar interface. JDBC is very similar to the SQL Access Group’s Open Database Connectivity (ODBC) and with a small bridge program, we can use the JDBC interface to access databases through the ODBC interface. In this article, we will learn how to make a Java console application using the various methods accessing an SQL server Database, and manipulating the data of the same.

Requisites

For this article, we need some requisite knowledge of making a database in SQL server. We are going to use the following predefined database and table for manipulation:

  • s_details: it’s a SQL server Database
    • student: it’s a table of the s_details database.
      • name: it is a column name of the student table of s_details database.
      • course: it is a column name of the student table of s_details database.
      • marks: it is a column name of the student table of s_details database.

Further, we are going to use:

  • Netbeans IDE: it’s an IDE that provides comprehensive facilities to the user for Java software development.

Steps of execution

We are going to follow a step by step approach to get the desired result. The steps that we are going to execute are:

Step

Description

Making a database and table

We are going to use predefined database and manipulate the table of that particular database. But the user can also make a database of his own from desired sources.

Making an ODBC data source

As it’s been mentioned at the beginning of the article, we are going to make an ODBC data source that is going to work as a “bridge” between the program and the database.

Making the program

Last but not the least we are going to make a Java program that is going to accept the input from the user and save, display, delete, update the data of the particular table.

Making a Database and table

We are going to use predefined SQL server database for this article but we can easily create a database by logging into SQL server and execute the following query:

Listing 1. Query for creating the database

CREATE DATABASE s_details

After writing the whole query, select the whole query and click on the execute option or press f5 button, the database will be created. After creating the database, we will create a table under the same database, so we need to execute the following query:

Listing 2. Query for creating the table

USE s_details 

CREATE TABLE student 
  ( 
     NAME   VARCHAR(255), 
     course VARCHAR(255), 
     marks  INT, 
  )

Figure 1. Output of Listing 1 & 2: Creating database and the table

After writing the whole query select the whole query and click on the execute option or press F5 button, the table will be created. Then we are going to move on to the next step.

Making an ODBC data source

This is a very vital step as ODBC data source is going to work as a “bridge” between the program and the database. Below are the steps that are going to be used to create an ODBC data source:

  • Click on "Start".
  • Go to "Control Panel"
  • Click on "Administrative Tools"
  • Click on "ODBC Data Sources"
  • Under the "User DSN" tab click on "Add..." option
  • "Create New Data Source" window appears
  • Select "SQL" Server from the list and click on "Finish"
  • "Create a new Data Source to SQL Server" window appears
  • Add name and description of the data source, and if the server is local then, "."(dot) will be placed at the server text box, then click "Next".
  • From here we are going to select “With SQL Server authentication using…” option and going to provide the Login ID and password of the SQL Server here.
  • From here, click the “Default Database to” option and “s_details” will be selected. Click on “Next”, then click “Finish”.
  • A dialogue box will appear. From here click “Test Data Source” option. If the Data source configuration has been entered perfectly then the status will come as “TESTS COMPLETED SUCCESSFULLY!” If not, then please re-configure the data source from the beginning by doing these steps.

Making the Program

After creating the database and the data source, the only work that is left to is making a program that will interact with the database and do desired task(in this case, which is adding, displaying, deleting, and updating the data. We are going to use Netbeans IDE for this purpose, but the user can use any Java program maker to do the same task. For making the program we are going to open the NetBeans IDE and click on the file option and then we are going to click on “New Project”, from the list we are going to select Java then Java Application. After that a window will appear from which we can add our desired path location for the project then click on “Finish”.

Accepting and saving the data in database

In this step, we are going to add the functionality of accepting and saving data to the table within the blank program that has been created by NetBeans IDE. Here, we are going to use “java.lang.*" and “java.io.*” to accept user input and will use “java.sql.*” for database connectivity. Below is the code which we are going to use for doing the same:

Listing 3. Code for accepting and saving the data to the table

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

class JdbcTest {
	BufferedReader br = new BufferedReader(new InputStreamReader(System. in ));

	String name, course;
	int marks;

	public static void main(String[] args) {
		JdbcTest jt = new JdbcTest();
		jt.AcceptSave();
	}


	public void AcceptSave() {
		try {
			System.out.println(“Enter Student Name”);
			name = br.readLine();

			System.out.println(“Enter Student Course”);
			course = br.readLine();

			System.out.println(“Enter Student Marks”);
			marks = Integer.parseInt(br.readLine());

			Connection con = DriverManager.getConnection(“jdbc: odbc: priyamdsn”, ”sa”, ”niitsiliguri”);
			Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

			PreparedStatement ps = con.prepareStatement(“insert student values( ? , ? , ? )”);
			ps.setString(1, name);
			ps.setString(2, course);
			ps.setInt(3, marks);
			ps.executeUpdate();

			System.out.println(“Data Saved…”);
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}
}

To connect to a database via JDBC-ODBC driver, we need a “Connection” object. The Connection object uses a DriverManager. The DriverManager passes in our ODBC data source details, user name and password. Below is the syntax:

Connection con = DriverManager.getConnection( host, username, password );

The connection and DriverManager are the class of “java.sql” namespace. And the DriverManager calls the getConnection method to pass the above details. If the connection is successful, a Connection object is created, which in our case is named “con”.

Connection con = DriverManager.getConnection (“jdbc:odbc:priyamdsn”,”sa”,”niitsiliguri”);

“priyamdsn” is the name of the ODBC driver, you can put your ODBC driver’s name, after that “sa” is the user name of the SQL server database, and “niitsiliguri” is the password of the SQL server database.

Note : Class.forName(String className) method returns the Class object associated with the class or interface with the given string name. This “Class” class comes under the “java.Lang” package. when we call forName() method on Class class what happen is

  • Initially the class is loaded into the memory
  • Then it calls the static method forName()
  • The static forName() method contains a static block. That static block register the loaded driverclass with the DriverManager class.

Last but not the least PreparedStatementinterface object represents a precompiled SQL statement. This interface is used to efficiently execute SQL statements multiple times. It comes under the “java.sql” package.

To use PreparedStatement interface in source code, first we need to create object of PreparedStatement by calling prepareStatement() method. The prepareStatement() is available in java.sql.Connection interface.

Afterthat we need to set the value to each ? by using the setter method from PreparedStatement interface as follows:

Syntax :object.setXXX(ColumnIndex,value)

SQL datatype

Method used

char/varchar/varchar2

setString()

int/number

setInt()

float/number

setFloat()

double/Float

setDouble()

long/int

setLong()

int/short

setShort()

time

setTime()

datetime/date

setDate()

blob

setBlob()

The executeUpdate() method Executes the SQL statement in thisPreparedStatementobject, which must be an SQL Data Manipulation Language (DML) statement, such asINSERT,UPDATEorDELETE; or an SQL statement that returns nothing, such as a DDL statement.

Figure 2. Output of Listing 3: Accepting and saving data

Displaying Data from the database

In this step, we are going to add the functionality of displaying data from the table within a program based on a particular condition. The data is fetched according to the name of the student. Here, we are going to use “java.lang.*" and “java.io.*” to accepting user input and will use “java.sql.*” for database connectivity. Below is the code which we are going to use for doing the same:

Listing 4. Code for displaying data of the table

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

class JdbcTest {

	BufferedReader br = new BufferedReader(new InputStreamReader(System. in ));
	String name;

	public static void main(String[] args) {
		JdbcTest jt = new JdbcTest();
		jt.disdata();
	}

	public void disdata() {
		try {
			System.out.println(“Enter Student Name”);
			name = br.readLine();

			Connection con = DriverManager.getConnection(“jdbc: odbc: priyamdsn”, ”sa”, ”niitsiliguri”);
			Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
			PreparedStatement ps = con.prepareStatement(“select * from student where name = ? ”);

			ps.setString(1, name);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				System.out.println(“\n\nName: ” + rs.getString(1));
				System.out.println(“\nCourse: ” + rs.getString(2));
				System.out.println(“Marks: ” + rs.getString(3));
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}
}

Figure 3. Output of Listing 4: For displaying data of the table

Deleting Data from the database

In this step we, are going to add the functionality of deleting data from the table within a program based on a particular condition. The data is fetched according to the name of the student. Below is the code which we are going to use for doing the same:

Listing 5. Code for deleting data of the table

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

class JdbcTest {

	BufferedReader br = new BufferedReader(new InputStreamReader(System. in ));
	String name;

	public static void main(String[] args) {
		JdbcTest jt = new JdbcTest();
		jt.deldata();
	}

	public void deldata() {
		try {
			System.out.println(“Enter Student Name”);
			name = br.readLine();

			Connection con = DriverManager.getConnection(“jdbc: odbc: priyamdsn”, ”sa”, ”niitsiliguri”);
			Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
			PreparedStatement ps = con.prepareStatement(“delete from student where name = ? ”);

			ps.setString(1, name);

			ps.executeUpdate();
			System.out.println(“Data Deleted…”);
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}
}

Figure 4. Output of Listing 5: Display of deleting data of the table

Updating Data from the database

In this step, we are going to add the functionality of updating data from the table within a program based on a particular condition. The data is fetched according to the name of the student. Here we are going to use “java.lang.*" and “java.io.*” to accepting user input and will use “java.sql.*” for database connectivity. Below is the code which we are going to use for doing the same:

Listing 6. Code for displaying data of the table

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

class JdbcTest {

	BufferedReader br = new BufferedReader(new InputStreamReader(System. in ));
	String name;

	public static void main(String[] args) {
		JdbcTest jt = new JdbcTest();
		jt.updatedata();
	}

	public void updatedata() {
		try {
			System.out.println(“Enter Student Name to update it’s data”);
			name = br.readLine();

			Connection con = DriverManager.getConnection(“jdbc: odbc: priyamdsn”, ”sa”, ”niitsiliguri”);
			Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
			PreparedStatement ps = con.prepareStatement(“select * from student where name = ? ”);

			ps.setString(1, name);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				System.out.println(“Current Course” + rs.getString(2));
				System.out.println(“Current Marks”rs.getString(3));
			}

			System.out.println(“Enter student’s new course”);
			String new_crs = br.readLine();

			System.out.println(“Enter student’s new marks”);
			String new_mrks = Integer.parseInt(br.readLine());

			PreparedStatement ps1 = con.prepareStatement(“update student set course = ? , marks = ? Where name = ? ”);

			ps1.setString(1, new_crs);
			ps1.setString(2, new_mrks);
			ps1.setString(3, name);
			ps1.executeUpdate();

			System.out.println(“Data Updated…”);
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}
}

Figure 5. Output of Listing 6: Display of showing data to table before entering new data

Figure 6. Output of Listing 6: Display of showing data to table afterentering new data

JDBC Using Swing GUI

JDBC type 1 driver, also known as the JDBC-ODBC Bridge, can be used for Java Swing app connectivity with the database. The coding for connecting with the database will be the same; just the procedure will change itself as it’s a GUI app.

Swing is a GUI widget toolkit for Java. It is part of Oracle’s Java Foundation Classes- an API which provides GUI for Java programs.

To make a swing component, we just need to do few simple steps at NetBeans IDE.

  • First, we have to right click at source package option at the very left window pane then new > JFrame Form
  • After that, we have to name our form and click finish.

A blank GUI will appear after clicking finish button.

Figure 7. The blank GUI that will appear after creation of the frame

Inserting data to the database

Now, from the right hand side of the screen we need to drag and drop three swing controls. First, we have to drag and drop three Labels. Then we have to drag and drop three Text Fields alongside of the Labels and at the very end we need to drag and drop a button.

Figure 8. After adding the proper controls

Now, we need to right-click at every text field control and select the option Edit text. All three label’s name would be changed to “Name”, “Course”, “Marks”. At the very same way we need to change the text value of the text field’s to blank and need to resize the text fields as well. After that we have to change the button’s name to the very same way named “Save”.

After changing the Text Field’s name we have to change the variable name of the text fields for data manipulation . Again, we have to right click at every text field once and have to select the option named “Change Variable Name…” a proper name should be given at this field as this would represent the text field’s containable things. So the names that have been given to the text fields are: Name – txt_name, Course – txt_course, Marks – txt_marks.

After this, we need to right click at the button and hover upon the “Events > Actions > Actions Performed”. The source code of the GUI would appear in front of us. Now we need to write down the same console connectivity code as well here:

Listing 7. Code of the button click event to save data

private void saveActionPerformed(java.awt.event.ActionEvent evt) {
	try {
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		Connection con = DriverManager.getConnection("jdbc:odbc:priyamdsn", "sa", "Sid071994");
		PreparedStatement ps = con.prepareStatement("insert into student values(?,?,?)");
		ps.setString(1, txt_name.getText());
		ps.setString(2, txt_course.getText());
		ps.setInt(3, Integer.parseInt(txt_marks.getText()));
		ps.executeUpdate();
		System.out.println("Data saved...");
	} catch (Exception e) {
		System.out.println(e.getMessage());
	}
}

At the ending of the code if we click shift+f6 then the output window will be generated.

Figure 9. Listing 7 output : Saving data to table

Note:The import of the packages will be same as before, like: java.sql.*, java.lang.*.The message would be shown to output box of the program, like in the above case “Data Saved…”

Displaying Data from the database

Same as before, we need to recreate a new JFrame form for taking input from the user and to show it. At the blank GUI form we need to add a label named “Enter Name to see data” and add a text field underneath of the same. Then we need to right click and change the variable name to “txt_name”. After that we need to drag and drop a button and have to change the name of it to display. Then we will right click on it, hover upon the events and then have to choose the action for the event. Again the code will appear for the click event. The code for displaying data of the database is same as the console app.

Listing 8. Code of the button click event to display data

private void displayActionPerformed(java.awt.event.ActionEvent evt) {

	try {
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		Connection con = DriverManager.getConnection("jdbc:odbc:priyamdsn", "sa", "Sid071994");
		PreparedStatement ps = con.prepareStatement("select * from student where name=?");
		7 ps.setString(1, txt_name.getText());

		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			System.out.println("\n\nName:" + rs.getString(1));
			System.out.println("\nCourse:" + rs.getString(2));
			System.out.println("Marks:" + rs.getInt(3));
		}
	} catch (Exception e) {
		System.out.println(e.getMessage());
	}
}

Figure 10. Listing 8 output : Displaying data of the table

Deleting Data from the database

The deletion procedure of the data is the same as the one in the console application; we just have to add another JFrame form to delete data at the default package location. Then at the blank GUI we need to add a label named “Type the name to delete the associated data”, a blank text field beneath it to take user input (the variable name of the text field would be ‘txt_name’), a button named “Delete” (the variable name of the button would be ‘delete’.) Then we will right click on it, hover upon the events and then have to choose the action for the event. Again the code will appear for the click event. The code for displaying data of the database is same as the console app.

Listing 9. Code of the button click event to delete data

private void deleteActionPerformed(java.awt.event.ActionEvent evt) {

	try {
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		Connection con = DriverManager.getConnection("jdbc:odbc:priyamdsn", "sa", "Sid071994");
		PreparedStatement ps = con.prepareStatement("delete from student where name=?");
		ps.setString(1, txt_name.getText());

		ps.executeUpdate();
		
		System.out.println("Data Deleted");
	} catch (Exception e) {
		System.out.println(e.getMessage());
	}
}

Figure 11. Listing 9 output : Deleting data of the table

Updating Data from the database

Updating the data is the same as the one for the console application just we have to put a little bit of extra steps at the time of updating data. First, we have to add a label, a blank text field, a button. We will name the text field “Enter the name to update associated data”, and will name the variable name of the button as “update”. Then we will right click on it, hover upon the events and then have to choose the action for the event. Again the code will appear for the click event. The code for displaying data of the database is same as the console app.

Listing 10. Code of the button click event to update data

private void updateActionPerformed(java.awt.event.ActionEvent evt) {
	BufferedReader br = new BufferedReader(new InputStreamReader(System. in ));
	try {
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		Connection con = DriverManager.getConnection("jdbc:odbc:priyamdsn", "sa", "Sid071994");
		PreparedStatement ps = con.prepareStatement("select * from student where name=?");
		ps.setString(1, txt_name.getText());

		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			System.out.println("\n\n Current Course\t" + rs.getString(2));
			System.out.println("Current Marks\t" + rs.getString(3));
		}
		System.out.println("\n\n Enter new Student Course\t");
		String qcrs = br.readLine();
		System.out.println("\n\n Enter new Student Marks\t");
		String qmrks = br.readLine();
		PreparedStatement ps1 = con.prepareStatement("Update student set course=?,marks=? where 
  name=?");
		ps1.setString(1, qcrs);
		ps1.setString(2, qmrks);
		ps1.setString(3, txt_name.getText());
		ps1.executeUpdate();
		System.out.println("Data Updated...");
	} catch (Exception e) {
		System.out.println(e.getMessage());
	}
}

Figure 12. Listing 10 output : Updating data of the table

NOTE: An additional import statement would be there for this update of data at the database. Here we need to import java.io.* package. Then we have to create an object of BufferedReader class.When we would click at the update button, at the output screen of NetBeans IDE, the data will be shown and we need to click at that window for adding additional data for replacing.

Conclusion

JDBC type 1 driver, also known as the JDBC-ODBC Bridge, is a database driver execution that employs the ODBC driver to connect with the database. Use of the ODBC driver has other installation dependencies; i.e. ODBC must be installed on a computer having the driver and the database (in our case SQL server 2012) must support an ODBC driver. JDBC type 1 driver is one of the easiest ways to connect with the database. One of the largest advantages that an ODBC driver have is, almost any database for which an ODBC driver is installed can be accessed and data can then be easily retrieved. After executing the above steps, you can now easily manipulate the data of your existing database, or also can create and make database connectivity within your program and database.



Founder of prTechnologies, one stop IT solution company whose motto is to create the finest software/websites for its clients.

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