Spring 使用 JdbcTemplate 实现示例增删改查

1. 项目结构

2. 导入pom依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.bolin</groupId>
    <artifactId>spring-learning</artifactId>
    <version>1.0.0</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>5.2.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.16</version>
        </dependency>
        <dependency>
            <groupId> org.aspectj</groupId >
            <artifactId> aspectjweaver</artifactId >
            <version> 1.6.11</version >
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.2.0.RELEASE</version>
        </dependency>
    </dependencies>
</project>

3. 编写 pojo 实体类和 dao 层 service 层

1. User.java

// lombok 自动生成有参无参getter/setter等方法
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private Integer id;
    private String name;
    private String pwd;
}

2. UserDao.java

// 定义查询，删除，修改的方法
@Repository
public interface UserDao {
    // 查询所有用户
    List<User> getAllUser();
    // 删除用户
    void deleteUserById(int id);
    // 修改用户通过id
    int updateUser(String name,String pwd, int id);
}

3. UserDaoImpl.java

@Repository
public class UserDaoImpl implements UserDao {
	// 自动装配
    @Autowired
    private JdbcTemplate jdbcTemplate;

    // 查询所有用户
    @Override
    public List<User> getAllUser() {
         List<User> list = jdbcTemplate.query("select * from mybatis.user", new RowMapper() {
            @Override
            public User mapRow(ResultSet rs, int i) throws SQLException {
                User user = new User();
                user.setId(rs.getInt(1));
                user.setName(rs.getString(2));
                user.setPwd(rs.getString(3));
                return user;
            }
        });
      return list;
    }

    // 删除用户
    @Override
    public void deleteUserById(int id) {
        jdbcTemplate.update("delete from user where id=?", id);
    }

    @Override
    public int updateUser(String name,String pwd, int id) {
    	// 逗号不要写成and，记录一下踩过的坑
        return jdbcTemplate.update("update mybatis.user set name=?,pwd=? where id=?",name,pwd,id);
    }
}

4. UserServiceImpl.java

@Service
public class UserService {
	// service层调dao层
    @Autowired
    private UserDao userDao;

    public List<User> getAllUser(){
        return userDao.getAllUser();
    };

    public int deleteUserById(int id){
        userDao.deleteUserById(id);
        return id; 
    }
    
    public int updateUser(String name, String pwd, int id){
        return userDao.updateUser(name,pwd,id);
    }
}

5. applicationContext.xml

1、配置了数据源
2、接入了事务管理
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:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/aop
	   https://www.springframework.org/schema/aop/spring-aop.xsd
	   http://www.springframework.org/schema/tx
	   https://www.springframework.org/schema/tx/spring-tx.xsd
       http://www.springframework.org/schema/context
	   https://www.springframework.org/schema/context/spring-context.xsd">
	   
	 <!--1.开启包扫描-->
    <context:component-scan base-package="com.bolin.*"/>

    <!--2.配置数据源-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!--事务管理-->
    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>
    
    <tx:advice id="txAdvice" transaction-manager="txManager">
        <tx:attributes>
            <tx:method name="get*"  read-only="true"/>
            <tx:method name="delete*" propagation="REQUIRED"/>
            <tx:method name="update*" propagation="REQUIRED"/>
        </tx:attributes>
    </tx:advice>  
   
    <aop:config>
		<!--定义切点-->
        <aop:pointcut id="point" expression="execution(* com.bolin.*.*(..))"/>
        <!--定义通知-->
        <aop:advisor advice-ref="txAdvice" pointcut-ref="point" />
    </aop:config>
    
</beans>

6. 测试类Test.java

public class Test {
    @org.junit.Test // 1.查询所有用户
    public void test01(){
         ClassPathXmlApplicationContext cpx = new ClassPathXmlApplicationContext("applicationContext.xml");
         UserService userService = (UserService) cpx.getBean("userService");
         List<User> list  = userService.getAllUser();
       	 for (User user : list) {
            System.out.println(user);
        }
    }

    @org.junit.Test // 2.根据id删除用户
    public void test02(){
        ClassPathXmlApplicationContext cpx = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserService userService = (UserService) cpx.getBean("userService");

        int res = userService.deleteUserById(4); // 删除id=4 的用户
        if (res>0){
            System.out.println("id为"+res+"的用户被删除成功");
        }
    }

    // 根据id修改用户
    @org.junit.Test // 修改用户
    public void test03(){
        ClassPathXmlApplicationContext cpx = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserService userService = (UserService) cpx.getBean("userService");
        int res = userService.updateUser("aliy", "000000", 3);
        if (res>0){
            System.out.println(res+": 修改成功");
        }
    }
}

查询结果：

7. 总结

1. 使用 aop 一定要导入织入包 aspectjweave。
2. spring5 配置 jdbcTemplate 数据库源是：DriverManagerDataSource，还有数据源是dbcp包里的 BasicDataSource，后期可换成德鲁伊等优秀连接池作为数据源。
3. @Autowired 自动装配后，不用再写getter/setter方法，底层是通过反射来实现的。
4. jdbcTemplate 执行SQL语句的方法大致分为3类:
   • execute：可以执行所有SQL语句，一般用于执行DDL语句。
   • update：用于执行INSERT、UPDATE、DELETE等DML语句。
   • queryXxx：用于DQL数据查询语句。