Re: MYSQL CONNECTION CLASS

[Christian Rauh]

Fala Guilherme,
>
> Can someone who uses TOMCAT send me a sample class that conects to the
> database? I have made one that initializes de driver to connect and returns
> the connection, so it is very slow. What kind of solution do you have?

O esquema é este mesmo, faz a conexão com o banco de dados e depois faz
as queries. Para otimizar o processo faça a conexão na incialização do
servlet e guarde-a. A cada request você usa a mesma conexão.

Estou te mandando uma classes que fazer isto. São elas:

  SingleMethodServlet - apenas um servlet auxiliar para não ter
que mandar o doGet() chamar doPost(). Tenho
que tem mandar porque a próxima é
subclasse dela.
  DatabaseHttpServlet - servlet que cria a conexão com o banco de
dados ao ser criado e mantem esta conexão
no atributo dbConnection.
  DatabaseBuilderServlet - um exemplo. Eu uso esta classe sempre que
tenho que criar um banco de dados.
  web.xml - um exemplo de configuração destas classes.

Note que eu estou usando MySQL e os drivers do banco de dados devem
estar no diretório lib.

Até mais,

Christian
 DatabaseHttpServlet.java
 SingleMethodServlet.java
 DatabaseBuilderServlet.java



http://java.sun.com/j2ee/dtds/web-app_2_2.dtd">




   Enquete


   Aplicativo para a realização de enquetes.







  dbDriver
  org.gjt.mm.mysql.Driver
  
 Driver do banco de dados.
  



  dbConnectionUrl
  jdbc:mysql://200.183.10.6:3306/gatorade
  
 String para conexão no banco de dados.
  



  dbUser
  christian
  
 Usuário do banco de dados.
  



  dbPassword
  winona
  
 Senha do usuário do banco de dados.
  







  
 enquete
  
  
 com.newtradebr.enquete.EnqueteServlet
  



  
 database
  
  
 com.newtradebr.enquete.EnqueteDatabaseBuilderServlet
  

RE: MYSQL CONNECTION CLASS

[Dianne Cree]

here's a JDBC class:

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.util.*;

public class JDBCconn2 extends HttpServlet
{
   int iNumCols;
   int iNumRows;
   PrintWriter out;
   Object value[];
   String requestParam;

