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;
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();
}
public ItemDetails() {
initComponents();
}
private void Clear() {
txtICode.setText("");
txtIType.setText("");
txtISize.setText("");
txtIName.setText("");
txtIColor.setText("");
txtIStock.setText("");
txtIPrice.setText("");
}
@SuppressWarnings("unchecked")
private void initComponents() {
pack();
}
private void btnUpdateActionPerformed(java.awt.event.ActionEvent evt) {
}
private void btnDeleteActionPerformed(java.awt.event.ActionEvent evt) {
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) {
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();
} catch (Exception e) {
JOptionPane.showMessageDialog(null, e);
}
}
<big>Modified Find button</big>
private void btnFindActionPerformed(java.awt.event.ActionEvent evt) {
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);
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);
}
}
}
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;
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?