I am just learning SQL and this is my first task to incorporate it into a Java app. I am having issues with multiple words input by the user and it's breaking. If I only type on word with no spaces everything works fine. I would really appreciate some help!
Here are some screenshots of the errors (It may have to do with the scanner, but for the life of me I cannot fix it!) The code is below.
If I type in 2 words, displays the following 2 the .next() together.
But if I only type 1 word it doesn't break.
The same for 'searching' by title, if I type in more than one word the following error occurs.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
// This is a simple bookstore application that allows the user to add, update,
// delete and search for books in the MySQL database.
// Book class
class Books {
public int id;
public String title;
public String author;
public int qty;
public String search;
public Books() {
}
public Books(int id, String title, String author, int qty) {
this.id = id;
this.title = title;
this.author = author;
this.qty = qty;
}
}
// BookStore Class
public class BookStore {
public int choice;
static int id = 0;
static String title = "";
static String author = "";
static int qty = 0;
public static void main(String[] args) throws SQLException {
boolean quit = false;
Scanner input = new Scanner(System.in);
int menu = 0;
System.out.println("Betty's Book Store\n-----------------------------");
// SQL details
String url = "jdbc:mysql://localhost:3306/ebookstore_db?useSSL=false";
String user = "otheruser";
String password = "swordfish";
PreparedStatement myStmt = null;
Connection myConn = null;
do {
// Menu
System.out.println();
System.out.println("1. Enter book");
System.out.println("2. Update book");
System.out.println("3. Delete book");
System.out.println("4. Search books");
System.out.println("5. Exit");
System.out.print("\nPlease enter your choice: ");
menu = input.nextInt();
System.out.println();
switch (menu) {
case 1:
System.out.println("1. Enter book\n-----------------------------");
try {
// Add a new book
System.out.print("Book ID: ");
int id = input.nextInt();
System.out.print("Book Title: ");
String title = input.next();
System.out.print("Book Author: ");
String author = input.next();
System.out.print("Quantity: ");
int qty = input.nextInt();
// 1. Get a connection to database
myConn = DriverManager.getConnection(url, user, password);
// 2. Create a statement
String sql = "INSERT INTO books " + " (id, title, author, qty)" + " values (?, ?, ?, ?)";
myStmt = myConn.prepareStatement(sql);
// Set parameter values
myStmt.setInt(1, id);
myStmt.setString(2, title);
myStmt.setString(3, author);
myStmt.setInt(4, qty);
// 3. Execute SQL query
myStmt.executeUpdate();
System.out.println("Insert complete.");
printAllFromTable(myStmt);
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
break;
case 2:
// Update a book's data
System.out.println("2. Update book\n-----------------------------");
try {
// Search for book by ID
System.out.print("Enter Book ID: ");
int searchId = input.nextInt();
int id = searchId;
ResultSet results;
// 1. Get a connection to database
myConn = DriverManager.getConnection(url, user, password);
// 2. Create a statement
String sql = "SELECT * FROM books WHERE id = ?";
myStmt = myConn.prepareStatement(sql);
// Set parameter values
myStmt.setInt(1, id);
// Set parameter values
myStmt.setInt(1, id);
// 3. Execute SQL query
results = myStmt.executeQuery();
// Loop over the results, printing them all.
while (results.next()) {
System.out.println(results.getInt("id") + ", " + results.getString("title") + ", "
+ results.getString("author") + ", " + results.getInt("qty") + "\n");
}
// Close up our connections
results.close();
// Sub menu for updating book data after selecting ID
System.out.println("1. Update ID");
System.out.println("2. Update Title");
System.out.println("3. Update Author");
System.out.println("4. Update Quantity");
System.out.println("5. Main menu");
do {
System.out.print("\nPlease enter your choice: ");
menu = input.nextInt();
System.out.println();
switch (menu) {
case 1:
try {
// Update Book ID
System.out.print("Enter New ID: ");
int updateId = input.nextInt();
int id2 = updateId;
// 1. Get a connection to database
myConn = DriverManager.getConnection(url, user, password);
// 2. Create a statement
String sql1 = "UPDATE books SET id = ? WHERE id = ?";
myStmt = myConn.prepareStatement(sql1);
// Set parameter values
myStmt.setInt(1, id2);
myStmt.setInt(2, id);
// 3. Execute SQL query
myStmt.execute();
System.out.println("ID updated.");
printAllFromTable(myStmt);
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
break;
case 2:
try {
// Update Book title
System.out.print("Enter New Title: ");
String title = input.next();
// 1. Get a connection to database
myConn = DriverManager.getConnection(url, user, password);
// 2. Create a statement
String sql1 = "UPDATE books SET title = ? WHERE id = ?";
myStmt = myConn.prepareStatement(sql1);
// Set parameter values
myStmt.setString(1, title);
myStmt.setInt(2, id);
// 3. Execute SQL query
myStmt.execute();
System.out.println("Title updated.");
printAllFromTable(myStmt);
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
break;
case 3:
try {
// Update Book Author
System.out.print("Enter New Author: ");
String author = input.next();
// 1. Get a connection to database
myConn = DriverManager.getConnection(url, user, password);
// 2. Create a statement
String sql1 = "UPDATE books SET author = ? WHERE id = ?";
myStmt = myConn.prepareStatement(sql1);
// Set parameter values
myStmt.setString(1, author);
myStmt.setInt(2, id);
// 3. Execute SQL query
myStmt.execute();
System.out.println("Autor updated.");
printAllFromTable(myStmt);
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
break;
case 4:
try {
// Update Book Quantity
System.out.print("Enter New Quantity: ");
int qty = input.nextInt();
// 1. Get a connection to database
myConn = DriverManager.getConnection(url, user, password);
// 2. Create a statement
String sql1 = "UPDATE books SET qty = ? WHERE id = ?";
myStmt = myConn.prepareStatement(sql1);
// Set parameter values
myStmt.setInt(1, qty);
myStmt.setInt(2, id);
// 3. Execute SQL query
myStmt.execute();
System.out.println("Quantity updated.");
printAllFromTable(myStmt);
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
break;
case 5:
break;
default:
System.out.println("Invalid Entry");
}
} while (menu != 5);
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
break;
case 3:
// Delete a book from the database
System.out.println("3. Delete book\n-----------------------------");
try {
// Search
System.out.print("Enter Book ID to delete: ");
int searchId = input.nextInt();
int id = searchId;
// 1. Get a connection to database
myConn = DriverManager.getConnection(url, user, password);
// 2. Create a statement
String sql = "DELETE FROM books WHERE id = ?";
myStmt = myConn.prepareStatement(sql);
// Set parameter values
myStmt.setInt(1, id);
// 3. Execute SQL query
myStmt.executeUpdate();
System.out.println("Delete complete.\n");
// printAllFromTable(myStmt);
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
break;
case 4:
// Search for a book in the database
System.out.println("4. Search books\n-----------------------------");
System.out.println();
do {
// Sub menu for searching by ID, title or author
System.out.println("1. Enter ID");
System.out.println("2. Enter Title");
System.out.println("3. Enter Author");
System.out.println("4. Main menu");
System.out.print("\nPlease enter your choice: ");
menu = input.nextInt();
System.out.println();
switch (menu) {
case 1:
try {
// Search by book id
System.out.print("Enter Book ID: ");
int searchId = input.nextInt();
int id = searchId;
ResultSet results;
// 1. Get a connection to database
myConn = DriverManager.getConnection(url, user, password);
// 2. Create a statement
String sql = "SELECT * FROM books WHERE id = ?";
myStmt = myConn.prepareStatement(sql);
// Set parameter values
myStmt.setInt(1, id);
// 3. Execute SQL query
results = myStmt.executeQuery();
// Loop over the results, printing them all.
while (results.next()) {
System.out.println(results.getInt("id") + ", " + results.getString("title") + ", "
+ results.getString("author") + ", " + results.getInt("qty"));
}
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
break;
case 2:
try {
// Search by book title
System.out.print("Enter Book Title: ");
String searchTitle = input.next();
String title = searchTitle;
ResultSet results;
// 1. Get a connection to database
myConn = DriverManager.getConnection(url, user, password);
// 2. Create a statement
String sql = "SELECT * FROM books WHERE title = ?";
myStmt = myConn.prepareStatement(sql);
// Set parameter values
myStmt.setString(1, title);
// 3. Execute SQL query
results = myStmt.executeQuery();
// Loop over the results, printing them all.
while (results.next()) {
System.out.println(results.getInt("id") + ", " + results.getString("title") + ", "
+ results.getString("author") + ", " + results.getInt("qty"));
}
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
break;
case 3:
try {
// Search by book Author
System.out.print("Enter Author: ");
String searchAuthor = input.next();
String title = searchAuthor;
ResultSet results;
// 1. Get a connection to database
myConn = DriverManager.getConnection(url, user, password);
// 2. Create a statement
String sql = "SELECT * FROM books WHERE author = ?";
myStmt = myConn.prepareStatement(sql);
// Set parameter values
myStmt.setString(1, title);
// 3. Execute SQL query
results = myStmt.executeQuery();
// Loop over the results, printing them all.
while (results.next()) {
System.out.println(results.getInt("id") + ", " + results.getString("title") + ", "
+ results.getString("author") + ", " + results.getInt("qty"));
}
} catch (Exception exc) {
exc.printStackTrace();
} finally {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
break;
case 4:
break;
default:
System.out.println("Invalid Entry");
}
} while (menu != 4);
break;
case 5:
// Exits the program
System.out.println("Exiting...");
quit = true;
break;
default:
System.out.println("Invalid Entry");
}
} while (!quit);
if (input != null) {
input.close();
}
}
/**
* Method printing all values in all rows. Takes a statement to try to avoid
* spreading DB access too far.
*
* @param a statement on an existing connection
* @throws SQLException
*/
public static void printAllFromTable(Statement statement) throws SQLException {
ResultSet results = statement.executeQuery("SELECT id, title, author, qty FROM books");
while (results.next()) {
System.out.println(results.getInt("id") + ", " + results.getString("title") + ", "
+ results.getString("author") + ", " + results.getInt("qty"));
}
}
}
