• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to insert data into MySQL database by writing java code in class that is not Main class

 
Greenhorn
Posts: 23
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a problem. For my assignment I need to write a program in java that will insert data into MySQL database by writing code in java class that is not Main class. I have created a class called Books with a method insert in it, which should insert data into MySQL database. Then I made an instance of the class Books in Main class and tried to run the insert method.
But something is wrong with my code and I get an error message:

Exception in thread "main" java.lang.NullPointerException
at Books.insert(Books.java:15)
at Main.main(Main.java:25)

I've tried to correct my code but I can't make it work. Can somebody help me? Thanks in advance. Here's my code:

// Class Books



// Main Class



 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Milos Radovic,

First of all, a warm welcome to CodeRanch!

Milos Radovic wrote:I've tried to correct my code but I can't make it work. Can somebody help me?


We will definitely help you

Did you try debugging your code? Do you know which line (and reference variable) is causing the NullPointerException to be thrown? You first have to discover what's causing the issue before you can fix it. Once you have fixed your application and it is working, we'll discuss the overall approach of your code a little bit as well. You are using prepared statements to insert data into your table. That's awesome and the way to go

Hope it helps!
Kind regards,
Roel
 
Milos Radovic
Greenhorn
Posts: 23
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Roel De Nijs wrote:

Did you try debugging your code? Do you know which line (and reference variable) is causing the NullPointerException to be thrown?



Hello Thank you for your answer! I must say that I'm totally new in programming. As far as I understand the NullPointerException is in the Mian Class at line:
and in the Books Class at line:
I tried to fix this code but there is something wrong. Unfortunately don't know how to solve this.
 
Rancher
Posts: 4801
50
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If that line in Books is the source of the exception then there is only one thing on that line that can be null and cause the issue. That is 'conn'.

So the next job is to find out why conn is null.

Books has a conn instance declared as a member attribute. This looks like the instance of 'conn' that is being used in the insert() method. So, where is that variable initialised?
 
Milos Radovic
Greenhorn
Posts: 23
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:Books has a conn instance declared as a member attribute. This looks like the instance of 'conn' that is being used in the insert() method. So, where is that variable initialised?



You are right, I shouldn't have declared conn as an attribute in Books but I should have used the instance of the Connection (conn) method from the Main class. So I just wrote Main.conn:

and the problem is fixed

Thank you very much Dave You helped a lot!
Best regards,
Milos
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Milos Radovic wrote:I tried to fix this code but there is something wrong. Unfortunately don't know how to solve this.


Debugging an application is one of the most important skills of a developer. And you will learn very quickly how it is done, because you will do it a gazillion times

Milos Radovic wrote:and the problem is fixed


That was indeed the issue causing the NullPointerException being thrown at runtime. Since you have fixed your issue, we can now discuss your code a bit.

Let's start with a very important issue with your code: to avoid resource leaks you should always close your resources. If you are using Java 7 (or later) you could use a try-with-resources statement. More info about how to this statement (and what to do if you are using a Java version prior to Java 7) can be found in this section of Oracle's Java tutorial.

From a design point of view, it is a good thing that you created a seperate class to insert a record into the books table. In this class you could also add other CRUD operations to this books table (e.g. update, delete, get, find,...). According to the naming conventions a class name should be a noun. Because you are creating a DAO or repository for the books table, BookDao or BookRepository would be a more appropriate name.

So you have created a seperate class for the insert method, which is good. But why didn't you create a seperate class to create Connection objects as well, instead of having a static method in your Main class to create a Connection object and exposing a public variable? Maybe a factory to create Connection objects, like this classAnd then you create an instance of this factory in your BookDao class and invoke the create method whenever you need a Connection instance

Finally, if you are using a JDBC 4.0 compliant driver, you no longer need to explicitly load JDBC drivers using Class.forName(). The JDBC drivers are automatically loaded during initialitzation, thanks to the Java SE Service Provider mechanism (SPM). So you can remove the first line from your Connection() method (+ remove some of the exceptions from the throws clause as well) and the code will still work as intended.

Hope it helps!
Kind regards,
Roel
 
Milos Radovic
Greenhorn
Posts: 23
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dear Roel thank you VERY much for all your suggestions They really mean a lot to me since I'm still learning basics of Java programming.

Roel De Nijs wrote:
to avoid resource leaks you should always close your resources.



I already made the method close() in Main class which should be used to close the connection to the database.
So can I, in this case, just write:
at the end of the insert method to close the connection?



I will now create separate project and try to apply all your suggestions.

Thank you one more time for your help.

Kind regards,
Milos
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Milos Radovic wrote:at the end of the insert method to close the connection?


It is better than not closing any resources, but it is definitely not enough! You should also close your prepared statement. And you have to make sure your resources will be closed, even if an exception is thrown at runtime. You should definitely read that article about the try-with-resources statement from my previous post.

Milos Radovic wrote:I will now create separate project and try to apply all your suggestions.


Once you have incorporated all my suggestions, your insert() method should look like thisAnd that doesn't only look much nicer than your current code, but also guarantees that your resources (connection and prepared statement) will always be closed, even if an exception is thrown at runtime.

Hope it helps!
Kind regards,
Roel
 
Milos Radovic
Greenhorn
Posts: 23
2
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well here it is, I tried to put but instead I made the method static because that's the only way that I made it work:

ConnectionFactory class:



BookDao class:



Main class:



Cheers!
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Milos Radovic wrote:Well here it is


That already looks so much better. Well done! Have a cow for all the effort you made to refactor your code.

But there are still some improvements you can (and should) make:

1/ you said you had to make the create() method a class (static) method in order to make it work. Although it works, it is not a very good approach. Using too many static methods can be a code smell (and can indicate very poor OO design). And in your code it is very easy to fix. In the BookDao class you create an instance variable of type ConnectionFactory and you initialize it with (of course) a new ConnectionFactory instance. Now you can invoke the instance method create() on this instance variable from any instance method of the BookDao class.

2/ in the create() method, you print an error message if an SQLException occurs. And having some exception handling is better than just do nothing and ignore the exception. But if you simply print an error message, the code will continue to execute and null will be returned. So that would require adding null checks in your code before you could safely use the Connection object, otherwise a NullPointerException will be thrown and that might obfuscate the real underlying problem (not getting a connection). I think it's safe to say that in this little application it is key to get a connection with the database. If you can't get a connection with the database (for whatever reason), it's useless to continue the program execution (as you won't be able to insert/update/delete a record). Therefore it would be better to throw an unchecked exception from the exception handler, like thisWhen an SQLException occurs during creating an exception, the RuntimeException will propagate to the main() method and you'll know immediately what's wrong (without a NullPointerException being thrown as the program execution will terminate).

3/ Why do you have a cast to Connection in the create() method? The getConnection() already returns a Connection.

4/ You should cleanup the throws clauses of your methods. Many (if not all) of the listed exceptions are never thrown from within these methods.

5/ Why are you using a prepared statement in the insert() and delete() methods, but not in the update() method? You should always use a prepared statement (wherever it is possible).

6/ The exception handlers in the BookDao class will only print the fully qualified name and the message (if any) of the SQLException. The complete stack trace will not be printed and thus some useful information might be lost. Use this exception handler instead to print the complete stack trace and have all possible information available to troubleshoot an issue

Hope it helps!
Kind regards,
Roel
 
Milos Radovic
Greenhorn
Posts: 23
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Roel De Nijs wrote:
That already looks so much better. Well done! Have a cow for all the effort you made to refactor your code.



Thank you very much for your help and support

I think I corrected most of the things that you suggested, but still I don't know how to solve this:

Roel De Nijs wrote:
3/ Why do you have a cast to Connection in the create() method? The getConnection() already returns a Connection.



If I'm right, you are referring to "(Connection)" in the line:

If I delete it the "(Connection)" from the line, my NetBeans shows an error and tells me that I should cast the "(Connection)" back.

Any idea how can I fix this?

Here 's the code:

ConnectionFactory class:



BookDao class:



Main class:



Best regards,
Milos
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Milos Radovic wrote:

Roel De Nijs wrote:
3/ Why do you have a cast to Connection in the create() method? The getConnection() already returns a Connection.



If I'm right, you are referring to "(Connection)" in the line:

If I delete it the "(Connection)" from the line, my NetBeans shows an error and tells me that I should cast the "(Connection)" back.

Any idea how can I fix this?



That shouldn't be happening. It looks like you have imported some different "Connection" type. You didn't post your import statements, but look at them: you should be importing "java.sql.Connection". Sometimes Netbeans will pop up a dialog saying something like "Which of these Connection types would you like to import?" and if you choose the wrong one then this sort of thing can happen.
 
Milos Radovic
Greenhorn
Posts: 23
2
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Paul Clapham wrote:

That shouldn't be happening. It looks like you have imported some different "Connection" type. You didn't post your import statements, but look at them: you should be importing "java.sql.Connection". Sometimes Netbeans will pop up a dialog saying something like "Which of these Connection types would you like to import?" and if you choose the wrong one then this sort of thing can happen.



You are right i have imported "com.mysql.jdbc.Connection;" instead of "java.sql.Connection;"
Thank you

Here is the final code:

ConnectionFactory class:



BookDao class:



Main Class:



Kind regards,
Milos
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Milos Radovic wrote:You are right i have imported "com.mysql.jdbc.Connection;" instead of "java.sql.Connection;"


That was indeed the reason why you had to add the cast to the (wrong) Connection interface.

Milos Radovic wrote:Here is the final code:


You think that is the final code, but it still isn't There are still some improvements which could be made:

1/ You should properly format your code. Properly formatted and indented code is much easier to read.

2/ You should mark the instance variable cf as private (this variable should only be used within the BookDao class) and final (the variable should not be changed once it is initialized)

3/ Although you are using a prepared statement in the update() method, you are not using different placeholders in your query (like in the insert() and delete() methods)

4/ The throws clause of the main() method still has a bunch of exceptions which are not thrown within this method (and thus should be removed).

5/ And maybe you could have a few (private) class (static) variables (constants) with the different queries: e.g. DELETE_QUERY, INSERT_QUERY, and UPDATE_QUERY.

I think once you have incorporated those changes as well, you'll be very close to an A+ for your assigment

Hope it helps!
Kind regards,
Roel
 
Milos Radovic
Greenhorn
Posts: 23
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the help, I have learned so much

Here is the code:

ConnectionFactory class:



BookDao class:



Main class:




Kind regards,
Milos
 
Dave Tolls
Rancher
Posts: 4801
50
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Since ID is an int of some sort (as shown by your INSERT code), then shouldn't it be treated as an int for your DELETE and UPDATE ones as well?
 
Milos Radovic
Greenhorn
Posts: 23
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:Since ID is an int of some sort (as shown by your INSERT code), then shouldn't it be treated as an int for your DELETE and UPDATE ones as well?



You are right, here is the BookDao class with corrections:



Thanks
 
Greenhorn
Posts: 28
1
Eclipse IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Roel De Nijs wrote:5/ And maybe you could have a few (private) class (static) variables (constants) with the different queries: e.g. DELETE_QUERY, INSERT_QUERY, and UPDATE_QUERY.



RE: 5 What are the benefits of having these declared as private static queries instead of initialized inside of each method? I have many, many queries inside each DAO so should I do the same? I have particular ones that aren't just DELETE, INSERT, etc. I have some that return boolean, etc instead of a DTO (data transfer object, I believe that is the correct term). For instance, I have a query that just checks to see if a username is being stored in the database. I return a boolean if rs.next() is true or false (false giving me the okay to user that username). Does it make more sense, then to just use my quick and easy method, or just re-use the general SELECT method and return a "user" object? I guess the more I think about it, when I get that user object I can just call user.getUsername() and if that is == null, then I can use that username...
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Milos Radovic wrote:Thanks for the help, I have learned so much


Glad to hear you have learned so much. That was the true intent of this topic

But again there are still some small improvements which could/should be made:

1/ the class variables XXX_QUERY should be marked final (these variables should not be changed once it is initialized)

2/ in the update query you are still using one actual value (the headline) instead of a placeholder, so you can truely benefit from using prepared statements (I think it's the 2nd or 3rd time I have mentioned this issue)

3/ the indentation / formatting of your code could still be improved a bit

Hope it helps!
Kind regards,
Roel
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dustin Ward wrote:What are the benefits of having these declared as private static queries instead of initialized inside of each method? I have many, many queries inside each DAO so should I do the same?


Assuming these queries inside each DAO are all String literals, there is no real benefit of having these queries defined as class (static) final variables (constants) thanks to how Java handles String literals besides having all queries grouped together at the top of your DAO class.
 
Dave Tolls
Rancher
Posts: 4801
50
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Roel De Nijs wrote:
Assuming these queries inside each DAO are all String literals, there is no real benefit of having these queries defined as class (static) final variables (constants) thanks to how Java handles String literals besides having all queries grouped together at the top of your DAO class.



That's the gain for me.
If there's a change in the table for whatever reason (usually in early development) then you have all the SQL in one spot, rather than sifting through the whole DAO.
It's not a huge gain, but it does help.
 
Milos Radovic
Greenhorn
Posts: 23
2
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Roel De Nijs wrote:
2/ in the update query you are still using one actual value (the headline) instead of a placeholder, so you can truely benefit from using prepared statements (I think it's the 2nd or 3rd time I have mentioned this issue)



You are right, sorry. I think I made the changes this time:

ConnectionFactory class:



BookDao class:



Main class:


Best regards,
Milos
 
Dustin Ward
Greenhorn
Posts: 28
1
Eclipse IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Roel De Nijs wrote:

Dustin Ward wrote:What are the benefits of having these declared as private static queries instead of initialized inside of each method? I have many, many queries inside each DAO so should I do the same?


Assuming these queries inside each DAO are all String literals, there is no real benefit of having these queries defined as class (static) final variables (constants) thanks to how Java handles String literals besides having all queries grouped together at the top of your DAO class.



Dave Tolls wrote:

Roel De Nijs wrote:
Assuming these queries inside each DAO are all String literals, there is no real benefit of having these queries defined as class (static) final variables (constants) thanks to how Java handles String literals besides having all queries grouped together at the top of your DAO class.



That's the gain for me.
If there's a change in the table for whatever reason (usually in early development) then you have all the SQL in one spot, rather than sifting through the whole DAO.
It's not a huge gain, but it does help.



Ahh, thank you both! Ok, I love having more useful / elegant code, so I will implement this soon.
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Milos Radovic wrote:You are right, sorry. I think I made the changes this time:


Looks great! Have another cow for all refactoring effort!
 
Milos Radovic
Greenhorn
Posts: 23
2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Roel De Nijs wrote:
Looks great! Have another cow for all refactoring effort!



Thank you again for your help

Best regards,
Milos
reply
    Bookmark Topic Watch Topic
  • New Topic