查在数据库这四种操作中算是最复杂的一种操作,“增”时一般是新创建一个对象,
然后就用set添加,而“删”和“改”时,就必须在这两种操作前查询
到你要进行操作的对象,而查询又需要各种查询的条件来满足自己的需求,
所以“查”是最为复杂的。大概有三种查询:
1. getByProperties、queryByProperties
SerVice中有getByProperties方法-根据属性名和属性值查询出你想得出的对象,如下:
/**
* 根据属性数组获取单个对象实体
*
* @param propName
* 属性数组名称
* @param propValue
* 属性数组值
* @return 返回对象实体
*/
public E getByProerties(String[] propName, Object[] propValue);
例: YjChannel channel = yjChannelService.getByProerties("id",
channelId);
便查询出id中值为channelId的channel对象。这种是单条件查询,双条件查询可以用如下形式:
YjChannel channel = yjChannelService.getByProerties(new String[] {
"id", "zoneId" }, new Object[] { channelId, zoneId });
即根据id值和zoneId值同时查询出你想要的对象。
以上都是查询出单一对象,那如果符合我要求的是一组数据,
即我想要一个符合我要求的list呢?
那就用queryByProerties方法,例:
List num = yjChannelService.queryByProerties(“zoneId”, zoneId);
同样list的查询条件也可以是双条件查询,例:
List num = yjChannelService.queryByProerties(new String[] { “zoneId”, “status” }, new Object[] { zoneId, (byte) 0 });
2. 利用BaseParameter进行查询
BaseParameter是一个已经封装大于、小于、等于、排序等的类,代码如下:
package core.support;
import java.io.Serializable;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
public class BaseParameter implements Serializable {
private static final long serialVersionUID = -2050801753454734869L;
public static final String SORTED_ASC = "ASC";
public static final String SORTED_DESC = "DESC";
public static final String AND = "AND";
public static final String OR = "OR";
private Integer maxResults = Integer.valueOf(20);
private Integer firstResult = Integer.valueOf(0);
private Integer topCount;
private String[] sortColumns;
private String cmd;
private String flag = "AND";
private Map<String, Object> queryDynamicConditions = new HashMap<String, Object>(4);
private Map<String, String> sortedConditions = new LinkedHashMap<String, String>(2);
private Map<String, Object> dynamicProperties = new HashMap<String, Object>(4);
public Integer getMaxResults() {
return this.maxResults;
}
public void setMaxResults(Integer maxResults) {
this.maxResults = maxResults;
}
public Map<String, Object> getQueryDynamicConditions() {
return this.queryDynamicConditions;
}
public void setQueryDynamicConditions(Map<String, Object> queryDynamicConditions) {
this.queryDynamicConditions = queryDynamicConditions;
}
public Map<String, String> getSortedConditions() {
return this.sortedConditions;
}
public void setSortedConditions(Map<String, String> sortedConditions) {
this.sortedConditions = sortedConditions;
}
public Integer getTopCount() {
return this.topCount;
}
public void setTopCount(Integer topCount) {
this.topCount = topCount;
}
public String[] getSortColumns() {
return this.sortColumns;
}
public Map<String, Object> getDynamicProperties() {
return this.dynamicProperties;
}
public void setDynamicProperties(Map<String, Object> dynamicProperties) {
this.dynamicProperties = dynamicProperties;
}
public String getSortColumnsString() {
StringBuffer sb = new StringBuffer();
if (this.sortColumns != null) {
for (String s : this.sortColumns) {
sb.append("&sortColumns=" + s);
}
}
return sb.toString();
}
public void setSortColumns(String[] sortColumns) {
this.sortColumns = sortColumns;
if (sortColumns != null) {
for (String s : sortColumns) {
String[] sa = s.split("\\|");
if (sa.length == 2) {
this.sortedConditions.put(sa[0], sa[1]);
}
}
}
}
public String getCmd() {
return this.cmd;
}
public void setCmd(String cmd) {
this.cmd = cmd;
}
public Integer getFirstResult() {
return this.firstResult;
}
public void setFirstResult(Integer firstResult) {
this.firstResult = firstResult;
}
public String getFlag() {
return this.flag;
}
public void setFlag(String flag) {
this.flag = flag;
}
}
具体用法如下:
BaseParameter bp = BaseParameterBuilder.newInstance().eq("operatorId", operatorId).ge("createTime", start)
.le("createTime", now).orderByDESC("createTime").build();
List<UserDataHeader> datas = userDataHeaderService.doQuery(bp);
其中eq是等于、ge是大于、le是小于、orderByDESC是倒叙排列,具体查询要求即为 operatorId等于operatorId、createTime大于start且小于now,结果按照createTime时间进行降序排列。可以看出,BaseParameter的查询方法十分灵活,可以满足的查询条件也很多。
3. sql语句查询
sql语句查询时最本质的查询方式,不过如果每次查询都用sql查询,
会使代码使用啰嗦和臃肿,但是一些时候我们的查询条件太多的话,
还是需要使用功能强大的sql语句查询。
select <列名> from <表名> [where <查询条件表达试>] [group by XXXX][order by <排序的列名>[asc或desc]]
<列名>可以用*,表示全部列;可以用sum(列名)函数;表示所选列的求和;
可以用count()函数将两个列相加;可以用distinct(列名)限制重复数据的出现
(注:distinct必须放在最前面);
字符串需要用"+string+"括起来、如果是时间字符串需要额外加单引号'' ,
具体例子如下:
public List<Map<String, Object>> allKindScript(String order, String sort, String departmentId){
SELECT uws.user_id,SUM(score) AS score,SUM(wait_publish_num) ASwaitPublishNum,
COUNT(have_published_num+wait_publish_num) AS bankScriptNum FROM user_work_statics AS uws WHERE uws.user_id in ("
+ userGroup + ") AND uws.department_id in (" + departmentId + ") AND uws.update_time > '" + bTime
+ "' AND uws.update_time < '" + eTime + "' GROUP BY uws.user_id order by " + order + " " + sort + "
上面是单表查询,要双表查询就需要用到左联合查询,即:A left join B on A.a = B.b ,具体例子如下:
String aString = "SELECT distinct(sp.script_origin_id) FROM script_process as sp left join script_module as sm on sp.script_origin_id =sm.script_origin_id where sp.isdelete = 0 and sm.isdelete = 0 AND sm.update_time > '" + bTime
+ "' AND sm.update_time < '" + eTime + "'";
这条查询语句的作用是从表script_process中所有数据 + script_module 中script_origin_id 与表script_process 中的script_origin_id相等的数据
挑选满足where后条件的数据,且显示的结果script_origin_id不重复,查询结果如下:
具体sql语句的详细实现过程我们会在后面的博客说明。