Too many connections
Posted by: Chris Bölter
Date: October 10, 2007 07:27AM

Hi,

I'am using the mysql-connector-java-5.1.2-beta-bin to connect to the MySQL Server (Version 5.0.22), but I have a big problem with my program.

Here are some example Code:

Import Class:

package de.util.readclasses;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import org.apache.commons.codec.digest.DigestUtils;

import de.util.readclasses.database.*;

public class ImportTagReadIn {

public static void readTag(String path) throws IOException,
ClassNotFoundException, SQLException {
ArrayList<ImportTagGetSet> tags = new ArrayList<ImportTagGetSet>();
ImportTag database = new ImportTag();
<!---- reduced ---->

//saves the datas in the database

for (Iterator<ImportTagGetSet> i = tags.iterator(); i.hasNext();) {

ImportTagGetSet newTag = i.next();
String script1 = newTag.getScript().substring(53);
String hash = DigestUtils.md5Hex(script1);

int exist = database.getHash(hash);
if (exist == 0) {
int webSiteId = database
.webSiteId(newTag.getSitename());
int TagId = database.insertTag(newTag.getScript(),
newTag.getNoscript(), newTag.getZone(),
webSiteId);
database.parseSize(TagId, newTag.getSize());

}
}

}
}
database.closeStore();
}
}


ImportTag class:

package de.orangemedia.readclasses.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.codec.digest.DigestUtils;

public class ImportTag {
private static Connection connect;

public ImportTag() throws ClassNotFoundException, SQLException {
/** MySQL Datenbanktreiber laden */
Class.forName("com.mysql.jdbc.Driver");

/**
* Verbindung zur Datenbank aufbauen und Zugangsdaten übergeben
* jdbc:mysql://localhost/NamederDatenbank, Benutzername, Passwort
*/
ImportTag.connect = DriverManager.getConnection(
"jdbc:mysql://localhost/test", "user",
"password");

}

public void closeStore() throws SQLException {
connect.close();
}

/**
*
* @param sitename
* @return
* @throws SQLException
*/

public int webSiteId(String sitename) throws SQLException {
Statement st = connect.createStatement();
ResultSet rs = st
.executeQuery("select ID from website where sitename = '"
+ sitename + "'");
int webSiteId = 0;
while (rs.next()) {
webSiteId = rs.getInt("ID");
}
st.close();
return webSiteId;
}

/**
*
* @param script
* @param noscript
* @param zone
* @param webSiteId
* @return tag_id
* @throws SQLException
*/

public int insertTag(String script, String noscript, String zone,
int webSiteId) throws SQLException {

String script1 = script.substring(53);
String scriptHash = DigestUtils.md5Hex(script1);

PreparedStatement stmt = connect
.prepareStatement(
"insert into tag(script,noscript,website_id,zone,hashcode) values(?,?,?,?,?)",
Statement.RETURN_GENERATED_KEYS);
stmt.setString(1, script);
stmt.setString(2, noscript);
stmt.setInt(3, webSiteId);
stmt.setString(4, zone);
stmt.setString(5, scriptHash);
stmt.execute();
ResultSet rs = stmt.getGeneratedKeys();
int tag_id = 0;
if (rs.next())
tag_id = rs.getInt(1);
rs.close();
stmt.close();
return tag_id;
}

/**
*
* @param TagId
* @param Sizes
* @throws ClassNotFoundException
* @throws SQLException
*/

public void parseSize(int TagId, String Sizes)
throws ClassNotFoundException, SQLException {
ImportTag data = new ImportTag();
String sizes = Sizes.replaceAll(" ", "");
String[] size = sizes.split(",");

for (int i = 0; i < size.length; i++) {
Statement st = connect.createStatement();
ResultSet rs = st
.executeQuery("select ID from tag_size where size = '"
+ size + "'");
int sizeId = 0;
while (rs.next()) {
sizeId = rs.getInt("ID");
}
data.insertSize(TagId, sizeId);
st.close();
}
}

/**
*
* @param TagId
* @param SizeId
* @throws SQLException
*/

public void insertSize(int TagId, int SizeId) throws SQLException {
Statement st = connect.createStatement();
st.execute("insert into size_id(tag_id,size_id) VALUES('" + TagId
+ "','" + SizeId + "')");
st.close();
}

/**
*
* @param size
* @throws SQLException
*/

public void insertSize(String size) throws SQLException {
Statement st = connect.createStatement();
st.execute("INSERT into tag_size(size) VALUES('" + size + "')");
st.close();
}

/**
*
* @param hash
* @return
* @throws SQLException
*/

public int getHash(String hash) throws SQLException {
Statement st = connect.createStatement();
ResultSet rs = st.executeQuery("select ID from tag where hashcode = '"
+ hash + "'");
rs.last();
int result = rs.getRow();
st.close();
return result;
}

/**
*
* @param size
* @return
* @throws SQLException
*/

public int getDuplicateSize(String size) throws SQLException {
Statement st = connect.createStatement();
ResultSet rs = st.executeQuery("select ID from tag_size where size = '"
+ size + "'");
rs.last();
int result = rs.getRow();
st.close();
return result;

}

}

My Problem is, i becomes a lot of connections, so much that the server abort the connection but i don't have an idea why he does it.

I hope anybody can help me...

Regards Chris

Options: ReplyQuote


Subject
Written By
Posted
Too many connections
October 10, 2007 07:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.