dbutil.java

这是一个基于大唐电信的一个任务管理模块程序

package com.datang.struts.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import com.datang.struts.common.DbCommon;
import com.datang.struts.dto.TangPlanPro;

public class DbUtil {
	// 连接数据库接口Connection
	static Connection conn;

	static Statement s;

	// 连接数据库url
	private static String oracle_url;

	// 连接数据库用户名
	private static String oracle_username;

	// 连接数据库密码
	private static String oracle_psw;

	/**
	 * 获得数据库接口Connection
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		try {
			// 初始化数据库连接信息，如果已经初始化跳过
			if (oracle_url == null || oracle_username == null
					|| oracle_psw == null) {
				Properties props = new Properties();
				DbUtil db = new DbUtil();
				InputStream is = db.getClass().getClassLoader().getResourceAsStream("OracleSetup.properties");
				props.load(is);
				oracle_url = props.getProperty("oracle_url");
				oracle_username = props.getProperty("oracle_username");
				oracle_psw = props.getProperty("oracle_psw");
				is.close();
			}
			if (DbCommon.DEBUG) {
				System.out.println(oracle_url);
				System.out.println(oracle_username);
				System.out.println(oracle_psw);
			}

			// 导入oracle数据库驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(oracle_url, oracle_username,
					oracle_psw);
			return conn;
		} catch (ClassNotFoundException e) {
			if (DbCommon.DEBUG)
				e.printStackTrace();
		} catch (FileNotFoundException e) {
			if (DbCommon.DEBUG)
				e.printStackTrace();
		} catch (IOException e) {
			if (DbCommon.DEBUG)
				e.printStackTrace();
		} catch (SQLException e) {
			if (DbCommon.DEBUG)
				e.printStackTrace();
		}
		return null;
	}

	/**
	 * 获得一个新的connection接口
	 * 
	 * @return
	 */
	public Connection getNewConnection() {
		FileInputStream fi = null;
		// 初始化数据库连接信息，如果已经初始化跳过
		try {
			if (oracle_url == null || oracle_username == null
					|| oracle_psw == null) {
				Properties props = new Properties();
				File f = new File(DbCommon.ORACLESETUPPATH);
				fi = new FileInputStream(f);
				props.load(fi);
				oracle_url = props.getProperty("oracle_url");
				oracle_username = props.getProperty("oracle_username");
				oracle_psw = props.getProperty("oracle_psw");
				fi.close();
			}
			if (DbCommon.DEBUG) {
				System.out.println(oracle_url);
				System.out.println(oracle_username);
				System.out.println(oracle_psw);
			}

			// 导入oracle数据库驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			Connection newconn = DriverManager.getConnection(oracle_url,
					oracle_username, oracle_psw);
			return newconn;

		} catch (FileNotFoundException e) {
			if (DbCommon.DEBUG)
				e.printStackTrace();
		} catch (IOException e) {
			if (DbCommon.DEBUG)
				e.printStackTrace();
		} catch (ClassNotFoundException e) {
			if (DbCommon.DEBUG)
				e.printStackTrace();
		} catch (SQLException e) {
			if (DbCommon.DEBUG)
				e.printStackTrace();
		}
		return null;
	}

	/**
	 * 
	 * @param sql
	 * @return
	 */
	public ResultSet select(String sql) {
		try {
			if (conn == null || conn.isClosed()) {
				conn = getConnection();
			}
			s = conn.createStatement();
			ResultSet rs = s.executeQuery(sql);
			return rs;
		} catch (SQLException e) {
			if (DbCommon.DEBUG)
				e.printStackTrace();
		}
		return null;
	}

	/**
	 * 
	 * @param sql
	 * @return
	 */
	public int update(String sql) {
		Statement s1 = null;
		try {
			if (conn == null || conn.isClosed()) {
				conn = getConnection();
			}

			s1 = conn.createStatement();
			int i = s1.executeUpdate(sql);
			return i;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				s1.close();
			} catch (SQLException e) {
				if (DbCommon.DEBUG)
					e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				if (DbCommon.DEBUG)
					e.printStackTrace();
			}
		}
		return -1;
	}

	/**
	 * 
	 * 
	 */
	public void close() {
		try {
			s.close();
		} catch (SQLException e) {
			if (DbCommon.DEBUG)
				e.printStackTrace();
		}
		try {
			conn.close();
		} catch (SQLException e) {
			if (DbCommon.DEBUG)
				e.printStackTrace();
		}
	}
	
	/**
	 * 拼出一条给定条件的查询计划sql语句 
	 * @param planpro
	 * @return
	 */
	public static String appendSelectPlanSql(TangPlanPro planpro){
		StringBuffer sqlbuff = new StringBuffer();
		sqlbuff.append("SELECT * FROM tang_plan WHERE clerk_id='");
		sqlbuff.append(planpro.getClerk_id());
		sqlbuff.append("'");
		sqlbuff.append(" AND task_id=");
		sqlbuff.append(planpro.getTask_id());
		
		if(planpro.getPlan_name() != null){
			sqlbuff.append(" AND plan_name='");
			sqlbuff.append(planpro.getPlan_name());
			sqlbuff.append("'");
		}
		if(planpro.getPlan_start_fir()!=null && planpro.getPlan_start_sec()!=null){
			sqlbuff.append(" AND (plan_start BETWEEN to_date('");
			sqlbuff.append(planpro.getPlan_start_fir());
			sqlbuff.append("','yyyy-mm-dd') AND to_date('");
			sqlbuff.append(planpro.getPlan_start_sec());
			sqlbuff.append("','yyyy-mm-dd'))");
		}
		if(planpro.getPlan_end_fir()!=null && planpro.getPlan_end_sec()!=null){
			sqlbuff.append(" AND (plan_end BETWEEN to_date('");
			sqlbuff.append(planpro.getPlan_end_fir());
			sqlbuff.append("','yyyy-mm-dd') AND to_date('");
			sqlbuff.append(planpro.getPlan_end_sec());
			sqlbuff.append("','yyyy-mm-dd'))");
		}
		if(planpro.getPlan_feedback() != null){
			sqlbuff.append(" AND plan_feedback='");
			sqlbuff.append(planpro.getPlan_feedback());
			sqlbuff.append("'");
		}
		String sql = sqlbuff.toString();
		if(DbCommon.DEBUG){
			System.out.println("appendSelectSql is:"+sql);
		}
		return sql;
	}
	
	public static void main(String[] args){
//		Connection conn = DbUtil.getConnection();
//		System.out.println("success");
//		try {
//			conn.close();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
		TangPlanPro planpro = new TangPlanPro();
		planpro.setClerk_id("C0001");
		planpro.setTask_id(10);
		planpro.setPlan_name("gogo");
		planpro.setPlan_start_fir(Date.valueOf("2008-5-5"));
		planpro.setPlan_start_sec(Date.valueOf("2008-5-6"));
		planpro.setPlan_end_fir(Date.valueOf("2008-6-6"));
		planpro.setPlan_end_sec(Date.valueOf("2008-6-7"));
		planpro.setPlan_feedback("NO");
		String sql = appendSelectPlanSql(planpro);
		System.out.println(sql);
	}
	
}