JdbcTemplate总结

jdbc.userName=root
jdbc.password=hsp//自己改密码
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/spring

<?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">
<!--    加载引入 jdbc.properties，引入驱动信息-->
    <context:property-placeholder location="classpath:jdbc.properties"/>
<!--    配置数据源，数据源来自 `"com.mchange.v2.c3p0.ComboPooledDataSource`-->
    <bean class="com.mchange.v2.c3p0.ComboPooledDataSource" id="dataSource">
        <property name="user" value="${jdbc.userName}"></property>
        <property name="password" value="${jdbc.password}"></property>
        <property name="driverClass" value="${jdbc.driverClass}"></property>
        <property name="jdbcUrl" value="${jdbc.url}"></property>
    </bean>
    <bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>
</beans>

public class JdbcTemplateTest {
    //查询 id=100 的 monster 并封装到 Monster 实体对象
    @Test
    public void selectDataByJdbcTemplate() {
        ApplicationContext ioc =
                new ClassPathXmlApplicationContext("JdbcTemplate_ioc.xml");
        JdbcTemplate bean = ioc.getBean(JdbcTemplate.class);
        String sql="select * from monster where id =100";
        RowMapper<Monster> rowMapper = new BeanPropertyRowMapper<Monster>(Monster.class);
        Monster monster = bean.queryForObject(sql, rowMapper);
        System.out.println("monster="+monster);
    }
//
    /**
     * 查询多条记录
     * 查询 id>=200 的 monster 并封装到 Monster 实体对象
     */
    @Test
    public void selectMulDataByJdbcTemplate(){
        ApplicationContext ioc =
                new ClassPathXmlApplicationContext("JdbcTemplate_ioc.xml");
        JdbcTemplate bean = ioc.getBean(JdbcTemplate.class);
        String sql="select * from monster where id >=?";
        RowMapper<Monster> rowMapper = new BeanPropertyRowMapper<Monster>(Monster.class);
        List<Monster> monsterList = bean.query(sql, rowMapper, 100);
        for (Monster monster :monsterList) {
            System.out.println(monster);
        }
    }
/**
 * 查询返回结果只有一行一列的值
 * 12. 查询返回结果只有一行一列的值，比如查询 id=100 的怪物名
 */
    @Test
    public void selectScalarByJdbcTemplate(){
        ApplicationContext ioc =
                new ClassPathXmlApplicationContext("JdbcTemplate_ioc.xml");
        JdbcTemplate bean = ioc.getBean(JdbcTemplate.class);
        String sql="select name from monster where id = 100";
        String name = bean.queryForObject(sql, String.class);
        System.out.println("name="+name);
    }
    //测试连接是否成功
    @Test
    public void testDataSource() throws SQLException {
        ApplicationContext ioc =
                new ClassPathXmlApplicationContext("JdbcTemplate_ioc.xml");
        DataSource dataSource = ioc.getBean(DataSource.class);
        Connection conn = dataSource.getConnection();
        System.out.println("conn="+conn);
        conn.close();
    }
    //添加数据
    @Test
    public void addDataSource() throws SQLException {
        ApplicationContext ioc =
                new ClassPathXmlApplicationContext("JdbcTemplate_ioc.xml");
        JdbcTemplate bean = ioc.getBean(JdbcTemplate.class);
        String sql="INSERT INTO monster VALUE(?,?,?)";
        int affected=bean.update(sql,700,"红孩儿","枪法厉害2");
        System.out.println("成功行数："+affected);
    }
    //修改数据
    @Test
    public void updateDataSource() throws SQLException {
        ApplicationContext ioc =
                new ClassPathXmlApplicationContext("JdbcTemplate_ioc.xml");
        JdbcTemplate bean = ioc.getBean(JdbcTemplate.class);
        String sql="update monster set skill=? where id=?";
        int affect = bean.update(sql, "美女计", 300);
        System.out.println("affected="+affect);
        System.out.println("update 成功!");
    }
    //批量添加数据
    @Test
    public void updateBatchDataSource() throws SQLException {
        ApplicationContext ioc =
                new ClassPathXmlApplicationContext("JdbcTemplate_ioc.xml");
        JdbcTemplate bean = ioc.getBean(JdbcTemplate.class);
        String sql="insert into monster values(?,?,?);";
        List<Object[]> param_list = new ArrayList<>();
        param_list.add(new Object[]{500,"白骨精","吃人"});
        param_list.add(new Object[]{600,"青蛇精","吃小孩"});
        bean.batchUpdate(sql,param_list);
        System.out.println("批量添加成功！");
    }
}