books

public class Book {

    final String name;
    final String isbn;
    final String authorName;

    public Book(String name, String isbn, String authorName) {
        this.name = name;
        this.isbn = isbn;
        this.authorName = authorName;
    }

    @Override
    public String toString() {
        return String.format("%s by %s (ISBN: %s)", name, authorName, isbn);
    }
}


/**
 * An abstraction for the database operations
 * Provides a simpler interface for interacting with data
 */
public class BooksRepository {

    // You will need something like below to connect to an Oracle database
    // private static final String CONNECTION_URL = "jdbc:oracle:thin:localhost:1521/";
    // private static final String DB_USER = "abhijeet";
    //private static final String DB_PASSWORD = "something";

    // Here I am using an SQLite database for demonstration
    // shouldn't make much difference except for query syntax and data types.
    private static final String CONNECTION_URL = "jdbc:sqlite:books.db";

    private final Connection connection;

    public BooksRepository() throws SQLException {
        this.connection = DriverManager.getConnection(CONNECTION_URL);
    }

    public void insertBook(Book book) throws SQLException {
        final PreparedStatement statement =
                connection.prepareStatement("INSERT INTO Books(name, authorName, isbn) values(?, ?, ?)");

        statement.setString(1, book.name);
        statement.setString(2, book.authorName);
        statement.setString(3, book.isbn);

        statement.executeUpdate();
    }

    /**
     * Fetches all books from the database
     */
    public List<Book> getAllBooks() throws SQLException {
        final Statement statement = connection.createStatement();
        final ResultSet result = statement.executeQuery("SELECT * FROM Books");

        final ArrayList<Book> books = new ArrayList<Book>();

        while (result.next()) {
            final String name = result.getString("name");
            final String isbn = result.getString("isbn");
            final String authorName = result.getString("authorName");

            books.add(new Book(name, isbn, authorName));
        }

        return books;
    }

    /**
     * Fetches a book with specific ISBN
     * if no such book exists, null is returned
     */
    public Book getBookByIsbn(String matchIsbn) throws SQLException {
        final PreparedStatement statement = connection.prepareStatement("SELECT * FROM BOOKS WHERE isbn = ?");

        statement.setString(1, matchIsbn);
        final ResultSet result = statement.executeQuery();

        if (result.next()) {
            final String name = result.getString("name");
            final String isbn = result.getString("isbn");
            final String authorName = result.getString("authorName");

            return new Book(name, isbn, authorName);
        } else {
            return null;
        }
    }

    /**
     * Fetches all the books with the name that match a given string
     */
    public List<Book> getBooksByName(String matchName) throws SQLException {
        final PreparedStatement statement = connection.prepareStatement("SELECT * FROM Books WHERE name LIKE ?");

        statement.setString(1, '%' + matchName + '%');
        final ResultSet result = statement.executeQuery();

        final ArrayList<Book> books = new ArrayList<Book>();

        while (result.next()) {
            final String name = result.getString("name");
            final String isbn = result.getString("isbn");
            final String authorName = result.getString("authorName");

            books.add(new Book(name, isbn, authorName));
        }

        return books;
    }

    public void close() throws SQLException {
        if (!this.connection.isClosed()) {
            this.connection.close();
        }
    }
}


public class Main {

    private static Book inputBook() {
        System.out.println("Enter book name, author and isbn on separate lines:");
        String name = Stdin.readLine();
        String authorName = Stdin.readLine();
        String isbn = Stdin.readLine();

        return new Book(name, authorName, isbn);
    }

    public static void main(String[] args) throws SQLException {

        final BooksRepository repo = new BooksRepository();

        // Add a new book to database
        final Book newBook = inputBook();
        repo.insertBook(newBook);

        // List out all books
        System.out.println("All books:");

        final List<Book> allBooks = repo.getAllBooks();
        for (final Book book : allBooks) {
            System.out.println(book);
        }

        // Search for books
        System.out.println("Books with search term:");

        final List<Book> booksWithSInName = repo.getBooksByName("s");
        for (final Book book : booksWithSInName) {
            System.out.println(book);
        }

        // Look up a specific book
        System.out.println("Book with specific ISBN:");

        final Book myBook = repo.getBookByIsbn("123");
        System.out.println(myBook);

        // Close out repository after we are done with it
        repo.close();
    }
}