Skip to content

Instantly share code, notes, and snippets.

@torch2424
Last active Oct 15, 2015
Embed
What would you like to do?
Java code for the jdbc project
---JAVA CODE---
/*
* 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 jdbcproject;
//Import sql packages
import java.sql.*;
import java.util.Scanner;
/**
*
* @author torch
*/
public class JDBCProject {
// JDBC driver name and database URL
static final String JDBC_DRIVER = "org.apache.derby.jdbc.ClientDriver";
static final String DB_URL = "jdbc:derby://localhost:1527/Project1";
// Database credentials
static final String USER = "Aaron";
static final String PASS = "test";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
//STEP 2: Register JDBC driver
Class.forName(JDBC_DRIVER);
//STEP 3: Open a connection
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
//STEP 4: Execute a query
System.out.println("Creating statement...");
stmt = conn.createStatement();
//Welcome user!
System.out.println("Welcome to the database query program!");
//Initialize our querying variable
boolean querying = true;
try ( //Our input scanner
Scanner scan = new Scanner(System.in)) {
while(querying)
{
//Print the menu
System.out.println("Please select a query below!");
//5 available options
System.out.println("1. List all album titles");
System.out.println("2. List all the data for a specified albulm");
System.out.println("3. Insert a new ablum");
System.out.println("4. Insert a new studio");
System.out.println("5.Remove an albulm");
System.out.println("6.Exit");
System.out.println();
//Get the input
String input = scan.nextLine();
//Try to get the value
//may throw exception if not a number
int answer;
try
{
answer = Integer.valueOf(input);
}
catch(Exception e)
{
answer = -1;
}
//Execute the function and error check
//Is it an integer
if(answer == -1
|| answer < 1
|| answer > 6)
{
//User entered incorrect input
System.out.println("Incorrect Input!");
System.out.println("Please enter a number 1-6");
}
else if(answer == 1)
{
//Get all albulm titles, execute the sql
String sql = "SELECT atitle FROM albulms";
//Simply loop through the result set for the albulms
try (ResultSet rs = stmt.executeQuery(sql)) {
//Simply loop through the result set for the albulms
System.out.println("Titles:");
while(rs.next()){
//Retrieve by column name
String title = rs.getString("atitle");
//Display values
System.out.println(title);
}
//Close the result set
rs.close();
}
catch(Exception e) {
//Handle errors for Class.forName
e.printStackTrace();
//Inform user of error
System.out.println("Error! Could Not get result from the Database");
}
}
else if(answer == 2)
{
//List all the data for a specified albulm
System.out.println("Please enter the title of the album you would like to view");
//Get the albulm title
input = scan.nextLine();
//Query the DB
String sql = "SELECT * FROM albulms WHERE LOWER(atitle) LIKE LOWER('%" + input + "%')";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next())
{
//Retrieve by column name
String title = rs.getString("atitle");
String group = rs.getString("groupname");
String studioName = rs.getString("sname");
String date = rs.getString("daterec");
String length = rs.getString("length");
String numSongs = rs.getString("numsongs");
//Display values
System.out.print("Title: ");
System.out.println(title);
System.out.print("Group: ");
System.out.println(group);
System.out.print("Studio: ");
System.out.println(studioName);
System.out.print("Date Recorded: ");
System.out.println(date);
System.out.print("Length: ");
System.out.println(length);
System.out.print("Number Of Songs: ");
System.out.println(numSongs);
}
else
{
System.out.println("That album does not exist...");
}
rs.close();
}
else if(answer == 3)
{
//Insert a new albulm
String title = "";
String groupName = "";
String studioName = "";
String dateRec = "";
String aLength = "";
String numSongs = "";
//Get the albulm title
System.out.println("Please enter a name for the album");
title = scan.nextLine();
//Please select an artist for the album
boolean getGroup = true;
while(getGroup)
{
System.out.println("Please enter a recording group for the album, from the options below.");
String sql = "SELECT groupname FROM recgroup";
//Simply loop through the result set for the albulms
try (ResultSet rs = stmt.executeQuery(sql)) {
//Simply loop through the result set for the albulms
System.out.println("Recording Groups:");
while(rs.next()){
//Retrieve by column name
String groups = rs.getString("groupname");
//Display values
System.out.println(groups);
}
//Close the result set
rs.close();
}
catch(Exception e) {
//Handle errors for Class.forName
e.printStackTrace();
//Inform user of error
System.out.println("Error! Could Not get result from the Database");
}
//Get the input
groupName = scan.nextLine();
//Check if it is a valid group
//Query the DB
String sqlCheck = "SELECT groupname FROM recgroup WHERE LOWER(groupname) LIKE LOWER('%" + groupName + "%')";
try (ResultSet rsCheck = stmt.executeQuery(sqlCheck)) {
if(rsCheck.next())
{
//Good it exists!
getGroup = false;
}
else
{
//Not a valid group
System.out.println("Group does not exist, please select an available group");
System.out.println();
}
rsCheck.close();
}
catch(Exception e) {
//Handle errors for Class.forName
e.printStackTrace();
//Inform user of error
System.out.println("Error! Could Not get result from the Database");
}
}
//Please select an artist for the album
boolean getStudio = true;
while(getStudio)
{
System.out.println("Please enter a recording studio for the album, from the options below.");
String sql = "SELECT sname FROM recstudio";
//Simply loop through the result set for the albulms
try (ResultSet rs = stmt.executeQuery(sql)) {
//Simply loop through the result set for the albulms
System.out.println("Recording Studios:");
while(rs.next()){
//Retrieve by column name
String studios = rs.getString("sname");
//Display values
System.out.println(studios);
}
//Close the result set
rs.close();
}
catch(Exception e) {
//Handle errors for Class.forName
e.printStackTrace();
//Inform user of error
System.out.println("Error! Could Not get result from the Database");
}
//Get the input
studioName = scan.nextLine();
//Check if it is a valid group
//Query the DB
String sqlCheck = "SELECT sname FROM recstudio WHERE LOWER(sname) LIKE LOWER('%" + studioName + "%')";
try (ResultSet rsCheck = stmt.executeQuery(sqlCheck)) {
if(rsCheck.next())
{
//Good it exists!
getStudio = false;
}
else
{
//Not a valid group
System.out.println("Studio does not exist, please select an available group");
System.out.println();
}
//Close the result set
rsCheck.close();
}
catch(Exception e) {
//Handle errors for Class.forName
e.printStackTrace();
//Inform user of error
System.out.println("Error! Could Not get result from the Database");
}
}
//Set the Date Recorded
boolean getDate = true;
while(getDate)
{
//Tell user to enter a valid date
System.out.println("Please enter a Date that the albulm was recorded (Format: yyyy-mm-dd)");
//Get the input
dateRec = scan.nextLine();
if(dateRec.length() == 10 &&
dateRec.charAt(4) == '-' &&
dateRec.charAt(7) == '-')
{
//Format is correct, continue on!
getDate = false;
}
else
{
//they entered the wrong format
System.out.println("Incorrect Format! Please enter the correct format for the date");
System.out.println();
}
}
//Length Of the Albulm
boolean isInt = false;
while(!isInt)
{
System.out.println("Please enter the length of the album (in minutes)");
aLength = scan.nextLine();
//Try to get the value
//may throw exception if not a number
int inputLength;
try
{
inputLength = Integer.valueOf(aLength);
}
catch(Exception e)
{
inputLength = -1;
}
//Execute the function and error check
//Is it an integer
if(inputLength < 0)
{
System.out.println("That is not a valid number");
}
else {
//Continue on!
isInt = true;
}
}
//Number Of Songs
isInt = false;
while(!isInt)
{
System.out.println("Please enter the number of songs on the album");
numSongs = scan.nextLine();
//Try to get the value
//may throw exception if not a number
int inputLength;
try
{
inputLength = Integer.valueOf(numSongs);
}
catch(Exception e)
{
inputLength = -1;
}
//Execute the function and error check
//Is it an integer
if(inputLength < 0)
{
System.out.println("That is not a valid number");
}
else {
//Continue on!
isInt = true;
}
}
//Insert values into the database
System.out.println("Sending to database...");
//Create the sql
String sql = "INSERT INTO albulms VALUES "
+ "('" + title + "', "
+ "'" + groupName + "', "
+ "'" + studioName + "', "
+ "'" + dateRec + "', "
+ "'" + aLength + "', "
+ "'" + numSongs + "')";
//Execute the sql
stmt.execute(sql);
//Finished!
System.out.println("Success!");
}
else if(answer == 4)
{
//Insert a new studio
String sname = "";
String saddress = "";
String sowner = "";
String sphone = "";
//Get the studio title
System.out.println("Please enter a name for the studio");
sname = scan.nextLine();
//Get the studio title
System.out.println("Please enter an address for the studio");
saddress = scan.nextLine();
//Get the studio title
System.out.println("Please enter an owner for the studio");
sowner = scan.nextLine();
//Set the Date Recorded
boolean getPhone = true;
while(getPhone)
{
//Tell user to enter a valid date
System.out.println("Please enter a phone number for the studio (Format: 5555555555)");
//Get the input
sphone = scan.nextLine();
//Try to get the value
//may throw exception if not a number
long inputLength;
try
{
inputLength = Long.valueOf(sphone);
}
catch(Exception e)
{
inputLength = -1;
}
//Execute the function and error check
//Is it an integer
if(inputLength < 999999999
|| inputLength > 10000000000L)
{
System.out.println("That is not a valid phone number");
}
else {
//Continue on!
getPhone = false;
}
}
//Insert values into the database
System.out.println("Sending to database...");
//Create the sql
String sql = "INSERT INTO recstudio VALUES "
+ "('" + sname + "', "
+ "'" + saddress + "', "
+ "'" + sowner + "', "
+ "'" + sphone + "')";
//Execute the sql
stmt.execute(sql);
//Finished!
System.out.println("Success!");
//Now ask if they would like to switch albulms published by another studio
//Please select an artist for the album
boolean getStudio = true;
String studioName;
while(getStudio)
{
System.out.println("Please enter a recording studio to update their albulms, and make them albulms of " + sname
+ ", from the options below. You may also enter 'n' if you would not like to do this.");
String sqlStudio = "SELECT sname FROM recstudio";
//Simply loop through the result set for the albulms
try (ResultSet rs = stmt.executeQuery(sqlStudio)) {
//Simply loop through the result set for the albulms
System.out.println("Recording Studios:");
while(rs.next()){
//Retrieve by column name
//Do not display the newly created studio
if(!sname.equalsIgnoreCase(rs.getString("sname")))
{
String studios = rs.getString("sname");
//Display values
System.out.println(studios);
}
}
//Close the result set
rs.close();
}
catch(Exception e) {
//Handle errors for Class.forName
e.printStackTrace();
//Inform user of error
System.out.println("Error! Could Not get result from the Database");
}
//Get the input
studioName = scan.nextLine();
//Check if it is a valid group
//Query the DB
String sqlCheck = "SELECT sname FROM recstudio WHERE LOWER(sname) LIKE LOWER('%" + studioName + "%')";
try (ResultSet rsCheck = stmt.executeQuery(sqlCheck)) {
if(rsCheck.next() && !sname.equalsIgnoreCase(studioName))
{
//Good it exists!
//Switch the albulms from the studio to this one!
String sqlSwitch= "UPDATE albulms SET sname = '" + sname + "' WHERE LOWER(sname) LIKE LOWER('%" + studioName + "%')";
stmt.executeUpdate(sqlSwitch);
//Close the result set and exit the loop
System.out.println("Albulms have been switched from " + studioName + " into the new studio " + sname +"!");
getStudio = false;
}
else if (studioName.equalsIgnoreCase("n"))
{
//They chose not to have the albums switched over
System.out.println("Switching albulms to this studio will not be performed!");
getStudio = false;
}
else
{
//Not a valid group
System.out.println("Studio does not exist, please select an available group");
System.out.println();
}
//Close the result set
rsCheck.close();
}
catch(Exception e) {
//Handle errors for Class.forName
e.printStackTrace();
//Inform user of error
System.out.println("Error! Could Not get result from the Database");
}
}
}
else if(answer == 5)
{
//Delete an albulm
System.out.println("Please enter the title of the album you would like to delete");
//Get the albulm title
input = scan.nextLine();
//Query the DB
String sql = "SELECT * FROM albulms WHERE LOWER(atitle) LIKE LOWER('%" + input + "%')";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next())
{
//Delete the albulm
//Create the sql
String sqlDel = "DELETE FROM albulms WHERE LOWER(atitle) LIKE LOWER('%" + input + "%')";
//Execute the sql
stmt.execute(sqlDel);
System.out.println("album deleted!");
System.out.println();
}
else
{
System.out.println("That album does not exist...");
}
rs.close();
}
else if(answer == 6)
{
//Quit
querying = false;
}
//Spacing
System.out.println();
}
//Finish the program
}
catch(Exception e) {
//Handle errors for Class.forName
e.printStackTrace();
//Inform user of error
System.out.println("Error! Scanner could not be Created!");
}
}catch(SQLException se){
//Handle errors for JDBC
se.printStackTrace();
//Inform user of error
System.out.println("Error connecting to the database!");
}catch(Exception e){
//Handle errors for Class.forName
e.printStackTrace();
//Inform user of error
System.out.println("Error!");
}finally{
//finally block used to close resources
try{
if(stmt!=null)
conn.close();
}catch(SQLException se){
}// do nothing
try{
if(conn!=null)
conn.close();
}catch(SQLException se){
se.printStackTrace();
//Inform user of error
System.out.println("Error closing connection to the database!");
}//end finally try
}//end try
System.out.println("Goodbye!");
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment