Link to home
Start Free TrialLog in
Avatar of agtmulder17
agtmulder17

asked on

IO and Embedded SQL

Hi, I am working on a database project building SQL statements with Java. I have successfully written the Java program to create tables and insert dummy data into them as well as run a small SQL query.

However, I am also trying to build into this (and thus need help) the ability for the user to input a name from the Student table and then output a grade, derived from the Grade_Report table I have created. I have tried a few different things but can't seem to get the input to work and produce the given output and therefore, I have gone back to the beginning of where I left off; the following program connects to a Sybase database, creates the requested tables and then outputs a query answer. Can you help. Thanks.  

import java.sql.*;
import com.sybase.jdbcx.SybDriver;
import java.io.*;
import java.util.*;

class SQLquery {
  public static void main (String args[]) {
    try {
      SybDriver sybdriver =   // Get Sybase Driver
      (SybDriver) Class
         . forName("com.sybase.jdbc2.jdbc.SybDriver")
         . newInstance();
sybdriver.setVersion(
     com.sybase.jdbcx.SybDriver.VERSION_5 );
DriverManager.registerDriver(sybdriver);

      Connection connection = null;   // Make Database Connection
String server = "database";
String port = "4101";
String database = "c670ab";
String username = args[0];
String password = args[1];
String url =   "jdbc:sybase:Tds:"
             + server + ":"
             + port + "/"
             + database;
  connection =
  DriverManager.getConnection(url, username, password);

// Begin Making Tables & Insertions
Statement stat = connection.createStatement();
stat.executeUpdate(
  "CREATE TABLE Student (Name CHAR(10),StudentNumber INT, Class INT, Major CHAR(10))" );
stat.executeUpdate(
  "INSERT INTO Student VALUES ( 'Smith',17,1, 'CS')" );
stat.executeUpdate(
  "INSERT INTO Student VALUES ( 'Brown',8,2, 'CS')" );

stat.executeUpdate(
  "CREATE TABLE Course (CourseName CHAR(30),CourseNumber VARCHAR(10),CreditHours INT, Department CHAR(5))" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Intro to Computer Science','CS1310',4,'CS')" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Data Structures','CS3320',4,'CS')" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Discrete Mathematics','MATH2410',3,'MATH')" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Database','CS3380',3,'CS')" );

stat.executeUpdate(
  "CREATE TABLE Section (SectionIdentifier VARCHAR(4),CourseNumber VARCHAR(10), Semester CHAR(7), Year VARCHAR(2), Instructor CHAR(20))" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '85','MATH2410','Fall','98','King' )" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '92','CS1310','Fall','98','Anderson')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '102','CS3320','Spring','99','Knuth')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '112','MATH2410','Fall','99','Chang')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '119','CS1310','Fall','99','Anderson')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '135','CS3380','Fall','99','Stone')" );

stat.executeUpdate(
  "CREATE TABLE Grade_Report (StudentNumber VARCHAR(4),SectionIdentifier VARCHAR(4),Grade CHAR(1))" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( '17','112','B')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( '17','119','C')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( '8','85','A')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( '8','92','A')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( '8','102','B')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( '8','135','A')" );

stat.executeUpdate(
  "CREATE TABLE Prerequisite (CourseNumber VARCHAR(10),PrerequisiteNumber VARCHAR(10))" );
stat.executeUpdate(
  "INSERT INTO Prerequisite VALUES ( 'CS3380','CS3320')" );
stat.executeUpdate(
  "INSERT INTO Prerequisite VALUES ( 'CS3380','MATH2410')" );
stat.executeUpdate(
  "INSERT INTO Prerequisite VALUES ( 'CS3320','CS1310')" );

// Run Query
ResultSet query =
  stat.executeQuery("SELECT * FROM Grade_Report");
while( query.next() ){
  System.out.println( query.getString( "Grade" ) );
}
query.close();

//  Drop Tables
stat.executeUpdate( "DROP TABLE Student" );
stat.executeUpdate( "DROP TABLE Course" );
stat.executeUpdate( "DROP TABLE Section" );
stat.executeUpdate( "DROP TABLE Grade_Report" );
stat.executeUpdate( "DROP TABLE Prerequisite" );
stat.close();

      connection.close();
    }
    catch (Exception e) { System.out.println( "error" ); }
} }


Avatar of vikraman_b
vikraman_b
the best way to do this is..
I am just showing the u need to do to make things easier
First always write a DAO(simple java class say singleton class) class in which it has methods for sellect,insert,delete and update.
e.g
public class xxxDAO{
      static xxxDAO   xxxDAOObj = null;
      Logger logger = Logger.getLogger(this.getClass().getName());

      public static xxxDAO   getInstance() {
            if (xxxDAOObj == null) {
                  xxxDAOObj = new xxxDAO   ();
            }

            return xxxDAOObj ;
      }

public void insertStudentTable(){
}
public String getStudentName(){
select name from the student table;
}
public String getStudentGrade(String studentName){
select grade from the student table where name = studentName;
}


...............................
.......etc.,
}

Then write a BO class(Simple java class say singleton class)
Here only u need to decide what r the methods to be called ..even u can handle ur transaction also.
 public String someXXXMethod(){
String grade ="";
String studentName = xxxDAO.getInstance().getStudentName();
grade = xxxDAO.getInstance().getStudentGrade(studentName );

return grade;


}

A simple flow..
do like this way..u wont get any struck...


Avatar of Mick Barry
Mick Barry
Flag of Australia image
something like:

Select * from GradeReport, Student where GradeReport.StudentNumber=Student.StudentNumber and
Name=?
Avatar of agtmulder17
agtmulder17

ASKER

objects,

I see the logic in the query you have given, however how do I implement this so that I have an input  with the given output? I have tried using

     InputStreamReader inBuff = new InputStreamReader(System.in);
     BufferedReader stdin = new BufferedReader ( inBuff );

with System.out.println(stdin);  giving me the result of the query. But I haven't for the life of me been able to successful insert these into my code in the right places to handle this. Is it more simple then I am making it? I think my main confusion is what exactly to put where to produce my output?
Avatar of Mick Barry
Mick Barry
Flag of Australia image
   InputStreamReader inBuff = new InputStreamReader(System.in);
    BufferedReader stdin = new BufferedReader ( inBuff );
    String name = stdin.readLine();

Avatar of agtmulder17
agtmulder17

ASKER

objects,

thanks for helping, so are you saying for my program, which I haven't got to work quite yet,

