（案例）Spring JdbcTemplate单表增删改查（xml配置方式，配置类方式）

1.xml配置方式

1.1引入依赖

<properties>
    <spring.version>5.3.12</spring.version>
  </properties>
<dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
    <!--    引入spring框架-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
    </dependency>
    <dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.5.4</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.21</version>
    </dependency>
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-dbcp2</artifactId>
      <version>2.8.0</version></dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>cn.hutool</groupId>
      <artifactId>hutool-all</artifactId>
      <version>5.7.14</version>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.22</version>
    </dependency>

  </dependencies>

1.2 添加数据源配置文件druid.properties

driverName= com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db_yun?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&useSSL=false
name=root
password=123456
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间3秒
maxWait=3000

1.3 添加spring配置文件applicationContext.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"
       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 https://www.springframework.org/schema/context/spring-context.xsd">

    <!--添加数据配置文件-->
    <context:property-placeholder location="druid.properties"></context:property-placeholder>
    <!--    配置数据源druid-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${driverName}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${name}"/>
        <property name="password" value="${password}"/>
    </bean>
    <!--    配置springJdbcTemplate-->
    <bean id="jdbcTmplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <!--    配置dao层-->
    <bean id="userDao" class="com.yun.dao.impl.UserDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTmplate"></property>
    </bean>

    <!--    配置entity-->
    <bean id="user" class="com.yun.entity.User"></bean>

</beans>

1.4 实体类User

public class User implements Serializable {
    private static final long serialVersionUID = 215276094532308892L;
    
    private Integer id;
    
    private String username;
    
    private String password;
    
    private Date createtime;
    
    private String phone;
    
    private Integer deptno;


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public Integer getDeptno() {
        return deptno;
    }

    public void setDeptno(Integer deptno) {
        this.deptno = deptno;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", createtime=" + createtime +
                ", phone='" + phone + '\'' +
                ", deptno=" + deptno +
                '}';
    }
}

1.5 UserDao以及实现类UserDaoImpl

public interface UserDao {
    /**
     * @ author:zyk
     * @ description:添加人员
     * @ param:[user]
     * @ return:boolean
     **/
   boolean addUser(User user);

   /**
    * @ author:zyk
    * @ description:删除人员
    * @ param:[id]
    * @ return:boolean
    **/
   boolean deleteUser(int id);

   /**
    * @ author:zyk
    * @ description:查询单个人员
    * @ param:[id]
    * @ return:com.yun.entity.User
    **/
   User findUser(int id);

   /**
    * @ author:zyk
    * @ description:查询所有人员信息
    * @ param:[]
    * @ return:java.util.List<com.yun.entity.User>
    **/
   List<User> findAllUser();

   /**
    * @ author:zyk
    * @ description:修改人员信息
    * @ param:[user]
    * @ return:boolean
    **/
   boolean updateUser(User user);
}

public class UserDaoImpl implements UserDao {
    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public boolean addUser(User user) {
        String sql = "insert into user(id,username,password,createtime,phone,deptno) values(?,?,?,?,?,?)";
        Object []objects = {user.getId(),user.getUsername(),user.getPassword(),user.getCreatetime(),user.getDeptno(),user.getDeptno()};
        int update = jdbcTemplate.update(sql, objects);
        if(update>0){
            return true;
        }
        return false;
    }

    @Override
    public boolean deleteUser(int id) {
        String sql = "delete  from user where id = ?";
        Object []objects = {id};
        int update = jdbcTemplate.update(sql, objects);
        if(update>0){
            return true;
        }
        return false;
    }

    @Override
    public User findUser(int id) {
        String sql = "select * from user where id = ?";
        Object []objects = {id};
        User user = jdbcTemplate.queryForObject(sql, objects, new BeanPropertyRowMapper<User>(User.class));
        return user;
    }

    @Override
    public List<User> findAllUser() {
        String sql = "select * from user";
        List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return userList;
    }

    @Override
    public boolean updateUser(User user) {
        String sql = "update user set username=?,password=?,createtime=?,phone=?,deptno=? where id=?";
        Object []objects = {user.getUsername(),user.getPassword(),user.getCreatetime(),user.getPhone(),user.getDeptno(),user.getId()};
        int update = jdbcTemplate.update(sql, objects);
        if(update>0){
            return true;
        }
        return false;
    }
}

 

1.6 使用junit进行测试

