Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes datechooser getting value to search between dates Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "datechooser getting value to search between dates" Watch "datechooser getting value to search between dates" New topic
Author

datechooser getting value to search between dates

Neha Kaushik
Greenhorn

Joined: Apr 03, 2013
Posts: 29
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
Bartender

Joined: Sep 13, 2007
Posts: 2380
    
    7

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?

K. Tsang JavaRanch SCJP5 SCJD/OCM-JD OCPJP7 OCPWCD5
Neha Kaushik
Greenhorn

Joined: Apr 03, 2013
Posts: 29
sir this is sql question i want correct sql query to search data from database between two dates ..

K. Tsang
Bartender

Joined: Sep 13, 2007
Posts: 2380
    
    7

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
Greenhorn

Joined: Apr 03, 2013
Posts: 29
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
Greenhorn

Joined: Apr 03, 2013
Posts: 29
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
 
Consider Paul's rocket mass heater.
 
subject: datechooser getting value to search between dates