SpringBoot（四） SpringBoot整合JdbcTemplate
一、数据准备
CREATE TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘ID’,
`username` varchar(50) NOT NULL COMMENT ‘用户名’,
`age` int(11) NOT NULL COMMENT ‘年龄’,
`ctm` datetime NOT NULL COMMENT ‘创建时间’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES(‘张三’, ’18’, NOW()) ;
INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES(‘李四’, ’20’, NOW()) ;
INSERT INTO `db_test`.`tb_user` (`username`, `age`, `ctm`) VALUES(‘王五’, ’19’, NOW()) ;
二、引入依赖：
————————————————
<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.4.RELEASE</version> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <!-- jdbcTemplate --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <!-- MySQL连接 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <optional>true</optional> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-api --> <dependency> <groupId>org.apache.logging.log4j</groupId> <artifactId>log4j-api</artifactId> <version>2.11.2</version> </dependency> <!-- Spring-Mybatis --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.0</version> </dependency> <!-- MySQL --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</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-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <optional>true</optional> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <fork>true</fork> </configuration> </plugin> </plugins> </build> </project>
三、数据库配置文件
一如既往，我们采用yaml文件配置，当然properties文件也是一样。
注意点，SpringBoot默认采用tomcat-jdbc连接池，如果需要C3P0，DBCP，Druid等作为连接池，需要加入相关依赖以及配置，这里不作说明，采用默认配置即可。
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/db_user
username: root
password: root
————————————————
四、代码
项目结构如下：
实体类User.class：
package com.demo.bean; import java.util.Date; public class User { public int id; public String username; public int age; public Date ctm; public User() { } public User(String username, int age) { this.username = username; this.age = age; this.ctm = new Date(); } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public Date getCtm() { return ctm; } public void setCtm(Date ctm) { this.ctm = ctm; } }
UserDao：
package com.demo.dao; import java.util.List; import com.demo.bean.User; public interface UserDao { public User getUserById(Integer id); public List<User> getUserList(); }
UserDaoImpl：
package com.demo.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.demo.bean.User; import com.demo.dao.UserDao; @Repository public class UserDaoImpl implements UserDao{ @Autowired private JdbcTemplate jdbcTemplate; @Override public User getUserById(Integer id) { List<User> list = jdbcTemplate.query("select * from tb_user where id = ?", new Object[]{id}, new BeanPropertyRowMapper(User.class)); if(list!=null && list.size()>0){ return list.get(0); }else{ return null; } } @Override public List<User> getUserList() { List<User> list = jdbcTemplate.query("select * from tb_user", new Object[]{}, new BeanPropertyRowMapper(User.class)); if(list!=null && list.size()>0){ return list; }else{ return null; } } }
UserService：
package com.demo.service; import java.util.List; import com.demo.bean.User; public interface UserService { User getUserById(Integer id); public List<User> getUserList(); }
UserServiceImpl：
package com.demo.service.impl; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import com.demo.bean.User; import com.demo.dao.UserDao; import com.demo.service.UserService; @Service public class UserServiceImpl implements UserService{ @Autowired private UserDao userDao; @Override public User getUserById(Integer id) { return userDao.getUserById(id); } @Override public List<User> getUserList() { return userDao.getUserList(); } }
UserController：
package com.demo.web; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.ResponseEntity; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RestController; import com.demo.bean.JsonResult; import com.demo.bean.User; import com.demo.service.UserService; @RestController public class UserController { @Autowired private UserService userService; @RequestMapping(value = "user/{id}", method = RequestMethod.GET) public ResponseEntity<JsonResult> getUserById (@PathVariable(value = "id") Integer id){ JsonResult r = new JsonResult(); try { User user = userService.getUserById(id); r.setResult(user); r.setStatus("ok"); } catch (Exception e) { r.setResult(e.getClass().getName() + ":" + e.getMessage()); r.setStatus("error"); e.printStackTrace(); } return ResponseEntity.ok(r); } /** * 查询用户列表 * @return */ @RequestMapping(value = "users", method = RequestMethod.GET) public ResponseEntity<JsonResult> getUserList (){ JsonResult r = new JsonResult(); try { List<User> users = userService.getUserList(); r.setResult(users); r.setStatus("ok"); } catch (Exception e) { r.setResult(e.getClass().getName() + ":" + e.getMessage()); r.setStatus("error"); e.printStackTrace(); } return ResponseEntity.ok(r); } }
JsonResult:
package com.demo.bean; public class JsonResult { private String status = null; private Object result = null; public JsonResult status(String status) { this.status = status; return this; } public String getStatus() { return status; } public void setStatus(String status) { this.status = status; } public Object getResult() { return result; } public void setResult(Object result) { this.result = result; } }
测试：
访问：
http://localhost:8080/user/1
http://localhost:8080/users
相关文章