We’ve seen how to user JDBC to retrieve rows from a database. Now let’s try adding new rows.
Lesson22Inserter
In project Lesson-22-Example-01 there’s a file named poem1.txt:
100,To kiss a mug
101,That's like a cactus
102,Takes more nerve
103,Than it does practice
104, -- Burma ShaveThis is input to the job in class Lesson22Inserter.java. So let’s run that.
- In the Eclipse main menu, select Run/Run Configurations….
- Select Lesson22Reader in the left-hand pane and then click on the Duplicate button above the window; it’s the fourth button from the left. We’re copying the configuration to because the execution classpath is already set up there.
- Change the Name of the resulting configuration from “Lesson22Reader (1)” to “Lesson22Inserter,” and change the Main Class to org.hardknockjava.lesson22.exercise01.Lesson22Inserter.
- Click Run.
And the result on the console should be:
Prepared statement=INSERT INTO PUBLIC.LESSON22
(LINE_NO
, ENTRY_DATE
, ENTRY_TIME
, TEXT_LINE)
VALUES (100, '2022-12-05 +02', '2022-12-05 11:39:47.512+02', 'To kiss a mug')
...
Prepared statement=SELECT LINE_NO
,ENTRY_DATE
,ENTRY_TIME
,TEXT_LINE
FROM PUBLIC.LESSON22
WHERE LINE_NO >= 100
ORDER BY LINE_NO
Displaying the inserted rows
100 2022-12-05 2022-12-05 11:39:47.512 To kiss a mug
101 2022-12-05 2022-12-05 11:39:47.519 That's like a cactus
102 2022-12-05 2022-12-05 11:39:47.52 Takes more nerve
103 2022-12-05 2022-12-05 11:39:47.521 Than it does practice
104 2022-12-05 2022-12-05 11:39:47.522 -- Burma ShaveSo what have we done here? After initializing the connection, class Lesson22Inserter calls this method:
private void process() throws Exception {
// Set up a reader to read file poem1.txt.
Reader in = new FileReader("data/poem1.txt");
BufferedReader rdr = new BufferedReader(in);
String inputRecord = null;
// Keep track of the lowest line number read from the file.
int firstNewLine = Integer.MAX_VALUE;
// Each record of the file has a LINE_NO value and
// a text line, separated by a comma.
try {
while ((inputRecord = rdr.readLine()) != null) {
String[] fields = inputRecord.split(",");
if (fields.length > 1) {
int nextLine = Integer.parseInt(fields[0]);
if (nextLine < firstNewLine) {
firstNewLine = nextLine;
}
String line = fields[1];
// Create a new DBO using the line number and text
// read from the file, and the current date and timestamp.
java.util.Date today = new java.util.Date();
java.sql.Date newDate = new java.sql.Date(today.getTime());
java.sql.Timestamp newTimestamp = new java.sql.Timestamp(today.getTime());
Lesson22Dbo dbo = new Lesson22Dbo(nextLine, newDate, newTimestamp, line);
// Insert the object we've created.
dbo.insert();
}
}
// Commit our changes.
Lesson22Dbo.commit();
// Read all the new lines and display them on the console.
List<Lesson22Dbo> dumpList = Lesson22Dbo.selectAllDynamic(firstNewLine);
System.out.println();
System.out.println("Displaying the inserted rows");
Lesson22Dbo.dump(dumpList);
} catch (Exception e) {
Lesson22Dbo.rollback();
throw e;
} finally {
rdr.close();
}
}For each record, we create a new Lesson22Dbo instance containing the lineNo and textLine values from the input file together with the current date and time for entryDate and entryTime, and call insert() on the instance. When we’ve exhausted the input, we call Lesson22Dbo.commit() (a static method, since commit doesn’t apply to a single instance), and read and display the contents of the database starting with the lowest LINE_NO value we inserted.
And the insert() method? First, take a look at the static fields and executable code used for initialization:
private static final String INSERT_STMT = //
"INSERT INTO " + TABLE_NAME //
+ "\n (" + COL_LINE_NO //
+ "\n, " + COL_ENTRY_DATE //
+ "\n, " + COL_ENTRY_TIME //
+ "\n, " + COL_TEXT_LINE + ")"//
+ "\n VALUES (?, ?, ?, ?)";
private static PreparedStatement insertStmt = null;
...
public static void connect() throws Exception {
insertStmt = conn.prepareStatement(INSERT_STMT);
}And now, the insert() method itself.
// Insert this instance to the database.
public void insert() throws SQLException {
insertStmt.setInt(1, lineNo);
insertStmt.setDate(2, entryDate);
insertStmt.setTimestamp(3, entryTime);
insertStmt.setString(4, textLine);
displayPreparedStatement(insertStmt);
insertStmt.execute();
}The method sets each of the missing values in the PreparedStatement and then calls execute(). (displayPreparedStatement() is a method used in this example project to display the resulting SQL call on the console.)
Just for fun, try running Lesson22Inserter again….
SQLException
… and you’ll see this:
Prepared statement=INSERT INTO PUBLIC.LESSON22
(LINE_NO
, ENTRY_DATE
, ENTRY_TIME
, TEXT_LINE)
VALUES (100, '2022-12-05 +02', '2022-12-05 11:57:20.285+02', 'To kiss a mug')
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "lesson22_pkey"
Detail: Key (line_no)=(100) already exists.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
...
at org.hardknockjava.lesson22.exercise01.Lesson22Dbo.insert(Lesson22Dbo.java:276)
at org.hardknockjava.lesson22.exercise01.Lesson22Inserter.process(Lesson22Inserter.java:60)
at org.hardknockjava.lesson22.exercise01.Lesson22Inserter.main(Lesson22Inserter.java:17)
SQLState=23505
error code=0
message=ERROR: duplicate key value violates unique constraint "lesson22_pkey"
Detail: Key (line_no)=(100) already exists.You’ve triggered a SQLException by, as the message shows, trying to insert a row with LINE_NO = 100 where such a row already exists. In our table, LINE_NO is a unique index column: in must be unique for each row. Trying to insert a second row with an existing value leads to this error.
We caught this error in the main() method of Lesson22Inserter, and the catch block looks like this:
} catch (SQLException e) {
SQLException ex = (SQLException) e;
while (ex != null) {
ex.printStackTrace();
System.out.println(" SQLState=" + ex.getSQLState());
System.out.println(" error code=" + ex.getErrorCode());
System.out.println(" message=" + ex.getMessage());
ex = ex.getNextException();
}
}SQLException is a subclass of Exception that adds the getSQLState() and getErrorCode() methods. The values returned are vendor specific: 23505 in PostgreSQL means duplicate key and is returned by getSQLState(); but in Oracle for example, the value is 00001 and is returned by getErrorCode().
The example above shows how multiple SQLExceptions might be chained for a given database call.
Lesson22Updater
We’ve inserted some new rows. Let’s try updating. Set up the launch configuration for Lesson22Updater as you did for Lesson22Inserter, and try running.
This input for this job is file poem2.txt:
100,Dim your lights
101,Behind a car
102,Let folks see
103,How bright you areAnd the output looks like this:
Prepared statement=SELECT LINE_NO
,ENTRY_DATE
,ENTRY_TIME
,TEXT_LINE
FROM PUBLIC.LESSON22
WHERE LINE_NO = 100
ORDER BY LINE_NO
Prepared statement=UPDATE PUBLIC.LESSON22
SET ENTRY_DATE = '2022-12-05 +02'
, ENTRY_TIME = '2022-12-05 11:39:47.512+02'
, TEXT_LINE = 'Dim your lights'
WHERE LINE_NO = 100
...
Prepared statement=SELECT LINE_NO
,ENTRY_DATE
,ENTRY_TIME
,TEXT_LINE
FROM PUBLIC.LESSON22
WHERE LINE_NO >= 100
ORDER BY LINE_NO
Displaying the updated rows
100 2022-12-05 2022-12-05 11:39:47.512 Dim your lights
101 2022-12-05 2022-12-05 11:39:47.519 Behind a car
102 2022-12-05 2022-12-05 11:39:47.52 Let folks see
103 2022-12-05 2022-12-05 11:39:47.521 How bright you are
104 2022-12-05 2022-12-05 11:39:47.522 -- Burma ShaveIn this job, we read a record from the input, get values for LINE_NO and for TEXT_LINE, read the existing row from our table, and replace the value of TEXT_LINE. It’s almost the same as Lesson22Inserter.
private void process() throws Exception {
// Set up a reader to read file poem2.txt.
Reader in = new FileReader("data/poem2.txt");
BufferedReader rdr = new BufferedReader(in);
String inputRecord = null;
// Keep track of the lowest line number read from the file.
int firstUpdateLine = Integer.MAX_VALUE;
try {
while ((inputRecord = rdr.readLine()) != null) {
String[] fields = inputRecord.split(",");
if (fields.length > 1) {
int updateLine = Integer.parseInt(fields[0]);
if (updateLine < firstUpdateLine) {
firstUpdateLine = updateLine;
}
String line = fields[1];
// Read the row with the selected line number. If it
// exists, replace its textLine and update it.
Lesson22Dbo dbo = Lesson22Dbo.selectOneDynamic(updateLine);
if (dbo != null) {
dbo.setTextLine(line);
dbo.update();
}
}
}
// Commit our changes.
Lesson22Dbo.commit();
// Read all the updated lines and display them on the console.
List<Lesson22Dbo> dumpList = Lesson22Dbo.selectAllDynamic(firstUpdateLine);
System.out.println();
System.out.println("Displaying the updated rows");
Lesson22Dbo.dump(dumpList);
} catch (Exception e) {
Lesson22Dbo.rollback();
throw e;
} finally {
rdr.close();
}
}The code in Lesson22Dbo is:
private static final String UPDATE_STMT = //
"UPDATE " + TABLE_NAME //
+ "\n SET " + COL_ENTRY_DATE + " = ?" //
+ "\n , " + COL_ENTRY_TIME + " = ?" //
+ "\n , " + COL_TEXT_LINE + " = ?" //
+ "\n WHERE " + COL_LINE_NO + " = ?";
private static PreparedStatement updateStmt = null;
...
public static void connect() throws Exception {
updateStmt = conn.prepareStatement(UPDATE_STMT);
...
}
...
public int update() throws SQLException {
updateStmt.setDate(1, entryDate);
updateStmt.setTimestamp(2, entryTime);
updateStmt.setString(3, textLine);
updateStmt.setInt(4, lineNo);
displayPreparedStatement(updateStmt);
return updateStmt.executeUpdate();
}The most interesting difference between execute(), which we used for an INSERT, and executeUpdate(), used for UPDATE, is that the latter returns a value: the number of rows affected. execute() works for UPDATE operations too, but you don’t get the return value. Likewise, you can use executeUpdate() for INSERT–it is possible to insert more than one row in a single call–but that’s another story. (execute() can also return results, but it’s more complicated and we recommend you check the PreparedStatement documentation for details.)
Lesson22Deleter
Now, to come full circle, we’ll delete the rows we’ve just inserted and then updated. Create the launch configuration for Lesson22Deleter as you did for Lesson22Inserter and try running it. Here’s the output you should expect:
Prepared statement=DELETE FROM PUBLIC.LESSON22 WHERE LINE_NO >= 100
...
5 lines were deleted
0 lines were deleted
0 lines were deleted
0 lines were deleted
0 lines were deleted
5 total lines were deleted.
Prepared statement=SELECT LINE_NO
,ENTRY_DATE
,ENTRY_TIME
,TEXT_LINE
FROM PUBLIC.LESSON22
WHERE LINE_NO >= -2147483648
ORDER BY LINE_NO
Displaying all rows
1 2022-11-27 2022-11-27 09:55:20.813813 Shoo-ba-dee, Shoo-ba-dee
2 2022-11-27 2022-11-27 09:55:20.813813 Cannonball Adderly
3 2022-11-27 2022-11-27 09:55:20.813813 Came on the scene
4 2022-11-27 2022-11-27 09:55:20.813813 Like a bolt from the blues.
5 2022-11-27 2022-11-27 09:55:20.813813 His popularity
6 2022-11-27 2022-11-27 09:55:20.813813 Coincidentally
7 2022-11-27 2022-11-27 09:55:20.813813 Left me more leisure for
8 2022-11-27 2022-11-27 09:55:20.813813 Romance and booze.
9 2022-11-27 2022-11-27 09:55:20.813813 (no text here)
10 2022-11-27 2022-11-27 09:55:20.813813 -- Paul Desmond
This job reads poem1.txt, the same file Lesson22Inserter did, and deletes the rows whose LINE_NO values appear there–which are, of course, those we inserted before.
This time around, we’ve done things a little differently. Instead of sending a DELETE call to the RDBMS for each input record, we’ve added each one to a batch and called the RDBMS just once.
private void process() throws Exception {
...
// Save the LINE_NO values of rows we'll delete from the table.
List<Integer> lineNoList = new ArrayList<Integer>();
try {
while ((inputRecord = rdr.readLine()) != null) {
String[] fields = inputRecord.split(",");
if (fields.length > 0) {
Integer deleteLine = Integer.parseInt(fields[0]);
lineNoList.add(deleteLine);
}
}
// Delete all the selected rows in a batch call.
int[] deleteCounts = Lesson22Dbo.batchDelete(lineNoList);
int deleteTotal = 0;
// Display the number of rows deleted by each DELETE
// statement in the batch.
for (int deleteCount : deleteCounts) {
System.out.println(deleteCount + " lines were deleted");
deleteTotal += deleteCount;
}
// Commit our changes.
Lesson22Dbo.commit();
System.out.println();
System.out.println(deleteTotal + " total lines were deleted.");
...
}
// Delete the rows with the provided LINE_NO values as a batch.
public static int[] batchDelete(List<Integer> lineNoList) throws SQLException {
for (Integer lineNo : lineNoList) {
deleteStmt.setInt(1, lineNo);
deleteStmt.addBatch();
displayPreparedStatement(deleteStmt);
}
return deleteStmt.executeBatch();
}See how executeBatch() returns an array of int values. These reflect the outcome of each of the statements in the batch. In this case, it’s the number of rows deleted.
Since our batchDelete() method doesn’t apply to a single row, it’s static.
Also notice that the first call deletes all rows where LINE_NO is equal to or greater than 100. There are 5 such rows, and the first call deletes all of them–so the first line of delete counts says “5 lines were deleted.” Of course, this leaves no rows for the rest of the batch, so the console reads “0 lines were deleted.”
The return from executeBatch() might indicate other things: see the PreparedStatement documentation for more information.
Now let’s take a look at JDBC metadata.