473,385 Members | 1,764 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

home > topics > java > questions > getting null pointer exception for simple jdbc code??

Join Bytes to post your question to a community of 473,385 software developers and data experts.

Getting Null Pointer Exception for simple jdbc code??

8
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(rs);
arrayList.add(p);
rs.close();

}
}
catch(Exception e)
{
System.err.println(e);
}

return arrayList;

}

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


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

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);
}

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.NullPointerException

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
Mar 1 '07 #1
6 4171
r035198x
13,262 8TB
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(rs);
arrayList.add(p);
rs.close();

}
}
catch(Exception e)
{
System.err.println(e);
}

return arrayList;

}

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


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

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);
}

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.NullPointerException

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_STMT or getProjectsStmt?
Mar 1 '07 #2
dev24
8
1.Use code tags when posting code
2.Are you using GET_PROJECTS_STMT or getProjectsStmt?
Thanks for reply.. Sorry I'll bear that in mind... Basically there is a method prepareQueries() where I initialise getProjectsStmt as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. getProjectsStmt = con.prepareStatement(GET_PROJECTS_STMT);  
  3.  
  4.  
If it helps the complete code is as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. import java.net.*;
  3. import java.io.*;
  4. import java.util.*;
  5. import java.io.OutputStream;
  6. import java.sql.Connection;
  7. import java.sql.DriverManager;
  8. import java.sql.PreparedStatement;
  9. import java.sql.ResultSet;
  10. import java.sql.SQLException;
  11. import database.Users;
  12. import database.Projects;
  13. import database.Diagram;
  14. import JavaSVN.ExtractText;
  15. import java.sql.Statement;
  16.  
  17. public class QuerySourceData 
  18. {
  19.  
  20.     private static Connection con;  
  21.     private static Object connectionLock = new Object();
  22.  
  23.     private static PreparedStatement getProjectsStmt;
  24.     private static PreparedStatement getNameStmt;
  25.     private static PreparedStatement addUserStmt;
  26.     private static PreparedStatement addProjectStmt;
  27.     private static PreparedStatement addDiagramStmt;
  28.     private static PreparedStatement getfilePathStmt;
  29.  
  30.     private static final String GET_NAME_STMT = "SELECT diagramID, name FROM diagram WHERE projectID = ?";
  31.     private static final String GET_PROJECTS_STMT = "SELECT projectID, name FROM project WHERE userID = ?";
  32.     private static final String GET_FILEPATH_STMT = "SELECT filePath FROM diagram WHERE diagramID = ?";
  33.     private static final String ADD_USER_STMT = "INSERT INTO Users VALUES (?, ?, ?, ?, ?, ?, ?)";
  34.     private static final String ADD_PROJECT_STMT = "INSERT INTO Project VALUES (?, ?, ?, ?, ?, ?)";
  35.     private static final String ADD_DIAGRAM_STMT = "INSERT INTO Diagram VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
  36.  
  37.     public QuerySourceData() {}
  38.  
  39.     private static void prepareQueries()
  40.         throws SQLException, Exception
  41.     {
  42.  
  43.         getProjectsStmt = con.prepareStatement(GET_PROJECTS_STMT);            // gets all info about projects in which user is involved from userID
  44.  
  45.         getNameStmt = con.prepareStatement(GET_NAME_STMT);                  
  46.  
  47.     } 
  48.  
  49.     //enables connection to the database to perform the queries and updates
  50.     public static void connect()
  51.         throws SQLException, Exception
  52.     {
  53.         synchronized (connectionLock) 
  54.         {
  55.             if (con != null) 
  56.             {
  57.                 throw new IllegalStateException("Already connected to database, close first, then re-connect");
  58.         }
  59.  
  60.             try
  61.             {
  62.                 Properties props = new Properties();
  63.                 FileInputStream in = new FileInputStream("Z:\\Database.Properties.txt");
  64.                 props.load(in);
  65.                 String driver = props.getProperty("jdbc.driver");
  66.                 Class.forName(driver);
  67.                 String url = props.getProperty("jdbc.url");
  68.                 String username = props.getProperty("jdbc.username");
  69.                 String password = props.getProperty("jdbc.password");       
  70.                 in.close();
  71.             }
  72.  
  73.             catch(Exception e)
  74.             {
  75.                 System.err.println("Exception: " + e);
  76.             }
  77.  
  78.         }
  79.  
  80.     }
  81.  
  82.     // determines if it is still connected to the database - designed for expansion
  83.     public static boolean isConnected() 
  84.     {
  85.         synchronized (connectionLock) 
  86.         {
  87.             return con != null;
  88.         }
  89.     }
  90.  
  91.     // closes connection to the database - designed for expansion
  92.     public static void close() 
  93.         throws SQLException, Exception
  94.     {
  95.         synchronized (connectionLock) 
  96.         {
  97.             con.close();
  98.             con = null;
  99.         }        
  100.     }
  101.  
  102.     public static void addUser(Users u) 
  103.         throws SQLException
  104.     {
  105.  
  106.         synchronized (connectionLock) 
  107.         {
  108.             try 
  109.             {
  110.                 addUserStmt.setInt(1, u.getuserID());
  111.                 addUserStmt.setString(2, u.getTitle());
  112.                 addUserStmt.setString(3, u.getfirstName());
  113.                 addUserStmt.setString(4, u.getlastName());
  114.                 addUserStmt.setString(5, u.getEmail());
  115.                 addUserStmt.setBoolean(6, u.getAdmin());
  116.         addUserStmt.setBoolean(7, u.getcreateProject());
  117.  
  118.         addUserStmt.executeUpdate();    
  119.             }
  120.  
  121.             catch (SQLException e) 
  122.             {
  123.                 throw e;
  124.             }
  125.         }
  126.     }
  127.  
  128.     public static void addProject(Projects p) 
  129.         throws SQLException
  130.     {
  131.  
  132.         synchronized (connectionLock) 
  133.         {
  134.             try 
  135.             {
  136.                 addProjectStmt.setInt(1, p.getprojectID());
  137.                 addProjectStmt.setInt(2, p.getuserID());
  138.                 addProjectStmt.setString(3, p.getprojectName());
  139.                 addProjectStmt.setString(4, p.getDescription());
  140.                 addProjectStmt.setInt(5, p.getcreateDate());
  141.                 addProjectStmt.setBoolean(6, p.getVisibility());
  142.  
  143.         addProjectStmt.executeUpdate();    
  144.             }
  145.  
  146.             catch (SQLException e) 
  147.             {
  148.                 throw e;
  149.             }
  150.         }
  151.     }
  152.  
  153.     public static void addDiagram(Diagram d) 
  154.         throws SQLException
  155.     {
  156.  
  157.         synchronized (connectionLock) 
  158.         {
  159.             try 
  160.             {
  161.                 addDiagramStmt.setInt(1, d.getdiagramID());
  162.                 addDiagramStmt.setInt(2, d.getuserID());
  163.                 addDiagramStmt.setInt(3, d.getprojectID());
  164.                 addDiagramStmt.setString(4, d.getdiagramName());
  165.                 addDiagramStmt.setString(5, d.getDescription());
  166.                 addDiagramStmt.setString(6, d.getSummary());
  167.         addDiagramStmt.setString(7, d.getfilePath());
  168.                 addDiagramStmt.setString(8, d.getdiagramUrl());
  169.                 addDiagramStmt.setInt(9, d.getcreateDate());
  170.  
  171.         addDiagramStmt.executeUpdate();    
  172.             }
  173.  
  174.             catch (SQLException e) 
  175.             {
  176.                 throw e;
  177.             }
  178.         }
  179.     }
  180.  
  181.     // gets names and IDs of projects in which user is involved from userID
  182.     public static ArrayList getProjects(int userID) 
  183.         throws SQLException, Exception 
  184.     {
  185.         ArrayList arrayList = null;
  186.         getProjectsStmt = con.prepareStatement(GET_PROJECTS_STMT);
  187.         getProjectsStmt.setInt(1, userID);
  188.         ResultSet rs = getProjectsStmt.executeQuery();
  189.         arrayList = new ArrayList();
  190.  
  191.         try
  192.         {
  193.             while (rs.next()) 
  194.             {
  195.                 Projects p = createProject(rs);
  196.                 arrayList.add(p);
  197.                 rs.close();
  198.  
  199.             }
  200.         }
  201.         catch(Exception e) 
  202.         {
  203.             System.err.println(e + ": UserID entered has not been found.");
  204.         }
  205.  
  206.         return arrayList;
  207.  
  208.     }
  209.  
  210.  
  211.     private static Projects createProject(ResultSet rs) 
  212.         throws SQLException, Exception 
  213.     {
  214.  
  215.         int projectID = rs.getInt("projectID");
  216.         int userID = rs.getInt("userID");
  217.         String name = rs.getString("name");
  218.         String description = rs.getString("description");
  219.         int createdate = rs.getInt("createdate");
  220.         boolean visibility = rs.getBoolean("visibility");
  221.  
  222.         return new Projects(projectID, userID, name, description, createdate, visibility);
  223.     }
  224.  
  225.     private static Diagram createDiagram(ResultSet rs) 
  226.         throws SQLException, Exception 
  227.     {
  228.  
  229.         int diagramID = rs.getInt("diagramID");
  230.         int userID = rs.getInt("userID");
  231.         int projectID = rs.getInt("projectID");
  232.         String name = rs.getString("name");
  233.         String description = rs.getString("description");
  234.         String summary = rs.getString("summary");
  235.         String filePath = rs.getString("filePath");
  236.         String diagramUrl = rs.getString("diagramUrl");
  237.         int createdate = rs.getInt("createdate");
  238.  
  239.         return new Diagram(diagramID, userID, projectID, name, description, summary, filePath, diagramUrl, createdate);
  240.     }
  241.  
  242.     private static Users createUsers(ResultSet rs) 
  243.         throws SQLException, Exception 
  244.     {
  245.  
  246.         int userID = rs.getInt("userID");
  247.         String title = rs.getString("title");
  248.         String firstname = rs.getString("firstname");
  249.         String lastname = rs.getString("lastname");
  250.         String email = rs.getString("email");
  251.         boolean admin = rs.getBoolean("admin");
  252.         boolean createProject = rs.getBoolean("createProject");
  253.  
  254.         return new Users(userID, title, firstname, lastname, email, admin, createProject);
  255.     }  
  256.  
  257.     public static void main(String[] args)    
  258.         throws SQLException, Exception
  259.     { 
  260.  
  261.         connect();    
  262.         prepareQueries();
  263.         getProjects(1);
  264.  
  265.     }
  266. }
  267.  
Thanks!!

Dev
Mar 1 '07 #3
r035198x
13,262 8TB
Thanks for reply.. Sorry I'll bear that in mind... Basically there is a method prepareQueries() where I initialise getProjectsStmt as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. getProjectsStmt = con.prepareStatement(GET_PROJECTS_STMT); 
  3.  
  4.  
If it helps the complete code is as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. import java.net.*;
  3. import java.io.*;
  4. import java.util.*;
  5. import java.io.OutputStream;
  6. import java.sql.Connection;
  7. import java.sql.DriverManager;
  8. import java.sql.PreparedStatement;
  9. import java.sql.ResultSet;
  10. import java.sql.SQLException;
  11. import database.Users;
  12. import database.Projects;
  13. import database.Diagram;
  14. import JavaSVN.ExtractText;
  15. import java.sql.Statement;
  16.  
  17. public class QuerySourceData 
  18. {
  19.  
  20. private static Connection con; 
  21. private static Object connectionLock = new Object();
  22.  
  23. private static PreparedStatement getProjectsStmt;
  24. private static PreparedStatement getNameStmt;
  25. private static PreparedStatement addUserStmt;
  26. private static PreparedStatement addProjectStmt;
  27. private static PreparedStatement addDiagramStmt;
  28. private static PreparedStatement getfilePathStmt;
  29.  
  30. private static final String GET_NAME_STMT = "SELECT diagramID, name FROM diagram WHERE projectID = ?";
  31. private static final String GET_PROJECTS_STMT = "SELECT projectID, name FROM project WHERE userID = ?";
  32. private static final String GET_FILEPATH_STMT = "SELECT filePath FROM diagram WHERE diagramID = ?";
  33. private static final String ADD_USER_STMT = "INSERT INTO Users VALUES (?, ?, ?, ?, ?, ?, ?)";
  34. private static final String ADD_PROJECT_STMT = "INSERT INTO Project VALUES (?, ?, ?, ?, ?, ?)";
  35. private static final String ADD_DIAGRAM_STMT = "INSERT INTO Diagram VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
  36.  
  37. public QuerySourceData() {}
  38.  
  39. private static void prepareQueries()
  40. throws SQLException, Exception
  41. {
  42.  
  43. getProjectsStmt = con.prepareStatement(GET_PROJECTS_STMT); // gets all info about projects in which user is involved from userID
  44.  
  45. getNameStmt = con.prepareStatement(GET_NAME_STMT); 
  46.  
  47. } 
  48.  
  49. //enables connection to the database to perform the queries and updates
  50. public static void connect()
  51. throws SQLException, Exception
  52. {
  53. synchronized (connectionLock) 
  54. {
  55. if (con != null) 
  56. {
  57. throw new IllegalStateException("Already connected to database, close first, then re-connect");
  58.      }
  59.  
  60. try
  61. {
  62. Properties props = new Properties();
  63. FileInputStream in = new FileInputStream("Z:\\Database.Properties.txt");
  64. props.load(in);
  65. String driver = props.getProperty("jdbc.driver");
  66. Class.forName(driver);
  67. String url = props.getProperty("jdbc.url");
  68. String username = props.getProperty("jdbc.username");
  69. String password = props.getProperty("jdbc.password"); 
  70. in.close();
  71. }
  72.  
  73. catch(Exception e)
  74. {
  75. System.err.println("Exception: " + e);
  76. }
  77.  
  78. }
  79.  
  80. }
  81.  
  82. // determines if it is still connected to the database - designed for expansion
  83. public static boolean isConnected() 
  84. {
  85. synchronized (connectionLock) 
  86. {
  87. return con != null;
  88. }
  89. }
  90.  
  91. // closes connection to the database - designed for expansion
  92. public static void close() 
  93. throws SQLException, Exception
  94. {
  95. synchronized (connectionLock) 
  96. {
  97. con.close();
  98. con = null;
  99. } 
  100. }
  101.  
  102. public static void addUser(Users u) 
  103. throws SQLException
  104. {
  105.  
  106. synchronized (connectionLock) 
  107. {
  108. try 
  109. {
  110. addUserStmt.setInt(1, u.getuserID());
  111. addUserStmt.setString(2, u.getTitle());
  112. addUserStmt.setString(3, u.getfirstName());
  113. addUserStmt.setString(4, u.getlastName());
  114. addUserStmt.setString(5, u.getEmail());
  115. addUserStmt.setBoolean(6, u.getAdmin());
  116.         addUserStmt.setBoolean(7, u.getcreateProject());
  117.  
  118.         addUserStmt.executeUpdate();    
  119. }
  120.  
  121. catch (SQLException e) 
  122. {
  123. throw e;
  124. }
  125. }
  126. }
  127.  
  128. public static void addProject(Projects p) 
  129. throws SQLException
  130. {
  131.  
  132. synchronized (connectionLock) 
  133. {
  134. try 
  135. {
  136. addProjectStmt.setInt(1, p.getprojectID());
  137. addProjectStmt.setInt(2, p.getuserID());
  138. addProjectStmt.setString(3, p.getprojectName());
  139. addProjectStmt.setString(4, p.getDescription());
  140. addProjectStmt.setInt(5, p.getcreateDate());
  141. addProjectStmt.setBoolean(6, p.getVisibility());
  142.  
  143.         addProjectStmt.executeUpdate();    
  144. }
  145.  
  146. catch (SQLException e) 
  147. {
  148. throw e;
  149. }
  150. }
  151. }
  152.  
  153. public static void addDiagram(Diagram d) 
  154. throws SQLException
  155. {
  156.  
  157. synchronized (connectionLock) 
  158. {
  159. try 
  160. {
  161. addDiagramStmt.setInt(1, d.getdiagramID());
  162. addDiagramStmt.setInt(2, d.getuserID());
  163. addDiagramStmt.setInt(3, d.getprojectID());
  164. addDiagramStmt.setString(4, d.getdiagramName());
  165. addDiagramStmt.setString(5, d.getDescription());
  166. addDiagramStmt.setString(6, d.getSummary());
  167.         addDiagramStmt.setString(7, d.getfilePath());
  168. addDiagramStmt.setString(8, d.getdiagramUrl());
  169. addDiagramStmt.setInt(9, d.getcreateDate());
  170.  
  171.         addDiagramStmt.executeUpdate();    
  172. }
  173.  
  174. catch (SQLException e) 
  175. {
  176. throw e;
  177. }
  178. }
  179. }
  180.  
  181. // gets names and IDs of projects in which user is involved from userID
  182. public static ArrayList getProjects(int userID) 
  183.     throws SQLException, Exception 
  184. {
  185. ArrayList arrayList = null;
  186. getProjectsStmt = con.prepareStatement(GET_PROJECTS_STMT);
  187. getProjectsStmt.setInt(1, userID);
  188. ResultSet rs = getProjectsStmt.executeQuery();
  189. arrayList = new ArrayList();
  190.  
  191. try
  192. {
  193. while (rs.next()) 
  194. {
  195. Projects p = createProject(rs);
  196. arrayList.add(p);
  197. rs.close();
  198.  
  199. }
  200. }
  201. catch(Exception e) 
  202. {
  203. System.err.println(e + ": UserID entered has not been found.");
  204. }
  205.  
  206. return arrayList;
  207.  
  208. }
  209.  
  210.  
  211. private static Projects createProject(ResultSet rs) 
  212. throws SQLException, Exception 
  213. {
  214.  
  215. int projectID = rs.getInt("projectID");
  216. int userID = rs.getInt("userID");
  217. String name = rs.getString("name");
  218. String description = rs.getString("description");
  219. int createdate = rs.getInt("createdate");
  220. boolean visibility = rs.getBoolean("visibility");
  221.  
  222. return new Projects(projectID, userID, name, description, createdate, visibility);
  223. }
  224.  
  225. private static Diagram createDiagram(ResultSet rs) 
  226. throws SQLException, Exception 
  227. {
  228.  
  229. int diagramID = rs.getInt("diagramID");
  230. int userID = rs.getInt("userID");
  231. int projectID = rs.getInt("projectID");
  232. String name = rs.getString("name");
  233. String description = rs.getString("description");
  234. String summary = rs.getString("summary");
  235. String filePath = rs.getString("filePath");
  236. String diagramUrl = rs.getString("diagramUrl");
  237. int createdate = rs.getInt("createdate");
  238.  
  239. return new Diagram(diagramID, userID, projectID, name, description, summary, filePath, diagramUrl, createdate);
  240. }
  241.  
  242. private static Users createUsers(ResultSet rs) 
  243. throws SQLException, Exception 
  244. {
  245.  
  246. int userID = rs.getInt("userID");
  247. String title = rs.getString("title");
  248. String firstname = rs.getString("firstname");
  249. String lastname = rs.getString("lastname");
  250. String email = rs.getString("email");
  251. boolean admin = rs.getBoolean("admin");
  252. boolean createProject = rs.getBoolean("createProject");
  253.  
  254. return new Users(userID, title, firstname, lastname, email, admin, createProject);
  255. } 
  256.  
  257. public static void main(String[] args) 
  258. throws SQLException, Exception
  259. { 
  260.  
  261. connect(); 
  262. prepareQueries();
  263. getProjects(1);
  264.  
  265. }
  266. }
  267.  
Thanks!!

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

Expand|Select|Wrap|Line Numbers
  1.  private static Connection con; 
Your constructor does not initialize it either so it's probably null when you want to use it
Mar 1 '07 #4
dev24
8
I have to go but look through your code and see how you intialized con

Expand|Select|Wrap|Line Numbers
  1.  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
Mar 1 '07 #5
dev24
8
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.println 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


Expand|Select|Wrap|Line Numbers
  1.  
  2. package database;
  3.  
  4. import java.net.*;
  5. import java.io.*;
  6. import java.util.*;
  7. import java.io.OutputStream;
  8. import java.sql.Connection;
  9. import java.sql.DriverManager;
  10. import java.sql.PreparedStatement;
  11. import java.sql.ResultSet;
  12. import java.sql.SQLException;
  13. import database.Users;
  14. import database.Project;
  15. import database.Diagram;
  16. import JavaSVN.ExtractText;
  17. import java.sql.Statement;
  18.  
  19.  
  20. public class QuerySourceData 
  21. {
  22.     private ExtractText ET;
  23.  
  24.  
  25.     private static Object connectionLock = new Object();
  26.  
  27.     private static PreparedStatement getProjectsStmt;
  28.     private static PreparedStatement getNameStmt;
  29.     private static PreparedStatement addUserStmt;
  30.     private static PreparedStatement addProjectStmt;
  31.     private static PreparedStatement addDiagramStmt;
  32.     private static PreparedStatement getfilePathStmt;
  33.  
  34.     private static final String GET_NAME_STMT = "SELECT diagramID, name FROM diagram WHERE projectID = ?";
  35.     private static final String GET_PROJECTS_STMT = "SELECT projectID, name FROM project WHERE userID = ?";
  36.     private static final String GET_FILEPATH_STMT = "SELECT filePath FROM diagram WHERE diagramID = ?";
  37.  
  38.     private static final String ADD_USER_STMT = "INSERT INTO Users VALUES (?, ?, ?, ?, ?, ?, ?)";
  39.     private static final String ADD_PROJECT_STMT = "INSERT INTO Project VALUES (?, ?, ?, ?, ?, ?)";
  40.     private static final String ADD_DIAGRAM_STMT = "INSERT INTO Diagram VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
  41.  
  42.  
  43.     public QuerySourceData() 
  44.         throws Exception
  45.     {
  46.         try
  47.         {
  48.             Properties props = new Properties();
  49.             FileInputStream in = new FileInputStream("Z:\\Database.Properties.txt");
  50.             props.load(in);
  51.             String driver = props.getProperty("jdbc.driver");
  52.             Class.forName(driver);
  53.             String url = props.getProperty("jdbc.url");
  54.             String username = props.getProperty("jdbc.username");
  55.             String password = props.getProperty("jdbc.password");       
  56.             in.close();
  57.         }
  58.  
  59.         catch(Exception e)
  60.         {
  61.             System.err.println("Exception: " + e);
  62.         }
  63.  
  64.         try
  65.         {
  66.             Connection conn = QuerySourceData.getConnection();
  67.  
  68.             getProjectsStmt = conn.prepareStatement(GET_PROJECTS_STMT);            // gets all info about projects in which user is involved from userID
  69.             getNameStmt = conn.prepareStatement(GET_NAME_STMT);                  //gets names of diagrams from projectID
  70.             getfilePathStmt = conn.prepareStatement(GET_FILEPATH_STMT);
  71.         }
  72.         catch(Exception e)
  73.         {
  74.             System.out.println(e);
  75.         }
  76.     }
  77.  
  78.  
  79.  
  80.     public static void addUser(Users u) 
  81.         throws SQLException
  82.     {
  83.  
  84.         synchronized (connectionLock) 
  85.         {
  86.             try 
  87.             {
  88.                 addUserStmt.setInt(1, u.getuserID());
  89.                 addUserStmt.setString(2, u.getTitle());
  90.                 addUserStmt.setString(3, u.getfirstName());
  91.                 addUserStmt.setString(4, u.getlastName());
  92.                 addUserStmt.setString(5, u.getEmail());
  93.                 addUserStmt.setBoolean(6, u.getAdmin());
  94.         addUserStmt.setBoolean(7, u.getcreateProject());
  95.  
  96.         addUserStmt.executeUpdate();    
  97.             }
  98.  
  99.             catch (SQLException e) 
  100.             {
  101.                 throw e;
  102.             }
  103.         }
  104.     }
  105.  
  106.     public static void addProject(Project p) 
  107.         throws SQLException
  108.     {
  109.  
  110.         synchronized (connectionLock) 
  111.         {
  112.             try 
  113.             {
  114.                 addProjectStmt.setInt(1, p.getprojectID());
  115.                 addProjectStmt.setInt(2, p.getuserID());
  116.                 addProjectStmt.setString(3, p.getprojectName());
  117.                 addProjectStmt.setString(4, p.getDescription());
  118.                 addProjectStmt.setInt(5, p.getcreateDate());
  119.                 addProjectStmt.setBoolean(6, p.getVisibility());
  120.  
  121.         addProjectStmt.executeUpdate();    
  122.             }
  123.  
  124.             catch (SQLException e) 
  125.             {
  126.                 throw e;
  127.             }
  128.         }
  129.     }
  130.  
  131.     public static void addDiagram(Diagram d) 
  132.         throws SQLException
  133.     {
  134.  
  135.         synchronized (connectionLock) 
  136.         {
  137.             try 
  138.             {
  139.                 addDiagramStmt.setInt(1, d.getdiagramID());
  140.                 addDiagramStmt.setInt(2, d.getuserID());
  141.                 addDiagramStmt.setInt(3, d.getprojectID());
  142.                 addDiagramStmt.setString(4, d.getdiagramName());
  143.                 addDiagramStmt.setString(5, d.getDescription());
  144.                 addDiagramStmt.setString(6, d.getSummary());
  145.         addDiagramStmt.setString(7, d.getfilePath());
  146.                 addDiagramStmt.setString(8, d.getdiagramUrl());
  147.                 addDiagramStmt.setInt(9, d.getcreateDate());
  148.  
  149.         addDiagramStmt.executeUpdate();    
  150.             }
  151.  
  152.             catch (SQLException e) 
  153.             {
  154.                 throw e;
  155.             }
  156.         }
  157.     }
  158.  
  159.  
  160.  
  161.      public static String getDiagrams(int projectID) 
  162.         throws SQLException, Exception 
  163.     {
  164.         new QuerySourceData();
  165.         Connection conn = QuerySourceData.getConnection();
  166.         getNameStmt = conn.prepareStatement(GET_NAME_STMT);
  167.         getNameStmt.setInt(1, projectID);
  168.         ResultSet rs = getNameStmt.executeQuery();
  169.  
  170.         if(rs.first())
  171.         {
  172.             Diagram d = createDiagram(rs);              
  173.             rs.close();
  174.             conn.close();
  175.             String a = d.toString(); 
  176.             return a;
  177.         }
  178.         else 
  179.         {
  180.             throw new Exception("An error occured!");
  181.         }
  182.  
  183.     }
  184.  
  185.     // gets names and IDs of projects in which user is involved from userID
  186.     public static void getProjects(int userID) 
  187.         throws SQLException, Exception 
  188.     {
  189.         new QuerySourceData();
  190.         Connection conn = QuerySourceData.getConnection();            
  191.         getProjectsStmt = conn.prepareStatement(GET_PROJECTS_STMT); 
  192.         getProjectsStmt.setInt(1, userID);
  193.         ResultSet rs = getProjectsStmt.executeQuery();
  194.  
  195.         if(rs.first()) 
  196.         {
  197.             Project p = createProject(rs);
  198.             rs.close();
  199.             conn.close();
  200.             String a = p.toString();
  201.             System.out.println(a);
  202.             //return a;
  203.         }
  204.  
  205.          else
  206.          {
  207.             throw new Exception("An error occured");
  208.          }    
  209.     }
  210.  
  211.     private static Project createProject(ResultSet rs) 
  212.         throws SQLException, Exception 
  213.     {
  214.  
  215.         int projectID = rs.getInt("projectID");
  216.         int userID = rs.getInt("userID");
  217.         String name = rs.getString("name");
  218.         String description = rs.getString("description");
  219.         int createdate = rs.getInt("createdate");
  220.         boolean visibility = rs.getBoolean("visibility");
  221.  
  222.         return new Project(projectID, userID, name, description, createdate, visibility);
  223.     }
  224.  
  225.     private static Diagram createDiagram(ResultSet rs) 
  226.         throws SQLException, Exception 
  227.     {
  228.  
  229.         int diagramID = rs.getInt("diagramID");
  230.         int userID = rs.getInt("userID");
  231.         int projectID = rs.getInt("projectID");
  232.         String name = rs.getString("name");
  233.         String description = rs.getString("description");
  234.         String summary = rs.getString("summary");
  235.         String filePath = rs.getString("filePath");
  236.         String diagramUrl = rs.getString("diagramUrl");
  237.         int createdate = rs.getInt("createdate");
  238.  
  239.         return new Diagram(diagramID, userID, projectID, name, description, summary, filePath, diagramUrl, createdate);
  240.     }
  241.  
  242.     private static Users createUsers(ResultSet rs) 
  243.         throws SQLException, Exception 
  244.     {
  245.  
  246.         int userID = rs.getInt("userID");
  247.         String title = rs.getString("title");
  248.         String firstname = rs.getString("firstname");
  249.         String lastname = rs.getString("lastname");
  250.         String email = rs.getString("email");
  251.         boolean admin = rs.getBoolean("admin");
  252.         boolean createProject = rs.getBoolean("createProject");
  253.  
  254.         return new Users(userID, title, firstname, lastname, email, admin, createProject);
  255.     }  
  256.  
  257.     public static void main(String[] args)    
  258.         throws SQLException, Exception
  259.     { 
  260.  
  261.         getProjects(2);
  262.         getDiagrams(3);          
  263.     }
  264.  
  265.      public static Connection getConnection() 
  266.         throws SQLException
  267.     {
  268.         return DriverManager.getConnection("jdbc:postgresql://dbteach/", "ug73dxs", "thowrisw");
  269.     }
  270. }
  271.  
  272.  

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

