Getting Null Pointer Exception for simple jdbc code??

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • dev24
    New Member
    • Feb 2007
    • 8
    #1

    Getting Null Pointer Exception for simple jdbc code??

    Hi all,

    I am writing a simple method which when entered with an Int parameter performs an sql query, creates a result set, uses that resultset to get values from the object created by another method and display the result as an arraylist. The method and the object are as below:

    public static ArrayList getProjects(int userID)
    throws SQLException, Exception
    {
    ArrayList arrayList = null;

    getProjectsStmt .setInt(1, userID);
    ResultSet rs = getProjectsStmt .executeQuery() ;
    arrayList = new ArrayList();

    try
    {
    while (rs.next())
    {
    Projects p = createProject(r s);
    arrayList.add(p );
    rs.close();

    }
    }
    catch(Exception e)
    {
    System.err.prin tln(e);
    }

    return arrayList;

    }

    //Query is as follows
    private static final String GET_PROJECTS_ST MT = "SELECT projectID, name FROM project WHERE userID = ?";


    //createProject method that creates the Project object is as below

    private static Projects createProject(R esultSet rs)
    throws SQLException, Exception
    {

    int projectID = rs.getInt("proj ectID");
    int userID = rs.getInt("user ID");
    String name = rs.getString("n ame");
    String description = rs.getString("d escription");
    int createdate = rs.getInt("crea tedate");
    boolean visibility = rs.getBoolean(" visibility");

    return new Projects(projec tID, userID, name, description, createdate, visibility);
    }

    When i try and run this method in main to test it, i.e. getProject(1); the entry 1 exists in database and the results of the query also exists but I still get the error saying java.lang.NullP ointerException

    It points to the following lines:

    getProjectsStmt .setInt(1, userID); - line 6 in the getProjects method - above
    getProject(1) - in main where I try to test it

    Any ideas why???

    Thanks,

    dev
    Tags: None
    • r035198x
      MVP
      • Sep 2006
      • 13225
      #2
      Originally posted by dev24
      Hi all,

      I am writing a simple method which when entered with an Int parameter performs an sql query, creates a result set, uses that resultset to get values from the object created by another method and display the result as an arraylist. The method and the object are as below:

      public static ArrayList getProjects(int userID)
      throws SQLException, Exception
      {
      ArrayList arrayList = null;

      getProjectsStmt .setInt(1, userID);
      ResultSet rs = getProjectsStmt .executeQuery() ;
      arrayList = new ArrayList();

      try
      {
      while (rs.next())
      {
      Projects p = createProject(r s);
      arrayList.add(p );
      rs.close();

      }
      }
      catch(Exception e)
      {
      System.err.prin tln(e);
      }

      return arrayList;

      }

      //Query is as follows
      private static final String GET_PROJECTS_ST MT = "SELECT projectID, name FROM project WHERE userID = ?";


      //createProject method that creates the Project object is as below

      private static Projects createProject(R esultSet rs)
      throws SQLException, Exception
      {

      int projectID = rs.getInt("proj ectID");
      int userID = rs.getInt("user ID");
      String name = rs.getString("n ame");
      String description = rs.getString("d escription");
      int createdate = rs.getInt("crea tedate");
      boolean visibility = rs.getBoolean(" visibility");

      return new Projects(projec tID, userID, name, description, createdate, visibility);
      }

      When i try and run this method in main to test it, i.e. getProject(1); the entry 1 exists in database and the results of the query also exists but I still get the error saying java.lang.NullP ointerException

      It points to the following lines:

      getProjectsStmt .setInt(1, userID); - line 6 in the getProjects method - above
      getProject(1) - in main where I try to test it

      Any ideas why???

      Thanks,

      dev
      1.Use code tags when posting code
      2.Are you using GET_PROJECTS_ST MT or getProjectsStmt ?

        Comment

        • dev24
          New Member
          • Feb 2007
          • 8
          #3
          Originally posted by r035198x
          1.Use code tags when posting code
          2.Are you using GET_PROJECTS_ST MT or getProjectsStmt ?
          Thanks for reply.. Sorry I'll bear that in mind... Basically there is a method prepareQueries( ) where I initialise getProjectsStmt as follows:

          Code:
          getProjectsStmt = con.prepareStatement(GET_PROJECTS_STMT);
          If it helps the complete code is as follows:

          Code:
          import java.net.*;
import java.io.*;
import java.util.*;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import database.Users;
import database.Projects;
import database.Diagram;
import JavaSVN.ExtractText;
import java.sql.Statement;

public class QuerySourceData 
{
    
    private static Connection con;  
    private static Object connectionLock = new Object();
    
    private static PreparedStatement getProjectsStmt;
    private static PreparedStatement getNameStmt;
    private static PreparedStatement addUserStmt;
    private static PreparedStatement addProjectStmt;
    private static PreparedStatement addDiagramStmt;
    private static PreparedStatement getfilePathStmt;
    
    private static final String GET_NAME_STMT = "SELECT diagramID, name FROM diagram WHERE projectID = ?";
    private static final String GET_PROJECTS_STMT = "SELECT projectID, name FROM project WHERE userID = ?";
    private static final String GET_FILEPATH_STMT = "SELECT filePath FROM diagram WHERE diagramID = ?";
    private static final String ADD_USER_STMT = "INSERT INTO Users VALUES (?, ?, ?, ?, ?, ?, ?)";
    private static final String ADD_PROJECT_STMT = "INSERT INTO Project VALUES (?, ?, ?, ?, ?, ?)";
    private static final String ADD_DIAGRAM_STMT = "INSERT INTO Diagram VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

    public QuerySourceData() {}
        
    private static void prepareQueries()
        throws SQLException, Exception
    {

        getProjectsStmt = con.prepareStatement(GET_PROJECTS_STMT);            // gets all info about projects in which user is involved from userID
    
        getNameStmt = con.prepareStatement(GET_NAME_STMT);                  
        
    } 
    
    //enables connection to the database to perform the queries and updates
    public static void connect()
        throws SQLException, Exception
    {
        synchronized (connectionLock) 
        {
            if (con != null) 
            {
                throw new IllegalStateException("Already connected to database, close first, then re-connect");
	    }
	    
            try
            {
                Properties props = new Properties();
                FileInputStream in = new FileInputStream("Z:\\Database.Properties.txt");
                props.load(in);
                String driver = props.getProperty("jdbc.driver");
                Class.forName(driver);
                String url = props.getProperty("jdbc.url");
                String username = props.getProperty("jdbc.username");
                String password = props.getProperty("jdbc.password");       
                in.close();
            }
       
            catch(Exception e)
            {
                System.err.println("Exception: " + e);
            }
      
        }

    }
    
    // determines if it is still connected to the database - designed for expansion
    public static boolean isConnected() 
    {
        synchronized (connectionLock) 
        {
            return con != null;
        }
    }
    
    // closes connection to the database - designed for expansion
    public static void close() 
        throws SQLException, Exception
    {
        synchronized (connectionLock) 
        {
            con.close();
            con = null;
        }        
    }
     
    public static void addUser(Users u) 
        throws SQLException
    {
        
        synchronized (connectionLock) 
        {
            try 
            {
                addUserStmt.setInt(1, u.getuserID());
                addUserStmt.setString(2, u.getTitle());
                addUserStmt.setString(3, u.getfirstName());
                addUserStmt.setString(4, u.getlastName());
                addUserStmt.setString(5, u.getEmail());
                addUserStmt.setBoolean(6, u.getAdmin());
		addUserStmt.setBoolean(7, u.getcreateProject());
		        
		addUserStmt.executeUpdate();	
            }
	        
            catch (SQLException e) 
            {
                throw e;
            }
        }
    }
    
    public static void addProject(Projects p) 
        throws SQLException
    {
        
        synchronized (connectionLock) 
        {
            try 
            {
                addProjectStmt.setInt(1, p.getprojectID());
                addProjectStmt.setInt(2, p.getuserID());
                addProjectStmt.setString(3, p.getprojectName());
                addProjectStmt.setString(4, p.getDescription());
                addProjectStmt.setInt(5, p.getcreateDate());
                addProjectStmt.setBoolean(6, p.getVisibility());
		        
		addProjectStmt.executeUpdate();	
            }
	        
            catch (SQLException e) 
            {
                throw e;
            }
        }
    }
    
    public static void addDiagram(Diagram d) 
        throws SQLException
    {
        
        synchronized (connectionLock) 
        {
            try 
            {
                addDiagramStmt.setInt(1, d.getdiagramID());
                addDiagramStmt.setInt(2, d.getuserID());
                addDiagramStmt.setInt(3, d.getprojectID());
                addDiagramStmt.setString(4, d.getdiagramName());
                addDiagramStmt.setString(5, d.getDescription());
                addDiagramStmt.setString(6, d.getSummary());
		addDiagramStmt.setString(7, d.getfilePath());
                addDiagramStmt.setString(8, d.getdiagramUrl());
                addDiagramStmt.setInt(9, d.getcreateDate());
		        
		addDiagramStmt.executeUpdate();	
            }
	        
            catch (SQLException e) 
            {
                throw e;
            }
        }
    }
    
    // gets names and IDs of projects in which user is involved from userID
    public static ArrayList getProjects(int userID) 
    	throws SQLException, Exception 
    {
        ArrayList arrayList = null;
        getProjectsStmt = con.prepareStatement(GET_PROJECTS_STMT);
        getProjectsStmt.setInt(1, userID);
        ResultSet rs = getProjectsStmt.executeQuery();
        arrayList = new ArrayList();
          
        try
        {
            while (rs.next()) 
            {
                Projects p = createProject(rs);
                arrayList.add(p);
                rs.close();
                
            }
        }
        catch(Exception e) 
        {
            System.err.println(e + ": UserID entered has not been found.");
        }
        
        return arrayList;
        
    }
    

    private static Projects createProject(ResultSet rs) 
        throws SQLException, Exception 
    {
        
        int projectID = rs.getInt("projectID");
        int userID = rs.getInt("userID");
        String name = rs.getString("name");
        String description = rs.getString("description");
        int createdate = rs.getInt("createdate");
        boolean visibility = rs.getBoolean("visibility");
        
        return new Projects(projectID, userID, name, description, createdate, visibility);
    }
    
    private static Diagram createDiagram(ResultSet rs) 
        throws SQLException, Exception 
    {
        
        int diagramID = rs.getInt("diagramID");
        int userID = rs.getInt("userID");
        int projectID = rs.getInt("projectID");
        String name = rs.getString("name");
        String description = rs.getString("description");
        String summary = rs.getString("summary");
        String filePath = rs.getString("filePath");
        String diagramUrl = rs.getString("diagramUrl");
        int createdate = rs.getInt("createdate");
        
        return new Diagram(diagramID, userID, projectID, name, description, summary, filePath, diagramUrl, createdate);
    }
    
    private static Users createUsers(ResultSet rs) 
        throws SQLException, Exception 
    {
        
        int userID = rs.getInt("userID");
        String title = rs.getString("title");
        String firstname = rs.getString("firstname");
        String lastname = rs.getString("lastname");
        String email = rs.getString("email");
        boolean admin = rs.getBoolean("admin");
        boolean createProject = rs.getBoolean("createProject");
        
        return new Users(userID, title, firstname, lastname, email, admin, createProject);
    }  
     
    public static void main(String[] args)    
        throws SQLException, Exception
    { 
        
        connect();    
        prepareQueries();
        getProjects(1);
                   
    }
}
          Thanks!!

          Dev

            Comment

            • r035198x
              MVP
              • Sep 2006
              • 13225
              #4
              Originally posted by dev24
              Thanks for reply.. Sorry I'll bear that in mind... Basically there is a method prepareQueries( ) where I initialise getProjectsStmt as follows:

              Code:
               
getProjectsStmt = con.prepareStatement(GET_PROJECTS_STMT);
              If it helps the complete code is as follows:

              Code:
               
import java.net.*;
import java.io.*;
import java.util.*;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import database.Users;
import database.Projects;
import database.Diagram;
import JavaSVN.ExtractText;
import java.sql.Statement;
 
