package getandset;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class OrcdbAccess{
	private Connection con;//连接
	private PreparedStatement  pstm;
	private String dbIp="127.0.0.1";
	private String dbPort="1521";
	private String dbName="ICSS";
	private String dbUser="jsp";
	private String dbPwd="jsp";
	private String conString="jdbc:oracle:thin:@"+dbIp+":"+dbPort+":"+dbName;
	private static OrcdbAccess dba;//数据库操作类对象
	
	//单例模式，构造函数私有
	private OrcdbAccess(){
		
	}
	
	//单例模式，如果第一次使用则新建，如果不是则直接发送
	public static OrcdbAccess getDbAccess(){
		if(dba==null)
			dba=new OrcdbAccess();
		return dba;
	}
	
	//用户自己指定连接字符串
	public  void setValue(String dbIp,String dbPort,String dbName,String dbUser,String dbPwd){
		this.dbIp=dbIp;
        this.dbPort=dbPort;
        this.dbName=dbName;
        this.dbUser=dbUser;
        this.dbPwd=dbPwd;
       this.conString="jdbc:oracle:thin:@"+dbIp+":"+dbPort+":"+dbName;
	}
	
	//获取数据库连接
	public void getCon(){
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");//加载驱动包
			try{
				con=DriverManager.getConnection(conString,dbUser, dbPwd);//初始化数据库连接
			}catch(Exception e){
				System.out.println("初始化数据库连接失败！");
			}
		}catch(Exception e){
			System.out.println("驱动包加载失败！");
		}
	}
	
	public void excute(String sql){
		if(con==null){//如果是第一次连接，则初始化连接
			getCon();
		}
		
		try {
			pstm=con.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public ResultSet getResultSet(String sql){//根据sql语句返回一个ResultSet结果集
		excute(sql);
		try {
			return pstm.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public String getFirstUnit(String sql){//根据sql语句返回结果集的第一行第一列单元格数据
		excute(sql);
		try {
			String value;
			ResultSet rs=pstm.executeQuery();
			rs.next();
			value=rs.getString(1);
			rs.close();
			return value;//返回第一个单元格数据
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public int getInt(String sql){//返回受影响的行数
		excute(sql);
		
		try {
			return	pstm.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return 0;
	}
	
	public void close(){//当最后退出程序时执行就行了，因为是单例模式，所以不用每次都关
		try {
			pstm.cancel();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}