springboot干货——（五）JdbcTemplates访问数据库

野性酷女 2021-09-26 08:04 334阅读 0赞

## 需要加入的依赖 ##

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

## 项目结构图 ##

![Center][]

## application.properties文件 ##

spring.datasource.url=jdbc:mysql://localhost:3306/test
    spring.datasource.username = root
    spring.datasource.password = XXXX
    spring.datasource.driver-class-name = com.mysql.jdbc.Driver

## Bean(domain) ##

package com.gwd.domain;
    
    public class User {
        private Integer id;
    
        private String name;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name == null ? null : name.trim();
        }
    }

## Dao ##

package com.gwd.dao;
    
    import com.gwd.domain.User;
    
    public interface IUserDao {
    	User getUserById(Integer id);
    }

## DaoImpl ##

package com.gwd.dao.impl;
    
    import java.util.List;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Repository;
    
    import com.gwd.dao.IUserDao;
    import com.gwd.domain.User;
    
    @Repository
    public class IUserDaoImpl implements IUserDao{
    	@Autowired
    	private JdbcTemplate jdbcTemplate;
    	
    	@Override
    	public User getUserById(Integer id) {
    		// TODO Auto-generated method stub
    		List<User> list = jdbcTemplate.query("select * from stu where id = ?", new Object[]{id},new BeanPropertyRowMapper<User>(User.class));
    		
    		if(list.size()>0) {
    			return list.get(0);
    		}
    		return null;
    	}
    
    }

## Service ##

package com.gwd.service;
    
    import com.gwd.domain.User;
    
    public interface StuService {
    	public User getById(int id);
    }

## ServiceImpl ##

package com.gwd.service.impl;
    
    import java.io.Serializable;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.gwd.dao.IUserDao;
    import com.gwd.domain.User;
    import com.gwd.service.StuService;
    @Service
    public class StuServiceImpl implements StuService,Serializable{
    	/**
    	 * 
    	 */
    	private static final long serialVersionUID = 1L;
    	@Autowired
    	private IUserDao iUserDao;
    	@Override
    	public User getById(int id) {
    		// TODO Auto-generated method stub
    		User user = iUserDao.getUserById(id);
    		return user;
    	}
    
    }

## Controller ##

package com.gwd.controller;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.ResponseBody;
    import org.springframework.web.bind.annotation.RestController;
    import com.gwd.domain.User;
    import com.gwd.service.StuService;
    
    @RestController
    public class StuController {
    	@Autowired
    	private StuService stuService;
    	
    	@RequestMapping("/index")
    	@ResponseBody
    	public String index() {
    		User stu = stuService.getById(1);
    		return stu.getName();
    	}
    }

## 运行类（数据源要注意） ##

package com.gwd;
    import javax.sql.DataSource;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.context.annotation.Bean;
    import org.springframework.core.env.Environment;
    
    import com.alibaba.druid.pool.DruidDataSource;
    
    @SpringBootApplication
    public class SpringBootTestApplication {
    	public static void main(String[] args) {
    		SpringApplication.run(SpringBootTestApplication.class, args);
    	}
    
    	@Autowired
    	private Environment env;
    
    	//destroy-method="close"的作用是当数据库连接不使用的时候,就把该连接重新放到数据池中,方便下次使用调用.
    	@Bean(destroyMethod =  "close")
    	public DataSource dataSource() {
    		DruidDataSource dataSource = new DruidDataSource();
    		dataSource.setUrl(env.getProperty("spring.datasource.url"));
    		dataSource.setUsername(env.getProperty("spring.datasource.username"));//用户名
    		dataSource.setPassword(env.getProperty("spring.datasource.password"));//密码
    		dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
    		dataSource.setInitialSize(2);//初始化时建立物理连接的个数
    		dataSource.setMaxActive(20);//最大连接池数量
    		dataSource.setMinIdle(0);//最小连接池数量
    		dataSource.setMaxWait(60000);//获取连接时最大等待时间，单位毫秒。
    		dataSource.setValidationQuery("SELECT 1");//用来检测连接是否有效的sql
    		dataSource.setTestOnBorrow(false);//申请连接时执行validationQuery检测连接是否有效
    		dataSource.setTestWhileIdle(true);//建议配置为true，不影响性能，并且保证安全性。
    		dataSource.setPoolPreparedStatements(false);//是否缓存preparedStatement，也就是PSCache
    		return dataSource;
    	}
    }

![Center 1][]

**JdbcTemplate文档：[JdbcTemplate API][]**

[Center]: /images/20210923/b52415a8b3804fbd9b6d34c3697c28c6.png
[Center 1]: /images/20210923/85b992581a554677afdda5b196246b19.png
[JdbcTemplate API]: https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html