public class QuerySourceData 
{
 
private static Connection con; 
private static Object connectionLock = new Object();
 
private static PreparedStatement getProjectsStmt;
private static PreparedStatement getNameStmt;
private static PreparedStatement addUserStmt;
private static PreparedStatement addProjectStmt;
private static PreparedStatement addDiagramStmt;
private static PreparedStatement getfilePathStmt;
 
private static final String GET_NAME_STMT = "SELECT diagramID, name FROM diagram WHERE projectID = ?";
private static final String GET_PROJECTS_STMT = "SELECT projectID, name FROM project WHERE userID = ?";
private static final String GET_FILEPATH_STMT = "SELECT filePath FROM diagram WHERE diagramID = ?";
private static final String ADD_USER_STMT = "INSERT INTO Users VALUES (?, ?, ?, ?, ?, ?, ?)";
private static final String ADD_PROJECT_STMT = "INSERT INTO Project VALUES (?, ?, ?, ?, ?, ?)";
private static final String ADD_DIAGRAM_STMT = "INSERT INTO Diagram VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 
public QuerySourceData() {}
 
private static void prepareQueries()
throws SQLException, Exception
{
 
getProjectsStmt = con.prepareStatement(GET_PROJECTS_STMT); // gets all info about projects in which user is involved from userID
 
getNameStmt = con.prepareStatement(GET_NAME_STMT); 
 
} 
 
//enables connection to the database to perform the queries and updates
public static void connect()
throws SQLException, Exception
{
synchronized (connectionLock) 
{
if (con != null) 
{
throw new IllegalStateException("Already connected to database, close first, then re-connect");
	 }
 
try
{
Properties props = new Properties();
FileInputStream in = new FileInputStream("Z:\\Database.Properties.txt");
props.load(in);
String driver = props.getProperty("jdbc.driver");
Class.forName(driver);
String url = props.getProperty("jdbc.url");
String username = props.getProperty("jdbc.username");
String password = props.getProperty("jdbc.password"); 
in.close();
}
 
catch(Exception e)
{
System.err.println("Exception: " + e);
}
 
}
 
}
 
// determines if it is still connected to the database - designed for expansion
public static boolean isConnected() 
{
synchronized (connectionLock) 
{
return con != null;
}
}
 
// closes connection to the database - designed for expansion
public static void close() 
throws SQLException, Exception
{
synchronized (connectionLock) 
{
con.close();
con = null;
} 
}
 
public static void addUser(Users u) 
throws SQLException
{
 
synchronized (connectionLock) 
{
try 
{
addUserStmt.setInt(1, u.getuserID());
addUserStmt.setString(2, u.getTitle());
addUserStmt.setString(3, u.getfirstName());
addUserStmt.setString(4, u.getlastName());
addUserStmt.setString(5, u.getEmail());
addUserStmt.setBoolean(6, u.getAdmin());
		addUserStmt.setBoolean(7, u.getcreateProject());
 
		addUserStmt.executeUpdate();	
}
 
catch (SQLException e) 
{
throw e;
}
}
}
 
public static void addProject(Projects p) 
throws SQLException
{
 
synchronized (connectionLock) 
{
try 
{
addProjectStmt.setInt(1, p.getprojectID());
addProjectStmt.setInt(2, p.getuserID());
addProjectStmt.setString(3, p.getprojectName());
addProjectStmt.setString(4, p.getDescription());
addProjectStmt.setInt(5, p.getcreateDate());
addProjectStmt.setBoolean(6, p.getVisibility());
 
		addProjectStmt.executeUpdate();	
}
 
catch (SQLException e) 
{
throw e;
}
}
}
 
public static void addDiagram(Diagram d) 
throws SQLException
{
 
synchronized (connectionLock) 
{
try 
{
addDiagramStmt.setInt(1, d.getdiagramID());
addDiagramStmt.setInt(2, d.getuserID());
addDiagramStmt.setInt(3, d.getprojectID());
addDiagramStmt.setString(4, d.getdiagramName());
addDiagramStmt.setString(5, d.getDescription());
addDiagramStmt.setString(6, d.getSummary());
		addDiagramStmt.setString(7, d.getfilePath());
addDiagramStmt.setString(8, d.getdiagramUrl());
addDiagramStmt.setInt(9, d.getcreateDate());
 
		addDiagramStmt.executeUpdate();	
}
 
catch (SQLException e) 
{
throw e;
}
}
}
 
// gets names and IDs of projects in which user is involved from userID
public static ArrayList getProjects(int userID) 
	throws SQLException, Exception 
{
ArrayList arrayList = null;
getProjectsStmt = con.prepareStatement(GET_PROJECTS_STMT);
getProjectsStmt.setInt(1, userID);
ResultSet rs = getProjectsStmt.executeQuery();
arrayList = new ArrayList();
 
try
{
while (rs.next()) 
{
Projects p = createProject(rs);
arrayList.add(p);
rs.close();
 
}
}
catch(Exception e) 
{
System.err.println(e + ": UserID entered has not been found.");
}
 
return arrayList;
 
}
 
 
private static Projects createProject(ResultSet rs) 
throws SQLException, Exception 
{
 
int projectID = rs.getInt("projectID");
int userID = rs.getInt("userID");
String name = rs.getString("name");
String description = rs.getString("description");
int createdate = rs.getInt("createdate");
boolean visibility = rs.getBoolean("visibility");
 
return new Projects(projectID, userID, name, description, createdate, visibility);
}
 
private static Diagram createDiagram(ResultSet rs) 
throws SQLException, Exception 
{
 
int diagramID = rs.getInt("diagramID");
int userID = rs.getInt("userID");
int projectID = rs.getInt("projectID");
String name = rs.getString("name");
String description = rs.getString("description");
String summary = rs.getString("summary");
String filePath = rs.getString("filePath");
String diagramUrl = rs.getString("diagramUrl");
int createdate = rs.getInt("createdate");
 
return new Diagram(diagramID, userID, projectID, name, description, summary, filePath, diagramUrl, createdate);
}
 
private static Users createUsers(ResultSet rs) 
throws SQLException, Exception 
{
 
int userID = rs.getInt("userID");
String title = rs.getString("title");
String firstname = rs.getString("firstname");
String lastname = rs.getString("lastname");
String email = rs.getString("email");
boolean admin = rs.getBoolean("admin");
boolean createProject = rs.getBoolean("createProject");
 
return new Users(userID, title, firstname, lastname, email, admin, createProject);
} 
 
public static void main(String[] args) 
throws SQLException, Exception
{ 
 
connect(); 
prepareQueries();
getProjects(1);
 
}
}
              Thanks!!

              Dev
              I have to go but look through your code and see how you intialized con

              Code:
               private static Connection con;
              Your constructor does not initialize it either so it's probably null when you want to use it

                Comment

                • dev24
                  New Member
                  • Feb 2007
                  • 8
                  #5
                  Originally posted by r035198x
                  I have to go but look through your code and see how you intialized con

                  Code:
                   private static Connection con;
                  Your constructor does not initialize it either so it's probably null when you want to use it

                  Hi,

                  Thanks a lot for your help. I initialized connection object in a constructor which I called in main and now it is connecting to the database. Now the queries are playing up but I think I can handle it.

                  Thanks a lot for your help again.

                  Dev

                    Comment

                    • dev24
                      New Member
                      • Feb 2007
                      • 8
                      #6
                      Originally posted by dev24
                      Hi,

                      Now the queries are playing up but I think I can handle it.

                      Thanks a lot for your help again.

                      Dev
                      May be not...

                      I just cant figure out what am i doing wrong.. I have tried different things but keep getting exceptions... I even tried system.out.prin tln to try and print the resultset rs.getString but it bypasses everything and goes into error... I have rewritten the code which is as follows


                      Code:
                      package database;

