• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Several questions on Inserting New Rows Using a ResultSet

 
Sean Zhang
Greenhorn
Posts: 23
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have several questions on Inserting New Rows Using a ResultSet. Can you please take a look? Thanks.

In K&B7 Page 895, there is a sample code as follows:


Question 1: Can I delete line 3, so that the cursor is pointing above the first row before we move the cursor to the insert row? Is this allowed?

Question 2: After executing line 8, where is the new row inserted in the database? Is it always appended in the end of the database as the last row no matter where the cursor was pointing in the result set before it was moved to the insert row?

Question 3: After executing line 8, if I want to insert another new row, do I need to add another line "rs.moveToInsertRow();" in the code?

Question 4: Between the line 7 and line 8, if I add a new line "rs.next();", will this lead to throw SQLException in runtime because next() method is not allowed when the cursor is in the insert row?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Sean Zhang,

First of all, a warm welcome to CodeRanch!

Sean Zhang wrote:I have several questions on Inserting New Rows Using a ResultSet. Can you please take a look?

Sure! But I think you can easily answer almost every question yourself if you make the changes you suggest and simply execute the code and/or have a look at the javadoc of the ResultSet interface.

Sean Zhang wrote:Question 1: Can I delete line 3, so that the cursor is pointing above the first row before we move the cursor to the insert row? Is this allowed?

Why would that not be allowed? The insert row is a special row associated with an updatable result set. There's no relation with the current position of the cursor. And also the javadoc of the moveToInsertRow() method doesn't mention throwing a SQLException if this method is positioned above the first row.

Sean Zhang wrote:Question 2: After executing line 8, where is the new row inserted in the database? Is it always appended in the end of the database as the last row no matter where the cursor was pointing in the result set before it was moved to the insert row?

That's not applicable as a database table doesn't have any order at all.

Sean Zhang wrote:Question 3: After executing line 8, if I want to insert another new row, do I need to add another line "rs.moveToInsertRow();" in the code?

That's another question you could try for yourself. According to the javadoc of the insertRow() method, it doesn't mention the cursor position is moved back to the original position (before the moveToInsertRow() method was called), that's the responsibility of the moveToCurrentRow() method. So I would assume that you can insert another row by just updating the columns with another set of new values and call the insertRow() method once more.

Sean Zhang wrote:Question 4: Between the line 7 and line 8, if I add a new line "rs.next();", will this lead to throw SQLException in runtime because next() method is not allowed when the cursor is in the insert row?

I expect to get a SQLException on the invocation of the insertRow() method, because by invoking the next() method you are not anymore on the special "insert row".

Hope it helps!
Kind regards,
Roel
 
Sean Zhang
Greenhorn
Posts: 23
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Roel,

Thank you for your reply. First, I agree that it's always a good habit that we write some small code and run experiments ourselves, and that's my way to prepare for my OCP 804 exam, especially for Chapters 13 and 14 on Threads. But unfortunately, I have no access to a database, and that's why I did not run experiments myself.

Question 1 is clear now.

Question 2: The ResultSet gives us a means to insert rows into the database, but any such rows cannot have been among the results when the query was executed, right? And my question is: where is the new inserted row in the database's table? (Is it true that the new inserted row is always put as the last row in the database's table?)

Question 3: The reason why I ask this question is I have seen a code used by Java doc which calls the moveToInsertRow() method a second time when they insert a second row. Please check the following webpage:
https://docs.oracle.com/javase/tutorial/jdbc/basics/jdbcrowset.html#inserting-row
Still you think it is not necessary to call the moveToInsertRow() method a second time when they insert a second row?

Question 4: So you mean the next() method can be called even when the cursor is in the insert row?

Thank you for your time again!

S.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sean Zhang wrote:But unfortunately, I have no access to a database, and that's why I did not run experiments myself.

There are many free database available which have a JDBC driver. Probably MySQL is the most popular one. But for these experiments you could even use an in-memory database like h2 or hsqldb.

Sean Zhang wrote:Question 2: The ResultSet gives us a means to insert rows into the database, but any such rows cannot have been among the results when the query was executed, right? And my question is: where is the new inserted row in the database's table? (Is it true that the new inserted row is always put as the last row in the database's table?)

Like I already mentioned, a database table is a set of data elements. So there is no particular order. That's why you need to use the ORDER BY clause in your query to list the records in a given order.

Sean Zhang wrote:Question 3: The reason why I ask this question is I have seen a code used by Java doc which calls the moveToInsertRow() method a second time when they insert a second row. Please check the following webpage:
https://docs.oracle.com/javase/tutorial/jdbc/basics/jdbcrowset.html#inserting-row
Still you think it is not necessary to call the moveToInsertRow() method a second time when they insert a second row?

Yes, I still think it's not required. Because otherwise the insertRow() method would mention that after inserting the record the cursor position is moved back to the current row. But that's not the case, so once a record is inserted, the cursor is still on the "insert row". Of course there is no harm in invoking this method again after inserting the first row. Now that you have a list of several databases you could use for your experiments, this is certainly one you could/should try.

Sean Zhang wrote:Question 4: So you mean the next() method can be called even when the cursor is in the insert row?

Yes. And that's yet another experiment you can write a code snippet for and try it yourself.

Hope it helps!
Kind regards,
Roel
 
Sean Zhang
Greenhorn
Posts: 23
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Roel,

Thank you for your detailed reply and useful info. Although I didn't run experiments myself for JDBC, fortunately this is the only place which confused me. Since I thought there was no way for me to access a database to run experiments myself for JDBC, I decided to post my very first question here and yes, this is my first time. Thank you for your patience and help!

Last, for those who are preparing for OCP 804 exam, I'd say JDBC is among one of the easiest chapters! After carefully reading K&B7 from cover to cover, I am confident not only to pass the OCP 804 exam, but also to face the incoming interviews! (It took me 6 days to finish the 1st part of K&B7, OCA, and another 6 weeks to finish the 2nd part of this book, OCP.)

Best,

S.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sean Zhang wrote:Although I didn't run experiments myself for JDBC, fortunately this is the only place which confused me.

Or maybe it was thanks to not running any experiments at all, you were only confused with this one as you didn't encounter confusing issues like here, here and here
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sean Zhang wrote:Dear Roel,

Thank you for your detailed reply and useful info. Although I didn't run experiments myself for JDBC, fortunately this is the only place which confused me. Since I thought there was no way for me to access a database to run experiments myself for JDBC, I decided to post my very first question here and yes, this is my first time. Thank you for your patience and help!

Last, for those who are preparing for OCP 804 exam, I'd say JDBC is among one of the easiest chapters! After carefully reading K&B7 from cover to cover, I am confident not only to pass the OCP 804 exam, but also to face the incoming interviews! (It took me 6 days to finish the 1st part of K&B7, OCA, and another 6 weeks to finish the 2nd part of this book, OCP.)

Best,

S.

You should consider installing a lightweight database so that you can play around with these tools, even if you don't think you need to do this for the OCP exam. I've worked with plenty of people who know all about the JDBC API, but they still have no idea how to use it intelligently. Practical experience will deepen your understanding, as well as making you much more useful to employers. If you're applying for jobs, it won't take long for people to recognise whether you have any practical experience or not.

Good luck.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic