JDBC Connection with Glassfish and MySQL on Netbeans 6.8

March 19th, 2010 by Usha Informatique Team Leave a reply »

This is a small proof of concept that we have done for creating the JDBC connection with MySql Database and  Glassfish Application Server. This is performed on Netbeans 6.8 which seems to be working best for our team.

These are the steps that need to be followed

1) Copy the mysql driver class (mysql-connector-java-5.1.6-bin.jar) which comes with Netbeans and located under {INSTALL DIR}/NetBeans 6.8/ide12/modules/ext to {INSTALL DIR}/glassfish/domains/domain1/lib/ext. You don’t need to have the driver in your classpath.

2) Go to the services and (re)start the Application Server

3) Right click on Server Name and click on View Admin Console

4) After admin console loads, click on Resources in left nav, JDBC resources and connection pools will be displayed

5) Create a Connection pool with resource type as javax.sql.DataSource and Datasource class name as com.mysql.jdbc.jdbc2.optional.MysqlDataSource ( This will be present in jar copied in step 1). Under additional attributes, add user,password, databaseName.

6) Always keep the ping enabled to check the connectivity

7) On Save, ping would be executed. Based on the status (Success or failure) message would be displayed

8) If the ping is successful,  create a JDBC resource with name as jdbc/{yourname}. In glassfish always give jdbc name starting with ‘jdbc/’.

9) Come back to netbeans, create a new project under category “Java Web”

10) In the Source Package Folder, create a package

11) Right click on package name and select new “Servlet”

12) After the servlet is created, in the try block within doProcess method, paste the following code

InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup(”jdbc/{yourjdbcname}”);

Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();

13) The above code gets the datasource connection in the JDBC pool created from Admin console

14) Execute a query which in my case is

ResultSet rs = stmt.executeQuery(”select * from person”);

out.println(”<html>”);
out.println(”<head>”);
out.println(”<title>Servlet POC on Glassfish</title>”);

while(rs.next())
{
out.println(”<h1>First Name is ” + rs.getString(”firstname”) + “</h1>”);
out.println(”<h1>Last Name is ” + rs.getString(”lastname”) + “</h1>”);
}
out.println(”</body>”);
out.println(”</html>”);

stmt.close();
stmt=null;
conn.close();
conn=null;

15) Always close the connections at the end

16) Compile the code and run it. The result would be displayed

Advertisement

