The following is my code: import java.sql.*; public class homework4 { public sta

Question

The following is my code:

import java.sql.*;

public class homework4 {

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

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

System.out.println("Driver loaded");

Connection connection = DriverManager.getConnection

("jdbc:sqlserver://s16988308.onlinehome-server.com:1433;databaseName=CUNY_DB;"

+ "integratedSecurity=false;", "cst3613", "password1");

System.out.println("Database connected");

Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery

("SELECT firstName, lastName, e.courseid, c.title "

+ "FROM students s, course c, enrollment e "

+ "WHERE s.ssn = e.ssn AND e.courseid = c.courseid "

+ "AND s.ssn " + " = '345678321'");

while (resultSet.next())

System.out.println(resultSet.getString(1) + " "

+ resultSet.getString(2) + " " + resultSet.getString(3) + " "

+ resultSet.getString(4));

resultSet.close();

connection.close();

}

}

Now nedd to modify using following requirement. I don't know how to do it on microsoft server management studio. So, you have to do it on sql server management studio.

Modify your homework 4 to enroll new course for you using [dbo].[insertStudent] store procedure from database.

You must ask user to enter SSN and course ID.

PROCEDURE [dbo].[insertStudent]

@SSN nvarchar(9),

@CourseID nvarchar(5)

Explanation / Answer

package com.chegg.question6;

import java.sql.*;

import java.util.Scanner;

public class homework4 {

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

ResultSet resultSet = null;

PreparedStatement preparedStmt=null;

Connection connection = null;

Statement statement = null;

Scanner sc =null;

try{

sc=new Scanner(System.in);

System.out.println("Please enter the SSN for the student you want to enroll");

Long ssn=sc.nextLong();

System.out.println("Please enter the course id which the student wants to enroll in");

String courseId =sc.next();

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

System.out.println("Driver loaded");

connection = DriverManager.getConnection

("jdbc:sqlserver://s16988308.onlinehome-server.com:1433;databaseName=CUNY_DB;"

+ "integratedSecurity=false;", "cst3613", "password1");

System.out.println("Database connected");

String storedProcSql = "EXEC insertStudent ?,?"; // for stored proc taking 2 parameters ssn and course id

preparedStmt = connection.prepareStatement(storedProcSql);

preparedStmt.setLong(1, ssn);

preparedStmt.setString(2,courseId);

int rows = preparedStmt.executeUpdate();

System.out.println("No. of rows inserted : " + rows );

// After successful insertion , fetch the row inserted in the following lines

statement = connection.createStatement();

resultSet = statement.executeQuery

("SELECT firstName, lastName, e.courseid, c.title "

+ "FROM students s, course c, enrollment e "

+ "WHERE s.ssn = e.ssn AND e.courseid = c.courseid "

+ "AND s.ssn " + " = "+ ssn); // SSN is of the type LONG here

while (resultSet.next())

System.out.println(resultSet.getString(1) + " "

+ resultSet.getString(2) + " " + resultSet.getString(3) + " "

+ resultSet.getString(4));

}

catch(SQLException sqlExec){

sqlExec.printStackTrace();

}

catch(Exception e){

e.printStackTrace();

}

finally{

resultSet.close();

connection.close();

sc.close();

}

}

}

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.