Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- * To change this license header, choose License Headers in Project Properties.
- * To change this template file, choose Tools | Templates
- * and open the template in the editor.
- */
- package model;
- import java.io.IOException;
- import java.sql.Connection;
- import java.sql.DatabaseMetaData;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.ArrayList;
- import java.util.List;
- import java.sql.PreparedStatement;
- import java.util.logging.Level;
- import java.util.logging.Logger;
- /**
- * A mock implementation of the BooksDBInterface interface to demonstrate how to
- * use it together with the user interface.
- *
- * Your implementation should access a real database.
- *
- * @author anderslm@kth.se
- */
- public class BooksDb implements BooksDbInterface {
- private final List<Book> books;
- private Connection con;
- public BooksDb() {
- books = new ArrayList();
- con = null;
- }
- @Override
- public boolean connect(String database, String user, String pwd) throws IOException, SQLException {
- String server = "jdbc:mysql://localhost:3306/"
- + database
- + "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&UseClientEnc=UTF8";
- con = DriverManager.getConnection(server, user, pwd);
- return true;
- }
- @Override
- public void disconnect() throws IOException, SQLException {
- // mock implementation
- con.close();
- }
- /**
- *
- * @param searchTitle
- * @return
- * @throws IOException
- * @throws SQLException
- * @throws InvalidISBNException
- */
- @Override
- public ArrayList<Book> searchBooksByTitle(String searchTitle)
- throws IOException, SQLException, InvalidISBNException {
- PreparedStatement pstmt = null;
- ArrayList<Book> result = new ArrayList();
- try {
- String sql = "SELECT * FROM Book WHERE title LIKE ?";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1, "%" + searchTitle + "%");
- ResultSet rs = pstmt.executeQuery();
- while(rs.next()) {
- String isbn = rs.getString("isbn");
- String title = rs.getString("title");
- Book temp;
- temp = new Book(isbn, title);
- temp.setYear(rs.getInt("year"));
- temp.setRating(rs.getInt("rating"));
- temp.setPublisher(rs.getString("publisher"));
- temp.setUsername(rs.getString("username"));
- getAuthorByISBN(temp, isbn);
- getGenreByISBN(temp, isbn);
- result.add(temp);
- }
- } finally {
- pstmt.close();
- }
- return result;
- }
- private void getAuthorByISBN(Book temp, String isbn) throws SQLException{
- PreparedStatement pstmt = null;
- try {
- String sql = "SELECT * FROM Author A, CreatedBy C, Book B WHERE A.creatorID = C.creatorID AND C.isbn = B.isbn AND C.isbn = ?";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1,isbn);
- ResultSet rs = pstmt.executeQuery();
- while(rs.next()) {
- temp.addAuthor(new Author(rs.getString("firstName") + " " + rs.getString("lastName"), rs.getDate("dob").toLocalDate(), rs.getInt("creatorID")));
- }
- } finally {
- pstmt.close();
- }
- }
- private void getGenreByISBN(Book temp, String isbn) throws SQLException{
- PreparedStatement pstmt = null;
- try {
- String sql = "SELECT * FROM Genre WHERE isbn = ?";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1,isbn);
- ResultSet rs = pstmt.executeQuery();
- while(rs.next()) {
- temp.addGenre(rs.getString("genre"));
- }
- } finally {
- pstmt.close();
- }
- }
- @Override
- public ArrayList<Book> searchBooksByAuthor(String searchAuthor)
- throws IOException, SQLException, InvalidISBNException {
- PreparedStatement pstmt = null;
- ArrayList<Book> result = new ArrayList();
- try {
- String sql = "SELECT * FROM Book B, CreatedBy C, Author A "
- + "WHERE A.creatorID = C.creatorID AND C.isbn = B.isbn AND UPPER(REPLACE(CONCAT_WS('',firstName,lastName), ' ', '')) LIKE ?";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1, "%" + searchAuthor + "%");
- ResultSet rs = pstmt.executeQuery();
- while(rs.next()) {
- String isbn = rs.getString("isbn");
- String title = rs.getString("title");
- Book temp;
- temp = new Book(isbn, title);
- temp.setYear(rs.getInt("year"));
- temp.setRating(rs.getInt("rating"));
- temp.setPublisher(rs.getString("publisher"));
- temp.setUsername(rs.getString("username"));
- getAuthorByISBN(temp, isbn);
- getGenreByISBN(temp, isbn);
- result.add(temp);
- }
- } finally {
- pstmt.close();
- }
- return result;
- }
- @Override
- public ArrayList<Book> searchBooksByISBN(String searchISBN) throws IOException, SQLException, InvalidISBNException {
- ArrayList<Book> result = new ArrayList();
- PreparedStatement pstmt = null;
- try {
- String sql = "SELECT * FROM Book WHERE isbn LIKE ?";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1, "%" + searchISBN + "%");
- ResultSet rs = pstmt.executeQuery();
- while(rs.next()) {
- String isbn = rs.getString("isbn");
- String title = rs.getString("title");
- Book temp;
- temp = new Book(isbn, title);
- temp.setYear(rs.getInt("year"));
- temp.setRating(rs.getInt("rating"));
- temp.setPublisher(rs.getString("publisher"));
- temp.setUsername(rs.getString("username"));
- getAuthorByISBN(temp, isbn);
- getGenreByISBN(temp, isbn);
- result.add(temp);
- }
- } finally {
- pstmt.close();
- }
- return result;
- }
- @Override
- public void insertNewBook(Book b) throws SQLException {
- PreparedStatement pstmt = null;
- try {
- String sql = "INSERT INTO Book VALUES(?,?,?,?,'0',?,CURDATE())";
- con.setAutoCommit(false);
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1, b.getIsbn());
- pstmt.setString(2, b.getTitle());
- pstmt.setInt(3, b.getYear());
- pstmt.setString(4, b.getPublisher());
- pstmt.setString(5, b.getUsername());
- pstmt.executeUpdate();
- for(Author a : b.getAuthors()){
- if(a.getName() != null){
- sql = "INSERT INTO Author VALUES(?,?,?,?)";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1, a.getName().split("\\s+")[0]);
- pstmt.setString(2, a.getName().split("\\s+")[1]);
- pstmt.setDate(3, java.sql.Date.valueOf(a.getDateOfBirth()));
- pstmt.setInt(4, a.getCreatorID());
- pstmt.executeUpdate();
- }
- sql = "INSERT INTO CreatedBy VALUES(?,?,?,CURDATE())";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(2, b.getIsbn());
- pstmt.setInt(1, a.getCreatorID());
- pstmt.setString(3, b.getUsername());
- pstmt.executeUpdate();
- }
- for(String g : b.getGenres()){
- sql = "INSERT INTO Genre VALUES(?,?)";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1, b.getIsbn());
- pstmt.setString(2, g);
- pstmt.executeUpdate();
- }
- con.commit();
- } catch (SQLException ex) {
- con.rollback();
- throw ex;
- } finally {
- pstmt.close();
- con.setAutoCommit(true);
- }
- }
- @Override
- public void updateBookAuthor(Author a, String isbn, String username) throws SQLException{
- String sql = "";
- PreparedStatement pstmt = null;
- try {
- con.setAutoCommit(false);
- if(a.getName() != null){
- sql = "INSERT INTO Author VALUES(?,?,?,?)";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1, a.getName().split("\\s+")[0]);
- pstmt.setString(2, a.getName().split("\\s+")[1]);
- pstmt.setDate(3, java.sql.Date.valueOf(a.getDateOfBirth()));
- pstmt.setInt(4, a.getCreatorID());
- pstmt.executeUpdate();
- }
- sql = "INSERT INTO CreatedBy VALUES(?,?,?,CURDATE())";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(2, isbn);
- pstmt.setInt(1, a.getCreatorID());
- pstmt.setString(3, username);
- pstmt.executeUpdate();
- con.commit();
- } catch (SQLException ex) {
- con.rollback();
- throw ex;
- } finally {
- pstmt.close();
- con.setAutoCommit(true);
- }
- }
- /**
- *
- * @param searchString
- * @param mode
- * @throws SQLException
- */
- @Override
- public void removeBooks(String searchString, SearchMode mode) throws SQLException{
- PreparedStatement pstmt = null;
- try {
- String sql = "DELETE FROM Book WHERE isbn = ?";
- con.setAutoCommit(false);
- pstmt = con.prepareStatement(sql);
- ArrayList<String> isbn = getIsbnFromSearch(searchString, mode);
- for(String s : isbn){
- pstmt.setString(1, s);
- pstmt.executeUpdate();
- }
- con.commit();
- } catch (SQLException ex) {
- con.rollback();
- throw ex;
- } finally {
- pstmt.close();
- con.setAutoCommit(true);
- }
- }
- @Override
- public boolean checkPermission(String username) throws SQLException, IOException{
- PreparedStatement pstmt = null;
- try {
- connect("LABB1", "root", "basket97");
- String user = "";
- String sql = "SELECT Insert_priv FROM mysql.user WHERE user = ?";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1, username);
- ResultSet rs = pstmt.executeQuery();
- while(rs.next()){
- user = rs.getString("Insert_priv");
- System.out.println(user);
- }
- if(user.equals("N")){
- return false;
- }
- return true;
- } finally {
- pstmt.close();
- }
- }
- private ArrayList<String> getIsbnFromSearch(String searchString, SearchMode mode) throws SQLException{
- ArrayList<String> isbn = new ArrayList();
- PreparedStatement pstmt = null;
- try {
- switch(mode){
- case Title:
- String sql = "SELECT isbn FROM Book WHERE title LIKE ?";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1, "%" + searchString + "%");
- ResultSet rs = pstmt.executeQuery();
- while(rs.next()) {
- isbn.add(rs.getString("isbn"));
- }
- break;
- case ISBN:
- sql = "SELECT isbn FROM Book WHERE isbn LIKE ?";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1, "%" + searchString + "%");
- rs = pstmt.executeQuery();
- while(rs.next()) {
- isbn.add(rs.getString("isbn"));
- }
- break;
- case Author:
- sql = "SELECT DISTINCT B.isbn FROM Book B, CreatedBy C, Author A "
- + "WHERE A.creatorID = C.creatorID AND C.isbn = B.isbn AND (A.firstName LIKE ? OR A.lastName LIKE ?)";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1, "%" + searchString + "%");
- pstmt.setString(2, "%" + searchString + "%");
- rs = pstmt.executeQuery();
- while(rs.next()) {
- isbn.add(rs.getString("isbn"));
- }
- break;
- }
- } finally {
- pstmt.close();
- }
- return isbn;
- }
- @Override
- public void addRatingToBook(Book b) throws SQLException {
- String sql = null;
- PreparedStatement pstmt = null;
- try {
- con.setAutoCommit(false);
- sql = "UPDATE Book SET rating = ? WHERE isbn = ?";
- pstmt = con.prepareStatement(sql);
- pstmt.setInt(1, b.getRating());
- pstmt.setString(2, b.getIsbn());
- pstmt.executeUpdate();
- con.commit();
- } catch (SQLException ex) {
- con.rollback();
- throw ex;
- } finally {
- pstmt.close();
- con.setAutoCommit(true);
- }
- }
- @Override
- public void insertUserReview(String user, String isbn, String reviewText) throws SQLException{
- PreparedStatement pstmt = null;
- try {
- con.setAutoCommit(false);
- String sql = "INSERT INTO Reviews VALUES(?, ?, ?)";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1, user);
- pstmt.setString(2, isbn);
- pstmt.setString(3, reviewText);
- pstmt.executeUpdate();
- con.commit();
- } catch (SQLException ex) {
- con.rollback();
- throw ex;
- } finally {
- pstmt.close();
- con.setAutoCommit(true);
- }
- }
- @Override
- public String getUserReviews(String isbn) throws SQLException{
- String reviews = "";
- PreparedStatement pstmt = null;
- try {
- String sql = "SELECT * FROM Reviews WHERE isbn = ?";
- pstmt = con.prepareStatement(sql);
- pstmt.setString(1, isbn);
- ResultSet rs = pstmt.executeQuery();
- while(rs.next()) {
- reviews = reviews.concat(rs.getString("username"));
- reviews = reviews.concat(": ");
- reviews = reviews.concat(rs.getString("review"));
- reviews = reviews.concat("\n");
- }
- } finally {
- pstmt.close();
- }
- return reviews;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement