jdbconnection.java

一个真实的网络选课系统,由实际需求得到的,本人实际开发出来,运用JSP+TOMCAT

package com;
import java.sql.*;

import javax.naming.Context;
import javax.naming.InitialContext;

import log.CreateLogs;
public class JDBConnection {
 private Connection con = null;
 private Statement allstmt= null;

 public JDBConnection() {
     
 }

//创建数据库连接
 public Connection creatConnection() {
         try   {   
                 Context   initCtx   =   new   InitialContext();   
                 Context   ctx   =   (Context)   initCtx.lookup("java:comp/env");   
                 //获取连接池对象   
                 Object   obj   =   (Object)   ctx.lookup("jdbc/test");   
                 //类型转换   
                 javax.sql.DataSource   ds   =   (javax.sql.DataSource)   obj;   
                 Connection   conn   =   ds.getConnection();   
                 return   conn; 
         }   
         catch   (Exception   ex)   {  
        	 CreateLogs.createLog(ex, "JDBConnection.java");
                 ex.printStackTrace();   
                 return   null;   
         }  
 }

//对数据库的增加、修改和删除的操作
 public boolean executeUpdate(String sql) {

     if (con == null) {
         con=creatConnection();
     }
     try {
         Statement stmt = con.createStatement();
         int iCount = stmt.executeUpdate(sql);
         System.out.println("操作成功，所影响的记录数为" + String.valueOf(iCount));
       //  stmt.close();
         con.close(); 
         con   =   null; 
         return true;
     } catch (SQLException e) {
    	 CreateLogs.createLog(e, "JDBConnection.java");
    	 System.out.println("数据更新失败");
         System.out.println(e.getMessage());
         return false;
     }
     finally{   
         if(con   !=   null){ 
        	con=null;
           }   
     }
     
 }

//对数据库的查询操作
 public ResultSet executeQuery(String sql) {
     ResultSet rs;
     try {
         if (con == null) {
        	 con=creatConnection();
         }
         allstmt = con.createStatement();
         rs = allstmt.executeQuery(sql);
     	} catch (SQLException e) {
     		CreateLogs.createLog(e, "JDBConnection.java");
     	 System.out.println("数据查询失败");
         System.out.println(e.getMessage());
         return null;
     }
     return rs;
 }
 
//插入学生的选择
public boolean insertSelInfo(int week,int time,int value,String stdno)
{
	if (con == null) {
		con=creatConnection();
    }
	ResultSet rs;
    try {
        allstmt = con.createStatement();
        String quer2="select * from selectInfo where t_week="+week+" and stdno='"+stdno+"' and v_date="+value;
        rs=allstmt.executeQuery(quer2);
        if(rs.next())
        {
        	System.out.println("学生插入数据失败，不能同一天内选两个!");
        	closeConnection() ;
        	return false;
        }
        closeStatement();
    	allstmt = con.createStatement();
        String query="select * from selectInfo where t_week="+week+" and t_hour="+time+" and v_date="+value;
        rs=allstmt.executeQuery(query);
        if(rs.next())
        {
        	System.out.println("学生插入数据失败，记录已存在!");
        	closeConnection() ;
        	return false;
        }
        closeStatement();
    	allstmt = con.createStatement();
        String insert="insert into selectInfo (t_week,t_hour,stdno,v_date)values ("+week+","+time+",'"+stdno+"',"+value+")";
        int iCount = allstmt.executeUpdate(insert);
        System.out.println("学生插入数据成功，所影响的记录数为" + String.valueOf(iCount));
        closeConnection() ;
        return true;
    } catch (SQLException e) {
    	CreateLogs.createLog(e, "JDBConnection.java");
    	System.out.println("学生插入数据失败!");
        System.out.println(e.getMessage());
        System.out.println(e.getStackTrace());
        return false;
    }
    
}
//管理员更新选择信息
public boolean updateSelInfo(int week,int time,int value,String stdno)
{
	if (con == null) {
		con=creatConnection();
    }
	ResultSet rs;
    try {
        allstmt = con.createStatement();
        String query="select * from selectInfo where t_week="+week+" and t_hour="+time+" and v_date="+value;
        rs=allstmt.executeQuery(query);
        if(rs.next())
        {
        	closeStatement();
	    	allstmt = con.createStatement();
        	String update="update selectInfo set stdno='"+stdno+"' where t_week="+week+" and t_hour="+time+" and v_date="+value;
        	int iCount = allstmt.executeUpdate(update);
            System.out.println("管理员更新数据成功,所影响的记录数为" + String.valueOf(iCount));
        }
        else
        {
        	closeStatement();
	    	allstmt = con.createStatement();
	        String insert="insert into selectInfo (t_week,t_hour,stdno,v_date)values ("+week+","+time+",'"+stdno+"',"+value+")";
	        int iCount = allstmt.executeUpdate(insert);
	        System.out.println("管理员插入数据成功，所影响的记录数为" + String.valueOf(iCount));
        }
        closeConnection();
        return true;
    } catch (SQLException e) {
    	CreateLogs.createLog(e, "JDBConnection.java");
    	System.out.println("管理员更新数据失败!");
        e.printStackTrace();
        return false;
    }
    
}



//管理员更新上课信息
public boolean updateWeekInfo(int week,int date,boolean value)
{
	if (con == null) {
		con=creatConnection();
	}
	try {
		  ResultSet rs;
	      allstmt = con.createStatement();
	      String query="select * from weekInfo where t_week="+week+"  and v_date="+date;
	      rs=allstmt.executeQuery(query);
	      if(rs.next())
	      {
	    	closeStatement();
	    	allstmt = con.createStatement();
	      	String update="update weekInfo set wkvalue="+value+" where t_week="+week+" and v_date="+date;
	      	int iCount = allstmt.executeUpdate(update);
	        System.out.println("管理员更新数据成功,所影响的记录数为" + String.valueOf(iCount));
	      }
	      else
	      {
	    	 closeStatement();;
	     	 allstmt = con.createStatement();
	    	 String insert="insert into weekInfo (t_week,v_date,wkvalue)values ("+week+","+date+","+value+")";
	    	 int iCount = allstmt.executeUpdate(insert);
	         System.out.println("管理员插入数据成功，所影响的记录数为" + String.valueOf(iCount));
	      }
	      closeConnection();
	      return true;
	  } catch (SQLException e) {
		  CreateLogs.createLog(e, "JDBConnection.java");
	  	  System.out.println("管理员更新数据失败!");
	      e.printStackTrace();
	      return false;
	  }
}


//关闭数据库

public void closeConnection() {
    
        try 
        {
        	if(allstmt!=null){
        	allstmt.close();
        	allstmt=null;
        	}
        	if (con != null) {
            con.close();
            con=null;
        	}
        //	System.out.println("关闭数据库按成功");
        } 
        catch (SQLException e) 
        {
        	CreateLogs.createLog(e, "JDBConnection.java");
            e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
            System.out.println("关闭数据库失败");
        } 
        finally {
        	allstmt=null;
            con = null;
        }
	}
public void closeStatement(){
		try 
	    {
	    	if(allstmt!=null){
	    	allstmt.close();
	    	allstmt=null;
	    	}
	    	// System.out.println("关闭stmt成功");
	    } 
	    catch (SQLException e) 
	    {
	    	CreateLogs.createLog(e, "JDBConnection.java");
	        e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
	        System.out.println("关闭stmt失败");
	    } 
	    finally {
	    	allstmt=null;
	    }
	}

}