springboot postgresql druid连接池和jpa，jdbctemplate执行sql查询

1.maven依赖配置(pom.xml)

 1         <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>  
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.20</version>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

2.数据源配置（application.properties）

spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
#druid database connect pool
#config database connect info
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://127.0.0.1:5432/druiddb
spring.datasource.username=postgres
spring.datasource.password=postgres
#config druid
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
spring.datasource.maxWait=10000
spring.datasource.timeBetweenEvictionRunMillis=60000
spring.datasource.minEvictableIdleTimeMillis=10000
spring.datasource.validationQuery=SELECT 'x'
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=true
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=10
spring.datasource.filters=stat

3.druid管理器配置

工程结构如下

@Configuration
public class DruidConfiguration {
    private static Logger log = LoggerFactory.getLogger(DruidConfiguration.class);
    @Bean  
    @ConfigurationProperties(prefix="spring.datasource")  
    public DataSource druid() {  
        return new DruidDataSource();  
    }  
    /**
     * 配置druid管理页面的访问控制
     * 访问网址: http://127.0.0.1:8080/druid
     * @return
     */
    @Bean
    public ServletRegistrationBean<Servlet> druidServlet() {
        log.info("init Druid Servlet Configuration");
        ServletRegistrationBean<Servlet> servletRegistrationBean = new ServletRegistrationBean<>();
        servletRegistrationBean.setServlet(new StatViewServlet());  //配置一个拦截器
        servletRegistrationBean.addUrlMappings("/druid/*");    //指定拦截器只拦截druid管理页面的请求
        HashMap<String, String> initParam = new HashMap<String,String>();
        initParam.put("loginUsername", "admin");    //登录druid管理页面的用户名
        initParam.put("loginPassword", "admin");    //登录druid管理页面的密码
        initParam.put("resetEnable", "true");       //是否允许重置druid的统计信息
        initParam.put("allow", "");         //ip白名单，如果没有设置或为空，则表示允许所有访问
        servletRegistrationBean.setInitParameters(initParam);
        return servletRegistrationBean;
    }
    
    @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/*");
        return filterRegistrationBean;
    }
    
}

4.实体类

//建表语句
//create table USERS(id INT,age int,name VARCHAR, PRIMARY KEY(id));
/**
 * @DESC users表的实体类
 * @author guchuang
 */
@Entity
@Table(name="users")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    private int id;
    private int age;
    private String name;
    
    public User() {
    }
    public User(int id, int age, String name) {
        this.id = id;
        this.age = age;
        this.name = name;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", age=" + age + ", name=" + name + "]";
    }
}

/**
 * @DESC 实现jpa接口，拥有jpa提供的默认crud操作，无需自己写实现代码（除非要扩展功能）
 * @author guchuang
 *
 */
public interface UserRepository extends JpaRepository<User,Integer> {
    
}

5.jdbcTemplate实现数据库读写

@RestController
@RequestMapping("/jpa")
public class JpaUserController {
    @Autowired
    private UserRepository userRepository;  
    
    @GetMapping(value="/user")
    public List<User> getUser() throws SQLException {
        List<User> users = userRepository.findAll();
        return users;
    }
    @GetMapping(value="/user/{id}")
    public User getUser(@PathVariable(value="id") int id) throws SQLException {
        Optional<User> user = userRepository.findById(id);
        return user.get();
    }
    @PostMapping(value = "/user")
    public String saveUser(@RequestBody User user){
        userRepository.save(user);
        return "success to add user";
    }
    @PutMapping(value = "/user")
    public String updateUser(@RequestBody User user){
        userRepository.deleteById(user.getId());
        userRepository.save(user);
        return "success to update user";
    }
    @DeleteMapping(value = "/user/{id}")
    public String deleteUser(@PathVariable(value="id") int id){
        userRepository.deleteById(id);
        return "success to delete user";
    }
}

  

6.jpa实现数据库读写

@RestController
@RequestMapping("/jpa")
public class JpaUserController {
    @Autowired
    private UserRepository userRepository;  
    
    @GetMapping(value="/user")
    public List<User> getUser() throws SQLException {
        List<User> users = userRepository.findAll();
        return users;
    }
    @GetMapping(value="/user/{id}")
    public User getUser(@PathVariable(value="id") int id) throws SQLException {
        Optional<User> user = userRepository.findById(id);
        return user.get();
    }
    @PostMapping(value = "/user")
    public String saveUser(@RequestBody User user){
        userRepository.save(user);
        return "success to add user";
    }
    @PutMapping(value = "/user")
    public String updateUser(@RequestBody User user){
        userRepository.deleteById(user.getId());
        userRepository.save(user);
        return "success to update user";
    }
    @DeleteMapping(value = "/user/{id}")
    public String deleteUser(@PathVariable(value="id") int id){
        userRepository.deleteById(id);
        return "success to delete user";
    }
}

7.druid管理页面

　　

　　

  　　 8.测试例（postman）　

　　备注：test_ip_port为postman中的变量，实际替换为ip:port即可

　　　　　　　　 

　　　　

9.附件

postman编辑的测试例，暂时不清楚如何上传