Spring Boot—14JdbcTemplate

pom.xml

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.11</version>
</dependency>                
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid-spring-boot-starter</artifactId>
   <version>1.1.9</version>           
</dependency>

 

application.properties

# 
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

# 旧的JDBC驱动的写法(新的写法见下一节JPA中的参数)
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=false
spring.datasource.username=root
spring.datasource.password=gis
# 
spring.datasource.initialSize=1
spring.datasource.minIdle=1
spring.datasource.maxActive=5
# 
spring.datasource.maxWait=60000
# 
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 
spring.datasource.filters=stat,wall,log
spring.datasource.logSlowSql=true

 

连接池的配置类

package com.smartmap.sample.test.conf;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;

@Configuration
public class DataSourceConfiguration {

    private final Log logger = LogFactory.getLog(DataSourceConfiguration.class);

    @Value("${spring.datasource.url}")
    private String dbUrl;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.initialSize}")
    private int initialSize;

    @Value("${spring.datasource.minIdle}")
    private int minIdle;

    @Value("${spring.datasource.maxActive}")
    private int maxActive;

    @Value("${spring.datasource.maxWait}")
    private int maxWait;

    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;

    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;

    @Value("${spring.datasource.filters}")
    private String filters;

    @Value("${spring.datasource.logSlowSql}")
    private String logSlowSql;

    @Bean
    public ServletRegistrationBean<StatViewServlet> druidServlet() {
        ServletRegistrationBean<StatViewServlet> reg = new ServletRegistrationBean<StatViewServlet>();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        reg.addInitParameter("loginUsername", username);
        reg.addInitParameter("loginPassword", password);
        reg.addInitParameter("logSlowSql", logSlowSql);
        return reg;
    }

    @Bean
    public FilterRegistrationBean<WebStatFilter> filterRegistrationBean() {
        FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<WebStatFilter>();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        return filterRegistrationBean;
    }

    @Bean
    public DataSource druidDataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            logger.error("druid configuration initialization filter", e);
        }
        return datasource;
    }
}

 

SQL

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_bin DEFAULT NULL COMMENT '名称',
  `department_id` int(11) DEFAULT NULL,
  `create_time` date DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


INSERT INTO `user` VALUES (1, 'helo', 1, '2017-4-21 11:52:41');
INSERT INTO `department` VALUES ('1', '研发部门');

 

DAO类

package com.smartmap.sample.test.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.smartmap.sample.test.dao.UserDao;
import com.smartmap.sample.test.entity.User;

@Repository
public class UserDaoImpl implements UserDao {

    @Autowired
    JdbcTemplate jdbcTempalte;

    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public Integer totalUserInDepartment(Long departmentId) {
        String sql = "select count(1) from user where department_id=?";
        Integer count = jdbcTempalte.queryForObject(sql, Integer.class, departmentId);
        return count;
    }

    public Integer totalUserInDepartment2(Long departmentId) {
        String sql = "select count(1) from user where department_id=:deptId";
        MapSqlParameterSource namedParameters = new MapSqlParameterSource();
        namedParameters.addValue("deptId", departmentId);
        Integer count = namedParameterJdbcTemplate.queryForObject(sql, namedParameters, Integer.class);
        return count;
    }

    public User findUserById(Long userId) {
        String sql = "select * from user where id=?";
        User user = jdbcTempalte.queryForObject(sql, new RowMapper<User>() {
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setName(rs.getString("name"));
                user.setDepartmentId(rs.getLong("department_id"));
                return user;
            }
        }, 1);
        return user;
    }

    public List<User> getUserAll() {
        String sql = "select * from user";
        List<User> users = jdbcTempalte.query(sql, new UserRowMapper());
        return users;
    }

    public List<User> getUserByDepartmentId(Long departmenetId) {
        String sql = "select * from user where department_id=? ";
        List<User> user = jdbcTempalte.query(sql, new UserRowMapper(), 1);
        return user;
    }

    public Map findUserById2(Integer userId) {
        String sql = "select * from user where id=?";
        Map map = jdbcTempalte.queryForMap(sql, userId);
        return map;
    }

    public void updateInfo(User user) {
        String sql = "update user set name=? and departmet_id=? where id = ?";
        jdbcTempalte.update(sql, user.getName(), user.getDepartmentId(), user.getId());
    }

    public void updateInfoByNamedJdbc(User user) {
        String sql = "update user set name=:name and departmet_id=:departmentId where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(user);
        namedParameterJdbcTemplate.update(sql, source);

    }

    public Integer insertUser(final User user) {
        final String sql = "insert into user (name, departmet_id ) values (?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();//用于获取数据生成的主键
        jdbcTempalte.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, new String[] { "id" });
                ps.setString(1, user.getName());
                ps.setLong(2, user.getDepartmentId());
                return ps;
            }
        }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    public Integer deleteUser(Long userId) {
        final String sql = "delete from user where id=?";
        int count = jdbcTempalte.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setLong(1, userId);
                return ps;
            }
        });
        return count;
    }

    static class UserRowMapper implements RowMapper<User> {
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setName(rs.getString("name"));
            user.setCreateTime(rs.getDate("create_time"));
            user.setDepartmentId(rs.getLong("department_id"));
            return user;
        }
    }
}

 

