【JdbcTemplate】Spring 整合 JdbcTemplate

一、Spring 项目整合 JdbcTemplate

1.1.1 引入 Maven 依赖

<dependencies>
    <!--    Spring    -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.0.2.RELEASE</version>
    </dependency>

    <!--    lombok    -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.12</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-log4j12</artifactId>
        <version>1.7.25</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.25</version>
    </dependency>

    <!--    jdbctemplate    -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>5.0.8.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.0.8.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.11</version>
    </dependency>
    <dependency>
        <groupId>commons-dbcp</groupId>
        <artifactId>commons-dbcp</artifactId>
        <version>1.4</version>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.0.9</version>
    </dependency>
</dependencies>

 

 

1.2 编写配置

新增配置类：JdbcConfig.java

public class JdbcConfig {

    @Value("${jdbc.driver}")
    private String driver;

    @Value("${jdbc.url}")
    private String url;

    @Value("${jdbc.username}")
    private String user;

    @Value("${jdbc.password}")
    private String password;

    @Bean(name="dataSource")
    public DataSource createDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driver);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "jdbcTemplate")
    public JdbcTemplate createJdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

增加配置文件：

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://xxxxxx:3306/test?&useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true
jdbc.username=root
jdbc.password=root

增加主要的配置文件 SpringConfiguration.java

@Component
@ComponentScan({"com.tom"})
@Import(JdbcConfig.class)
@PropertySource("classpath:jdbcConfig.properties")
public class SpringConfiguration {
}

 

1.3 修改启动类

public static void main(String[] args) {
    ApplicationContext ac = new AnnotationConfigApplicationContext(SpringConfiguration.class);
    UserService userService = ac.getBean("userService", UserService.class);
    userService.findById(1);
}

 

1.4 测试

public interface UserService {
    public void findById(int id);
}

@Slf4j
@Service("userService")
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;

    /**
     * 根据id查找用户
     * @param id
     */
    public void findById(int id) {
        log.info("findById");
        userDao.findOneById();
    }
}

 

二、使用

@Repository
@Slf4j
public class UserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

public void findAll() {
    log.info("dao findAll");
    String sql = "select * from user";
    List<User> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
    for (User user : list) {
        log.info(user.toString());
    }
}

    public void findOneById(int id) {
        log.info("dao findOneById");
        String sql = "select * from user where id = ?";
        RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
        User user = jdbcTemplate.queryForObject(sql, rowMapper, id);
        log.info(user.toString());
    }

    public void count() {
        String sql = "select count(*) from user where money > ?";
        int count = jdbcTemplate.queryForObject(sql, Integer.class, 500);
        log.info(String.valueOf(count));
    }

    public void insert(User user) {
        String sql = "insert into user(name, money) values (?,?)";
        int res = jdbcTemplate.update(sql, user.getName(), user.getMoney());
        log.info(String.valueOf(res));
    }
}