05-spring整合jdbc-jdbc模板对象JdbcTemplate

1 演示JdbcTemplate模板对象

1 导包

2 准备数据库

3 书写UserDao

package www.test.dao;

import java.util.List;

import www.test.bean.User;

public interface UserDao {

    //重置表

    public abstract void reset(String tableName);

    //增

    public abstract void save(User u);

    //删

    public abstract void delete(Integer id);

    //改

    public abstract void update(User u);

    //查

    public abstract User getById(Integer id);

    //查

    public abstract Integer getTotalCount();

    //查

    public abstract List<User> getAll();

}

4 书写UserDaoImpl

package www.test.dao;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import www.test.bean.User;

//使用JDBC模板实现增删改查

public class UserDaoImpl implements UserDao {

    private  JdbcTemplate jt;

    @Override

    //增加用户

    public void save(User u) {

        String sql ="insert into t_user values (?,?)";

        jt.update(sql, null,u.getName());

    }

    @Override

    //删除用户

    public void delete(Integer id) {

        String sql ="delete from t_user where id = ?";

        jt.update(sql, id);

    }

    @Override

    //修改用户

    public void update(User u) {

        String sql ="UPDATE t_user SET NAME=? WHERE id=?";

        jt.update(sql, u.getName(),u.getId());

    }

    @Override

    //根据id查找用户

    public User getById(Integer id) {

        String sql ="select * from t_user where id=?";

        //queryForObject(sql, User.class)

        //第二个参数接口

        //第三个是动态参数

        User user = jt.queryForObject(sql, new RowMapper<User>(){

            @Override

            public User mapRow(ResultSet rs, int arg1) throws SQLException {

                User u = new User();

                u.setId(rs.getInt("id"));

                u.setName(rs.getString("name"));

                return u;

            }

        }, id);

        return user;

    }

    @Override

    //获得用户的数量

    public Integer getTotalCount() {

        String sql = "select count(*) from t_user";

        //queryForObject(sql, requiredType)

        //requiredType需要的类型

        Integer totalCount = jt.queryForObject(sql, Integer.class);

        return totalCount;

    }

    @Override

    //获取用户列表

    public List<User> getAll() {

        String sql ="select * from t_user";

        //jt.query(sql, rowMapper, args)

        //rowMapper接口  args可变参数

        List<User> userList = jt.query(sql, new RowMapper<User>(){

            @Override

            public User mapRow(ResultSet rs, int arg1) throws SQLException {

                User u = new User();

                u.setId(rs.getInt("id"));

                u.setName(rs.getString("name"));

                return u;

            }

        });

        return userList;

    }

    public JdbcTemplate getJt() {

        return jt;

    }

    public void setJt(JdbcTemplate jt) {

        this.jt = jt;

    }

    @Override

    //重置表 使用truncate

    public void reset(String tableName) {

        String sql="truncate table "+tableName+"";

        jt.update(sql);

    }

}

5 配置applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>

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

    <!-- 1.将连接池放入spring容器 -->

    <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">

        <property name="driverClass" value="com.mysql.jdbc.Drive"></property>

        <property name="jdbcUrl" value="jdbc:mysql:///spring"></property>

        <property name="user" value="root"></property>

        <property name="password" value="root"></property>

        <property name="initialPoolSize" value="5"></property>

        <property name="maxPoolSize" value="20"></property>

    </bean>

    <!-- 2.将JDBCTemplate放入spring容器 -->

    <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">

        <property name="dataSource" ref="dataSource"></property>

    </bean>

    <!-- 3.将UserDao放入spring容器 -->

    <bean name="userDao" class="www.test.dao.UserDaoImpl">

        <property name="jt" ref="jdbcTemplate"></property>

    </bean>

</beans>

6 测试

package www.test.dao;

import java.util.List;

import javax.annotation.Resource;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.test.context.ContextConfiguration;

import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import www.test.bean.User;

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration("classpath:applicationContext.xml")

public class Demo {

    @Resource(name="userDao")

    private UserDao ud;

    @Test

    //测试删除表使用truncate uid会重置

    /*

     * 注意： delete 删除， uid 不会重置！ 而使用 truncate 操作， uid 会重置。

     * 因为它删除了表结构， 然后再创建一张一模一样的表， 所以再次插入数据的数据的时候从 1

     * 开始。

     */

    public void fun1(){

        ud.reset("t_user");

    }

    @Test

    //测试添加用户

    public void fun2(){

        User u = new User();

        u.setName("旺财");

        ud.save(u);

    }

    @Test

    //测试删除用户

    public void fun3(){

        ud.delete(1);

    }

    @Test

    //测试修改用户

    public void fun4(){

        User user = new User();

        user.setId(1);

        user.setName("小强");

        ud.update(user);

    }

    @Test

    //根据id查找用户

    public void fun5(){

        User u = ud.getById(1);

        System.out.println(u);

    }

    @Test

    //测试获取用户数量

    public void fun6(){

        Integer totalCount = ud.getTotalCount();

        System.out.println(totalCount);

    }

    @Test

    //测试获取用户列表

    public void fun7(){

        List<User> all = ud.getAll();

        System.out.println(all);

    }

}

2 spring整合jdbc扩展-JdbcDaoSupport

1 UserDaoImpl代码修改

1 继承JdbcDaoSupport

2 去掉private JdbcTemplate jt属性

3 jt换成super.getJdbcTemplate()

package www.test.dao;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

import www.test.bean.User;

//使用JDBC模板实现增删改查

public class UserDaoImpl extends JdbcDaoSupport implements UserDao {

    @Override

    //增加用户

    public void save(User u) {

        String sql ="insert into t_user values (?,?)";

        super.getJdbcTemplate().update(sql, null,u.getName());

    }

    @Override

    //删除用户

    public void delete(Integer id) {

        String sql ="delete from t_user where id = ?";

        super.getJdbcTemplate().update(sql, id);

    }

    @Override

    //修改用户

    public void update(User u) {

        String sql ="UPDATE t_user SET NAME=? WHERE id=?";

        super.getJdbcTemplate().update(sql, u.getName(),u.getId());

    }

    @Override

    //根据id查找用户

    public User getById(Integer id) {

        String sql ="select * from t_user where id=?";

        //queryForObject(sql, User.class)

        //第二个参数接口

        //第三个是动态参数

        User user = super.getJdbcTemplate().queryForObject(sql, new RowMapper<User>(){

            @Override

            public User mapRow(ResultSet rs, int arg1) throws SQLException {

                User u = new User();

                u.setId(rs.getInt("id"));

                u.setName(rs.getString("name"));

                return u;

            }

        }, id);

        return user;

    }

    @Override

    //获得用户的数量

    public Integer getTotalCount() {

        String sql = "select count(*) from t_user";

        //queryForObject(sql, requiredType)

        //requiredType需要的类型

        Integer totalCount = super.getJdbcTemplate().queryForObject(sql, Integer.class);

        return totalCount;

    }

    @Override

    //获取用户列表

    public List<User> getAll() {

        String sql ="select * from t_user";

        //super.getJdbcTemplate().query(sql, rowMapper, args)

        //rowMapper接口  args可变参数

        List<User> userList = super.getJdbcTemplate().query(sql, new RowMapper<User>(){

            @Override

            public User mapRow(ResultSet rs, int arg1) throws SQLException {

                User u = new User();

                u.setId(rs.getInt("id"));

                u.setName(rs.getString("name"));

                return u;

            }

        });

        return userList;

    }

    @Override

    //重置表 使用truncate

    public void reset(String tableName) {

        String sql="truncate table "+tableName+"";

        super.getJdbcTemplate().update(sql);

    }

}

2 applicationContext.xml配置修改

1 不在需要准备JdbcTemplate()

2 userDao中需要注入dataSource

<?xml version="1.0" encoding="UTF-8"?>

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

    <!-- 1.将连接池放入spring容器 -->

    <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">

        <property name="driverClass" value="com.mysql.jdbc.Drive"></property>

        <property name="jdbcUrl" value="jdbc:mysql:///spring"></property>

        <property name="user" value="root"></property>

        <property name="password" value="root"></property>

        <property name="initialPoolSize" value="5"></property>

        <property name="maxPoolSize" value="20"></property>

    </bean>

    <!-- 2.将JDBCTemplate放入spring容器 -->

    <!-- <bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">

        <property name="dataSource" ref="dataSource"></property>

    </bean> -->

    <!-- 3.将UserDao放入spring容器 -->

    <bean name="userDao" class="www.test.dao.UserDaoImpl">

        <!-- <property name="jt" ref="jdbcTemplate"></property> -->

        <property name="dataSource" ref="dataSource"></property>

    </bean>

</beans>

3 spring整合jdbc扩展-读取外部的properties配置