Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Java Swing MySQL
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.
 

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 5-Jan-13 21:05pm
Edited 13-Jan-13 4:51am
v5
Comments
jibesh at 6-Jan-13 2:57am
   
where you really stuck with? this is just some code dump.
Chiranthaka Sampath at 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 at 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 at 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 at 6-Jan-13 3:23am
   
When the sql command executed the result should pass to the corresponding text fields that part is the problem!
Richard MacCutchan at 8-Jan-13 12:03pm
   
Just take the data you receive from the database and set it into the text field you need. What is the difficulty with this?
Chiranthaka Sampath at 8-Jan-13 16:06pm
   
Then how am I able to get the data from the database & pass it into text fields? Pal
Richard MacCutchan at 8-Jan-13 16:33pm
   
Use an SQL command to read your data into the program and use the object methods to add particular items to your text fields, the same as if you were setting them with any other form of data.
 
Maybe your question needs to be made clearer - I don't quite understand what your difficulty is.
Chiranthaka Sampath at 8-Jan-13 16:54pm
   
I have done this to some extend but cannot complete it. The work I have done is as below
 
private void cmdFindActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
 
public Connection conn= null;
public ResultSet rs = null;
public PreparedStatement pst =null;

sql = "SELECT * FROM ITEMDETAILS WHERE ItemCode = 'txtICode.text'";

}
 
After executing the sql command what should I do and how am I able to pass the data to the textfields?
Richard MacCutchan at 8-Jan-13 17:02pm
   
See this tutorial for details of how to extract the data from your database. Once you have retrieved your records you just need to split the data into the items you require and then set each textbox with the appropriate items.
Chiranthaka Sampath at 8-Jan-13 17:29pm
   
The how am I able to split them and set them to textfields! That part cannot understandable!
Richard MacCutchan at 8-Jan-13 17:41pm
   
It depends on the structure of your data. If it is returned as individual items, then you just set each item (suitably converted to text) into the textfield. If it comes back as a single string then use one of the string methods to split it into its constituent parts. Spend some time reading the link I gave you; you really need to understand the mechanics of your database and how to manage its contents in order to make your program work effectively.
Chiranthaka Sampath at 8-Jan-13 17:46pm
   
I have read that tutorial pal and I already know about that but my problem is how am I able to get those fields and split them and pass that values to the corresponding textfields. I just need some sample source code only! Not the whole program!
Richard MacCutchan at 8-Jan-13 17:58pm
   
Well since I have no idea what format your data is in I cannot write the code for you. If your database query returns you a field (e.g. CustomerName) as a text string then I guess you just use setText() method call to set it, something like:
txtCustomer.setText(CustomerName);
What more do you need to know?
Chiranthaka Sampath at 8-Jan-13 18:07pm
   
This CustomerName is one of the fields in the database table right! But When I tried that it gives me an error 'Cannot find simple'. After executing the PreparedStatement (ps) & the SQL statement how am I able to get those data of the other fields of the database table to the textfields? That's where I have strucked!
Chiranthaka Sampath at 8-Jan-13 18:09pm
   
I tried it with my database table's fields but it does not work!!
Richard MacCutchan at 8-Jan-13 18:14pm
   
"does not work" is not a known diagnostic message. Please show your actual code and the complete error message you see.
Chiranthaka Sampath at 8-Jan-13 18:36pm
   
Ok the Code
 
private void cmdFindActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:

sql = "SELECT * FROM ITEMDETAILS WHERE ItemCode = 'txtICode.text'";

try{
ps=conn.prepareStatement(sql);
ps.execute();
txtIName.setText(ItemName);
} catch (Exception e){

}
 
Error Message
 
cannot find symbol
symbol: variable ItemName
location: class ItemDetails
Richard MacCutchan at 8-Jan-13 19:20pm
   
