JdbcTemplate CRUD

 

执行insert update delete操作

 

 

 

 执行select操作

1.User实体类及get/set方法

测试类

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class jdbcTemplateTest1 {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    // 测试修改
    @Test
    public void test1() {
        jdbcTemplate.update("update t_user set name=? where id=?", "tom", 1);
    }
    // 测试添加
    @Test
    public void test2() {
        jdbcTemplate.update("insert into t_user values(null,?,?,?)", "萌萌", 20,
                "男");
    }

    // 测试删除
    @Test
    public void test3() {
        jdbcTemplate.update("delete from t_user where id=?", 4);
    }

    // 测试修改简单的数据类型
    @Test
    public void test4() {
        String query = jdbcTemplate.queryForObject(
                "select name from t_user where id=?", String.class, 1);
        System.out.println(query);
    }

    // 测试修改简单的数据类型
    @Test
    public void test5() {
        Integer integer = jdbcTemplate.queryForObject(
                "select count(*) from t_user ", Integer.class);
        System.out.println(integer);
    }
    // 测试返回一个domain
    @Test
    public void test6() {
        User user = jdbcTemplate.queryForObject("select * from t_user where id = ?",new RowMapper<User>() {
                    @Override
                    public User mapRow(ResultSet rs, int num)throws SQLException {
                        //结果节封装到User对象
                        User user = new User();
                        user.setId(rs.getInt("id"));
                        user.setAge(rs.getInt(3));
                        user.setName(rs.getString(2));
                        user.setSex(rs.getString("sex"));
                        return user;
                    }
                }, 1);
        System.out.println(user);
    }
    // 测试返回一个list<User>---使用query
        @Test
        public void test7() {
            List<User> user = jdbcTemplate.query("select * from t_user ",new RowMapper<User>() {
                        @Override
                        public User mapRow(ResultSet rs, int num)throws SQLException {
                            //rs表示获取到的结果集,num表示结果集中数据的第几列
                            //结果节封装到User对象
                            User user = new User();
                            user.setId(rs.getInt(1));
                            user.setAge(rs.getInt(3));
                            user.setName(rs.getString(2));
                            user.setSex(rs.getString("sex"));
                            return user;
                        }
                    });
            System.out.println(user);
        }
        //使用RowMapper的实现类完成结果集bean封装--实体必须提供一个无参构造,类中的bean属性名称与表中的列要对应
        @Test
        public void test8(){
            List<User> user = jdbcTemplate.query("select*from t_user", new BeanPropertyRowMapper<User>(User.class));
            System.out.println(user);
        }
}

3.配置文件

<?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">
     <!-- 引入外部的properties文件 -->
     <context:property-placeholder location="classpath:db.properties"/>
     <!-- Spring内置的连接池  -->
    <!-- <bean id="driverManagerDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql:///springtest" />
        <property name="username" value="root"/>
        <property name="password" value="123"></property>
    </bean>  -->
    <!-- 创建c3p0连接池 -->
    <bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driverClass}" />
        <property name="jdbcUrl" value="${jdbc.url}" />
        <property name="user" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>
    <!-- ref声明c3p0的数据源,交由Spring管理 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="c3p0DataSource"></property>
    </bean> 
</beans>

 db.properties

jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///springtest
jdbc.username=root
jdbc.password=