Spring in action 4 剑指Spring - （八）通过Spring和JDBC征服数据库

Spring和JDBC操作数据库

使用JNDI配置数据源:

1. 在Tomcat服务器的conf文件夹下的context.xml 配置数据源

<Context>
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <Resource auth="Container" driverClassName="com.mysql.jdbc.Driver" 
    maxActive="20" maxIdle="5" 
    name="jdbc/spring_test" password="root" username="root"
    type="javax.sql.DataSource" 
    url="jdbc:mysql://119.23.104.xxx:3306/SpringTest?	rewriteBatchedStatements=true&amp;useUnicode=true&amp;characterEncoding=UTF-8" />
</Context>

2. 使用java配置类配置

   @Configuration
   public class DataSourceConfig {
       @Bean
       public JndiObjectFactoryBean dataSource(){
           JndiObjectFactoryBean bean = new JndiObjectFactoryBean();
           bean.setJndiName("jdbc/spring_test");
           bean.setResourceRef(true);
           bean.setProxyInterface(DataSource.class);
           return bean;
       }
       @Bean
       public JdbcTemplate jdbcTemplate(DataSource dataSource){
           return new JdbcTemplate(dataSource);
       }
   }
   

3. 使用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:jee="http://www.springframework.org/schema/jee"
          xsi:schemaLocation="http://www.springframework.org/schema/beans
          http://www.springframework.org/schema/beans/spring-beans.xsd
          http://www.springframework.org/schema/jee
          http://www.springframework.org/schema/jee/spring-jee-4.0.xsd">
       <jee:jndi-lookup id="dataSource" jndi-name="jdbc/spring_test" resource-ref="true"/>
   </beans>
   

4. pom

   <dependencies>
       <dependency>
         <groupId>junit</groupId>
         <artifactId>junit</artifactId>
         <version>4.12</version>
         <scope>test</scope>
       </dependency>
   
       <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-context</artifactId>
         <version>5.1.8.RELEASE</version>
       </dependency>
       <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-test</artifactId>
         <version>5.1.8.RELEASE</version>
         <scope>test</scope>
       </dependency>
       <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-aop</artifactId>
         <version>5.1.8.RELEASE</version>
       </dependency>
       <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-aspects</artifactId>
         <version>5.1.8.RELEASE</version>
       </dependency>
       <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-webmvc</artifactId>
         <version>5.1.8.RELEASE</version>
       </dependency>
       <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-web</artifactId>
         <version>5.1.8.RELEASE</version>
       </dependency>
       <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-context-support</artifactId>
         <version>5.1.8.RELEASE</version>
       </dependency>
       <dependency>
         <groupId>javax.servlet</groupId>
         <artifactId>javax.servlet-api</artifactId>
         <version>4.0.1</version>
         <scope>provided</scope>
       </dependency>
       <dependency>
         <groupId>taglibs</groupId>
         <artifactId>standard</artifactId>
         <version>1.1.2</version>
       </dependency>
       <dependency>
         <groupId>jstl</groupId>
         <artifactId>jstl</artifactId>
         <version>1.2</version>
       </dependency>
       <dependency>
         <groupId>org.thymeleaf</groupId>
         <artifactId>thymeleaf-spring5</artifactId>
         <version>3.0.11.RELEASE</version>
       </dependency>
       <dependency>
         <groupId>org.thymeleaf</groupId>
         <artifactId>thymeleaf</artifactId>
         <version>3.0.11.RELEASE</version>
       </dependency>
       
       <!--数据源-->
       <dependency>
         <groupId>com.alibaba</groupId>
         <artifactId>druid</artifactId>
         <version>1.1.20</version>
       </dependency>
       <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-jdbc</artifactId>
         <version>5.2.0.RELEASE</version>
       </dependency>
       <dependency>
         <groupId>com.mchange</groupId>
         <artifactId>c3p0</artifactId>
         <version>0.9.5.2</version>
       </dependency>
       <dependency>
         <groupId>com.mchange</groupId>
         <artifactId>mchange-commons-java</artifactId>
         <version>0.2.11</version>
       </dependency>
       <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>5.1.48</version>
       </dependency>
       <dependency>
         <groupId>org.apache.commons</groupId>
         <artifactId>commons-dbcp2</artifactId>
         <version>2.7.0</version>
       </dependency>
   
       <!--jackson依赖-->
       <dependency>
         <groupId>com.fasterxml.jackson.core</groupId>
         <artifactId>jackson-core</artifactId>
         <version>2.9.5</version>
       </dependency>
       <dependency>
         <groupId>com.fasterxml.jackson.core</groupId>
         <artifactId>jackson-databind</artifactId>
         <version>2.9.5</version>
       </dependency>
       <dependency>
         <groupId>com.fasterxml.jackson.core</groupId>
         <artifactId>jackson-annotations</artifactId>
         <version>2.9.5</version>
       </dependency>
       <dependency>
         <groupId>com.fasterxml.jackson.module</groupId>
         <artifactId>jackson-module-jaxb-annotations</artifactId>
         <version>2.9.5</version>
       </dependency>
       <dependency>
         <groupId>com.alibaba</groupId>
         <artifactId>fastjson</artifactId>
         <version>1.2.51</version>
       </dependency>
     </dependencies>
   

5. 测试

   /*java配置方式*/
   @Controller
   @RequestMapping({"/Test8SpittrController","/Test8Spittr","/Test8"})
   public class SpittrController {
   	@Autowired
       private JdbcTemplate jdbcTemplate;
   
       @RequestMapping("/Print5")
       @ResponseBody
       public List<Map<String, Object>> Print5(){
           List<Map<String, Object>> maps = jdbcTemplate.queryForList("SELECT * FROM tb_user");
           return maps;
       }
   }
   /*========================================================================================*/
   /*xml配置方式@ImportResource(value = "classpath:jdni.xml")*/
   @Controller
   @RequestMapping({"/Test8SpittrController","/Test8Spittr","/Test8"})
   @ImportResource(value = "classpath:jdni.xml")
   public class SpittrController {
   	@Autowired
       private JdbcTemplate jdbcTemplate;
   
       @RequestMapping("/Print5")
       @ResponseBody
       public List<Map<String, Object>> Print5(){
           List<Map<String, Object>> maps = jdbcTemplate.queryForList("SELECT * FROM tb_user");
           return maps;
       }
   }
   

Java配置类配置数据源:

@Configuration
public class DataSourceConfig {
    @Bean
    @Primary
    /*配置jndi数据源*/
    public JndiObjectFactoryBean dataSource(){
        JndiObjectFactoryBean bean = new JndiObjectFactoryBean();
        bean.setJndiName("jdbc/spring_test");
        bean.setResourceRef(true);
        bean.setProxyInterface(DataSource.class);
        return bean;
    }

    /*配置C3P0数据源*/
    @Bean
    public DataSource dataSource1() throws PropertyVetoException {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setMaxPoolSize(20);
        dataSource.setMinPoolSize(5);
        dataSource.setUser("root");
        dataSource.setPassword("root");
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://119.23.104.xxx:3306/SpringTest?useUnicode=true&characterEncoding=utf-8");
        return dataSource;
    }


    /*配置Druid数据源*/
    @Bean
    public DataSource dataSource2(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setMaxActive(20);
        dataSource.setMaxWait(5);
        dataSource.setMinIdle(5);
        dataSource.setInitialSize(10);
        dataSource.setUrl("jdbc:mysql://119.23.104.xxx:3306/SpringTest?useUnicode=true&characterEncoding=utf-8");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        return dataSource;
    }


    /*配置DBCP数据源*/
    @Bean
    public DataSource dataSource3(){
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setMaxTotal(20);
        dataSource.setMaxIdle(10);
        dataSource.setInitialSize(5);
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://119.23.104.xxx:3306/SpringTest?useUnicode=true&characterEncoding=utf-8");
        dataSource.setMinIdle(5);
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        return dataSource;
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource){
        return new JdbcTemplate(dataSource);
    }
}