dataaccessobject.java

一个很使用的例子

package org.kk.model;

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



public class DataAccessObject {
	private String driver="oracle.jdbc.driver.OracleDriver";
	private String url="jdbc:oracle:thin:@localhost:1521:ORCL";
	private String username="scott";
	private String password="tiger";
	private Connection con=null;
	
	public DataAccessObject(){
		this.initConnection();
	}
	private void initConnection(){
		try {
			Class.forName(driver);
			con=DriverManager.getConnection(url,username,password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public boolean updateemp(Information infor,String model){
		boolean ok=true;
		try {
			CallableStatement cal=con.prepareCall("{call pro_user(?,?,?,?,?,?,?)}");
			cal.setString(1,infor.getUSERID());
			cal.setString(2,infor.getUSERNAME());
			cal.setString(3,infor.getUSERPASS());
			cal.setString(4,infor.getSEX());
			cal.setString(5,infor.getTELEPHONE());
			cal.setString(6,infor.getEMAIL());
			cal.setString(7,model);
			cal.executeUpdate();
			con.close();
		} catch (SQLException e) {
			ok=false;
			e.printStackTrace();
		}
		return ok;
	}
	public boolean updatemer(Information infor,String model){
		boolean ok=true;
		try {
			CallableStatement cal=con.prepareCall("{call pro_mer(?,?,?,?,?,?)}");
			cal.setString(1,infor.getTRADEID());
			cal.setString(2,infor.getTRADENAME());
			cal.setString(3,infor.getTYPE());
			cal.setString(4,infor.getPRICE());
			cal.setString(5,infor.getMANUFACTURER());
			cal.setString(6,model);
			cal.executeUpdate();
			con.close();
		} catch (SQLException e) {
			ok=false;
			e.printStackTrace();
		}
		return ok;
	}
	public ArrayList findemp(String id){
		ArrayList list=new ArrayList();
		Information infor=null;
		try {
			CallableStatement cal=con.prepareCall("{call pak_user.get(?,?)}");
			cal.setString(1,id);
			cal.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
			cal.execute();
			ResultSet rs=(ResultSet)cal.getObject(2);
			while(rs.next()){
				infor=new Information();
				infor.setUSERID(rs.getString("USERID"));
				infor.setUSERNAME(rs.getString("USERNAME"));
				infor.setUSERPASS(rs.getString("USERPASS"));
				infor.setSEX(rs.getString("SEX"));
				infor.setTELEPHONE(rs.getString("TELEPHONE"));
				infor.setEMAIL(rs.getString("EMAIL"));
				list.add(infor);
			}
			rs.close();
			con.close();
		} catch (SQLException e) {
			list=null;
			e.printStackTrace();
		}
		return list;
	}
	public ArrayList findmer(String id){
		ArrayList list=new ArrayList();
		Information infor=null;
		try {
			CallableStatement cal=con.prepareCall("{call pak_mer.get(?,?)}");
			cal.setString(1,id);
			cal.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
			cal.execute();
			ResultSet rs=(ResultSet)cal.getObject(2);
			while(rs.next()){
				infor=new Information();
				infor.setTRADEID(rs.getString("TRADEID"));
				infor.setTRADENAME(rs.getString("TRADENAME"));
				infor.setTYPE(rs.getString("TYPE"));
				infor.setPRICE(rs.getString("PRICE"));
				infor.setMANUFACTURER(rs.getString("MANUFACTURER"));
				list.add(infor);
			}
			rs.close();
			con.close();
		} catch (SQLException e) {
			list=null;
			e.printStackTrace();
		}
		return list;
	}
	public ArrayList findorder(){
		ArrayList list=new ArrayList();
		Information infor=null;
		String sql="select u.USERNAME,o.ORDERSID,o.BUYDATE from USERS u,USERORDERS o where u.USERID=o.USERID";
		try {
			ResultSet rs=con.createStatement().executeQuery(sql);
			while(rs.next()){
				infor=new Information();
				infor.setUSERNAME(rs.getString("USERNAME"));
				infor.setORDERSID(rs.getString("ORDERSID"));
				infor.setBUYDATE(rs.getString("BUYDATE"));
				list.add(infor);
			}
			con.close();
		} catch (SQLException e) {
			list=null;
			e.printStackTrace();
		}
		return list;
	}
	public ArrayList findorder1(String id){
		ArrayList list=new ArrayList();
		Information infor=null;
		String sql="select t.TRADENAME,t.TYPE,t.PRICE from MERCHANDISES t,ORDERSDETAIL o where t.TRADEID=o.TRADEID and o.ORDERSID='"+id+"'";
		try {
			ResultSet rs=con.createStatement().executeQuery(sql);
			while(rs.next()){
				infor=new Information();
				infor.setTRADENAME(rs.getString("TRADENAME"));
				infor.setTYPE(rs.getString("TYPE"));
				infor.setPRICE(rs.getString("PRICE"));
				list.add(infor);
			}
			con.close();
		} catch (SQLException e) {
			list=null;
			e.printStackTrace();
		}
		return list;
	}
}