Question

JAVA

We are learning about java databases in my highschool java class.

I Need some help with it.

Create a Java program (feel free to do it all in a main method) that works with a database that contains your 'Contractors' table (see the last exercise).

• Id (integer, primary key)
• CompanyName (varchar, 30 characters)
• Phone(varchar, 12 characters)
• ContactName(varchar, 30 characters)
• Rating (integer)
• OutOfStateService (boolean)

	#	company name	phone #	Name	rating	Out of state service
	1	Joe's Brewery	1111111111	Joe	100	true
	2	Mary's Brewery	2222222222	Mary	100	false
	3	Bob's Brewery	3333333333	Bob	99	true
	4	Tim's Brewery	4444444444	Tim	95	true
	5	Kim's Bakery	5555555555	Kim	2	true

Your program should have a menu (and associated code) that allows for the following features:

Can display all of the data from the table.

Can read a user-provided field from the table (example, the user asks for names and gets a list of all the names).

Can delete a record. Can add a record.

Answer 1

Code

import java.sql.*;

import java.util.Scanner;
public class ContractorsDatabase {
public static Connection con;
public static Statement st;
private static ResultSet rs;
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql?zeroDateTimeBehavior=convertToNull","root","");
st=con.createStatement();
String companyName,cName,phone,query;
  
int id,choice,choiceColumn,rating,service;
Scanner input=new Scanner(System.in);
while(true)
{
menu();
System.out.print(" Your choice: ");
choice=input.nextInt();
if(choice==1)
{
query="select * from Contractor";
rs=st.executeQuery(query);
System.out.printf("%s%20s%20s%20s%20s%20s ","Id","Company Name","Phone","Contact Name","Rating","Out of state service");
while(rs.next())
{
id=rs.getInt("id");
companyName=rs.getString("CompanyName");
phone=rs.getString("Phone");
cName=rs.getString("ContactName");
rating=rs.getInt("Rating");
service=rs.getInt("OutOfStateService");
String sc;
if(service==1)
sc="true";
else
sc="false";   
System.out.printf("%d%20s%20s%20s%20d%20s ",id,companyName,phone,cName,rating,sc);
}
System.out.println();
}
else if(choice==2)
{
columnMenu();
System.out.print(" Your choice: ");
choiceColumn=input.nextInt();
if(choiceColumn==1)
{
query="select CompanyName from Contractor";
rs=st.executeQuery(query);
System.out.println("Company Names: ");
while(rs.next())
{
companyName=rs.getString("CompanyName");
System.out.println(companyName);
}
}
else if(choiceColumn==2)
{
query="select ContactName from Contractor";
rs=st.executeQuery(query);
System.out.println("Contact Names: ");
while(rs.next())
{
companyName=rs.getString("ContactName");
System.out.println(companyName);
}
}
else if(choiceColumn==3)
{
query="select Phone from Contractor";
rs=st.executeQuery(query);
System.out.println("Phone numbers: ");
while(rs.next())
{
companyName=rs.getString("Phone");
System.out.println(companyName);
}
}
else
{
System.out.println("Invalid choice!!!");
}
System.out.println();
}
else if(choice==3)
{
System.out.println("Enter ID: ");
id=input.nextInt();
System.out.println("Enter name of the company: ");
companyName=input.next();
System.out.println("Enter name Puone number: ");
phone=input.next();
System.out.println("Enter contacnt name: ");
cName=input.next();
System.out.println("Enter the rating: ");
rating=input.nextInt();
System.out.println("Out of state service? (1 for Yes 0 for No)");
service=input.nextInt();
query="insert into Contractor values('"+id+"','"+companyName+"','"+phone+"','"+cName+"','"+rating+"','"+service+"');";
st.executeUpdate(query);
System.out.println(" Record inserted successfully!! ");
}
else if(choice==4)
{
System.out.println("Enter ID of the record you want to delete: ");
id=input.nextInt();
query="DELETE FROM Contractor WHERE id='"+id+"'";
st.executeUpdate(query);
System.out.println(" Record deleted successfully!! ");
}
else if(choice==5)
{
break;
}
else
System.out.println("Invalid choice!!! ");
}
  
}
public static void menu()
{
System.out.println("1. Display All the data 2. Display Perticular Column 3. Add New Record 4. Delete Record 5. Exit");
}
public static void columnMenu()
{
System.out.println("1. Company Name 2. Contact Name 3 Phone Number ");
}
}

database snaps

output

If you have any query regarding the code please ask me in the comment i am here for help you. Please do not direct thumbs down just ask if you have any query. And if you like my work then please appreciates with up vote. Thank You.