spring管理数据库连接池完成 JdbcTemplate crud 及使用spirng提供的 test类--spring环境下

目录结构如上图

 

javabean - User类

package com.xiangshuai.bean;

 

public class User {

    private String name;

    private Integer id;

    public String getName() {

       return name;

    }

    public void setName(String name) {

       this.name = name;

    }

    public Integer getId() {

       return id;

    }

    public void setId(Integer id) {

       this.id = id;

    }

    @Override

    public String toString() {

       return "User [name=" + name + ", id=" + id + "]";

    }

   

}

 

dao层

package com.xiangshuai.dao;

 

import java.util.List;

 

import com.xiangshuai.bean.User;

 

public interface UserDao {

         public void save(User u);

        

         public void delete(Integer id);

        

         public void update(User u);

        

         public User getById(Integer id);

        

         public int getTotalCount();

 

        

         public List<User> getAll();

}

 

package com.xiangshuai.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 com.xiangshuai.bean.User;

 

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

public class UserDaoImpl extends JdbcDaoSupport implements UserDao {

         @Override

         public void save(User u) {

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

                  super.getJdbcTemplate().update(sql, 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

         public User getById(Integer id) {

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

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

 

                          @Override

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

                                   User user = new User();

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

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

                                   return user;

                          }

                         

                  },id);

                 

         }

         @Override

         public int getTotalCount() {

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

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

                  return count;

         }

        

 

 

         @Override

         public List<User> getAll() {

                  String sql = "select * from t_user  ";

                  List<User> list = 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 list;

         }

 

 

}

 

配置文件

jdbc.jdbcUrl=jdbc:mysql:///crm

jdbc.driverClass=com.mysql.jdbc.Driver

jdbc.user=root

jdbc.password=password

 

<?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" 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 ">

 

<!-- 指定spring读取db.properties配置 -->

<context:property-placeholder location="classpath:db.properties"  />

 

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

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

    <property name="jdbcUrl" value="${jdbc.jdbcUrl}" ></property>

    <property name="driverClass" value="${jdbc.driverClass}" ></property>

    <property name="user" value="${jdbc.user}" ></property>

    <property name="password" value="${jdbc.password}" ></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="com.xiangshuai.dao.UserDaoImpl" >

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

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

</bean>

   

 

</beans>

 

测试

package com.xiangshuai.demo;

 

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 com.sun.org.apache.xml.internal.resolver.helpers.PublicId;

import com.xiangshuai.bean.User;

import com.xiangshuai.dao.UserDao;

 

/**

 * @author lqx

 * 引入 spring-aop.jar包

 * 配置 c3p0 连接池 JdbcTemplate --applicationContext.xml中配置好

 * * 所在位置:E:\学习文档子目录压缩\框架\spring\spring小技巧\spring管理数据库连接池完成 JdbcTemplate crud 及使用spirng提供的 test类--spring环境下

 * E:\学习文档子目录压缩\框架\spring\简单模拟spring IOC底层实现原理

或 我的网盘/我的笔记/学习文档子目录压缩/框架/spring/spring小技巧/spring管理数据库连接池完成 JdbcTemplate crud 及使用spirng提供的 test类--spring环境下

 */

 

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration("classpath:applicationContext.xml")

public class Demo {                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             

         @Resource(name="userDao")

         private UserDao ud;

        

         @Test

         public void save(){

                  User user1 = new User();

                  user1.setName("王大亮");

                  User user2 = new User();

                  user1.setName("赵云");

                  ud.save(user1);

                  ud.save(user2);

         }      

        

        

         @Test

         public void delete(){

                  ud.delete(3);

         }      

        

        

         @Test

         public void update(){

                  User user = ud.getById(1);

                  user.setName("王小米");

        ud.update(user);

         }      

        

        

         @Test

          public void getll(){

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

                    System.out.println(all);

         }      

        

        

 

}