import java.net.*;
import java.io.*;
import java.util.*;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import database.Users;
import database.Project;
import database.Diagram;
import JavaSVN.ExtractText;
import java.sql.Statement;


public class QuerySourceData 
{
    private ExtractText ET;
    

    private static Object connectionLock = new Object();
    
    private static PreparedStatement getProjectsStmt;
    private static PreparedStatement getNameStmt;
    private static PreparedStatement addUserStmt;
    private static PreparedStatement addProjectStmt;
    private static PreparedStatement addDiagramStmt;
    private static PreparedStatement getfilePathStmt;
    
    private static final String GET_NAME_STMT = "SELECT diagramID, name FROM diagram WHERE projectID = ?";
    private static final String GET_PROJECTS_STMT = "SELECT projectID, name FROM project WHERE userID = ?";
    private static final String GET_FILEPATH_STMT = "SELECT filePath FROM diagram WHERE diagramID = ?";
    
    private static final String ADD_USER_STMT = "INSERT INTO Users VALUES (?, ?, ?, ?, ?, ?, ?)";
    private static final String ADD_PROJECT_STMT = "INSERT INTO Project VALUES (?, ?, ?, ?, ?, ?)";
    private static final String ADD_DIAGRAM_STMT = "INSERT INTO Diagram VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    
    