47 comments

  1. You got a really useful blog I have been here reading for about an hour. I am a newbie and your success is very much an inspiration for me.

  2. Thank you for your article. Really Cool.

  3. Mariana says:

    Bookmarked, i will add your homepage to my toolbar

  4. cpalead says:

    I have been searching this for a long time, thanks for posting

  5. crenFlels says:

    Just want to say what a great blog you got here!
    I’ve been around for quite a lot of time, but finally decided to show my appreciation of your work!

    Thumbs up, and keep it going!

    Cheers
    Christian, iwspo.net

  6. p.s. You have a excellent template . Where did you find this?

  7. crenFlels says:

    Just want to say what a great blog you got here!
    I’ve been around for quite a lot of time, but finally decided to show my appreciation of your work!

    Thumbs up, and keep it going!

    Cheers
    Christian,Diet Guide!

  8. Mike Hellweg says:

    I randomly browse blogs on the internet, and I find your article to be very informational. I’ve already bookmark it on my browser, so that I can view your blog publish once more later. Additionally, I am wondering whether your weblog is open for link change, as I really need to change hyperlinks with you. I do not normally do that, but I hope that we are able to have a mutual link exchange. Let me know and have a fantastic day!

  9. crenFlels says:

    Just want to say what a great blog you got here!
    I’ve been around for quite a lot of time, but finally decided to show my appreciation of your work!

    Thumbs up, and keep it going!

    Cheers
    Christian,Earn Free Vouchers / Cash

  10. I really liked your blog! It helped me alot…

  11. jet says:

    im getting error in:

    InitialContext ctx = new InitialContext();
    DataSource ds = (DataSource) ctx.lookup(”jdbc/{yourjdbcname}”);

    Statement stmt = conn.createStatement();

    i add this:
    import java.sql.Connection;
    import java.sql.Statement;
    import javax.naming.*;

    error:
    1. must be caught or declared to be thrown.(initialContext and Statement)
    2.illegal start of expression not a statement, \8221 (DataSource)

  12. Did you put it in try catch block? Please compare with the following below

    Connection conn = null;
    PreparedStatement stmt = null;
    try {

    InitialContext ctx = new InitialContext();
    DataSource ds = (DataSource) ctx.lookup(”jdbc/jsfperson”);
    //DataSource ds = (DataSource) ctx.lookup(”java:/MySqlDS”);
    conn = ds.getConnection();
    String query = “insert into employer(firstname,lastname,email,phone) values (?,?,?,?)”;
    //String query = “insert into employer(firstname,lastname,email,phone) values (’Mayank’,'Singhai’,'123456′,’abc@yahoo.com’)”;
    stmt = conn.prepareStatement(query);
    stmt.setString(1, emp.getFirstname());
    stmt.setString(2, emp.getLastname());
    stmt.setString(3, emp.getEmail());
    stmt.setString(4, emp.getPhone());
    stmt.executeUpdate();
    } catch (Exception e) {
    e.printStackTrace();
    } finally {
    stmt.close();
    conn.close();
    }

    with imports as below
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.util.ArrayList;
    import javax.naming.InitialContext;
    import javax.sql.DataSource;

  13. jet says:

    I carefully review all the steps, pinged successfully but still no luck in my codes…

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    //response.setContentType(”text/html;charset=UTF-8″);
    //PrintWriter out = response.getWriter();

    Connection conn = null;
    PreparedStatement stmt = null;
    try {
    InitialContext ctx = new InitialContext();
    DataSource ds = (DataSource) ctx.lookup(”jdbc/schema_db”);
    //DataSource ds = (DataSource) ctx.lookup(”java:/MySqlDS”);
    conn = ds.getConnection();

    } finally {
    stmt.close();
    conn.close();
    }
    }

    still the same error, since yesterday…

  14. I found your blog on google and read a few of your other posts. I just added you to my Google News Reader. Keep up the good work Look forward to reading more from you in the future.

  15. jet says:

    guys, I was able to solve some errors… now the only error left is:

    DataSource ds = (DataSource) ctx.lookup(”jdbc/schema_db”);

    cannot find symbol:
    symbol: variable schema_db
    location: class.MyClass.connect

  16. This Blog is very nice.

  17. Great Blog! I have a similar site, and get a lot of spam. Do you face such problems? Can you please recommend some steps I take to combat spam. Thanks.

  18. Paris Rasole says:

    well done!nice job!

  19. Interesting, your’s is the second site today that I’ve visited with this opportunity. I’ll keep an eye on it.

  20. Jesse Inbody says:

    Some of the pictures were not showing correctly but, the site still looks great. I have been coming to this blog for a few months now and i’m really impressed with the content. What is the rss address?

  21. IM Elite says:

    Awesome article which has got me considering about the potential of this concept. Truly genuinely remarkable.

  22. I never, ever imagined I would have to know this thank goodness for the internet, right?

  23. Excellent writing about Blog health, it is actually useful for me. keep writing and happy blogging.

  24. I would like to thank you for the endeavors you have made in publishing this article. I am trusting the same best work from you in the future as well. In fact your fanciful writing abilities has inspired me to start my own blog now. Genuinely the blogging is spreading its wings rapidly. Your write up is a fine example of it.Thanks again for sharing this free online

  25. Hi, You just have to subscribe from the RSS link in the top navigation

  26. Great blog. Thank you!

  27. vemmahb says:

    I’ve been visiting your blog for a while now and I always find a gem in your new posts. Thanks for sharing.

  28. I agree with the author that we need to share the knowledge we gain!. . . . . .

  29. This is my first time i visit here. I found so many entertaining stuff in your blog, especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the leisure here! Keep up the excellent work.

  30. Easily, the post is actually the greatest on this deserving topic. I agree with your conclusions and will thirstily look forward to your coming updates. . . . .

  31. I’ve read some good stuff here. Definitely worth bookmarking for revisiting.

  32. Excellent read, I just passed this onto a colleague who was doing a little research on that. And he actually bought me lunch because I found it for him smile So let me rephrase that: Thanks for lunch!

  33. wow, nice post, I was wondering how to cure acne naturally. and found your site by yahoo, many userful stuff here, now i have got some idea. I’ve bookmark your site and also add rss. keep us updated.

  34. Sexteen says:

    Just wanted to say this blog is kinda awesome, bye, Sexteen

  35. scuba diving says:

    Very Interesting Information! Thank You For Thi Blog!

  36. diving says:

    You certainly deserve a round of applause for your post and more specifically, your blog in general. Very high quality material

  37. my bookmarks says:

    Nice intriguing article. It provides informative insights to the blog readers like me.

  38. you really get some great content here. Thanks

  39. A thoughtful insight and ideas I will use on my blog. You’ve obviously spent some time on this. Well done!

  40. It¡¯s posts like this that keep me coming back and checking this site regularly, thanks for the info!

  41. Took me awhile to read all the comments, but I really enjoyed the article. It proved to be very useful to me and I am sure to all the commenters here! It’s always nice when you can not only be informed, but also engaged! I’m sure you had fun writing this article.

  42. gry says:

    Very informative post. Thanks for taking the time to share your view with us.

  43. Thank you for another informative blog. Where else could I get that kind of info written in such a perfect way? I have a project that I am just now working on, and I have been on the look out for such information.

  44. thanks for the post. I am very happy to read this post. . . . .

  45. Its very informative and interesting article.all the points are very useful. Simple but very effective writing. Thanks for sharing such a nice post.

  46. Thanks for good article. Hope to see more soon. . . . .

  47. Sorry for going offtopic, but where did you get this theme?