Thanks...
Mar 2 '07 #6
r035198x
13,262 8TB
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.println 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


Expand|Select|Wrap|Line Numbers
  1.  
  2. package database;
  3.  
  4. import java.net.*;
  5. import java.io.*;
  6. import java.util.*;
  7. import java.io.OutputStream;
  8. import java.sql.Connection;
  9. import java.sql.DriverManager;
  10. import java.sql.PreparedStatement;
  11. import java.sql.ResultSet;
  12. import java.sql.SQLException;
  13. import database.Users;
  14. import database.Project;
  15. import database.Diagram;
  16. import JavaSVN.ExtractText;
  17. import java.sql.Statement;
  18.  
  19.  
  20. public class QuerySourceData 
  21. {
  22. private ExtractText ET;
  23.  
  24.  
  25. private static Object connectionLock = new Object();
  26.  
  27. private static PreparedStatement getProjectsStmt;
  28. private static PreparedStatement getNameStmt;
  29. private static PreparedStatement addUserStmt;
  30. private static PreparedStatement addProjectStmt;
  31. private static PreparedStatement addDiagramStmt;
  32. private static PreparedStatement getfilePathStmt;
  33.  
  34. private static final String GET_NAME_STMT = "SELECT diagramID, name FROM diagram WHERE projectID = ?";
  35. private static final String GET_PROJECTS_STMT = "SELECT projectID, name FROM project WHERE userID = ?";
  36. private static final String GET_FILEPATH_STMT = "SELECT filePath FROM diagram WHERE diagramID = ?";
  37.  
  38. private static final String ADD_USER_STMT = "INSERT INTO Users VALUES (?, ?, ?, ?, ?, ?, ?)";
  39. private static final String ADD_PROJECT_STMT = "INSERT INTO Project VALUES (?, ?, ?, ?, ?, ?)";
  40. private static final String ADD_DIAGRAM_STMT = "INSERT INTO Diagram VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
  41.  
  42.  
  43. public QuerySourceData() 
  44. throws Exception
  45. {
  46. try
  47. {
  48. Properties props = new Properties();
  49. FileInputStream in = new FileInputStream("Z:\\Database.Properties.txt");
  50. props.load(in);
  51. String driver = props.getProperty("jdbc.driver");
  52. Class.forName(driver);
  53. String url = props.getProperty("jdbc.url");
  54. String username = props.getProperty("jdbc.username");
  55. String password = props.getProperty("jdbc.password"); 
  56. in.close();
  57. }
  58.  
  59. catch(Exception e)
  60. {
  61. System.err.println("Exception: " + e);
  62. }
  63.  
  64. try
  65. {
  66. Connection conn = QuerySourceData.getConnection();
  67.  
  68. getProjectsStmt = conn.prepareStatement(GET_PROJECTS_STMT); // gets all info about projects in which user is involved from userID
  69. getNameStmt = conn.prepareStatement(GET_NAME_STMT); //gets names of diagrams from projectID
  70. getfilePathStmt = conn.prepareStatement(GET_FILEPATH_STMT);
  71. }
  72. catch(Exception e)
  73. {
  74. System.out.println(e);
  75. }
  76. }
  77.  
  78.  
  79.  
  80. public static void addUser(Users u) 
  81. throws SQLException
  82. {
  83.  
  84. synchronized (connectionLock) 
  85. {
  86. try 
  87. {
  88. addUserStmt.setInt(1, u.getuserID());
  89. addUserStmt.setString(2, u.getTitle());
  90. addUserStmt.setString(3, u.getfirstName());
  91. addUserStmt.setString(4, u.getlastName());
  92. addUserStmt.setString(5, u.getEmail());
  93. addUserStmt.setBoolean(6, u.getAdmin());
  94.         addUserStmt.setBoolean(7, u.getcreateProject());
  95.  
  96.         addUserStmt.executeUpdate();    
  97. }
  98.  
  99. catch (SQLException e) 
  100. {
  101. throw e;
  102. }
  103. }
  104. }
  105.  
  106. public static void addProject(Project p) 
  107. throws SQLException
  108. {
  109.  
  110. synchronized (connectionLock) 
  111. {
  112. try 
  113. {
  114. addProjectStmt.setInt(1, p.getprojectID());
  115. addProjectStmt.setInt(2, p.getuserID());
  116. addProjectStmt.setString(3, p.getprojectName());
  117. addProjectStmt.setString(4, p.getDescription());
  118. addProjectStmt.setInt(5, p.getcreateDate());
  119. addProjectStmt.setBoolean(6, p.getVisibility());
  120.  
  121.         addProjectStmt.executeUpdate();    
  122. }
  123.  
  124. catch (SQLException e) 
  125. {
  126. throw e;
  127. }
  128. }
  129. }
  130.  
  131. public static void addDiagram(Diagram d) 
  132. throws SQLException
  133. {
  134.  
  135. synchronized (connectionLock) 
  136. {
  137. try 
  138. {
  139. addDiagramStmt.setInt(1, d.getdiagramID());
  140. addDiagramStmt.setInt(2, d.getuserID());
  141. addDiagramStmt.setInt(3, d.getprojectID());
  142. addDiagramStmt.setString(4, d.getdiagramName());
  143. addDiagramStmt.setString(5, d.getDescription());
  144. addDiagramStmt.setString(6, d.getSummary());
  145.         addDiagramStmt.setString(7, d.getfilePath());
  146. addDiagramStmt.setString(8, d.getdiagramUrl());
  147. addDiagramStmt.setInt(9, d.getcreateDate());
  148.  
  149.         addDiagramStmt.executeUpdate();    
  150. }
  151.  
  152. catch (SQLException e) 
  153. {
  154. throw e;
  155. }
  156. }
  157. }
  158.  
  159.  
  160.  
  161. public static String getDiagrams(int projectID) 
  162.     throws SQLException, Exception 
  163. {
  164. new QuerySourceData();
  165. Connection conn = QuerySourceData.getConnection();
  166. getNameStmt = conn.prepareStatement(GET_NAME_STMT);
  167. getNameStmt.setInt(1, projectID);
  168. ResultSet rs = getNameStmt.executeQuery();
  169.  
  170. if(rs.first())
  171. {
  172. Diagram d = createDiagram(rs); 
  173. rs.close();
  174. conn.close();
  175. String a = d.toString(); 
  176. return a;
  177. }
  178. else 
  179. {
  180. throw new Exception("An error occured!");
  181. }
  182.  
  183. }
  184.  
  185. // gets names and IDs of projects in which user is involved from userID
  186. public static void getProjects(int userID) 
  187.     throws SQLException, Exception 
  188. {
  189. new QuerySourceData();
  190. Connection conn = QuerySourceData.getConnection(); 
  191. getProjectsStmt = conn.prepareStatement(GET_PROJECTS_STMT); 
  192. getProjectsStmt.setInt(1, userID);
  193. ResultSet rs = getProjectsStmt.executeQuery();
  194.  
  195. if(rs.first()) 
  196. {
  197. Project p = createProject(rs);
  198. rs.close();
  199. conn.close();
  200. String a = p.toString();
  201. System.out.println(a);
  202. //return a;
  203. }
  204.  
  205. else
  206. {
  207. throw new Exception("An error occured");
  208. } 
  209. }
  210.  
  211. private static Project createProject(ResultSet rs) 
  212. throws SQLException, Exception 
  213. {
  214.  
  215. int projectID = rs.getInt("projectID");
  216. int userID = rs.getInt("userID");
  217. String name = rs.getString("name");
  218. String description = rs.getString("description");
  219. int createdate = rs.getInt("createdate");
  220. boolean visibility = rs.getBoolean("visibility");
  221.  
  222. return new Project(projectID, userID, name, description, createdate, visibility);
  223. }
  224.  
  225. private static Diagram createDiagram(ResultSet rs) 
  226. throws SQLException, Exception 
  227. {
  228.  
  229. int diagramID = rs.getInt("diagramID");
  230. int userID = rs.getInt("userID");
  231. int projectID = rs.getInt("projectID");
  232. String name = rs.getString("name");
  233. String description = rs.getString("description");
  234. String summary = rs.getString("summary");
  235. String filePath = rs.getString("filePath");
  236. String diagramUrl = rs.getString("diagramUrl");
  237. int createdate = rs.getInt("createdate");
  238.  
  239. return new Diagram(diagramID, userID, projectID, name, description, summary, filePath, diagramUrl, createdate);
  240. }
  241.  
  242. private static Users createUsers(ResultSet rs) 
  243. throws SQLException, Exception 
  244. {
  245.  
  246. int userID = rs.getInt("userID");
  247. String title = rs.getString("title");
  248. String firstname = rs.getString("firstname");
  249. String lastname = rs.getString("lastname");
  250. String email = rs.getString("email");
  251. boolean admin = rs.getBoolean("admin");
  252. boolean createProject = rs.getBoolean("createProject");
  253.  
  254. return new Users(userID, title, firstname, lastname, email, admin, createProject);
  255. } 
  256.  
  257. public static void main(String[] args) 
  258. throws SQLException, Exception
  259. { 
  260.  
  261. getProjects(2);
  262. getDiagrams(3); 
  263. }
  264.  
  265. public static Connection getConnection() 
  266. throws SQLException
  267. {
  268. return DriverManager.getConnection("jdbc:postgresql://dbteach/", "ug73dxs", "thowrisw");
  269. }
  270. }
  271.  
  272.  

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?
Mar 2 '07 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

