databasedaoimpl.java

论坛软件系统亦称电子公告板(BBS)系统

package cn.jsprun.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import cn.jsprun.dao.DataBaseDao;
import cn.jsprun.utils.Coding;
import cn.jsprun.utils.HibernateUtil;
import cn.jsprun.utils.JspRunConfig;
import cn.jsprun.vo.system.FieldStatusVO;
import cn.jsprun.vo.system.FieldVO;
import cn.jsprun.vo.system.TableStatusVO;

public class DataBaseDaoImpl implements DataBaseDao {
	@SuppressWarnings("unchecked")
	public Map sqldumptable(List<String> excepttables,String table,int startfrom, long currsize,long sizelimit,boolean complete,String version,int extendins,String sqlcompat,String dumpcharset,String sqlcharset,boolean usehex) {
		int offset = 300;
		Map map=new HashMap();
		StringBuffer tabledump = new StringBuffer();
		if (table.contains("adminsessions")) {
			map.put("tabledump", tabledump);
			return map;
		}
		List<FieldStatusVO> fieldStatusVOs = this.findTableFieldStatus(table);
		if (fieldStatusVOs == null || fieldStatusVOs.size() <= 0) {
			map.put("tabledump", tabledump);
			return map;
		}
		if(startfrom==0){
			String createtable = this.showCreateSql(table).replaceAll("`", "");
			if (createtable != null && createtable.length() > 0) {
				tabledump.append("DROP TABLE IF EXISTS " + table + ";\n");
			} else {
				map.put("tabledump", tabledump);
				return map;
			}
			tabledump.append(table.indexOf(".") != -1?createtable:createtable.replaceFirst("CREATE TABLE " + table,"CREATE TABLE " + table.substring(table.indexOf(".") + 1)));
			TableStatusVO tableStatusVO = this.findTableStatus("SHOW TABLE STATUS LIKE '" + table + "';").get(0);
			if (sqlcompat.equals("MYSQL41") && version.compareTo("4.1") < 0) {
				tabledump.replace(0, tabledump.length(),tabledump.toString().replaceFirst("TYPE\\=(.+)","ENGINE="+tableStatusVO.getEngine()+" DEFAULT CHARSET=" + dumpcharset)) ;
			}else if (sqlcompat.equals("MYSQL40") && version.compareTo("4.1") >= 0&& version.compareTo("5.1") < 0) {
				tabledump.replace(0, tabledump.length(),tabledump.toString().replaceFirst("ENGINE\\=(.+)","TYPE=" + tableStatusVO.getEngine()));
			}else if (version.compareTo("4.1") > 0 && sqlcharset.length()>0) {
				tabledump.replace(0, tabledump.length(),tabledump.toString().replaceFirst("(DEFAULT)*\\s*CHARSET=.+","DEFAULT CHARSET=" + sqlcharset));
			}
			tabledump.append((tableStatusVO.getAuto_increment()!=null&&!"".equals(tableStatusVO.getAuto_increment()) ?" AUTO_INCREMENT="+tableStatusVO.getAuto_increment(): "")+";\n\n");
			if (sqlcompat.equals("MYSQL40") && version.compareTo("4.1") >= 0&& version.compareTo("5.1") < 0) {
				if (tableStatusVO.getAuto_increment()!=null&&!"".equals(tableStatusVO.getAuto_increment())) {
					tabledump.insert(tabledump.indexOf(","), " auto_increment");
				}
				if ("MEMORY".equals(tableStatusVO.getEngine())) {
					int index=tabledump.indexOf("TYPE=MEMORY");
					if(index>0){
						tabledump.replace(index, "TYPE=MEMORY".length()+index, "TYPE=HEAP");
					}
				}
			}
		}
		if(!excepttables.contains(table))
		{
			int tabledumped=0;
			int numrows=offset;
			FieldStatusVO firstfield=fieldStatusVOs.get(0);
			if(extendins==0) {
				while((currsize+tabledump.length())<sizelimit&&numrows==offset&&complete){
					String selectsql=null;
					if("auto_increment".equals(firstfield.getExtra())){
						selectsql="SELECT * FROM "+table+" WHERE "+firstfield.getField()+" > "+startfrom+" LIMIT "+offset+";";
					}
					else{
						selectsql="SELECT * FROM "+table+" LIMIT "+startfrom+", "+offset+";";
					}
					tabledumped = 1;
					List<Map<String,String>> rows=this.executeQuery(selectsql);
					if(rows!=null){
						numrows=rows.size();
						StringBuffer t=null;
						for (Map<String, String> row : rows) {
							t=new StringBuffer();
							for (FieldStatusVO fieldStatusVO : fieldStatusVOs) {
								String type=fieldStatusVO.getType();
								String value=row.get(fieldStatusVO.getField());
								if(value==null&&"date".equals(type)){
									value="0000-00-00";
								}
								t.append(","+(usehex&&!value.equals("")&&(type.contains("char")||type.contains("text"))?"0x"+Coding.bin2hex(value,JspRunConfig.charset):"\'"+value+"\'"));
							}
							if((t.length()+currsize+tabledump.length())<sizelimit)
							{
								if("auto_increment".equals(firstfield.getExtra())){
									startfrom=Integer.valueOf(row.get(firstfield.getField()));
								}
								else{
									startfrom++;
								}
								if(t.length()>0){
									t.deleteCharAt(0);
									tabledump.append("INSERT INTO "+table+" VALUES ("+t+");\n");
								}
							}else{
								complete=false;
								break;
							}
						}
					}
					else{
						break;
					}
				}
			}
			else{
				while(currsize+tabledump.length()<sizelimit&&numrows==offset&&complete){
					String selectsql=null;
					if("auto_increment".equals(firstfield.getExtra())){
						selectsql="SELECT * FROM "+table+" WHERE "+firstfield.getField()+" > "+startfrom+" LIMIT "+offset+";";
					}
					else{
						selectsql="SELECT * FROM "+table+" LIMIT "+startfrom+", "+offset+";";
					}
					tabledumped = 1;
					List<Map<String,String>> rows=this.executeQuery(selectsql);
					if(rows!=null){
						numrows=rows.size();
						StringBuffer t1=new StringBuffer();
						for (Map<String, String> row : rows) {
							StringBuffer t2=new StringBuffer();
							for (FieldStatusVO fieldStatusVO : fieldStatusVOs) {
								String type=fieldStatusVO.getType();
								String value=row.get(fieldStatusVO.getField());
								if(value==null&&"date".equals(type)){
									value="0000-00-00";
								}
								t2.append(","+(usehex&&!value.equals("")&&(type.contains("char")||type.contains("text"))?"0x"+Coding.bin2hex(value,JspRunConfig.charset):"\'"+value+"\'"));
							}
							if(t1.length()+currsize+tabledump.length()<sizelimit){
								if("auto_increment".equals(firstfield.getExtra())){
									startfrom=Integer.valueOf(row.get(firstfield.getField()));
								}
								else{
									startfrom++;
								}
								if(t2.length()>0){
									t2.deleteCharAt(0);
									t1.append(",("+t2+")");
								}
							}else{
								complete=false;
								break;
							}
						}
						if(t1.length()>0){
							t1.deleteCharAt(0);
							tabledump.append("INSERT INTO "+table+" VALUES "+t1+";\n");
						}
					}
					else{
						break;
					}
				}
			}
			tabledump.append("\n");
			map.put("startfrom", startfrom);
			map.put("complete", complete);
		}
		map.put("tabledump", tabledump);
		return map;
	}

