//console based application
import java.sql.*;
import java.util.*;
public class JDBCConnection {
public static void main(String[] args) {
String url="jdbc:mysql://localhost:3306/vit";
String user="root";
String pwd="";
Scanner sc=new Scanner(System.in);
System.out.println("Enter rollNo: ");
int rno = sc.nextInt();
System.out.println("Enter Sname: ");
String sname = sc.next();
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(url,user,pwd);
Statement st=con.createStatement();
String sqlInsert="insert into CSA1 values('"+rno+"','"+sname+"')";
st.executeUpdate(sqlInsert);
System.out.println("record inserted successfully");
String sql="select * from CSA1";
ResultSet rs= st.executeQuery(sql);
while(rs.next()) {
System.out.println("RollNo: "+rs.getInt(1));
System.out.println("Sname: "+rs.getString(2));
//
// String sqlupdate="update CSA1 set sname='ABC' where rno=10";
// st.executeUpdate(sqlupdate);
// System.out.println("record updated successfully");
//
//
// String sqldelete="delete from CSA1 where rno=1";
// st.executeUpdate(sqldelete);
// System.out.println("record deleted successfully");
//
con.close();
}
catch(Exception ex) {
System.out.println(ex);
//GUI
//Assign6.java
import java.awt.EventQueue;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import java.awt.Font;
import javax.swing.JTextField;
import javax.swing.JButton;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.awt.event.ActionEvent;
public class Assign6 {
private JFrame frame;
private JTextField textField_roll;
private JTextField textField_name;
private JTextField textField_class;
String driverName = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/v1?serverTimezone=UTC";
static final String USER = "root";
static final String PASS = "";
/**
* Launch the application.
*/
public static void main(String[] args) {
EventQueue.invokeLater(new Runnable() {
public void run() {
try {
Assign6 window = new Assign6();
window.frame.setVisible(true);
} catch (Exception e) {
e.printStackTrace();
}
});
/**
* Create the application.
*/
public Assign6() {
initialize();
/**
* Initialize the contents of the frame.
*/
private void initialize() {
frame = new JFrame();
frame.setBounds(100, 100, 450, 300);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.getContentPane().setLayout(null);
JLabel lblNewLabel_roll = new JLabel("Roll No");
lblNewLabel_roll.setFont(new Font("Tahoma", Font.BOLD, 12));
lblNewLabel_roll.setBounds(90, 56, 43, 14);
frame.getContentPane().add(lblNewLabel_roll);
JLabel lblNewLabel = new JLabel("Student Login");
lblNewLabel.setFont(new Font("Tahoma", Font.BOLD, 14));
lblNewLabel.setBounds(165, 11, 111, 35);
frame.getContentPane().add(lblNewLabel);
textField_roll = new JTextField();
textField_roll.setFont(new Font("Tahoma", Font.PLAIN, 12));
textField_roll.setBounds(165, 53, 166, 20);
frame.getContentPane().add(textField_roll);
textField_roll.setColumns(10);
JLabel lblNewLabel_name = new JLabel("Name: ");
lblNewLabel_name.setFont(new Font("Tahoma", Font.BOLD, 12));
lblNewLabel_name.setBounds(90, 93, 46, 14);
frame.getContentPane().add(lblNewLabel_name);
textField_name = new JTextField();
textField_name.setFont(new Font("Tahoma", Font.PLAIN, 12));
textField_name.setBounds(165, 90, 166, 20);
frame.getContentPane().add(textField_name);
textField_name.setColumns(10);
JLabel lblNewLabel_class = new JLabel("Class");
lblNewLabel_class.setFont(new Font("Tahoma", Font.BOLD, 12));
lblNewLabel_class.setBounds(90, 137, 46, 14);
frame.getContentPane().add(lblNewLabel_class);
textField_class = new JTextField();
textField_class.setBounds(165, 135, 166, 20);
frame.getContentPane().add(textField_class);
textField_class.setColumns(10);
JButton btnNewButton = new JButton("ADD");
btnNewButton.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
String roll = textField_roll.getText();
String sName = textField_name.getText();
String class1 = textField_class.getText();
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(DB_URL,USER,PASS);
String query = "INSERT INTO studdata values('" + roll + "','" + sName + "','" + class1 +
"')";
Statement sta = connection.createStatement();
int x = sta.executeUpdate(query);
JOptionPane.showMessageDialog(btnNewButton,"data for "+ sName + " added
sucessfully ");
connection.close();
} catch (Exception exception) {
exception.printStackTrace();
}
});
btnNewButton.setFont(new Font("Tahoma", Font.BOLD, 13));
btnNewButton.setBounds(28, 201, 89, 23);
frame.getContentPane().add(btnNewButton);
JButton btnDisplay = new JButton("DISPLAY");
btnDisplay.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
new Display().showTableData();
});
btnDisplay.setFont(new Font("Tahoma", Font.BOLD, 13));
btnDisplay.setBounds(127, 201, 109, 23);
frame.getContentPane().add(btnDisplay);
JButton btnUpdate = new JButton("UPDATE");
btnUpdate.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
new UpdateStud().updateTableData();
}
});
btnUpdate.setFont(new Font("Tahoma", Font.BOLD, 13));
btnUpdate.setBounds(246, 201, 89, 23);
frame.getContentPane().add(btnUpdate);
JButton btnDelete = new JButton("DELETE");
btnDelete.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
new DeleteStud().deleteTableData();
});
btnDelete.setFont(new Font("Tahoma", Font.BOLD, 13));
btnDelete.setBounds(345, 201, 89, 23);
frame.getContentPane().add(btnDelete);
//Display.java
import java.awt.BorderLayout;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
//import com.mysql.jdbc.PreparedStatement;
public class Display {
String driverName = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/v1?serverTimezone=UTC";
static final String USER = "root";
static final String PASS = "";
public void showTableData() {
JFrame frame1 = new JFrame("Database Result");
JTable table;
PreparedStatement pst;
String[] columnNames = {"Roll No","Student Name" ,"Class"};
frame1.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame1.setLayout(new BorderLayout());
DefaultTableModel model = new DefaultTableModel();
model.setColumnIdentifiers(columnNames);
table = new JTable();
table.setModel(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
table.setFillsViewportHeight(true);
JScrollPane scroll = new JScrollPane(table);
scroll.setHorizontalScrollBarPolicy(
JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
scroll.setVerticalScrollBarPolicy(
JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED);
try {
Class.forName(driverName);
Connection con = DriverManager.getConnection(DB_URL,USER,PASS);
String sql = "select * from studData";
Statement ps = con.createStatement();
ResultSet rs = ps.executeQuery(sql);
int i = 0;
while(rs.next())
int roll = rs.getInt(1);
String sname =rs.getString(2);
String class1 =rs.getString(3);
model.addRow(new Object[]{roll,sname,class1 });
i++;
if(i <1)
JOptionPane.showMessageDialog(null, "No Record Found","Error",
JOptionPane.ERROR_MESSAGE);
if(i ==1)
System.out.println(i+" Record Found");
else
System.out.println(i+" Records Found");
}
} catch (Exception ex) {
JOptionPane.showMessageDialog(null, ex.getMessage(), "Error",
JOptionPane.ERROR_MESSAGE);
frame1.add(scroll);
frame1.setVisible(true);
frame1.setSize(400,300);
//UpdatStud.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.swing.JOptionPane;
public class UpdateStud {
String driverName = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/v1?serverTimezone=UTC";
static final String USER = "root";
static final String PASS = "";
public void updateTableData() {
try {
Connection conn = DriverManager.getConnection(DB_URL,USER,PASS);
InputDialogInFrame frame = new InputDialogInFrame();
String m1 = JOptionPane.showInputDialog(frame, "Enter rollNo to update a record");
if(m1.isEmpty()){
frame.closeIt();
String m = JOptionPane.showInputDialog(frame, "New value for Student name");
if(m.isEmpty()){
frame.closeIt();
String usql = "update studData set sname=? WHERE roll =?";
java.sql.PreparedStatement pst = conn.prepareStatement(usql);
pst.setString(1,m);
pst.setString(2,m1);
pst.executeUpdate();
JOptionPane.showMessageDialog(null, "record updated");
} catch (SQLException e ) {
JOptionPane.showMessageDialog(null, e);
}}
//DeleteStud.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.swing.JOptionPane;
public class DeleteStud {
String driverName = "com.mysql.jdbc.Driver";
static final String DB_URL =
"jdbc:mysql://localhost:3306/v1?
serverTimezone=UTC";
static final String USER = "root";
static final String PASS = "";
public void deleteTableData() {
try {
Connection conn =
DriverManager.getConnection(DB_URL,USER,PASS);
InputDialogInFrame frame = new
InputDialogInFrame();
String m =
JOptionPane.showInputDialog(frame, "Enter RollNo
to delete a record");
if(m.isEmpty()){
frame.closeIt();
}
String usql = "delete from studData
WHERE roll =?";
java.sql.PreparedStatement pst =
conn.prepareStatement(usql);
pst.setString(1,m);
pst.executeUpdate();
JOptionPane.showMessageDialog(null,
"record deleted");
} catch (SQLException e ) {
JOptionPane.showMessageDialog(null, e);
}
}}
//InputDialogFrame.java
import java.awt.Color;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
public class InputDialogInFrame extends JFrame{
/**
*/
//private static final long serialVersionUID = 1L;
public InputDialogInFrame() {
getContentPane().setBackground(Color.DARK_GRAY);
setTitle("Input Dialog in Frame");
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setVisible(true);
setResizable(false);
setSize(400, 300);
getContentPane().setLayout(null);
}
void closeIt(){
this.getContentPane().setVisible(false);
this.dispose();
