GlassFish JDBC Connection Pooling

Question

I am trying to make a connection pooling for my web application but don't know basics how to setup with sql server with it

Main questions: what is resource type (What to write), classname, driverclass name?

public static void main(String[] args) throws ClassNotFoundException, SQLException {
    String str="SELECT * from Book";
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        try {
            Connection con = DriverManager.getConnection("jdbc:odbc:lol","Sar\\KILLER_GUY",null);
            Statement stmt=con.createStatement();
            ResultSet rs=stmt.executeQuery(str);
            System.out.println("SalesPerson Id: ");
            while(rs.next())
            {
                String id= rs.getString("Add");
            System.out.println(id);
            }
            con.close();
        }
    catch(SQLException e)
    {

    }

Answer 1

You need to set up a connection pool in GlassFish, then access the connection pool data source via JNDI (Java Naming and Directory Interface - the Java API for directory services). See this step by step tutorial, which gives details of the following outline:

1. Open List of JDBC Connection Pools

   In the Glassfish admin panel, open the list of JDBC Connections Pools using the navigation menu on the left side of the page (Resources->JDBC->Connections Pools). In the JDBC Connections Pools, click New... to create a new JDBC Connection Pool.

2. Create a New JDBC Connection Pool

   In the New JDBC Connection Pool dialog, specify a name for the pool. Be sure to specify the correct "Resource Type" and click Next.

3. Specify Datasource Classname (com.microsoft.sqlserver.jdbc.SQLServerDataSource)

   On the next page, specify the Datasource Classname. The class name is of course driver specific. Consult your driver documentation to find the right class . Click Finish to create the JDBC Connection Pool.

4. Edit the Connection Pool

   Once the JDBC Connection Pool has been created, you can go back and add/edit properties such as the JDBC Connection URL, username, password, etc.

5. Create a JDBC Resource

   Finally, you will need to create a new JDBC Resource (Resources->JDBC->JDBC Resources) that you can reference in your web application.

6. Create a Connection

   Once you have created a connection pool, you can use it in your web app (e.g. servlet or JSP) like this:

// Look up the connection pool data source
javax.naming.InitialContext ctx = new javax.naming.InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup("Your JNDI Name here");

// Get a database connection
java.sql.Connection conn = ds.getConnection();
try {
  // Do something with the connection
} finally {
  // Close connection so it can be released back to the pool!
  conn.close();
}