Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I want to find one record that exists on a mysql database table using the following statement. The filteration is done using a textfiled. I am using Java & i don't use the JPA. Just simple mysql database connection with mysql connector.

At the below I have pasted the whole java application. The application has 2 classes.

Class 01 = The database connection

import java.sql.*;
import javax.swing.*;

public class DbConn {
    public Connection conn= null;
    public ResultSet rs = null;
    public PreparedStatement pst =null;
   
    public static Connection ConnDB(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
         Connection conn=DriverManager.getConnection("jdbc:mysql://localhost/naturetiles", "root","");
         
          JOptionPane.showMessageDialog(null,"Connection Established");
         return conn;
        }catch(Exception e){
           JOptionPane.showConfirmDialog(null, e); 
           
           return null;
        }
    }
}



Class 02 - The User Interface - Used to Insert, Update, Find, Delete records of the desired database.


Java
import java.sql.*;
import javax.swing.JOptionPane;

/**
 *
 * @author Roshan
 */
public class ItemDetails extends javax.swing.JInternalFrame {

    public DbConn DbConnect = new DbConn();
    public Connection conn = DbConn.ConnDB();
    ResultSet rs = null;
    PreparedStatement ps = null;
    public String sql;

    public static void main(String[] args) {
        DbConn.ConnDB();
    }

    /**
     * Creates new form ItemDetails
     */
    public ItemDetails() {
        initComponents();
    }

    private void Clear() {

        txtICode.setText("");
        txtIType.setText("");
        txtISize.setText("");
        txtIName.setText("");
        txtIColor.setText("");
        txtIStock.setText("");
        txtIPrice.setText("");

    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">
    private void initComponents() {

        pack();
  }// </editor-fold>

    private void btnUpdateActionPerformed(java.awt.event.ActionEvent evt) {                                          
        // TODO add your handling code here:
    }                                         

    private void btnDeleteActionPerformed(java.awt.event.ActionEvent evt) {                                          
        // TODO add your handling code here:
        
        
        sql = "DELETE FROM ITEMDETAILS WHERE ItemCode = txtICode.text";
        try{
            
            ps = conn.prepareStatement(sql);
            ps.execute();
            JOptionPane.showMessageDialog(null, "Deleted Successfully !" );
            
            Clear();
            
        }catch(Exception e){
        
            JOptionPane.showMessageDialog(null, e);
        
        }
        
    }                                         

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

        Clear();

    }                                        

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

        //conn = DbConn.ConnDB();

        sql = "INSERT INTO ITEMDETAILS("
                + "ItemCode,"
                + "ItemType,"
                + "ItemSize,"
                + "ItemName,"
                + "ItemColour,"
                + "InStock,"
                + "ItemPrice)" + "VALUES(" + txtICode.getText() + ",'" + txtIType.getText() + "','" + txtISize.getText() + "','" + txtIName.getText() + "','" + txtIColor.getText() + "','" + txtIStock.getText() + "','" + txtIPrice.getText() + "')";


