Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Inserting rows with prepared statements

From: Java Database Integration with JDBC

Video: Inserting rows with prepared statements

Once you have created a JavaBean class to represent the structure of a table, you can start adding code to your application to insert update and delete rows. I'll start with inserting data. In this version of the project InsertSQL, my AdminManager class has a new method called Insert. It accepts an instance of my JavaBean class as an argument, and it already has an SQL command. An SQL command to insert data looks like this. It starts with the INSERT command, then into, and the name of the table. In this admin and then a common delimited list of the column names you are inserting into inside parentheses.

Inserting rows with prepared statements

Once you have created a JavaBean class to represent the structure of a table, you can start adding code to your application to insert update and delete rows. I'll start with inserting data. In this version of the project InsertSQL, my AdminManager class has a new method called Insert. It accepts an instance of my JavaBean class as an argument, and it already has an SQL command. An SQL command to insert data looks like this. It starts with the INSERT command, then into, and the name of the table. In this admin and then a common delimited list of the column names you are inserting into inside parentheses.

You follow that with the keyword values and then the values you are inserting also comma delimited and also in parentheses. For JDBC, I'm representing those values with the question mark placeholder, and this method already has the code to create the connection object and a prepared statement. I'm going to add the code to populate the placeholders with values. I will move the cursor into the code block after the opening brace and make a little bit of extra space, and I'll call this set string method of the statement object twice once for each placeholder.

I'll start with stmt.setString, and I will pass in a parameter index of one for the first parameter, and I will pass in the bean objects getUserName method. Then I will do the same thing for the password. I will once again call setString, I will pass in 2 this time, and now I will call the bean objects get password method. Once you have populated the SQL statement, you can execute the update, and I'll call stmt.executeUpdate to pass those changes to the database. Now that's all you need to do if you're providing the primary key value.

But if you're working with a database table where the primary key column uses an integer primary key that auto increments, that is where the next available value is assigned automatically by the database then JDBC gives you the ability to get that new value right after you execute the update. Here's how it works. First you need to find out whether the insert statement was successful. The executeUpdate method always returns an integer value, the integer is the number of rows that were affected by the insert and for this kind of insert statement if the execution was successful it'll always be one.

So I'm going to place the cursor before the call to execute update, and I will declare a new variable called affected data typed as an int, and I will populate its value from the executeUpdate method. Then after the call to the method, I will test whether the execution was successful. I will use an if else clause. I will set the condition to affected has a value of 1. Now if the value is 1, that means that a row is inserted into the database. In order to get back that value, you're going to need to declare a result set object, and because of the structure of the try catch block, you will want to declare the result set object before the try block.

I will move the cursor to above the try, I'll declare a result set object, and I'll name it keys, and I will initially set it to null, then I'll move the cursor back to the if block. To find out what the generated primary key value is, call a method called get generated keys. It'll return that result set object. So I will type in keys equals, and I will get the results set by calling the statement objects, getGeneratedKeys method. It's a result set, and as with all result sets, the cursor starts before the first row of data.

So then I will call keys.next to move the cursor to the one and only row of data. Now I am ready to get the value. When you call getGeneratedKeys, you will always get back a result set with a single column and a single row. So to get the value out, I will declare a variable named newKey, and I will call keyes.getint, and I will pass in a value of 1 for the first column. You might be tempted to find out what the name of column is and use that instead. But the name of that column will differ from one database to the next.

So for maximum portability, just say you are getting it from column 1. Then I will take that value, and I will pass it to the bean object using bean.setAdminId, and I will pass in newKey. Now I am not going to return the bean object, instead I am just setting this property of the bean object which will also be accessible from the calling context. So I am going to place the value there, and then I'll get it back later after I have called the insert method. I'm not done with this method yet, though. First, let's do with the else condition.

If I get into this bit of code, that means that my insert statement was unsuccessful. So I will do some system error output and output a static string of No rows affected, and then I will return false which according to the logic of this method, the insert method, means that this was not successful. I also need to deal with the keys result set as with all results sets. You need to explicitly close it when you're done. So I'll move the cursor down to within the finally block, I will test the keys object and make sure it's not null, and if it isn't, I will close it.

