JDBC Programs

 Here are examples based on JDBC.  I have used Eclipse IDE and MySQL database.

Video Tutorial is also available, here is the playlist link:

JDBC VIDEO TUTORIAL

Q1) Write a JDBC program that will create a Emp table. The emp table will have EmpNo, EmpName, Salary and Location.

CODE:

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Statement;

public class B1 {

public static void main(String[] args)

{

try

{

Class.forName(“com.mysql.cj.jdbc.Driver”);

System.out.println(“Driver Loaded.”);

Connection

con=DriverManager.getConnection(“jdbc:mysql://localhost:3306/BatchB2″,”root”,”720899025

1″);

System.out.println(“Connection Created!”);

Statement st=con.createStatement();

String query=”create table emp1(empno int,empname varchar(20),salary int,location

varchar(30))”;

st.execute(query);

System.out.println(“Table Created!”);

}

catch(Exception e)

{

e.printStackTrace();

}

}

}

2] Write a JDBC program that will allow users to insert & delete records to the Emp table.

import javax.swing.*;

import java.sql.*;

//instance variable

private JPanel contentPane;

private JTextField textField;

private JTextField textField_1;

private JTextField textField_2;

private JTextField textField_3;

java.sql.Statement st;

Connection con;

ResultSet rs;

String query;

PreparedStatement ps;

//constructor

try {

Class.forName(“com.mysql.cj.jdbc.Driver”);

System.out.println(“Driver Loaded.”);

con=DriverManager.getConnection(“jdbc:mysql://localhost:3306/Employee”,”root”,”123456″);

System.out.println(“COnnection Created”);

st=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UP DATABLE);

query= “select * from emp;”;

rs=st.executeQuery(query);

rs.next();

show(rs);

} catch (Exception ex) {

System.out.println(“ex”);

}

//insert button code

int r=Integer.parseInt(textField.getText());

String n=textField_1.getText();

int m=Integer.parseInt(textField.getText());

String o=textField_3.getText();

query=”insert into emp values(?,?,?,?)”;

try {

ps=con.prepareStatement(query);

ps.setInt(1, r);

ps.setString(2, n);

ps.setInt(3, m);

ps.setString(4, o);

int i=ps.executeUpdate();

JOptionPane.showMessageDialog(btnNewButton, i+”Record Added!!”); query=”select * from emp;”;

rs=st.executeQuery(query);

rs.last();

show(rs);

} catch (Exception e2) {

e2.printStackTrace();

}

//delete button code

String s=JOptionPane.showInputDialog(btnNewButton_1,”Enter emp no:”); int no=Integer.parseInt(s);

int result=JOptionPane.showConfirmDialog(btnNewButton_1,” Are you sure you want to delete”,”Delete?”,JOptionPane.YES_NO_OPTION);

if(result==JOptionPane.YES_OPTION) {

query=”delete from emp where empno=?”;

try {

ps=con.prepareStatement(query);

ps.setInt(1, no);

int i=ps.executeUpdate();

JOptionPane.showMessageDialog(btnNewButton_1, i+”Record Deleted!!”); query=”select * from emp;”;

rs=st.executeQuery(query);

rs.last();

show(rs);

} catch (Exception e2) {

e2.printStackTrace();

}

//show method

public void show(ResultSet rs) {

try {

textField.setText(rs.getString(1));

textField_1.setText(rs.getString(2));

textField_2.setText(rs.getString(3));

textField_3.setText(rs.getString(4));

}

 catch (Exception e) {

                                   System.out.println(“”);

}

}

            Output:-

3)

Write a JDBC program that will be used to navigate & update records to the Emp table.

Code:

import javax.swing.*;
import java.sql.*;

//instance variable name

private JPanel contentPane;

private JTextField t1;

private JTextField t2;

private JTextField t4;

private JTextField t3;

PreparedStatement ps;

String query;

ResultSet rs;

Statement st;

Connection con;

//constructor

try {

Class.forName(“com.mysql.cj.jdbc.Driver”);

con = DriverManager.getConnection(“jdbc:mysql://localhost/Employee”,”root”,”123456″);

System.out.println(“Connection created”);

st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);;

query = “select * from employee”;

rs = st.executeQuery(query);

}

catch(Exception e) {

System.out.println(e);

}

//insert button code

int num = Integer.parseInt(t1.getText());

String n = t2.getText();

int s = Integer.parseInt(t3.getText());

String l = t4.getText();

query = “insert into employee values(?,?,?,?)”;

try {

ps = con.prepareStatement(query);

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

ps.setInt(1, num);

ps.setString(2, n);

ps.setInt(3, s);

ps.setString(4, l);

int i = ps.executeUpdate();

JOptionPane.showMessageDialog(btnNewButton , i+”Record Updated “);

query = “select * from employee”;

rs = st.executeQuery(query);

String sql = “select * from employee”;

rs = st.executeQuery(sql);

while(rs.next()) {

System.out.print(rs.getInt(1)+” “);

System.out.print(rs.getString(2)+” “);

System.out.print(rs.getInt(3)+” “);

System.out.println(rs.getString(4));

}

}

catch(Exception e1) {

System.out.println(e1);

}

//clear button code

t1.setText(“”);

t2.setText(“”);

t3.setText(“”);

t4.setText(“”);

//first button code

try {

rs.first();

show(rs);

}

catch(Exception e2) {

System.out.println(e2);

}

//next button code

try {

if(rs.next() == true) {

show(rs);

}

else {

JOptionPane.showMessageDialog(btnNewButton_4, “You are at last record”);

rs.first();

show(rs);

}

}

catch(Exception e2) {

System.out.println(e2);

}

//last button code

try {

rs.last();

show(rs);

}

catch(Exception e3) {

System.out.println(e3);

}

//previous button code

try {

if(rs.previous() == true) {

show(rs);

}

else {

JOptionPane.showMessageDialog(btnNewButton_4, “You are at first record”);

rs.last();

show(rs);

}

}

catch(Exception e2) {

System.out.println(e2);

}

//show method code

public void show(ResultSet rs) {

try {

t1.setText(rs.getString(1));

t2.setText(rs.getString(2));

t3.setText(rs.getString(3));

t4.setText(rs.getString(4));

} catch (Exception e) {

System.out.println(“”);

}

       OUTPUT:

4) Write a program to accept username and password from user and verify records from login(uname,password) table if correct display login successful otherwise retry.

COMPLETE TUTORIAL VIDEO LINK