Skip to main content

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

Posted by arungupta on July 25, 2008 at 6:02 AM PDT



This TOTD (Tip
Of style="font-weight: bold;">The style="font-weight: bold;">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:



      src="http://blogs.sun.com/arungupta/resource/images/ps-new-project-name.png">



      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:


      style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
      cellpadding="2" cellspacing="2">
      ~ >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:


      style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
      cellpadding="2" cellspacing="2">
      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:



      src="http://blogs.sun.com/arungupta/resource/images/ps-mysql-db-connection.png">



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



      src="http://blogs.sun.com/arungupta/resource/images/ps-mysql-db-explorer.png">
    5. Create the database table as:


      style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
      cellpadding="2" cellspacing="2">
      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:



      src="http://blogs.sun.com/arungupta/resource/images/ps-mysql-db-run-command.png">

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


      style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
      cellpadding="2" cellspacing="2">
      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:



      src="http://blogs.sun.com/arungupta/resource/images/ps-pu-entity-classes-from-db.png">
    2. In DataSource, select "New Data Source..." and enter the
      JNDI name "jndi/states" as shown:



      src="http://blogs.sun.com/arungupta/resource/images/ps-pu-jndi-name.png">
    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:



      src="http://blogs.sun.com/arungupta/resource/images/ps-pu-include-entity-class.png">
    7. Select the XML view and replace
      <properties/> with


      style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
      cellpadding="2" cellspacing="2">
         
      <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:


      style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
      cellpadding="2" cellspacing="2">
      <?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:


      style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
      cellpadding="2" cellspacing="2">
          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:


      style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
      cellpadding="2" cellspacing="2">
         
      @PersistenceContext(unitName="AutocompletePU")

          EntityManager em;
    4. Replace the commented code in "processRequest" with the
      following fragment:


      style="text-align: left; background-color: rgb(204, 204, 255); width: 100%;"
      cellpadding="2" cellspacing="2">
                 
      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:



src="http://blogs.sun.com/arungupta/resource/images/mysql-pu-output-ca.png">



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



src="http://blogs.sun.com/arungupta/resource/images/mysql-pu-output-cb.png">



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 href="http://blogs.sun.com/arungupta/tags/totd">here.




Technorati: totd
mysql href="http://technorati.com/tag/jpa">jpa href="http://technorati.com/tag/persistenceunit">persistenceunit
netbeans
glassfish



Technorati: totd
mysql href="http://technorati.com/tag/jpa">jpa href="http://technorati.com/tag/persistenceunit">persistenceunit
netbeans
glassfish
jquery
autocomplete

Related Topics >>