...
class SQLquery {
  public static void main (String args[]) {
 
    InputStreamReader inBuff = new InputStreamReader(System.in);
    BufferedReader stdin = new BufferedReader ( inBuff );
    String name = stdin.readLine();
 
    try {
...

// Run Query
ResultSet query =
  stat.executeQuery("SELECT * FROM Grade_Report, Student WHERE
                             GradeReport.StudentNumber=Student.StudentNumber AND Name =" + stdin.readLine() );
while( query.next() ){
  System.out.println( query.getString( ) );
}
query.close();

....





Avatar of Mick Barry
Mick Barry
Flag of Australia image
yes that looks about right, you'll just need to add quotes around the name.
Avatar of agtmulder17
agtmulder17

ASKER

around name? how do you mean?
Avatar of Mick Barry
Mick Barry
Flag of Australia image
 stat.executeQuery("SELECT * FROM Grade_Report, Student WHERE                              GradeReport.StudentNumber=Student.StudentNumber AND Name ='" + stdin.readLine()+"'" );

Avatar of agtmulder17
agtmulder17

ASKER

I keep getting a complie error

99: No method matching getString() found in interface java.sql.ResultSet.
  System.out.println( query.getString() );
                                     ^
1 error


Here is my code for the query (the tables are being created),

// Run Query
ResultSet query =
  stat.executeQuery("SELECT * FROM Grade_Report, Student WHERE GradeReport.StudentNumber=Student.Number AND Name ='" + stdin.readLine()+"'" );
while( query.next() ){
  System.out.println( query.getString() );
}
query.close();

what do you think?
Avatar of Mick Barry
Mick Barry
Flag of Australia image
you need to specify the column name that you want getString() to return the value of

 System.out.println( query.getString("GRADE") );
Avatar of agtmulder17
agtmulder17

ASKER

Yeah, somehow I took it out, but it compiles now with no problem. I am having runtime errors now but not sure how to debug it. I seem to have been here before; here is what I have:

class SQLquery {
  public static void main (String args[])throws IOException  {

    InputStreamReader inBuff = new InputStreamReader(System.in);
    BufferedReader stdin = new BufferedReader ( inBuff );
    System.out.print ("Insert Student Name: ");
    String name = stdin.readLine();

    try {
...
 
// Run Query
ResultSet query =
  stat.executeQuery("SELECT * FROM Grade_Report, Student WHERE GradeReport.StudentNumber=Student.Number AND Name ='" + stdin.readLine()+"'" );
while( query.next() ){
  System.out.println( query.getString( "Grade" ) );
}
query.close();

I type in a name from the Student table (either Smith or Brown) and hit return, which just drops me down a line. Typing anything else or hitting return simply gives me the error  exeception at the end of my program. It seems as though my query is hanging up since the tables are being created, but not droped from the program. And suggestion of what is going on? Is there a problem with my while loop?
Avatar of Mick Barry
Mick Barry
Flag of Australia image
whats the error?

the fact that the last thing you do is drop the table could be a problem as if any error occurs the tables will not get dropped. And thus next time you run it it fails.
So if its done last then it should be in a finally block.
Avatar of agtmulder17
agtmulder17

ASKER

I am going into Sybase each run and deleting the tables that failed to drop, to avoid a failure on the next run.

Here is the error I get. After typing 'Smith' at the 'Insert Student Name:' prompt, I hit return and the cursor drops down a line. Then hitting return before or after typing something, I get the word 'error' returned.

Avatar of agtmulder17
agtmulder17

ASKER

ok, fixed small query error.

Student WHERE GradeReport.StudentNumber=Student...

is now
Student WHERE Grade_Report.StudentNumber=Student...
Avatar of agtmulder17
agtmulder17

ASKER

but still getting same error situation
Avatar of Mick Barry
Mick Barry
Flag of Australia image
>     catch (Exception e) { System.out.println( "error" ); }

change that line to print out the details of the error

    catch (Exception e) { ex.printStackTrace(); }
Avatar of Mick Barry
Mick Barry
Flag of Australia image
StudentNumber is an INT in your STUDENT table but it is a VARCHAR in the others.
Thats going to cause an error. Any reason why its not a INT in all tables?
Avatar of agtmulder17
agtmulder17

ASKER

go compile error now,

 Undefined variable or class name: ex
catch (Exception e) { ex.printStackTrace(); }
                      ^
1 error
Avatar of Mick Barry
Mick Barry
Flag of Australia image
sorry, i habitually name exceptions 'ex'

catch (Exception e) { e.printStackTrace(); }
Avatar of agtmulder17
agtmulder17

ASKER

Changed back to orginal catch exeception line and Student to INT.

Now it compiles and executes, however here is what happens, which seems like a looping problem?
At the prompt, I type Smith, hit return, and it drops me to the next line. Then typing either Smith or Brown and return, gives the grades for Smith or Brown but only gives one grade on each line. I am trying to get to the point where I can get all the grades averaged for either Smith or Brown.

Do you know why I have to type in the second name and it isn't just reading a name and then returning my desired info?
Avatar of Mick Barry
Mick Barry
Flag of Australia image
better post your current code
Avatar of agtmulder17
agtmulder17

ASKER

ok, did somemore testing. It appears that what ever is entered on the second line returns all of the grades line by line for each student. Not sure how to make the return of grades occur right from the name at the prompt and hit return?
Avatar of agtmulder17
agtmulder17

ASKER

import java.sql.*;
import com.sybase.jdbcx.SybDriver;
import java.io.*;
import java.util.*;

class SQLquery {
  public static void main (String args[])throws IOException  {

    InputStreamReader inBuff = new InputStreamReader(System.in);
    BufferedReader stdin = new BufferedReader ( inBuff );
    System.out.print ("Insert Student Name: ");
    String name = stdin.readLine();


    try {
      SybDriver sybdriver =   // Get Sybase Driver
      (SybDriver) Class
         . forName("com.sybase.jdbc2.jdbc.SybDriver")
         . newInstance();
sybdriver.setVersion(
     com.sybase.jdbcx.SybDriver.VERSION_5 );
DriverManager.registerDriver(sybdriver);

      Connection connection = null;   // Make Database Connection
String server = "database";
String port = "4101";
String database = "c670ab";
String username = args[0];
String password = args[1];
String url =   "jdbc:sybase:Tds:"
             + server + ":"
             + port + "/"
             + database;
  connection =
  DriverManager.getConnection(url, username, password);

// Begin Making Tables & Insertions
Statement stat = connection.createStatement();
stat.executeUpdate(
  "CREATE TABLE Student (Name CHAR(10),StudentNumber INT, Class INT, Major CHAR(10))" );
stat.executeUpdate(
  "INSERT INTO Student VALUES ( 'Smith',17,1, 'CS')" );
stat.executeUpdate(
  "INSERT INTO Student VALUES ( 'Brown',8,2, 'CS')" );

stat.executeUpdate(
  "CREATE TABLE Course (CourseName CHAR(30),CourseNumber VARCHAR(10),CreditHours INT, Department CHAR(5))" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Intro to Computer Science','CS1310',4,'CS')" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Data Structures','CS3320',4,'CS')" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Discrete Mathematics','MATH2410',3,'MATH')" );
stat.executeUpdate(
  "INSERT INTO Course VALUES ( 'Database','CS3380',3,'CS')" );

stat.executeUpdate(
  "CREATE TABLE Section (SectionIdentifier VARCHAR(4),CourseNumber VARCHAR(10), Semester CHAR(7), Year VARCHAR(2), Instructor CHAR(20))" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '85','MATH2410','Fall','98','King' )" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '92','CS1310','Fall','98','Anderson')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '102','CS3320','Spring','99','Knuth')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '112','MATH2410','Fall','99','Chang')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '119','CS1310','Fall','99','Anderson')" );
stat.executeUpdate(
  "INSERT INTO Section VALUES ( '135','CS3380','Fall','99','Stone')" );

stat.executeUpdate(
  "CREATE TABLE Grade_Report (StudentNumber INT,SectionIdentifier VARCHAR(4),Grade CHAR(1))" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( 17,'112','B')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( 17,'119','C')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( 8,'85','A')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( 8,'92','A')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( 8,'102','B')" );
stat.executeUpdate(
  "INSERT INTO Grade_Report VALUES ( 8,'135','A')" );

stat.executeUpdate(
  "CREATE TABLE Prerequisite (CourseNumber VARCHAR(10),PrerequisiteNumber VARCHAR(10))" );
stat.executeUpdate(
  "INSERT INTO Prerequisite VALUES ( 'CS3380','CS3320')" );
stat.executeUpdate(
  "INSERT INTO Prerequisite VALUES ( 'CS3380','MATH2410')" );
stat.executeUpdate(
  "INSERT INTO Prerequisite VALUES ( 'CS3320','CS1310')" );

// Run Query
ResultSet query =
  stat.executeQuery("SELECT * FROM Grade_Report, Student WHERE Grade_Report.StudentNumber=Student.StudentNumber AND Student.Name ='" + stdin.r
eadLine()+"'" );
while( query.next() ){
  System.out.println( query.getString( "Grade" ) );
}
query.close();

//  Drop Tables
stat.executeUpdate( "DROP TABLE Student" );
stat.executeUpdate( "DROP TABLE Course" );
stat.executeUpdate( "DROP TABLE Section" );
stat.executeUpdate( "DROP TABLE Grade_Report" );
stat.executeUpdate( "DROP TABLE Prerequisite" );
stat.close();

      connection.close();
    }
catch (Exception e) { e.printStackTrace(); }
} }

ASKER CERTIFIED SOLUTION
Avatar of Mick Barry
Mick Barry
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of agtmulder17
agtmulder17

ASKER

ok, I see what you mean, that makes sense. My output however seems to give me:

Grades for : BC

or

Grades for : AABA

It looks like it is missing the 'name' of the person after 'Grades for...'
Avatar of agtmulder17
agtmulder17

ASKER

Shoot, I can just take out the 'for' and it should be smooth. Thanks for your help, you have been extremely helpful! As far as I am concerned you have earned all the points. I learned a lot too. Thanks objects!

agtmulder17
Avatar of Mick Barry
Mick Barry
Flag of Australia image
no worries :)