home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
ftp.disi.unige.it
/
2015-02-11.ftp.disi.unige.it.tar
/
ftp.disi.unige.it
/
pub
/
.person
/
CataniaB
/
teach-act
/
DB3
/
servlet-bean-jsp
/
DBMS.java
next >
Wrap
Text File
|
2001-04-02
|
8KB
|
285 lines
import java.sql.*;
import java.util.*;
import java.text.*;
/**
* Fornisce l'interfaccia al database.
*/
public class DBMS {
// parametri di connessione
String url = "jdbc:postgresql://arena.sci.univr.it/esercitazioni";
String user = "db01";
String passwd = "";
GregorianCalendar calendar = new GregorianCalendar();
private String StudenteSql =
"SELECT * FROM db01studenti WHERE matricola = ?";
private String StudentiSql =
"SELECT * FROM db01studenti";
private String EsamiStudenteSql =
"SELECT E.*, C.denominazione "+
"FROM db01esami E, db01corsi C "+
"WHERE E.materia = C.codice AND E.studente = ? "+
"ORDER BY E.data DESC";
private String insStudenteSql =
"INSERT INTO db01studenti "+
"VALUES (?, ?, ?, ?, ?, ?)";
/**
* Costruttore
*/
public DBMS()
throws DBMSException {
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException cnfe) {
throw new DBMSException(cnfe.getMessage());
}
}
/******************************************************************
* metodi makeXXXBean
*/
/**
* Popola il bean per uno studente
*/
private StudenteBean makeStudenteBean(ResultSet rs)
throws DBMSException {
try {
StudenteBean bean = new StudenteBean();
bean.setMatricola(rs.getString("matricola"));
bean.setCognome(rs.getString("congnome"));
bean.setNome(rs.getString("nome"));
bean.setFacolta(rs.getString("facolta"));
bean.setDataNascita(rs.getDate("data_nascita"));
bean.setCreditiOttenuti(rs.getInt("crediti_ottenuti"));
if (rs.wasNull()) bean.setCreditiOttenuti(-1);
return bean;
}
catch (SQLException e) {
throw new DBMSException(e.getMessage());
}
}
/**
* Popola il bean per un esame
*/
private EsameBean makeEsameBean(ResultSet rs)
throws DBMSException {
try {
EsameBean bean = new EsameBean();
bean.setStudente(rs.getString("studente"));
bean.setMateria(rs.getInt("materia"));
bean.setVoto(rs.getInt("voto"));
bean.setData(rs.getDate("data"));
if (rs.wasNull()) bean.setData(null);
return bean;
}
catch (SQLException e) {
throw new DBMSException(e.getMessage());
}
}
/**
* Popola il bean per un esame
* aggiungendo il nome della materia
*/
private EsameBean makeEsameBeanCompleto(ResultSet rs)
throws DBMSException {
try {
EsameBean bean = makeEsameBean(rs);
bean.setDenominazione(rs.getString("denominazione"));
return bean;
}
catch (SQLException e) {
throw new DBMSException(e.getMessage());
}
}
/**
* Popola il bean per un corso
*/
/*private CorsoBean makeCorsoBean(ResultSet rs)
throws DBMSException {
try {
CorsoBean bean = new CorsoBean();
bean.setCodice(rs.getInt("codice"));
bean.setFacolta(rs.getString("facolta"));
if (rs.wasNull()) bean.setFacolta("");
bean.setDenominazione(rs.getString("denominazione"));
bean.setCrediti(rs.getInt("crediti"));
bean.setProfessore(rs.getString("professore"));
return bean;
}
catch (SQLException e) {
throw new DBMSException(e.getMessage());
}
}*/
/********************************************************************
* metodi extractXXX
*/
/**
* Restituisce lo StudenteBean associato al parametro matricola
*/
public StudenteBean extractStudente(String matricola)
throws UnknownKeyDBMSException, DBMSException {
ResultSet rs = null;
Connection con = null;
PreparedStatement pst = null;
try {
con = DriverManager.getConnection(url,user,passwd);
pst = con.prepareStatement(StudenteSql);
pst.clearParameters();
pst.setString(1, matricola);
rs = pst.executeQuery();
if (rs.next())
return makeStudenteBean(rs);
else
throw new UnknownKeyDBMSException("Studente matricola=" + matricola + " non trovato!");
}
catch (SQLException e) {
throw new DBMSException(e.getMessage());
}
finally {
try {
con.close();
}
catch (SQLException e) {
throw new DBMSException(e.getMessage());
}
}
}
/**
* Restituisce un vettore di StudenteBean contenente TUTTI gli
* studenti
*/
public Vector extractStudenti()
throws DBMSException {
Vector lista = new Vector();
ResultSet rs = null;
Connection con = null;
Statement st = null;
try {
con = DriverManager.getConnection(url,user,passwd);
st = con.createStatement();
rs = st.executeQuery(StudentiSql);
while (rs.next())
lista.add(makeStudenteBean(rs));
return lista;
}
catch (SQLException e) {
throw new DBMSException(e.getMessage());
}
finally {
try {
con.close();
}
catch (SQLException e) {
throw new DBMSException(e.getMessage());
}
}
}
/**
* Restituisce un vettore di EsamiBean contenente TUTTI gli
* esami superati da uno studente
*/
public Vector extractEsamiStudente(String matricola)
throws DBMSException {
Vector lista = new Vector();
ResultSet rs = null;
Connection con = null;
PreparedStatement pst = null;
try {
con = DriverManager.getConnection(url,user,passwd);
pst = con.prepareStatement(EsamiStudenteSql);
pst.clearParameters();
pst.setString(1, matricola);
rs = pst.executeQuery();
while (rs.next())
lista.add(makeEsameBeanCompleto(rs));
return lista;
}
catch (SQLException e) {
throw new DBMSException(e.getMessage());
}
finally {
try {
con.close();
}
catch (SQLException e) {
throw new DBMSException(e.getMessage());
}
}
}
/********************************************************************
* metodi insXXX
*/
/**
* Inserisce uno studente
*/
public void insStudente(StudenteBean bean)
throws UnknownKeyDBMSException, DBMSException {
ResultSet rs = null;
Connection con = null;
PreparedStatement pst = null;
try {
con = DriverManager.getConnection(url,user,passwd);
pst = con.prepareStatement(insStudenteSql);
pst.clearParameters();
pst.setString(1, bean.getMatricola());
pst.setString(2, bean.getCognome());
pst.setString(3, bean.getNome());
pst.setString(4, bean.getFacolta());
calendar.setTime(bean.getDataNascita());
java.sql.Date data = new java.sql.Date(calendar.getTime().getTime());
pst.setDate(5, data);
pst.setInt(6, bean.getCreditiOttenuti());
pst.executeUpdate();
}
catch (SQLException e) {
throw new DBMSException(e.getMessage());
}
finally {
try {
con.close();
}
catch (SQLException e) {
throw new DBMSException(e.getMessage());
}
}
}
}