JDBC连接详解

JDBC配置详解

配置文件 database.properties

# oracle 
driver_class=oracle.jdbc.OracleDriver
connection.url=jdbc:oracle:thin:@192.168.8.122:1521:orcl
connection.name=SSS2
connection.pwd=SSS2

配置文件读取 ConfigureManager

public class ConfigureManager {
	private static Properties properties = null;
	private static ConfigureManager cm = null;

	private ConfigureManager() {
		properties = new Properties();
		try {
			properties.load(ConfigureManager.class.getClassLoader().getResourceAsStream("database.properties"));
		} catch (IOException e) {
			e.printStackTrace();

		}
	}
	
	public static ConfigureManager getInstance() {
		if(cm==null) {
			cm = new ConfigureManager();
		}
		return cm;
	}
	
	public String getValuesByKey(String key) {
		return properties.getProperty(key);
	}

}

常量 Constrant

public interface Constrant {
	public static final String DRIVER_CLASS = ConfigureManager.getInstance().getValuesByKey("driver_class");
	public static final String URL = ConfigureManager.getInstance().getValuesByKey("connection.url");
	public static final String NAME = ConfigureManager.getInstance().getValuesByKey("connection.name");
	public static final String PWD = ConfigureManager.getInstance().getValuesByKey("connection.pwd");
}

数据库连接类basedao

public class BaseDao {
	protected Connection conn;
	protected PreparedStatement pstmt;
	protected int result;
	protected ResultSet rs;

	// 获取连接对象
	public boolean getConn() {
		boolean flag = false;
		try {
			Class.forName(Constrant.DRIVER_CLASS);
			conn = DriverManager.getConnection(Constrant.URL, Constrant.NAME, Constrant.PWD);
			flag = true;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	// 增删改
	public int executeUpdate(String sql, Object[] params) {
		if (getConn()) {
			try {
				pstmt = conn.prepareStatement(sql);
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(i + 1, params[i]);
				}
				result = pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	// 查询
	public ResultSet executeQuery(String sql, Object[] params) {
		if (getConn()) {
			try {
				pstmt = conn.prepareStatement(sql);
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(i + 1, params[i]);
				}
				rs = pstmt.executeQuery();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rs;
	}

	// 关闭连接
	public void closeResource(Connection conn, PreparedStatement pstmt, ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();

		}
		try {
			if (pstmt != null) {
				pstmt.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();

		}
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();

		}
	}
}

实现层继承方法

public List<Bill> QueryAll() throws Exception {
		List<Bill>list=new ArrayList<Bill>();
		Bill bill=null;
		try {
			String sql="select a.bill_id,a.product_name,b.pro_name,a.bill_money,a.payed,a.op_time from smbms_bill a ,smbms_provider b where a.pro_id=b.pro_id order by a.op_time desc";
			Object[]params={};
			rs=this.executeQuery(sql, params);
			while(rs.next()){
				bill=new Bill(rs.getInt("bill_id"),rs.getString("product_name"),rs.getString("pro_name"),
						rs.getInt("bill_money"),rs.getInt("payed"),rs.getDate("op_time"));
				list.add(bill);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			this.closeResource(conn, pstmt, rs);
		}
		return list;
	}
public int Delete(Integer bill_id) throws Exception {
		try {
			String sql="delete from smbms_bill where bill_id=?";
			Object[]params={bill_id};
			result=this.executeUpdate(sql, params);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			this.closeResource(conn, pstmt, null);
		}
		return result;
	}