//一下是项目中用到过的一些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);
}
分享到:
相关推荐
文档整理了SQL常用的增加删除修改查询语句。分别有基本语句,高级查询语句,函数查询语句,事务,存储过程等。
SQL SERVER语句-存储过程自动实现增删改查-想看的可以看
sql常用语句,sql语句,sqlserver ,查询 常用的对数据库执行的增删改查等sql语句!
java连接sql server数据库增删改查
用nodejs+mssql+SQL Server实现增删改查
本人自己根据北大青鸟第一学期SQL课程里的内容所写的增删改查个中语句的知识点。可以说是一个小复习。每个知识点都配有例子来解释。
T-SQL批量增删改查
基本通用的java sql增删改查工具包类。异常和链接,根据自己的需要更改就行
本系统适合初学者,环境: SqlServer 工具:Visual Studio 2017 本系统实现了对Sql Server数据库的增删改查,共分为三个模块,学生管理,成绩管理,系统设置
一个自己写的管理系统跟SQL连接增删改查功能等
用C#来实现对MS-SQL数据库中数据表的增删改查功能源代码,带注释说明,ADO.net入门学习
C# winform程序 SQLServer增删改查案例,内含数据库语句,进行建库建表建字段。
jsp连接SQLserver数据库实现增删改查.这是一个完整的项目,适合新手使用啊教程都写的很详细。
菜鸟学T-SQL与C# 增删改查 菜鸟学T-SQL与C# 增删改查 菜鸟学T-SQL与C# 增删改查 菜鸟学T-SQL与C# 增删改查 菜鸟学T-SQL与C# 增删改查
SQL增删改查源码
sql于语句增删改查
C#连接sql数据库执行简单的增删改查操作 有很强的参考实用价值,适合初学者
javaFX实现的图书馆后台管理系统,有数据库增删改查,基于sqlserver数据库.zip
一个专门实现sql server数据库的增删改查,以及将查询的结果返回成表格等功能,分享代码如下 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data....