You obviously did not read the tutorial that I gave you the link for as your SQL is incomplete. You need to use a command of the form ResultSet rs = stmt.executeQuery(sql); and then extract the fields from the result set returned from the query. I have no idea what this code is but since it does not even compile then it is not going to be of much use. Where is the field ItemName even defined?
Chiranthaka Sampath at 8-Jan-13 20:57pm
   
Ok then I will try that!
Richard MacCutchan at 8-Jan-13 12:05pm
   
You should also use proper parameterised SQL commands. Your use of concatenated strings leaves you open to SQL injection and the destruction or corruption of your database.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

String item = txtICode.getText();
 
To Delete :
 
sql = "DELETE FROM ITEMDETAILS WHERE ItemCode = '"+item+"'";
 
To Retrive
sql = "SELECT * FROM ITEMDETAILS WHERE ItemCode = '"+item+"'";
  Permalink  
Comments
Richard MacCutchan at 9-Jan-13 5:02am
   
Firstly, you should use proper parameterised queries for SQL commands.
Secondly, please read the question.
Shubhashish_Mandal at 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 at 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 at 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 at 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);
}
}

}
Chiranthaka Sampath at 13-Jan-13 9:12am
   
I have used that tutorial & tried the following code but getting an error i.e. Parameter index out of range (1 > number of parameters, which is 0).
 
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);
}
}

}
Richard MacCutchan at 13-Jan-13 9:23am
   
Please do not expect people to guess where the error occurs. Update your original question with this information and show exactly where the error occurs and what values you are using.
Chiranthaka Sampath at 13-Jan-13 9:57am
   
Ok pal I have paste the modified code for the find button and stated it as the "Modified find button". Now could you please tell me what I have done wrong! Thank you
Richard MacCutchan at 13-Jan-13 10:06am
   
Once again you have ignored my comments and just dumped some code and expect people to figure out what is going on. As I said above: show exactly where the error occurs and what values you are using.. We cannot be expected to guess what is happening in your code.
Chiranthaka Sampath at 13-Jan-13 10:12am
   
Ok pal I have paste the modified code for the Find button and I have stated it as the Modified Find button. So could you please tell me what is the problem with the code!
Chiranthaka Sampath at 13-Jan-13 10:12am
   
Ok pal I have paste the modified code for the Find button and I have stated it as the Modified Find button. So could you please tell me what is the problem with the code!
Richard MacCutchan at 13-Jan-13 10:43am
   
I am guessing (as you still have not explained) that the error occurs on the statement preparedStatement.setInt(1, 1);. What exactly is this statement supposed to be there for, since you do not have any IN parameters on your Select statement. Have you checked the documentation for PreparedStatement?
Chiranthaka Sampath at 12-Jan-13 1:48am
   
I have used that tutorial & tried the following code but getting an error i.e. Parameter index out of range (1 > number of parameters, which is 0).
 
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);
}
}

}
Shubhashish_Mandal at 14-Jan-13 1:53am
   
When you are using preparedstatement rather than Statement then implemention is bit difference.
When using Statement :
selectSQL = "Select * from Purchasing WHERE SupNo= '"+temp+"'";
 
When using Statement :
selectSQL = "Select * from Purchasing WHERE SupNo= ? "; // ? is known as place holder
 
And update this line
 
preparedStatement.setInt(1, Integer.valueof(temp));
 
If the sql query does not contain any place holder and someone try to set the value against the place holder it thorw the above exception.
 
And one more thing without knowing anything you try to do experiment. This is the place you can ask your problem instead of expecting that someone doing your homework. So read any tutorial before doing anything.
 
<pre lang="java"></pre>
Chiranthaka Sampath at 14-Jan-13 5:56am
   
Ok pal I will take your advice! Thanks anyway!

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

  Print Answers RSS
0 George Jonsson 175
1 Kornfeld Eliyahu Peter 169
2 Zoltán Zörgő 139
3 PIEBALDconsult 130
4 OriginalGriff 120
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,342


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 13 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100