Posts Tagged ‘MySQL’

Database modeling with Microsoft Visio for an Existing MySQL Database

April 18th, 2010

In a SDLC, creating a database model diagram is an important step that needs to be taken to achieve the following

- Create a clear visualization of the Database for the system which makes the database understanding pretty easier

- It helps in derive out the classes required to be created by just looking into the database modeling diagram in depth

If you are not able to buy the paid tool for UML modeling such as rational rose etc., it’s still achievable through Microsoft Visio. Following are the steps that need to be performed to create a model diagram from an existing MySQL database in Microsoft Visio which is called as Reverse Engineering

- First of all, download the ODBC driver for MySQL from the location http://dev.mysql.com/downloads/connector/odbc/5.1.html. Download the installable by selecting the appropriate one based on  your machine configuration (32 bit or 64 bit)

- Install the mysql connector

- After installation, open visio

- Go to File->New->Database->Database Model

- From Database Menu, select Reverse Engineer

- On the Create New Datasource screen displayed, click on User Data Source

- Provide a datasource name

- Select the mysql connector driver, click on Next

- Click Finish and your new datasource would be added and would come as selected

- Click on Next

- Enter the valid username and password to create the connection using the datasource

- Select Object Type to reverse engineers

- You would be asked to select the database

- On selecting database, tables would be displayed

- Select the tables you want to create the datamodel for

- On selecting the tables, you would be asked to create the model with shape or without shape

- I select the shaped one and the model is generated

It’s really a helpful tool for the beginner to start creating the Database Modeling Diagram from an existing Database

Dynamic JSF Application with MySql on JBoss 4.2.2 GA

March 21st, 2010

I have created a small JSF application (Employer Management) with MySQL as database on JBoss 4.2.2 GA in Netbeans 6.8.

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}/server/default/lib. You don’t need to have the driver in your classpath.

2) Go to  {INSTALL DIR}/docs/examples, copy the mysql-ds.xml and copy it to {INSTALL DIR}/server/default/deploy. Change the database settings as per your configuration

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

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

5) Create a Managed Bean called “Employer” with member variables (id, firstname, lastname,phone and email) and corresponding getter and setter methods

6) Create a Backing Bean called EmployerFormBean with two variables

a) Employer

b) EmployerList

7) Create the getter setter for the above two variables and initialize the employerList by calling the getEmployerList function in EmployerDAO which is discussed later

8) Add method called addEmployer with its code as follows

EmployerDAO empDAO = new EmployerDAO();
empDAO.insertData(this.getEmployer());
return “go_insert_data”;

9) The configuration for Managed Bean, Backing Bean and “go_insert_data” should be made in faces-config.xml

<managed-bean>
<managed-bean-name>employerBean</managed-bean-name>
<managed-bean-class>com.testjsf.EmployerFormBean</managed-bean-class>
<managed-bean-scope>request</managed-bean-scope>
<managed-property>
<property-name>employer</property-name>
<property-class>com.domain.Employer</property-class>
<value>#{employer}</value>
</managed-property>
</managed-bean>
<managed-bean>
<managed-bean-name>employer</managed-bean-name>
<managed-bean-class>com.domain.Employer</managed-bean-class>
<managed-bean-scope>none</managed-bean-scope>
</managed-bean>

<from-view-id>/employer.jsp</from-view-id>
<navigation-case>
<from-outcome>go_insert_data</from-outcome>
<to-view-id>/employerlist.jsp</to-view-id>
</navigation-case>

10) As you see, the database operations are performed inside DAO. The method insertData would be as follows

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

11) While doing the JNDI lookup in JBoss call it as “java:/{dsname}” as defined in mysql-ds.xml

12) After insert, populate the employerList again in the backing bean before redirecting

12) Similarly create the getEmployerList function to get the list of employers

13) Now create two jsp

a) employer.jsp where form to insert data for the employer. The fields here are bind to the employer object in employerbean. The structure is as

<h:form id=”EmployerForm”>
<h:outputLabel value=”#{msg.firstname}”/> <h:inputText value=”#{employerBean.employer.firstname}”/>
<h:outputLabel value=”#{msg.lastname}”/> <h:inputText value=”#{employerBean.employer.lastname}”/>
<h:outputLabel value=”#{msg.phone}”/> <h:inputText value=”#{employerBean.employer.phone}”/>
<h:outputLabel value=”#{msg.email}”/> <h:inputText value=”#{employerBean.employer.email}”/>
<h:commandButton action=”#{employerBean.addEmployer}” value=”Submit”/>
<h:commandLink action=”show_emp_data” value=”Show all employers”/>
</h:form>

b) employerlist.jsp To display the list of employers. In this use the dataTable concept of JSF which is as follows

<h:form>
<h:dataTable value=”#{employerBean.empList}” var=”item”>
<h:column>
<f:facet name=”header”>
<h:outputText value=”Employee No.”/>
</f:facet>
<h:outputText value=”#{item.id}”/>
</h:column>
<h:column>
<f:facet name=”header” >
<h:outputText value=”First Name”/>
</f:facet>
<h:outputText value=”#{item.firstname}”/>
</h:column>
<h:column>
<f:facet name=”header” >
<h:outputText value=”Last Name”/>
</f:facet>
<h:outputText value=”#{item.lastname}”/>
</h:column>
<h:column>
<f:facet name=”header” >
<h:outputText value=”Email”/>
</f:facet>
<h:outputText value=”#{item.email}”/>
</h:column>
<h:column>
<f:facet name=”header” >
<h:outputText value=”Phone”/>
</f:facet>
<h:outputText value=”#{item.phone}”/>
</h:column>

</h:dataTable>
</h:form>

16) Compile the code and run employer.jsp. On successful insertion, employerlist with newly inserted record would be displayed.