Search code examples
javasqlswingnetbeansjdatechooser

Using jDateChooser in SQL query

I'm working on a little exercise, using ORACLE HR database and Netbeans, and I want to calculate difference between two jDateChooser values using a search button, here is a screenshot of my jFrame which explains everything, including my output error :

http://prnt.sc/d7k538

And here is my code :

import java.util.*;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;


public class Exercise extends javax.swing.JFrame {

     Connection Con = myConn.getCon();
     Statement St = null;
     ResultSet Rs = null;

     String tab[] = {"LAST_NAME", "FIRST_NAME", "JOB_TITLE", "DEPARTMENT_NAME", "SALARY"};
     DefaultTableModel dtm = new DefaultTableModel(tab, 0);

    public Exercise() {
        initComponents();

        jTable1.setModel(dtm);

        fill_combo_job();
        fill_combo_dept();

        int Rc = dtm.getRowCount();
        row_count_label.setText(Integer.toString(Rc));
    }

    public void fill_combo_job(){

             try{
             //Getting data from table JOBS

             St = Con.createStatement();
             Rs = St.executeQuery("SELECT DISTINCT JOB_ID FROM JOBS");

             //Filling our jcombobox

             while(Rs.next()){
                 job_id_combo.addItem(Rs.getString("JOB_ID"));
             }

         }catch(Exception ex){

             System.out.println(ex.toString());
    }
    }

    private void fill_combo_dept(){

        try{

            St = Con.createStatement();
            Rs = St.executeQuery("SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL ORDER BY DEPARTMENT_ID");

            while(Rs.next()){
                dept_id_combo.addItem(Rs.getString("DEPARTMENT_ID"));
            }

        }catch(Exception ex){

            System.out.println(ex.toString());
        }

    }

    private void search_btn(){

        DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
        String date1 = df.format(jDateChooser1.getDate());
        String date2 = df.format(jDateChooser2.getDate());



        try{

   St = Con.createStatement();
   Rs = St.executeQuery("SELECT e.LAST_NAME, e.FIRST_NAME, j.JOB_TITLE, d.DEPARTMENT_NAME, e.SALARY FROM EMPLOYEES e, JOBS j, DEPARTMENTS d "
   + "WHERE j.JOB_ID = e.JOB_ID "
   + " AND d.DEPARTMENT_ID = e.DEPARTMENT_ID"
   + " AND j.JOB_ID = "+job_id_combo.getSelectedItem().toString()+" AND e.DEPARTMENT_ID = "+dept_id_combo.getSelectedItem().toString()
   + " AND HIRE_DATE BETWEEN TO_CHAR ('"+date1+"', 'dd/MM/yyyy') AND TO_CHAR('"+date2+"', 'dd/MM/yyyy')"
   + " AND SALARY > "+salary_field.getText());

   while(Rs.next()){

    dtm.addRow(new Object[]{Rs.getString("LAST_NAME"), Rs.getString("FIRST_NAME"),
    Rs.getString("JOB_TITLE"), Rs.getString("DEPARTMENT_NAME"), Rs.getInt("SALARY")});
            }

            Con.close();
            St.close();
            Rs.close();


        }catch(Exception ex){

            System.out.println(ex.toString());
        }


    }

    public void reset_form(){

        job_id_combo.setSelectedIndex(0);
        dept_id_combo.setSelectedIndex(0);
        salary_field.setText("");


    }
Solution

  • This error means there is something wrong with your query. Typo, incorrect quotes, incorrect capitalization.