Arun Gupta, Miles to go ...

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

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