JDBC-MySql Based Project Creation

Content to be covered:

  • Introduction of JDBC
  • How do you install MySQL Server on your PC?
  • Check if it is installed properly or not?
  • How do you create a database in MySQL Server?
  • How do you connect Eclipse IDE with MySQL Server?
  • How do you access the database created in the MySQL Server through a Java project? (Project)

Watch this video for a hands-on explanation and deeper insights:


Chapters (Clickable):

JDBC Introduction:

  • JDBC stands for Java Database Connectivity
  • JDBC MySQL connector is used to make a connection between Eclipse IDE and MySQL Server
  • Eclipse IDE has been used to create a Java-based project
  • Further, the database can be accessed through this Java-based project

1. Install MySQL Server

  • Click on this link and download the community server
  • Install the server and keep the username and password safe, as these will be used during project configuration

2. Create a database on this MySQL Server

2.1 First Login over the server with the admin(root) profile
mysql -u root -p
root@123
2.2 Now, create a database on this server
create database mysqlDatabase;

use mysqldatabase; -- have to use it before adding any table into it

-- Other operations (If required)
show databases; -- to check the different databases, this server contains
drop database mysqlDatabase; -- to delete the database you have created
2.3 Create a table in this database
create table jdbcTable (id int NOT NULL PRIMARY KEY, title varchar(255), author varchar(255), date varchar(255));

-- Other operations (If required)
drop table jdbcTable; -- to delete the table irtself
2.4 Insert data into it
insert into jdbcTable values (1,"MySql","Amit","2-2-2024");
insert into jdbcTable values (2,"MySql","Sumit","2-2-2024");
insert into jdbcTable values (3,"MySql","Atul","2-2-2024");
insert into jdbcTable values (4,"MySql","Anuj","2-2-2024");

