Reducing the amounts of connections

[Christoffer Killander]

Hi guys.
I have this code that connects to a DB and either edits the data or retrieves data (the code works as intended).
This code is used by aswing program.
So my question is how can change the way it connects so that it only needs to connect once to the database (while my swing program is running) instead of once everytime it needs to uppdate or retrieve data (see code).

import java.sql.*;
import javax.swing.table.*;
import java.util.*;

public class DBManager {
	static Connection con;

	public static TableModel UpdateTable(String URL, String User, String PW) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(URL, User, PW);
			Statement statement = con.createStatement();
			ResultSet result = statement.executeQuery("SELECT * FROM Company");
			TableModel TM = resultSetToTableModel(result);
			return TM;
		} catch (Exception e) {
			System.err.println("Exception: " + e.getMessage());
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
		return null;
	}

	public static void editCompany(String NewCompanyName, String NewDate,
			String OldCompanyName, String URL, String User, String PW) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(URL, User, PW);
			Statement statement = con.createStatement();
			statement.executeUpdate("UPDATE Company " + "Set CompanyName = "
					+ "'" + NewCompanyName + "', " + "Created = " + "'"
					+ NewDate + "'" + " Where " + "CompanyName = " + "'"
					+ OldCompanyName + "'" // get values from table
			);
		} catch (Exception e) {
			System.err.println("Exception: " + e.getMessage());
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static void RemoveCompany(String OldCompanyName, String URL,
			String User, String PW) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(URL, User, PW);
			Statement statement = con.createStatement();
			statement.executeUpdate("Delete From Company " + " Where "
					+ "CompanyName = " + "'" + OldCompanyName + "'");
		} catch (Exception e) {
			System.err.println("Exception: " + e.getMessage());
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static void RemoveCompany(Array OldCompanyNames, String URL,
			String User, String PW) {
		// TODO: For Removing more than one Company
	}

	public static void AddCompany(String NewCompanyName, String NewDate,
			String URL, String User, String PW) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(URL, User, PW);
			Statement statement = con.createStatement();
			statement
					.executeUpdate("INSERT INTO Company (CompanyName, Created)"
							+ " VALUES " + "('" + NewCompanyName + "', '"
							+ NewDate + "')");
		} catch (Exception e) {
			System.err.println("Exception: " + e.getMessage());
		} finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	public static TableModel resultSetToTableModel(ResultSet rs) {
		try {
			ResultSetMetaData metaData = rs.getMetaData();
			int numberOfColumns = metaData.getColumnCount();
			Vector columnNames = new Vector();
			// Get the column names
			for (int column = 0; column < numberOfColumns; column++) {
				columnNames.addElement(metaData.getColumnLabel(column + 1));
			}
			// Get the rows
			Vector rows = new Vector();
			while (rs.next()) {
				Vector newRow = new Vector();
				for (int i = 1; i <= numberOfColumns; i++) {
					newRow.addElement(rs.getObject(i));
				}
				rows.addElement(newRow);
			}
			return new DefaultTableModel(rows, columnNames);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
}

Any constructive comments are greatly appreciated

//Christoffer

[Jeanne Boyarsky]

You could store the connection in a variable and not close it each time. This is basically how connection pooling works - you have an open connection and return it to the pool rather than closing it. I'm not sure how long you can keep the same open connection. (the pool implementation usually handles it), but that's something you can test and find out.

[chris webster]

Hi Christoffer,

Slightly different topic, but you should look at how you define the key for your Company table. Right now it looks like your primary key is CompanyName, and you are updating this in your edit method. This means that when you update the value, your RDBMS also needs to update the PK index value, which will take extra time.

In any case, it is generally bad practice to update your key e.g. what do you do with any child records that use the CompanyName as a foreign key to refer back to your Company record?

If you think you will need to modify a key, then it is not really your primary key. Instead, you should probably use a numeric ID as a surrogate primary key. This can be an arbitrary unique value which your DB can generate for you e.g. define the column as "auto-increment", or populate it from a DB sequence (Oracle). Hibernate knows how to handle these options on the Java side.

Then you simply use the CompanyId to find your Company record, and you can also use it anywhere you have a foreign key back to the Company table. If you change the CompanyName, it's just another attribute and does not affect your foreign keys etc.

Of course, you may still need to ensure the CompanyName is unique, which is usually done via a unique index on the column, so changing the name will still cause an update to the index. But at least your FKs will not have to change.

Another advantage of using numeric surrogate keys is that they are small, so there is less data to move around when you fetch them, and you only ever need one PK column on a table rather than having a composite PK of several columns, which also makes it easier to manage foreign keys on child/grandchild tables.

Hope this helps.

Chris

[Christoffer Killander]

Hi Chris.
My table looks like this at the moment:

CREATE TABLE if not exists company ( ID int AUTO_INCREMENT primary key, CompanyName varchar(30) unique, Created datetime );

I changed my code a while ago so it uppdates by matcing the ID's of the selected row and the database.
Anyway is there something i have still missed?

// Best Regards Christoffer



P.S. Also very thankfull for all the help i have gotten so far