jdbconnection.java

使用java

package com.tool;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

//这是一个工具类实现与数据库的连接
public class JDBConnection {
  String sd = "sun.jdbc.odbc.JdbcOdbcDriver";
 String sc = "jdbc:odbc:db_Client";
// Connection conect = null;
 public static Connection getConn(){
		Connection conn = null;
		try {
			Context context = new InitialContext();
			DataSource datasource = (DataSource) context
					.lookup("java:comp/env/jdbc/client");
			conn = datasource.getConnection();
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
 }

 public static ResultSet executeQuery(Connection conect,Statement stmt,String sql) {
	 ResultSet rs = null;
   try {
	 conect=getConn();
     stmt = conect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                             ResultSet.CONCUR_READ_ONLY);
     rs = stmt.executeQuery(sql);
   }
   catch (SQLException ex) {
     System.err.println(ex.getMessage());
   }
   return rs;
 }

 public static int executeUpdate(String sql) {
   int result = 0;
	 Statement stmt=null;
	 Connection conect=null;
   try {
	 conect=getConn();
	 stmt = conect.createStatement();
     result = stmt.executeUpdate(sql);
   }
   catch (SQLException er) {
     System.err.println(er.getMessage());
   }finally{
	   close(stmt,conect);
   }
   return result;
 }

  //设置提交
  public static void setAutoCommit(boolean flag) {
    try {
    	getConn().setAutoCommit(flag);
    }
    catch (Exception exception) {
      System.out.println("Exception in set autoCommit :" + exception.getMessage());
    }
  }

  //执行提交
  public static void commit() {
    try {
      getConn().commit();
    }
    catch (Exception exception) {
      System.out.println("Exception in commit :" + exception.getMessage());
    }
  }

  //执行回滚
  public static void rollback() {
    try {
      getConn().rollback();
    }
    catch (Exception exception) {
      System.out.println("Exception in rollback :" + exception.getMessage());
    }
  }

  //将sql语句中的？号转为相应的参数 ，其中参数为字符串型的
  public static String editSqlCode(String sql, String str) {

    try {
      // 截取从0到第一个问号的字符串加参数str，然后在加上第一问号以后的字符串
      sql = sql.substring(0, sql.indexOf("?")) + str +
          sql.substring(sql.indexOf("?") + 1);
    }
    catch (Exception e) {
      System.out.println(e.getMessage());
    }
    return sql;
  }

  //将sql语句中的？号转为相应的参数 ，其中参数为长整型的
  public static String editSqlCode(String sql, Integer str) {

    try {
      sql = sql.substring(0, sql.indexOf("?")) + str +
          sql.substring(sql.indexOf("?") + 1);

    }
    catch (Exception e) {
      System.out.println(e.getMessage());
    }
    return sql;
  }

  //将sql语句中的？号转为相应的参数 ，其中参数为整型的
  public static String editSqlCode(String sql, int str) { //提取SQL语句，并把(int str)传出来的值来代替？

    try {
      sql = sql.substring(0, sql.indexOf("?")) + str +
          sql.substring(sql.indexOf("?") + 1);
    }
    catch (Exception e) {
      System.out.println(e.getMessage());
    }
    return sql;
  }
  //将sql语句中的？号转为相应的参数 ，其中参数为整型的
  public static String editSqlCode(String sql, Float str) { //提取SQL语句，并把(Float str)传出来的值来代替？

    try {
      sql = sql.substring(0, sql.indexOf("?")) + str +
          sql.substring(sql.indexOf("?") + 1);
    }
    catch (Exception e) {
      System.out.println(e.getMessage());
    }
    return sql;
  }
  
  /**
	 * 关闭所有连接.
	 * 
	 * @param rs
	 *            结果集.
	 * @param stmt
	 *            语句对象.
	 * @param conn
	 *            连接对象.
	 */
	public static void close(Statement stmt, Connection conn) {
		if (stmt != null) {
			try {
				stmt.close();
				stmt = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		if (conn != null) {
			try {
				conn.close();
				conn = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	
	public static void close(ResultSet rs,Statement stmt, Connection conn) {	
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		if (stmt != null) {
			try {
				stmt.close();
				stmt = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}

		if (conn != null) {
			try {
				conn.close();
				conn = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void closeRs(ResultSet rs){
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}