Beginning Spring学习笔记——第4章（一）Spring JDBC连接的配置

普通JDBC存在的问题

---

• JDBC没有对不同数据库供应商提供的SQL符号进行完全抽象
• 数据访问样板代码散落在DAO层各个角落，造成维护困难

Spring配置Datasource来获取JDBC连接

---

为方便测试，此处我们使用H2嵌入式数据库，MAVEN依赖如下

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.lonelyquantum.springbeginning.wileybookch4</groupId>
  <artifactId>JDBCTest</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>JDBCTest</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <spring.version>4.3.10.RELEASE</spring.version>
    <junit.version>4.12</junit.version>
    <h2.version>1.4.196</h2.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>${junit.version}</version>
      <scope>test</scope>
    </dependency>

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>${spring.version}</version>
    </dependency>

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${spring.version}</version>
    </dependency>

    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>${h2.version}</version>
    </dependency>
  </dependencies>
</project>

首先在Maven Dependencies库类找到org.h2.tools.Console类作为Java应用程序运行，使用用户名sa和空密码登陆，运行SQL代码

create table account(id bigint identity primary key, owner_name varchar(255), balance double, accesss_time timestamp, locked boolean)

创建一个这样的表。
然后在包类创建Spring Bean配置类

@Configuration
public class Ch4Configuration {
    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl("jdbc:h2:tcp://localhost/~/test");
        dataSource.setUsername("sa");
        dataSource.setPassword("");
        return dataSource;
    }
}

在其中定义dataSource Bean。
再在同一个包内创建Main函数测试创建是否成功

public class Main {
    public static void main(String[] args) throws SQLException {
        AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Ch4Configuration.class);
        DataSource dataSource = applicationContext.getBean("dataSource", DataSource.class);

        Connection connection = dataSource.getConnection();
        System.out.println(connection.isClosed());
        connection.close();
        System.out.println(connection.isClosed());
    }
}

该函数通过dataSource Bean的掉dataSource实例后用它创建连接，检查连接是否被创建，然后关闭连接检查是否关闭，得到控制台输出结果：

成功地创建和关闭了连接，说明配置正确。

由于DriverManagerDataSource没有连接池功能，每次使用该类都会尝试打开新的物理JDBC连接，花费昂贵，因此不要在生产环境使用该类

Spring还提供了EmbeddedDatabase接口来运行嵌入式DB的SQL脚本，这样的脚本既可以用XML文件配置

<?xml version="1.0" encoding="UTF-8"?>
 <beans xmlns="http://www.springframework.org/schema/beans"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/jdbc 
    http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd">

  <jdbc:embedded-database id="dataSource" type="H2">
       <jdbc:script location="classpath:schema.sql"/>
       <jdbc:script location="classpath:data.sql"/>
   </jdbc:embedded-database>
</beans>

也可以用在Java文件中配置

public class EmbeddedDataSourceTest {
    private DataSource dataSource;
    @Before
    public void setUp() {
        dataSource = new EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.H2)
                .addScript("classpath:schema.sql")
                .addScript("classpath:data.sql").build();
    }
    @Test
    public void testDataAccessLogic() throws SQLException {
        Connection connection = dataSource.getConnection();
        Assert.assertFalse(connection.isClosed());
        connection.close();
        Assert.assertTrue(connection.isClosed());
    }
    @After
   public void tearDown() {
        ((EmbeddedDatabase)dataSource).shutdown();
   }
}

配置和使用Spring JDBCTemplate

---

接上，这次对应数据库的Account表先创建一个Java的Account类，

public class Account {
    private long id;
    private String ownerName;
    private double balance;
    private Date accessTime;
    private boolean locked;
    public long getId() {
        return id;
    }
    public void setId(long id) {
        this.id = id;
    }
    public String getOwnerName() {
        return ownerName;
    }
    public void setOwnerName(String ownerName) {
        this.ownerName = ownerName;
    }
    public double getBalance() {
        return balance;
    }
    public void setBalance(double balance) {
        this.balance = balance;
    }
    public Date getAccessTime() {
        return accessTime;
    }
    public void setAccessTime(Date accessTime) {
        this.accessTime = accessTime;
    }
    public boolean isLocked() {
        return locked;
    }
    public void setLocked(boolean locked) {
        this.locked = locked;
    }
}

然后创建AccountDao接口定义对Account类的持久化操作

public interface AccountDao {
    public void insert(Account account);
    public void update(Account account);
    public void update(List<Account> accounts);
    public void delete(long accountId);
    public Account find(long accountId);
    public List<Account> find(List<Long> accountIds);
    public List<Account> find(String ownerName);
    public List<Account> find(boolean locked);
}

再定义一个AccountDao接口的实现类

public class AccountDaoJdbcImpl implements AccountDao {

    private JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    //method implementations...
}

暂且什么都不实现，直接配置jdbcTemplate和AccountDao Bean，使用之前定义的dataSource Bean

@Configuration
public class Ch4Configuration {
    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl("jdbc:h2:tcp://localhost/~/test");
        dataSource.setUsername("sa");
        dataSource.setPassword("");
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate() {
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(dataSource());
        return jdbcTemplate;
    }

    @Bean
    public AccountDao accountDao() {
        AccountDaoJdbcImpl accountDao = new AccountDaoJdbcImpl();
        accountDao.setJdbcTemplate(jdbcTemplate());
        return accountDao;
    }

}

在主程序中可以获取Bean确认成功即可

public class Main {
    public static void main(String[] args) throws SQLException {
        AnnotationConfigApplicationContext applicationContext = new AnnotationConfigApplicationContext(Ch4Configuration.class);

        AccountDao accountDao = applicationContext.getBean(AccountDao.class);

    }
}