• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Selecting Specific Java Object from SQL db and Update

I want to retrieve a specific java object namley Job by an equals statement therefore if a Job object used to query is equal to an existing object i want replace it with the new one.

eg, updateJob(Job currentJob, Job updateJob);

search the SQL database for current job and replace it with updateJob

please could you provide me with example code

any help or advice is greatly appreciated

Thank You
0
ACEAFTY
Asked:
ACEAFTY
  • 12
  • 10
  • 8
1 Solution
 
CEHJCommented:
You've got the code effectively - i just gave it to you. Select it first. If O != OtherO, use the other code to do an update
0
 
zzynxSoftware engineerCommented:
I would store an extra (unique) JobID column in the table
instead of having to read out all Job Blobs one by one to see if it matches.
0
 
ACEAFTYAuthor Commented:
Im sorry for my iggnorance but i am very new to this if u could show me some code examples as you did in previous thread that will be great
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
CEHJCommented:
>>if u could show me some code examples

You already have the examples. The only other code you need is this:


Object o = <object got from db select>
if (!o.equals(yourOtherObject)) {
   // use the insert code i gave you
}
0
 
zzynxSoftware engineerCommented:
And I'm saying that instead of querying for all records in the table

   for (all records in that table) {

>>   Object o = // code to query the object
>>    if (!o.equals(yourOtherObject)) {
>>         // use the insert code i gave you
             break; // update done
>>    }

}


... you could have an extra column that stores some unique JobID.
Then you don't need to iterate through all database records to find the one
that contains the job you want to replace.
Then you just need to query the record for which jobID = id of the Job you want to replace
0
 
CEHJCommented:
I assume anyway that you're going to have more than just the entire object saved in one column with no other information at all, but what are your criteria for

>>if a Job object used to query is equal to an existing object

?
0
 
ACEAFTYAuthor Commented:
i can only store a single entity of Job in the db as defined in the problem, what i do is:


retrieve an object
change the object values using set methods
then want to store tat object back

i can retrieve the object but how do i replace it it with a new object? what is the best aproach?
0
 
CEHJCommented:
Is there more than one job in the db? What is your table definition?
0
 
ACEAFTYAuthor Commented:
yes there is more than one job in the db, the types are Blob types, only one column!
0
 
zzynxSoftware engineerCommented:
>> Blob types, only one column!
Bad database design
0
 
zzynxSoftware engineerCommented:
It's bad, because you can't query on a blob column
and since performant querying is the core business of a database...
0
 
zzynxSoftware engineerCommented:
That's why a client database doesn't exist of just one BLOB column (containing a Client object)
You can't query on that.
If you need the information of the client with the name "zzynx",
you have to select all the blob's one by one and see if name matches "zzynx".

If you have a "name" column, you can

   select * from table where name = 'zzynx'

to get exactly the record you want
0
 
CEHJCommented:
Surely there must be other columns that relate to job info?
0
 
ACEAFTYAuthor Commented:
but that is what the secification asks for, is it not possible to achieve this using only a single entity?
0
 
ACEAFTYAuthor Commented:
this is what i have done but i still dont get the right object back if i use ! i get all the objects back seems like the object doesnt exist int he db!

            while (rs.next()){
                ByteArrayOutputStream out = new ByteArrayOutputStream();
                InputStream in = rs.getBinaryStream(1);
                int buf = -1;  
               
                while ((buf = in.read()) > -1) {
                    out.write(buf);
                }
               
                in.close();
                ObjectInputStream ois = new ObjectInputStream(new ByteArrayInputStream(out.toByteArray()));
                Object retrievedObject = ois.readObject();
                Job recievedJob = (Job) retrievedObject;
                if (recievedJob.equals(oldJob)){
                    System.out.println("hello");
                }
            }
0
 
zzynxSoftware engineerCommented:
Sure

