how to connect to SQL Server from JSF

Question

I have created a simple JSF application, now must to connect to SQL Server and perform CRUD operations on those tables from database.

I was a .NET programmer and i don't know how to connect to SQL Server from JSF. I have read something about JNDI, but not understood well. My questions are:

• where should JNDI be defined: on Tomcat or my application?
• where to define the connection string?
• which driver/jar should be used?

Can you recommend any code samples, links to tutorials how to perform crud operations, or any other guidance?

Answer 1

This is a very broad question. I will take a shot at trying too keep it simple and short.

Here are the steps.

1. First create a backing bean that works with your front end face page.
2. Create a service class that encapsulates the CRUD tasks.
3. Create a database methods class that performs each CRUD task.

This is how the code should flow:

"Your UI face invokes a method in the backing bean->the backing bean invokes service class->service invokes the database methods class. This is commonly referred to as the DAO pattern."

For details on how to connect to a database.

1. You can either create a local data source and connect via standard JDBC procedure.
2. Or you can create connection pools in your container (JBOSS, WebLogic, etc). Then look up those connection pools in your app via JNDI lookup.

If you are very new to this, then I would recommend to start out with creating a basic database connection using JDBC and running your queries against it. In the long term, you will want to get yourself familiar with connection pool (actually this will give you a better performance too), Spring JDBC framework, ORM support (hibernate, iBatis).

Here is a link to starting a jdbc connection for Microsoft SQL server (example on step1).

Answer 2

• where should JNDI be defined: on Tomcat or my application?

In the JNDI container. That's thus Tomcat.

• where to define the connection string?

In the JNDI container. In case of Tomcat, that'll go in the context.xml. You can either modify Tomcat's own context.xml or supply your own in META-INF folder of your webapp. More details can be found in the Tomcat JNDI resources HOW-TO.

• which driver/jar should be used?

The one which can communicate with the DB in question. In case of Microsoft SQL Server, that's under each the DB-vendor provided JDBC driver or the performancetechnically better jTDS driver.

Here are some useful tutorials which might help you step by step further:

• Basic DAO tutorial (with JDBC) - Using in JSF
• CRUD with JSF 2.0
• JPA tutorial (better than JDBC)
• Netbeans CRUD with JSF 2.0 and JPA 2.0