Spring(6): 数据库连接

1.概念

Spring JDBC 是 Spring 所提供的持久层技术，以更直接、更简洁的方式使用 JDBC API。

不同形式的数据源配置：

（1）Spring 自带的数据源

依赖包：import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Beanpublic DriverManagerDataSource dataSource() {    DriverManagerDataSource ds = new DriverManagerDataSource();    ds.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));    ds.setUrl(env.getProperty("spring.datasource.url"));    ds.setUsername(env.getProperty("spring.datasource.username"));    ds.setPassword(env.getProperty("spring.datasource.password"));    return ds;}

（2）数据源连接池

依赖包：import org.apache.tomcat.jdbc.pool.DataSource;

@Bean(destroyMethod = "close")public DataSource dataSource() {    DataSource ds = new DataSource();    ds.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));    ds.setUrl(env.getProperty("spring.datasource.url"));    ds.setUsername(env.getProperty("spring.datasource.username"));    ds.setPassword(env.getProperty("spring.datasource.password"));    return ds;}

说明：
设定 destroyMethod = “close” 属性，以使 Spring 容器关闭时，数据源能够正常关闭。

2.使用 JDBC 连接数据库

2.1 Java 配置

@Configurationpublic class JdbcConfig {    @Autowired    public Environment env;    @Bean(destroyMethod = "close")    public DataSource dataSource() {        DataSource ds = new DataSource();        ds.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));        ds.setUrl(env.getProperty("spring.datasource.url"));        ds.setUsername(env.getProperty("spring.datasource.username"));        ds.setPassword(env.getProperty("spring.datasource.password"));        return ds;    }    @Bean    public JdbcTemplate jdbcTemplate() {        JdbcTemplate jt = new JdbcTemplate();        jt.setDataSource(dataSource());        return jt;    }    @Bean    public ForumDao forumDao() {        return new ForumDao();    }}

2.2 XML 配置

在 Spring 配置文件中配置 DAO 的 4 个步骤：
（1）定义 DataSource
（2）定义 JdbcTemplate
（3）声明一个抽象的 ，以便所有 DAO 复用配置 JdbcTemplate 属性的配置。
（4）配置具体的 DAO。

<!--扫描包以注册注解声明的Bean--><context:component-scan base-package="com.chen.jdbc"/><context:property-placeholder location="classpath:jdbc.properties"/><!--配置数据源--><bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close"      p:defaultAutoCommit="false"      p:driverClassName="${jdbc.driverClassName}"      p:url="${jdbc.url}"      p:username="${jdbc.username}"      p:password="${jdbc.password}"/><!--声明JdbcTemplateBean--><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"      p:dataSource-ref="dataSource"/><bean id="forumDao" class="com.chen.jdbc.dao.ForumDao"/>

使用 JdbcTemplate 对数据进行 CURD 操作。

2.3 更改数据代码

@Repositorypublic class ForumDao {    private JdbcTemplate jdbcTemplate;    @Autowired    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {        this.jdbcTemplate = jdbcTemplate;    }    public void addForum(Forum forum) {        String sql = " INSERT INTO t_forum(forum_name, forum_desc) VALUES (?,?) ";        Object[] params = new Object[]{forum.getForumName(), forum.getForumDesc()};        jdbcTemplate.update(sql, params);    }}

2.4 查询数据代码

public Forum findByForumName(final String forumName) {    String sql = " SELECT * FROM t_forum WHERE forum_name = ? ";    final Forum forum = new Forum();    jdbcTemplate.query(sql, new Object[]{forumName}, new RowCallbackHandler() {        @Override        public void processRow(ResultSet rs) throws SQLException {            forum.setForumId(rs.getInt("forum_id"));            forum.setForumName(forumName);            forum.setForumDesc(rs.getString("forum_desc"));        }    });    return forum;}

2.5 查询单值数据代码

public int getForumNum() {    String sql = " SELECT count(*) FROM t_forum ";    return jdbcTemplate.queryForObject(sql, Integer.class);}

3.使用 JPA 连接数据库

3.1 Domain实体类

@Entity@Table(name = "t_customer")public class Customer {    @Id    @GeneratedValue(strategy = GenerationType.AUTO)    private Long id;    private String firstName;    private String lastName;    protected Customer() {    }    public Customer(String firstName, String lastName) {        this.firstName = firstName;        this.lastName = lastName;    }    @Override    public String toString() {        return String.format("Customer[id=%d, firstName='%s', lastName='%s']", id, firstName, lastName);    }}

3.2 Dao接口（继承JpaRepository）

public interface CustomerRepository extends CrudRepository<Customer, Long> {    List<Customer> findByLastName(String lastName);}

3.3 Application应用

@SpringBootApplicationpublic class CustomerApp {    private static final Logger log = LoggerFactory.getLogger(CustomerApp.class);    public static void main(String[] args) {        SpringApplication.run(CustomerApp.class);    }    @Bean    public CommandLineRunner demo(CustomerRepository repository) {        return (args) -> {            // save a couple of customers            repository.save(new Customer("Jack", "Bauer"));            repository.save(new Customer("Kim", "Bauer"));            repository.save(new Customer("David", "Palmer"));            // fetch all customers            log.info("Customers found with findAll():");            log.info("-------------------------------");            for (Customer customer : repository.findAll()) {                log.info(customer.toString());            }            log.info("");            // fetch an individual customer by ID            Customer customer = repository.findOne(1L);            log.info("Customer found with findOne(1L):");            log.info("--------------------------------");            log.info(customer.toString());            log.info("");            // fetch customers by last name            log.info("Customer found with findByLastName('Bauer'):");            log.info("--------------------------------------------");            for (Customer bauer : repository.findByLastName("Bauer")) {                log.info(bauer.toString());            }            log.info("");        };    }}

源代码：
https://github.com/leifchen/hello-spring
代码包：com.chen.jdbc