By on

 JAVA MYSQL CRUDS

The full meaning of CRUDS is:

C - CREATE (INSERT RECORD)

R - READ (VIEW RECORD)

U - UPDATE (EDIT RECORD)

D - DELETE (ERASE RECORD)

S - SEARCH (LOOK FOR RECORD)

How to perform CRUD operation with JAVA and XAMPP

We will create a simple Song Management System to help manage the details of artist name, song name and album name. You can add more functionalities

Required tools

You need the following on your machine:

- Netbeans (you can use other IDE of your choice)
- XAMPP (Cross platform, Apache, MySQL, PHP, and Perl )
- mysql-connector-j-8.0.32.jar
- rs2xml.jar

TIP: if you don't have the above, download and install them from Google

PHASE 1 - Launch NETBEANS and Create Java Application

Click file -> new project ->  select java -> select java application -> click next button -> give it a name - Song-mgt -> click finish button


PHASE 2 - Create database and table in XAMPP

Launch XAMPP panel 

-> click start button for Apache and Mysql 

-> click admin (phpmyadmin will open in your default web browser) 

-> click new 

-> give a name for your database -song-db 

-> click create button 

-> give a name for your table -> tblsong -> click save button

alternatively to visit phpmyadmin -> type localhost/phpmyadmin in the browser url and press the enter key 

image of table fields in the database


image of data entered from the java application into the database



PHASE 3 - Add jar files (at the point of writing this blog)

-> download and add mysql-connector-j-8.0.32.jar

right click libraries -> select add jar/folder -> locate where it on your machine -> click it -> click open

-> download and add rs2xml.jar to manage Jtable


PHASE 4 - Create a dedicated database file



right click the package name - song.mgt -> new -> java class -> give it a name - dbcon -> click finish button

paste the code below into the dbcon.java

//package song.mgt;

import java.sql.*;

public class dbcon {

        public static Connection mycon(){

        try{

            //load and reigster database driver to connect java application to database

            String url = "com.mysql.cj.jdbc.Driver";

            Class.forName(url);            

            //create variable for the connection

            String dbServer = "jdbc:mysql://localhost:3306/song-db";

            String dbPass = "root";

            String dbUser = "";

            //create a connection between java application and database            

            Connection con = DriverManager.getConnection(dbServer, dbPass, dbUser);

            return con;

            

        }catch(Exception ex){

            System.out.println(ex);

        }

        return null;

    }    

}


PHASE 5 - Design your UI (User Interface) for Songpage

right click the package name - song.mgt -> new -> JFrame Form -> give it a name - Songpage -> click finish button


Design Process

Drag and drop first JPanel on the left -> another JPanel on the right -> drop four buttons and edit them on the left panel -> drop 4 labels and edit on the right panel -> drop 3 text boxes on the right panel -> drop one button on the right panel

if you know the environment of NETBEANS very well, you can skip 5.1 - 5.6 and jump to 5.7

5.1    to add icon to the button

- download the icon from Google
- if the size is too big, click the image -> open with paint -> click resize -> give it 15-20 -> save it
- right click the button in Design view Netbeans -> properties -> 
- click the three dots box in icon attributes -> select the three dots box in external image 
- search for the image on your computer -> select the image -> click open -> ok -> close

TIP: personally i drop all the images inside the folder of my project

5.2    to centralize the JFrame on the screen:

 right click the jframe in design view -> properties -> select code tab -> check Generate center

5.3    to give title to the JFrame 

click the jframe -> right click the jframe in design view -> properties -> click inside the textbox of title attribute -> type a name - Song Management System -> click close

to give unique size to the JFrame and other frames to be created -> right click the jframe in design view -> properties -> click inside the textbox of preferredSize attribute -> type a value -> click close
Aliter: in the Design view -> ensure you click the JFrame -> take the mouse pointer to the edge on it turns horizontal -> double click -> type a value for width, height -> click ok
Aliter: click the jframe and drag to any preferred size with the mouse

to divide the frame -> drag and drop selector from the palette -> resize it from left to right on the JPanel

5.4    to edit any java component, 

for example JLabel, you can double click or press F2 on the keyboard, type "Artist Name" and press enter key
for example JTextField, press F2, press backspace key

5.5    to rename any java component variable

for example to give a variable name to a JTextField, in the palette pane, click code tab -> in the variable name, type txtartist -> press enter key (use same approach to other JTextFields)
Aliter: right click the component -> select Change variable name -> type a name -> click ok

5.6    to give color, font size, backgound to java component

for example jlabel, in the properties bar, click the three dot box of foreground attribute
for example jlabel, in the properties bar, click the three dot box of font attribute
for example JButton, in the properties bar, click the three dot box of background attribute

5.7    Add Insert Operation Functionalities to Save Button

