从零开始学Spring(十二)——JDBC模板的CRUD操作

• 配置applicationContext.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       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
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd">
    <!--开启IOC注解-->
    <context:annotation-config/>
    <!--开启AOP注解-->
    <aop:aspectj-autoproxy/>
    <!--配置文件配置Bean-->
       <!--使用C3P0连接池-->
    <!--使用属性文件配置数据库连接信息-->
    <!--bean方式引入属性文件-->
    <!--<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">-->
        <!--<property name="location" value="property"/>-->
    <!--</bean>-->
    <!--context方式引入属性文件 location中的classpath可以省略-->
    <!--<context:property-placeholder location="property"/>-->
    <context:property-placeholder location="classpath:property"/>
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <!--注入属性，引用属性名称的key值-->
        <property name="driverClass" value="${driverClass}"/>
        <property name="jdbcUrl" value="${jdbcUrl}"/>
        <property name="user" value="${user}"/>
        <property name="password" value="${password}"/>
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

• 配置属性文件property.property

driverClass=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql:///Spring
user=root
password=123456

• ORM映射类User

public class User {
    private Integer id;
    private Integer age;
    private String name;

    public void setId(Integer id) {
        this.id = id;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getId() {

        return id;
    }
    public Integer getAge() {
        return age;
    }
    public String getName() {
        return name;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", age=" + age +
                ", name='" + name + '\'' +
                '}';
    }
}

• 测试类

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class testJDBC {
    @Resource
    private JdbcTemplate jdbcTemplate;

    // 插入操作
    @Test
    public void testInsert() {
        //创建jdbc模板
        jdbcTemplate.update("insert into user values(null,?,?)", "张无忌", 26);
        System.out.println("插入成功");
    }

    // 删除操作
    @Test
    public void testDelete() {
        //创建jdbc模板
        jdbcTemplate.update("delete from user where id=?", 1);
        System.out.println("删除成功");
    }

    //更新操作
    @Test
    public void testUpdate() {
        //创建jdbc模板
        jdbcTemplate.update("update user set name =?,age=? where id=?", "白素贞", 19, 6);
        System.out.println("更新数据成功");
    }

    //查询某一个字段queryForObject()
    @Test
    public void testQuery() {
        //创建jdbc模板
        //String.class查询结果类型
        String name = jdbcTemplate.queryForObject("select name from user where id = ?", String.class, 6);
        System.out.println("查询成功，查询结果为" + name);
        //Long.class查询结果类型
        Long count = jdbcTemplate.queryForObject("select count(*) from user", Long.class);
        System.out.println("查询成功，库中共有" + count + "条记录");
    }

    //查询返回对象的集合query()
    @Test
    public void testQueryList() {
        //创建jdbc模板

//查询一条记录

//List<User> list = jdbcTemplate.query("select * from user where id =?", new myQueryList(),5);

//查询所有记录
        List<User> list = jdbcTemplate.query("select * from user", new myQueryList());
        for (User user:list) {
            System.out.println(user);
        }
        System.out.println("查询成功");
    }
}

//用以保存查询结果
class myQueryList implements RowMapper<User>{
    @Override
    public User mapRow(ResultSet resultSet, int i) throws SQLException {
        User user=new User();
        user.setId(resultSet.getInt("id"));
        user.setAge(resultSet.getInt("age"));
        user.setName(resultSet.getString("name"));
        return user;
    }
}

• 运行结果

测试前数据库记录

执行插入操作

 

执行删除操作

执行更新操作

执行查询操作，查询某一个字段

执行查询操作，查询返回对象的集合