package com.trifulcas.datos; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; public class ActorDAO { private Connection con; private int max_records=1000; public ActorDAO() { try { Class.forName("com.mysql.cj.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sakila", "root", ""); } catch (Exception ex) { System.out.println(ex); } } public Actor getActor(int id) { Actor res = null; try { String sql = "select * from actor where actor_id=?" ; PreparedStatement stmt = con.prepareStatement(sql); stmt.setInt(1, id); ResultSet rs = stmt.executeQuery(); if (rs.next()) { res = new Actor(rs.getInt("actor_id"), rs.getString("first_name"),rs.getString("last_name"),rs.getDate("last_update")); } } catch (Exception ex) { System.out.println(ex); } return res; } public List<Actor> getActors(int limite){ List<Actor> res=new ArrayList<Actor>(); try { String sql = "select * from actor limit ?" ; PreparedStatement stmt = con.prepareStatement(sql); stmt.setInt(1, limite); ResultSet rs = stmt.executeQuery(); while (rs.next()) { Actor temp = new Actor(rs.getInt("actor_id"), rs.getString("first_name"),rs.getString("last_name"),rs.getDate("last_update")); res.add(temp); } } catch (Exception ex) { System.out.println(ex); } return res; } public List<Actor> getActors(){ return getActors(max_records); } /** * Devuelve un arraylist de actores cuyo apellido contenga la cadena que le pasamos * @param cad cadena a buscar * @return ArrayList de actores */ public List<Actor> getActors(String cad) { List<Actor> res = new ArrayList<Actor>(); try { String sql = "select * from actor where last_name like ?"; PreparedStatement stmt = con.prepareStatement(sql); stmt.setString(1, '%'+cad+'%'); ResultSet rs = stmt.executeQuery(); while (rs.next()) { Actor temp = new Actor(rs.getInt("actor_id"), rs.getString("first_name"), rs.getString("last_name"), rs.getDate("last_update")); res.add(temp); } } catch (Exception ex) { System.out.println(ex); } return res; } public boolean addActor(Actor actor) { try { String sql = "insert into actor (first_name, last_name) values (?,?)" ; PreparedStatement stmt = con.prepareStatement(sql); stmt.setString(1, actor.getFirst_name()); stmt.setString(2, actor.getLast_name()); int res=stmt.executeUpdate(); return res==1; } catch (Exception ex) { System.out.println(ex); } return false; } public boolean updateActor(Actor actor) { try { String sql = "update actor set first_name=?, last_name=? where actor_id=?" ; PreparedStatement stmt = con.prepareStatement(sql); stmt.setString(1, actor.getFirst_name()); stmt.setString(2, actor.getLast_name()); stmt.setInt(3, actor.getActor_id()); int res=stmt.executeUpdate(); return res==1; } catch (Exception ex) { System.out.println(ex); } return false; } public boolean deleteActor(Actor actor) { try { return deleteActor(actor.getActor_id()); } catch (Exception ex) { System.out.println(ex); } return false; } public boolean deleteActor(int actor_id) { try { String sql = "delete from actor where actor_id=?" ; PreparedStatement stmt = con.prepareStatement(sql); stmt.setInt(1, actor_id); int res=stmt.executeUpdate(); return res==1; } catch (Exception ex) { System.out.println(ex); } return false; } }
package com.trifulcas.datos; import java.util.List; public class AccesoDAO { public static void main(String[] args) { // He abstraído la base de datos // Sólo tengo que crear una instancia de mi capa de Datos ActorDAO bd=new ActorDAO(); // Trabajo exclusivamente con POJO no con registros de la base de datos // Que hasta desconozco o no me interesa como están implementados Actor penelope=bd.getActor(1); System.out.println(penelope.getFirst_name()); // Obtengo todos los actores en una lista y recorro la lista List<Actor> actores=bd.getActors(20); for(Actor actor:actores) { System.out.println(actor); } // Para añadir creo un actor nuevo y después llamo a la capa DAO para añadirlo Actor nuevo=new Actor(1,"wilfredo","etxevarria",null); if(bd.addActor(nuevo)) { System.out.println("Actor "+nuevo+" añadido"); } // Modifico un actor penelope.setLast_name("Campofrío"); bd.updateActor(penelope); // Elimino el actor concreto o por id Actor w=bd.getActor(320); bd.deleteActor(w); bd.deleteActor(321); } }