java:jdbc
Differences
This shows you the differences between two versions of the page.
Previous revision | |||
— | java:jdbc [2022/12/02 22:02] (current) – external edit 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== JDBC ====== | ||
+ | |||
+ | [[http:// | ||
+ | |||
+ | ===== Un ejemplo sencillo ===== | ||
+ | |||
+ | <code java> | ||
+ | // Creamos un objeto conexión especificando la clase | ||
+ | // de driver que vamos a usar | ||
+ | Connection con = DriverManager.getConnection | ||
+ | ( " | ||
+ | |||
+ | Statement stmt = con.createStatement(); | ||
+ | ResultSet rs = stmt.executeQuery(" | ||
+ | |||
+ | // recorremos el resulset. rs está en la posición 0, | ||
+ | // de tal forma que la llamada a rs.next() lo posiciona | ||
+ | // en el primer elemento | ||
+ | while (rs.next()) | ||
+ | { | ||
+ | int x = rs.getInt(" | ||
+ | String s = rs.getString(" | ||
+ | float f = rs.getFloat(" | ||
+ | } | ||
+ | |||
+ | </ | ||
+ | |||
+ | ===== Ejemplo de consulta preparada ===== | ||
+ | |||
+ | Las consultas preparadas son altamente intersantes por cuestiones de eficacia, seguridad y rendimiento. | ||
+ | |||
+ | ==== Cómo funciona una consulta preparada ==== | ||
+ | |||
+ | El cliente primero manda la consulta, que es algo del tipo " | ||
+ | |||
+ | La base de datos, una vez que ha recibido la consulta, la puede compilar y optimizar. | ||
+ | |||
+ | A continuación, | ||
+ | |||
+ | Las ventajas son evidentes: ya que en casos de consultas que se repitan con frecuencia, nos ahorramos el paso de compilar y optimizar. | ||
+ | |||
+ | Además tenemos una ventaja en seguridad, ya que es mucho más difícil cometer el nefasto error de la inyección SQL. | ||
+ | |||
+ | <code java> | ||
+ | import java.sql.*; | ||
+ | |||
+ | public class UpdateCar { | ||
+ | |||
+ | public static void UpdateCarNum(int carNo, int empNo) | ||
+ | throws SQLException { | ||
+ | Connection con = null; | ||
+ | PreparedStatement pstmt = null; | ||
+ | |||
+ | try { | ||
+ | con = DriverManager.getConnection(" | ||
+ | |||
+ | pstmt = con.prepareStatement( | ||
+ | " | ||
+ | "WHERE EMPLOYEE_NUMBER = ?"); | ||
+ | pstmt.setInt(1, | ||
+ | pstmt.setInt(2, | ||
+ | pstmt.executeUpdate(); | ||
+ | } | ||
+ | finally { | ||
+ | if (pstmt != null) pstmt.close(); | ||
+ | } | ||
+ | } | ||
+ | } | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== Recogiendo valores de los resultsets ===== | ||
+ | |||
+ | Los resultSets son los objetos que almacenan los resultados de una consulta. | ||
+ | |||
+ | A la hora de crear el comando se puede configurar el tipo de resultset que queremos | ||
+ | que nos devuelva la consulta una vez ejecutada: | ||
+ | |||
+ | <code java> | ||
+ | Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, | ||
+ | | ||
+ | ResultSet srs = stmt.executeQuery(" | ||
+ | </ | ||
+ | |||
+ | ^ Valor ^ Qué significa ^ | ||
+ | | ResultSet.TYPE_FORWARD_ONLY | El resultset sólo se puede consultar hacia adelante, no se puede reposicionar hacia atrás ni mover arbitrariamente | | ||
+ | | ResultSet.TYPE_SCROLL_INSENSITIVE | Se puede mover arbitrariamente a cualquier posición, y moverse hacia atrás en el resultset | | ||
+ | | ResultSet.TYPE_SCROLL_SENSITIVE | Se puede mover arbitrariamente a cualquier posición, y moverse hacia atrás en el resultset | | ||
+ | ^^^ | ||
+ | |ResultSet.CONCUR_READ_ONLY| Supongo que será de sólo lectura | | ||
+ | |ResultSet.CONCUR_UPDATABLE| Lectura y escritura | | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== Otro ejemplo utilizando un datasource ===== | ||
+ | |||
+ | <code java> | ||
+ | |||
+ | Connection con = null; | ||
+ | Statement stmt = null; | ||
+ | ResultSet rs = null; | ||
+ | try | ||
+ | { | ||
+ | Class.forName(" | ||
+ | | ||
+ | con = DriverManager.getConnection( | ||
+ | " | ||
+ | | ||
+ | stmt = con.createStatement(); | ||
+ | | ||
+ | rs = stmt.executeQuery( | ||
+ | " | ||
+ | "WHERE username = '" | ||
+ | "AND password = '" | ||
+ | | ||
+ | if (rs.next()) | ||
+ | valid = true; | ||
+ | } | ||
+ | catch (ClassNotFoundException e) { | ||
+ | e.printStackTrace(); | ||
+ | } | ||
+ | catch(SQLException e) { | ||
+ | e.printStackTrace(); | ||
+ | } | ||
+ | finally | ||
+ | { | ||
+ | try { if (rs != null) rs.close(); } catch (SQLException e) {}; | ||
+ | try { if (stmt != null) stmt.close(); | ||
+ | try { if (con != null) con.close(); | ||
+ | } | ||
+ | return valid; | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | Tenemos que definir el datasource en un fichero de configuración: | ||
+ | |||
+ | <code xml> | ||
+ | |||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | < | ||
+ | </ | ||
+ | </ | ||
+ | |||
+ | </ | ||
+ | |||
+ | ===== Transacciones en JDBC ===== | ||
+ | |||
+ | Para ejecutar varios comandos en modo transaccional lo primero que tenemos que hacer es deshabilitar el AutoCommit. El " | ||
+ | |||
+ | <code java> | ||
+ | con.setAutoCommit(false); | ||
+ | PreparedStatement updateSales = con.prepareStatement( | ||
+ | " | ||
+ | updateSales.setInt(1, | ||
+ | updateSales.setString(2, | ||
+ | updateSales.executeUpdate(); | ||
+ | PreparedStatement updateTotal = con.prepareStatement( | ||
+ | " | ||
+ | updateTotal.setInt(1, | ||
+ | updateTotal.setString(2, | ||
+ | updateTotal.executeUpdate(); | ||
+ | con.commit(); | ||
+ | con.setAutoCommit(true); | ||
+ | </ | ||
+ | |||
+ | Al ejecutar commit() se hacen los dos updates a la vez. | ||
+ | |||
+ | ==== Nivel de isolation en transaccion ==== | ||
+ | |||
+ | One example of a transaction isolation level is TRANSACTION_READ_COMMITTED, | ||
+ | |||
+ | Normally, you do not need to do anything about the transaction isolation level; you can just use the default one for your DBMS. JDBC allows you to find out what transaction isolation level your DBMS is set to (using the Connection method getTransactionIsolation) and also allows you to set it to another level (using the Connection method setTransactionIsolation). Keep in mind, however, that even though JDBC allows you to set a transaction isolation level, doing so has no effect unless the driver and DBMS you are using support it. | ||
+ | |||
+ | ==== Haciendo rollback (hasta un punto de guarda) ==== | ||
+ | |||
+ | <code java> | ||
+ | |||
+ | Statement stmt = conn.createStatement(); | ||
+ | int rows = stmt.executeUpdate(" | ||
+ | " | ||
+ | // set savepoint | ||
+ | Savepoint svpt1 = conn.setSavepoint(" | ||
+ | rows = stmt.executeUpdate(" | ||
+ | " | ||
+ | ... | ||
+ | conn.rollback(svpt1); | ||
+ | ... | ||
+ | conn.commit(); | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ===== Insertar un tipo de datos BLOB en Oracle ===== | ||
+ | |||
+ | No es algo trivial porque el driver JDBC de oracle funciona mal. Aquí está un ejemplo de código que funciona: | ||
+ | |||
+ | <code java> | ||
+ | /** | ||
+ | * OracleBlobSetBinaryStream.java | ||
+ | * Copyright (c) 2007 by Dr. Herong Yang. All rights reserved. | ||
+ | */ | ||
+ | import java.io.*; | ||
+ | import java.sql.*; | ||
+ | public class OracleBlobSetBinaryStream { | ||
+ | public static void main(String [] args) { | ||
+ | Connection con = null; | ||
+ | try { | ||
+ | oracle.jdbc.pool.OracleDataSource ds | ||
+ | = new oracle.jdbc.pool.OracleDataSource(); | ||
+ | ds.setDriverType(" | ||
+ | ds.setServerName(" | ||
+ | ds.setPortNumber(1521); | ||
+ | ds.setDatabaseName(" | ||
+ | ds.setUser(" | ||
+ | ds.setPassword(" | ||
+ | con = ds.getConnection(); | ||
+ | |||
+ | // Deleting the record for re-testing | ||
+ | String subject = "Test of setBinaryStream() methods"; | ||
+ | Statement sta = con.createStatement(); | ||
+ | sta.executeUpdate(" | ||
+ | +subject+"'" | ||
+ | |||
+ | // Inserting CLOB value with a PreparedStatement | ||
+ | PreparedStatement ps = con.prepareStatement( | ||
+ | " | ||
+ | ps.setString(1, | ||
+ | InputStream bodyIn = | ||
+ | new FileInputStream(" | ||
+ | |||
+ | // Test 1 - This will not work with JDBC 3.0 drivers | ||
+ | // ps.setBinaryStream(2, | ||
+ | |||
+ | // Test 2 - This will not work with JDBC 3.0 drivers | ||
+ | // File fileIn = new File(" | ||
+ | // ps.setBinaryStream(2, | ||
+ | |||
+ | // Test 3 - This works with JDBC 3.0 drivers | ||
+ | File fileIn = new File(" | ||
+ | ps.setBinaryStream(2, | ||
+ | |||
+ | int count = ps.executeUpdate(); | ||
+ | bodyIn.close(); | ||
+ | ps.close(); | ||
+ | |||
+ | // Retrieving BLOB value with getBytes() | ||
+ | sta = con.createStatement(); | ||
+ | ResultSet res = sta.executeQuery(" | ||
+ | +" WHERE Subject = '" | ||
+ | res.next(); | ||
+ | System.out.println(" | ||
+ | System.out.println(" | ||
+ | System.out.println(" | ||
+ | +new String(res.getBytes(" | ||
+ | res.close(); | ||
+ | |||
+ | sta.close(); | ||
+ | con.close(); | ||
+ | } catch (Exception e) { | ||
+ | System.err.println(" | ||
+ | e.printStackTrace(); | ||
+ | } | ||
+ | } | ||
+ | } | ||
+ | </ | ||
+ | |||
+ | |||
+ | Este ejemplo lo he sacado de esta página: | ||
+ | |||
+ | [[http:// | ||
+ | |||
+ | |||