dbutil.java

Sun公司Dream项目

/*
 * The contents of this file are subject to the terms
 * of the Common Development and Distribution License
 * (the "License").  You may not use this file except
 * in compliance with the License.
 *
 * You can obtain a copy of the license at
 * http://www.opensource.org/licenses/cddl1.php
 * See the License for the specific language governing
 * permissions and limitations under the License.
 *
 * When distributing Covered Code, include this CDDL
 * HEADER in each file and include the License file at
 * http://www.opensource.org/licenses/cddl1.php.  If 
 * applicable, add the following below this CDDL HEADER, 
 * with the fields enclosed by brackets "[]" replaced 
 * with your own identifying information: 
 * Portions Copyright [yyyy]
 * [name of copyright owner]
 */ 

/*
 * $(@)DBUtil.java $Revision: 1.1.1.1 $ $Date: 2006/07/24 21:56:11 $
 * 
 * Copyright 2006 Sun Microsystems, Inc. All Rights Reserved.
 */


/*
 * @(#)DBUtil.java  1.1 05/11/09
 *
 * Copyright 2002-2005 Sun Microsystems, Inc. All Rights Reserved.
 */


package com.sun.dream.shop;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Vector;

import java.sql.*;
import java.util.*;
import javax.sql.*;
import javax.ejb.*;
import javax.naming.*;