>> if (recievedJob.equals(oldJob)){

does what you expect it to do?
What is Job?
0
 
zzynxSoftware engineerCommented:
>> is it not possible to achieve this using only a single entity?
It is possible. But with bad performance.
0
 
ACEAFTYAuthor Commented:
ok ive sorted that, how would i replace the recived job with a new job
this is what i have done, i understand its bad programming but i jus need it to work thnx for you help

public void updateJob(Job oldJob, Job newJob){
        String query = "SELECT Job FROM jobtable";
        String update = "UPDATE";
        try{
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
           
            while (rs.next()){
                ByteArrayOutputStream out = new ByteArrayOutputStream();
                InputStream in = rs.getBinaryStream(1);
                int buf = -1;  
               
                while ((buf = in.read()) > -1) {
                    out.write(buf);
                }
               
                in.close();
                ObjectInputStream ois = new ObjectInputStream(new ByteArrayInputStream(out.toByteArray()));
                Object retrievedObject = ois.readObject();
                Job recievedJob = (Job) retrievedObject;
                if ((recievedJob.getJobNumber()) == (oldJob.getJobNumber())){
                    System.out.println("hello");
                }
            }
        } catch (Exception e) {  
            e.printStackTrace();
        }
    }

i want to replace the retrieved job with the new job
0
 
CEHJCommented:
>>i want to replace the retrieved job with the new job

You have the code already to do that - i gave it to you before
0
 
zzynxSoftware engineerCommented:
Replace
>> System.out.println("hello");

with the code you got
0
 
CEHJCommented:
Incidentally if you replaced the old equals code with

>>if ((recievedJob.getJobNumber()) == (oldJob.getJobNumber()))

because equals didn't work, then you should implement it properly or it may well fail in collection classes
0
 
ACEAFTYAuthor Commented:
im very sorry for being thick but wot do i exactly put in there? how do i overwrite the existing job object
0
 
CEHJCommented:
Since there's only one anyway, just do the insert using the code i gave you. It will overwrite it
0
 
ACEAFTYAuthor Commented:
i still cant get this working! i have a sql table with many Job objects once i retrieve the job object how do i update it? if i do the code you gave me it creates a new entry ov the new job and does not update the old one!! Please Help!
0
 
CEHJCommented:
>>i have a sql table with many Job objects

I thought there was only one! You'll probably have to use an updatable result set, so your db and driver will need to support it. See

http://javaalmanac.com/egs/java.sql/UpdateRow.html

in your case it'll be SELECT Job from X and you'll need to iterate them all until you've found the right one
0
 
ACEAFTYAuthor Commented:
nope still dont wrk i used the code there but i get the following error

com.mysql.jdbc.NotUpdatable: Result Set not updatable.This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.
        at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:1557)
        at JobApp.DataStore.updateJob(DataStore.java:117)
        at JobApp.DataStore.main(DataStore.java:170)

this is what i have done
                if ((recievedJob.getJobNumber()) == (oldJob.getJobNumber())){
       
                    Statement stmt1 = conn.createStatement(
            ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet = stmt1.executeQuery("SELECT Job FROM jobtable");
   
        // Move cursor to the row to update
        resultSet.first();
   
        // Update the value of column col_string on that row
      //  resultSet.updateString("col_string", "new data");
   
        // Update the row; if auto-commit is enabled, update is committed
        resultSet.updateRow();



                   
                    System.out.println("hello");
                   
                }
0
 
ACEAFTYAuthor Commented:
i have inserted a primary key as Job but now it will only allow me to have one entry of Job :(
0
 
CEHJCommented:
>>
this is what i have done
                if ((recievedJob.getJobNumber()) == (oldJob.getJobNumber())){
>>

No - you should be doing the select with that updatable statement in the first place - you're trying to create one after doing a select first
0
 
CEHJCommented:
Instead of

// Move cursor to the row to update
        resultSet.first();
   
        // Update the value of column col_string on that row
        resultSet.updateString("col_string", "new data");
   
        // Update the row; if auto-commit is enabled, update is committed
        resultSet.updateRow();

your code should look something like:        
       
boolean updated = false;
while (resultSet.next() && !updated) {
      Job job = (Job)resultSet.getObject(1);
      if (job.getId() == otherJob.getId()) {
            resultSet.updateObject(1, otherJob);
            resultSet.updateRow();
            updated = true;
      }
      
}
0
 
CEHJCommented:
:-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now