如果有整合jpa了, 那么在dao中直接, 不需要引入依赖
@Resource
private JdbcTempalte jdbcTempalte;
如果没有的话, 就先在pom.xml中加入依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
编写dao声明为repository
package com.iwhere.test.dao; import javax.annotation.Resource; import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository; import com.iwhere.test.demo.Demo; /**
* 使用jdbcTemplate操作数据库
* @author 231
*
*/
@Repository
public class JdbcDao { @Resource
private JdbcTemplate jdbcTemplate; /**
* 通过id获取对象
* @return
*/
public Demo getById(Integer id) {
String sql = "select id, name, sex from demo where id = ?";
BeanPropertyRowMapper<Demo> mapper = new BeanPropertyRowMapper<Demo>(Demo.class);
return jdbcTemplate.queryForObject(sql, mapper, id);
} }
之后即可在service和controller层进行调用了
更多操作方式
public List<AreaDto> getAreaDtos() {
String sql = "select * from t_area ";
return (List<AreaDto>) template.query(sql, new BeanPropertyRowMapper(AreaDto.class));
}
public List<AreaDto> getAreaDtos1() {
String sql = "select * from t_area ";
return (List<AreaDto>) template.query(sql, new AreaRowMapper());
}
public Map<String, Object> getBuilds() {
String sql = "select buildid,buildname from t_building ";
List<BuildingDto> list = this.template.query(sql, new BeanPropertyRowMapper(BuildingDto.class));
Map<String, Object> map = new HashMap<String, Object>();
for (BuildingDto build : list) {
map.put(String.valueOf(build.getBuildid()), build.getBuildname());
}
return map;
}
public Map<String, Object> getFloors(String build) {
String sql = "select floorid ,floorname from t_floor where build_id=?";
List<FloorDto> list = this.template.query(sql, new Object[] { build }, new BeanPropertyRowMapper(FloorDto.class));
Map<String, Object> map = new LinkedHashMap<String, Object>();
for (FloorDto floorDto : list) {
System.out.println(floorDto.getFloorid());
map.put(String.valueOf(floorDto.getFloorid()), floorDto.getFloorname());
}
return map;
}
public Map<String, Object> getAreas(String build, String floor) {
String sql = "select areaid ,area_name from t_area where build_id=? and floor_id=?";
List<AreaDto> list = this.template.query(sql, new Object[] { build, floor }, new BeanPropertyRowMapper(AreaDto.class));
Map<String, Object> map = new LinkedHashMap<String, Object>();
for (AreaDto areaDto : list) {
System.out.println(areaDto.getAreaid());
map.put(String.valueOf(areaDto.getAreaid()), areaDto.getArea_name());
}
return map;
}
public List<StoreDto> getStoreDtos(int buildid, int floorid, int areaid) {
String sql = "select * from t_store where build_id=? and floor_id=? and area_id=?";
return (List<StoreDto>) template.query(sql, new Object[] { buildid, floorid, areaid }, new BeanPropertyRowMapper(
StoreDto.class));
}
public List<BuildingDto> getBuildingDto() {
String sql = "select * from t_building ";
return (List<BuildingDto>) template.query(sql, new BeanPropertyRowMapper(BuildingDto.class));
}
public List<FloorDto> getFloorDtos(int buildid) {
String sql = "select * from t_floor where build_id=? ";
return (List<FloorDto>) template.query(sql, new Object[] { buildid }, new BeanPropertyRowMapper(FloorDto.class));
}
public List<AreaDto> getAreaDto(int buildid, int floorid) {
String sql = "select * from t_area where build_id=? and floor_id=?";
return (List<AreaDto>) template.query(sql, new Object[] { buildid, floorid }, new BeanPropertyRowMapper(AreaDto.class));
}
public AreaDto getAreaDto(int id) {
String sql = "select * from t_area where areaid=?";
return template.queryForObject(sql, new Object[] { id }, new BeanPropertyRowMapper(AreaDto.class));
}
public BuildingDto getBuildingDto(int buildId) {
String sql = "select * from t_building where buildid=?";
return template.queryForObject(sql, new Object[] { buildId }, new BeanPropertyRowMapper(BuildingDto.class));
}
public FloorDto getFloorDto(int floorId) {
String sql = "select * from t_floor where floorid=?";
return template.queryForObject(sql, new Object[] { floorId }, new BeanPropertyRowMapper(FloorDto.class));
}
public List<StoreDto> getAllStores() {
String sql = "select * from t_store ";
return (List<StoreDto>) template.query(sql, new BeanPropertyRowMapper(StoreDto.class));
}
public String storeName(int storeId) {
String sql = "select storename from t_store where id=?";
return template.queryForObject(sql, new Object[] { storeId }, String.class);
}
public StoreDto getStoreById(int storeInt) {
String sql = "select * from t_store where id=?";
return template.queryForObject(sql, new Object[] { storeInt }, new BeanPropertyRowMapper(StoreDto.class));
}
public int getCountStore() {
String sql = "select count(id) from t_store";
return this.template.queryForInt(sql);
}
public void saveBuild(BuildingDto buildingDto) {
}
public void deleteBuildById(int buildid) {
String sql = "delete from t_store where id=?";
this.template.update(sql, buildid);
}
public void updateBuildById(BuildingDto buildingDto) {
}
}
class AreaRowMapper implements RowMapper {
public Object mapRow(ResultSet rs, int index) throws SQLException {
AreaDto areaDto = new AreaDto();
areaDto.setAreaid(rs.getInt("areaid"));
areaDto.setArea_name(rs.getString("area_name"));
areaDto.setArea_detail(rs.getString("area_detail"));
areaDto.setBuild_id(rs.getInt("build_id"));
areaDto.setFloor_id(rs.getInt("floor_id"));
areaDto.setInsert_time(rs.getTimestamp("insert_time"));
areaDto.setOperate_id(rs.getInt("operate_id"));
areaDto.setUpdate_time(rs.getTimestamp("update_time"));
areaDto.setRegion_name(rs.getString("region_name"));
areaDto.setSortno(rs.getString("sortno"));
return areaDto;
}