datechooser getting value to search between dates

[Neha Kaushik]

How can i get value from 2 JDateChooser formatted yyyy-MM-d and put it in sql query (String sql="select .... from ... between date1 and date2") which should search data between those dates and outputted in a JTable.

I used code but no output appeared:


try {
Connection con1 = DBConnection.connect();

int index = jList1.getSelectedIndex();
String click = (jList1.getModel().getElementAt(index).toString());

String sql = "SELECT distinctrow request.file_no , request.file_subject , request.hardware_request , approval.quantity , approval.approved_quantity FROM request INNER JOIN approval ON request.id = approval.id WHERE request.selected_department = '" + click + "' ";

PreparedStatement pst = con1.prepareStatement(sql);
ResultSet rst = pst.executeQuery();
ResultSetMetaData rsMetaData = (ResultSetMetaData) rst.getMetaData();
int cols = rsMetaData.getColumnCount();
System.out.println("database" + cols);
for (int i = 1; i < cols; i++) {
columnNames.addElement(rsMetaData.getColumnName(i));
}
System.out.println("while ke baher");

while (rst.next()) {
System.out.println("while ke ander");

Vector row = new Vector(cols);

for (int i = 1; i <= 1; i++) {
row.addElement(rst.getObject(i));
}
int ro = row.size();
System.out.println(ro + "row val");
for (int i = 1; i <= 1; i++) {

String file_no = rst.getString("file_no");
System.out.println(file_no);
String sub = rst.getString("file_subject");
System.out.println(sub);
String hard = rst.getString("hardware_request");
System.out.println(hard);
String rquan = rst.getString("quantity");
System.out.println(rquan);
String aquan = rst.getString("approved_quantity");
System.out.println(aquan);

//
DefaultTableModel model1 = (DefaultTableModel) mis_dept.getModel();

model1.addRow(new Object[]{ file_no, sub,hard,rquan, aquan});
}
}

//
} catch (Exception ex) {
JOptionPane.showMessageDialog(null, "Error in submitting data!" + ex);
}


please tell me how i use the where clause with 2 conditions as i use WHERE request.selected_department = '" + click + "' "...
and 2nd clause to search data between two dates..
any help will be apperitiated...

[K. Tsang]

Is this a sql question or JDateChooser question. If the latter, are you able to get the chosen date to your sql? Is it java.util.Date or you need to format it to YYYY-MM-DD (or some other format) with SimpleDateFormat?

[Neha Kaushik]

sir this is sql question i want correct sql query to search data from database between two dates ..

[K. Tsang]

Well in SQL I'm not aware of a so-called IN_BETWEEN(date1, date2) function. Yet the simplest is to do something like:

select * from table where startCol >= date1 and endCol <= date2

If your date passed into sql is a string format then you can use the to_date(date1, 'yyyy-mm-dd') function. The 2nd parm is the format.

[Neha Kaushik]

thanks for the help ...
i also got the sql query ..
SELECT distinctrow request.file_no , request.file_subject , request.hardware_request , approval.quantity , approval.approved_quantity FROM request INNER JOIN approval ON request.id = approval.id WHERE request.selected_department = '" + click + "' and approval_date Between '" + d + "' And '" + d1 + "' "

where d1 and d is
java.util.Date date= fromchooser.getDate(); //fromchooser is jdatechooser for startdate
long dateTime = date.getTime();
java.sql.Date d = new Date(dateTime);
java.util.Date date1= tochooser.getDate(); //tochooser is jdatechooser for enddate
long dateTime1 = date1.getTime();
java.sql.Date d1 = new Date(dateTime1);

[Neha Kaushik]

Hey guys

I'm currently developing an Equipment approval System. I'm using Java and mySQL as the database. I'm getting fairly close to finishing it all I need to do is add a few analytical abilities to it and to install it on another's pc.

But one thing I'm wondering now is how will I set up the database on their computer? Will I have to actually download mySQL and set up the tables etc? I've only done brief searches .



I was hoping anyone who has had experience in doing something similar could point me in the right direction.

Regards