This is a small proof of concept that we have done for creating the JDBC connection with MySql Database and Glassfish Application Server. This is performed on Netbeans 6.8 which seems to be working best for our team.
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}/glassfish/domains/domain1/lib/ext. You don’t need to have the driver in your classpath.
2) Go to the services and (re)start the Application Server
3) Right click on Server Name and click on View Admin Console
4) After admin console loads, click on Resources in left nav, JDBC resources and connection pools will be displayed
5) Create a Connection pool with resource type as javax.sql.DataSource and Datasource class name as com.mysql.jdbc.jdbc2.optional.MysqlDataSource ( This will be present in jar copied in step 1). Under additional attributes, add user,password, databaseName.
6) Always keep the ping enabled to check the connectivity
7) On Save, ping would be executed. Based on the status (Success or failure) message would be displayed
If the ping is successful, create a JDBC resource with name as jdbc/{yourname}. In glassfish always give jdbc name starting with ‘jdbc/’.
9) Come back to netbeans, create a new project under category “Java Web”
10) In the Source Package Folder, create a package
11) Right click on package name and select new “Servlet”
12) After the servlet is created, in the try block within doProcess method, paste the following code
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup(”jdbc/{yourjdbcname}”);
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
13) The above code gets the datasource connection in the JDBC pool created from Admin console
14) Execute a query which in my case is
ResultSet rs = stmt.executeQuery(”select * from person”);
out.println(”<html>”);
out.println(”<head>”);
out.println(”<title>Servlet POC on Glassfish</title>”);
while(rs.next())
{
out.println(”<h1>First Name is ” + rs.getString(”firstname”) + “</h1>”);
out.println(”<h1>Last Name is ” + rs.getString(”lastname”) + “</h1>”);
}
out.println(”</body>”);
out.println(”</html>”);
stmt.close();
stmt=null;
conn.close();
conn=null;
15) Always close the connections at the end
16) Compile the code and run it. The result would be displayed