Service类

package com.smartmap.sample.test.service.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.smartmap.sample.test.dao.UserDao;
import com.smartmap.sample.test.entity.User;
import com.smartmap.sample.test.service.UserService;

@Transactional
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    UserDao userDao;

    @Override
    public List<User> allUser() {
        /*
         * List<User> userList = new LinkedList<User>(); userList.add(new User("123",
         * "root"));
         */

        return userDao.getUserAll();
    }

    @Override
    public User getUserById(Long userId) {
        /* return new User("123", "root"); */

        return userDao.findUserById(userId);
    }

    @Override
    public User save(User user) {
        /* return new User("123", "root"); */
        userDao.insertUser(user);
        return user;
    }

    @Override
    public int delete(Long userId) {
        /* return 1; */

        return userDao.deleteUser(userId);
    }

}

 

RestController类

package com.smartmap.sample.test.controller.rest;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import com.smartmap.sample.test.entity.User;
import com.smartmap.sample.test.service.UserService;

@RestController
@RequestMapping("/api/v1.1/system/user")
public class UserRestController {
    private final Log logger = LogFactory.getLog(UserRestController.class);

    @Autowired
    UserService userService;

    /**
     * 查询所有用户
     *
     * curl -XGET 'http://127.0.0.1:8080/api/v1.1/system/user/'
     * 
     * @return
     */
    @GetMapping("/")
    public List<User> getAllUsers() {
        return userService.allUser();
    }

    /**
     * 根据Id查询用户
     * 
     * curl -XGET 'http://127.0.0.1:8080/api/v1.1/system/user/123'
     * 
     * @param userId
     * @return
     */
    @GetMapping("/{userId}")
    public User getUserById(@PathVariable("userId") Long userId) {
        return userService.getUserById(userId);
    }

    /**
     * 翻页查询用户
     * 
     * curl -XGET
     * 'http://127.0.0.1:8080/api/v1.1/system/user/query?offset=123&limit=456&sortBy=789&sortOrder=456'
     * 
     * @param offset
     * @param limit
     * @param sortBy
     * @param sortOrder
     * @return
     */
    @GetMapping("/query")
    public List<User> queryUserById(@RequestParam("offset") int offset, @RequestParam("limit") int limit,
            @RequestParam("sortBy") int sortBy, @RequestParam("sortOrder") int sortOrder) {
        logger.info(String.valueOf(offset));
        logger.info(String.valueOf(limit));
        logger.info(String.valueOf(sortBy));
        logger.info(String.valueOf(sortOrder));
        return userService.allUser();
    }

    /**
     * 添加用户
     * 
     * curl -XPOST 'http://127.0.0.1:8080/api/v1.1/system/user/'
     * -H'Content-type:application/json;charset=UTF-8' -d ' { "id": "123",
     * "name":"123" } '
     * 
     * @param user
     * @return
     */
    @PostMapping("/")
    public User addUse(@RequestBody User user) {
        System.out.println(user.getName());
        return userService.save(user);
    }

    /**
     * 更新用户
     * 
     * curl -XPUT 'http://127.0.0.1:8080/api/v1.1/system/user/'
     * -H'Content-type:application/json;charset=UTF-8' -d ' { "id": "123",
     * "name":"123" } '
     * 
     * @param user
     * @return
     */
    @PutMapping("/")
    public User updateUse(@RequestBody User user) {
        return userService.save(user);
    }

    /**
     * 删除用户
     * 
     * curl -XDELETE 'http://127.0.0.1:8080/api/v1.1/system/user/123'
     * 
     * @param userId
     * @return
     */
    @DeleteMapping("/{userId}")
    public String deleteUser(@PathVariable("userId") Long userId) {
        if (userService.delete(userId) > 0) {
            return "{success:true, message:'delete success'}";
        } else {
            return "{success:false, message:'delete fail'}";
        }
    }

}