gitsilence 的个人博客

spring的jdbc_template的入门例子

数据源

具体配置见applicationContext.xml

c3p0数据源

        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>

Druid数据源

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>

• 导入jar

          <dependency>
              <groupId>org.springframework</groupId>
              <artifactId>spring-jdbc</artifactId>
              <version>5.2.0.RELEASE</version>
          </dependency>
  

• 导入mysql驱动

          <dependency>
              <groupId>mysql</groupId>
              <artifactId>mysql-connector-java</artifactId>
              <version>5.1.43</version>
          </dependency>
  

• 数据库

  CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(255) DEFAULT NULL,
    `password` varchar(255) DEFAULT NULL,
    `address` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
  
  
  

• 配置数据源

  <?xml version="1.0" encoding="UTF-8"?>
  <beans xmlns="http://www.springframework.org/schema/beans"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
  
  <!--    配置数据源-->
      <bean id="springDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <!--       数据库驱动 -->
          <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
          <property name="url" value="jdbc:mysql:///jdbc_template"/>
          <property name="username" value="root"/>
          <property name="password" value="123456"/>
      </bean>
  
  
      <!--    配置Druid数据源-->
      <bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource">
          <!--       数据库驱动 -->
          <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
          <property name="url" value="jdbc:mysql:///jdbc_template"/>
          <property name="username" value="root"/>
          <property name="password" value="123456"/>
      </bean>
  
      <!--    配置c3p0数据源-->
      <bean id="c3p0DataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
          <!--       数据库驱动 -->
          <property name="driverClass" value="com.mysql.jdbc.Driver"/>
          <property name="jdbcUrl" value="jdbc:mysql:///jdbc_template"/>
          <property name="user" value="root"/>
          <property name="password" value="123456"/>
      </bean>
  
  
  <!--    配置jdbc模板-->
      <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
          <property name="dataSource" ref="c3p0DataSource"/>
      </bean>
  
      <bean id="user" class="cn.lacknb.beans.User">
          <constructor-arg index="0" value="test01"/>
          <constructor-arg index="1" value="123456"/>
          <constructor-arg index="2" value="安阳师范学院老校区"/>
      </bean>
  
      <bean id="userDao" class="cn.lacknb.dao.UserDaoImpl">
          <property name="jdbcTemplate" ref="jdbcTemplate"/>
      </bean>
      <bean id="service" class="cn.lacknb.service.UserServiceImpl">
          <property name="userDao" ref="userDao"/>
      </bean>
  
  </beans>
  

• User.java

  package cn.lacknb.beans;
  
  public class User {
      private Integer id;
      private String username;
      private String password;
      private String address;
  
      public User(String username, String password, String address) {
          this.username = username;
          this.password = password;
          this.address = address;
      }
  
      public User() {
      }
  
      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 String getAddress() {
          return address;
      }
  
      public void setAddress(String address) {
          this.address = address;
      }
  
      @Override
      public String toString() {
          return "User{" +
                  "id=" + id +
                  ", username='" + username + '\'' +
                  ", password='" + password + '\'' +
                  ", address='" + address + '\'' +
                  '}';
      }
  }
  
  

• UserDao.java

  package cn.lacknb.dao;
  
  import cn.lacknb.beans.User;
  
  import java.util.List;
  
  public interface UserDao {
  
      int addUser(User user);
      int deleteUser(Integer id);
      int updateUser(User user);
      User find(Integer id);
      List<User> findAll();
  
  }
  
  

• UserImpl.java

  package cn.lacknb.dao;
  
  import cn.lacknb.beans.User;
  import org.springframework.jdbc.core.BeanPropertyRowMapper;
  import org.springframework.jdbc.core.JdbcTemplate;
  import org.springframework.jdbc.core.RowMapper;
  
  import java.util.List;
  
  public class UserDaoImpl implements UserDao {
  
      private JdbcTemplate jdbcTemplate;
  
      public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
          this.jdbcTemplate = jdbcTemplate;
      }
  
      public int addUser(User user) {
          String sql = "insert into user(username, password, address) values(?, ?, ?)";
          return jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getAddress());
      }
  
      public int deleteUser(Integer id) {
          String sql = "delete from user where id=?";
          return jdbcTemplate.update(sql, id);
      }
  
      public int updateUser(User user) {
          String sql = "update user set username=?, password=?, address=? where id = ?";
          return jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getAddress(), user.getId());
      }
  
      public User find(Integer id) {
          String sql = "select * from user where id = ?";
          RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
          return (User) jdbcTemplate.queryForObject(sql, rowMapper, id);
      }
  
      public List<User> findAll() {
          String sql = "select * from user";
          RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
          return jdbcTemplate.query(sql, rowMapper);
      }
  }
  
  

• UserService.java

  package cn.lacknb.service;
  
  import cn.lacknb.beans.User;
  
  import java.util.List;
  
  public interface UserService {
  
      int addUser(User user);
      int deleteUser(Integer id);
      int updateUser(User user);
      User find(Integer id);
      List<User> findAll();
  
  }
  
  

• UserServiceImpl.java

  package cn.lacknb.service;
  
  import cn.lacknb.beans.User;
  import cn.lacknb.dao.UserDaoImpl;
  
  import java.util.List;
  
  public class UserServiceImpl implements UserService {
  
  
      private UserDaoImpl userDao;
  
      public void setUserDao(UserDaoImpl userDao) {
          this.userDao = userDao;
      }
  
      public int addUser(User user) {
          return userDao.addUser(user);
      }
  
      public int deleteUser(Integer id) {
          return userDao.deleteUser(id);
      }
  
      public int updateUser(User user) {
          return userDao.updateUser(user);
      }
  
      public User find(Integer id) {
          return userDao.find(id);
      }
  
      public List<User> findAll() {
          return userDao.findAll();
      }
  }
  
  

• MyTest.java

  package cn.lacknb.test;
  
  import cn.lacknb.beans.User;
  import cn.lacknb.service.UserServiceImpl;
  import org.junit.Before;
  import org.junit.Test;
  import org.springframework.context.ApplicationContext;
  import org.springframework.context.support.ClassPathXmlApplicationContext;
  
  import java.util.List;
  
  public class MyTest {
  
      ApplicationContext context;
      UserServiceImpl service;
  
      @Before
      public void before() {
          context = new ClassPathXmlApplicationContext("applicationContext.xml");
          service = (UserServiceImpl) context.getBean("service");
      }
  
      @Test
      // 添加用户
      public void test01() {
  
          User user = (User) context.getBean("user");
          System.out.println("改变的行数为: " + service.addUser(user));
      }
  
      // 删除用户
      @Test
      public void test02() {
          // 删除主键为1的用户
          System.out.println("影响的行数: " + service.deleteUser(1));
      }
  
      // 修改用户
      @Test
      public void test03(){
          User user = (User) context.getBean("user");
          // 修改2号 的 username 为张三
          user = service.find(2);
          user.setUsername("张三");
          System.out.println("影响的行数为: " + service.updateUser(user));
      }
  
      // 查询一个用户
      @Test
      public void test04(){
          // 查找2号用户
          User user = service.find(2);
          System.out.println(user);
      }
  
      // 查找所有用户
      @Test
      public void test05(){
          List<User> list = service.findAll();
          for (User user : list){
              System.out.println(user);
          }
      }
  
  }