36.8. Chapter Summary
33.3. Batch Processing
In all the preceding examples, SQL commands are submitted to the database for execution one at a time. This is inefficient for processing a large number of updates. For example, suppose you wanted to insert a thousand rows into a table. Submitting one INSERT command at a time would take nearly a thousand times longer than submitting all the INSERT commands in a batch at once. To improve performance, JDBC 2 introduced the batch update for processing nonselect SQL commands. A batch update consists of a sequence of nonselect SQL commands. These commands are collected in a batch and submitted to the database all together.
To use the batch update, you add nonselect commands to a batch using the addBatch method in the Statement interface. After all the SQL commands are added to the batch, use the executeBatch method to submit the batch to the database for execution.
For example, the following code adds a create table command, two insert statements in a batch, and executes the batch:
Statement statement = connection.createStatement(); // Add SQL commands to the batch statement.addBatch( "create table T (C1 integer, C2 varchar(15))" ); statement.addBatch( "insert into T values (100, 'Smith')" ); statement.addBatch( "insert into T values (200, 'Jones')" ); // Execute the batch int count[] = statement.executeBatch() ;
The executeBatch() method returns an array of counts, each of which counts the number of rows affected by the SQL command. The first count returns because it is a DDL command. The other counts return 1 because only one row is affected.
Note
| | To find out whether a driver supports batch updates, invoke supportsBatchUpdates() on a DatabaseMetaData instance. If the driver supports batch updates, it will return true . The JDBC drivers for MySQL, Access, and Oracle all support batch updates. |
To demonstrate batch processing, consider writing a program that gets data from a text file and copies the data from the text file to a table, as shown in Figure 33.2. The text file consists of lines that each corresponds to a row in the table. The fields in a row are separated by commas. The string values in a row are enclosed in single quotes. You can view the text file by clicking the View File button and copy the text to the table by clicking the Copy button. The table must already be defined in the database. Figure 33.2 shows the text file table.txt copied to table Person. Person is created using the following statement:
create table Person ( firstName varchar ( 20 ), mi char ( 1 ), lastName varchar ( 20 ) )
Figure 33.2. The CopyFileToTable utility copies text files to database tables.
Listing 33.2 gives the solution to the problem.
Listing 33.2. CopyFileToTable.java
(This item is displayed on pages 1131 - 1135 in the print version)
1 import javax.swing.*; 2 import javax.swing.border.*; 3 import java.awt.*; 4 import java.awt.event.*; 5 import java.io.*; 6 import java.sql.*; 7 import java.util.*; 8 9 public class CopyFileToTable extends JFrame { 10 // Text file info 11 private JTextField jtfFilename = new JTextField(); 12 private JTextArea jtaFile = new JTextArea(); 13 14 // JDBC and table info 15 private JComboBox jcboDriver = new JComboBox( new String[] { 16 "com.mysql.jdbc.Driver" , "sun.jdbc.odbc.JdbcOdbcDriver" , 17 "oracle.jdbc.driver.OracleDriver" }); 18 private JComboBox jcboURL = new JComboBox( new String[] { 19 "jdbc:mysql: //localhost/test", "jdbc:odbc:exampleMDBDataSource", 20 "jdbc:oracle:thin:@liang.armstrong.edu: 1521:orcl" ); 21 private JTextField jtfUsername = new JTextField(); 22 private JPasswordField jtfPassword = new JPasswordField(); 23 private JTextField jtfTableName = new JTextField(); 24 25 private JButton jbtViewFile = new JButton( "View File" ); 26 private JButton jbtCopy = new JButton( "Copy" ); 27 private JLabel jlblStatus = new JLabel(); 28 29 public CopyFileToTable() { 30 JPanel jPane1 = new JPanel(); 31 jPane1.setLayout( new BorderLayout()); 32 jPane1.add( new JLabel( "Filename" ), BorderLayout.WEST); 33 jPane1.add(jbtViewFile, BorderLayout.EAST); 34 jPane1.add(jtfFilename, BorderLayout.CENTER); 35 36 JPanel jPane2 = new JPanel(); 37 jPane2.setLayout( new BorderLayout()); 38 jPane2.setBorder( new TitledBorder( "Source Text File" )); 39 jPane2.add(jPane1, BorderLayout.NORTH); 40 jPane2.add( new JScrollPane(jtaFile), BorderLayout.CENTER); 41 42 JPanel jPane3 = new JPanel(); 43 jPane3.setLayout( new GridLayout( 5 , )); 44 jPane3.add( new JLabel( "JDBC Driver" )); 45 jPane3.add( new JLabel( "Database URL" )); 46 jPane3.add( new JLabel( "Username" )); 47 jPane3.add( new JLabel( "Password" )); 48 jPane3.add( new JLabel( "Table Name " )); 49 50 JPanel jPane4 = new JPanel(); 51 jPane4.setLayout( new GridLayout( 5 , )); 52 jcboDriver.setEditable( true ); 53 jPane4.add(jcboDriver); 54 jcboURL.setEditable( true ); 55 jPane4.add(jcboURL); 56 jPane4.add(jtfUsername); 57 jPane4.add(jtfPassword); 58 jPane4.add(jtfTableName); 59 60 JPanel jPane5 = new JPanel(); 61 jPane5.setLayout( new BorderLayout()); 62 jPane5.setBorder( new TitledBorder( "Target Database Table" )); 63 jPane5.add(jbtCopy, BorderLayout.SOUTH); 64 jPane5.add(jPane3, BorderLayout.WEST); 65 jPane5.add(jPane4, BorderLayout.CENTER); 66 67 add(jlblStatus, BorderLayout.SOUTH); 68 add( new JSplitPane(JSplitPane.HORIZONTAL_SPLIT, 69 jPane2, jPane5), BorderLayout.CENTER); 70 71 jbtViewFile.addActionListener( new ActionListener() { 72 public void actionPerformed(ActionEvent evt) { 73 showFile(); 74 } 75 }); 76 77 jbtCopy.addActionListener( new ActionListener() { 78 public void actionPerformed(ActionEvent evt) { 79 try { 80 copyFile(); 81 } 82 catch (Exception ex) { 83 jlblStatus.setText(ex.toString()); 84 } 85 } 86 }); 87 } 88 89 /** Display the file in the text area */ 90 private void showFile() { 91 Scanner input = null ; 92 try { 93 // Use a Scanner to read text from the file 94 input = new Scanner( new File(jtfFilename.getText().trim())); 95 96 // Read a line and append the line to the text area 97 while (input.hasNext()) 98 jtaFile.append(input.nextLine() + '\n' ); 99 } 100 catch (FileNotFoundException ex) { 101 System.out.println( "File not found: " + jtfFilename.getText()); 102 } 103 catch (IOException ex) { 104 ex.printStackTrace(); 105 } 106 finally { 107 if (input != null ) input.close(); 108 } 109 } 110 111 private void copyFile() throws Exception { 112 // Load the JDBC driver 113 Class.forName(((String)jcboDriver.getSelectedItem()).trim()); 114 System.out.println( "Driver loaded" ); 115 116 // Establish a connection 117 Connection conn = DriverManager.getConnection 118 (((String)jcboURL.getSelectedItem()).trim(), 119 jtfUsername.getText().trim(), 120 String.valueOf(jtfPassword.getPassword()).trim()); 121 System.out.println( "Database connected" ); 122 123 // Read each line from the text file and insert it to the table 124 insertRows(conn); 125 } 126 127 private void insertRows(Connection connection) { 128 // Build the SQL INSERT statement 129 String sqlInsert = "insert into " + jtfTableName.getText() 130 + " values (" ; 131 132 // Use a Scanner to read text from the file 133 Scanner input = null ; 134 135 // Get file name from the text field 136 String filename = jtfFilename.getText().trim(); 137 138 try { 139 // Create a scanner 140 input = new Scanner( new File(filename)); 141 142 // Create a statement 143 Statement statement = connection.createStatement(); 144 145 System.out.println( "Driver major version? " + 146 connection.getMetaData().getDriverMajorVersion()); 147 148 // Determine if batchUpdatesSupported is supported 149 boolean batchUpdatesSupported = false ; 150 151 try { 152 if (connection.getMetaData().supportsBatchUpdates() ) { 153 batchUpdatesSupported = true ; 154 System.out.println( "batch updates supported" ); 155 } 156 else { 157 System.out.println( "The driver is of JDBC 2 type, but " + 158 "does not support batch updates" ); 159 } 160 } 161 catch (UnsupportedOperationException ex) { 162 System.out.println( "The driver does not support JDBC 2" ); 163 } 164 165 // Determine if the driver is capable of batch updates 166 if (batchUpdatesSupported) { 167 // Read a line and add the insert table command to the batch 168 while (input.hasNext()) { 169 statement.addBatch(sqlInsert + input.nextLine() + ")" ); 170 } 171 172 statement.executeBatch(); 173 174 jlblStatus.setText( "Batch updates completed" ); 175 } 176 else { 177 // Read a line and execute insert table command 178 while (input.hasNext()) { 179 statement.executeUpdate(sqlInsert + input.nextLine() + ")" ); 180 } 181 182 jlblStatus.setText( "Single row update completed" ); 183 } 184 } 185 catch (SQLException ex) { 186 System.out.println(ex); 187 } 188 catch (FileNotFoundException ex) { 189 System.out.println( "File not found: " + filename); 190 } 191 catch (IOException ex) { 192 ex.printStackTrace(); 193 } 194 finally { 195 if (input != null ) input.close(); 196 } 197 } 198 } |
The insertRows method (lines 127 “197) uses the batch updates to submit SQL INSERT commands to the database for execution, if the driver supports batch updates. Lines 168 “175 check whether the driver supports batch updates. If the driver is not JDBC 2 compatible, an UnsupportedOperationException exception will be thrown (line 152) when the supports-BatchUpdates() method is invoked.
The tables must already be created in the database. The file format and contents must match the database table specification. Otherwise, the SQL INSERT command will fail.
In Exercise 33.1, you will write a program to insert a thousand records to a database and compare the performance with and without batch updates.
EAN: N/A
Pages: 503