5
\$\begingroup\$

I have got a JTable implementing a TableModel interface. There are ID (primary key, auto-increment), tbl_Date, Flat, Mobile, Food, Alcohol, Transport, Outdoor, Pauls_stuff, Income, Stuff columns.

Every time I start the program, I want to get rows filled with data: tbl_Date until tommorow, everything else except ID, set to zeroes.

To be more clear, I will give an example. Let's say the last time I entered any data to database was on 2014-12-10. Today is 2014-12-14. I want the column tbl_date to be inserted with dates accordingly to tommorow's date (2014-12-15), and the rest of columns, except ID, to be set to zeroes.

I have the program producing the result I wanted. I wonder if it could be made somehow simpler and/or be optimised.

private static void createAndShowGUI() throws SQLException {
    //Create and set up the window.
    JFrame frame = new JFrame("TableWithBottomLine");
    TableWithBottomLine tbl = new TableWithBottomLine();        

    frame.addWindowListener(
            new WindowAdapter() {
                public void windowOpened(WindowEvent e) {
                    try {                       
                        tbl.bottomLabel2.setText("Text");                           

                        String query ="SELECT f.tbl_Date FROM finance.fin f";       
                        ResultSet rs ;
                        Connection connection = TableWithBottomLine.getConnection();
                        Statement stmt = null;
                        stmt = connection.createStatement();
                        rs = stmt.executeQuery(query);
                        rs.last();
                        // Let's get last date in tbl_Date row from database
                        tbl.lastDate = rs.getDate("tbl_Date");                      
                        long milliseconds = tbl.lastDate.getTime();
                        // Let's get current day
                        Calendar cal = Calendar.getInstance();
                        cal.set(Calendar.HOUR_OF_DAY, 0);
                        cal.set(Calendar.MINUTE, 0);
                        cal.set(Calendar.SECOND, 0);
                        cal.set(Calendar.MILLISECOND, 0);
                        Long currentDate = cal.getTimeInMillis();                       

                        PreparedStatement pStmt = connection.prepareStatement("INSERT into finance.fin (tbl_Date, Flat, Mobile, Food, Alcohol, Transport, Outdoor, Pauls_stuff, Income, Stuff) values(?,?,?,?,?,?,?,?,?,?)");                       
                        while (milliseconds < currentDate + 86400000)
                            {                                   
                                milliseconds = milliseconds + 86400000;

                                pStmt.setDate(1, getDateForInsertion(milliseconds));
                                pStmt.setFloat(2, 0.0f);
                                pStmt.setFloat(3, 0.0f);
                                pStmt.setFloat(4, 0.0f);
                                pStmt.setFloat(5, 0.0f);
                                pStmt.setFloat(6, 0.0f);
                                pStmt.setFloat(7, 0.0f);
                                pStmt.setFloat(8, 0.0f);
                                pStmt.setFloat(9, 0.0f);
                                pStmt.setFloat(10, 0.0f);                               

                                pStmt.addBatch();                       
                            }
                        pStmt.executeBatch(); 

                    } catch (Exception ee) {
                        ee.printStackTrace();
                    }                       
                } 
            }
        );        

    frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
 //   frame.setContentPane(new TableWithBottomLine());
    frame.setContentPane(tbl);        
    new TableWithBottomLine().setOpaque(true); //content panes must be opaque        
    frame.pack();
    frame.setVisible(true);
}

private static java.sql.Date getDateForInsertion(Long date) {
    Date tmp_date = new Date(date);  
    return new java.sql.Date(tmp_date.getTime());       
}
\$\endgroup\$

migrated from stackoverflow.com Dec 14 '14 at 23:53

This question came from our site for professional and enthusiast programmers.

2
\$\begingroup\$

I'm not a kind of pro but I know that you SHOULDN'T use String Combining on SQL queries, you should use PreparedStatement.

\$\endgroup\$
  • \$\begingroup\$ good comment but the OP is using prepared statement. Or was this edited after the fact? \$\endgroup\$ – Joeblade Dec 15 '14 at 3:14
1
\$\begingroup\$

As Visian Maya points out, use PreparedStatement. This is to avoid sql injection if nothing else.

Additionally it is considered good practice to close your resultsets and statements as discussed here

I would urge you to move the database connection logic to a separate method, if not a separate class. User interface classes are already very cluttered with a lot of UI code, it will not help readability to add db code to it. This will also help you reuse the db code, if you need to.

I would really not use java.sql.Date , instead create your own timestamp and store it as a long. see the accepted answer in this discussion for why

It looks like you want to get the highest date from your table. Why not simply do that in sql (order by f.tbl_Date desc) and get the first result?

If you're never going to insert anything other than 0 into the query, you can pull the 0 values into the query.

while (...) {
    PreparedStatement pStmt = connection.prepareStatement("INSERT into finance.fin (tbl_Date, Flat, Mobile, Food, Alcohol, Transport, Outdoor, Pauls_stuff, Income, Stuff) values(?,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0)");   
    milliseconds = milliseconds + 86400000;
    pStmt.setDate(1, getDateForInsertion(milliseconds));
}

I would not use magic numbers. better make a

// somewhere at the top of your class: don't use magic numbers, use constants
public final static MILS_IN_DAY = 1000 * 60 * 60 * 24;


// and use this inside your loop
while (milliseconds < currentDate + MILS_IN_DAY) {                                   
    milliseconds = milliseconds + MILS_IN_DAY;
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy