• Author / Uploaded
• Ricardo Pacheco

• Categories
• Arquitectura Computacional Distribuida
• Red de arquitectura
• Software
• SQL
• Protocolos de internet

Citation preview

Persistencia y Datos Transaccionales Base de datos BancoXYZ

Joan Sebastián Oviedo Tinjacá. Código 1421029468 Jordán Orlando Sánchez Código 1721025393 Javier Hely Medrano Bermúdez Código 1721023463

Docente Persistencia y Datos Transaccionales Alexis Rojas Cordero

POLITÉCNICO GRANCOLOMBIANO Facultad de Ingeniería y Ciencias Básicas Bogotá 2019

Los siguientes códigos es dos tipos de base de datos y en ultimo la integración de los Sockets y el código de conectar java con MYSQL

Tipo de base de datos # 1

[18:06, 2/7/2019] Javier Medrano: -- MySQL Script generated by MySQL Workbench -- Tue Jul 2 18:01:08 2019 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_D ATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTIT UTION';

-- ------------------------------------------------------ Schema BANCO_XXX -- -----------------------------------------------------

-- ------------------------------------------------------ Schema BANCO_XXX -- ----------------------------------------------------CREATE SCHEMA IF NOT EXISTS `BANCO_XXX` DEFAULT CHARACTER SET utf8 ; USE `BANCO_XXX` ;

-- ------------------------------------------------------ Table `BANCO_XXX`.`PAIS`

-- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BANCO_XXX`.`PAIS` ( `codPAIS` VARCHAR(15) NOT NULL, `nomPais` VARCHAR(45) NOT NULL, PRIMARY KEY (`codPAIS`)) ENGINE = InnoDB;

-- ------------------------------------------------------ Table `BANCO_XXX`.`CIUDAD` -- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BANCO_XXX`.`CIUDAD` ( `codCiudad` VARCHAR(15) NOT NULL, `nomCiudad` VARCHAR(45) NOT NULL, `codPais` VARCHAR(15) NOT NULL, PRIMARY KEY (`codCiudad`), INDEX `codPais_idx` (`codPais` ASC) VISIBLE, CONSTRAINT `codPais` FOREIGN KEY (`codPais`) REFERENCES `BANCO_XXX`.`PAIS` (`codPAIS`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- ------------------------------------------------------ Table `BANCO_XXX`.`CLIENTE` -- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BANCO_XXX`.`CLIENTE` (

`cedula` VARCHAR(15) NOT NULL, `nomCliente` VARCHAR(45) NOT NULL, `codCiudad` VARCHAR(15) NOT NULL, PRIMARY KEY (`cedula`), INDEX `codCiudad_idx` (`codCiudad` ASC) VISIBLE, CONSTRAINT `codCiudad` FOREIGN KEY (`codCiudad`) REFERENCES `BANCO_XXX`.`CIUDAD` (`codCiudad`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- ------------------------------------------------------ Table `BANCO_XXX`.`TELEFONO` -- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BANCO_XXX`.`TELEFONO` ( `numTel` INT NOT NULL, `cedula` VARCHAR(15) NOT NULL, PRIMARY KEY (`numTel`), INDEX `cedula_idx` (`cedula` ASC) VISIBLE, CONSTRAINT `cedula` FOREIGN KEY (`cedula`) REFERENCES `BANCO_XXX`.`CLIENTE` (`cedula`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- ------------------------------------------------------ Table `BANCO_XXX`.`SALDO_CTA` -- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BANCO_XXX`.`SALDO_CTA` ( `nroCta` VARCHAR(45) NOT NULL, `saldo` DECIMAL(45) NOT NULL, `cedula` VARCHAR(15) NOT NULL, PRIMARY KEY (`nroCta`), INDEX `cedula_idx` (`cedula` ASC) VISIBLE, CONSTRAINT `cedula` FOREIGN KEY (`cedula`) REFERENCES `BANCO_XXX`.`CLIENTE` (`cedula`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

-- ------------------------------------------------------ Table `BANCO_XXX`.`TIPO_MVTO` -- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BANCO_XXX`.`TIPO_MVTO` ( `tipoMvto` INT(1) NOT NULL, `descripcion` VARCHAR(45) NOT NULL, PRIMARY KEY (`tipoMvto`)) ENGINE = InnoDB;

-- ------------------------------------------------------ Table `BANCO_XXX`.`MVTO`

-- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BANCO_XXX`.`MVTO` ( `nroCta` VARCHAR(45) NOT NULL, `fechaMvto` DATETIME(45) NOT NULL, `secuencia` INT NOT NULL AUTO_INCREMENT, `valorMvto` DECIMAL(45) NOT NULL, `tipoMvto` INT(1) NOT NULL, PRIMARY KEY (`secuencia`, `nroCta`, `fechaMvto`), INDEX `nroCta_idx` (`nroCta` ASC) VISIBLE, INDEX `tipoMvto_idx` (`tipoMvto` ASC) VISIBLE, CONSTRAINT `nroCta` FOREIGN KEY (`nroCta`) REFERENCES `BANCO_XXX`.`SALDO_CTA` (`nroCta`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `tipoMvto` FOREIGN KEY (`tipoMvto`) REFERENCES `BANCO_XXX`.`TIPO_MVTO` (`tipoMvto`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; [18:09, 2/7/2019] Javier Medrano: la otra version es la siguiente: [18:09, 2/7/2019] Javier Medrano: -- MySQL Script generated by MySQL Workbench -- Tue Jul 2 18:08:24 2019

-- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering

Tipo de bases de datos # 2

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_D ATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTIT UTION';

-- ------------------------------------------------------ Schema BDbancoSebas -- -----------------------------------------------------

-- ------------------------------------------------------ Schema BDbancoSebas -- ----------------------------------------------------CREATE SCHEMA IF NOT EXISTS `BDbancoSebas` DEFAULT CHARACTER SET utf8 ; USE `BDbancoSebas` ;

-- ------------------------------------------------------ Table `BDbancoSebas`.`paises` -- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BDbancoSebas`.`paises` (

`cod_pais` INT(255) NOT NULL AUTO_INCREMENT, `nombre` VARCHAR(255) NOT NULL, PRIMARY KEY (`cod_pais`), UNIQUE INDEX `uq_cod_pais` (`cod_pais` ASC) VISIBLE, UNIQUE INDEX `uq_nombre` (`nombre` ASC) VISIBLE) ENGINE = InnoDb;

-- ------------------------------------------------------ Table `BDbancoSebas`.`ciudades` -- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BDbancoSebas`.`ciudades` ( `cod_ciudad` INT(255) NOT NULL AUTO_INCREMENT, `nombre` VARCHAR(255) NOT NULL, `cod_pais` INT(255) NOT NULL, PRIMARY KEY (`cod_ciudad`), UNIQUE INDEX `uq_cod_ciudad` (`cod_ciudad` ASC) VISIBLE, UNIQUE INDEX `uq_nombre` (`nombre` ASC) VISIBLE, INDEX `fk_ciudades_paises` (`cod_pais` ASC) VISIBLE, CONSTRAINT `fk_ciudades_paises` FOREIGN KEY (`cod_pais`) REFERENCES `BDbancoSebas`.`paises` (`cod_pais`)) ENGINE = InnoDb;

-- ------------------------------------------------------ Table `BDbancoSebas`.`clientes` -- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BDbancoSebas`.`clientes` (

`cedula` VARCHAR(255) NOT NULL, `nombre` VARCHAR(255) NOT NULL, `apellido` VARCHAR(255) NOT NULL, `cod_ciudad` INT(255) NOT NULL, PRIMARY KEY (`cedula`), UNIQUE INDEX `uq_cedula` (`cedula` ASC) VISIBLE, INDEX `fk_clientes_ciudades` (`cod_ciudad` ASC) VISIBLE, CONSTRAINT `fk_clientes_ciudades` FOREIGN KEY (`cod_ciudad`) REFERENCES `BDbancoSebas`.`ciudades` (`cod_ciudad`)) ENGINE = InnoDb;

-- ------------------------------------------------------ Table `BDbancoSebas`.`telefonos` -- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BDbancoSebas`.`telefonos` ( `nro_tfno` INT(255) NOT NULL, `cedula` VARCHAR(255) NOT NULL, PRIMARY KEY (`nro_tfno`, `cedula`), UNIQUE INDEX `uq_nro_tfno` (`nro_tfno` ASC) VISIBLE, INDEX `fk_telefonos_clientes` (`cedula` ASC) VISIBLE, CONSTRAINT `fk_telefonos_clientes` FOREIGN KEY (`cedula`) REFERENCES `BDbancoSebas`.`clientes` (`cedula`)) ENGINE = InnoDb;

-- -----------------------------------------------------

-- Table `BDbancoSebas`.`cuentas` -- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BDbancoSebas`.`cuentas` ( `nro_cta` INT(255) NOT NULL, `cedula` VARCHAR(255) NOT NULL, `ingreso` INT(255) NOT NULL, `egreso` INT(255) NOT NULL, `saldo` INT(255) NOT NULL, PRIMARY KEY (`nro_cta`), UNIQUE INDEX `uq_nro_cta` (`nro_cta` ASC) VISIBLE, INDEX `fk_cuentas_clientes` (`cedula` ASC) VISIBLE, CONSTRAINT `fk_cuentas_clientes` FOREIGN KEY (`cedula`) REFERENCES `BDbancoSebas`.`clientes` (`cedula`)) ENGINE = InnoDb;

-- ------------------------------------------------------ Table `BDbancoSebas`.`tiposmovimiento` -- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BDbancoSebas`.`tiposmovimiento` ( `mvto_id` INT(255) NOT NULL AUTO_INCREMENT, `descripcion` VARCHAR(255) NOT NULL, PRIMARY KEY (`mvto_id`), UNIQUE INDEX `uq_mvto_id` (`mvto_id` ASC) VISIBLE) ENGINE = InnoDb;

-- -----------------------------------------------------

-- Table `BDbancoSebas`.`movimientos` -- ----------------------------------------------------CREATE TABLE IF NOT EXISTS `BDbancoSebas`.`movimientos` ( `nro_cta` INT(255) NOT NULL, `fecha` DATE NOT NULL, `secuencia` INT(255) NOT NULL AUTO_INCREMENT, `mvto_id` INT(255) NOT NULL, `hora` TIME NOT NULL, `valor_mvto` INT(255) NOT NULL, PRIMARY KEY (`secuencia`, `nro_cta`, `fecha`), INDEX `fk_movimientos_cuentas` (`nro_cta` ASC) VISIBLE, INDEX `fk_movimientos_tiposmovimiento` (`mvto_id` ASC) VISIBLE, CONSTRAINT `fk_movimientos_cuentas` FOREIGN KEY (`nro_cta`) REFERENCES `BDbancoSebas`.`cuentas` (`nro_cta`), CONSTRAINT `fk_movimientos_tiposmovimiento` FOREIGN KEY (`mvto_id`) REFERENCES `BDbancoSebas`.`tiposmovimiento` (`mvto_id`)) ENGINE = InnoDb;

SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

La siguiente es la conexión entre bases de datos y mysql

import java.sql.DriverManager; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet;

public class PruebaMySQL { public static void main(String[] args) {

Connection con = null;

try { Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/sakila?serverTimezone=UTC", "root", "jhmb100352JH"); System.out.println("Connection OK"); }catch (Exception e) {

System.err.println(e);

} } } [18:29, 2/7/2019] Javier Medrano: package clientSocket;

import java.net.*; import java.io.*;

public class Ncliente {

// 1. initialize socket and input output streams private Socket socket = null; private DataInputStream input = null; private DataInputStream inputServer = null; private DataOutputStream out = null; private String information = ""; // 2. Constructor to put ip address and port

public Ncliente(String address, int port) {

// establish a connection try { socket = new Socket(address, port); System.out.println("Connected");

// takes input from terminal

input = new DataInputStream(System.in);

// takes imput from server inputServer = new DataInputStream(socket.getInputStream());

// sends output to the socket out = new DataOutputStream(socket.getOutputStream());

// receives message from the server information = inputServer.readUTF(); System.out.println(information); } catch(UnknownHostException u) { System.out.println(u); } catch(IOException i) { System.out.println(i); }

// String to read message from input String line = "";

// keep reading until "Over is input and shows information from server while(!line.contentEquals("Over")) { try {

line = input.readLine(); out.writeUTF(line);

information = inputServer.readUTF(); System.out.println(information); } catch(IOException i) { System.out.println(i); } }

// close the connection

try { input.close(); out.close(); socket.close(); } catch(IOException i) { System.out.println(i); } }

public static void main(String[] args) { // TODO Auto-generated method stub

Ncliente cliente = new Ncliente("186.86.32.212", 5000); }

} [18:34, 2/7/2019] Javier Medrano: package Sockets;

import java.net.*; import java.io.*; import java.util.Scanner;

public class Servidor { static int PUERTO = 5000; // aquí definimos el puerto ServerSocket sc; Socket so; DataOutputStream salida; // bufer para armar las salidas o lo que debe responder DataInputStream entrada; // bufer para recibir datos String mensajeRecibido; // Variable donde guardo lo que llega public void initServidor() { Scanner teclado = new Scanner(System.in); try { sc = new ServerSocket(5000); //arbrir el puerto so = new Socket(); System.out.println("Esperando conexión..."); so = sc.accept(); System.out.println("Se conecto un cliente...");

entrada = new DataInputStream(so.getInputStream()); //armar el constructor de recibo salida = new DataOutputStream(so.getOutputStream()); String msn = ""; while (!msn.equals("x")) { mensajeRecibido = entrada.readUTF();// Leemos lo que llega

System.out.println(mensajeRecibido); System.out.println("responder al cliente --> escriba algo"); msn = teclado.nextLine(); salida.writeUTF("" + msn);// enviamos mensaje } // sc.close(); } catch (Exception e) { } } public static void main(String[] args) { Servidor o = new Servidor(); o.initServidor(); }