· 4 years ago · May 03, 2021, 11:46 AM
1import java.util.Scanner;
2
3import org.postgresql.PGConnection;
4import org.postgresql.PGNotification;
5
6import java.io.File;
7import java.io.FileInputStream;
8import java.io.FileOutputStream;
9import java.sql.CallableStatement;
10import java.sql.Connection;
11import java.sql.DriverManager;
12import java.sql.PreparedStatement;
13import java.sql.ResultSet;
14import java.sql.SQLException;
15import java.sql.Statement;
16import java.util.InputMismatchException;
17import java.util.Properties;
18
19public class AD05 {
20
21 public static void main(String[] args) {
22 try {
23 Scanner es = new Scanner(System. in ); //aqui inicia el menú
24 boolean salir = false;
25 int opcion;
26
27 String url ="localhost";
28 String db = "tarea5";
29 //Indicamos las propiedades de la conexión
30 Properties props = new Properties();
31 props.setProperty("user", "usertarea");
32 props.setProperty("password", "abc123.");
33 //Dirección de conexión a la base de datos
34 String postgres = "jdbc:postgresql://"+url+"/"+db;
35 //Conectamos a la base de datos
36 try {
37 Connection miConexion = DriverManager.getConnection(postgres,props);
38 while (!salir) {
39
40 System.out.println("0 - Salir");
41 System.out.println("1 - Crear tabla alumno y agregar datos");
42 System.out.println("2 - Búsqueda alumnos");
43 System.out.println("3 - Notificar insercción de nuevos alumnos");
44 try {
45
46 System.out.println("Escribe una de las opciones");
47 opcion = es.nextInt();
48 switch (opcion) {
49 case 0:
50 salir = true;
51 break;
52 case 1:
53 //Definimos el tipo tipoDireccion
54 String createTipo = "CREATE TYPE tipoDireccion AS (" +
55 " localidad text," +
56 " tipoVia text," +
57 " nombreCalle text," +
58 " numero integer" +
59 ");";
60
61 //Creamos la tabla que contendra las imagenes
62 String sqlTableCreation ="CREATE TABLE IF NOT EXISTS alumno ("
63 + "id SERIAL PRIMARY KEY,"
64 + "dni text,"
65 + "nombre text,"
66 + "foto bytea,"
67 + "direccionHabitual tipoDireccion,"
68 + "direccionContacto tipoDireccion,"
69 + "modulosMatricula text[]"
70 + ");";
71 CallableStatement callstmt = null;
72
73 //Ejecutamos la sentencia SQL anterior
74 callstmt = miConexion.prepareCall(createTipo);
75 callstmt.execute();
76 CallableStatement createFunction = miConexion.prepareCall(sqlTableCreation);
77 createFunction.execute();
78
79 // Definimos la query para añadir valores
80 String insertSQL = "INSERT INTO alumno (dni,nombre,foto,direccionHabitual,direccionContacto,modulosMatricula) "
81 + "VALUES (?,?,?,ROW(?,?,?,?),ROW(?,?,?,?),?)";
82
83 // Preparamos la query y sus parámetros
84 File file = new File("Canario.jpg");
85 FileInputStream fis = null;
86 fis = new FileInputStream(file);
87 String[] arraymodulos = {"AD","ED","LMS","SI"};
88 String[] arraymodulos2 = {"ED","LMS","SI"};
89
90 // Primer alumno
91 PreparedStatement prepInsert = miConexion.prepareStatement(insertSQL);
92 prepInsert.setString(1, "75645768M");
93 prepInsert.setString(2, "Pablo");
94 prepInsert.setBinaryStream(3, fis, (int)file.length());
95 prepInsert.setString(4, "Orense");
96 prepInsert.setString(5, "Calle");
97 prepInsert.setString(6, "micalle");
98 prepInsert.setInt(7, 15);
99 prepInsert.setString(8, "Orense");
100 prepInsert.setString(9, "Calle");
101 prepInsert.setString(10, "micalle");
102 prepInsert.setInt(11, 15);
103 prepInsert.setArray(12, miConexion.createArrayOf("TEXT", arraymodulos));
104 //se ejecuta y cierra el preparedStatement
105 prepInsert.execute();
106 prepInsert.close();
107 fis.close();
108
109 // Añadimos segundo alumno
110 FileInputStream fis1 = null;
111 fis1 = new FileInputStream(file);
112 PreparedStatement prepInsert1 = miConexion.prepareStatement(insertSQL);
113 prepInsert1.setString(1, "45615768M");
114 prepInsert1.setString(2, "Pepe");
115 prepInsert1.setBinaryStream(3, fis1, (int)file.length());
116 prepInsert1.setString(4, "Orense");
117 prepInsert1.setString(5, "Calle");
118 prepInsert1.setString(6, "laguna");
119 prepInsert1.setInt(7, 2);
120 prepInsert1.setString(8, "Orense");
121 prepInsert1.setString(9, "Calle");
122 prepInsert1.setString(10, "laguna");
123 prepInsert1.setInt(11, 2);
124 prepInsert1.setArray(12, miConexion.createArrayOf("TEXT", arraymodulos));
125 //se ejecuta y cierra el preparedStatement
126 prepInsert1.execute();
127 prepInsert1.close();
128 fis1.close();
129
130 // Añadimos tercer alumno
131 FileInputStream fis2 = null;
132 fis2 = new FileInputStream(file);
133 PreparedStatement prepInsert11 = miConexion.prepareStatement(insertSQL);
134 prepInsert11.setString(1, "56748768M");
135 prepInsert11.setString(2, "Manuel");
136 prepInsert11.setBinaryStream(3, fis2, (int)file.length());
137 prepInsert11.setString(4, "Pontevedra");
138 prepInsert11.setString(5, "Calle");
139 prepInsert11.setString(6, "micalle");
140 prepInsert11.setInt(7, 31);
141 prepInsert11.setString(8, "Pontevedra");
142 prepInsert11.setString(9, "Calle");
143 prepInsert11.setString(10, "micalle");
144 prepInsert11.setInt(11, 31);
145 prepInsert11.setArray(12, miConexion.createArrayOf("TEXT", arraymodulos2));
146 //se ejecuta y cierra el preparedStatement
147 prepInsert11.execute();
148 prepInsert11.close();
149 fis2.close();
150
151 // Añadimos cuarto alumno
152 FileInputStream fis3 = null;
153 fis3 = new FileInputStream(file);
154 PreparedStatement prepInsert111 = miConexion.prepareStatement(insertSQL);
155 prepInsert111.setString(1, "75632598M");
156 prepInsert111.setString(2, "Sara");
157 prepInsert111.setBinaryStream(3, fis3, (int)file.length());
158 prepInsert111.setString(4, "Lugo");
159 prepInsert111.setString(5, "Calle");
160 prepInsert111.setString(6, "lamia");
161 prepInsert111.setInt(7, 135);
162 prepInsert111.setString(8, "Lugo");
163 prepInsert111.setString(9, "Calle");
164 prepInsert111.setString(10, "lamia");
165 prepInsert111.setInt(11, 135);
166 prepInsert111.setArray(12, miConexion.createArrayOf("TEXT", arraymodulos2));
167 //se ejecuta y cierra el preparedStatement
168 prepInsert111.execute();
169 prepInsert111.close();
170 fis3.close();
171
172 break;
173 case 2:
174 // Obtenemos los datos del apartado a
175 String insertSQLB = "Select dni,nombre,(direccionhabitual).localidad,(direccioncontacto).localidad,foto "
176 + "from alumno "
177 + "where (direccionhabitual).localidad=? "
178 + "and ? = ANY(modulosMatricula)";
179 PreparedStatement prepsearch = miConexion.prepareStatement(insertSQLB);
180 prepsearch.setString(1, "Orense");
181 prepsearch.setString(2, "AD");
182 ResultSet rs = prepsearch.executeQuery();
183
184 // Ponemos cabeceras a una "tabla" (no queda muy bien, pero por lo menos clarifica cada columna)
185 System.out.print("DNI Nombre Habitual Contacto\n");
186
187 // Recorremos el resultSet printeando en cada fila un alumno y descargando su foto
188 while (rs.next())
189 {
190 System.out.print(rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4) + "\n");
191 byte[] imgBytes = null;
192 imgBytes = rs.getBytes(5);
193
194 // Construimos el nombre del fichero
195 String ficheroSaida = new String("foto_" + rs.getString(2) + ".jpg");
196 File fileOut = new File(ficheroSaida);
197 FileOutputStream flujoDatos = new FileOutputStream(fileOut);
198
199 //Guardamos el fichero recuperado
200 if(imgBytes != null){
201 flujoDatos.write(imgBytes);
202 }
203 flujoDatos.close();
204 }
205 prepsearch.close();
206 break;
207 case 3:
208 String sqlCreateFunction = "CREATE OR REPLACE FUNCTION notificar_insercion() " +
209 "RETURNS trigger AS $$ " +
210 "BEGIN " +
211 "PERFORM pg_notify('nuevoalumno',NEW.id); " +
212 "RETURN NEW; " +
213 "END; " +
214 "$$ LANGUAGE plpgsql; ";
215 CallableStatement funcioncilla = miConexion.prepareCall(sqlCreateFunction);
216 funcioncilla.execute();
217 funcioncilla.close();
218
219 //Creamos el trigger que se ejecuta cuando se añade una persona
220 String sqlCreateTrigger = "DROP TRIGGER IF EXISTS trigger_alumno ON alumno; " +
221 "CREATE TRIGGER trigger_alumno " +
222 "AFTER INSERT ON alumno FOR EACH ROW " +
223 "EXECUTE PROCEDURE notificar_insercion(); ";
224 CallableStatement createTrigger = miConexion.prepareCall(sqlCreateTrigger);
225 createTrigger.execute();
226 createTrigger.close();
227
228 //Configuramos para estar a la escucha
229 PGConnection pgconn = miConexion.unwrap(PGConnection.class);
230 Statement stmt = miConexion.createStatement();
231 stmt.execute("LISTEN nuevoalumno;");
232 stmt.close();
233 System.out.println("Esperando cambios...");
234
235 //Recogemos todas las notificaciones
236 PGNotification[] notifications = pgconn.getNotifications();
237 //Si hay notificaciones, las recorremos e imprimimos
238 if(notifications != null){
239 for(int i=0;i < notifications.length;i++){
240 //La notificacion nos da como parámetro el id de la persona
241 String id = notifications[i].getParameter();
242 //Hacemos una consulta a la BBDD y recuperamos los teléfonos asociados
243 PreparedStatement sqlMensaxe = miConexion.prepareStatement(
244 "SELECT dni, nombre FROM alumno WHERE id=?;");
245 sqlMensaxe.setString(1, id);
246 ResultSet rs2 = sqlMensaxe.executeQuery();
247 rs2.next();
248 System.out.println(rs2.getString(1) + ":" + rs2.getString(2));
249 rs2.close();
250 }
251 }
252 break;
253 default:
254 System.out.println("Solo números entre 0 y 3");
255 }
256 } catch(InputMismatchException e) {
257 System.out.println("Debes insertar un número");
258 es.next();
259 }
260 }
261 miConexion.close();
262 } catch (SQLException ex) {
263 System.err.println("Error: " + ex.toString());
264 }
265 es.close();
266
267 }catch (Exception e) {
268 System.out.println("Error");
269 e.printStackTrace();
270 }
271
272 }
273
274}
275