bioroot
Class DBUtil

java.lang.Object
  bioroot.DBUtil

public class DBUtil

extends java.lang.Object

Utility class to hold database relevant methods and fields, some from Hall and Brown The following is a collection of stuff for interacting with a SQL database First open a connection by calling connectMySQL, then get results with executeSQL, then close the connection with closeConnection.

Constructor Summary

DBUtil()

DBUtil(java.lang.String database)

Method Summary

static java.lang.String	buildSqlStatement(java.lang.String reagentType, UserBean user, DBUtil bioRoot, boolean descending) Creates a sql statement given a UserBean and a boolean as to whether to add a DESC to the order by where appropriate.
boolean	canAlter(UserBean userBean, java.lang.String tableName, int id) Checks whether the user can modify or delete an item.
boolean	canView(UserBean user, java.lang.String tableName, int id) Checks whether the user can view an item without creating a bean, just an id
java.lang.String[]	canView(UserBean user, java.lang.String tableName, java.lang.String[] ids) Checks whether the user can view multiple items based on id.
boolean	checkIP(java.lang.String IP) Checks to see if a given IP has exceeded the number of allowable failed logins.
void	closeConnection() Closes the Connection and subsequently the Statement and ResultSet objects
boolean	connectMySQL(java.lang.String database) Method to connect to the MySQL lab_collection database and create a statement object
java.lang.String	convertOrganismIdToName(int id) Converts and Organism id to and Organism name.
int	convertOrganismNameToId(java.lang.String name) Converts and Organism name to and OrganismId.
java.sql.ResultSet	executeSQL(java.lang.String sqlQuery)
java.sql.ResultSet	executeSQLAdvance(java.lang.String sqlQuery) Execute and advance the ResultSet.
boolean	executeSQLUpdate(java.lang.String sqlQuery) Fires SQL Update statement returning true if no problems were encountered.
java.lang.String[]	fetchCellTypeConcats(int id, UserBean user) Fetches concatinate of name and notes from CellType
java.lang.String[]	fetchColumn(java.lang.String statement) Returns a String[] of the items in a particular column.
java.lang.String[]	fetchConcatMakeIdLast(java.lang.String sql, int numColumns, java.lang.String divider, int lengthCutOff) Returns a concatinate of the results where each String in the String[] represents a results table row where each column entry is separated by the divider String.
java.lang.String[]	fetchGeneConcat(int id, UserBean user) Fetches concatinate of name and notes from Gene
java.lang.String[]	fetchGeneNames(int labGroupId) Fetches geneNames given a labGroupId, assumes connection to BioRoot+Database.
IdNameMatch[]	fetchIdNameMatchArray(java.lang.String sql) Fetches an IndexNameMatch Array.
java.lang.String	fetchLabGroupName(int id) Fetches an labGroupName given an id, assumes connection to BioRoot+Database.
java.lang.String[]	fetchLabGroups(java.lang.String[] labGroups) Returns a sorted list of lab groups.
java.lang.String[]	fetchMarkerConcats(int id) Fetches concatinate of name and notes from Marker
java.lang.String[]	fetchMarkerNames() Fetches markerNames given an labGroupId, assumes connection to BioRoot+Database.
java.lang.String[]	fetchMultipleCells(java.lang.String sql) Returns a String[] of the results.
java.lang.String[]	fetchOrganismConcat(int id) Fetches concatinate of name and notes from Organism
int[]	fetchReagentCounts(UserBean user, java.lang.String tableName) Fetches the number of reagents in the users labgroup, and the number owned by the user.
java.lang.String	fetchReagentNames(int labGroupId, java.lang.String reagentType) Fetches the sorted reagent names associated with a labGroup.
java.lang.String[]	fetchRecentReagentConcats(int userId, java.lang.String reagentName) Fetches concatinate of name and notes from Plasmids
java.lang.String[]	fetchResultConcat(java.lang.String sql, int numColumns, java.lang.String divider, int lengthCutOff) Returns a concatinate of the results where each String in the String[] represents a results table row where each column entry is separated by the divider String.
int[]	fetchSingleColumn(java.lang.String sql) Returns a int[] of the items in the first column.
java.lang.String[]	fetchSingleColumn(java.lang.String tableName, java.lang.String columnName) Returns a String[] of the items in a particular column.
java.lang.String	fetchUserName(int id, boolean firstNameFirst) Returns firstName lastName or lastName, firstName given a user id number
java.lang.String	getCell(java.lang.String sql) Returns the first cell from an sql statement.
int	getCellTypeId(java.lang.String name, int labGroupId) Fetches an tissueId given a tissue name and labGroupId (they aren't unique except within a labGroup)
java.sql.Connection	getConnection()
int	getGeneId(java.lang.String geneName, int labGroupId) Fetches an geneId given a geneName and labGroupId (they aren't unique except within a labGroup), assumes connection to BioRoot+Database.
java.lang.String	getGeneName(int geneId) Fetches an geneName given an geneId, assumes connection to BioRoot+Database.
int	getLastInsertId(java.lang.String table) Returns the last inserted row auto increment number.
int	getOrganismId(java.lang.String organismName) Fetches an organismId given a organismName, assumes connection to BioRoot.
java.lang.String	getOrganismName(int organismId) Fetches an organismName given an organismId, assumes connection to BioRoot.
static java.lang.String	getResult(java.sql.ResultSet result)
int	getResultSetInt(java.lang.String columnName) Use to avoid a null return when attempting to fetch a ResultSet.getInt().
java.lang.String	getResultSetString(java.lang.String columnName) Use to avoid a null return when attempting to fetch a ResultSet.getString().
java.lang.String	getResultString() returns the first column values from a result, for testing
int	getRowsEffected()
boolean	isConnectionClosed() checks if the connection is open
boolean	isNameUnique(java.lang.String tableName, java.lang.String name) Looks for a particular name in a table with a Name column.
boolean	isNameUnique(java.lang.String tableName, java.lang.String name, int labGroupId) Looks for a particular name in a table with a Name column and a particular lab group.
java.lang.String	tableLastModified(java.lang.String tableName)
boolean	updateCommonAccessFields(ReagentBean reagentBean, java.lang.String tableName) Just updates fields that are accessible by all lab group memebers.
boolean	updateCommonAccessFields(ReagentBean bean, UserBean userBean, javax.servlet.http.HttpServletRequest request) Just update the commonly accessible fields: lastUser, comments
void	updateLoginFailure(java.lang.String IP) Increments the numberFailures for an ip in the Login table if it exists or creates a new record.

Methods inherited from class java.lang.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

DBUtil

public DBUtil()

DBUtil

public DBUtil(java.lang.String database)

Method Detail

connectMySQL

public boolean connectMySQL(java.lang.String database)

Method to connect to the MySQL lab_collection database and create a statement object

executeSQLUpdate

public boolean executeSQLUpdate(java.lang.String sqlQuery)

Fires SQL Update statement returning true if no problems were encountered.

tableLastModified

public java.lang.String tableLastModified(java.lang.String tableName)

isNameUnique

public boolean isNameUnique(java.lang.String tableName,
                            java.lang.String name)

Looks for a particular name in a table with a Name column. Throws error if no name column. Returns true if nothing is found. Not specific to a particular lab group.

isNameUnique

public boolean isNameUnique(java.lang.String tableName,
                            java.lang.String name,
                            int labGroupId)

Looks for a particular name in a table with a Name column and a particular lab group. Throws error if no name column. Returns true if nothing is found.

executeSQL

public java.sql.ResultSet executeSQL(java.lang.String sqlQuery)

getResult

public static java.lang.String getResult(java.sql.ResultSet result)

getConnection

public java.sql.Connection getConnection()

canAlter

public boolean canAlter(UserBean userBean,
                        java.lang.String tableName,
                        int id)

Checks whether the user can modify or delete an item. They are the owner of the reagent, or a super user and the item is owned by the lab group.

canView

public boolean canView(UserBean user,
                       java.lang.String tableName,
                       int id)

Checks whether the user can view an item without creating a bean, just an id

canView

public java.lang.String[] canView(UserBean user,
                                  java.lang.String tableName,
                                  java.lang.String[] ids)

Checks whether the user can view multiple items based on id. Returns the ids that can be viewed by the user.

fetchIdNameMatchArray

public IdNameMatch[] fetchIdNameMatchArray(java.lang.String sql)

Fetches an IndexNameMatch Array. Use with getting info about genes or organisms, or anywhere you want just the index and name and want to match these.

getOrganismName

public java.lang.String getOrganismName(int organismId)

Fetches an organismName given an organismId, assumes connection to BioRoot.

getOrganismId

public int getOrganismId(java.lang.String organismName)

Fetches an organismId given a organismName, assumes connection to BioRoot.

getGeneName

public java.lang.String getGeneName(int geneId)

Fetches an geneName given an geneId, assumes connection to BioRoot+Database.

fetchLabGroupName

public java.lang.String fetchLabGroupName(int id)

Fetches an labGroupName given an id, assumes connection to BioRoot+Database.

fetchGeneNames

public java.lang.String[] fetchGeneNames(int labGroupId)

Fetches geneNames given a labGroupId, assumes connection to BioRoot+Database.

fetchMarkerNames

public java.lang.String[] fetchMarkerNames()

Fetches markerNames given an labGroupId, assumes connection to BioRoot+Database.

getGeneId

public int getGeneId(java.lang.String geneName,
                     int labGroupId)

Fetches an geneId given a geneName and labGroupId (they aren't unique except within a labGroup), assumes connection to BioRoot+Database.

getCellTypeId

public int getCellTypeId(java.lang.String name,
                         int labGroupId)

Fetches an tissueId given a tissue name and labGroupId (they aren't unique except within a labGroup)

updateCommonAccessFields

public boolean updateCommonAccessFields(ReagentBean reagentBean,
                                        java.lang.String tableName)

Just updates fields that are accessible by all lab group memebers.

updateCommonAccessFields

public boolean updateCommonAccessFields(ReagentBean bean,
                                        UserBean userBean,
                                        javax.servlet.http.HttpServletRequest request)

Just update the commonly accessible fields: lastUser, comments

getCell

public java.lang.String getCell(java.lang.String sql)

Returns the first cell from an sql statement.

getLastInsertId

public int getLastInsertId(java.lang.String table)

Returns the last inserted row auto increment number.

getResultString

public java.lang.String getResultString()

returns the first column values from a result, for testing

fetchSingleColumn

public java.lang.String[] fetchSingleColumn(java.lang.String tableName,
                                            java.lang.String columnName)

Returns a String[] of the items in a particular column.

fetchColumn

public java.lang.String[] fetchColumn(java.lang.String statement)

Returns a String[] of the items in a particular column.

fetchSingleColumn

public int[] fetchSingleColumn(java.lang.String sql)

Returns a int[] of the items in the first column. Returns a null if no entries are present.

fetchLabGroups

public java.lang.String[] fetchLabGroups(java.lang.String[] labGroups)

Returns a sorted list of lab groups.

convertOrganismNameToId

public int convertOrganismNameToId(java.lang.String name)

Converts and Organism name to and OrganismId.

convertOrganismIdToName

public java.lang.String convertOrganismIdToName(int id)

Converts and Organism id to and Organism name.

fetchMultipleCells

public java.lang.String[] fetchMultipleCells(java.lang.String sql)

Returns a String[] of the results. Returns a new String[]{""} if no entries are present.

fetchUserName

public java.lang.String fetchUserName(int id,
                                      boolean firstNameFirst)

Returns firstName lastName or lastName, firstName given a user id number

closeConnection

public void closeConnection()

Closes the Connection and subsequently the Statement and ResultSet objects

isConnectionClosed

public boolean isConnectionClosed()

checks if the connection is open

checkIP

public boolean checkIP(java.lang.String IP)

Checks to see if a given IP has exceeded the number of allowable failed logins. Will create a new IP entry if it's not found. Will reset entry and return true if 12hrs has passed.

fetchReagentNames

public java.lang.String fetchReagentNames(int labGroupId,
                                          java.lang.String reagentType)

Fetches the sorted reagent names associated with a labGroup.

buildSqlStatement

public static java.lang.String buildSqlStatement(java.lang.String reagentType,
                                                 UserBean user,
                                                 DBUtil bioRoot,
                                                 boolean descending)

Creates a sql statement given a UserBean and a boolean as to whether to add a DESC to the order by where appropriate. This also sets the Preference.ordered boolean if the sql contains an ORDER BY otherwise will set false. reagentType = Oligo, Plasmid, Strain...

updateLoginFailure

public void updateLoginFailure(java.lang.String IP)

Increments the numberFailures for an ip in the Login table if it exists or creates a new record.

executeSQLAdvance

public java.sql.ResultSet executeSQLAdvance(java.lang.String sqlQuery)

Execute and advance the ResultSet. Good for retrieving a single table row.

getResultSetString

public java.lang.String getResultSetString(java.lang.String columnName)

Use to avoid a null return when attempting to fetch a ResultSet.getString().

getResultSetInt

public int getResultSetInt(java.lang.String columnName)

Use to avoid a null return when attempting to fetch a ResultSet.getInt(). Sorta stupid

fetchReagentCounts

public int[] fetchReagentCounts(UserBean user,
                                java.lang.String tableName)

Fetches the number of reagents in the users labgroup, and the number owned by the user.

fetchOrganismConcat

public java.lang.String[] fetchOrganismConcat(int id)

Fetches concatinate of name and notes from Organism

fetchMarkerConcats

public java.lang.String[] fetchMarkerConcats(int id)

Fetches concatinate of name and notes from Marker

fetchCellTypeConcats

public java.lang.String[] fetchCellTypeConcats(int id,
                                               UserBean user)

Fetches concatinate of name and notes from CellType

fetchGeneConcat

public java.lang.String[] fetchGeneConcat(int id,
                                          UserBean user)

Fetches concatinate of name and notes from Gene

fetchRecentReagentConcats

public java.lang.String[] fetchRecentReagentConcats(int userId,
                                                    java.lang.String reagentName)

Fetches concatinate of name and notes from Plasmids

fetchResultConcat

public java.lang.String[] fetchResultConcat(java.lang.String sql,
                                            int numColumns,
                                            java.lang.String divider,
                                            int lengthCutOff)

Returns a concatinate of the results where each String in the String[] represents a results table row where each column entry is separated by the divider String. If a particular column has no value, nothing is added, no double dividers. Use the lengthCutOff to limit the size of the Strings; a ... is added if it is trunkated. Returns new String[]{""} if no entries are present.

fetchConcatMakeIdLast

public java.lang.String[] fetchConcatMakeIdLast(java.lang.String sql,
                                                int numColumns,
                                                java.lang.String divider,
                                                int lengthCutOff)

Returns a concatinate of the results where each String in the String[] represents a results table row where each column entry is separated by the divider String. If a particular column has no value, nothing is added, no double dividers. Use the lengthCutOff to limit the size of the Strings; a ... is added if it is trunkated. Returns new String[]{""} if no entries are present. The first query is assumed to be the id value and is always added to the end, even if the string was truncated. Thus the sql should look like "SELECT id, bla, bla FROM Table.." The result will look like bla,bla,id or bla, bla...,id This is a way of moving the ID to the end of the result yet trunkating in needed.

getRowsEffected

public int getRowsEffected()