dbconnector.java

基于struts的网上商店源码

package com.mole.struts.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.mole.struts.bean.CustomerRecordCommentBean;

//数据库连接器原形
public class DBConnector {
	private Connection conn;

	public DBConnector() {
		System.out.println("Data source init...");
		try {
			Context ctx = new InitialContext();
			if (ctx == null)
				throw new Exception("Failed to initial context!");
			DataSource ds = (DataSource) ctx
					.lookup("java:comp/env/jdbc/crmdata");
			// String ConnectionUrl="jdbc:sqlserver://192.168.10.9:1433";
			// String ClassName ="com.microsoft.sqlserver.jdbc.SQLServerDriver";
			// Class.forName(ClassName);
			long start = System.currentTimeMillis();
			conn = ds.getConnection();
			// conn=DriverManager.getConnection(ConnectionUrl,"crm","crm");
			long end = System.currentTimeMillis();
			System.out.println("执行时间：" + (end - start));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 获取评论信息
	public CustomerRecordCommentBean getComment(String sql) {
		if (conn == null)
			try {
				conn = getConn();
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		ResultSet rs = null;
		CustomerRecordCommentBean bean = new CustomerRecordCommentBean();
		PreparedStatement ps = null;
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				bean.setComment(rs.getString(1));
				bean.setReleaseDate(String.valueOf(rs.getDate(2)));
				bean.setGrade(String.valueOf(rs.getInt(3)));
			}
			return bean;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (ps != null)
				try {
					ps.close();
					return bean;
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

	}

	public Connection getConn() throws Exception {
		return conn;
	}

	// 执行更新语句
	public int executeUpdate(String sql, Object... args) throws Exception {
		int result = 0;
		PreparedStatement ps = null;
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			for (int i = 1; i <= args.length; i++)
				ps.setObject(i, args[i - 1]);
			result = ps.executeUpdate();
			return result;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 执行查询语句
	public ArrayList<Object[]> executeQuery(String sql, Object... args)
			throws Exception {
		ResultSet rs = null;
		Object[] ret = null;
		PreparedStatement ps = null;
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			for (int i = 1; i <= args.length; i++)
				ps.setObject(i, args[i - 1]);
			rs = ps.executeQuery();
			while (rs.next()) {
				ret = new Object[rs.getMetaData().getColumnCount()];
				for (int i = 0; i < rs.getMetaData().getColumnCount(); i++)
					ret[i] = rs.getObject(i + 1);
				al.add(ret);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 执行存储过程
	public ArrayList<Object[]> executeStoredProcedure(String spName,
			Object... args) throws Exception {
		ResultSet rs = null;
		Object[] ret = null;
		CallableStatement stmt = null;
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		try {
			conn.setAutoCommit(true);
			stmt = conn.prepareCall("{call " + spName + "}");
			for (int i = 1; i <= args.length; i++)
				stmt.setObject(i, args[i - 1]);
			rs = stmt.executeQuery();
			while (rs.next()) {
				ret = new Object[rs.getMetaData().getColumnCount()];
				for (int i = 0; i < rs.getMetaData().getColumnCount(); i++)
					ret[i] = rs.getObject(i + 1);
				al.add(ret);
			}
			return al;
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}
}