添加数据库依赖
- <dependency>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-jdbc</artifactId>
- </dependency>
- <dependency>
- <groupId>MySQL</groupId>
- <artifactId>MySQL-connector-java</artifactId>
- </dependency>
在
application.properties
配置文件中配置数据源
- spring.datasource.url = jdbc:MySQL://localhost:3306/spring?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT+8
- spring.datasource.username = root
- spring.datasource.password = 123456
- spring.datasource.driver-class-name = com.MySQL.cj.jdbc.Driver
添加数据池依赖, 这里用的是阿里巴巴的数据池
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>druid</artifactId>
- <version>1.0.19</version>
- </dependency>
在启动类
Chapter1Application.java
中读取数据源配置信息, Spring Boot 会自动的用我们配置的这个 DataSource. 添加如下代码
- @Autowired
- private Environment environment;
- public DataSource dataSource(destroyMethod = "close") {
- DruidDataSource dataSource = new DruidDataSource();
- dataSource.setUrl(environment.getProperty("datasource.url"));
- dataSource.setUsername(environment.getProperty("datasource.username"));
- dataSource.setPassword(environment.getProperty("datasource.password"));
- dataSource.setDriverClassName(environment.getProperty("datasource.driver-class-name"));
- dataSource.setInitialSize(5);// 初始化时建立物理连接的个数
- dataSource.setMaxActive(20);// 最大连接池数量
- dataSource.setMinIdle(0);// 最小连接池数量
- dataSource.setMaxWait(50000);// 获取连接时最大等待时间, 单位毫秒.
- dataSource.setValidationQuery("SELECT 1");// 用来检测连接是否有效的 sql
- return dataSource;
- }
下面就用我们配置的这个数据源信息, 用 JdbcTemplate 来与数据库进行数据交互
本地创建 spring 数据库, 再创建一张 user 表, 表属性如下, 自行填几条数据:
1.PNG
简单的做了个查询的例子:
创建实体文件: User.java
- public class User {
- private int id;
- private String name;
- private int age;
- private String address;
- private String phone;
- //set and get
- }
controller 层: UserController.java:
- @RestController
- public class UserController {
- @Autowired
- private UserService userService;
- private Logger logger = LoggerFactory.getLogger(this.getClass());
- @RequestMapping(value = "/queryUserList",method = RequestMethod.GET,produces="application/json;charset=UTF-8")
- @ResponseBody
- public String queryLearnList(HttpServletRequest request , HttpServletResponse response){
- String name = request.getParameter("name");
- String phone = request.getParameter("phone");
- Map<String,Object> params = new HashMap<String,Object>();
- params.put("name", name);
- params.put("phone", phone);
- List userList =userService.queryUserList(params);
- return JSONArray.fromObject(userList).toString();
- }
service 层: 接口 UserService.java 和实现类
UserServiceImpl.java
- public interface UserService {
- List queryUserList(Map<String, Object> params);
- }
- @Service
- public class UserServiceImpl implements UserService {
- @Autowired
- UserDao userDao;
- @Override
- public List queryUserList(Map<String,Object> params) {
- return userDao.queryUserList(params);
- }
- }
dao 层: 接口 UserDao.java 和实现类 UserDaoImpl.java
- public interface UserDao {
- public List queryUserList(Map<String, Object> params);
- }
UserDaoImpl .java 注入 JdbcTemplate,spring boot 会自动选择我们配置好的数据源:
- @Repository
- public class UserDaoImpl implements UserDao{
- @Autowired
- private JdbcTemplate jdbcTemplate;// 这里直接引用即可
- @Override
- public List queryUserList(Map<String, Object> params) {
- StringBuffer sql =new StringBuffer();
- sql.append("select * from user where 1=1");
- if(!StringUtil.isNull((String)params.get("name"))){
- sql.append("and name like'%").append((String)params.get("name")).append("%'");
- }
- if(!StringUtil.isNull((String)params.get("phone"))){
- sql.append("and phone like'%").append((String)params.get("phone")).append("%'");
- }
- List<User> list = jdbcTemplate.query(sql.toString(),new BeanPropertyRowMapper(User.class));
- return list;
- }
- }
结果
运行项目, 浏览器输入: http://localhost:8080/queryUserList, 得到如下结果
1.PNG
总结
在此实践过程可能会出现以下问题:
第一个错误
报错信息:
1.PNG
原因: 项目打了 jar 包, 一旦运行就会有两个启动类, 指定其中一个即可
解决办法: 在 pom.xml 的 plugin 下面添加如下配置即可
- <configuration>
- <mainClass>com.mlin.ChapterApplication</mainClass>
- </configuration>
第二个错误
报错信息:
java.sql.SQLException: The server time zone value 'Öйú±ê*¼Ê±¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
原因: 没有配置时区信息
解决办法: 在配置数据源的时候加上 serverTimezone=GMT+8
datasource.url = jdbc:MySQL://localhost:3306/spring?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT+8
第三个错误
报错信息: 就是在用到 return JSONArray.fromObject(userList).toString(); 时, 引入 net.sf.JSON-lib 依赖一直引入不进去
原因: 要指定 jdk
解决办法: 加入 < classifier>jdk15</classifier > 即可, 但是改成 jdk8 或是 jdk7 不行:
- <dependency>
- <groupId.NET.sf.JSON-lib</groupId>
- <artifactId>JSON-lib</artifactId>
- <version>2.4</version>
- <classifier>jdk15</classifier>
- </dependency>
原创作者: 梦凌小样
来源: http://www.jianshu.com/p/45eb56f448ae