CODEWITHSUNDEEP
javasqljdbcsql-updateprepared-statement
Jayden Ng
Jayden Ng

Reputation: 151

JDBC - How to update record where date -1

I have two tables with two columns for storing application settings in a SQL database. My question is how to update the record with changing the dCreateTime value into minus 1 day plus there are two table to calling where userID = atest211. My code seems not to be working.

My code: 

public void getEmployeesFromDataBase() {
        try {
            String query = "update a set a.dCreateTime=a.dCreateTime-1 from tbet a, tuser b where a.iUserKey= b.iUserKey and b.sUserid = 'atest211' and a.dCreateTime> GETDATE()-5";
            statement = connection.createStatement();
            rs = statement.executeQuery(query);

            while (rs.next()) {
                int EmpId = rs.getInt("iUserKey");
                String EmpName = rs.getString("sUserid");
                System.out.println(EmpId + "\t" + EmpName + "\t");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

My updated code, it still is not working correctly

public void updateTableData() {
        PreparedStatement pstmt = null;
        try {
            pstmt = connection.prepareStatement("update a set a.dCreateTime=a.dCreateTime-1 from tbet a, tuser b where a.iUserKey= b.iUserKey and b.sUserid = 'atest211' and a.dCreateTime> GETDATE()-5");
            pstmt.setString(1, "dCreateTime");
            pstmt.setString(2, "atest211");
            // To execute update query.
            pstmt.executeUpdate();

            // Printing all records of user table after updating record.
            String query = "select * from tbet";
            // Get the contents of user table from DB
            ResultSet res = statement.executeQuery(query);
            // Print the result untill all the records are printed
            // res.next() returns true if there is any next record else returns
            // false
            while (res.next()) {
                System.out.println(String.format("%s - %s - %s - %s ", res.getString(1), res.getString(2),
                        res.getString(3), res.getString(4)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

Upvotes: 1

Views: 1796

Answers (3)

Jayden Ng
Jayden Ng

Reputation: 151

Credit to @OldMcDonald , as i amend the code and it's working unfortunately there are some error during select statement while trying to retrieve the record out to check either the record has been updated , encounter error of : java.lang.NullPointerException. It resolved the error by adding statement = connection.createStatement();

Here's the code : 

public void updateTableData() {
            PreparedStatement pstmt = null;
            try { 
                //create calendar date -1 day
                Calendar cal = Calendar.getInstance();
                cal.add(Calendar.DATE, -1); 

                // preparing query to update record In db. 
                pstmt = connection.prepareStatement("update a set a.dCreateTime= ? from tbet a, tuser b where a.iUserKey= b.iUserKey and b.sUserid = ? and a.dCreateTime> GETDATE()-5");
                // Set name value which you wants to update. 
                pstmt.setDate(1, new java.sql.Date(cal.getTime().getTime()));
                // Set id of record which you wants to update.
                pstmt.setString(2, "atest211");
                // To execute update query.
                pstmt.executeUpdate();

                // Printing all records of user table after updating record.
                String query = "select * from tbet where iUserKey=53298 ORDER BY tbet.dCreateTime desc";
                statement = connection.createStatement();
                // Get the contents of user table from DB
                ResultSet res = statement.executeQuery(query);
                // Print the result untill all the records are printed
                // res.next() returns true if there is any next record else returns
                // false
                while (res.next()) {
                    System.out.println(String.format("%s - %s - %s - %s ", res.getString(1), res.getString(2),
                            res.getString(3), res.getString(4)));
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

Upvotes: 1

OldMcDonald
OldMcDonald

Reputation: 574

You are using a result set with an update, that is not possible. Also, in your while loop, you are basically getting values from the database, not updating them.

If you want to update values, I advise you to use PreparedStatement.

If you want your code to work, try using SELECT instead of UPDATE in your query.

 String query = "SELECT a.dCreateTime FROM tbet a, tuser b WHERE a.iUserKey= b.iUserKey and b.sUserid = 'atest211' and a.dCreateTime> GETDATE()-5";

EDIT: If you want to use an PreparedStatement, try something like this

// create the preparedstatement
PreparedStatement ps = conn.prepareStatement(
  "UPDATE yourTable SET title = ?, author = ? WHERE id = ?");

ps.setString(1,title);
ps.setString(2,author);
ps.setInt(3,id);

//execute the upate
ps.executeUpdate();
ps.close();

Upvotes: 2

Jens
Jens

Reputation: 69494

You need the update ... join .. set .. where Syntax:

       String query = "update a join b on a.iUserKey= b.iUserKey set a.dCreateTime=a.dCreateTime-1  where b.sUserid = 'atest211' and a.dCreateTime> GETDATE()-5";

Next is you have to use statement.execute(query); instead of using executeQuery(query). That method do not return an resultset ....

Also you should read about SQL injection and using prepared Statements.

Upvotes: 1

Related Questions