/**
 *
 * To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
public class DBUtil {
	
	//public static final String DEFAULT_DATA_SOURCE = "java:comp/env/jdbc/operaserverdb";
	public static DataSource dataSource;
		
	public static Connection getConnection()
		throws ShopException {
		try {
                        InitialContext ic = new InitialContext();
                        dataSource = (DataSource) ic.lookup("jdbc/sample");
        
			//Class.forName(ShopServlet.iProperties.getProperty(Const.DATABASE_DRIVER, "org.hsqldb.jdbcDriver"));
			//return  DriverManager.getConnection(
			//		ShopServlet.iProperties.getProperty(Const.DATABASE_URL,"jdbc:hsqldb:hsql://localhost"), 
			//		ShopServlet.iProperties.getProperty(Const.DATABASE_USERNAME, "demo"), 
			//		ShopServlet.iProperties.getProperty(Const.DATABASE_PASSWORD, "demo"));			
                        return dataSource.getConnection();
			
		} catch (Exception ex) {
                        System.out.println (ex.toString());
			throw new ShopException(ShopException.DATABASE_CONNECTION_ERROR , 
				ex.toString());
			
		}
	}
	
	public static String[] getRecord(String sql, int[] sqlTypes, 
		String[] formats, Object[] params) 
		throws ShopException {

		Connection conn = null;
		PreparedStatement st = null;
		ResultSet results = null;
		try {
			conn = getConnection();
			st = conn.prepareStatement(sql);
		
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					st.setObject(i + 1, params[i]);
				}
			}

			results = st.executeQuery();

			if (!results.next()) {
				return null;
			}
			String[] resultStrings = new String[sqlTypes.length];
			int numFields = resultStrings.length;
			if (formats == null) {
				for (int i = 0; i < numFields; i++) {
					resultStrings[i] =
						getFormattedValue(results, i + 1, sqlTypes[i], null);
				}
			} else {
				for (int i = 0; i < numFields; i++) {
					resultStrings[i] =
						getFormattedValue(
							results,
							i + 1,
							sqlTypes[i],
							formats[i]);
				}
			}
			return resultStrings;
		} catch (java.sql.SQLException ex) {
			ex.printStackTrace();
			throw new ShopException (ShopException.DATABASE_CONNECTION_ERROR,
					ex.toString());
		} finally {
			close(results);
			close(st);
			close(conn);
		}
	}

	public static Vector getRecords(
		String sql,
		int[] sqlTypes,
		String[] formats)
		throws ShopException {

		Connection conn = null;
		PreparedStatement st = null;
		ResultSet results = null;
		try {
			conn = getConnection();
			st = conn.prepareStatement(sql);
			results = st.executeQuery();

			Vector records = new Vector();
			while (results.next()) {
				String[] resultStrings = new String[sqlTypes.length];
				int numFields = resultStrings.length;
				if (formats == null) {
					for (int i = 0; i < numFields; i++) {
						resultStrings[i] =
							getFormattedValue(
								results,
								i + 1,
								sqlTypes[i],
								null);
					}
				} else {
					for (int i = 0; i < numFields; i++) {
						resultStrings[i] =
							getFormattedValue(
								results,
								i + 1,
								sqlTypes[i],
								formats[i]);
					}
				}
				records.addElement(resultStrings);
			}
			return records;
		} catch (java.sql.SQLException ex) {
			ex.printStackTrace();
			throw new ShopException (ShopException.DATABASE_CONNECTION_ERROR,
					ex.toString());
		} finally {
			close(results);
			close(st);
			close(conn);
		}
	}
	
	public static String getField(
		String sql,
		int sqlType,
		String format)
		throws ShopException {

		Connection conn = null;
		PreparedStatement st = null;
		ResultSet results = null;
		try {
			conn = getConnection();
			st = conn.prepareStatement(sql);
			results = st.executeQuery();

			if (!results.next()) {
				return null;
			}

			return getFormattedValue(results, 1, sqlType, format);

		} catch (java.sql.SQLException ex) {
			ex.printStackTrace();
			throw new ShopException (ShopException.DATABASE_CONNECTION_ERROR,
					ex.toString());
		} finally {
			close(results);
			close(st);
			close(conn);
		}
	}

	public static Vector getFields(
		String sql,
		int sqlType,
		String format)
		throws ShopException {

		Connection conn = null;
		PreparedStatement st = null;
		ResultSet results = null;

		try {
			conn = getConnection();
			st = conn.prepareStatement(sql);
			results = st.executeQuery();
			Vector fieldVec = new Vector();
			while (results.next()) {
				fieldVec.addElement(
					getFormattedValue(results, 1, sqlType, format));
			}

			return fieldVec;

		} catch (java.sql.SQLException ex) {
			ex.printStackTrace();
			throw new ShopException (ShopException.DATABASE_CONNECTION_ERROR,
					ex.toString());
		} finally {
			close(results);
			close(st);
			close(conn);
		}
	}

	public static boolean execute(String sql)
		throws ShopException {

		Connection conn = null;
		PreparedStatement st = null;

		try {
			conn = getConnection();
			st = conn.prepareStatement(sql);
			boolean result = st.execute();
			return result;
		} catch (java.sql.SQLException ex) {
			ex.printStackTrace();
			throw new ShopException (ShopException.DATABASE_CONNECTION_ERROR,
					ex.toString());
		} finally {
			close(st);
			close(conn);
		}
	}

	public static boolean execute(
		String sql,
		Object[] params)
		throws ShopException {

		Connection conn = null;
		PreparedStatement st = null;

		try {
			conn = getConnection();
			st = conn.prepareStatement(sql);

			if (params != null) {

				int numParams = params.length;
				for (int i = 0; i < numParams; i++) {
					st.setObject(i + 1, params[i]);
				}
			}
			boolean result = st.execute();
			return result;
		} catch (java.sql.SQLException ex) {
			ex.printStackTrace();
			throw new ShopException (ShopException.DATABASE_CONNECTION_ERROR,
					ex.toString());
		} finally {
			close(st);
			close(conn);
		}
	}
		
	public static String getFormattedValue(
		ResultSet rs,
		int colIndex,
		int colType,
		String format)
		throws SQLException {

		switch (colType) {
			case java.sql.Types.INTEGER :
				if (format == null)
					return Integer.toString(rs.getInt(colIndex));
				else
					return new DecimalFormat(format).format(
						rs.getInt(colIndex));
			case java.sql.Types.FLOAT :
				if (format == null)
					return Float.toString(rs.getFloat(colIndex));
				else
					return new DecimalFormat(format).format(
						rs.getFloat(colIndex));
			case java.sql.Types.TIMESTAMP :
				if (format == null)
					return rs.getTimestamp(colIndex).toString();
				else
					return new SimpleDateFormat(format).format(
						rs.getTimestamp(colIndex));
			case java.sql.Types.DATE :
				java.sql.Date date = rs.getDate(colIndex);
				if (date == null)
					return "N/A";
				if (format == null)
					return date.toString();
				else
					return new SimpleDateFormat(format).format(date);
			case java.sql.Types.BIT :
				//if (format == null)
				return new Boolean(rs.getBoolean(colIndex)).toString();
			default :
				return rs.getString(colIndex);
		}
	}	

	public static void close(Connection con) {
		if (con != null) {
		  try {
			con.close();
		  } catch (SQLException e) {
		  }
		}		
	}
	public static void close(ResultSet rs) {
		if (rs != null) {
		  try {
			rs.close();
		  } catch (SQLException e) {
		  }
		}		
	}
	public static void close(java.sql.Statement st) {
		if (st != null) {
		  try {
			st.close();
		  } catch (SQLException e) {
		  }
		}		
	}

}