    public QuerySourceData() 
        throws Exception
    {
        try
        {
            Properties props = new Properties();
            FileInputStream in = new FileInputStream("Z:\\Database.Properties.txt");
            props.load(in);
            String driver = props.getProperty("jdbc.driver");
            Class.forName(driver);
            String url = props.getProperty("jdbc.url");
            String username = props.getProperty("jdbc.username");
            String password = props.getProperty("jdbc.password");       
            in.close();
        }
       
        catch(Exception e)
        {
            System.err.println("Exception: " + e);
        }
        
        try
        {
            Connection conn = QuerySourceData.getConnection();
            
            getProjectsStmt = conn.prepareStatement(GET_PROJECTS_STMT);            // gets all info about projects in which user is involved from userID
            getNameStmt = conn.prepareStatement(GET_NAME_STMT);                  //gets names of diagrams from projectID
            getfilePathStmt = conn.prepareStatement(GET_FILEPATH_STMT);
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
    }
    
    
    
    public static void addUser(Users u) 
        throws SQLException
    {
        
        synchronized (connectionLock) 
        {
            try 
            {
                addUserStmt.setInt(1, u.getuserID());
                addUserStmt.setString(2, u.getTitle());
                addUserStmt.setString(3, u.getfirstName());
                addUserStmt.setString(4, u.getlastName());
                addUserStmt.setString(5, u.getEmail());
                addUserStmt.setBoolean(6, u.getAdmin());
		addUserStmt.setBoolean(7, u.getcreateProject());
		        
		addUserStmt.executeUpdate();	
            }
	        
            catch (SQLException e) 
            {
                throw e;
            }
        }
    }
    
    public static void addProject(Project p) 
        throws SQLException
    {
        
        synchronized (connectionLock) 
        {
            try 
            {
                addProjectStmt.setInt(1, p.getprojectID());
                addProjectStmt.setInt(2, p.getuserID());
                addProjectStmt.setString(3, p.getprojectName());
                addProjectStmt.setString(4, p.getDescription());
                addProjectStmt.setInt(5, p.getcreateDate());
                addProjectStmt.setBoolean(6, p.getVisibility());
		        
		addProjectStmt.executeUpdate();	
            }
	        
            catch (SQLException e) 
            {
                throw e;
            }
        }
    }
    
    public static void addDiagram(Diagram d) 
        throws SQLException
    {
        
        synchronized (connectionLock) 
        {
            try 
            {
                addDiagramStmt.setInt(1, d.getdiagramID());
                addDiagramStmt.setInt(2, d.getuserID());
                addDiagramStmt.setInt(3, d.getprojectID());
                addDiagramStmt.setString(4, d.getdiagramName());
                addDiagramStmt.setString(5, d.getDescription());
                addDiagramStmt.setString(6, d.getSummary());
		addDiagramStmt.setString(7, d.getfilePath());
                addDiagramStmt.setString(8, d.getdiagramUrl());
                addDiagramStmt.setInt(9, d.getcreateDate());
		        
		addDiagramStmt.executeUpdate();	
            }
	        
            catch (SQLException e) 
            {
                throw e;
            }
        }
    }
    
  
    
     public static String getDiagrams(int projectID) 
    	throws SQLException, Exception 
    {
        new QuerySourceData();
        Connection conn = QuerySourceData.getConnection();
        getNameStmt = conn.prepareStatement(GET_NAME_STMT);
        getNameStmt.setInt(1, projectID);
        ResultSet rs = getNameStmt.executeQuery();
        
        if(rs.first())
        {
            Diagram d = createDiagram(rs);              
            rs.close();
            conn.close();
            String a = d.toString(); 
            return a;
        }
        else 
        {
            throw new Exception("An error occured!");
        }
        
    }
    
    // gets names and IDs of projects in which user is involved from userID
    public static void getProjects(int userID) 
    	throws SQLException, Exception 
    {
        new QuerySourceData();
        Connection conn = QuerySourceData.getConnection();            
        getProjectsStmt = conn.prepareStatement(GET_PROJECTS_STMT); 
        getProjectsStmt.setInt(1, userID);
        ResultSet rs = getProjectsStmt.executeQuery();
          
        if(rs.first()) 
        {
            Project p = createProject(rs);
            rs.close();
            conn.close();
            String a = p.toString();
            System.out.println(a);
            //return a;
        }
         
         else
         {
            throw new Exception("An error occured");
         }    
    }
    
    private static Project createProject(ResultSet rs) 
        throws SQLException, Exception 
    {
        
        int projectID = rs.getInt("projectID");
        int userID = rs.getInt("userID");
        String name = rs.getString("name");
        String description = rs.getString("description");
        int createdate = rs.getInt("createdate");
        boolean visibility = rs.getBoolean("visibility");
        
        return new Project(projectID, userID, name, description, createdate, visibility);
    }
    
    private static Diagram createDiagram(ResultSet rs) 
        throws SQLException, Exception 
    {
        
        int diagramID = rs.getInt("diagramID");
        int userID = rs.getInt("userID");
        int projectID = rs.getInt("projectID");
        String name = rs.getString("name");
        String description = rs.getString("description");
        String summary = rs.getString("summary");
        String filePath = rs.getString("filePath");
        String diagramUrl = rs.getString("diagramUrl");
        int createdate = rs.getInt("createdate");
        
        return new Diagram(diagramID, userID, projectID, name, description, summary, filePath, diagramUrl, createdate);
    }
    
    private static Users createUsers(ResultSet rs) 
        throws SQLException, Exception 
    {
        
        int userID = rs.getInt("userID");
        String title = rs.getString("title");
        String firstname = rs.getString("firstname");
        String lastname = rs.getString("lastname");
        String email = rs.getString("email");
        boolean admin = rs.getBoolean("admin");
        boolean createProject = rs.getBoolean("createProject");
        
        return new Users(userID, title, firstname, lastname, email, admin, createProject);
    }  
     
    public static void main(String[] args)    
        throws SQLException, Exception
    { 

        getProjects(2);
        getDiagrams(3);          
    }
    
     public static Connection getConnection() 
        throws SQLException
    {
        return DriverManager.getConnection("jdbc:postgresql://dbteach/", "ug73dxs", "thowrisw");
    }
}

