DAO
Aqui van las clase que van a realizar las transacciones con mysql.creamos el paquete com.tutosoftware.autotransport.dao y en el creamos la clase LicenciaDAO
package com.tutosoftware.autotransport.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.tutosoftware.autotransport.domain.Licencia;
import com.tutosoftware.autotransport.util.DataConnect;
public class LicenciaDAO {
public void insertarLicencia(Licencia licencia) {
// TODO Auto-generated method stub
Connection cn;
Statement st;
try {
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql;
tsql = " insert into licencia values('"+licencia.getIdLicencia()+"',";
tsql+= "'"+licencia.getNombre()+"',";
tsql+= "'"+licencia.getApellidoPaterno()+"',";
tsql+= "'"+licencia.getApellidoMaterno()+"',";
tsql+= "'"+licencia.getFechaObtencion()+"',";
tsql+= "'"+licencia.getFechaExpiracion()+"',";
tsql+= "'"+licencia.getTipoLetra()+"')";
st.execute(tsql);
cn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
public Licencia buscarLicencia(String idLicencia) {
Connection cn = null;
Licencia licencia = new Licencia();
Statement st;
ResultSet rs;
try{
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql = "Select * from licencia where idlicencia = '"+idLicencia+"'";
rs = st.executeQuery(tsql);
if(!rs.next()) {
System.out.println("No hay licencias disponibles con ese ID");
}else {
licencia.setIdLicencia(rs.getString("idlicencia"));
licencia.setNombre(rs.getString("nombre"));
licencia.setApellidoPaterno(rs.getString("apellidoPaterno"));
licencia.setApellidoMaterno(rs.getString("apellidoMaterno"));
licencia.setFechaObtencion(rs.getDate("fechaObtencion"));
licencia.setFechaExpiracion(rs.getDate("fechaExpiracion"));
licencia.setTipoLetra(rs.getString("tipoLetra"));
}
cn.close();
}catch(Exception e){
e.printStackTrace();
}
return licencia;
}
public void actualizarLicencia(Licencia licencia) {
Connection cn;
Statement st;
try {
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql;
tsql = " update licencia set nombre ='"+licencia.getNombre()+"',";
tsql+= "apellidoPaterno ='"+licencia.getApellidoPaterno()+"',";
tsql+= "apellidoMaterno ='"+licencia.getApellidoMaterno()+"',";
tsql+= "fechaObtencion ='"+licencia.getFechaObtencion()+"',";
tsql+= "fechaExpiracion ='"+licencia.getFechaExpiracion()+"',";
tsql+= "tipoLetra ='"+licencia.getTipoLetra()+"' where idlicencia='"+licencia.getIdLicencia()+"'";
st.execute(tsql);
cn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
public void eliminarLicencia(String idLicencia) {
Connection cn;
Statement st;
try {
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql;
tsql = "delete from licencia where idlicencia ='"+idLicencia+"' ";
st.execute(tsql);
cn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
Creamos la clase TransporteDAO
package com.tutosoftware.autotransport.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.tutosoftware.autotransport.domain.Transporte;
import com.tutosoftware.autotransport.util.DataConnect;
public class TransporteDAO {
public void insertarTrasporte(Transporte t) {
Connection cn;
Statement st;
try {
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql;
tsql = " insert into transporte values(null,'"+t.getMatricula()+"',";
tsql+= "'"+t.getMarca()+"',";
tsql+= "'"+t.getModelo()+"',";
tsql+= "'"+t.getCarga()+"',";
tsql+= "'"+t.getTipoLicencia()+"',";
tsql+= "'Disponible')";
st.execute(tsql);
cn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
public Transporte buscarTrasporte(int idTransporte) {
Connection cn = null;
Transporte t = new Transporte();
Statement st;
ResultSet rs;
try{
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql = "Select * from transporte where idTransporte = "+idTransporte;
rs = st.executeQuery(tsql);
if(!rs.next()) {
System.out.println("No hay trasportes disponibles");
}else {
t.setIdTrasporte(rs.getInt("idTransporte"));
t.setMatricula(rs.getString("matricula"));
t.setMarca(rs.getString("marca"));
t.setModelo(rs.getString("modelo"));
t.setCarga(rs.getString("carga"));
t.setTipoLicencia(rs.getString("tipoLicencia"));
t.setDisponibilidad(rs.getString("disponibilidad"));
}
cn.close();
}catch(Exception e){
e.printStackTrace();
}
return t;
}
public Transporte buscarTrasporte(int idTransporte,String tipoLicencia) {
Connection cn = null;
Transporte t = new Transporte();
Statement st;
ResultSet rs;
try{
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql = "Select * from transporte where idTransporte = "+idTransporte+ " and tipoLicencia ='"+tipoLicencia+"' ";
tsql +=" and disponibilidad = 'Disponible' ";
//System.out.println(tsql);
rs = st.executeQuery(tsql);
if(!rs.next()) {
System.out.println("No hay trasportes disponibles");
}else {
t.setIdTrasporte(rs.getInt("idTransporte"));
t.setMatricula(rs.getString("matricula"));
t.setMarca(rs.getString("marca"));
t.setModelo(rs.getString("modelo"));
t.setCarga(rs.getString("carga"));
t.setTipoLicencia(rs.getString("tipoLicencia"));
t.setDisponibilidad(rs.getString("disponibilidad"));
}
cn.close();
}catch(Exception e){
e.printStackTrace();
}
return t;
}
public void actualizarTransporte(Transporte t) {
Connection cn;
Statement st;
try {
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql;
tsql = " update transporte set matricula ='"+t.getMatricula()+"',";
tsql+= "marca ='"+t.getMarca()+"',";
tsql+= "modelo ='"+t.getModelo()+"',";
tsql+= "carga ='"+t.getCarga()+"',";
tsql+= "tipoLicencia ='"+t.getTipoLicencia()+"',";
tsql+= "disponibilidad ='"+t.getDisponibilidad()+"' where idTransporte="+t.getIdTrasporte();
st.execute(tsql);
cn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
public void eliminarTransporte(int idTransporte) {
Connection cn;
Statement st;
try {
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql;
tsql = "delete from transporte where idTransporte = "+idTransporte;
st.execute(tsql);
cn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
public void mostrarTransportes(){
Connection cn = null;
Statement st;
ResultSet rs;
try{
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql = "Select * from transporte";
rs = st.executeQuery(tsql);
System.out.println("IDTransporte Matricula Marca Modelo Carga TipoLicencia Dsiponibilidad ");
while(rs.next()){
System.out.println(rs.getInt("idTransporte")+" "+rs.getString("matricula")+" "+rs.getString("marca")+" "+rs.getString("modelo")+" "+rs.getString("carga")+" "+rs.getString("tipoLicencia")+" "+rs.getString("disponibilidad"));
}
cn.close();
}catch(Exception e){
e.printStackTrace();
}
}
public int mostrarTransportePorTipoLicenciaDisponibilidad(String tipoLicencia) {
Connection cn = null;
int bandera=0;
Statement st;
ResultSet rs;
try{
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql = "Select * from transporte where tipoLicencia= '"+tipoLicencia+"' and disponibilidad= 'Disponible' ";
rs = st.executeQuery(tsql);
System.out.println("IDTransporte Matricula Marca Modelo Carga TipoLicencia Dsiponibilidad ");
while(rs.next()){
System.out.println(rs.getInt("idTransporte")+" "+rs.getString("matricula")+" "+rs.getString("marca")+" "+rs.getString("modelo")+" "+rs.getString("carga")+" "+rs.getString("tipoLicencia")+" "+rs.getString("disponibilidad"));
++bandera;
}
cn.close();
}catch(Exception e){
e.printStackTrace();
}
return bandera;
}
}
La clase TransportistaDAO
package com.tutosoftware.autotransport.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.tutosoftware.autotransport.domain.Transportista;
import com.tutosoftware.autotransport.util.DataConnect;
public class TransportistaDAO {
public void insertarTransportista(Transportista t) {
Connection cn;
Statement st;
try {
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql,tsql2;
tsql = " insert into transportista values(null,"+t.getIdTransporte()+",";
tsql+= "'"+t.getIdLicencia()+"',";
tsql+= "'"+t.getNombre()+"',";
tsql+= "'"+t.getApellidoPaterno()+"',";
tsql+= "'"+t.getApellidoMaterno()+"',";
tsql+= "'"+t.getTelefono()+"') ";
tsql2 = " update transporte set disponibilidad = 'Ocupado' where idTransporte= "+t.getIdTransporte();
st.execute(tsql);
st.execute(tsql2);
cn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
public Transportista buscarTransportista(int idTransportista) {
Connection cn = null;
Transportista t = new Transportista();
Statement st;
ResultSet rs;
try{
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql = "Select * from transportista where idTransportista = "+idTransportista;
rs = st.executeQuery(tsql);
if(!rs.next()) {
System.out.println("No hay trasportistas disponibles");
}else {
t.setIdTransportista(rs.getInt("idTransportista"));
t.setIdTransporte(rs.getInt("idTransporte"));
t.setIdLicencia(rs.getString("idlicencia"));
t.setNombre(rs.getString("nombre"));
t.setApellidoPaterno(rs.getString("apellidoPaterno"));
t.setApellidoMaterno(rs.getString("apellidoMaterno"));
t.setTelefono(rs.getString("telefono"));
}
cn.close();
}catch(Exception e){
e.printStackTrace();
}
return t;
}
public void actualizarTrasportista(Transportista t) {
Connection cn;
Statement st;
try {
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql;
tsql = " update transportista set nombre ='"+t.getNombre()+"',";
tsql+= "apellidoPaterno ='"+t.getApellidoPaterno()+"',";
tsql+= "apellidoMaterno ='"+t.getApellidoMaterno()+"',";
tsql+= "telefono ='"+t.getTelefono()+"' where idTransportista="+t.getIdTransportista();
st.execute(tsql);
cn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
public void eliminarTransportista(int idTransportista) {
Connection cn;
Statement st;
try {
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql;
tsql = "delete from transportista where idTransportista = "+idTransportista;
st.execute(tsql);
cn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
public void mostrarTransportistas() {
Connection cn = null;
Statement st;
ResultSet rs;
try{
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql = "Select * from transportista";
rs = st.executeQuery(tsql);
System.out.println("IDTransportista IDTransporte Licencia Nombre ApellidoPaterno ApellidoMaterno Telefono ");
while(rs.next()){
System.out.println(rs.getInt("idTransportista")+" "+rs.getInt("idTransporte")+" "+rs.getString("idlicencia")+" "+rs.getString("nombre")+" "+rs.getString("apellidoPaterno")+" "+rs.getString("apellidoMaterno")+" "+rs.getString("telefono"));
}
cn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
La clase ViajeDAO
package com.tutosoftware.autotransport.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.tutosoftware.autotransport.domain.Viaje;
import com.tutosoftware.autotransport.util.DataConnect;
public class ViajeDAO {
public void insertarViaje(Viaje v) {
Connection cn;
Statement st;
try {
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql;
tsql = " insert into viajes values(null,'"+v.getFecha()+"',";
tsql+= ""+v.getIdTransporte()+",";
tsql+= ""+v.getIdTransportista()+",";
tsql+= "'"+v.getNombre()+"',";
tsql+= "'"+v.getApellidoPaterno()+"',";
tsql+= "'"+v.getApellidoMaterno()+"',";
tsql+= "'"+v.getDescripcion()+"') ";
//System.out.println(tsql);
st.execute(tsql);
cn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
public void mostrarViajes() {
Connection cn = null;
Statement st;
ResultSet rs;
try{
cn = DataConnect.getConnection();
st = cn.createStatement();
String tsql = "Select * from viajes";
rs = st.executeQuery(tsql);
while(rs.next()){
System.out.println("ID viaje: "+rs.getInt("idViaje"));
System.out.println("Fecha: "+rs.getDate("fecha"));
System.out.println("ID Transporte: "+rs.getInt("idTransporte"));
System.out.println("ID Transportista: "+rs.getInt("idTransportista"));
System.out.println("Nombre: "+rs.getString("nombre"));
System.out.println("Apellido Paterno: "+rs.getString("apellidoPaterno"));
System.out.println("Apellido Materno: "+rs.getString("apellidoMaterno"));
System.out.println("Descripción: "+rs.getString("descripcion"));
}
cn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}