Download Capitulo 6 Programación de Base de Datos En 1996, Sun liberó la

Capitulo 6
Programación de Base de Datos
En 1996, Sun liberó la primera versión de la API de JDBC - Java DataBase Connectivity
(Conectividad de Base de Datos). Esta API deja a programadores conectarse a una base
de datos y luego consultar o actualizarlo, usando el Lenguaje Estructurado de Consulta
(SQL- Structure Query Languaje). (SQL "sequel" pronunciada generalmente, es un
estándar de la industria para el acceso a Base de Datos Relacionales.) JDBC se ha
convertido en uno de los API más comúnmente usadas en la biblioteca de Java desde ese
entonces.
El diseño de JDBC
JDBC para la comunicación con la Base de Datos
Conexión con Base de Datos
1) Crear la Base de Datos
Para esta tarea se puede utilizar Microsoft Access, MySql, Postgress y otros gestores de
Base de Datos.
La estructura es la siguiente, para el ejemplo tomaremos Microsoft Access:
Nombre Base de Datos: Prueba
Nombre de Tabla: Person
2) Crear la conexión ODBC
a) En el menú inicio elegir la opción
b) Luego elegir la opción
c) En herramientas administrativas elegir la opción
3) En este formulario elegir el botón agregar:
4) Luego seleccionar el Driver do Microsoft Access (*.mdb) que permite seleccionar la Base
de Datos presionar el botón Finalizar.
5) En la siguiente ventana, en el Nombre del origen de datos: coloque un nombre corto (que
es la cadena de conexión) y haga clic en el botón Seleccionar la Base Datos para enlazar a
la Base Datos y hacer referencia en el código de Programación Java:
6) Una vez creado la cadena este aparece en la ventana principal como se muestra a
continuación:
7) Finalmente en el código de programación Java haga referencia a la cadena de conexión.
1) Clase PersonInfo.java
public class PersonInfo
{
private String name,address,email;
private int id, phone;
// constructor por defecto
public PersonInfo()
{
name = "";
address = "";
email = "";
id = 0;
phone = 0;
}
public PersonInfo(int id, String name, String address, int phone,
String email)
{
this.id = id;
this.name = name;
this.address = address;
this.phone = phone;
this.email = email;
}
// constructor con 4 valores
public PersonInfo(String name, String address, int phone, String
email)
{
this.name = name;
this.address = address;
this.phone = phone;
this.email = email;
}
// setters
public void setId(int i)
{
id = i;
}
public void setName(String n)
{
name=n;
}
public void setAddress(String a)
{
address=a;
}
public void setPhone(int ph)
{
phone=ph;
}
public void setEmail(String e)
{
email=e;
}
// getters
public int getId( )
{
return id;
}
public String getName()
{
return name;
}
public String getAddress()
{
return address;
}
public int getPhone()
{
return phone;
}
public String getEmail()
{
return email;
}
}
2) Clase PersonDAO.java
import java.util.*;
import java.sql.*;
public class PersonDAO{
/* Tabla Person se debe crear.
* create table Person (
id Integer,
name Varchar(30),
address Varchar(30),
phone Integer,
email Varchar(50)
);*/
// Para Postgress
//private ArrayList personsList;
//private String userid = "postgres";
//private String password = "postgres";
//static String url = "jdbc:postgresql://localhost/ug";
//Para Access
private ArrayList personsList;
private String userid = "";
private String password = "";
static String url = "jdbc:odbc:cnn";
private Connection con;
// constructor
public PersonDAO(){
personsList = new ArrayList();
getConnection();
//Crea Conexion a la base de datos
}
public Connection getConnection(){
try {
Class.forName("sun.jdbc.odbc:JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url, userid,
password);
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
return con;
}
public ArrayList searchPerson(String name)
{
try
{
String sql = "SELECT * FROM Person WHERE name like
'%"+name+"%'";
// Crea una declaracion preparada
Statement s = con.createStatement();
ResultSet rs = s.executeQuery(sql);
String pname = "";
String address = "";
String email = "";
int id, phone;
while(rs.next())
{
id = rs.getInt("id");
pname = rs.getString("name");
address = rs.getString("address");
phone = rs.getInt("phone");
email = rs.getString("email");
//Crea un objeto PersonInfo
PersonInfo person = new PersonInfo(id, pname,
address, phone, email);
//Adiciona el objeto person a una lista
personsList.add(person);
}
}
catch(Exception e){
System.out.println(e);
}
return personsList;
}
public void savePerson(PersonInfo person){
try
{
String sql = "INSERT INTO Person(name, address, " +
"phone, email) VALUES (?,?,?,?)
";
// Crea una Prepared statement ya preparada
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, person.getName());
ps.setString(2, person.getAddress());
ps.setInt(3, person.getPhone());
ps.setString(4, person.getEmail());
ps.executeUpdate();
}
catch(Exception e){
System.out.println(e);
}
}
public void updatePerson(PersonInfo person)
{
try
{
String sql = "UPDATE Person SET name = ?, address=? , "
+
"phone=? , email=? where id=?";
// Crea una Prepared statement
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1 , person.getName());
ps.setString(2 , person.getAddress());
ps.setInt(3 , person.getPhone());
ps.setString(4 , person.getEmail());
ps.setInt(5 , person.getId());
ps.executeUpdate();
}
catch(Exception e){
System.out.println(e);
}
}
public int removePerson(String name){
int no = 0;
try{
String sql = "DELETE FROM Person WHERE name = ?";
// Crea una Prepared statement
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, name);
no = ps.executeUpdate();
}
catch(Exception e){
System.out.println(e);
}
return no;
}
}// fin class PersonDAO
3) Clase AddressBookDemo.java
import
import
import
import
import
import
import
java.awt.Container;
java.awt.GridBagConstraints;
java.awt.GridBagLayout;
java.awt.Insets;
java.awt.event.ActionEvent;
java.awt.event.ActionListener;
java.util.ArrayList;
import
import
import
import
import
javax.swing.JButton;
javax.swing.JFrame;
javax.swing.JLabel;
javax.swing.JOptionPane;
javax.swing.JTextField;
public class AddressBookDemo implements ActionListener
{
ArrayList personsList;
PersonDAO pDAO;
JFrame appFrame;
JLabel jlbName, jlbAddress, jlbPhone, jlbEmail;
JTextField jtfName, jtfAddress, jtfPhone, jtfEmail;
JButton jbbSave, jbnDelete, jbnClear, jbnUpdate, jbnSearch,
jbnForward, jbnBack, jbnExit;
String name, address, email;
int phone;
int recordNumber; // usados para navegar usando los botones >> y <<
Container cPane;
public static void main(String args[]){
new AddressBookDemo();
}
public AddressBookDemo()
{
name
= "";
address = "";
email = "";
phone
= 0 ;
//Almacena 0 para indicar que no hay numero
de telefono
recordNumber = -1;
createGUI();
personsList = new ArrayList();
// creando un objeto PersonDAO
pDAO = new PersonDAO();
}
public void createGUI(){
/*Crea un frame, obtiene su contentpane y define su layout*/
appFrame = new JFrame("Agenda");
cPane = appFrame.getContentPane();
cPane.setLayout(new GridBagLayout());
//coloca los componentes en contentPane y define Action
Listeners para cada JButton
arrangeComponents();
appFrame.setSize(280,300);
appFrame.setResizable(false);
appFrame.setVisible(true);
appFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
public void arrangeComponents(){
jlbName = new JLabel("Nombre");
jlbAddress = new JLabel("Direccion");
jlbPhone = new JLabel("telefono");
jlbEmail = new JLabel("Email");
jtfName
jtfAddress
jtfPhone
jtfEmail
jbbSave
jbnDelete
jbnClear
jbnUpdate
jbnSearch
=
=
=
=
=
=
=
=
=
new
new
new
new
new
new
new
new
new
JTextField(20);
JTextField(20);
JTextField(20);
JTextField(20);
JButton("Grabar");
JButton("Borrar");
JButton("Limpiar");
JButton("Actualizar");
JButton("Buscar");
jbnForward = new JButton(">>");
jbnBack
= new JButton("<<");
jbnExit
= new JButton("Salir");
/*adiciona todos los componentes inicializados al container*/
GridBagConstraints gridBagConstraintsx01 = new
GridBagConstraints();
gridBagConstraintsx01.gridx = 0;
gridBagConstraintsx01.gridy = 0;
gridBagConstraintsx01.insets = new Insets(5,5,5,5);
cPane.add(jlbName, gridBagConstraintsx01);
GridBagConstraints gridBagConstraintsx02 = new
GridBagConstraints();
gridBagConstraintsx02.gridx = 1;
gridBagConstraintsx02.insets = new Insets(5,5,5,5);
gridBagConstraintsx02.gridy = 0;
gridBagConstraintsx02.gridwidth = 2;
gridBagConstraintsx02.fill = GridBagConstraints.BOTH;
cPane.add(jtfName, gridBagConstraintsx02);
GridBagConstraints gridBagConstraintsx03 = new
GridBagConstraints();
gridBagConstraintsx03.gridx = 0;
gridBagConstraintsx03.insets = new Insets(5,5,5,5);
gridBagConstraintsx03.gridy = 1;
cPane.add(jlbAddress, gridBagConstraintsx03);
GridBagConstraints gridBagConstraintsx04 = new
GridBagConstraints();
gridBagConstraintsx04.gridx = 1;
gridBagConstraintsx04.insets = new Insets(5,5,5,5);
gridBagConstraintsx04.gridy = 1;
gridBagConstraintsx04.gridwidth = 2;
gridBagConstraintsx04.fill = GridBagConstraints.BOTH;
cPane.add(jtfAddress, gridBagConstraintsx04);
GridBagConstraints gridBagConstraintsx05 = new
GridBagConstraints();
gridBagConstraintsx05.gridx = 0;
gridBagConstraintsx05.insets = new Insets(5,5,5,5);
gridBagConstraintsx05.gridy = 2;
cPane.add(jlbPhone, gridBagConstraintsx05);
GridBagConstraints gridBagConstraintsx06 = new
GridBagConstraints();
gridBagConstraintsx06.gridx = 1;
gridBagConstraintsx06.gridy = 2;
gridBagConstraintsx06.insets = new Insets(5,5,5,5);
gridBagConstraintsx06.gridwidth = 2;
gridBagConstraintsx06.fill = GridBagConstraints.BOTH;
cPane.add(jtfPhone, gridBagConstraintsx06);
GridBagConstraints gridBagConstraintsx07 = new
GridBagConstraints();
gridBagConstraintsx07.gridx = 0;
gridBagConstraintsx07.insets = new Insets(5,5,5,5);
gridBagConstraintsx07.gridy = 3;
cPane.add(jlbEmail, gridBagConstraintsx07);
GridBagConstraints gridBagConstraintsx08 = new
GridBagConstraints();
gridBagConstraintsx08.gridx = 1;
gridBagConstraintsx08.gridy = 3;
gridBagConstraintsx08.gridwidth = 2;
gridBagConstraintsx08.insets = new Insets(5,5,5,5);
gridBagConstraintsx08.fill = GridBagConstraints.BOTH;
cPane.add(jtfEmail, gridBagConstraintsx08);
GridBagConstraints gridBagConstraintsx09 = new
GridBagConstraints();
gridBagConstraintsx09.gridx = 0;
gridBagConstraintsx09.gridy = 4;
gridBagConstraintsx09.insets = new Insets(5,5,5,5);
cPane.add(jbbSave, gridBagConstraintsx09);
GridBagConstraints gridBagConstraintsx10 = new
GridBagConstraints();
gridBagConstraintsx10.gridx = 1;
gridBagConstraintsx10.gridy = 4;
gridBagConstraintsx10.insets = new Insets(5,5,5,5);
cPane.add(jbnDelete, gridBagConstraintsx10);
GridBagConstraints gridBagConstraintsx11 = new
GridBagConstraints();
gridBagConstraintsx11.gridx = 2;
gridBagConstraintsx11.gridy = 4;
gridBagConstraintsx11.insets = new Insets(5,5,5,5);
cPane.add(jbnUpdate, gridBagConstraintsx11);
GridBagConstraints gridBagConstraintsx12 = new
GridBagConstraints();
gridBagConstraintsx12.gridx = 0;
gridBagConstraintsx12.gridy = 5;
gridBagConstraintsx12.insets = new Insets(5,5,5,5);
cPane.add(jbnBack, gridBagConstraintsx12);
GridBagConstraints gridBagConstraintsx13 = new
GridBagConstraints();
gridBagConstraintsx13.gridx = 1;
gridBagConstraintsx13.gridy = 5;
gridBagConstraintsx13.insets = new Insets(5,5,5,5);
cPane.add(jbnSearch, gridBagConstraintsx13);
GridBagConstraints gridBagConstraintsx14 = new
GridBagConstraints();
gridBagConstraintsx14.gridx = 2;
gridBagConstraintsx14.gridy = 5;
gridBagConstraintsx14.insets = new Insets(5,5,5,5);
cPane.add(jbnForward, gridBagConstraintsx14);
GridBagConstraints gridBagConstraintsx15 = new
GridBagConstraints();
gridBagConstraintsx15.gridx = 1;
gridBagConstraintsx15.insets = new Insets(5,5,5,5);
gridBagConstraintsx15.gridy = 6;
cPane.add(jbnClear, gridBagConstraintsx15);
GridBagConstraints gridBagConstraintsx16 = new
GridBagConstraints();
gridBagConstraintsx16.gridx = 2;
gridBagConstraintsx16.gridy = 6;
gridBagConstraintsx16.insets = new Insets(5,5,5,5);
cPane.add(jbnExit, gridBagConstraintsx16);
jbbSave.addActionListener(this);
jbnDelete.addActionListener(this);
jbnClear.addActionListener(this);
jbnUpdate.addActionListener(this);
jbnSearch.addActionListener(this);
jbnForward.addActionListener(this);
jbnBack.addActionListener(this);
jbnExit.addActionListener(this);
}
public void actionPerformed (ActionEvent e){
if (e.getSource () == jbbSave){
savePerson();
clear();
}
else if (e.getSource() == jbnDelete){
deletePerson();
clear();
}
else if (e.getSource() == jbnUpdate){
updatePerson();
clear();
}
else if (e.getSource() == jbnSearch){
searchPerson();
}
else if (e.getSource() == jbnForward){
displayNextRecord();
}
else if (e.getSource() == jbnBack){
displayPreviousRecord();
}
else if (e.getSource() == jbnClear){
clear();
}
else if (e.getSource() == jbnExit){
System.exit(0);
}
}
// Guarda a la persona en la agenda
public void savePerson(){
name
= jtfName.getText();
name = name.toUpperCase();
//Almacena a todos los nombres
en mayusculas
address = jtfAddress.getText();
try{
phone = Integer.parseInt(""+jtfPhone.getText());
}catch(Exception e){
System.out.print("debe ingresar una cadena");
JOptionPane.showMessageDialog(null, "Digite un numero
de telefono");
}
email
= jtfEmail.getText();
if(name.equals("")){
JOptionPane.showMessageDialog(null, "Por favor digite
el nombre de una persona.");
}else{
//crea un objeto PersonInfo y le pasa un objeto
PersonDAO para almacenarlo
PersonInfo person = new PersonInfo(name, address,
phone, email);
pDAO.savePerson(person);
JOptionPane.showMessageDialog(null, "Persona
guardada");
}
}
public void deletePerson(){
name = jtfName.getText();
name = name.toUpperCase();
if(name.equals("")){
JOptionPane.showMessageDialog(null,"Por favor digite el
nombre de una persona.");
}
else{
//remueve una Persona con un nombre dado de la agenda
int numberOfDeleted = pDAO.removePerson(name);
JOptionPane.showMessageDialog(null, numberOfDeleted + "
Registro(s) borrado(s).");
}
}
public void updatePerson(){
if (recordNumber >= 0 && recordNumber < personsList.size())
{
PersonInfo person =
(PersonInfo)personsList.get(recordNumber);
int id = person.getId();
/*obtiene valores de los TextFields*/
name
= jtfName.getText();
address = jtfAddress.getText();
phone
= Integer.parseInt(jtfPhone.getText());
email
= jtfEmail.getText();
/*actualiza la informacion dado un nombre*/
person = new PersonInfo(id, name, address, phone, email);
pDAO.updatePerson(person);
JOptionPane.showMessageDialog(null, "Registro de la persona
actualizado con exito.");
}
else
{
JOptionPane.showMessageDialog(null, "No se actualizo
registro");
}
}
//Realiza una busqueda sin Case-Insensitive para buscar a una persona
public void searchPerson() {
name = jtfName.getText();
name = name.toUpperCase();
/*limpia contenidos del arraylist si hay algo de previas
busquedas*/
personsList.clear();
recordNumber = 0;
if(name.equals("")){
JOptionPane.showMessageDialog(null,"Por favor digite el
nombre para buscar.");
}
else{
/*obtiene un array list de las personas buscadas usando
PersonDAO*/
personsList = pDAO.searchPerson(name);
if(personsList.size() == 0){
JOptionPane.showMessageDialog(null, "No se
encontro.");
//Limpia si no se encuentra nada.
clear();
}
else
{
/*realiza un downcast del objeto del array list a
PersonInfo*/
PersonInfo person = (PersonInfo)
personsList.get(recordNumber);
// muestra el registro buscado en los textFields
jtfName.setText(person.getName());
jtfAddress.setText(person.getAddress());
jtfPhone.setText(""+person.getPhone());
jtfEmail.setText(person.getEmail());
}
}
}
public void displayNextRecord(){
// incrementa el recordNumber para desplegar la siguiente persona, ya
almacenada
// en personsList
recordNumber++;
if(recordNumber >= personsList.size()){
JOptionPane.showMessageDialog(null, "Llego al final de " +
"resultados de busqueda");
/*si el usuarios llega al final de los resultados,
deshabilita el boton >>*/
jbnForward.setEnabled(false);
jbnBack.setEnabled(true);
// decrementa en uno el contador del ultimo incremento
recordNumber -- ;
}
else{
jbnBack.setEnabled(true);
PersonInfo person = (PersonInfo)
personsList.get(recordNumber);
// despliega el registro buscado en loe testfields
jtfName.setText(person.getName());
jtfAddress.setText(person.getAddress());
jtfPhone.setText(""+person.getPhone());
jtfEmail.setText(person.getEmail());
}
}
public void displayPreviousRecord(){
// decrementa el recordNumber para mostrar la persona previa
recordNumber--;
if(recordNumber < 0 ){
JOptionPane.showMessageDialog(null, "Llego al principio " +
"de la busqueda");
/*si el usuario esta en el inicio deshabilita el boton
de <<*/
jbnForward.setEnabled(true);
jbnBack.setEnabled(false);
// incrementa en uno
recordNumber++;
}else{
jbnForward.setEnabled(true);
PersonInfo person = (PersonInfo)
personsList.get(recordNumber);
jtfName.setText(person.getName());
jtfAddress.setText(person.getAddress());
jtfPhone.setText(""+person.getPhone());
jtfEmail.setText(person.getEmail());
}
}
public void clear(){
jtfName.setText("");
jtfAddress.setText("");
jtfPhone.setText("");
jtfEmail.setText("");
recordNumber = -1;
personsList.clear();
jbnForward.setEnabled(true);
jbnBack.setEnabled(true);
}
}