                      Why r resultsets not providing the same results as when i try them through direct code in main???

                      Thanks...

                        Comment

                        • r035198x
                          MVP
                          • Sep 2006
                          • 13225
                          #7
                          Originally posted by dev24
                          May be not...

                          I just cant figure out what am i doing wrong.. I have tried different things but keep getting exceptions... I even tried system.out.prin tln to try and print the resultset rs.getString but it bypasses everything and goes into error... I have rewritten the code which is as follows


                          Code:
                           
package database;
 
import java.net.*;
import java.io.*;
import java.util.*;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import database.Users;
import database.Project;
import database.Diagram;
import JavaSVN.ExtractText;
import java.sql.Statement;
 
 
public class QuerySourceData 
{
private ExtractText ET;
 
 
private static Object connectionLock = new Object();
 
private static PreparedStatement getProjectsStmt;
private static PreparedStatement getNameStmt;
private static PreparedStatement addUserStmt;
private static PreparedStatement addProjectStmt;
private static PreparedStatement addDiagramStmt;
private static PreparedStatement getfilePathStmt;
 
private static final String GET_NAME_STMT = "SELECT diagramID, name FROM diagram WHERE projectID = ?";
private static final String GET_PROJECTS_STMT = "SELECT projectID, name FROM project WHERE userID = ?";
private static final String GET_FILEPATH_STMT = "SELECT filePath FROM diagram WHERE diagramID = ?";
 
private static final String ADD_USER_STMT = "INSERT INTO Users VALUES (?, ?, ?, ?, ?, ?, ?)";
private static final String ADD_PROJECT_STMT = "INSERT INTO Project VALUES (?, ?, ?, ?, ?, ?)";
private static final String ADD_DIAGRAM_STMT = "INSERT INTO Diagram VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 
 
public QuerySourceData() 
throws Exception
{
try
{
Properties props = new Properties();
FileInputStream in = new FileInputStream("Z:\\Database.Properties.txt");
props.load(in);
String driver = props.getProperty("jdbc.driver");
Class.forName(driver);
String url = props.getProperty("jdbc.url");
String username = props.getProperty("jdbc.username");
String password = props.getProperty("jdbc.password"); 
in.close();
}
 
catch(Exception e)
{
System.err.println("Exception: " + e);
}
 
try
{
Connection conn = QuerySourceData.getConnection();
 
getProjectsStmt = conn.prepareStatement(GET_PROJECTS_STMT); // gets all info about projects in which user is involved from userID
getNameStmt = conn.prepareStatement(GET_NAME_STMT); //gets names of diagrams from projectID
getfilePathStmt = conn.prepareStatement(GET_FILEPATH_STMT);
}
catch(Exception e)
{
System.out.println(e);
}
}
 
 
 
public static void addUser(Users u) 
throws SQLException
{
 
synchronized (connectionLock) 
{
try 
{
addUserStmt.setInt(1, u.getuserID());
addUserStmt.setString(2, u.getTitle());
addUserStmt.setString(3, u.getfirstName());
addUserStmt.setString(4, u.getlastName());
addUserStmt.setString(5, u.getEmail());
addUserStmt.setBoolean(6, u.getAdmin());
		addUserStmt.setBoolean(7, u.getcreateProject());
 
		addUserStmt.executeUpdate();	
}
 
catch (SQLException e) 
{
throw e;
}
}
}
 
public static void addProject(Project p) 
throws SQLException
{
 
synchronized (connectionLock) 
{
try 
{
addProjectStmt.setInt(1, p.getprojectID());
addProjectStmt.setInt(2, p.getuserID());
addProjectStmt.setString(3, p.getprojectName());
addProjectStmt.setString(4, p.getDescription());
addProjectStmt.setInt(5, p.getcreateDate());
addProjectStmt.setBoolean(6, p.getVisibility());
 
		addProjectStmt.executeUpdate();	
}
 
catch (SQLException e) 
{
throw e;
}
}
}
 
public static void addDiagram(Diagram d) 
throws SQLException
{
 
synchronized (connectionLock) 
{
try 
{
addDiagramStmt.setInt(1, d.getdiagramID());
addDiagramStmt.setInt(2, d.getuserID());
addDiagramStmt.setInt(3, d.getprojectID());
addDiagramStmt.setString(4, d.getdiagramName());
addDiagramStmt.setString(5, d.getDescription());
addDiagramStmt.setString(6, d.getSummary());
		addDiagramStmt.setString(7, d.getfilePath());
addDiagramStmt.setString(8, d.getdiagramUrl());
addDiagramStmt.setInt(9, d.getcreateDate());
 
		addDiagramStmt.executeUpdate();	
}
 
catch (SQLException e) 
{
throw e;
}
}
}
 
 
 
public static String getDiagrams(int projectID) 
	throws SQLException, Exception 
{
new QuerySourceData();
Connection conn = QuerySourceData.getConnection();
getNameStmt = conn.prepareStatement(GET_NAME_STMT);
getNameStmt.setInt(1, projectID);
ResultSet rs = getNameStmt.executeQuery();
 
if(rs.first())
{
Diagram d = createDiagram(rs); 
rs.close();
conn.close();
String a = d.toString(); 
return a;
}
else 
{
throw new Exception("An error occured!");
}
 
}
 
// gets names and IDs of projects in which user is involved from userID
public static void getProjects(int userID) 
	throws SQLException, Exception 
{
new QuerySourceData();
Connection conn = QuerySourceData.getConnection(); 
getProjectsStmt = conn.prepareStatement(GET_PROJECTS_STMT); 
getProjectsStmt.setInt(1, userID);
ResultSet rs = getProjectsStmt.executeQuery();
 
if(rs.first()) 
{
Project p = createProject(rs);
rs.close();
conn.close();
String a = p.toString();
System.out.println(a);
//return a;
}
 
else
{
throw new Exception("An error occured");
} 
}
 
private static Project createProject(ResultSet rs) 
throws SQLException, Exception 
{
 
int projectID = rs.getInt("projectID");
int userID = rs.getInt("userID");
String name = rs.getString("name");
String description = rs.getString("description");
int createdate = rs.getInt("createdate");
boolean visibility = rs.getBoolean("visibility");
 
return new Project(projectID, userID, name, description, createdate, visibility);
}
 
private static Diagram createDiagram(ResultSet rs) 
throws SQLException, Exception 
{
 
int diagramID = rs.getInt("diagramID");
int userID = rs.getInt("userID");
int projectID = rs.getInt("projectID");
String name = rs.getString("name");
String description = rs.getString("description");
String summary = rs.getString("summary");
String filePath = rs.getString("filePath");
String diagramUrl = rs.getString("diagramUrl");
int createdate = rs.getInt("createdate");
 
return new Diagram(diagramID, userID, projectID, name, description, summary, filePath, diagramUrl, createdate);
}
 
private static Users createUsers(ResultSet rs) 
throws SQLException, Exception 
{
 
int userID = rs.getInt("userID");
String title = rs.getString("title");
String firstname = rs.getString("firstname");
String lastname = rs.getString("lastname");
String email = rs.getString("email");
boolean admin = rs.getBoolean("admin");
boolean createProject = rs.getBoolean("createProject");
 
return new Users(userID, title, firstname, lastname, email, admin, createProject);
} 
 
public static void main(String[] args) 
throws SQLException, Exception
{ 
 
getProjects(2);
getDiagrams(3); 
}
 
public static Connection getConnection() 
throws SQLException
{
return DriverManager.getConnection("jdbc:postgresql://dbteach/", "ug73dxs", "thowrisw");
}
}

                          Why r resultsets not providing the same results as when i try them through direct code in main???

                          Thanks...
                          What did you say is the problem now?

                            Comment

                            Previous template Next
                            Working...