        try {
            ps = conn.prepareStatement(sql);
            ps.execute();
            JOptionPane.showMessageDialog(null, "Saved Successfully !");

            //Clear textfields.
            Clear();

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

    } 

<big>Modified Find button</big>
    private void btnFindActionPerformed(java.awt.event.ActionEvent evt) {                                        
        // TODO add your handling code here:
        
        Connection dbConnection = null;
		PreparedStatement preparedStatement = null;
                
                String temp = txtSupNo.getText();
 
		String selectSQL;
        selectSQL = "Select * from Purchasing WHERE SupNo= '"+temp+"'";
 
		try {
			dbConnection = DbConn.ConnDB();
			preparedStatement = dbConnection.prepareStatement(selectSQL);
			preparedStatement.setInt(1, 1);
 
			// execute select SQL stetement
			ResultSet rs = preparedStatement.executeQuery();
 
			while (rs.next()) {
 
				String SupNo = rs.getString("SupNo");
				String SupName = rs.getString("SupName");
 
				txtSupNo.setText(SupNo);
                                txtSupName.setText(SupName);
 
			}
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		} finally {
 
			if (preparedStatement != null) {
                        try {
                            preparedStatement.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(Purchasing.class.getName()).log(Level.SEVERE, null, ex);
                        }
			}
 
			if (dbConnection != null) {
                        try {
                            dbConnection.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(Purchasing.class.getName()).log(Level.SEVERE, null, ex);
                        }
			}
 
		}                                         


    // Variables declaration - do not modify
    private javax.swing.JButton btnClear;
    private javax.swing.JButton btnDelete;
    private javax.swing.JButton btnUpdate;
    private javax.swing.JButton cmdAddData;
    private javax.swing.JButton cmdFind;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JLabel jLabel7;
    private javax.swing.JLabel jLabel8;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JPanel jPanel4;
    private javax.swing.JTextField txtICode;
    private javax.swing.JTextField txtIColor;
    private javax.swing.JTextField txtIName;
    private javax.swing.JTextField txtIPrice;
    private javax.swing.JTextField txtISize;
    private javax.swing.JTextField txtIStock;
    private javax.swing.JTextField txtIType;
    // End of variables declaration



My problem Is how to find an specific record that according to a value that entered to a text field, delete an specific record according to a value that entered to a text field.

Thank You
ChiranSJ

After got the result I want to pass the rest of the fields in that specific record to other textfields in my UI that created using SWING.

Please someone help me to do this

Thank You!

Chiransj

PS:

I have developed some code for the Find button with the help of a buddy in the codeproject but when I have run the application and click the find button and in the output window I got an error i.e Parameter index out of range (1 > number of parameters, which is 0)". Then what should I do?
Posted
Updated 13-Jan-13 3:51am
v5
Comments
Jibesh 6-Jan-13 2:57am    
where you really stuck with? this is just some code dump.
Chiranthaka Sampath 6-Jan-13 3:42am    
When the sql command executed the result should pass to the corresponding text fields that part is the problem!
Jibesh 6-Jan-13 3:45am    
thats doesnt answer my question yet.
Are you having trouble with reading query ? dont know how to connect to DB? dont know how get the sql result?
we cant read your mind so to get the best answer you must provide what ever the details you have in details
Chiranthaka Sampath 6-Jan-13 6:10am    
Ok pal I have posted the total application as the Solution 1 & I have stated what really I want

Thank You
ChiranSJ
Chiranthaka Sampath 6-Jan-13 3:23am    
When the sql command executed the result should pass to the corresponding text fields that part is the problem!

1 solution

Java
String item = txtICode.getText();


To Delete :

Java
sql = "DELETE FROM ITEMDETAILS WHERE ItemCode = '"+item+"'";


To Retrive
Java
sql = "SELECT * FROM ITEMDETAILS WHERE ItemCode = '"+item+"'";
 
Share this answer
 
Comments
Richard MacCutchan 9-Jan-13 5:02am    
Firstly, you should use proper parameterised queries for SQL commands.
Secondly, please read the question.
Shubhashish_Mandal 9-Jan-13 9:18am    
Here is the Question
"My problem Is how to find an specific record that according to a value that entered to a text field, delete an specific record according to a value that entered to a text field."

And I know about parameterised queries for SQL commands. But look at the posted code. I just modify the existing code.

Chiranthaka Sampath 10-Jan-13 21:09pm    
Ok pal now I have deleted the record successfully but now I want to pass the retrieved fields to the text boxes. At there do I have to use encapsulated fields of the prepared statement, statement & resultset? Then how to use them to pass the rest of the data of the record!
Shubhashish_Mandal 11-Jan-13 4:31am    
PreparedStatement is the best choice over Statement.I suggest you to read any jdbc tutorial before start working.There are lots of tutorial available in the web.
You may try this example
http://www.mkyong.com/jdbc/jdbc-preparestatement-example-select-list-of-the-records/
Chiranthaka Sampath 12-Jan-13 1:51am    
I tried that tutorial & did the following coding set but it generated an error "Parameter index out of range (1 > number of parameters, which is 0)". I don't know how to fix this matter.

private void btnFindActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:

Connection dbConnection = null;
PreparedStatement preparedStatement = null;

String temp = txtSupNo.getText();

String selectSQL;
selectSQL = "Select * from Purchasing WHERE SupNo= '"+temp+"'";

try {
dbConnection = DbConn.ConnDB();
preparedStatement = dbConnection.prepareStatement(selectSQL);
preparedStatement.setInt(1, 1);

// execute select SQL stetement
ResultSet rs = preparedStatement.executeQuery();

while (rs.next()) {

String SupNo = rs.getString("SupNo");
String SupName = rs.getString("SupName");

txtSupNo.setText(SupNo);
txtSupName.setText(SupName);

}

} catch (SQLException e) {

System.out.println(e.getMessage());

} finally {

if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException ex) {
Logger.getLogger(Purchasing.class.getName()).log(Level.SEVERE, null, ex);
}
}

if (dbConnection != null) {
try {
dbConnection.close();
} catch (SQLException ex) {
Logger.getLogger(Purchasing.class.getName()).log(Level.SEVERE, null, ex);
}
}

}

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900