Notice I'm putting all of this code on a single line because I don't have multiple lines of code, I'm not using braces, and I am keeping it nice and simple. This method is now complete, but before I leave it I will point out one little bit of code that I added previously. When I call the prepared statement method, I'm explicitly passing in these option statement.return generated keys. For some database management systems, the statement object will do this automatically, but for others it won't. As with all such options where database behaviors will differ, I recommend being explicit in the options.

Here I'm saying no matter what database management system I'm working on, if you're able to return the generated keys, please do it. So that's the entire insert method. Now let's add code in the main class to call the method. I will go to Main.java and with the cursor after the call to display all rows, I'll get information from the user and pass it to a bean object. First, I'll create an instance of the Admin bean object. I will name it bean, and I will instantiate it with the bean class's constructor method. Now I will set the two properties that I want to add my new database row.

First, I'll call the bean object setUserName method, and I will directly pass in the result of calling my InputHelpers, get input method. I will type InputHelper and press Ctrl+Space to add the import then get input, and I will display a prompt of User name, then I'll do the same thing again. This time for the password, I will call the setPassword method of the bean object, and I'll pass in the inputHelpers getInput method again, and this time I'll use a prompt of Password. Now I'm ready to insert the data into the database.

Remember that the insert method will return a boolean value, so I will declare a variable that I will name result, and I will call AdminManager.insert, and I will pass in the bean object. Then I will test the result. If the result is true, I will do a little bit of system output, and I will output the string New row with primary key, and then I will append to that bean.getAdminId. Remember, I populated that value from the inset method and then I will complete the output with was inserted. And now I am ready to test my application.

I will make sure I have saved all my code, and I will run the application. I will type in a new user of New Admin, and I will type in a password of New Password. And I get back the message that the New row with primary key of 2 was inserted. So to check to make sure that the data was correctly inserted, I will go to my browser, and I will open up phpMyAdmin. I will go to my explorecalifornia database to the admin table and browse, and I see my new row was successfully inserted. So that's how you insert data using JDBC.

You don't have to use JavaBean classes, but as you can see here, to help keep your code clean and easily maintainable, you encapsulate the data that you want to add into the database inside an instance of your JavaBean, you pass the bean object from one method to the next, and then if you need to restructure your database table, you can restructure your JavaBean class as well.

Show transcript

This video is part of

Image for Java Database Integration with JDBC
Java Database Integration with JDBC

32 video lessons · 6137 viewers

David Gassner
Author

 
Expand all | Collapse all
  1. 5m 23s
    1. Welcome
      53s
    2. What you should know before starting this course
      1m 20s
    3. Using the exercise files
      3m 10s
  2. 12m 2s
    1. Testing your Java development environment
      5m 14s
    2. Importing a MySQL database
      5m 1s
    3. Creating a database user in MySQL
      1m 47s
  3. 32m 47s
    1. What is JDBC?
      4m 26s
    2. Choosing a JDBC driver
      6m 46s
    3. Connecting to a MySQL database server
      8m 7s
    4. Connecting to a HyperSQL database file
      6m 23s
    5. Executing a static SQL statement
      7m 5s
  4. 17m 42s
    1. Connecting to multiple databases
      6m 24s
    2. Handling JDBC exceptions
      7m 7s
    3. Closing database resources in Java 7
      4m 11s
  5. 47m 25s
    1. Looping through result sets
      8m 23s
    2. Moving the cursor in scrollable result sets
      5m 51s
    3. Limiting the number of fetched rows
      6m 57s
    4. Filtering data with prepared statements
      6m 58s
    5. Calling stored procedures
      5m 48s
    6. Handling multiple values from stored procedures
      5m 54s
    7. Using generic getter methods in Java SE 7
      7m 34s
  6. 45m 23s
    1. Managing data entities with JavaBean classes
      5m 0s
    2. Retrieving a single row as a JavaBean object
      6m 5s
    3. Inserting rows with prepared statements
      8m 2s
    4. Updating rows with prepared statements
      5m 4s
    5. Deleting rows with prepared statements
      4m 9s
    6. Managing data with updatable result sets
      6m 6s
    7. Using a persistent database connection
      6m 43s
    8. Committing and rolling back transactions
      4m 14s
  7. 9m 35s
    1. Getting the DatabaseMetaData object
      3m 40s
    2. Getting column and data type information
      5m 55s
  8. 50s
    1. Next steps
      50s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Java Database Integration with JDBC.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.