J
jjmbcom
** POSTING TO THIS LIST ALSO **
Hello folks,
Sorry if this is a basic question. I have an application that
establishes a connection to two databases vua JDBC. Basically the
application pulls data from database-a, processes it, then writes it to
database-b. I have created by own DbClient class which basically
abstracts some of the work associated with connecting to MySQL
databases.
Prior to entering the main processing loop I create two separate
instances of the my DbClient class, one to connect to database-a and
another for database-b. Shortly after entering the main processing
loop I call a method on the first instance of DbClient, supposedly
connected to database-a. Oddly enough this connection is not in fact
connected to database-a, instead it is connected to database-b. It
almost seems that there is some LILO logic in the mix or the last
DbClient instance is simply overwriting the first?
Any advice would be greatly appreciated.
John
Reply
"."
Nov 15, 2:42 pm show options
Newsgroups: comp.lang.java.help
From: (e-mail address removed) (".") - Find messages by this author
Date: 15 Nov 2005 19:42:47 GMT
Local: Tues, Nov 15 2005 2:42 pm
Subject: Re: Issue managing multiple MySQL database connections
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse
On Tue, 15 Nov 2005 (e-mail address removed) wrote:
- Hide quoted text -
- Show quoted text -
Hard to say without seeing the code. Could you be sharing an instance
variable? Could you be using a class variable? I would guess if it is
something like a shared variable, it would be in the DbClient class you
created.
--
Send e-mail to: darrell dot grainger at utoronto dot ca
Reply
(e-mail address removed)
Nov 15, 4:05 pm show options
Newsgroups: comp.lang.java.help
From: (e-mail address removed) - Find messages by this author
Date: 15 Nov 2005 13:05:08 -0800
Local: Tues, Nov 15 2005 4:05 pm
Subject: Re: Issue managing multiple MySQL database connections
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse
Source code for the DbClient class is below:
/*
* DbClient.java
*
* Created on November 9, 2005, 4:44 PM
*
* To change this template, choose Tools | Options and locate the
template under
* the Source Creation and Management node. Right-click the template
and choose
* Open. You can then make changes to the template in the Source
Editor.
*/
package xyz;
import org.apache.log4j.Logger;
import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.PropertyConfigurator;
import java.sql.*;
import java.sql.DriverManager;
import java.util.*;
/**
*
* @author jbrzozowski
*/
public class DbClient {
private static final String CLASSNAME = DbClient.class.getName();
public static Logger logger = Logger.getLogger(CLASSNAME);
private static final String D_DBURL = null;
private static Connection connection = null;
private static String driverName = "com.mysql.jdbc.Driver";
private static final String D_DBTYPE = "mysql";
private static final String D_DBHOST = "localhost";
private static final String D_DBUSER = "test";
private static final String D_DBPASS = "test";
private static final String D_DBNAME = "test";
private static String dbUrl = D_DBURL;
private static String dbHost = D_DBHOST;
private static String dbUser = D_DBUSER;
private static String dbPass = D_DBPASS;
private static String dbName = D_DBNAME;
private static String dbType = D_DBTYPE;
/**
* Creates a new instance of DbClient
*/
public DbClient() {
}
public DbClient(String type, String host, String user, String pass,
String name) {
if(type != null) {
dbType = type;
}
if(host != null) {
dbHost = host;
}
if(user != null) {
dbUser = user;
}
if(pass != null) {
dbPass = pass;
}
dbUrl = "jdbc:mysql://" + dbHost + "/" + dbName;
}
public static void dump() {
logger.debug("Database Host = " + dbHost);
logger.debug("Database Type = " + dbType);
logger.debug("Database Name = " + dbName);
logger.debug("Database User = " + dbUser);
logger.debug("Database Pass = " + dbPass);
}
public boolean connect() throws Exception {
boolean connected = false;
this.dump();
try {
// Load the JDBC driver
driverName = "com.mysql.jdbc.Driver"; // MySQL-Connector
Class.forName(driverName);
// Create a connection to the database
logger.info("Attempting to connect to database, " + dbUrl +
" as " + dbUser);
connection = DriverManager.getConnection(dbUrl, dbUser,
dbPass);
if(!connection.isClosed()) {
logger.debug("Database connection successful");
connected = true;
}
else {
logger.fatal("Database connection failed");
}
} catch (ClassNotFoundException e) {
// Could not find the database driver
logger.fatal("Could not load database driver, " +
driverName);
logger.fatal("Exception:" + e);
throw e;
// e.printStackTrace();
} catch (SQLException e) {
// Could not connect to the database
logger.fatal("Could not connect to database at, " + dbUrl +
" as, " + dbUser);
logger.fatal("Exception:" + e);
// e.printStackTrace();
throw e;
}
return connected;
}
public boolean isConnected() {
boolean connected = true;
try {
if(connection.isClosed()) {
connected = false;
}
} catch (Exception e) {
// Not doing anything with the exception
connected = false;
}
logger.debug("Database connected, " + connected);
return connected;
}
public boolean store(Lease lease) {
boolean set = false;
lease.dump();
set = true;
return set;
}
public boolean toAggDb(Lease lease) {
boolean set = false;
set = true;
return set;
}
public boolean updateLocalLeaseProcessedFlag(int localleaserecid) {
boolean updated = false;
String sql = "update local_lease set processed = 1 where id =
?";
try {
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1,localleaserecid);
updated = pstmt.execute();
} catch (SQLException sqlx) {
logger.fatal(sqlx);
}
return updated;
}
public ResultSet fetchLocalUnprocessed(int lastrecidnum) {
this.dump();
ResultSet unprocessed = null;
String sql = "select * from local_lease where id > ? and
processed = 0";
logger.debug("Using connection, " + connection.toString());
try {
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1,lastrecidnum);
logger.debug("SQL = " + pstmt.toString());
unprocessed = pstmt.executeQuery();
} catch (SQLException sqlx) {
logger.fatal(sqlx);
}
return unprocessed;
}
public int purgeProcessed(String db) {
int count = 0;
if(db.equalsIgnoreCase("local")) {
logger.info("Purging local lease database");
String sql = "delete from local_lease where processed = 1";
try {
Statement stmt = connection.createStatement();
count = stmt.executeUpdate(sql);
}
catch (SQLException sqlx) {
logger.fatal(sqlx);
}
}
else if (db.equalsIgnoreCase("agg")) {
logger.info("Purging agg lease database");
}
else {
logger.warn("Uknown database for purging, "+ db);
}
return count;
}
public boolean toLocalDb(String leaseblob) {
long t0 = System.currentTimeMillis();
boolean set = false;
String sql = "insert into local_lease (processed, leaseblob)
values(?,?)";
try {
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1,0);
pstmt.setString(2, leaseblob);
logger.debug("SQL = " + pstmt.toString());
set = pstmt.execute();
} catch(SQLException sqlx) {
logger.fatal(sqlx);
}
long t1 = System.currentTimeMillis();
long elapsed = t1 - t0;
logger.debug("Elapsed time to complete processing, " +
elapsed);
return set;
}
Hello folks,
Sorry if this is a basic question. I have an application that
establishes a connection to two databases vua JDBC. Basically the
application pulls data from database-a, processes it, then writes it to
database-b. I have created by own DbClient class which basically
abstracts some of the work associated with connecting to MySQL
databases.
Prior to entering the main processing loop I create two separate
instances of the my DbClient class, one to connect to database-a and
another for database-b. Shortly after entering the main processing
loop I call a method on the first instance of DbClient, supposedly
connected to database-a. Oddly enough this connection is not in fact
connected to database-a, instead it is connected to database-b. It
almost seems that there is some LILO logic in the mix or the last
DbClient instance is simply overwriting the first?
Any advice would be greatly appreciated.
John
Reply
"."
Nov 15, 2:42 pm show options
Newsgroups: comp.lang.java.help
From: (e-mail address removed) (".") - Find messages by this author
Date: 15 Nov 2005 19:42:47 GMT
Local: Tues, Nov 15 2005 2:42 pm
Subject: Re: Issue managing multiple MySQL database connections
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse
On Tue, 15 Nov 2005 (e-mail address removed) wrote:
- Hide quoted text -
- Show quoted text -
Hard to say without seeing the code. Could you be sharing an instance
variable? Could you be using a class variable? I would guess if it is
something like a shared variable, it would be in the DbClient class you
created.
--
Send e-mail to: darrell dot grainger at utoronto dot ca
Reply
(e-mail address removed)
Nov 15, 4:05 pm show options
Newsgroups: comp.lang.java.help
From: (e-mail address removed) - Find messages by this author
Date: 15 Nov 2005 13:05:08 -0800
Local: Tues, Nov 15 2005 4:05 pm
Subject: Re: Issue managing multiple MySQL database connections
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse
Source code for the DbClient class is below:
/*
* DbClient.java
*
* Created on November 9, 2005, 4:44 PM
*
* To change this template, choose Tools | Options and locate the
template under
* the Source Creation and Management node. Right-click the template
and choose
* Open. You can then make changes to the template in the Source
Editor.
*/
package xyz;
import org.apache.log4j.Logger;
import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.PropertyConfigurator;
import java.sql.*;
import java.sql.DriverManager;
import java.util.*;
/**
*
* @author jbrzozowski
*/
public class DbClient {
private static final String CLASSNAME = DbClient.class.getName();
public static Logger logger = Logger.getLogger(CLASSNAME);
private static final String D_DBURL = null;
private static Connection connection = null;
private static String driverName = "com.mysql.jdbc.Driver";
private static final String D_DBTYPE = "mysql";
private static final String D_DBHOST = "localhost";
private static final String D_DBUSER = "test";
private static final String D_DBPASS = "test";
private static final String D_DBNAME = "test";
private static String dbUrl = D_DBURL;
private static String dbHost = D_DBHOST;
private static String dbUser = D_DBUSER;
private static String dbPass = D_DBPASS;
private static String dbName = D_DBNAME;
private static String dbType = D_DBTYPE;
/**
* Creates a new instance of DbClient
*/
public DbClient() {
}
public DbClient(String type, String host, String user, String pass,
String name) {
if(type != null) {
dbType = type;
}
if(host != null) {
dbHost = host;
}
if(user != null) {
dbUser = user;
}
if(pass != null) {
dbPass = pass;
}
dbUrl = "jdbc:mysql://" + dbHost + "/" + dbName;
}
public static void dump() {
logger.debug("Database Host = " + dbHost);
logger.debug("Database Type = " + dbType);
logger.debug("Database Name = " + dbName);
logger.debug("Database User = " + dbUser);
logger.debug("Database Pass = " + dbPass);
}
public boolean connect() throws Exception {
boolean connected = false;
this.dump();
try {
// Load the JDBC driver
driverName = "com.mysql.jdbc.Driver"; // MySQL-Connector
Class.forName(driverName);
// Create a connection to the database
logger.info("Attempting to connect to database, " + dbUrl +
" as " + dbUser);
connection = DriverManager.getConnection(dbUrl, dbUser,
dbPass);
if(!connection.isClosed()) {
logger.debug("Database connection successful");
connected = true;
}
else {
logger.fatal("Database connection failed");
}
} catch (ClassNotFoundException e) {
// Could not find the database driver
logger.fatal("Could not load database driver, " +
driverName);
logger.fatal("Exception:" + e);
throw e;
// e.printStackTrace();
} catch (SQLException e) {
// Could not connect to the database
logger.fatal("Could not connect to database at, " + dbUrl +
" as, " + dbUser);
logger.fatal("Exception:" + e);
// e.printStackTrace();
throw e;
}
return connected;
}
public boolean isConnected() {
boolean connected = true;
try {
if(connection.isClosed()) {
connected = false;
}
} catch (Exception e) {
// Not doing anything with the exception
connected = false;
}
logger.debug("Database connected, " + connected);
return connected;
}
public boolean store(Lease lease) {
boolean set = false;
lease.dump();
set = true;
return set;
}
public boolean toAggDb(Lease lease) {
boolean set = false;
set = true;
return set;
}
public boolean updateLocalLeaseProcessedFlag(int localleaserecid) {
boolean updated = false;
String sql = "update local_lease set processed = 1 where id =
?";
try {
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1,localleaserecid);
updated = pstmt.execute();
} catch (SQLException sqlx) {
logger.fatal(sqlx);
}
return updated;
}
public ResultSet fetchLocalUnprocessed(int lastrecidnum) {
this.dump();
ResultSet unprocessed = null;
String sql = "select * from local_lease where id > ? and
processed = 0";
logger.debug("Using connection, " + connection.toString());
try {
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1,lastrecidnum);
logger.debug("SQL = " + pstmt.toString());
unprocessed = pstmt.executeQuery();
} catch (SQLException sqlx) {
logger.fatal(sqlx);
}
return unprocessed;
}
public int purgeProcessed(String db) {
int count = 0;
if(db.equalsIgnoreCase("local")) {
logger.info("Purging local lease database");
String sql = "delete from local_lease where processed = 1";
try {
Statement stmt = connection.createStatement();
count = stmt.executeUpdate(sql);
}
catch (SQLException sqlx) {
logger.fatal(sqlx);
}
}
else if (db.equalsIgnoreCase("agg")) {
logger.info("Purging agg lease database");
}
else {
logger.warn("Uknown database for purging, "+ db);
}
return count;
}
public boolean toLocalDb(String leaseblob) {
long t0 = System.currentTimeMillis();
boolean set = false;
String sql = "insert into local_lease (processed, leaseblob)
values(?,?)";
try {
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1,0);
pstmt.setString(2, leaseblob);
logger.debug("SQL = " + pstmt.toString());
set = pstmt.execute();
} catch(SQLException sqlx) {
logger.fatal(sqlx);
}
long t1 = System.currentTimeMillis();
long elapsed = t1 - t0;
logger.debug("Elapsed time to complete processing, " +
elapsed);
return set;
}