Hot questions for Using JTextField in jdbc

Question:

This is a code for a login form where i used JDBC connection the code has no error but when i run it it always goes to else statement

    Connection con;
    Statement st;
    ResultSet rs;
    try{
      Class.forName("java.sql.Driver");
      con=DriverManager.getConnection   ("jdbc:mysql://localhost/database","root","password");
      st=con.createStatement();
      rs=st.executeQuery("select * from users;");
      while(rs.next()){
                String userID=rs.getString("userID");
                String password=rs.getString("password");

                if(userID.equals(txtuserID.getText())
                    && (password.equals(txtpassword.getPassword())) {
                // ***HERE IS MY PROBLEM I WANT TO CHECK IF WHATS IN THE 
                // TEXT FIELD OR PASSWORD FIELD IS THE SAME FROM MySQL***
                    JOptionPane.showMessageDialog(null,"you have logged in");
                    new MainForm().setVisible(true);
                } else {
                    JOptionPane.showMessageDialog(null,"Incorrect username and password");
                }
     }
  } catch(Exception e) {
     JOptionPane.showMessageDialog(null,"Error in Connectivity: " +e.getMessage());
  }  

Answer:

The JPasswordField getPassword() method returns char[]. Convert to String before comparison : ( actually construct a new String with those chars ...)

if(userID.equals(txtuserID.getText()) &&
  (password.equals(new String((txtpassword.getPassword()))) {
}

Question:

I want to display some questions and their answers. So to test that I just tried to display the question number and the question but there's

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

error and I don't know what to do next. 1. I want to display next record with the next button. 2. seteditable isn't working (giving error)

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
String qid=jTextField1.setEditable(false);
String qid=jTextField1.getText();
String ques=jTextField2.getText();

 try{       
            Class.forName("com.mysql.jdbc.Driver");
            Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1","root","");
            PreparedStatement ps = con.prepareStatement("select * from qc where qid=1");                          
            ResultSet rs=ps.executeQuery();
            if(rs.next()){
                jTextField1.setText(rs.getString("qid"));
                jTextField2.setText(rs.getString("ques"));
            }
            }           
            catch(ClassNotFoundException | SQLException e)
            {
            System.out.println(e);
            }
}                                        

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

Answer:

Make sure, that you have a MySql-Driver in your classpath.

You can download it here: https://dev.mysql.com/downloads/connector/j/5.0.html

Configuring your classpath is dependent on your environment. If you are running your code from within eclipse you can follow these steps:

  1. Download jdbc driver
  2. Put jdbc driver (for example mysql-connector-java-5.0.8-bin.jar) into your eclipse project
  3. Rightclick on it, Build Path, Add to Build Path

Using netbeans:

  1. Download jdbc driver
  2. Put jdbc driver (for example mysql-connector-java-5.0.8-bin.jar) into your netbeans project
  3. Right click on project, Properties, go to Libraries, choose Run-time libraries
  4. Add the jar to the list

For jumping to the next item, there are several options.

One solution could look like this:

Add this code at the top of your class:

// private JTextField jTextField1;
// private JTextField jTextField2;
// ...
private int currentQid; // add this line to declare a new field of type int

Then inside your actionlistener method:

PreparedStatement ps = con.prepareStatement("SELECT * FROM qc WHERE qid > ? ORDER BY qid LIMIT 1");
ps.setInt(1, currentQid);
ResultSet rs=ps.executeQuery();
if(rs.next()){
    currentQid = rs.getInt("qid");
    jTextField1.setText(Integer.toString(currentQid));
    jTextField2.setText(rs.getString("ques"));
}

In detail:

  • SELECT * FROM qc WHERE qid > ? ORDER BY qid LIMIT 1 will select the record, with the qid next to ?
  • ps.setInt(1, currentQid); will set the first parameter of the query (the ?) to a provided value
  • currentQid = rs.getInt("qid"); will save the next qid in the instance field currentQid
  • Integer.toString(currentQid) will convert currentQid to a text
  • jTextField1.setText(...); will display the text inside of jTextField1

Question:

I am designing a advanced search option in a Java project with sqlite in NetBeans. There are 5 different JTextfields and 5 columns, I want to populate JTable with corresponding matching criteria. If a JTextfield is empty then it should select all items of that column.

Query I was using is:

String val1 = txt_billing2.getText();
String val2 = txt_station2.getText();
String val3 = txt_invoice2.getText();
String val4 = txt_amonth2.getText();
String val5 = txt_umonth2.getText();

String sql = "SELECT * from airindia_sqlite WHERE BILLING = '"+val1+"' and STATION =    '"+val2+"' and INVOICE = '"+val3+"' and AMONTH = '"+val4+"' and UMONTH = '"+val5+"'";

pst = conn.prepareStatement(sql);
rs = pst.executeQuery();

Table_airindia.setModel(DbUtils.resultSetToTableModel(rs));

But when i leave a JTextfield empty it shows no data in JTable. Only method I know is to use if else conditions but that generates 5!= 120 conditions.


Answer:

First off please note your query is vulnerable to SQL injection attacks. To avoid these issues you need to use PreparedStatement properly:

String val1 = txt_billing2.getText();
...
String val5 = txt_umonth2.getText();

String sql = "SELECT * from airindia_sqlite WHERE BILLING = ? and STATION = ? and INVOICE = ? and AMONTH = ? and UMONTH = ?";
ps = con.prepareStatement(sql);
ps.setObject(1, val1);
...
ps.setObject(5, val5);

Having said this, I'd use an auxiliar class to write WHERE clause given a number of parameters. Something like:

public class WhereClause {

    private Integer currentIndex = 0;
    private Map<Integer, Object> parameters = new HashMap<>();
    private StringBuilder whereClause = null;

    public void addParameter(String columnName, Object value) {
        if(whereClause == null) {
            whereClause = new StringBuilder(" WHERE ");
        } else if (currentIndex > 0) {
            whereClause.append(" AND ");
        }
        whereClause.append(columnName).append(" = ?");
        paramenters.put(++currentIndex, value);
    }

    public String getWhereClause() {
        return whereClause != null ? whereClause.toString() : "";
    }

    public Map<Integer, Object> getParamenters() {
        return parameters;
    }
}

Then you could do something like this to get the appropriate SQL statement:

WhereClause whereClause = new WhereClause();

if(!(txt_billing2.getText().trim().isEmpty())) {
    whereClause.addParameter("BILLING", txt_billing2.getText().trim());
}

...

if(!(txt_umonth2.getText().trim().isEmpty())) {
    whereClause.addParameter("UMONTH ", txt_umonth2.getText().trim());
}

String sql = "SELECT * FROM airindia_sqlite" + whereClause.getWhereClause();
ps = con.prepareStatement(sql);

Map<Integer, Object> parameters = whereClause.getParameters();
for (Integer key : parameters.keySet()) {
    ps.setObject(key, parameters.get(key));
}

rs = ps.executeQuery();

Off-topic

Beware database calls are time consuming tasks and may block the Event Dispatch Thread (a.k.a. EDT) causing the GUI become unresponsive. The EDT is a single and special thread where Swing components creation and update take place. To avoid block this thread consider use a SwingWorker to perform database calls in a background thread and update Swing components in the EDT. See more in Concurrency in Swing trail.