/*******************************************************************************
* Copyright (c) 2010 Robert "Unlogic" Olofsson (unlogic@unlogic.se).
* All rights reserved. This program and the accompanying materials
* are made available under the terms of the GNU Lesser Public License v3
* which accompanies this distribution, and is available at
* http://www.gnu.org/licenses/lgpl-3.0-standalone.html
******************************************************************************/
package se.unlogic.standardutils.db;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DBUtils {
public static boolean tableExists(DataSource dataSource, String tableName) throws SQLException {
Connection connection = null;
ResultSet rs = null;
try {
connection = dataSource.getConnection();
DatabaseMetaData meta = connection.getMetaData();
rs = meta.getTables(null, null, tableName, null);
if (rs.next()) {
return true;
}
} finally {
closeResultSet(rs);
closeConnection(connection);
}
return false;
}
public static ArrayList<String> listAllTables(Connection connection) throws SQLException {
ResultSet rs = null;
try {
DatabaseMetaData meta = connection.getMetaData();
rs = meta.getTables(null, null, null, null);
ArrayList<String> tableList = new ArrayList<String>();
while (rs.next()) {
tableList.add(rs.getString(3));
}
return tableList;
} finally {
closeResultSet(rs);
closeConnection(connection);
}
}
public static DataSource getDataSource(String name) throws NamingException {
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
return (DataSource) envContext.lookup(name);
}
public static boolean containsColumn(ResultSet rs, String columnName) {
try {
rs.findColumn(columnName);
return true;
} catch (SQLException e) {
return false;
}
}
public static List<String> getTableColumns(Connection connection, String selectedTable) throws SQLException {
ResultSet rs = null;
try {
DatabaseMetaData meta = connection.getMetaData();
rs = meta.getColumns(null, null, selectedTable, null);
ArrayList<String> columnNames = new ArrayList<String>();
while (rs.next()) {
columnNames.add(rs.getString(4));
}
return columnNames;
} finally {
closeResultSet(rs);
closeConnection(connection);
}
}
public static int getTableColumnCount(DataSource dataSource, String selectedTable) throws SQLException {
Connection connection = null;
ResultSet rs = null;
try {
connection = dataSource.getConnection();
DatabaseMetaData meta = connection.getMetaData();
rs = meta.getColumns(null, null, selectedTable, null);
rs.last();
return rs.getRow();
} finally {
closeResultSet(rs);
closeConnection(connection);
}
}
public static void closeConnection(Connection connection) {
try {
if (connection != null && !connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
}
}
public static void closeResultSet(ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
}
}
public static void closePreparedStatement(PreparedStatement pstmt) {
try {
if (pstmt != null) {
pstmt.close();
}
} catch (SQLException e) {
}
}
}