spring boot使用jdbcTemplate

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

另外需要请注意的是要手动创建表和初始化数据库数据