Right click save button -> Events -> Action -> actionPerformed
Aliter: double click save button

TIP: You can use preparedStatement() or createStatement() method to implement your CRUD. But in this blog i will use

TIP: Immediately after the package name, type
import java.sql.*;


TIP: NETBEANS show a bulb on the left sometimes, click it to add now required import functionalities or other required operations to clear errors from your code


paste the code below into the save button

//button to insert and save data into the database
        if(txtartist.getText().isEmpty()||
                txtsong.getText().isEmpty()||
                txtalbum.getText().isEmpty()){
            JOptionPane.showMessageDialog(this, "Fields cannot be empty");
            
        }else{
            //variables can be here
            String artist, song, album;
            artist = txtartist.getText();
            song = txtsong.getText();
            album = txtalbum.getText();
            
            try{
                //variables can also be here
                Statement pst = dbcon.mycon().createStatement();
                String sql = "insert into tblsong(artistname, songname, albumname) values('"+artist+"','"+song+"','"+album+"')";
                pst.executeUpdate(sql);
                JOptionPane.showMessageDialog(this, "Records Added Successfully");
                
                //to manage clearing alternatively
                setVisible(false);
                new Songpage().setVisible(true);
                
                //close the connection
                pst.close();
                
            }catch(Exception ex){
                JOptionPane.showMessageDialog(this, "Error");
            }
        }


PHASE 6 - Design your UI (User Interface) for Updatepage

The Updatepage.java will handle the following functionalities: search and update, search and delete

right click the package name - song.mgt -> new -> JFrame Form -> give it a name - Updatepage -> click finish button -> then drop the required components as needed from scratch

Alternatively,
ALITER-1: you can create a copy of the existing UI design, then do some minor changes
for example, to reuse the interface of Songpage.java

- right click Songpage.java
- select copy
- right click package name (song.mgt)
- select paste -> refactor copy
- give it a name of your choice - Updatepage
- click refactor copy

ALITER-2: from the new JFrame form, give it a preferredSize, click the left panel in Songpage.java, paste it inside the frame of Updatepage.java, then drag and drop the second panel on the frame, drag and drop the required components such as label, textfield, button



TIP: Immediately after the package name, type
import java.sql.*;

Add Search Operation Functionalities to Search Button

Right click search button -> Events -> Action -> actionPerformed
Aliter: double click search button

paste the code below into the search button


//button to search data from the database
        if(txtsearch.getText().isEmpty()){
            JOptionPane.showMessageDialog(this, "Field cannot be empty");
            
        }else{
            //variables can be here
            String sid = txtsearch.getText();
            
            try{
                //variables can also be here
                Statement pst = dbcon.mycon().createStatement();
                String sql = "select * from tblsong where id ='"+sid+"'";
                ResultSet rs = pst.executeQuery(sql);
                
                if(rs.next()){
                 
                    // txtid.setText(rs.getString(1));
                    //instead of using index of the database field as above 1,2 ,3,4, 
                    //you can type the database field name directly
                  txtid.setText(rs.getString("id"));
                  txtartist.setText(rs.getString("artistname"));
                  txtsong.setText(rs.getString("songname"));
                  txtalbum.setText(rs.getString("albumname"));                 
                  //make not editable
                  txtid.setEditable(false);                            
                  }
                //if matric number does not exist in the database
                else{
                    JOptionPane.showMessageDialog(null, "ID does not exist in the database");
                    setVisible(false);
                    new Updatepage().setVisible(true);
                }                
                
                //close the connection
                rs.close();
                pst.close();
                
            }catch(Exception ex){
                JOptionPane.showMessageDialog(this, "Error");
            }
        }

Add Update Operation Functionalities to Update Button

Right click update button -> Events -> Action -> actionPerformed
Aliter: double click update button

paste the code below into the update button

//button to update data into the database
        if(txtsearch.getText().isEmpty()){
            JOptionPane.showMessageDialog(this, "Field cannot be empty");
            
        }else{
            //variables can be here
            String sid, artist, song, album;
            sid = txtid.getText();
            artist = txtartist.getText();
            song = txtsong.getText();
            album = txtalbum.getText();
            
            try{
                //variables can also be here
                Statement pst = dbcon.mycon().createStatement();
                String sql = "update tblsong set id='"+sid+"',artistname='"+artist+"',songname='"+song+"',albumname='"+album+"' where id='"+sid+"' ";
                pst.executeUpdate(sql);
                JOptionPane.showMessageDialog(this, "Record Updated Successfully");
                
                //to manage clearing alternatively
                setVisible(false);
                new Updatepage().setVisible(true);
                          
                //close the connection
                pst.close();
                
            }catch(Exception ex){
                JOptionPane.showMessageDialog(this, "Something went wrong");
                setVisible(false);
                new Updatepage().setVisible(true);
            }
        }


