Arun Gupta, Miles to go ...

Arun Gupta is a technology enthusiast, a passionate runner, and a community guy who works for Sun Microsystems.

« Previous day (Jul 24, 2008) | Main | Next day (Jul 26, 2008) »

Friday July 25, 2008

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.

In NetBeans IDE, create a new project

Create a new NetBeans Web project and enter the values ("Autocomplete") as shown:

and click on "Next".
Choose GlassFish v2 as the deployment server and then click on "Finish".

Set up the database

Start the database as:

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

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)

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".
Right-click on the newly created database and select "Execute Command ..." as shown:
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:

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

Create and configure the persistence unit

Right-click on the newly created project and select "New", "Entity Classes from Database ..." as shown:
In DataSource, select "New Data Source..." and enter the JNDI name "jndi/states" as shown:
Select "STATES" table in "Available Tables:" and click on "Add >" and then "Next >".
Click on "Create Persistence Unit ...", take all the defaults and click on "Create".
Enter the package name as "server" and click on "Finish".
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:

Select the XML view and replace <properties/> with

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>

Create a Servlet to perform the database operations

Right-click on the project, select "New", "Servlet".
Enter the class name as "StatesServlet" and package as "server" and click on "Finish".

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;

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

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

Posted by Arun Gupta in web2.0 | Comments[15]