java数据库dao_JAVA DAO层 对数据库操作常用方法 | 学步园

第一种：   List>

1、不带参数的

/**

* @return

* @throws SQLException

*/

@SuppressWarnings("unchecked")

public List> 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> 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> 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

/**

* @param documentId

* @return

*/

@SuppressWarnings("unchecked")

public List findFilePathBySrcId(String srcId)

{

StringBuffer sql = new StringBuffer();

sql.append("SELECT ........ ");

sql.append("FROM dbo......... ");

sql.append("WHERE srcID = '"+srcId+"' ");

List 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();

}