MYSQL Commands

There are some common mysql commands used for database connectivity to any programming language.

1.       Creation of database :-  The following figure shows the mysql  command line client. We have to give all commands against the mysql prompt.

Command is :-

Create database <database name>

For example :- create database test;

Database name is a user defined word for example in the above figure, it is test.

2.       Use <database name>  :-  After the successful creation of database, we have to use it for the addition of table and data.

For example :- use test;

Now this test database is ready to use and we can create table or insert data here.

3.       Creation of table :- Now for the creation of a table we can use the create table command as:-

mysql> create table student  (rollno int,  name char(20),  marks float);

4.       Insertion of record in table :- when table is created we can add data by the insert command.

For the insertion of a record in the above table we can give command as follows :-

Insert into student values(1,’rakhee chhibber’,95);

You can use the same command after changing the values as many as record you want to insert in a table.

5.       Display of records :- to display all the records present in a table we can use SELECT command as:-

Select * from student;

6.       Deletion of a Record :- if you want to delete a record from a table we can use a delete record

Delete from student;  - delete all the records from student table

Delete from student where rollno=1; :- it will delete the record where rollno of the student is 1 but when we use this command in any language the we use it as

Delete from student where rollno=?.

7.       Deletion of a table :- to delete a table with structure use

DROP table Student;

               

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                   

}

Adding data using .CSV file in java

As we know, an excel file is a spreadsheet which represents our data in tabular form ( an intersection of row and column). We all add our database tables in our java programs using JDBD and ODBC ( a bridge) between Java (a high level language) and a RDBMS ( a Structured Query Language). If you want to know the database connectivity in java then go my blog JDBC-ODBC Connectivity using Java. But in this block I am going to educate you people, How we can use data of excel file in our java program.

To use an excel file in java first we have to convert it in a .CSV (comma separated values) file.

You all must be knowing the creation of excel file and entering data like

This is an excel spreadsheet. Now to convert it in .csv file, we have to save this file with an extension as .csv like as follows :-

After saving an excel file in .csv extension. We can open this file using simple notepad also which will show the comma separated file which further we can us in java by using bufferreader class. When you open your .csv file with an editor like notepad it will looks like :- 

 

Which is a simple comma separated flat file. Now write a program to read this text file in java,C, C++ any language of your choice.

I am including code in java for reading a simple .csv file.

Country.java

import java.io.BufferedReader;

import java.io.FileReader;

import java.io.IOException;

class CSVReader {

    public static void main(String[] args) {

        String csvFile = "country.csv";

        String line = "";

        String cvsSplitBy = ",";

        try (BufferedReader br = new BufferedReader(new FileReader(csvFile))) {

            while ((line = br.readLine()) != null) {

                // use comma as separator

                String[] country = line.split(cvsSplitBy);

                System.out.println("Country [code= " + country[0] + " , name=" + country[1] + "]");

            }

        } catch (IOException e) {

            e.printStackTrace();

        }     }

}

Now compile it an run this file and got the result like :-

I am sure this blog will help you.