easyjdbengine.java

简易java框架开源论坛系统,简 易java框架开源论坛系统

package com.easyjf.dbo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.easyjf.dbo.config.DBOConfig;
import com.easyjf.dbo.sql.*;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
/**
 * 数据库处理引擎，实现DBObject对象到数据库的转换，完成具体的数据库操作
 * @author 蔡世友
 *
 */
public class EasyJDBEngine {
private final static Logger logger = Logger.getLogger(EasyJDBEngine.class);
private final static Map engines=new HashMap(); 
private DataSource dataSource;
private  ISqlQuery sqlQuery;

public EasyJDBEngine(DataSource dataSource)
{
	this(dataSource,new MySqlQuery());
}
public EasyJDBEngine(DataSource dataSource,ISqlQuery sqlQuery)
{
	this.dataSource=dataSource;
	this.sqlQuery=sqlQuery;
}
public static EasyJDBEngine getInstance()//数据库操作，将会修改为由配置文件中读取参数
{
	EasyJDBEngine engine=null;
	if(engines.isEmpty()){
	logger.info("初始化数据库查询引擎！");
	ISqlQuery dialect=null;
	try{
		dialect=(ISqlQuery)Class.forName(DBOConfig.getInstance().getDialect()).newInstance();
	}
	catch (Exception e) 
	{
		logger.error("数据库类型(方言)配置错误，系统将使用默认MySQL数据类型方言！\n"+e);
	}
	if(dialect==null)dialect=new MySqlQuery();
	DataSource ds=null;
	try{
	ds=DataSourceManager.getDataSource();
	}
	catch(EasyDBOException e)
	{
		logger.warn("通过EasyDBO配置文件easyjf-dbo.xml文件创建数据源错误,请在程序中手动设置EasyDBO数据源，详情请查www.easyjf.com！\n"+e);
	}
	engine=new EasyJDBEngine(ds,dialect);	
	}
	if(engine.getDataSource()==null)logger.warn("通过EasyDBO的配置文件无法读取数据源配置信息，若要使用EasyDBO，必须在应用程序中手动设置数据源，详情请到www.easyjf.com查询！");
	return engine;
}
/**
 * 初始化数据源
 *
 */
public static void init()
{
	if(!engines.isEmpty())engines.clear();
	Map sources=null;
	try{
	sources=DataSourceManager.getDataSources();
	}
	catch(EasyDBOException e)
	{
	logger.warn("通过EasyDBO配置文件easyjf-dbo.xml文件创建数据源错误,请在程序中手动设置EasyDBO数据源，详情请查www.easyjf.com！\n"+e);	
	}
	if(sources!=null){
	Iterator it=sources.keySet().iterator();
	while(it.hasNext())
	{
		String name=(String)it.next();
		DataSource dsource=(DataSource)sources.get(name);
		if(dsource!=null)engines.put(name,dsource);
	}
	}		
}

public static EasyJDBEngine getInstance(String name)//数据库操作，将会修改为由配置文件中读取参数
{
	EasyJDBEngine engine=null;
	return engine;
}
public Object uniqueResult(String sql)
{
    return uniqueResult(sql,null);
}
public Object uniqueResult(String sql,Collection params)
{
	if(dataSource==null)
	{
		logger.error("数据源不可用，请先设置EasyDBO数据源！详情请查询www.easyjf.com！");
		return null;		
		}
	Object ret=null;
	Connection conn = null;
	PreparedStatement ps = null;	
	ResultSet rs = null;
	try{
        conn = dataSource.getConnection();
   	}
   	catch(SQLException e)
   	{
   	logger.error("无没取得数据源，请确认数据源配置是否正确！");
       return null;
   	}
	try
	{
		
	    ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
	    if(DBOConfig.getInstance().isShow_sql())System.out.println("EasyDBO:"+sql);
	    JDBCUtil.setQueryParams(params,ps);	    
	    rs = ps.executeQuery();	   
	  	if(rs.next())ret=rs.getObject(1);
	}
	catch(Exception e)
	{
		 logger.error("数据查询错误："+e+":sql="+sql);
	}
	finally
	{
		 try{
		 if (rs != null)
	        rs.close();
	    if (ps != null)
	        ps.close();
	    if (conn != null)
	        conn.close();
		 }
		 catch(Exception e)
		 {
			 logger.error("释放数据库资源错误："+e);
		 }
	}
	return ret;	
}
public DBObject get(String sql)
{
	return get(sql,null);
}
public DBObject get(String sql,Collection params)//通过完整的sql语句读取得单个对象
{	
  DBObject obj=null;
  List list = new ArrayList();
  list=query(sql,params,0,1);
  if(list!=null && (list.size()>0))
  {
	obj=(DBObject)list.get(0);
	//obj.setIdValue(obj.getValue().get(obj.getTable().getId()));
  }
  return obj;
}
public DBObject get(DBObject obj,Object idvalue)//通过ID值读取数据表数据
{	
	if(dataSource==null)
	{
		logger.error("数据源不可用，请先设置EasyDBO数据源！详情请查询www.easyjf.com！");
		return null;		
		}
	Connection conn = null;
    PreparedStatement ps = null;	
    ResultSet rs = null;   
    if(obj==null)return null;    
    String sql=sqlQuery.getQuerySql(obj);
    if(sql.equals(""))
    	{
    	logger.error("找不到查询参数!");
    	return null;    	
    	}    
    try{
        conn = dataSource.getConnection();
   	}
   	catch(SQLException e)
   	{
   	logger.error("无没取得数据源，请确认数据源配置是否正确！");
       return null;
   	}
    List list = new ArrayList();
    try
    {
        ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        if(DBOConfig.getInstance().isShow_sql())System.out.println("EasyDBO:"+sql);
        JDBCUtil.setQueryParam(idvalue,1,ps);
        rs = ps.executeQuery();
        list = JDBCUtil.resultSet2List(rs);
        if(list!=null && (list.size()>0))
        {
        	obj.setValue((Map)list.get(0));
        	obj.setIdValue(obj.getValue().get(obj.getTable().getId()));
        }
        else//没有查询到数据
        {
        	return null;
        }
    }
    catch(Exception e)
    {
   	 logger.error("数据查询错误："+e+":sql="+sql);
    }
    finally
    {
   	 try{
   	 if (rs != null)
            rs.close();
        if (ps != null)
            ps.close();
        if (conn != null)
            conn.close();
   	 }
   	 catch(Exception e)
   	 {
   		 logger.error("释放数据库资源错误："+e);
   	 }
    }
	return obj;
}
public DBObject get(DBObject obj,String scope,Collection params)//通过ID值读取数据表数据
{	
	 if(obj==null)return null;
	List list = new ArrayList();
	String sql=sqlQuery.getQuerySql(obj,scope);
	list=query(sql,params,0,1);
	 if(list!=null && (list.size()>0))
     { 
		DBObject dbo=(DBObject)list.get(0);
        obj.setValue(dbo.getValue());
     	obj.setIdValue(obj.get(obj.getTable().getId()));
     }
     else//没有查询到数据
     {
     	return null;
     }	
	return obj;
}
public boolean add(DBObject obj)//添加一个对象
{	
	if(dataSource==null)
	{
		logger.error("数据源不可用，请先设置EasyDBO数据源！详情请查询www.easyjf.com！");
		return false;		
		}
	String sql=sqlQuery.getInsertSql(obj);
	if(sql.equals(""))
		{
		logger.error("空的sql语句无法执行!");
		return false;
		}
	Connection conn = null;
    PreparedStatement ps = null;     
    boolean ret=false;
    try{    	
        conn = dataSource.getConnection();
   	}
   	catch(SQLException e)
   	{
   	logger.error("无没取得数据源，请确认数据源配置是否正确！");
    return false;
   	}
    try {
       
         ps = conn.prepareStatement(sql);
         if(DBOConfig.getInstance().isShow_sql())System.out.println("EasyDBO:"+sql);
         if(obj.getValue()!=null)
         JDBCUtil.setQueryParams(obj.getValue().values(), ps);      
         if(ps.executeUpdate()>0)ret=true;
     } 
     catch(Exception e)
     {
    	logger.error("添加数据错误："+e+":sql="+sql);
    	
     }
     finally
     {
    	 try{    	
         if (ps != null)
             ps.close();
         if (conn != null)
             conn.close();
    	 }
    	 catch(Exception e)
    	 {
    		 logger.error("释放数据库资源错误："+e);
    	 }
     }
  
	return ret;
}
public boolean update(DBObject obj)//修改一个对象
{
	if(dataSource==null){
		logger.error("数据源不可用，请先设置EasyDBO数据源！详情请查询www.easyjf.com！");
		return false;		
		}
	String sql=sqlQuery.getUpdateSql(obj);
	if(sql.equals(""))
		{
		logger.error("空的sql语句无法执行!");
		return false;
		}
	Connection conn = null;
    PreparedStatement ps = null;     
    boolean ret=false;
    try{
        conn = dataSource.getConnection();
   	}
   	catch(SQLException e)
   	{
   	logger.error("无没取得数据源，请确认数据源配置是否正确！");
       return false;
   	}
    try {
         ps = conn.prepareStatement(sql);
         if(DBOConfig.getInstance().isShow_sql())System.out.println("EasyDBO:"+sql);
         int lastPara=0;
         if(obj.getValue()!=null){
         obj.getValue().values().remove(obj.getIdValue());
         lastPara=obj.getValue().values().size();
         JDBCUtil.setQueryParams(obj.getValue().values(), ps);
         }
         JDBCUtil.setQueryParam(obj.getIdValue(),lastPara+1,ps);
         if(ps.executeUpdate()>0)ret=true;
     } 
     catch(Exception e)
     {
    	logger.error("添加数据错误："+e+":sql="+sql);
     }
     finally
     {
    	 try{    	
         if (ps != null)
             ps.close();
         if (conn != null)
             conn.close();
    	 }
    	 catch(Exception e)
    	 {
    		 logger.error("释放数据库资源错误："+e);
    	 }
     }
  
	return ret;
}
public boolean del(DBObject obj)
{
	if(dataSource==null){
		logger.error("数据源不可用，请先设置EasyDBO数据源！详情请查询www.easyjf.com！");
		return false;		
		}
	String sql=sqlQuery.getDelSql(obj);
	if(sql.equals(""))
		{
		logger.error("空的sql语句无法执行!");
		return false;
		}
	Connection conn = null;
    PreparedStatement ps = null;     
    boolean ret=false;
    try{
        conn = dataSource.getConnection();
   	}
   	catch(SQLException e)
   	{
   	logger.error("无没取得数据源，请确认数据源配置是否正确！");
       return false;
   	}
    try {    	
         ps = conn.prepareStatement(sql); 
         if(DBOConfig.getInstance().isShow_sql())System.out.println("EasyDBO:"+sql);
         JDBCUtil.setQueryParam(obj.getIdValue(),1,ps);        
         if(ps.executeUpdate()>0)ret=true;
     }
     catch(Exception e)
     {
    	logger.error("添加数据错误："+e+":sql="+sql);
     }
     finally
     {
    	 try{    	
         if (ps != null)
             ps.close();
         if (conn != null)
             conn.close();
    	 }
    	 catch(Exception e)
    	 {
    		 logger.error("释放数据库资源错误："+e);
    	 }
     }
  
	return ret;	
}
public List query(String sql)//查询一组sql
{
	return null;
}
public List query(String sql,Collection params)
{
	return query(sql,params,0,-1);
}

public List query(String sql,Collection params,int first,int max)
{
	if(dataSource==null){
		logger.error("数据源不可用，请先设置EasyDBO数据源！详情请查询www.easyjf.com！");
		return null;		
		}
	 Connection conn = null;
     PreparedStatement ps = null;	
     ResultSet rs = null;     
     String s=sql;
     if(max>0)s=sqlQuery.getTopSql(sql,first+max);     
     List list = new ArrayList();
     try{
         conn = dataSource.getConnection();
    	}
    	catch(SQLException e)
    	{
    	logger.error("无没取得数据源，请确认数据源配置是否正确！");
        return null;
    	}
     try
     {	 
         ps = conn.prepareStatement(s,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
         if(DBOConfig.getInstance().isShow_sql())System.out.println("EasyDBO:"+s);
         JDBCUtil.setQueryParams(params,ps);
         rs = ps.executeQuery();
         if(first>0){
         int begin=0;
         while((begin<first)&& rs.next())
         {
        	 begin++;
         }
         }
         while (rs.next()) {	     
        	  DBObject obj=new DBObject(new DBTable());
        	  // System.out.println(rs.getMetaData().getTableName(2));
        	  obj.setValue(JDBCUtil.resultSet2Map(rs));
              list.add(obj);
         }         
     }
     catch(Exception e)
     {
    	 logger.error("数据查询错误："+e+":sql="+sql);
     }
     finally
     {
    	 try{
    	 if (rs != null)
             rs.close();
         if (ps != null)
             ps.close();
         if (conn != null)
             conn.close();
    	 }
    	 catch(Exception e)
    	 {
    		 logger.error("释放数据库资源错误："+e);
    	 }
     }
	return list;
}
public int execute (String sql)throws Exception
{
	return execute(sql,null);
}
public int execute(String sql,Collection params) throws Exception//执行sql语句，返回影响的记录数
{
	 if(dataSource==null){
			logger.error("数据源不可用，请先设置EasyDBO数据源！详情请查询www.easyjf.com！");
			return -1;		
		 }
	 Connection conn = null;
     PreparedStatement ps = null;     
     int ret=-1;
     try{
         conn = dataSource.getConnection();
    	}
    	catch(SQLException e)
    	{
    	logger.error("无没取得数据源，请确认数据源配置是否正确！");
        return -1;
    	}
     try {        
         ps = conn.prepareStatement(sql); 
         JDBCUtil.setQueryParams(params, ps);
         ret=ps.executeUpdate();
     } catch (SQLException se) {
         throw new Exception("SQLException: " + se.getMessage());
     } catch (Exception ex) {       
         throw new Exception(ex);
     } finally {      
         if (ps != null)
             ps.close();
         if (conn != null)
             conn.close();
     }
     return ret;
   }
public DataSource getDataSource() {
	return dataSource;
}
public void setDataSource(DataSource dataSource) {
	this.dataSource = dataSource;
}
public static void main(String[] args) {
	
	}
}