   public void doGet(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException
   {
String url;
Connection con;
Statement stmt;
ResultSet resultset;
out = response.getWriter();
response.setContentType("text/html");

requestParam = request.getParameter("hidRandomNum");
//out.println("mstest2 db");
url = "jdbc:odbc:MSTest2";

/*
String createString;
createString = "create table COFFEES " +
"(COF_NAME varchar(32), " +
"SUP_ID int, " +
"PRICE float, " +
"SALES int, " +
"TOTAL int)";
*/
try
{
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(java.lang.ClassNotFoundException e)
{
   System.err.print("ClassNotFoundException: ");
   System.err.println(e.getMessage());
   out.println(e.getMessage());
}

try
{
   //con = DriverManager.getConnection(url,"Admin","duke1");
   con = DriverManager.getConnection(url);
   stmt = con.createStatement();
   //stmt.executeUpdate("SELECT * FROM TableRND"); //this would update
the db if you were adding a table like from above.
   resultset = stmt.executeQuery("SELECT * FROM TableRND");
   displayTheResults(resultset);

   stmt.close();
   con.close();
}
catch(SQLException ex)
{
System.err.println("SQLException: " + ex.getMessage());
}
   }


   private void displayTheResults(ResultSet rs)
   {
  String strDBNumber;
  try
  {
 //get some metadata: columns names, etc about the results
 ResultSetMetaData metadata = rs.getMetaData();

 iNumCols = metadata.getColumnCount();
 iNumRows=1;  //keep track of row count
 //the arrays are NOT zero based
/*
 for(int j=1;j<=iNumCols;j++)
 {
out.println(metadata.getColumnLabel(j));
out.println(metadata.getColumnDisplaySize(j));
 }
 //out.println("\n");
*/


 out.println("\n");
 out.println("");
 out.println("   ");
 out.println("   ");
 out.println("   ");
out.println("");
out.println("the hidden field random number is: " + requestParam);
out.println("");
//out.println("the database number is: " +
rs.getObject("iRandNum").toString().trim());
out.println("the database number is: ");
rs.next();
strDBNumber = rs.getObject("iRandNum").toString().trim();
out.println(strDBNumber);
out.println("");

if(requestParam != null  && strDBNumber != null)
   try
   {
  if(Integer.parseInt(strDBNumber) ==
Integer.parseInt(requestParam))
 out.println("The numbers match");
  else
 out.println("The numbers don't match");
   }
   catch(NumberFormatException e)
   {
  out.println("The argument you specify must be an integer.");
   }
   catch(Exception e)
   {
  out.println("The exception is: " + e);
   }
else
   out.println("the requestParam or strDBNumber was null.");

/*
 out.println("  ");

 //rows
 while(rs.next())
 {
out.println(" ");
//columns
for(int c=1;c<=iNumCols;c++)
{
   out.println("\n");
   out.println(rs.getObject(c).toString().trim());
   out.println("");
}
out.println(" ");
 }
 out.println("  ");
*/
  }
  catch(SQLException ex)
  {
 System.err.println("SQLException: " + ex.getMessage());
  }

  out.println("   ");
  out.println("");

   }

   public void doPost(

Re: MYSQL CONNECTION CLASS

[Guilherme Zambon]

Thanks to all that tried do help me :). I was really about to live from
Tomcat to another Jsp Runner when I got this problem. I really think Tomcat
needs a internal connection pooling, and I wish someday we will have it!

[ ]'s

Guilherme Zambon
[EMAIL PROTECTED], [EMAIL PROTECTED] <- home
[EMAIL PROTECTED] <- work

Re: MYSQL CONNECTION CLASS

[hische]

I am using the com.javaexchange.dbConnectionBrocker package. It's simple, free
and does the trick.

Wilko









"Guilherme Zambon" <[EMAIL PROTECTED]> on 15-03-2001 02:29:03

Please respond to [EMAIL PROTECTED]

To:   [EMAIL PROTECTED], josé placide <[EMAIL PROTECTED]>
cc:(bcc: Wilko Hische/HADV/NL)
Subject:  MYSQL CONNECTION CLASS





Can someone who uses TOMCAT send me a sample class that conects to the
database? I have made one that initializes de driver to connect and returns
the connection, so it is very slow. What kind of solution do you have?

RE: MYSQL CONNECTION CLASS

[James Carroll]

What you need is connection pooling

Let me know what you think of this class (I'm pretty happy with it)

All it needs is the URL that represents your connection to the
database...  make the following calls in your init()

 itsConnectionPool = new
VSDBPool("jdbc:mysql://localhost/database?user=user&password=pass");
 itsConnectionPool.sc = getServletContext();



import java.sql.*;
import java.util.Vector; // as in list
import java.lang.String;

// for debugging
import javax.servlet.*;

class VSDBPool implements Runnable
{
// connections act as a stack... When it gets too low, we add more
// and when it gets too high, we remove some.
protected Vector itsConnections;
protected Thread itsWorker;

// for debug logging
public ServletContext sc;

private int itsLowWaterCount = 5;
private int itsHighWaterCount = 25;

private String itsDBURL;

public VSDBPool(String dbURL)
{
itsDBURL = dbURL; 
itsConnections = new Vector();

itsWorker = new Thread(this);
itsWorker.start();
}
 
private synchronized Connection createConnection()
{
Connection c = null;
try
{
c = DriverManager.getConnection(itsDBURL);
}
catch (Exception e)
{
sc.log("SQLException: " + e.getMessage());
}
return c;
}

private synchronized void closeConnection(Connection c)
{
// perform any wrap-up we need
return;
}

public synchronized Connection getConnection()
{
Connection c;

// if there are any in the pool
if (itsConnections.size() > 0)
{ 
int last = itsConnections.size() - 1;
c = (Connection)itsConnections.elementAt(last);
itsConnections.removeElementAt(last);
}
else
{   // create one right away and return it!
// this one never sees our collection of Connections
c = createConnection();
sc.log("Quickly creating connection: " + c);
}
return c;
}

public synchronized void returnConnection(Connection c)
{
itsConnections.addElement(c);
}

// handle background tasks...
public void run()
{
while(true)
{
// check once in a while for the need to add or remove
connections
try
{
Thread.sleep(5000);
} catch (InterruptedException e)
{
}

if (itsConnections.size() < itsLowWaterCount)
{
Connection c = createConnection();
if (c != null)
{
itsConnections.addElement(c);
}
}

if (itsConnections.size() > itsHighWaterCount)
{
int last = itsConnections.size() - 1;
Connection c =
(Connection)itsConnections.elementAt(last);
itsConnections.removeElementAt(last);
closeConnection(c);
}

// check existing connections 
// to make sure they haven't timed out
for (int i = 0; i < itsConnections.size(); i++)
{
Connection c = (Connection)itsConnections.elementAt(i);
try
{
if (c.isClosed())
{
// create a new connection and put it in the
// pool
itsConnections.setElementAt(createConnection(),
i);
}
} 
catch (SQLException e)
{
System.out.println(e.toString());
}
}
}
}
}


-Original Message-
From: Guilherme Zambon [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 14, 2001 8:29 PM
To: [EMAIL PROTECTED]; josé placide
Subject: MYSQL CONNECTION CLASS


Can someone who uses TOMCAT send me a sample class that conects to the
database? I have made one that initializes de driver to connect and
returns
the connection, so it is very slow. What kind of solution do you have?