TOTD #38: Creating a MySQL Persistence Unit using NetBeans IDE


This TOTD (Tip Of The Day) shows how to create a Persistence Unit (PU) for a MySQL database using NetBeans IDE. This PU can then be used in any of Java EE artifacts (JSP, Servlet, EJB, ...) for database interaction.
  1. In NetBeans IDE, create a new project
    1. Create a new NetBeans Web project and enter the values ("Autocomplete") as shown:



      and click on "Next".
    2. Choose GlassFish v2 as the deployment server and then click on "Finish".
  2. Set up the database
    1. Start the database as:

      ~ >sudo mysqld_safe --user root
      Password:<YOUR PASSWORD>
      Starting mysqld daemon with databases from /usr/local/mysql/data

    2. Create a user, create the database and grant the privileges to newly created user as:

      mysql> CREATE USER duke IDENTIFIED by 'duke';
      Query OK, 0 rows affected (0.00 sec)

      mysql> create database states;
      Query OK, 1 row affected (0.00 sec)

      mysql> GRANT ALL on states.\* TO duke;
      Query OK, 0 rows affected (0.00 sec)
    3. In NetBeans IDE, Services panel, right-click on Databases and click on "New Connection..." and enter the values as shown:



      and click on "OK" and again on "OK".
    4. Right-click on the newly created database and select "Execute Command ..." as shown:


    5. Create the database table as:

      CREATE TABLE STATES (
            id INT,
            abbrev VARCHAR(2),
            name VARCHAR(50),
            PRIMARY KEY (id)
      );

      and click on the green button to run the query as shown here:


    6. Following the same instructions, populate the table using the following SQL:

      INSERT INTO STATES VALUES (1, "AL", "Alabama");
      INSERT INTO STATES VALUES (2, "AK", "Alaska");
      INSERT INTO STATES VALUES (3, "AZ", "Arizona");
      INSERT INTO STATES VALUES (4, "AR", "Arkansas");
      INSERT INTO STATES VALUES (5, "CA", "California");
      INSERT INTO STATES VALUES (6, "CO", "Colorado");
      INSERT INTO STATES VALUES (7, "CT", "Connecticut");
      INSERT INTO STATES VALUES (8, "DE", "Delaware");
      INSERT INTO STATES VALUES (9, "GL", "Florida");
      INSERT INTO STATES VALUES (10, "GA", "Georgia");
      INSERT INTO STATES VALUES (11, "HI", "Hawaii");
      INSERT INTO STATES VALUES (12, "ID", "Idaho");
      INSERT INTO STATES VALUES (13, "IL", "Illinois");
      INSERT INTO STATES VALUES (14, "IN", "Indiana");
      INSERT INTO STATES VALUES (15, "IA", "Iowa");
      INSERT INTO STATES VALUES (16, "KS", "Kansas");
      INSERT INTO STATES VALUES (17, "KY", "Kentucky");
      INSERT INTO STATES VALUES (18, "LA", "Louisiana");
      INSERT INTO STATES VALUES (19, "ME", "Maine");
      INSERT INTO STATES VALUES (20, "MD", "Maryland");
      INSERT INTO STATES VALUES (21, "MA", "Massachussetts");
      INSERT INTO STATES VALUES (22, "MI", "Michigan");
      INSERT INTO STATES VALUES (23, "MN", "Minnesota");
      INSERT INTO STATES VALUES (24, "MS", "Mississippi");
      INSERT INTO STATES VALUES (25, "MO", "Missouri");
      INSERT INTO STATES VALUES (26, "MT", "Montana");
      INSERT INTO STATES VALUES (27, "NE", "Nebraska");
      INSERT INTO STATES VALUES (28, "NV", "Nevada");
      INSERT INTO STATES VALUES (29, "NH", "New Hampshire");
      INSERT INTO STATES VALUES (30, "NJ", "New Jersey");
      INSERT INTO STATES VALUES (31, "NM", "New Mexico");
      INSERT INTO STATES VALUES (32, "NY", "New York");
      INSERT INTO STATES VALUES (33, "NC", "North Carolina");
      INSERT INTO STATES VALUES (34, "ND", "North Dakota");
      INSERT INTO STATES VALUES (35, "OH", "Ohio");
      INSERT INTO STATES VALUES (36, "OK", "Oklahoma");
      INSERT INTO STATES VALUES (37, "OR", "Orgeon");
      INSERT INTO STATES VALUES (38, "PA", "Pennsylvania");
      INSERT INTO STATES VALUES (39, "RI", "Rhode Island");
      INSERT INTO STATES VALUES (40, "SC", "South Carolina");
      INSERT INTO STATES VALUES (41, "SD", "South Dakota");
      INSERT INTO STATES VALUES (42, "TN", "Tennessee");
      INSERT INTO STATES VALUES (43, "TX", "Texas");
      INSERT INTO STATES VALUES (44, "UT", "Utah");
      INSERT INTO STATES VALUES (45, "VT", "Vermont");
      INSERT INTO STATES VALUES (46, "VA", "Virginia");
      INSERT INTO STATES VALUES (47, "WA", "Washington");
      INSERT INTO STATES VALUES (48, "WV", "West Virignia");
      INSERT INTO STATES VALUES (49, "WI", "Wisconsin");
      INSERT INTO STATES VALUES (50, "WY", "Wyoming");
  3. Create and configure the persistence unit
    1. Right-click on the newly created project and select "New", "Entity Classes from Database ..." as shown:


    2. In DataSource, select "New Data Source..." and enter the JNDI name "jndi/states" as shown:


    3. Select "STATES" table in "Available Tables:" and click on "Add >" and then "Next >".
    4. Click on "Create Persistence Unit ...", take all the defaults and click on "Create".
    5. Enter the package name as "server" and click on "Finish".
    6. Expand "Configuration File", open "persistence.xml". Unselect "Include All Entity Classes" check box, click on "Add Class...", select "server.States" and click on OK. The updated view looks like:

    7. Select the XML view and replace <properties/> with

          <properties>
              <property name="toplink.jdbc.user" value="duke"/>
              <property name="toplink.jdbc.password" value="duke"/>
          </properties>

      The username and password values must match the ones specified during database creation. The updated "persistence.xml" looks like:

      <?xml version="1.0" encoding="UTF-8"?>
      <persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
        <persistence-unit name="AutocompletePU" transaction-type="JTA">
          <jta-data-source>jndi/states</jta-data-source>
          <class>server.States</class>
          <exclude-unlisted-classes>true</exclude-unlisted-classes>
          <properties>
            <property name="toplink.jdbc.user" value="duke"/>
            <property name="toplink.jdbc.password" value="duke"/>
          </properties>
        </persistence-unit>
      </persistence>
  4. Create a Servlet to perform the database operations
    1. Right-click on the project, select "New", "Servlet".
    2. Enter the class name as "StatesServlet" and package as "server" and click on "Finish".
    3. Add the following fragment in the beginning of the class:

          EntityManager em;

          @Override
          public void init() throws ServletException {
              EntityManagerFactory emf = Persistence.createEntityManagerFactory("AutocompletePU");
              em = emf.createEntityManager();
          }

      Alternatively, you can use resource injection to populate the EntityManager. Use the following fragment, instead of the above, to achieve that:

          @PersistenceContext(unitName="AutocompletePU")
          EntityManager em;
    4. Replace the commented code in "processRequest" with the following fragment:

                  String abbrev = request.getParameter("abbrev");

                  List<States> list = em.createNamedQuery("States.findByAbbrev").
                          setParameter("abbrev", abbrev).
                          getResultList();

                  if (list.size() > 0) {
                      States s = list.get(0);
                      out.println("Found " + s.getName() + " with abbrev \\"" + abbrev + "\\"");
                  } else {
                      out.println("No matching state found with \\"" + abbrev + "\\"");
                  }

      and fix the imports by right-clicking in editor pane and selecting "Fix Imports".
    5. Right-click on the project and select "Undeploy and Deploy".
