sql简单的增删改查
//一下是项目中用到过的一些sql，鉴于我是初级程序员,于是收藏于此。希望大家不要见笑!
//纯jdbc
public List<Object[]> getSendId(String cardId) {
List list = new ArrayList();
String sql = "SELECT send_Id,[status],send_id FROM TASK_CALENDAR_ACCEPT WHERE accept_Id=?";
Mapping mapping = new Mapping();
mapping.setArgument(cardId);
QueryResult qr = this.queryByJdbc(sql, mapping);
Object[] obj = null;
for(int i=0;i<qr.getRowCount();i++){
obj = new Object[3];
obj[0] = qr.getInt(i, 0);
obj[1] = qr.getString(i, 1);
obj[2] = qr.getInt(i, 2);
list.add(obj);
}
return list;
}
//更新
public void UpdateTaskAccept(String cardId, String sendId) {
String sql = "UPDATE TASK_CALENDAR_ACCEPT SET status=1 WHERE accept_Id=? and send_Id=?";
Session session = this.getSession();
try{
Query query = s.createSQLQuery(sql);
query.setParameter(0, cardId);
query.setParameter(1, sendId);
query.executeUpdate();
} finally{
this.releaseSession(session);
}
}
//返回一个临时封装的对象 ChooseOrderInfoB不是实体bean
public ChooseOrderInfoB getMessage(String id,int roleType,String abSolution) {
String sql = "select distinct m1.lb_type,m1.LB_NAME,m1.Lb_directions,m1.LB_ID,m1.action,m1.lb_name,m1.lb_desc,m1.role_id"
+" from M_LB_LIST m1,t_test_progress m2,M_STUDENT_INFO m3"
+" where m1.role_id = ? and m1.AB_SOLUTION like ?"
+" and m1.LB_ID=m2.LB_CODE and m1.LB_ID=?"
+" and m2.grade_code=m3.grade_code"
+" and m2.class_code=m3.class_no"
+" and m2.sch_code=m3.SCHOOL_ID and is_lb=1";
ChooseOrderInfoB c = null;
Session session = this.getSession();
Query query = session.createSQLQuery(sql);
query.setParameter(0, roleType);
query.setParameter(1, '%'+abSolution+'%');
query.setParameter(2, id);
List list = new ArrayList();
list = query.list();
if(list.size()>0){
for(int i=0;i<list.size();i++){
Object[] obj = (Object[]) list.get(i);
c = new ChooseOrderInfoB();
c.setId(obj[3].toString());
c.setTitle(obj[1].toString());
c.setDirections(obj[2].toString());
c.setTempType(Integer.valueOf(obj[0].toString()));
c.setTestRole(obj[7].toString());
c.setIntroduction(obj[6].toString());
}
}
this.releaseSession(session);
return c;
}
//返回一个boolean类型
public boolean exisStudent(Answer an) {
String sql = "SELECT COUNT(*) FROM [M_ANSWER_OPTION] WHERE [BATCH_NO] = ? AND [STUDENT_CARD_ID] = ?";
Mapping maping = new Mapping();
maping.setArgument(an.getBatch());
maping.setArgument(an.getStudentCode());
QueryResult qr = this.queryByJdbc(sql, maping);
return qr != null && qr.getRowCount() != 0 && qr.getInt(0, 0) != 0;
}
//返回一个int类型
public Integer getLatestExamId(String userName) {
String sql = " select top 1 e.id from m_examination_batch e "
+ " where 1=1 and exists (select 1 from m_examination_score s where s.card_id = ? and s.BATCH_ID = e.id )"
+ " order by e.start_Time desc";
Mapping mapping = new Mapping();
mapping.setArgument(userName);
QueryResult rs = this.queryByJdbc(sql, mapping);
if (rs.getRowCount() > 0) {
return rs.getIntegerObject(0, 0);
}
return null;
}
//注入实体
public List<Examinations> getAllExamBatches(String userName) {
List<Examinations> list = new ArrayList<Examinations> ();
String sql = "select b.* from m_examination_score s" +
" inner join m_examination_batch b on b.id = s.batch_id" +
" where s.card_id = ? " +
" order by b.start_time desc" ;
Session s = this.getSession();
SQLQuery qu = s.createSQLQuery(sql);
qu.addEntity(Examinations.class); 因为是sql所以它不知道应该返回到哪个实体里面，用这种方法可以注入实体
qu.setParameter(0, userName); setParameter是不考虑所带参数的类型，也可以setString(),setInteger().....
list = qu.list();
this.releaseSession(s);
return list;
}
//在Hibernate中执行存储过程(有传入参数，传出参数)
public Klzs saveAnswerStatus(String carid,double avgScore) {
Klzs sst = new Klzs();
String areaCode = null;
String schCode = null;
String batch = null;
Connection conn = this.getSession().connection();
CallableStatement call;
try {
call = conn.prepareCall("{call PROC_KLZS_PERSONAL(?,?,?,?,?)}");
call.registerOutParameter("AREACODE", Types.VARCHAR); 参数要与存储过程中的大小写一致
call.registerOutParameter("SCHOOL_ID", Types.VARCHAR);
call.registerOutParameter("MONTH", Types.VARCHAR);
call.setString("CARD_ID", carid);
call.setDouble("INDEXSCORE", avgScore);
call.execute();
areaCode = call.getString("AREACODE");
schCode = call.getString("SCHOOL_ID");
batch = call.getString("MONTH");
sst.setAreaCode(areaCode);
sst.setSchCode(schCode);
sst.setBatch(batch);
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return sst;
}
//
返回String , int
public String getStatus(String scale, int roleid) {
String hql = "SELECT an.status FROM AnswerStatus an WHERE an.scaleCode=? and an.roleid=?";
Session session = this.getSession();
Query cquery = session.createQuery(hql);
cquery.setString(0, scale);
cquery.setInteger(1, roleid);
cquery.setMaxResults(1); 只取一条，相当于top 1
String status = (String)cquery.uniqueResult();
return status;
//返回实体
public Question getSerialNumber(String questionId) {
return (Question) this.getHibernateTemplate().get(Question.class, questionId);
}