14
Getting intermediate results in 64 bits
by: Richard Cavell | last post by:
Hi, Using GCC on my G4, if I have a calculation like this: #include <stdint.h> uint64_t a = 0xffff * 0xffff ; the result will be clobbered to 32 bits because that's the length of an...
C / C++
18
exceptions with null pointers
by: Denis Petronenko | last post by:
Hello, in the following code i have segmentaion fault instead of exception. Why? What i must to do to catch exceptions in such situation? Used compiler: gcc version 3.3.6 (Debian 1:3.3.6-13) ...
C / C++
3
NULL pointer returned in calloc
by: Alexander Behnke | last post by:
Hello, I am currently facing a problem with a calloc function call which returns a NULL pointer while in a debugging environment. If I start the same executable from the shell (not via the...
C / C++
1
Null Pointer Exception for simple jdbc code
by: dev24 | last post by:
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...
Java
22
Getting SQLCODE -302 after migrating DB2 V7 to V8 IN ibm z/os
by: Sri | last post by:
All Recenetly our shop migrated to DB2 V8 from V7. We are in IBM System Level: z/OS 1.6.1 @ RSU 0702. Processor : IBM 2064-1C7 (z/900) # 1B89 Mode: 64-bit One of my application is facing...
DB2 Database
2
jdbc createConnection(String url) throws exception
by: bevis | last post by:
I'm new to sql server and mysql but this seems like it should be a pretty straight forward jdbc connection. But I have spent almost 2 days just trying to get a jdbc connection. Please help if you...
Microsoft SQL Server
76
'null' references
by: valentin tihomirov | last post by:
As explained in "Using pointers vs. references" http://groups.google.ee/group/borland.public.delphi.objectpascal/browse_thread/thread/683c30f161fc1e9c/ab294c7b02e8faca#ab294c7b02e8faca , the...
C# / C Sharp
1
getting Null Pointer exception
by: MRamaLakshmi | last post by:
hi, I am developing an application using Java Applet which will be uploading files. Its throwing Null Pointer exception while detecting the proxy when we are trying to load the applet using Java6....
Java
1
exception:error in mysql syntax
by: ajos | last post by:
hi evrybdy, the problem is:- i had this running before in jsp but when i changed the jsp page using struts tags there occoured a problem... when i enter values in the 2 text boxes and click enter...
MySQL Database
0
Basic Javascript concepts
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
Javascript
0
Batch import of multiple excel files into the database
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
Data Management
0
Merging data from multiple Excel files
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
Data Management
0
Migrating Website to Cloud - Emmanuel Katto
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
General
0
BarryA
Navigating the Data Structures and Algorithms (DSA)
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
Algorithms / Advanced Math
1
Is that possible of reading the .csv file in column wise and the column have different lengths ?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
C / C++
0
marktang
What is ONU?
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
General
0
Changing the language in Windows 10
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
Windows Server
0
Oralloy
Problem With Comparison Operator <=> in G++
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
C / C++

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes:
Post a Job
Sponsored Posts
Platinum & Gold Sponsors
Hire Now!