Estude fácil! Tem muito documento disponível na Docsity
Ganhe pontos ajudando outros esrudantes ou compre um plano Premium
Prepare-se para as provas
Estude fácil! Tem muito documento disponível na Docsity
Prepare-se para as provas com trabalhos de outros alunos como você, aqui na Docsity
Encontra documentos específicos para os exames da tua universidade
Prepare-se com as videoaulas e exercícios resolvidos criados a partir da grade da sua Universidade
Responda perguntas de provas passadas e avalie sua preparação.
Ganhe pontos para baixar
Ganhe pontos ajudando outros esrudantes ou compre um plano Premium
Programación Avanzada de Base de Datos con Java
Tipologia: Resumos
1 / 26
Esta página não é visível na pré-visualização
Não perca as partes importantes!
35-2 Chapter 35 Advanced Java Database Programming
35.1 Introduction
35.2 A Universal SQL Client
Point
Key
Point
Key
Listing 35.1 SQLClient.java
1 import java.sql.*; 2 import javafx.application.Application; 3 import javafx.collections.FXCollections; 4 import javafx.geometry.Pos; 5 import javafx.scene.Scene; 6 import javafx.scene.control.Button; 7 import javafx.scene.control.ComboBox; 8 import javafx.scene.control.Label; 9 import javafx.scene.control.PasswordField; 10 import javafx.scene.control.ScrollPane; 11 import javafx.scene.control.TextArea; 12 import javafx.scene.control.TextField; 13 import javafx.scene.layout.BorderPane; 14 import javafx.scene.layout.GridPane; 15 import javafx.scene.layout.HBox; 16 import javafx.scene.layout.VBox;
35-4 Chapter 35 Advanced Java Database Programming
78 gridPane, hBoxConnection); 79 80 gridPane.setStyle( "-fx-border-color: black;" ); 81 82 HBox hBoxSQLCommand = new HBox( 5 ); 83 hBoxSQLCommand.getChildren().addAll( 84 btClearSQLCommand, btExecuteSQL); 85 hBoxSQLCommand.setAlignment(Pos.CENTER_RIGHT); 86 87 BorderPane borderPaneSqlCommand = new BorderPane(); 88 borderPaneSqlCommand.setTop( 89 new Label( "Enter an SQL Command" )); 90 borderPaneSqlCommand.setCenter( 91 new ScrollPane(tasqlCommand)); 92 borderPaneSqlCommand.setBottom( 93 hBoxSQLCommand); 94 95 HBox hBoxConnectionCommand = new HBox( 10 ); 96 hBoxConnectionCommand.getChildren().addAll( 97 vBoxConnection, borderPaneSqlCommand); 98 99 BorderPane borderPaneExecutionResult = new BorderPane(); 100 borderPaneExecutionResult.setTop( 101 new Label( "SQL Execution Result" )); 102 borderPaneExecutionResult.setCenter(taSQLResult); 103 borderPaneExecutionResult.setBottom(btClearSQLResult); 104 105 BorderPane borderPane = new BorderPane(); 106 borderPane.setTop(hBoxConnectionCommand); 107 borderPane.setCenter(borderPaneExecutionResult); 108 109 // Create a scene and place it in the stage 110 Scene scene = new Scene(borderPane, 670 , 400 ); 111 primaryStage.setTitle( "SQLClient" ); // Set the stage title 112 primaryStage.setScene(scene); // Place the scene in the stage 113 primaryStage.show(); // Display the stage 114 115 btConnectDB.setOnAction(e -> connectToDB()); 116 btExecuteSQL.setOnAction(e -> executeSQL()); 117 btClearSQLCommand.setOnAction(e -> tasqlCommand.setText( null )); 118 btClearSQLResult.setOnAction(e -> taSQLResult.setText( null )); 119 } 120 121 /** Connect to DB */ 122 private void connectToDB() { 123 // Get database information from the user input 124 String driver = cboDriver 125 .getSelectionModel().getSelectedItem(); 126 String url = cboURL.getSelectionModel().getSelectedItem(); 127 String username = tfUsername.getText().trim(); 128 String password = pfPassword.getText().trim(); 129 130 // Connection to the database 131 try { 132 Class.forName(driver); 133 connection = DriverManager.getConnection( 134 url, username, password); 135 lblConnectionStatus.setText( "Connected to " + url); 136 } 137 catch (java.lang.Exception ex) { 138 ex.printStackTrace();
35.2 A Universal SQL Client 35-
142 /** Execute SQL commands / 143 private void executeSQL() { 144 if (connection == null ) { 145 taSQLResult.setText( "Please connect to a database first" ); 146 return ; 147 } 148 else { 149 String sqlCommands = tasqlCommand.getText().trim(); 150 String[] commands = sqlCommands.replace( '\n' , ' ' ).split( ";" ); 151 152 for (String aCommand: commands) { 153 if (aCommand.trim().toUpperCase().startsWith( "SELECT" )) { 154 processSQLSelect(aCommand); 155 } 156 else { 157 processSQLNonSelect(aCommand); 158 } 159 } 160 } 161 } 162 163 /* Execute SQL SELECT commands */ 164 private void processSQLSelect(String sqlCommand) { 165 try { 166 // Get a new statement for the current connection 167 statement = connection.createStatement(); 168 169 // Execute a SELECT SQL command 170 ResultSet resultSet = statement.executeQuery(sqlCommand); 171 172 // Find the number of columns in the result set 173 int columnCount = resultSet.getMetaData().getColumnCount(); 174 String row = "" ; 175 176 // Display column names 177 for ( int i = 1 ; i <= columnCount; i++) { 178 row += resultSet.getMetaData().getColumnName(i) + "\t" ; 179 } 180 181 taSQLResult.appendText(row + '\n' ); 182 183 while (resultSet.next()) { 184 // Reset row to empty 185 row = "" ; 186 187 for ( int i = 1 ; i <= columnCount; i++) { 188 // A non-String column is converted to a string 189 row += resultSet.getString(i) + "\t" ; 190 } 191 192 taSQLResult.appendText(row + '\n' ); 193 } 194 } 195 catch (SQLException ex) { 196 taSQLResult.setText(ex.toString()); 197 } 198 } 199
35.3 Batch Processing 35-
statement.addBatch("insert into T values (100, 'Smith')"); statement.addBatch("insert into T values (200, 'Jones')");
// Execute the batch int count[] = statement.executeBatch();
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.
create table Person ( firstName varchar ( 20 ), mi char ( 1 ), lastName varchar ( 20 ) )
Listing 35.2 CopyFileToTable.java
1 import java.io.File; 2 import java.io.FileNotFoundException; 3 import java.io.IOException; 4 import java.sql.*; 5 import java.util.Scanner; 6 import javafx.application.Application; 7 import javafx.collections.FXCollections; 8 import javafx.geometry.Pos; 9 import javafx.scene.Scene; 10 import javafx.scene.control.Button; 11 import javafx.scene.control.ComboBox; 12 import javafx.scene.control.Label; 13 import javafx.scene.control.PasswordField; 14 import javafx.scene.control.SplitPane;
35-8 Chapter 35 Advanced Java Database Programming
15 import javafx.scene.control.TextArea; 16 import javafx.scene.control.TextField; 17 import javafx.scene.layout.BorderPane; 18 import javafx.scene.layout.GridPane; 19 import javafx.scene.layout.HBox; 20 import javafx.scene.layout.VBox; 21 import javafx.stage.Stage; 22 23 public class CopyFileToTable extends Application { 24 // Text file info 25 private TextField tfFilename = new TextField(); 26 private TextArea taFile = new TextArea(); 27 28 // JDBC and table info 29 private ComboBox
35-10 Chapter 35 Advanced Java Database Programming
138 // Establish a connection 139 Connection conn = DriverManager.getConnection( 140 cboURL.getSelectionModel().getSelectedItem().trim(), 141 tfUsername.getText().trim(), 142 String.valueOf(pfPassword.getText()).trim()); 143 System.out.println( "Database connected" ); 144 145 // Read each line from the text file and insert it to the table 146 insertRows(conn); 147 } 148 149 private void insertRows(Connection connection) { 150 // Build the SQL INSERT statement 151 String sqlInsert = "insert into " + tfTableName.getText() 152 + " values (" ; 153 154 // Use a Scanner to read text from the file 155 Scanner input = null ; 156 157 // Get file name from the text field 158 String filename = tfFilename.getText().trim(); 159 160 try { 161 // Create a scanner 162 input = new Scanner( new File(filename)); 163 164 // Create a statement 165 Statement statement = connection.createStatement(); 166 167 System.out.println( "Driver major version? " + 168 connection.getMetaData().getDriverMajorVersion()); 169 170 // Determine if batchUpdatesSupported is supported 171 boolean batchUpdatesSupported = false ; 172 173 try { 174 if (connection.getMetaData().supportsBatchUpdates()) { 175 batchUpdatesSupported = true ; 176 System.out.println( "batch updates supported" ); 177 } 178 else { 179 System.out.println( "The driver " + 180 "does not support batch updates" ); 181 } 182 } 183 catch (UnsupportedOperationException ex) { 184 System.out.println( "The operation is not supported" ); 185 } 186 187 // Determine if the driver is capable of batch updates 188 if (batchUpdatesSupported) { 189 // Read a line and add the insert table command to the batch 190 while (input.hasNext()) { 191 statement.addBatch(sqlInsert + input.nextLine() + ")" ); 192 } 193 194 statement.executeBatch(); 195 196 lblStatus.setText( "Batch updates completed" ); 197 }
35.4 Scrollable and Updatable Result Set 35-
198 else { 199 // Read a line and execute insert table command 200 while (input.hasNext()) { 201 statement.executeUpdate(sqlInsert + input.nextLine() + ")" ); 202 } 203 204 lblStatus.setText( "Single row update completed" ); 205 } 206 } 207 catch (SQLException ex) { 208 System.out.println(ex); 209 } 210 catch (FileNotFoundException ex) { 211 System.out.println( "File not found: " + filename); 212 } 213 finally { 214 if (input != null ) input.close(); 215 } 216 } 217 }
35.3.1 What is batch processing in JDBC? What are the benefits of using batch processing?
35.3.2 How do you add an SQL statement to a batch? How do you execute a batch?
35.3.3 Can you execute a SELECT statement in a batch?
35.3.4 How do you know whether a JDBC driver supports batch updates?
35.4 Scrollable and Updatable Result Set
Statement statement = connection.createStatement ( int resultSetType, int resultSetConcurrency);
Point
Check
Point
Key
35.4 Scrollable and Updatable Result Set 35-
6 // Load the JDBC driver 7 Class.forName( "oracle.jdbc.driver.OracleDriver" ); 8 System.out.println( "Driver loaded" ); 9 10 // Connect to a database 11 Connection connection = DriverManager.getConnection 12 ( "jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl" , 13 "scott" , "tiger" ); 14 connection.setAutoCommit( true ); 15 System.out.println( "Database connected" ); 16 17 // Get a new statement for the current connection 18 Statement statement = connection.createStatement( 19 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); 20 21 // Get ResultSet 22 ResultSet resultSet = statement.executeQuery 23 ( "select state, capital from StateCapital" ); 24 25 System.out.println( "Before update " ); 26 displayResultSet(resultSet); 27 28 // Update the second row 29 resultSet.absolute( 2 ); // Move cursor to the second row 30 resultSet.updateString( "state" , "New S" ); // Update the column 31 resultSet.updateString( "capital" , "New C" ); // Update the column 32 resultSet.updateRow(); // Update the row in the data source 33 34 // Insert after the last row 35 resultSet.last(); 36 resultSet.moveToInsertRow(); // Move cursor to the insert row 37 resultSet.updateString( "state" , "Florida" ); 38 resultSet.updateString( "capital" , "Tallahassee" ); 39 resultSet.insertRow(); // Insert the row 40 resultSet.moveToCurrentRow(); // Move the cursor to the current row 41 42 // Delete fourth row 43 resultSet.absolute( 4 ); // Move cursor to the 5th row 44 resultSet.deleteRow(); // Delete the second row 45 46 System.out.println( "After update " ); 47 resultSet = statement.executeQuery 48 ( "select state, capital from StateCapital" ); 49 displayResultSet(resultSet); 50 51 // Close the connection 52 resultSet.close(); 53 } 54 55 private static void displayResultSet(ResultSet resultSet) 56 throws SQLException { 57 ResultSetMetaData rsMetaData = resultSet.getMetaData(); 58 resultSet.beforeFirst(); 59 while (resultSet.next()) { 60 for ( int i = 1 ; i <= rsMetaData.getColumnCount(); i++) 61 System.out.printf( "%-12s\t" , resultSet.getObject(i)); 62 System.out.println(); 63 } 64 } 65 }
35-14 Chapter 35 Advanced Java Database Programming
Note Not all current drivers support scrollable and updatable result sets. The example is tested using Oracle ojdbc6 driver. You can use supportsResultSetType(int type) and supportsResultSetConcurrency(int type, int concurrency) in the DatabaseMetaData interface to find out which result type and currency modes are supported by the JDBC driver. But even if a driver supports the scrollable and updatable result set, a result set for a complex query might not be able to perform an update. For example, the result set for a query that involves several tables is likely not to support update operations.
Note The program may not work due to an issue in the Oracle JDBC driver if lines 22–23 are replaced by
ResultSet resultSet = statement.executeQuery ( "select * from StateCapital" );
35.4.1 What is a scrollable result set? What is an updatable result set? 35.4.2 How do you create a scrollable and updatable ResultSet? 35.4.3 How do you know whether a JDBC driver supports a scrollable and updatable
Point
Check
35-16 Chapter 35 Advanced Java Database Programming
5 public class SimpleRowSet { 6 public static void main(String[] args) 7 throws SQLException, ClassNotFoundException { 8 // Load the JDBC driver 9 Class.forName( "com.mysql.jdbc.Driver" ); 10 System.out.println( "Driver loaded" ); 11 12 // Create a row set 13 RowSet rowSet = new JdbcRowSetImpl(); 14 15 // Set RowSet properties 16 rowSet.setUrl( "jdbc:mysql://localhost/javabook" ); 17 rowSet.setUsername( "scott" ); 18 rowSet.setPassword( "tiger" ); 19 rowSet.setCommand( "select firstName, mi, lastName " + 20 "from Student where lastName = 'Smith'" ); 21 rowSet.execute(); 22 23 // Iterate through the result and print the student names 24 while (rowSet.next()) 25 System.out.println(rowSet.getString( 1 ) + "\t" + 26 rowSet.getString( 2 ) + "\t" + rowSet.getString( 3 )); 27 28 // Close the connection 29 rowSet.close(); 30 } 31 }
Tip Since RowSet is a subinterface of ResultSet , all the methods in ResultSet can be used in RowSet. For example, you can obtain ResultSetMetaData from a RowSet using the getMetaData() method.
35.5.2 RowSet for PreparedStatement
Listing 35.5 RowSetPreparedStatement.java
1 import java.sql.; 2 import javax.sql.RowSet; 3 import com.sun.rowset.; 4
35.5 RowSet, JdbcRowSet, and CachedRowSet 35-
5 public class RowSetPreparedStatement { 6 public static void main(String[] args) 7 throws SQLException, ClassNotFoundException { 8 // Load the JDBC driver 9 Class.forName( "com.mysql.jdbc.Driver" ); 10 System.out.println( "Driver loaded" ); 11 12 // Create a row set 13 RowSet rowSet = new JdbcRowSetImpl(); 14 15 // Set RowSet properties 16 rowSet.setUrl( "jdbc:mysql://localhost/javabook" ); 17 rowSet.setUsername( "scott" ); 18 rowSet.setPassword( "tiger" ); 19 rowSet.setCommand( "select * from Student where lastName =? " + 20 "and mi = ?" ); 21 rowSet.setString( 1 , "Smith" ); 22 rowSet.setString( 2 , "R" ); 23 rowSet.execute(); 24 25 ResultSetMetaData rsMetaData = rowSet.getMetaData(); 26 for ( int i = 1 ; i <= rsMetaData.getColumnCount(); i++) 27 System.out.printf( "%-12s\t" , rsMetaData.getColumnName(i)); 28 System.out.println(); 29 30 // Iterate through the result and print the student names 31 while (rowSet.next()) { 32 for ( int i = 1 ; i <= rsMetaData.getColumnCount(); i++) 33 System.out.printf( "%-12s\t" , rowSet.getObject(i)); 34 System.out.println(); 35 } 36 37 // Close the connection 38 rowSet.close(); 39 } 40 }
35.5.3 Scrolling and Updating RowSet
Listing 35.6 ScrollUpdateRowSet.java
1 import java.sql.*; 2 import javax.sql.RowSet; 3 import com.sun.rowset.JdbcRowSetImpl; 4 5 public class ScrollUpdateRowSet { 6 public static void main(String[] args) 7 throws SQLException, ClassNotFoundException { 8 // Load the JDBC driver 9 Class.forName( "com.mysql.jdbc.Driver" ); 10 System.out.println( "Driver loaded" ); 11
35.5 RowSet, JdbcRowSet, and CachedRowSet 35-
35.5.4 RowSetEvent
Listing 35.7 TestRowSetEvent.java
1 import java.sql.; 2 import javax.sql.; 3 import com.sun.rowset.*; 4 5 public class TestRowSetEvent { 6 public static void main(String[] args) 7 throws SQLException, ClassNotFoundException { 8 // Load the JDBC driver 9 Class.forName( "com.mysql.jdbc.Driver" ); 10 System.out.println( "Driver loaded" ); 11 12 // Create a row set 13 RowSet rowSet = new JdbcRowSetImpl(); 14 rowSet.addRowSetListener( new RowSetListener() { 15 public void cursorMoved(RowSetEvent e) { 16 System.out.println( "Cursor moved" ); 17 } 18 19 public void rowChanged(RowSetEvent e) { 20 System.out.println( "Row changed" ); 21 } 22 23 public void rowSetChanged(RowSetEvent e) { 24 System.out.println( "row set changed" ); 25 } 26 }); 27 28 // Set RowSet properties 29 rowSet.setUrl( "jdbc:mysql://localhost/javabook" ); 30 rowSet.setUsername( "scott" ); 31 rowSet.setPassword( "tiger" ); 32 rowSet.setCommand( "select * from Student" ); 33 rowSet.execute(); 34 35 rowSet.last(); // Cursor moved 36 rowSet.updateString( "lastName" , "Yao" ); // Update column 37 rowSet.updateRow(); // Row updated 38 39 // Close the connection 40 rowSet.close(); 41 } 42 }
35-20 Chapter 35 Advanced Java Database Programming
35.5.1 What are the advantages of RowSet? 35.5.2 What are JdbcRowSet and CachedRowSet? What are the differences between
35.5.3 How do you create a JdbcRowSet and a CachedRowSet? 35.5.4 Can you scroll and update a RowSet? What method must be invoked to write the
35.5.5 Describe the handlers in RowSetListener.
35.6 Storing and Retrieving Images in JDBC
create table Country(name varchar( 30 ), flag blob, description varchar( 255 ));
Note MS Access database does not support the BLOB and CLOB types.
PreparedStatement pstmt = connection.prepareStatement( "insert into Country values(?, ?, ?)");
// Store image to the table cell File file = new File(imageFilename); InputStream inputImage = new FileInputStream(file); pstmt.setBinaryStream( 2 , inputImage, ( int )(file.length()));
// Store image to the table cell Blob blob = rs.getBlob( 1 ); ImageIcon imageIcon = new ImageIcon( blob.getBytes( 1 , ( int )blob.length()));
Point
Check
Point
Key
