JDBC Programs

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:

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:-

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.