spring boot使用jdbcTemplate
摘自,使用spring boot 版本为2.0.0.RC2
1.项目结构
2.引入maven依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
3.application.yml
文件
spring:
datasource:
schema: classpath:create-db.sql //一定要加classpath,不然找不到文件
data: classpath:seed-data.sql
continue-on-error: true
4.创建create-db.sql
文件放在resources
根目录下
CREATE TABLE users
(
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
email varchar(100) DEFAULT NULL,
PRIMARY KEY (id)
);
5.创建seed-data.sql
文件,放在resources
根目录下
insert into users(id, name, email) values(1,'John','john@gmail.com');
insert into users(id, name, email) values(2,'Rod','rod@gmail.com');
insert into users(id, name, email) values(3,'Mike','mike@gmail.com');
6.创建实体类User.java
public class User {
private int id;
private String name;
private String email;
//getter and setter omit...
}
7.创建UserRepository.java
和UserRowMapper.java
//UserRepository.java
@Repository
public class UserRepository {
@Autowired
private JdbcTemplate jdbcTemplate;
@Transactional(readOnly = true)
public List<User> findAll(){
return jdbcTemplate.query("select * from users", new UserRowMapper());
}
@Transactional(readOnly = true)
public User findUserById(int id){
return jdbcTemplate.queryForObject("select * from users where id=?",
new Object[]{id}, new UserRowMapper());
}
public User createUser(final User user){
final String sql = "insert into users(name,email) values(?,?)";
KeyHolder holder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement pstmt = connection.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1, user.getName());
pstmt.setString(2, user.getEmail());
return pstmt;
}
}, holder);
int newUserId = holder.getKey().intValue();
user.setId(newUserId);
return user;
}
}
//UserRowMapper.java
public class UserRowMapper implements RowMapper<User> {
@Nullable
@Override
public User mapRow(ResultSet resultSet, int i) throws SQLException {
User user = new User();
user.setId(resultSet.getInt("id"));
user.setEmail(resultSet.getString("email"));
user.setName(resultSet.getString("name"));
return user;
}
}
测试
@RunWith(SpringRunner.class)
@SpringBootTest(classes = {DemoApplication.class})
public class DemoApplicationTests {
@Autowired
private UserRepository userRepository;
@Test
public void findAllUsers() {
List<User> users = userRepository.findAll();
System.out.println(users);
//[
// User{id=1, name='John', email='john@gmail.com'},
// User{id=2, name='Rod', email='rod@gmail.com'},
// User{id=3, name='Mike', email='mike@gmail.com'}
// ]
}
@Test
public void findUserById() {
User user = userRepository.findUserById(1);
System.out.println(user);
//User{id=1, name='John', email='john@gmail.com'}
}
@Test
public void createUser() {
User user = new User(0, "Johnson", "johnson@gmail.com");
User savedUser = userRepository.createUser(user);
User newUser = userRepository.findUserById(savedUser.getId());
System.out.println(newUser);
//User{id=4, name='Johnson', email='johnson@gmail.com'}
}
}
也可以使用mysql数据库代替h2数据库,只不过需要引入mysql的maven依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
修改application.yml
文件为
spring:
datasource:
url: jdbc:mysql://localhost:3306/spboot
username: root
password: 111111
driver-class-name: com.mysql.jdbc.Driver
另外需要请注意的是要手动创建表和初始化数据库数据