public class UserDaoTest {
    UserDao userDao;
    User user;
    @Before
    public void init(){
        ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
        userDao = app.getBean("userDao",UserDao.class);
        user = app.getBean("user",User.class);
    }
    @Test
    public void testAddUser() {
        user.setId(100);
        user.setUsername("测试");
        user.setPassword("123456");
        user.setCreatetime(new Date());
        user.setPhone("12354321");
        boolean b = userDao.addUser(user);
        if (b){
            System.out.println("插入成功");
        }else {
            System.out.println("插入失败");
        }
    }
    @Test
    public void testDeleteUser() {
        int id  = 100;
        boolean b = userDao.deleteUser(id);
        if (b){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
    }
    @Test
    public void testFindUser() {
        int id  = 100;
        User user = userDao.findUser(id);
        System.out.println(user.toString());
    }
    @Test
    public void testFindAllUser() {
        List<User> allUser = userDao.findAllUser();
        for (User usr:allUser) {
            System.out.println(usr.toString());
        }
    }
    @Test
    public void testUpdateUser() {
        user.setId(100);
        user.setUsername("修改");
        user.setPassword("000000");
        user.setCreatetime(new Date());
        user.setPhone("555555555");
        boolean b = userDao.updateUser(user);
        if (b){
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }
    }
}

上述代码测试均成功，这里就不在粘贴截图了。

2.配置类方式

2.1引入依赖

同上

2.2 添加数据源配置文件

同上

2.3 实体类User

同上

2.4 配置类SpringConfig

@Configuration
@ComponentScan(basePackages = "com.yun")
@PropertySource(value = "classpath:druid.properties")
public class SpringConfig {
    @Value("${driverName}")
    private String driverClassName;
    @Value("${url}")
    private String url;
    @Value("${name}")
    private String username;
    @Value("${password}")
    private String password;
    /**
     *  @Bean("empDaoSpringJdbcImpl")最终会覆盖默认的bean名字（默认是方法名）
     * @return
     */
    @Bean
    public DruidDataSource druidDataSource(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(this.driverClassName);
        druidDataSource.setUrl(this.url);
        druidDataSource.setUsername(this.username);
        druidDataSource.setPassword(this.password);
        return druidDataSource;
    }

    @Bean(value = "jdbcTemplate")
    public JdbcTemplate jdbcTemplate(){
        JdbcTemplate jdbcTemplate= new JdbcTemplate();
        jdbcTemplate.setDataSource(druidDataSource());
        return jdbcTemplate;
    }
    @Bean(value = "user")
    public User user(){
        User user = new User();
        return user;
    }
}

2.5 UserDao以及实现类UserDaoImpl

UserDao同上

@Repository(value = "userDao")
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public boolean addUser(User user) {
        String sql = "insert into user(id,username,password,createtime,phone,deptno) values(?,?,?,?,?,?)";
        Object []objects = {user.getId(),user.getUsername(),user.getPassword(),user.getCreatetime(),user.getDeptno(),user.getDeptno()};
        int update = jdbcTemplate.update(sql, objects);
        if(update>0){
            return true;
        }
        return false;
    }

    @Override
    public boolean deleteUser(int id) {
        String sql = "delete  from user where id = ?";
        Object []objects = {id};
        int update = jdbcTemplate.update(sql, objects);
        if(update>0){
            return true;
        }
        return false;
    }

    @Override
    public User findUser(int id) {
        String sql = "select * from user where id = ?";
        Object []objects = {id};
        User user = jdbcTemplate.queryForObject(sql, objects, new BeanPropertyRowMapper<User>());
        return user;
    }

    @Override
    public List<User> findAllUser() {
        String sql = "select * from user";
        List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return userList;
    }

    @Override
    public boolean updateUser(User user) {
        String sql = "update user set username=?,password=?,createtime=?,phone=?,deptno=? where id=?";
        Object []objects = {user.getUsername(),user.getPassword(),user.getCreatetime(),user.getPhone(),user.getDeptno(),user.getId()};
        int update = jdbcTemplate.update(sql, objects);
        if(update>0){
            return true;
        }
        return false;
    }
}

2.6 使用junit进行测试

public class SpringJdbcTest {
    User user;
    UserDao userDao;
    @Before
    public void init(){
        //加载spring容器

        //另一种写法(推荐)
        AnnotationConfigApplicationContext ann=new AnnotationConfigApplicationContext();
        //注册配置类
        ann.register(SpringConfig.class);
        //刷新spring容器
        ann.refresh();
        //获取bean
        userDao = ann.getBean("userDao",UserDao.class);
        user  = ann.getBean("user",User.class);
    }
    @Test
    public void testAddUser() {
        user.setId(100);
        user.setUsername("测试");
        user.setPassword("123456");
        user.setCreatetime(new Date());
        user.setPhone("12354321");
        boolean b = userDao.addUser(user);
        if (b){
            System.out.println("插入成功");
        }else {
            System.out.println("插入失败");
        }
    }
    @Test
    public void testDeleteUser() {
        int id  = 100;
        boolean b = userDao.deleteUser(id);
        if (b){
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
    }
    @Test
    public void testFindUser() {
        int id  = 100;
        User user = userDao.findUser(id);
        System.out.println(user.toString());
    }
    @Test
    public void testFindAllUser() {
        List<User> allUser = userDao.findAllUser();
        for (User usr:allUser) {
            System.out.println(usr.toString());
        }
    }
    @Test
    public void testUpdateUser() {
        user.setId(100);
        user.setUsername("修改");
        user.setPassword("000000");
        user.setCreatetime(new Date());
        user.setPhone("555555555");
        boolean b = userDao.updateUser(user);
        if (b){
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }
    }

}

测试成功！上面代码未粘贴导入的一些类，所以使用时自己导入一下即可。由于无法上传sql,下面给个数据库截图，自己配置一下即可。