dbutil.java

基于J2EE技术的 电子购物商城系统

package dataservice;

import java.sql.Date;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

public class DBUtil {
	
	public static void main(String[] args) {
		DBUtil util = new DBUtil();
		try {
			ResultSet rs = util.createStatement().executeQuery("select * from users");
			while (rs.next()) {
				System.out.print(rs.getString(1));
				System.out.print(rs.getString(2));
				System.out.print(rs.getString(3));
				System.out.print(rs.getString(4));
				System.out.print(rs.getString(5));
				System.out.println(rs.getString(6));
				System.out.println(rs.getType());
				
			}
			util.close();
		} catch (SQLException e) {
			System.out.println(util.getErrorMsg());
			e.printStackTrace();
		}
	}
	
	/**
	 * default constructor
	 * connect oracle with SYSTEM_USER/SYSTEM_PASSWORD.
	 * TODO: about configuration
	 * 如果这个系统需要正式运行，这个构造函数必须重写，以满足配置的需要；
	 * 解决方案：
	 * 可以写个DatabaseConfig.xml文件，用于保存数据库系统初始化的信息，
	 * DBUtil类从该文件读取系统运行所需要的数据库配置信息。
	 */
	public DBUtil() {
		this(SYSTEM_USER, SYSTEM_PASSWORD);
	}
	
	/**
	 * @param user:		database user
	 * @param password:	database password
	 */
	public DBUtil(String user, String password) {
		connect(user, password);
	}
	
	public void connect(String user, String password) {
		try {
			Class.forName(DRIVER);
			con = DriverManager.getConnection(URL, user, password);
			errorMsg = "";
		} catch (ClassNotFoundException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
		} catch (SQLException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
		}
	}
	
	public void close() {
		try {
			con.close();
		} catch (SQLException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
		}
	}
	
	public Statement createStatement() {
		try {
			return con.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public boolean execute() {
		try {
			return cstmt.execute();
		} catch (SQLException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
			return false;
		}
	}
	
	public ResultSet getResultSet(int index) {
		Object o = null;
		try {
			o = cstmt.getObject(index);
		} catch (SQLException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
		}
		if (o instanceof ResultSet)
			return (ResultSet)o;
		else
			return null;
	}
	
	public String getString(int index) {
		try {
			return cstmt.getString(index);
		} catch (SQLException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
		}
		return null;
	}
	
	public int getInt(int index) {
		try {
			return cstmt.getInt(index);
		} catch (SQLException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
		}
		return 0;
	}
	
	public Date getDate(int index) {
		try {
			return cstmt.getDate(index);
		} catch (SQLException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
		}
		return null;
	}
	
	public CallableStatement prepareCall(String sql) {
		try {
			return (cstmt=con.prepareCall(sql));
		} catch (SQLException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
		}
		return null;
	}
	
	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) {
		try {
			return (cstmt=con.prepareCall(sql, resultSetType, resultSetConcurrency));
		} catch (SQLException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
		}
		return null;
	}
	
	public CallableStatement prepareCall(String sql, int resultSetType,
				int resultSetConcurrency, int resultSetHoldability) {
		return (cstmt=prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability));
	}
	
	public void registerOutParameter(int index, int sqlType) {
		try {
			cstmt.registerOutParameter(index, sqlType);
		} catch (SQLException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
		}
	}
	
	public void setString(int index, String value) {
		try {
			cstmt.setString(index, value);
		} catch (SQLException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
		}
	}
	
	public void setInt(int index, int value) {
		try {
			cstmt.setInt(index, value);
		} catch (SQLException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
		}
	}
	
	public void setDouble(int index, double value) {
		try {
			cstmt.setDouble(index, value);
		} catch (SQLException e) {
			errorMsg = e.getMessage();
			e.printStackTrace();
		}
	}
	
	/**
	 * @return Returns the con.
	 */
	public Connection getCon() {
		return con;
	}
	/**
	 * @param con The con to set.
	 */
	public void setCon(Connection con) {
		this.con = con;
	}
	/**
	 * @return Returns the cstmt.
	 */
	public CallableStatement getCstmt() {
		return cstmt;
	}
	/**
	 * @param cstmt The cstmt to set.
	 */
	public void setCstmt(CallableStatement cstmt) {
		this.cstmt = cstmt;
	}
	/**
	 * @return Returns the errorMsg.
	 */
	public String getErrorMsg() {
		return errorMsg;
	}
	
	Connection con = null;
	CallableStatement cstmt = null;
	
	String errorMsg = null;
	
	final static String HOST = "127.0.0.1";
	final static int PORT = 1521;
	final static String DATABASE = "ORACLE";//FIXME!DATABASE 
	
	final static String DRIVER = "oracle.jdbc.OracleDriver";
	final static String URL = "jdbc:oracle:thin:@" + HOST + ":" + PORT + ":" + DATABASE;
	
	final static String ISHOP_ADMIN = "ishop_admin";
	final static String ISHOP_ADMIN_PASSWORD = "ishop_admin";
	
	final static String ISHOP_USER = "ishop_user";
	final static String ISHOP_USER_PASSWORD = "ishop_user";
	
	final static String SYSTEM_USER = "system";
	final static String SYSTEM_PASSWORD = "system";
}