public class Book { private int id; private String isbn; private String author; private String title; private int pagesnumber; public Book(String author, String title) { this.author = author; this.title = title; } public Book(int id, String isbn, String author, String title, int pagesnumber) { this.id = id; this.isbn = isbn; this.author = author; this.title = title; this.pagesnumber = pagesnumber; } public int getId() { return id; } public String getIsbn() { return isbn; } public String getAuthor() { return author; } public String getTitle() { return title; } public int getPagesnumber() { return pagesnumber; } public void setId(int id) { this.id = id; } public void setIsbn(String isbn) { this.isbn = isbn; } public void setPagesnumber(int pagesnumber) { this.pagesnumber = pagesnumber; } @Override public String toString() { return author + ", " + title; } }
public class Database { private String host; private int port; private String schema; private String username; private String password; private Connection conn = null; public Database(String host, int port, String schema, String username, String password) { this.host = host; this.port = port; this.schema = schema; this.username = username; this.password = password; } public String getHost() { return host; } public int getPort() { return port; } public String getSchema() { return schema; } public void Connect() { try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection( "jdbc:mysql://"+host+":"+port+"/"+schema,username,password); } catch (Exception ex) { Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } } public void Close() { if (conn != null) try { conn.close(); } catch (SQLException ex) { Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex); } } public ArrayList<Book> getBooks() { ArrayList<Book> list = new ArrayList<Book>(); try { String sql = "select * from libri order by id"; PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery(); if (rs != null) { while(rs.next()) { String author = rs.getString("autore"); String title = rs.getString("titolo"); String isbn = rs.getString("isbn"); int id = rs.getInt("id"); int pagesnumber = rs.getInt("npagine"); Book b = new Book(id, isbn, author, title, pagesnumber); list.add(b); } } } catch(Exception ex) { } return list; } public ArrayList<Book> getBooks(String name, String surname) { ArrayList<Book> list = new ArrayList<Book>(); try { String sql = "select * from anagrafica a, letture l, libri b " + "where a.id = l.id_anagrafica and l.id_libro = b.id " + "and a.nome = ? and a.cognome = ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, name); ps.setString(2, surname); ResultSet rs = ps.executeQuery(); if (rs != null) { while(rs.next()) { String author = rs.getString("autore"); String title = rs.getString("titolo"); String isbn = rs.getString("isbn"); int id = rs.getInt("id"); int pagesnumber = rs.getInt("npagine"); Book b = new Book(id, isbn, author, title, pagesnumber); list.add(b); } } } catch(Exception ex) { } return list; } }
<html> <head> <title>Biblioteca</title> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> </head> <body> <div>Gestione dati della biblioteca</div> <h3><a href="books.jsp">elenco libri</a></h3> <hr/> <div>Ricerca dei libri letti dall'utente</div> <form action = "books.jsp" method = "POST"> nome: <input type = "text" name = "first_name"> <br /> cognome: <input type = "text" name = "last_name" /> <input type = "submit" value = "Submit" /> </form> </body> </html>
<%@page import="java.util.ArrayList"%> <%@page import="code.Book"%> <%@page import="code.Database"%> <%@page contentType="text/html" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>JSP Page - Books list</title> <style> table, th, td { border: 1px solid black; border-collapse: collapse; } th, td { padding: 15px; text-align: left; } table#t01 { width: 100%; background-color: #f1f1c1; } </style> </head> <body> <h1>Books list</h1> <% Database db = new Database("localhost", 3306, "biblioteca", "user", ""); db.Connect(); ArrayList<Book> list; // = db.getBooks(); //out.print(request.getParameter("first_name")); //out.print(request.getParameter("last_name")); String name = request.getParameter("first_name"); String lastname = request.getParameter("last_name"); if (name != null && lastname != null && name.length() > 0 && lastname.length() > 0) { list = db.getBooks(name, lastname); out.print("<p>libri letti da<b> "+name+" "+lastname+"</b></p>"); } else { list = db.getBooks(); out.print("<p>tutti i libri</p>"); } %> <hr/> <table id="t01"> <tr> <th>isbn</th> <th>Author</th> <th>Title</th> </tr> <% for(int i=0; i < list.size(); i++) { out.print("<tr>"); out.print("<td>"+list.get(i).getIsbn()+"</td>"); out.print("<td>"+list.get(i).getAuthor()+"</td>"); out.print("<td>"+list.get(i).getTitle()+"</td>"); out.print("</tr>"); } %> </table> </body> </html>