	public String showCreateSql(String tableName) {
		 String sql = "SHOW CREATE TABLE " + tableName + ";";
		 return this.executeQuery(sql,2).get(0);
	}
	public List<String> executeQuery(String sql, String columnName) {
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs=null;
		Transaction transaction = null;
		try{
			List<String> rows = new ArrayList<String>();
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			transaction = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			rs =pstmt.executeQuery();
			while (rs.next()) {
				rows.add(rs.getString(columnName));
			}
			transaction.commit();
			return rows;
		}catch(Exception exception){
			exception.printStackTrace();
			if(transaction!=null){
				transaction.rollback();
			}
			return null;
		}finally {
			try {
				if(transaction!=null){
					transaction=null;
				}
				if(rs!=null){
					rs.close();
					rs=null;
				}
				if(pstmt!=null){
					pstmt.close();
					pstmt=null;
				}
				if(conn!=null){
					conn.close();
					conn=null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public List<Map<String,String>> executeQuery(String sql) {
		Session session =null;
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs=null;
		Transaction transaction = null;
		List<Map<String,String>> rows =null;
		try{
			session = HibernateUtil.getSessionFactory().getCurrentSession();
			transaction = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			rs =pstmt.executeQuery();
			rows= new ArrayList<Map<String,String>>();
			ResultSetMetaData rsmd=rs.getMetaData();
			int columnCount=rsmd.getColumnCount();
			String []columns=new String[columnCount];
			for(int i=1;i<=columnCount;i++){
				columns[i-1]=rsmd.getColumnLabel(i);
			}
			rsmd=null;
			Map<String,String> row=null;
			while (rs.next()){
				row=new HashMap<String,String>(columnCount);
				for (int i=1;i<=columnCount;i++) {
					row.put(columns[i-1],rs.getString(i));
				}
				rows.add(row);				
			}
			row=null;
			columns=null;
			transaction.commit();
		}catch(Exception exception){
			exception.printStackTrace();
			if(transaction!=null){
				transaction.rollback();
			}
		}finally {
			try {
				if(transaction!=null){
					transaction=null;
				}
				if(rs!=null){
					rs.close();
					rs=null;
				}
				if(pstmt!=null){
					pstmt.close();
					pstmt=null;
				}
				if(conn!=null){
					conn.close();
					conn=null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rows;
	}
	public Boolean executeUpdateByHql(String hql) {
		boolean flag = false;
		Transaction tran = null;
		Query query = null;
		try {
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			tran = session.beginTransaction();
			query = session.createQuery(hql);
			query.executeUpdate();
			flag = true;
			tran.commit();
		} catch (HibernateException e) {
			flag = false;
			if(tran!=null){
				tran.rollback();
			}
			e.printStackTrace();
		}
		return flag;
	}
	private List<String> executeQuery(String sql, int columnIndex) {
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs=null;
		Transaction transaction = null;
		List<String> rows = new ArrayList<String>();
		try{
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			transaction = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			rs =pstmt.executeQuery();
			while (rs.next()) {
				rows.add(rs.getString(columnIndex));
			}
			transaction.commit();
		}catch(Exception exception){
			exception.printStackTrace();
			if(transaction!=null){
				transaction.rollback();
			}
		}finally {