Now let's try it!

Invoking "curl http://localhost:8080/Autocomplete/StatesServlet?abbrev=CA" shows the following output on command prompt:

Found California with abbrev "CA"

Alternatively, you can enter this URL in browser as well to see the output as:



Invoking "http://localhost:8080/Autocomplete/StatesServlet?abbrev=CB" shows the output:



Even though MySQL is used as the database in this case, any other database can be easily used for creating this portable PU.

Please leave suggestions on other TOTD (Tip Of The Day) that you'd like to see. A complete archive of all tips is available here.

Technorati: totd mysql jpa persistenceunit netbeans glassfish
Technorati: totd mysql jpa persistenceunit netbeans glassfish jquery autocomplete
Comments:

Do you have to have the userid/password in the persistence.xml file? What happens if the userid/password changes from environment to environment (i.e. development to production)? Cannot we use a jndi datasource and not worry about the connection details? How would that look in the persistence.xml?

Posted by Jason Kilgrow on July 27, 2008 at 09:51 PM PDT #

Jason, http://davidwburns.wordpress.com/2008/03/13/how-to-use-a-jndi-datasource-with-jpa-in-netbeans-601/ provides some more details on the topic of your interest.

Posted by Arun Gupta on July 28, 2008 at 03:43 AM PDT #

Never mind, I just looked at the
servlet code again. The list created is
based on the findByAbbrev query...
Sorry.

John

Posted by John Gregory on July 28, 2008 at 07:50 PM PDT #

[Trackback] There are several JavaScript libraries that can be embedded in your web application to create a visually appealing interface. Script.aculo.us is one of the popular ones and is built on the Prototype JavaScript Framework. The library provides an easy-to...

Posted by Arun Gupta's Blog on July 29, 2008 at 02:30 AM PDT #

This is fine!

Posted by guest on August 18, 2008 at 03:30 PM PDT #

[Trackback] This TOTD (Tip Of The Day) shows how to create a simple Java Server Faces application using NetBeans IDE 6.1. This is my first ever Java Server Faces application :) Much more comprehensive applications are already available in NetBeans and...

Posted by Arun Gupta's Blog on August 19, 2008 at 11:20 PM PDT #

I have created a new table using the execute command utility for a PostgreSQL data base. The table got created in PostgreSQL data base.
But it is not being viewed in Databases -> Mydatabase-> tables under Services tab.Due to this i am not able to create the persistence unit.please help

Posted by Karthik on August 20, 2008 at 09:44 PM PDT #

Karthik,

I've not tried PostgreSQL within NetBeans. However I found the blog entry at:

http://blogs.sun.com/phantom/entry/solaris_postgresql_and_netbeans_perfect

that describes a similar setup. Otherwise, please post your question to nbusers@netbeans.org for

Archives of the alias are available at:

http://www.netbeans.org/servlets/SummarizeList?listName=nbusers

Posted by Arun Gupta on August 21, 2008 at 08:57 AM PDT #

I'm having a little trouble with this example. On this line of code:

States s = list.get(0);

I'm getting a ClassCastException. I'm wondering if somehow I'm using two different class loaders. Does anyone have an idea what I could be doing wrong?

Thanks!

Posted by Carol on October 08, 2008 at 06:48 AM PDT #

Carol, I've seen it sometimes. The error disappeared if I re-create the project following exactly the same steps of process.

Posted by Arun Gupta on October 08, 2008 at 10:35 AM PDT #

[Trackback] I presented on "Creating powerful web applications using GlassFish, MySQL and NetBeans/Eclipse" as the first talk of FISL 10 yesterday. The room was only partial full being the first talk of FISL but got packed towards the middle so...

Posted by Arun Gupta's Blog on June 24, 2009 at 01:57 PM PDT #

[Trackback] I presented on "Creating powerful web applications using GlassFish, MySQL and NetBeans/Eclipse" as the first talk of FISL 10 yesterday. The room was only partial full being the first talk of FISL but got packed towards the middle so...

Posted by Arun Gupta's Blog on June 24, 2009 at 02:01 PM PDT #

[Trackback] TOTD #86 explained how to get started with deploying a Apache Wicket application on GlassFish. This Tip Of The Day (TOTD) will show how to add pagination to your Wicket application. The blog entry "JPA/Hibernate and Wicket Repeating Views...

Posted by Arun Gupta's Blog on August 04, 2009 at 11:14 PM PDT #

[Trackback] NetBeans 6.8 M1 introduces support for creating Java EE 6 applications ... cool! This Tip Of The Day (TOTD) shows how to create a simple web application using JPA 2.0 and Servlet 3.0 and deploy on GlassFish v3 latest...

Posted by Arun Gupta's Blog on August 12, 2009 at 10:42 PM PDT #

[Trackback] NetBeans 6.8 M1 introduces support for creating Java EE 6 applications ... cool! This Tip Of The Day (TOTD) shows how to create a simple web application using JPA 2.0 and Servlet 3.0 and deploy on GlassFish v3 latest...

Posted by Arun Gupta's Blog on August 13, 2009 at 03:58 AM PDT #

Post a Comment:
Comments are closed for this entry.
About

profile image
Arun Gupta is a technology enthusiast, a passionate runner, author, and a community guy who works for Oracle Corp.


Java EE 7 Samples

Stay Connected

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today