Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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) + "\t"
- + resultSet.getString(2) + "\t" + resultSet.getString(3) + "\t"
- + 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)
- 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) + "\t"
- + resultSet.getString(2) + "\t" + resultSet.getString(3) + "\t"
- + resultSet.getString(4));
- }
- catch(SQLException sqlExec){
- sqlExec.printStackTrace();
- }
- catch(Exception e){
- e.printStackTrace();
- }
- finally{
- resultSet.close();
- connection.close();
- sc.close();
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement