JavaEE——Spring4--（8）Spring 对JDBC的支持

连接数据库 首先导入jar包

然后创建properties.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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="user" value="root"></property>
        <property name="password" value="1234"></property>
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/db_person"></property>
    </bean>
</beans>

　　这样就可以使用了

public class Main {

    public static void main(String[] args) throws SQLException {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("bean-properties.xml");

        DataSource dataSource = (DataSource) applicationContext.getBean("dataSource");

        System.out.println(dataSource.getConnection());
    }
}

　　但是一般不这样做，因为后续维护起来再找比较麻烦，所以将properties属性放入一个专有的db.properties文件中，再引用

user=root
password=1234
driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/db_person

　　

可通过 <context:property-placeholder> 元素简化:
　　<beans> 中添加 context Schema 定义
　　在配置文件中加入如下配置:

<?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: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 http://www.springframework.org/schema/context/spring-context.xsd">


    <!--导入资源文件-->
    <context:property-placeholder location="classpath:db.properties"/>

    <!--配置C3P0数据源-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="user" value="${jdbc.user}"></property>
        <property name="password" value="${jdbc.password}"></property>
        <property name="driverClass" value="${jdbc.driverClass}"></property>
        <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>

        <property name="initialPoolSize" value="${jdbc.initPoolSize}"></property>
        <property name="maxPoolSize" value="${jdbc.maxPoolSize}"></property>
    </bean>

    <!--配置Spring的JDBCTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

</beans>

　　连接数据库进行测试更新操作  INSERT  UPDATE  DELETE

package jdbc;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.SQLException;

public class JDBCTest {

    private ApplicationContext applicationContext = null;
    private JdbcTemplate jdbcTemplate = null;

    {
        applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        jdbcTemplate = (JdbcTemplate)applicationContext.getBean("jdbcTemplate");
    }



    @Test
    public void testUpdate(){
        String sql = "UPDATE student SET name = ? WHERE id = ?";

        jdbcTemplate.update(sql, "niu", 3);
    }

    @Test
    public void test() throws SQLException {
        DataSource dataSource = (DataSource) applicationContext.getBean("dataSource");

        System.out.println(dataSource.getConnection());
    }

}

　　批量更新操作  INSERT  UPDATE  DELETE

/**
     * 批量更新
     *
     */
    @Test
    public void testBatchUpdate(){
        String sql = "INSERT INTO student(name, sex, age) VALUES(?, ?, ?)";

        List<Object[]> args = new ArrayList<>();
        args.add(new Object[]{"AA", "girl", 23});
        args.add(new Object[]{"BB", "boy", 21});
        args.add(new Object[]{"CC", "girl", 17});

        jdbcTemplate.batchUpdate(sql, args);
    }

　　

从数据库获取一条记录，实际得到对应的一个对象

@Test
    public void testQueryForObject(){
        String sql = "SELECT name, sex, age FROM student WHERE id = ?";
        RowMapper<Student> rowMapper = new BeanPropertyRowMapper<>(Student.class);
        Student student = jdbcTemplate.queryForObject(sql, rowMapper, 1);
        System.out.println(student);
    }

　　获取单个列的值或做统计查询

@Test
    public void testQueryForObject2(){
        String sql = "SELECT COUNT(id) FROM student WHERE sex = ?";
        long count = jdbcTemplate.queryForObject(sql, Long.class, "girl");
        System.out.println(count);
    }

　　实际开发时使用

在 JDBC 模板中使用具名参数
具名参数只在 NamedParameterJdbcTemplate 中得到支持