Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Developer and personalized recommendations.Start Your Free Trial Now
- View Offline
Author David Gassner explains how to choose a JDBC driver and connect to one or more databases. He also provides detailed instructions on reading, selecting, and updating data; calling stored procedures; managing data via JavaBean classes or with prepared statements; and working with metadata.
- Importing a MySQL database
- Connecting to databases
- Handling JDBC exceptions
- Looping through result sets
- Limiting the number of fetched rows
- Filtering data with prepared statements
- Calling stored procedures
- Inserting, updating, and deleting rows with prepared statements
- Using a persistent database connection
- Committing and rolling back transactions
Skill Level Intermediate
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.