首页 > 代码库 > sql server使用杂记
sql server使用杂记
SqlServer导出数据库
navcat for sql server中打开连接,打开数据库,右键--数据传输,常规选项卡--模式选择dbo,目标选择连接(选择你新建的库)或者文件(导出你要的sql文件位置),点击开始
stuff使得表
CUSTOMER
1 shz
2 sz
1 Chinese
转为
CUSTOMER
1 shz,Chinese
2 sz
select NO,stuff((select ‘,‘ + LOGIN_NAME from CUSTOMER where NO = a.NO for xml path(‘‘)),1,1,‘‘) from CUSTOMER a
存储过程(返回记录列表)
PROCEDURE [dbo].[get_customer]
@No varchar(10),
@beginTime varchar(19),
@endTime varchar(19)
AS
BEGIN
select no, loginName,age where CONVERT(varchar, open_time, 120) between @beginTime and @endTime and no=@No
ORDER BY info.agent_no;
END
hibernate调用存储过程(其中Query不支持存储过程的分页,当然可以将分页写入存储过程中)
public List<Customer> getAllCustomer(final String No,final Date beginTime,final Date endTime) {
return (List<Customer>) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
Query query = session.createSQLQuery("{CALL get_customer(:parm1, :parm2, :parm3)}");
query.setParameter("parm1", agentNo);
query.setParameter("parm2", XADateUtil.getDateW3CFormat(beginTime));
query.setParameter("parm3", XADateUtil.getDateW3CFormat(endTime));
List arr = query.list();
List<Customer> list = new ArrayList();
if(arr==null)return null;
for (int i = 0; i < arr.size(); i++) {
Object[] ob = (Object[]) arr.get(i);
Customerpl = new Customer();
pl.setNo((String) ob[0]);
pl.setLoginName((String) ob[1]);
list.add(pl);
}
return list;
}
});
}
sql server使用杂记