select * from jdbcTable; -- check the content of this table
2.5 Delete data from this table or update data in this table
delete from jdbcTable where id = 4; -- to delete row('s) from the table

update jdbcTable set author = 'Ankur' where id = 3; -- to update the table

3. Connect your Eclipse IDE with this MySQL server

  • First, download the JDBC-MySQL Connector
  • Unzip this into a directory, there will be a .jar file inside this folder
  • Now, create a Java project in Eclipse IDE and configure the build path
  • Go to the library tab and click on the Add external jar option
  • Now add this .jar file (select the jar file) and click on the apply button

4. Now let’s access the created database in MySQL server through a Java Program (Project)

String userName = "root";
String password = "root@123";
String databaseName = "mysqlDatabase";

String URL = "jdbc:mysql://localhost:3306" + "/" + databaseName;

Connection conObj;
Statement stmObj;

conObj = DriverManager.getConnection(URL, userName, password);

stmObj = conObj.createStatement();

int a = stmObj.executeUpdate("insert into mysqlTable values (6, 'Mysql','Ankit','2-2-2024')");

System.out.println(a);

ResultSet rstObj;

rstObj = stmObj.executeQuery("select * from mysqlTable");

while(rstObj.next()) {
	System.out.println(rstObj.getString("id") + "," + rstObj.getString("title") + "," + rstObj.getString("author") + "," + rstObj.getString("date"));
}

The terminal output should look like this:

Assignment:

Create a database of employees with the following fields

  • Name
  • Code
  • Designation
  • Salary

(a) Write a Java program to create a GUI Java application to take employee data from the TextFields and store it in the database using JDBC connectivity.
(b) Write a JDBC Program to retrieve all the records from the employee database.

You may create any of the GUIs listed below:

1. Simple GUI Interface:
2. Extended GUI Interface:
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

public class BackendDevelopmentUsingJDBC {
	public static void main(String[] args) throws SQLException {

		ATWSwingInterface objInterface = new ATWSwingInterface();
//		mysqlWork();

	}

	static void mysqlWork() throws SQLException {
		Connection myConn = null;
		Statement myStmt = null;
		ResultSet myRs = null;

		try {
			String userName = "root";
			String password = "root@123";// MySql Server
			String databaseName = "mysqlDatabase";

			String URL = "jdbc:mysql://localhost:3306" + "/" + databaseName;

			myConn = DriverManager.getConnection(URL, userName, password);
			myStmt = myConn.createStatement();

			String insertDataQuery = "INSERT INTO jdbcTable VALUES (24, 'Mysql', 'Amit', '2024-2-2')";

//			String deleteQuery = "delete from jdbcTable where id = 6";

			int a = myStmt.executeUpdate(insertDataQuery);
			System.out.println(a);

			String showTableQuery = "select * from jdbcTable";
			myRs = myStmt.executeQuery(showTableQuery);

			System.out.println("id" + ", " + "title" + ", " + "author" + ", " + "date");

			while (myRs.next()) {
				System.out.println(myRs.getString("id") + ", " + myRs.getString("title") + ", "
						+ myRs.getString("author") + ", " + myRs.getString("date"));
			}

		} catch (Exception exc) {
			exc.printStackTrace();
		} finally {
			myRs.close();
			myStmt.close();
			myConn.close();
		}
	}
}

class ATWSwingInterface extends JFrame {

	Connection myConn = null;
	Statement myStmt = null;
	ResultSet myRs = null;

	ATWSwingInterface() throws SQLException {

		String userName = "root";
		String password = "root@123";// MySql Server/XAMP password
		String databaseName = "mysqlDatabase";

		String URL = "jdbc:mysql://localhost:3306" + "/" + databaseName;

		myConn = DriverManager.getConnection(URL, userName, password);
		myStmt = myConn.createStatement();

		JLabel l1 = new JLabel("id");
		JLabel l2 = new JLabel("title");
		JLabel l3 = new JLabel("author");
		JLabel l4 = new JLabel("date");

		JTextField t1 = new JTextField(15);
		JTextField t2 = new JTextField(15);
		JTextField t3 = new JTextField(15);
		JTextField t4 = new JTextField(15);

		add(l1);
		add(t1);

		add(l2);
		add(t2);

		add(l3);
		add(t3);

		add(l4);
		add(t4);

		JButton b1 = new JButton("Save");
		JButton b2 = new JButton("Show All Data");
		JButton b3 = new JButton("Reset");
		JButton b4 = new JButton("Exit");

		add(b1);
		add(b2);
		add(b3);
		add(b4);

		JLabel result = new JLabel("Result");
		add(result);
		
		DefaultTableModel model = new DefaultTableModel();
		JTable table = new JTable(model);
		JScrollPane jPane = new JScrollPane(table);
        add(jPane);

        // Add column names
        model.addColumn("id");
        model.addColumn("title");
        model.addColumn("author");
        model.addColumn("date");

		ActionListener al1 = new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {

				String s1 = t1.getText();
				String s2 = t2.getText();
				String s3 = t3.getText();
				String s4 = t4.getText();

				String insertDataQuery = String.format("INSERT INTO jdbcTable VALUES ('%d', '%s', '%s', '%s')", Integer.parseInt(s1), s2, s3, s4);

				System.out.println(insertDataQuery);
				
				Integer a = 0;
				try {
					a = myStmt.executeUpdate(insertDataQuery);
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 

				if (a > 0) {
					result.setText("Record Inserted");
				}

			}
		};
		b1.addActionListener(al1);

		ActionListener al2 = new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				String showTableQuery = "select * from jdbcTable";
				String data = "";
				try {
					myRs = myStmt.executeQuery(showTableQuery);
					while (myRs.next()) {
						data = data + myRs.getString("id") + ", " + myRs.getString("title") + ", "
								+ myRs.getString("author") + ", " + myRs.getString("date") + '\n';
					}
			        
						
				result.setText(data);
			        myRs.close();
					
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		};
		b2.addActionListener(al2);

		ActionListener al3 = new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				t1.setText("");
				t2.setText("");
				t3.setText("");
				t4.setText("");
			}
		};
		b3.addActionListener(al3);

		ActionListener al4 = new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent e) {
				System.exit(0);
			}
		};
		b4.addActionListener(al4);
	

		setVisible(true);
		setSize(1000, 500);
		setLayout(new FlowLayout());
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

	}
}

Thank you, with regards.

Leave a Reply

Your email address will not be published. Required fields are marked *