Home Java JDBC Microsoft Sql server CRUD Step by Step

JDBC Microsoft Sql server CRUD Step by Step

8 min read
0
0
2,649

This JDBC will teach you how to do basic database functions that are CREATE RETIEVE UPDATE and DELETE. using microsoft sql server database. The INSERT, SELECT, UPDATE and DELETE statements can be used in any database system, because this is support by all relational database systems.

We will learn how to INSERT, SELECT, UPDATE and DELETE in database by writing code to manage the records table in the database named BookCrud. records table consist of following columns bookname,author,price.

Feature of projects

The system shall be able to record the book details : bookname,author,price.

The system  shall be able to retrieve the book details : bookname,author,price.

Then system shall be able to Edit and Delete the book details : bookname,author,price.

Learn how to make this System Step by step

Step 1: Download JDK  Click here  follow the steps and install it.

Step 2: Download an appropriate jdbc driver Click here in order to connect jdbc and microsoft sql server.

Step 3: Download Sqlserver  follow the steps and install it.

after installed Sqlserver in your computer. you shall be able to create datebase.

The first step Create the database named with “BookCrud“.

To connect the database please refer the following code. To create the method name connect (); paste the below code inside the Connect () method

    
    Connection con;
    PreparedStatement pst;
    
    public void Connect()
    {
        String connection = "jdbc:sqlserver://KOBINATH-PC; databaseName=BookCrud; user=sa; password=admin123";
        
        try {
            con = DriverManager.getConnection(connection);
        } catch (SQLException ex) {
            Logger.getLogger(BookCrud.class.getName()).log(Level.SEVERE, null, ex);
        } 
        
    }

Add Records

you can use the following code snippet to add the records in to database. paste the code inside the add button

        String bname = txtbname.getText();
        String author = txtauthor.getText();
        String price = txtprice.getText();
        
        
        try {
            pst = con.prepareStatement("insert into records(bookname,author,price)values(?,?,?)");
            pst.setString(1, bname);
            pst.setString(2, author);
            pst.setString(3, price);
            int k = pst.executeUpdate();
            
            if(k==1)
            {
                JOptionPane.showMessageDialog(this, "Record Adddeddd");
                txtbname.setText("");
                txtauthor.setText("");
                txtprice.setText("");
                txtbname.requestFocus();
                Booktable();
                
            }
            else
            {
                JOptionPane.showMessageDialog(this, "Record Failed");
            }
 
            
        } catch (SQLException ex) {
            Logger.getLogger(BookCrud.class.getName()).log(Level.SEVERE, null, ex);
        }

View Records

you can use the following code snippet to retrieve the data stored in the database and present it to users in a proper format. create a method Booktable() .paste the method inside the constructor of the class.when the form is loaded all the records will be shown on the jTable.

public void Booktable()
    {
        try {
            pst = con.prepareStatement("select * from records");
             ResultSet rs = pst.executeQuery();
             ResultSetMetaData RSM = rs.getMetaData();
             int c;
             c = RSM.getColumnCount();
             DefaultTableModel DF = (DefaultTableModel)jTable1.getModel();
             DF.setRowCount(0);
             
             while(rs.next())
             {
                 Vector v2 = new Vector();
             for(int i = 1; i<=c; i++)
             {
                 v2.add(rs.getString("id"));
                 v2.add(rs.getString("bookname"));
                 v2.add(rs.getString("author"));
                  v2.add(rs.getString("price"));
                 
             }
             DF.addRow(v2);
             }
             
        } catch (SQLException ex) {
            Logger.getLogger(BookCrud.class.getName()).log(Level.SEVERE, null, ex);
        }   
    }

Select the jTable right click-Events-Mouse-MouseClicked

paste the code inside the mouseClicked Listener

when you select record which you need to edit selected record will be displayed  on the relavent textfields

DefaultTableModel d1 = (DefaultTableModel)jTable1.getModel();
int SelectIndex = jTable1.getSelectedRow();
String bookno = d1.getValueAt(SelectIndex, 0).toString();
        
txtbname.setText(d1.getValueAt(SelectIndex, 1).toString());
txtauthor.setText(d1.getValueAt(SelectIndex, 2).toString());
txtprice.setText(d1.getValueAt(SelectIndex, 3).toString());
jButton1.setEnabled(false);

Edit Records

you can use the following code snippet to edit the records in to database. paste the code inside the add button

 private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) 
{                                         
         DefaultTableModel d1 = (DefaultTableModel)jTable1.getModel();
         int SelectIndex = jTable1.getSelectedRow();
        
         String bookno = d1.getValueAt(SelectIndex, 0).toString();
        
        
        String bname = txtbname.getText();
        String author = txtauthor.getText();
        String price = txtprice.getText();
        
        
        try {
            pst = con.prepareStatement("update records set bookname =? , author = ? , price = ? where id = ?");
            pst.setString(1, bname);
            pst.setString(2, author);
            pst.setString(3, price);
            pst.setString(4, bookno);
            
            int k = pst.executeUpdate();
            
            if(k==1)
            {
                JOptionPane.showMessageDialog(this, "Record Update");
                txtbname.setText("");
                txtauthor.setText("");
                txtprice.setText("");
                txtbname.requestFocus();
                Booktable();
                jButton1.setEnabled(true);
                
            }
            else
            {
                JOptionPane.showMessageDialog(this, "Record Failed");
            }
   
            
        } catch (SQLException ex) {
            Logger.getLogger(BookCrud.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

Delete Records

you can use the following code snippet to delete the records in to database. paste the code inside the add button

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

        DefaultTableModel d1 = (DefaultTableModel)jTable1.getModel();
        int SelectIndex = jTable1.getSelectedRow();
        String bookno = d1.getValueAt(SelectIndex, 0).toString();

        try {
            pst = con.prepareStatement("delete from records where id = ? ");
          
            pst.setString(1, bookno);
            
            int k = pst.executeUpdate();
            
            if(k==1)
            {
                JOptionPane.showMessageDialog(this, "Record Deletedddd");
                txtbname.setText("");
                txtauthor.setText("");
                txtprice.setText("");
                txtbname.requestFocus();
                Booktable();
                jButton1.setEnabled(true);
                
            }
            else
            {
                JOptionPane.showMessageDialog(this, "Record Failed");
            }
   
        } catch (SQLException ex) {
            Logger.getLogger(BookCrud.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

i have attached the video link below. which will do this tutorials step by step.

 

 

 

 

 

Load More Related Articles
Load More By admin
Load More In Java

Leave a Reply

Your email address will not be published. Required fields are marked *

Check Also

Laravel 11 CRUD Application

In this tutorial will teach Laravel 11 CRUD Application step by step. Laravel  11 CRUD App…