Spring JDBCTemplate学习笔记

最新推荐文章于 2022-03-25 20:51:54 发布

kagula086

最新推荐文章于 2022-03-25 20:51:54 发布

阅读量491

点赞数

分类专栏： Java

本文链接：https://blog.csdn.net/lee353086/article/details/52326293

版权

Java 专栏收录该内容

64 篇文章 0 订阅

订阅专栏

Title: Spring JDBCTemplate学习笔记
Date: 2016-8-26
Author: kagula

Env: Spring 3.1.2.RELEASE, Oracle 11GR2

这里记下示例代码片段，免得过一段时间又忘记了。

增

		String sql = "insert into cityCodeMap (idcitycodemap,ICBCCode,ChinaPetroCode,cityName)"
			+ "values(seq_citycodemap.nextval,?,?,?)";	
		this.getJdbcTemplate().update(sql,
				new Object[]{ICBCCode,chinaPetroCode,cityName});

删

		String sql = "delete from cashbox where idcashbox=?";
		this.getJdbcTemplate().update(sql,new Object[]{idcashbox});

改

		createTime += " 00:00:00";//expect format is 2016-08-26 00:00:00
		Timestamp ts = Timestamp.valueOf(createTime);

		sql= "update paymentnumber set idcashbox=? where createTime=? and idStationInfo=?";
		this.jdbcTemplate.update(sql,new Object[]{
				idcashbox,ts,idStationinfo});

查

单值返回

	public boolean IsExist(String createTime,int idStationinfo)
	{
		createTime += " 00:00:00";//CreateTime expect format is "2016-08-26 00:00:00"
		Timestamp ts = Timestamp.valueOf(createTime);
		
		int count = this.jdbcTemplate.queryForInt(
				"select count(0) from cashbox where createTime=? and idStationInfo=?",
				new Object[]{ts,idStationinfo});
		if(count>0)
		{
			return true;
		}
		return false;
	}

单值多条记录

		String stationid = this.getJdbcTemplate().queryForObject("select stationid from stationinfotable where idStationinfo=?", 
				new Object[]{idStationinfo},String.class);

单条记录

	public SessionUserInfo Query(String userName)
	{
		String sql = "select a.idCityCodeMap,b.stationname,b.idStationinfo,b.stationid,c.chinapetrocode from userinfotable a "+ 
                     "left join stationinfotable b on a.idStationinfo=b.idStationinfo "
                     + "left join citycodemap c on a.idcitycodemap=c.idcitycodemap where a.name=?";
		/*
		 * Spring中jdbcTemplate的用法实例（一）
		 * http://1358440610-qq-com.iteye.com/blog/1826816
		 */
		SessionUserInfo sui = null;
		try{			
			sui = this.getJdbcTemplate().queryForObject(sql,
					new Object[]{userName},
					  new RowMapper<SessionUserInfo>(){
				@Override  
                public SessionUserInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
					SessionUserInfo sui  = new SessionUserInfo();
					sui.setStationid(rs.getString("stationid"));
					sui.setIdCityCodeMap(rs.getString("idCityCodeMap"));
					sui.setStationname(rs.getString("stationName"));
					sui.setIdStationinfo(rs.getString("idStationinfo"));
					sui.setChinaPetroCode(rs.getString("chinaPetroCode"));	
                    return sui; 
                }});			
		}catch(Exception e)
		{
			logger.debug(e.getMessage());
		}
		
		return sui;		
	}

多条记录

示例一：返回List<EscortManager>类型

			sql = "select a.*,b.cityname from escortManager a left join Citycodemap b on a.idcitycodemap=b.idcitycodemap where a.idCityCodeMap=? order by idEscortManager,a.idcitycodemap";
			return jdbcTemplate.query(sql, new Object[]{idCityCodeMap}, new RowMapper<EscortManager>(){
				@Override  
	            public EscortManager mapRow(ResultSet rs,int rowNum)throws SQLException {  
	                EscortManager em  = new EscortManager();
	                em.setIdEscortManager(rs.getString("idEscortManager"));
	                em.setLoginname(rs.getString("loginname"));
	                em.setIdCityCodeMap(rs.getString("idCityCodeMap"));
	                return em;  
	            }});

示例二：类属类

		String sql = "select a.idescortvehicle,a.platenumber from escortvehicle a "  ;
		return this.getJdbcTemplate().query(sql, new UserRowMapper());

类属类

	class UserRowMapper implements RowMapper<EscortVehicle> {
		@Override
		public EscortVehicle mapRow(ResultSet rs, int rowNum) throws SQLException {
			
			EscortVehicle ev = new EscortVehicle();
			ev.setIdEscortVehicle(rs.getString("idEscortVehicle"));
			ev.setPlateNumber(rs.getString("plateNumber"));
			//ev.setStationName(rs.getString("stationName"));
			return ev;
		}
	}

备注

[1]关于timestamp类型

列出createtime字段（timestamp类型）为今天的记录
SELECT * FROM cashbox where TO_CHAR(createtime,'YY/MM/DD') = TO_CHAR(SYSDATE,'YY/MM/DD')
其中TO_CHAR(SYSDATE,'YY/MM/DD')返回16/06/14.

timestamp类型比较示例
where = "and to_timestamp('"+beginDate+"','YYYY-MM-DD') <= a.createTime ";