sqlutil.java

一套购物车项目。电子商务系统。实现了前台和后台的业务逻辑。

package com.softfz.jn0708.util;

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

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

/**
 * 连接池  数据库的连接
 * @author student
 *
 */
public class SqlUtil {
	
	//连接池
	public static Connection getConn(){
		Connection conn = null;
		try {
			Context context = new InitialContext();
			DataSource ds = (DataSource)context.lookup("java:comp/env/jdbc/oracle");
			conn = ds.getConnection();
		} catch (NamingException e) {		
			e.printStackTrace();
		} catch (SQLException e) {			
			e.printStackTrace();
		}
		return conn;
	}
	
	//JDBC
//	public static Connection getConn(){
//		Connection conn = null;
//		String url="jdbc:oracle:thin:@localhost:1521:STUDENT";
//		String user="kaka";
//		String password="kaka";
//		try {
//			Class.forName("oracle.jdbc.driver.OracleDriver");
//			conn=DriverManager.getConnection(url,user,password);
//		} catch (ClassNotFoundException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		return conn;
//	}


	public static void tryToClose(ResultSet rs, PreparedStatement ps, Connection conn) {
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(ps!=null){
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	/**
	 * 关闭数据库结果集 statement 和连接
	 *
	 * 
	 */
	public static void tryClose(ResultSet rs, Statement ps, Connection conn) {
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(ps!=null){
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	/**
	 * 根据sql语句更新数据库操作包含，新增，修改，删除
	 * @param sql
	 * @return
	 */
	public static boolean execteUpdate(String sql) {
		Connection conn = SqlUtil.getConn();
		PreparedStatement st = null;
		
		try {
			conn.setAutoCommit(false);//设置不自动提交
			st = conn.prepareStatement(sql);
			int i=st.executeUpdate(sql);
			conn.commit();//提交事务
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			try {
				conn.rollback();//事务回顾
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			SqlUtil.tryClose(null,st,conn);
		}
		
		return false;
	}
	


	/**
	 * 根据多条数据库操作语句执行带事务的更新数据库操作，包含新增，修改，删除
	 * @param sql
	 * @return
	 */
	public static boolean executeUpdates(String[] sql){
		Connection conn = SqlUtil.getConn();
		Statement st = null;
		
		try {
			conn.setAutoCommit(false);//设置不自动提交
			st = conn.createStatement();
			for(int i=0;i<sql.length;i++){
				st.addBatch(sql[i]);
			}			
			int[] re=st.executeBatch();
			conn.commit();//提交事务
			if(re.length>0){
				return true;
			}
		} catch (SQLException e) {
			try {
				conn.rollback();//事务回顾
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			SqlUtil.tryClose(null,st,conn);
		}
		
		return false;
	}
}