Data Base Connectivity of java application with MYSQL :-

As we know that there is a huge difference between java(programming language) and database which is actually a software for managing the data. This is an exactly same case of as Japanese wants to talk with a Chinese, they both don’t know language of each other so for the solution of this problem, we arrange a translator who knows both the language and help the persons. Now in case of database connectivity with java we have JDBC-ODBC connection which behaves like a bridge between the Java and Database and help both. This topic of database connectivity is a very important requirement for any application software to store data permanently which can be used in future.

Requirements for the Application :- The software and files we require for this database connectivity application are :-

1.       Netbeans 7 +
2.       Java Development Kit  7+
3.       Mysql latest version (you can download it from this link 
       https://dev.mysql.com/downloads/windows/installer/5.5.html)
4.    mysql-connector-java-5.1.45 (for the connectivity of java with mysql) – for download you can           click on this link - https://dev.mysql.com/downloads/connector/j/5.1.html
5.   rs2xml.jar (for collection of data from recordset to java tables) – click on this link -                 https://download4share.com/download/get/kfogUJTTba/rs2xml_jar.html


MYSQL Commands :- if you want to learn some basic commands of mysql used in this blog for data base connectivity in java. Mysql basic Commands


JDBC Cycle :- This is a 5- Step Process:-

1.       Registration of database driver

2.       Creation of Connection

3.       Creation of Statement

4.       Creation of Resultset.

5.       Closing of connection, statement and Resultset.

1.       Registration of Database Driver :- create a new java application in netbeans and give its name as database or what ever you want. Now check for the libraries folder from the project explorer, in this folder there will be a JDK 1.7 folder which would be having all your java libraries. To do the database connectivity we also require the mysql-coonector jar file as well as rs2xml.jar file for java tables so first of all you have to download these required .jar files form the above given links and add them into your project as follows:-

a.       Select project à libraries folder à right click on it and click on add jar/folder

b.      It will open a dialog box to give the path of the mysql connector file. Select the folder from your hard disk , where you had store this connector’s jar file.

c.       Also add rs2xml.jar file in the same library folder. The following figure will help yo.

After adding the libraries our library folder will be looks like the following figure:-

This is all about the registration process of DriverManager in netbeans for the java database connectivity.

2.       Creation of Connection :- Another step is for the creation of connection of the mysql to java. So add a JFrame in the project and design it as given in the following figure and add the coding for creation of connection, creation of statement and execution of statement.

First of all we have to import the packages of java for data base connectivity.

import java.sql.*;

import javax.swing.JOptionPane;

import  javax.swing.table.DefaultTableModel;

import net.proteanit.sql.DbUtils;

Now create a global object for connection so that we can use it in all the function like for the insertion, deletion, modification or search. :-

Connection con=null;

3.   Create the object of statement like PreparedSatement class object.

   For example :- PreparedStatement pst = null;

4.       Create the object of Resultset for storing the data :-

For example :-  ResultSet rs= null;

5.       Finally Close the object of connection to disconnect the database.

/*

 * To change this template, choose Tools | Templates

 * and open the template in the editor.

 */

package database;

import java.sql.*;

import javax.swing.JOptionPane;

import javax.swing.table.DefaultTableModel;

import net.proteanit.sql.DbUtils;

/**

 *

 * @author rakhee.chhibber

 */

public class NewJFrame extends javax.swing.JFrame {

    Connection con=null;

    PreparedStatement pst = null;

    ResultSet rs= null;

    public NewJFrame() {

        initComponents();

        showtabledata();

    }

  

Coding for Insert Button :-

 private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                        

       try

        {

    String sql = "insert into student"+"(rollno,name,marks)"+ "values (?,?,?)";

    con=DriverManager.getConnection("jdbc:mysql://localhost/temp","root","root");

    pst=con.prepareStatement(sql);

    pst.setString(1,jTextField1.getText());

    pst.setString(2,jTextField2.getText());

    pst.setString(3,jTextField3.getText());

    pst.executeUpdate();

    JOptionPane.showMessageDialog(null, "inserted successfully");

          }

        catch(Exception e)

        {

        JOptionPane.showMessageDialog(null, e);}

        showtabledata();    }                                       

Coding For Delete Button :-

    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {                                        

        // TODO add your handling code here:

         try

        {

    String sql = "delete from student where rollno=?";

    con=DriverManager.getConnection("jdbc:mysql://localhost/temp","root","root");

    pst=con.prepareStatement(sql);

    pst.setString(1,jTextField1.getText());

    pst.executeUpdate();

    JOptionPane.showMessageDialog(null, "Deleted successfully");

      }

        catch(Exception e)

        {

        JOptionPane.showMessageDialog(null, e);}

         showtabledata();

      }                                       

Coding for Modification Button :-

    private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {                                        

        // TODO add your handling code here:

         try
        {
    String sql = "update student set rollno=?, name=?, marks=? where rollno=?";
    con=DriverManager.getConnection("jdbc:mysql://localhost/temp","root","root");
    pst=con.prepareStatement(sql);
    pst.setString(1,jTextField1.getText());
    pst.setString(2, jTextField2.getText());
    pst.setString(3, jTextField3.getText());
    pst.setString(4, jTextField1.getText());
    pst.executeUpdate();
    JOptionPane.showMessageDialog(null, "Updated successfully");
    showtabledata();
      }

        catch(Exception e)

        {         JOptionPane.showMessageDialog(null, e);}

    }                                       

Coding for showtabledata() function :- for display all the data in the java table.

    public void showtabledata()

    {

        try

        { con=DriverManager.getConnection("jdbc:mysql://localhost/temp","root","root");

        String sql="select * from student";

        pst=con.prepareStatement(sql);

        rs=pst.executeQuery();

        jTable1.setModel(DbUtils.resultSetToTableModel(rs));

        }

        catch(Exception e){JOptionPane.showMessageDialog(null, e);}

      }

    private void jTextField1KeyReleased(java.awt.event.KeyEvent evt) {                                       

       

    }                                       

 Code for the Text1 lost Focus for the searching data :-

    private void jTextField1FocusLost(java.awt.event.FocusEvent evt) {                                     

        String sql="Select * from student where rollno=?";

        try

        {

            pst=con.prepareCall(sql);

            pst.setString(1, jTextField1.getText());

            rs=pst.executeQuery();

            if(rs.next())

            {

            jTextField2.setText(rs.getString("name"));

            jTextField3.setText(rs.getString("marks"));

            }

        }

        catch(Exception e)

        {

            JOptionPane.showMessageDialog(null, e);

        }

    }                                    

   

  

 /**

     * @param args the command line arguments

     */

    public static void main(String args[]) {

        /* Set the Nimbus look and feel */

        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">

        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.

         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html

         */

        try {

            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {

                if ("Nimbus".equals(info.getName())) {

                    javax.swing.UIManager.setLookAndFeel(info.getClassName());

                    break;

                }

            }

        } catch (ClassNotFoundException ex) {

            java.util.logging.Logger.getLogger(NewJFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);

        } catch (InstantiationException ex) {

            java.util.logging.Logger.getLogger(NewJFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);

        } catch (IllegalAccessException ex) {

            java.util.logging.Logger.getLogger(NewJFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);

        } catch (javax.swing.UnsupportedLookAndFeelException ex) {

            java.util.logging.Logger.getLogger(NewJFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);

        }

        //</editor-fold>

        /* Create and display the form */

        java.awt.EventQueue.invokeLater(new Runnable() {

            public void run() {

                new NewJFrame().setVisible(true);

            }

        });

    }

    // Variables declaration - do not modify                    

    private javax.swing.JButton jButton1;

    private javax.swing.JButton jButton2;

    private javax.swing.JButton jButton3;

    private javax.swing.JLabel jLabel1;

    private javax.swing.JLabel jLabel2;

    private javax.swing.JLabel jLabel3;

    private javax.swing.JScrollPane jScrollPane1;

    private javax.swing.JTable jTable1;

    private javax.swing.JTextField jTextField1;

    private javax.swing.JTextField jTextField2;

    private javax.swing.JTextField jTextField3;

    // End of variables declaration                   

}