17.spring JDBC模板

1.导入坐标 

  <properties>
    <spring.version>5.0.5.RELEASE</spring.version>
    <!--mybatis 版本号-->
    <mybatis.version>3.2.4</mybatis.version>
    <!--log4j 日志文件管理包文件-->
    <slf4j.version>1.6.6</slf4j.version>
    <log4j.version>1.2.9</log4j.version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.32</version>
    </dependency>

    <dependency>
      <groupId>c3p0</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.1.2</version>
    </dependency>

    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.10</version>
    </dependency>

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

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

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>

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

 2.创建数据库表和实体类

public class Account {

  private String name;
  private String money;


  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }


  public String getMoney() {
    return money;
  }

  public void setMoney(String money) {
    this.money = money;
  }

  @Override
  public String toString() {
    return "Account{" +
            "name='" + name + '\'' +
            ", money='" + money + '\'' +
            '}';
  }
}

 3.测试

public class JdbcTemplateTest
{
    @Test
    public void test1() throws Exception//测试jdbc模板开发步骤
    {
        //创建一个数据源对象
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test2");
        dataSource.setUser("root");
        dataSource.setPassword("18081736467xr");

        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        //设置数据源对象，知道数据库在哪
        jdbcTemplate.setDataSource(dataSource);
        //执行操作
        int row = jdbcTemplate.update("insert into account values(?,?)","tom",5000);
        System.out.println(row);
    }
}

可以解耦合，用spring容器产生jabcStemplate对象

1.将dataSource中的四个属性抽取出来

新建jdbc.properties文件

jdbc.driver = com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test2
jdbc.username=root
jdbc.password=18081736467xr

在applicationContext.xml引入jdbc.properties

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">


<context:property-placeholder location="classpath:jdbc.properties"></context:property-placeholder>

applicationContext.xml创建dataSource和jdbcTemplate对象

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.url}"></property>
        <property name="user" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    @Test
    public void test2() throws Exception//测试jdbc模板开发步骤
    {
        ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
        JdbcTemplate jdbcTemplate = (JdbcTemplate) app.getBean("jdbcTemplate");
        int row = jdbcTemplate.update("insert into account values(?,?)","suqi",7000);
        System.out.println(row);
    }

CRUD

public class JdbcTemplateCRUDTest
{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void insert() throws Exception
    {
        int row = jdbcTemplate.update("insert into account values(?,?)","hah",7000);
        System.out.println(row);
    }

    @Test
    public void update() throws Exception
    {
        int row = jdbcTemplate.update("update account set money=? where name=?",999,"tom");
        System.out.println(row);
    }

    @Test
    public void detele() throws Exception
    {
        int row = jdbcTemplate.update("delete from account where name=?","tom");
        System.out.println(row);
    }

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

    @Test
    public void query() throws Exception
    {
        //第二个参数为需要返回的类型,第三个参数为占位符中的值
        Account account = (Account) jdbcTemplate.queryForObject("select * from account where name=?", new BeanPropertyRowMapper(Account.class),"suqi");
        System.out.println(account);
    }

    @Test
    public void queryCount() throws Exception
    {
        Long count = jdbcTemplate.queryForObject("select count(*) from account",Long.class);//第二个参数为需要返回的类型
        System.out.println(count);
    }
}

总结: