[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

IO and Embedded SQL

Posted on 2004-11-22
27
Medium Priority
?
272 Views
Last Modified: 2013-12-29
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" ); }
} }


0
Comment
Question by:agtmulder17
  • 14
  • 12
27 Comments
 
LVL 3

Expert Comment

by:vikraman_b
ID: 12651078
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...


0
 
LVL 92

Expert Comment

by:objects
ID: 12651089
something like:

Select * from GradeReport, Student where GradeReport.StudentNumber=Student.StudentNumber and
Name=?
0
 

Author Comment

by:agtmulder17
ID: 12651130
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 92

Expert Comment

by:objects
ID: 12651153
   InputStreamReader inBuff = new InputStreamReader(System.in);
    BufferedReader stdin = new BufferedReader ( inBuff );
    String name = stdin.readLine();

0
 

Author Comment

by:agtmulder17
ID: 12651223
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();

....





0
 
LVL 92

Expert Comment

by:objects
ID: 12651240
yes that looks about right, you'll just need to add quotes around the name.
0
 

Author Comment

by:agtmulder17
ID: 12651273
around name? how do you mean?
0
 
LVL 92

Expert Comment

by:objects
ID: 12651279
 stat.executeQuery("SELECT * FROM Grade_Report, Student WHERE                              GradeReport.StudentNumber=Student.StudentNumber AND Name ='" + stdin.readLine()+"'" );

0
 

Author Comment

by:agtmulder17
ID: 12651344
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?
0
 
LVL 92

Expert Comment

by:objects
ID: 12651371
you need to specify the column name that you want getString() to return the value of

 System.out.println( query.getString("GRADE") );
0
 

Author Comment

by:agtmulder17
ID: 12651415
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?
0
 
LVL 92

Expert Comment

by:objects
ID: 12651459
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.
0
 

Author Comment

by:agtmulder17
ID: 12651488
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.

0
 

Author Comment

by:agtmulder17
ID: 12651514
ok, fixed small query error.

Student WHERE GradeReport.StudentNumber=Student...

is now
Student WHERE Grade_Report.StudentNumber=Student...
0
 

Author Comment

by:agtmulder17
ID: 12651517
but still getting same error situation
0
 
LVL 92

Expert Comment

by:objects
ID: 12651572
>     catch (Exception e) { System.out.println( "error" ); }

change that line to print out the details of the error

    catch (Exception e) { ex.printStackTrace(); }
0
 
LVL 92

Expert Comment

by:objects
ID: 12651578
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?
0
 

Author Comment

by:agtmulder17
ID: 12651605
go compile error now,

 Undefined variable or class name: ex
catch (Exception e) { ex.printStackTrace(); }
                      ^
1 error
0
 
LVL 92

Expert Comment

by:objects
ID: 12651640
sorry, i habitually name exceptions 'ex'

catch (Exception e) { e.printStackTrace(); }
0
 

Author Comment

by:agtmulder17
ID: 12651661
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?
0
 
LVL 92

Expert Comment

by:objects
ID: 12651678
better post your current code
0
 

Author Comment

by:agtmulder17
ID: 12651681
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?
0
 

Author Comment

by:agtmulder17
ID: 12651688
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(); }
} }

0
 
LVL 92

Accepted Solution

by:
objects earned 2000 total points
ID: 12651704
you can't have the grade appear after the name you have type because you need to hit return to read the name.
What u could do is print out the name again after entered:

System.out.print("Grades for "+name+": ");
while( query.next() ){
  System.out.print( query.getString( "Grade" ) + " ");
}
System.out.println();
0
 

Author Comment

by:agtmulder17
ID: 12651734
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...'
0
 

Author Comment

by:agtmulder17
ID: 12651766
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
0
 
LVL 92

Expert Comment

by:objects
ID: 12651776
no worries :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
This video teaches viewers about errors in exception handling.
Suggested Courses
Course of the Month18 days, 8 hours left to enroll

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question