Esercizio no.1:soluzione

Conviene fare uso di metodi statici che venngono invocati dal programma principale (main). L'apertura della connessione avviene nel main() le chiamate SQL all'interno dei metodi. La connessione al db rimane, dunque aperta durante l'esecuzione dell'intero programma; viene chiusa all'uscita dello stesso.

import java.io.*;
import java.sql.*;
class database{
public static void main (String args [])throws IOException, SQLException {

InputStreamReader input=new InputStreamReader(System.in);
BufferedReader h= new BufferedReader(input);
Connection cn;
String url,s,ide;
char ch;
int num,id;
//connessione
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}catch ( ClassNotFoundException e) {
System.out.println("ClassNotFoundException: ");
System.err.println(e.getMessage());
}//fine try-catch
url = "jdbc:odbc:;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=data.mdb";
cn = DriverManager.getConnection(url,"","");
//connessione aperta
do{
System.out.println("v-vista");
System.out.println("i-inserimento");
System.out.println("c-cancellazione");
System.out.println("a-aggiornamento");
System.out.println("x-Exit");
s=(h.readLine()).toLowerCase();
ch = s.charAt(0);
switch (ch) {
case 'v': vista(cn);break;
case 'i': System.out.print("nome:");
          ide=h.readLine();
          System.out.print("target:");
          num=Integer.parseInt(h.readLine().trim());
          inserimento(cn,ide,num);
          break;
case 'c': System.out.print("i da cancellare:");
          num=Integer.parseInt(h.readLine().trim());
          cancella(cn,num);
          break;
case 'a': System.out.print("i:");
          id=Integer.parseInt(h.readLine().trim());
          System.out.print("nuovo nome:");
          ide=h.readLine();
          System.out.print("nuovo target:");
          num=Integer.parseInt(h.readLine().trim());
          aggiorna(cn,id,ide,num);
          break;
case 'x': System.out.println("EXIT"); break;
default: System.out.println("selezione non valida"); break;
}//fine switch
} while(ch!='x');
cn.close(); //connessione chiusa
}//fine main

static void vista(Connection cn) throws SQLException {
Statement st=cn.createStatement();
ResultSet rs;
String sql; sql="SELECT * FROM compensi;";
try{
rs=st.executeQuery(sql);
while(rs.next()==true)
System.out.println(rs.getString("i")+" "+ rs.getString("nome")+ "\t"+rs.getString("target"));
}catch(SQLException e) {
System.out.println("errore:"+e.getMessage());
}//fine try-catch
}//fine vista

static void inserimento(Connection cn,String ide,int num) throws SQLException {
Statement st=cn.createStatement();
ResultSet rs;
String sql;
sql="INSERT INTO compensi (nome,target) VALUES ('"+ide+"',"+num+");";
try{
st=cn.createStatement();
st.executeUpdate(sql);
}catch(SQLException e) {
System.out.println("errore:"+e.getMessage());
}//fine try-catch
}//fine inserimento

static void cancella(Connection cn,int num) throws SQLException {
Statement st=cn.createStatement();
ResultSet rs;
String sql;
sql="DELETE FROM compensi WHERE i="+num+";";
try{
st=cn.createStatement();
st.executeUpdate(sql);
}catch(SQLException e) {
System.out.println("errore:"+e.getMessage());
}//fine try-catch
}//fine cancella

static void aggiorna(Connection cn,int id,String ide,int num) throws SQLException {
Statement st=cn.createStatement();
ResultSet rs;
String sql;
sql="UPDATE compensi SET nome='"+ide+"', target="+num+" WHERE i="+id+";";
try{
st=cn.createStatement();
st.executeUpdate(sql);
}catch(SQLException e) {
System.out.println("errore:"+e.getMessage());
}//fine try-catch
}//fine aggiorna
}// fine classe main