JAVA DAO层 对数据库操作常用方法

第一种：   List<Map<String, Object>>

1、不带参数的

	/**
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, String>> getProjectModule() throws SQLException 
	{
		StringBuffer  sql = new StringBuffer();
		sql.append("select ........ ........,");
		sql.append("      ........ ........");
		sql.append(" from dbo.........");
		sql.append(" where fileuseflg='0' ");

		Query query = this.getSession().createSQLQuery(sql.toString())
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

2、带参数的

	
	/**
	 * @param custId
	 * @param page
	 * @param rows
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getCustomerList(String regieId, int page, int rows) throws SQLException{
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT a.*,b.CUST_NAME ");
		sql.append(" FROM ........  ");
		sql.append(" WHERE a.CUSTOM_ID = b.CUSTOM_ID ");
		sql.append(" AND a.VISIT_REGION_ID =:regieId ");
		sql.append(" ORDER BY a.CUST_REGIE_CODE ");
		
		return this.getSession().createSQLQuery(sql.toString()).setParameter("regieId", regieId)
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.setFirstResult((page-1)*rows).setMaxResults(rows).list();
	}

        sql.append(" AND a.VISIT_REGION_ID =:regieId ");            
        return this.getSession().createSQLQuery(sql.toString()).setParameter("regieId", regieId)
        跟 sql.append(" AND a.VISIT_REGION_ID ='"+regieId+"' "); 效果一样，如：

	/**
	 * @param projectId
	 * @param documentID
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> fillDgPaddingListData(String projectId,
			String documentID) throws SQLException {
		System.out.println("编辑操作");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT a.*  ");
		sql.append("FROM   ........ a ");
		sql.append("  left  join  dbo.XLJXC_PROJECT   b   ");
		sql.append("   on  a.subProjectID=b.PROJECT_ID   ");
		sql.append("   WHERE  projectID = '" + projectId
				+ "' and  documentID='" + documentID + "' ");

		Query query = this.getSession().createSQLQuery(sql.toString())
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}

第二种  List<String>

	/**
	 * @param documentId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<String> findFilePathBySrcId(String srcId) 
	{
		StringBuffer  sql = new StringBuffer();
		sql.append("SELECT ........ ");
		sql.append("FROM   dbo......... ");
		sql.append("WHERE  srcID = '"+srcId+"' ");
		
		List<String> list=this.getSession().createSQLQuery(sql.toString()).list();
		return list;
	}

第三种 String

	/**
	 * @param custId
	 * @return
	 */
	
	public String getCustomerCount(String custId) throws SQLException{
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT count(*)");
		sql.append(" FROM ........cc_customer b  ");
		sql.append(" WHERE a.CUST_REGIE_CODE = b.CUST_CODE ");
		sql.append(" AND a.VISIT_REGION_ID =:custId ");
		
		return this.getSession().createSQLQuery(sql.toString()).setParameter("custId", custId).list().get(0).toString();
	}

return query.list().get(0)==null?"": query.list().get(0).toString();  判断query.list().get(0)是否为空 如:

	public String getComment(String id) throws SQLException{
		StringBuffer sql = new StringBuffer();
		sql.append("select COMMENT ");
		sql.append(" from ........ ");
		sql.append(" where id='"+id+"'");
		Query query = this.getSession().createSQLQuery(sql.toString());
		return query.list().get(0)==null?"": query.list().get(0).toString();
 }

第四种 不带返回值

	/**
	 * @param ent
	 * @throws SQLException
	 * 插入
	 */
	public void saveVisitCustormer(Work135DayVisitCust ent) throws SQLException{
		StringBuffer sql = new StringBuffer();
		sql.append("insert into DB2INST1.WORK135_DAY_VISIT_CUST ");
		sql.append("........ ");
		sql.append(" ...... ");

		Query query = this.getSession().createSQLQuery(sql.toString());
		query.executeUpdate();
		//this.getSession().save(ent);
	}
	/**
	 * @throws SQLException
	 * 删除
	 */
	public void delectDictByItemId(String itemId,String personCode,String orgCode)throws SQLException{
		StringBuffer sql = new StringBuffer();
		sql.append("delete from ........");
		sql.append(" where evaluate_Item_Id = '"+itemId+"' and person_Code ='"+personCode+"' and org_Code ='"+orgCode+"'");
		Query query = getSession().createSQLQuery(sql.toString());
		query.executeUpdate();
	}