Spring基础 02 | JdbcTemplate

JdbcTemplate

Spring对Jdbc的Api简单封装

• 开发步骤

  1.导入Spring-jdbc、spring-tx坐标
  2.创建数据库表和实例
  3.创建jdbcTemplate对象
  4.执行数据库操作
  

  • 导入坐标

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.3.15</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>5.3.15</version>
    </dependency>
    

  • 实体类

    domain.Account

    public class Account {
        private String name;
        private double money;
        // Getter/Setter
        // toString()
    }
    

  • 操作

    JdbcTemplateTest

    public class JdbcTemplate {
        @Test
        public void test1() {
            // 创建数据源对象
            ComboPooledDataSource dataSource = new ComboPooledDataSource();
            dataSource.setDataSource('com.mysql.jdbc.Driver');
            dataSource.setJdbcClass('jdbc:mysql://localhost:3306/test');
            dataSource.setUser("root");
            dataSource.setPassword("123456");
    
            JdbcTemplate jdbcTemplate = new JdbcTemplate();
            jdbcTemplate.setDataSource(dataSource); // 设置数据源
    
            // 执行数据库操作
            int row = jdbcTemplate.update("insert into account values(?,?)", "mkl", 2000);
            System.out.pringln(row);
        }
    }
    

    • 使用Spring操作对象 配置文件方式

      配置连接Bean

      <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
          <property name="driverClass" value="com.mysql.jdbc.Driver"/>
          <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test"/>
          <property name="user" value="root"/>
          <property name="password" value="123456"/>
      </bean>
      <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
          <property name="dataSource" ref="dataSource"/>
      </bean>
      

      操作Template对象

      public class JdbcTemplateTest {
          @Test
          public void test2() throws Exception {
              ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
              JdbcTemplate jdbcTemplate = app.getBean(JdbcTemplate.class);
              int row = jdbcTemplate.update("insert into account values(?,?)", "mkl", 1000);
              System.out.println(row);
          }
      }
      

      抽取配置到properties ： 略

    • JdbcTemplate增删改查

      • 改/删

        @RunWith(SpringJUnit4ClassRunner.class)
        @ContextConfiguration("classpath:applicationContext.xml")
        public class JdbcTemplateCRUDTest {
            @Autowired
            private JdbcTemplate jdbcTemplate;
        
            @Test
            public void testUpdate() {
                jdbcTemplate.update("update account set money=? where name=?", 1000, "mkl");
            }
        
            @Test
            public void testDelete() {
                jdbcTemplate.update("delete from account where name=?", "mkl");
            }
        }
        

      • 查

        查询多个对象

        @Test
        public void testQueryAll() {
            List<Account> accountList = jdbcTemplate.query("select * from account", new BeanPropertyRowMapper<Account>(Account.class));
            System.out.println(accountList);
        }
        

        查看单个对象

        @Test
        public void testQueryAll() {
            Account account = jdbcTemplate.queryForObject(
                "select * from account where name=?", 
                new BeanPropertyRowMapper<Account>(Account.class),
                "mkl"
            );
            System.out.println(account);
        }
        

        聚合查总数

        @Test
        public void testQueryAll() {
            Long count = jdbcTemplate.queryForObject(
                "select count(*) from account", 
                Long.class
            );
            System.out.println(count);
        }