//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();