各种数据库 不同查询语句代码

一.orcale

 1.hibernate  qbc例子

@SuppressWarnings("unchecked")
public Map<String, Object> findAllemployee(TBaseEmployee tb,
String departStr,int page,int rows) {
Map<String, Object> map=new HashMap<String, Object>();
HttpSession session = ServletActionContext.getRequest().getSession();// 创建会话session

Criteria cri = null;
cri = this.getHibernateTemplate().getSessionFactory()
.getCurrentSession().createCriteria(TBaseEmployee.class, "f");
cri.addOrder(Order.desc("f.OEntrytime"));// 


if (null != departStr) {//分部门权限查询
String str[] = departStr.split(",");
cri = cri.add(Restrictions.in("f.ODepartmentid", str));
}


if (null != tb) {
if (null != tb.getOEmployeename()
&& !"".equals(tb.getOEmployeename())) {// 姓名
Criterion cr = Restrictions.like("f.OEmployeename", "%"
+ tb.getOEmployeename() + "%");
cri = cri.add(cr);
}
if (null != tb.getOIccard() && !"".equals(tb.getOIccard())) {// 工号
Criterion cr = Restrictions.like("f.OIccard", "%"
+ tb.getOIccard() + "%");
cri = cri.add(cr);
}
if (null != tb.getODepartment() && !"".equals(tb.getODepartment())) {// 部门
Criterion cr = Restrictions.eq("f.ODepartment", tb
.getODepartment());
cri = cri.add(cr);
}
if (null != tb.getOJob() && !"".equals(tb.getOJob())) {// 职业类型
Criterion cr = Restrictions.eq("f.OJob", tb.getOJob());
cri = cri.add(cr);
}
if(null!=tb.getBegindate()&&!"".equals(tb.getBegindate())){//入职时间
Criterion cr = Restrictions.ge("f.OEntrytime", tb.getBegindate());
cri = cri.add(cr);
}
if(null!=tb.getEnddate()&&!"".equals(tb.getEnddate())){
Criterion cr = Restrictions.le("f.OEntrytime", tb.getEnddate());
cri = cri.add(cr);
}
}
int totalrows=cri.list().size();//总条数

cri.setFirstResult((page-1)*rows);
cri.setMaxResults(rows);

map.put("rows", cri.list());
map.put("total",totalrows);
session.setAttribute("employeelist",  cri.list());//导出数据
return map;
}

    2.hibernate 的sql 例子

     public Map<String, Object> findDoorInfos(int page, int rows, ParamDto pdt) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("rows", "");
map.put("total", 0);
DateUtils du = new DateUtils();
if (null != pdt) {
if ("".equals(pdt.getDate()) || null == pdt.getDate()) {
pdt.setDate(du.getday());
}
String table = "ytcd.t_jk_door" + du.getDateToStr1(pdt.getDate());
String sql = "select t.o_busno,t.o_linename,t.o_date,t.o_time,t.o_direction,t.o_prestationno,t.o_frontdoor,t.o_middoor,t.o_reardoor"
+ " from " + table + " t  where 1=1";
if (null != pdt.getLineno() && !"".equals(pdt.getLineno())) {
sql += " and  t.o_lineno='" + pdt.getLineno() + "'";
}
if (null != pdt.getBusno() && !"".equals(pdt.getBusno())) {
sql += " and  t.o_busno='" + pdt.getBusno() + "'";
}
if (null != pdt.getDate() && !"".equals(pdt.getDate())) {
sql += " and  t.o_date='" + pdt.getDate() + "'";
}
if (null != pdt.getDirection() && !"".equals(pdt.getDirection())) {
sql += " and  t.o_direction='" + pdt.getDirection() + "'";
}
if (null != pdt.getStationno()&& !"".equals(pdt.getStationno())) {
sql += " and  t.o_stationno='" + pdt.getStationno()+ "'";
}
if (null == pdt.getStartime() || "".equals(pdt.getStartime())) {
pdt.setStartime("00:00");
}
if (null == pdt.getEndtime() || "".equals(pdt.getEndtime())) {
pdt.setEndtime("23:59");
}


sql += " and  t.o_time between '" + pdt.getStartime() + "' and '"
+ pdt.getEndtime() + "'";
sql += " order by t.o_time";
try {
Query qu = getsession().createSQLQuery(sql);
if (null != qu.list() && qu.list().size() != 0) {
int totals = qu.list().size();
map.put("total", totals);
qu.setFirstResult((page - 1) * rows);
qu.setMaxResults(rows);
qu.list();


map.put("rows", qu.list());
} else {
return map;
}
} catch (Exception e) {
e.printStackTrace();
return map;
}


} else {
return map;
}
return map;
}

 3.hibernate 的hql 例子

public Serializable saveentity(Object ob) {
return this.getHibernateTemplate().save(ob);
}

 4.jdbc sql  调用存储过程并分页例子

public PagesBean<FullGpsData> findfindGpsDataInfo(String odirection,
CommSearch coms, PagesBean<FullGpsData> page) {
List<FullGpsData> list = new ArrayList<FullGpsData>();
String tablename = "t_jk_fullgpsdata"
+ DateUtils.formatDate(coms.getDateTime());
String sqlsz = "";
String sqlszcount ="";
if(coms.getStarttime().equals("00:00:00")&&coms.getEndtime().equals("23:59:59")){
sqlszcount = "select count(1) from "+ tablename + " where " + "O_LINENAME='" + coms.getSelLine()
+ "' and O_BUSNAME='" + coms.getSelCar()+ "' and O_UP='" + odirection + "'";
sqlsz = "select O_BUSNAME,O_LINENAME,O_DATE,O_TIME,"
+ "decode (O_RUN,0,'营运中',1,'营运中') as O_RUNSTATUS,"
+ "O_LONGITUDE,O_LATITUDE,O_SPEED,decode (O_UP,0,'上行',1,'下行' ) as UP,"
+ "(O_NEXTSTATIONNO-1) as STATIONNO,o_Up " + "from "
+ tablename + " where " + "O_LINENAME='" + coms.getSelLine()
+ "' and O_BUSNAME='" + coms.getSelCar()
+ "' and O_UP='" + odirection + "' order by O_TIME";
}
Connection connTwo = getConnTwo();// 连接用户sz
CallableStatement cstmt = null;
ResultSet rs = null;
try {
String spName = "{call PROC_PAGE_ext(?,?,?,?,?,?)}";
cstmt = connTwo.prepareCall(spName);
cstmt.setInt(1, page.getCurrentPage());// 当前页数
cstmt.setString(2, sqlsz);// sql语句
cstmt.setString(3, sqlszcount);// sql语句总条数
cstmt.setInt(4, page.getPageSize());// 每页显示数目
System.out.println(oracle.jdbc.OracleTypes.INTEGER);
cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);// 返回的总条数
System.out.println(oracle.jdbc.OracleTypes.CURSOR);
cstmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);// 要查的结果集
cstmt.execute();
page.setTotalRecords(cstmt.getInt(5));
rs = (ResultSet) cstmt.getObject(6);
while (rs.next()) {
FullGpsData fg = new FullGpsData();
fg.setBusName(rs.getString(1)); 
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
CloseResuSet(rs);
CloseCallSta(cstmt);
CloseConn(connTwo);
}
return page;
}

待续。。。。。