databasedaoimpl.java

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

			try {
				if(transaction!=null){
					transaction=null;
				}
				if(rs!=null){
					rs.close();
					rs=null;
				}
				if(pstmt!=null){
					pstmt.close();
					pstmt=null;
				}
				if(conn!=null){
					conn=null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rows;
	}

	private List<FieldStatusVO> findTableFieldStatus(String tableName) {
		List<FieldStatusVO> fieldStatusVOs =  new ArrayList<FieldStatusVO>();
		String sql = "SHOW FULL COLUMNS FROM " + tableName;
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs=null;
		Transaction transaction = null;
		try{
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			transaction = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			rs =pstmt.executeQuery();
			FieldStatusVO fieldStatusVO = null;
			while (rs.next()) {
				fieldStatusVO = new FieldStatusVO();
				fieldStatusVO.setField(rs.getString("Field"));
				fieldStatusVO.setType(rs.getString("Type"));
				fieldStatusVO.setCollation(rs.getString("Collation"));
				fieldStatusVO.setAllowNull(rs.getString("Null"));
				fieldStatusVO.setKey(rs.getString("Key"));
				fieldStatusVO.setDefaultValue(rs.getString("Default"));
				fieldStatusVO.setExtra(rs.getString("Extra"));
				fieldStatusVO.setPrivileges(rs.getString("Privileges"));
				fieldStatusVO.setComment(rs.getString("Comment"));
				fieldStatusVOs.add(fieldStatusVO);
			}
			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 fieldStatusVOs;
	}

	public String findBasedir() {
		String sql = "SHOW VARIABLES LIKE 'basedir';";
		return this.executeQuery(sql, "value").get(0);
	}

	public List<String> findAllTableNames(String prefix) {
		return this.executeQuery("SHOW TABLES LIKE '" + prefix + "%';", 1);
	}
	public int insert(String sql,boolean show_last_insert_id){
		int id=0;
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		Transaction tran = session.beginTransaction();
		Connection conn = session.connection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.execute(sql);
			if(show_last_insert_id){
				pstmt = conn.prepareStatement("SELECT last_insert_id()");
				rs =pstmt.executeQuery();
				while (rs.next()) {
					id=rs.getInt(1);
				}
			}
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			e.printStackTrace();
		} finally {
			try {
				if(tran!=null){
					tran=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 id;
	}
	public Map<String, String> runQuery(String sql) {
		Session session = null;
		Transaction tran = null;
		Map<String, String> infos = new HashMap<String, String>();
		session = HibernateUtil.getSessionFactory().getCurrentSession();
		tran = session.beginTransaction();
		Connection conn = session.connection();
		Statement pstmt = null;
		ResultSet rs=null;
		try {
			pstmt = conn.createStatement();
			pstmt.execute(sql);
			int num = pstmt.getUpdateCount();
			if (num == -1) {
				num = 0;
				rs = pstmt.executeQuery(sql);
				while (rs.next()) {
					num++;
				}
			}
			infos.put("ok", String.valueOf(num));
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			infos.put("error", e.getMessage());
			infos.put("errorCode", e.getErrorCode()+"");
			e.printStackTrace();
		} finally {
			try {
				if(rs!=null){
					rs.close();
					rs=null;
				}
				if(tran!=null){
					tran=null;
				}
				if(pstmt!=null){
					pstmt.close();
					pstmt=null;
				}
				if(conn!=null)
				{
					conn.close();
					conn=null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return infos;
	}
	public void runQuery(String sql,boolean noUpdateCount) {
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		Transaction tran = session.beginTransaction();
		Connection conn = session.connection();
		Statement pstmt = null;
		try {
			pstmt = conn.createStatement();
			pstmt.execute(sql);
			tran.commit();
		} catch (SQLException e) {
			tran.rollback();
			e.printStackTrace();
		} finally {
			try {
				if(tran!=null){
					tran=null;
				}
				if(pstmt!=null){
					pstmt.close();
					pstmt=null;
				}
				if(conn!=null)
				{
					conn.close();
					conn=null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public List<FieldVO> findTableFields(String tableName) {
		List<FieldVO> fieldVOs = new ArrayList<FieldVO>();
		String sql = "SHOW FIELDS FROM " + tableName;
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs=null;
		Transaction transaction = null;
		try{
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			transaction = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			rs =pstmt.executeQuery();
			FieldVO fieldVO = null;
			while (rs.next()) {
				fieldVO = new FieldVO();
				fieldVO.setField(rs.getString("Field"));
				fieldVO.setType(rs.getString("Type"));
				fieldVO.setAllowNull(rs.getString("Null"));
				fieldVO.setKey(rs.getString("Key"));
				fieldVO.setDefaultValue(rs.getString("Default"));
				fieldVO.setExtra(rs.getString("Extra"));
				fieldVOs.add(fieldVO);
			}
			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 fieldVOs;
	}

	public List<TableStatusVO> findTableStatus(String sql) {
		List<TableStatusVO> tableStatusVOs = new ArrayList<TableStatusVO>();
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs=null;
		Transaction transaction = null;
		try{
			Session session = HibernateUtil.getSessionFactory().getCurrentSession();
			transaction = session.beginTransaction();
			conn = session.connection();
			pstmt = conn.prepareStatement(sql);
			rs =pstmt.executeQuery();
			TableStatusVO tableStatusVO = null;
			while (rs.next()) {
				tableStatusVO = new TableStatusVO();
				tableStatusVO.setName(rs.getString("Name"));
				tableStatusVO.setEngine(rs.getString("Engine"));
				tableStatusVO.setRows(rs.getLong("Rows"));
				tableStatusVO.setData_length(rs.getLong("Data_length"));
				tableStatusVO.setIndex_length(rs.getLong("Index_length"));
				tableStatusVO.setData_free(rs.getLong("Data_free"));
				tableStatusVO.setAuto_increment(rs.getString("Auto_increment"));
				tableStatusVO.setCollation(rs.getString("Collation"));
				tableStatusVOs.add(tableStatusVO);
			}
			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 tableStatusVOs;
	}
		
	public void executeDelete(String sql) {
		this.runQuery(sql, true);
	}
	
	public void execute(String sql) {
		executeDelete(sql);
	}
}