Add Delete Operation Functionalities to Delete Button

Right click delete button -> Events -> Action -> actionPerformed
Aliter: double click delete button

paste the code below into the delete button

//button to delete data in the database
        String sid = txtid.getText();
        try{
            Statement pst = dbcon.mycon().createStatement();
            String sql = "delete from tblsong where id ='"+sid+"'";
            pst.executeUpdate(sql);
            JOptionPane.showMessageDialog(this, "Record Deleted Successfully");
            
        }catch(Exception ex){
            JOptionPane.showMessageDialog(this, "Something went wrong");
        }

PHASE 7 - Design your UI (User Interface) for Viewpage

right click the package name - song.mgt -> new -> JFrame Form -> give it a name - Viewpage -> click finish button -> then drop the required components as needed from scratch. OR follow the aliter-1 or aliter-2 of phase 6.

The Viewpage.java will handle the following functionalities: view list of song details





To view data in the table, this is where we will utilize the rs2xml.jar (benefits: it automatically adjust the table when new data is added to the database, and also add the field names as it is in the database)

TIP: Immediately after the package name, type
import net.proteanit.sql.DbUtils;


paste the code below into the formComponentShown

- drop the table directly on the frame (rename the table as tblview)
- click the frame -> right click the Frame -> Events -> Components -> ComponentShown

//code to send and view data on the table from the database
        try{
                Statement pst = dbcon.mycon().createStatement();
                ResultSet rs = pst.executeQuery("select * from tblsong");
                tblview.setModel(DbUtils.resultSetToTableModel(rs));                
                              
        }catch(Exception ex){
            JOptionPane.showMessageDialog(this, "Connection Error");
        }
        tblview.setEnabled(false);

To add scrollbar to your table when the data exceed the default range

click the table -> right click  -> select enclose in -> scroll pane

To remove the visible rows in the default table

click the table -> right click the topmost part (where the titles are) -> Table Contents -> click the row tab -> in the count box reduce the number to zero -> click close

To Navigate among Manage, Update Details, View Details, Log Out Buttons

The activity below will be done to each JFrame Form Page,

In Songpage.java

- Double click Manage button, paste the code below

setVisible(false);
new Songpage().setVisible(true);

- Double click Update Details button, paste the code below
setVisible(false);
new Updatepage().setVisible(true);

- Double click Update Details button, paste the code below
setVisible(false);
new Viewpage().setVisible(true);

Repeat the above in Updatepage.java, Viewpage.java

TODO Log Out Button, create another JFrame (call it indexpage), then redirect the button to the indexpage.java, add a button in the JFrame that will take user to Songpage.java (it will automatically allow the user to navigate other menus)

Comments

Post a Comment

Popular posts from this blog

By on
Image
   DATA STRUCTURE WITH C++ PROGRAMMING USING STACK PREREQUISITES TO IMPLEMENTING STACK IN C++ We will need to understand how to use some of the constructs in C++ that makes up the implementation of stack in C++ against. And this concepts will also help you in developing other codes and to debug or understand the C++ codes in a company's code bank or to interpret a boiler plate code (a ready made code). Some of these constructs or concepts are: 1. sizeof() 2. Function 3. Pointer 4. Function Pointer 5. Arrow operator 6. malloc() 7. typedef 8. struct PHASE 1: sizeof() It is used to determine the size of variables used in a program, it can determine the size of primitive (or predefined) datatype too and for any user-defined datatypes. It is an inbuilt keyword and a compile-time operator. syntax-1 using variable sizeof(variableName); syntax-2 using primitive datatype sizeof(datatype); //code implementation #include<iostream> using namespace std; int main(){ //declare v...
Read more
By on
Image
 ADDING FILES TO YOUR GITHUB ACCOUNT & Inspect Element & Export SQL file from XAMPP sign into your github account create a new repository give it a name of your choice click create repository ensure you are in the project folder directory for quick access; copy the folder on the desktop open git bash terminal cd desktop cd folder-name (cd whitepace-academy) HANDLE few git commands type:: git init (a git folder is created in your project folder) type:: git add . (add the period sign) type:: git config --global user.email "ajalabamise@gmail.com" type:: git config --global user.name "ajalabamise" type:: git commit -m "initial commit"  (it shows all your folder files) CONNECT your local github folder in your github repository copy and paste the code below from your github repo into your git terminal and press enter key git remote add origin https://github.com/ajalabamise/my-web-dev-tutorial.git UPLOAD the files to github repo git push -u or...
Read more
By on
 DATA STRUCTURE WITH C++ PROGRAMMING USING GRAPH A graph contains interconnected nodes referred to as vertices with a set of edges. C++ does not have inbuilt function to handle graph. However for implementation, we can use inbuilt STL of list and vector.
Read more