JdbcTemplate实现增删查改

JdbcTemplate介绍

JdbcTemplate是Spring MVC内置的对JDBC的一个封装。使用spring的注入功能把DataSource注册到JdbcTemplate中。

JdbcTemplate提供的方法

1. execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
2. update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批处理相关语句；
3. query方法及queryForXXX方法：用于执行查询相关语句；
4. call方法：用于执行存储过程、函数相关语句。

实现步骤

项目结构：

数据库：

1. pom.xml添加依赖

<dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.0.0.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.16.8</version>
      <scope>provided</scope>
    </dependency>
    <!--    连接数据库的驱动-->
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.12</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.0.0.RELEASE</version>
    </dependency>

    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.0.1</version>
      <scope>test</scope>
    </dependency>

    <!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
    <dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.5.2</version>
    </dependency>

2. bean.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"
       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.3.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">

<!--    引入外部文件-->
    <context:property-placeholder location="classpath*:jdbc.properties"></context:property-placeholder>

    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass">
            <value>${jdbc.driver}</value>
        </property>
        <property name="jdbcUrl">
            <value>${jdbc.url}</value>
        </property>
        <property name="user">
            <value>${jdbc.username}</value>
        </property>
        <property name="password">
            <value>${jdbc.password}</value>
        </property>

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

<!--    这个时我们的dao层，他要注入jdbcTemplate-->
    <bean id="userDaoImpl" class="com.jt.dao.impl.UserDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

<!--    注入到service层-->
    <bean id="userServiceImpl" class="com.jt.service.impl.UserServiceImpl">
        <property name="userDaoImpl" ref="userDaoImpl"></property>
    </bean>
</beans>

3. 实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {
	private int userId;
	private String username;
	private String password;
	private String email;
}

4. 创建dao层接口和实现类

//dao层接口
public interface UserDao {
	List<User> selectAll();
	int add(User user);
	int delete(int id);
	int update(User user);
}
//------------------------------------------------
//接口实现类
@Data//注意，否则无法注入jdbcTemplate
public class UserDaoImpl implements UserDao {
	private JdbcTemplate jdbcTemplate;
	@Override
	public List<User> selectAll() {
		String sql = "select *  from user";
		BeanPropertyRowMapper<User> mapper = new BeanPropertyRowMapper<>(User.class);
		List<User> userList = jdbcTemplate.query(sql, mapper);
		return userList;
	}

	@Override
	public int add(User user) {
		String sql = "insert into user values(0,?,?,?)";
		int add = jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getEmail());
		return add;
	}

	@Override
	public int delete(int id) {
		String sql = "delete from user where userId = ?";
		int del = jdbcTemplate.update(sql, id);
		return del;
	}

	@Override
	public int update(User user) {
		String sql = "update user set username=?,password=?,email=? where userId=?";
		int update = jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getEmail(), user.getUserId());
		return update;
	}

}

5. service层接口及实现类

//业务层接口
public interface UserService {
	List<User> selectAllSer();
	int addSer(User user);
	int deleteSer(int id);
	int updateSer(User user);
}
//-------------------------------------------------
//业务层实现类
@Data
public class UserServiceImpl implements UserService {
   private UserDaoImpl userDaoImpl;
   @Override
   public List<User> selectAllSer() {
      return userDaoImpl.selectAll();
   }

   @Override
   public int addSer(User user) {
      return userDaoImpl.add(user);
   }

   @Override
   public int deleteSer(int id) {
      return userDaoImpl.delete(id);
   }

   @Override
   public int updateSer(User user) {
      return userDaoImpl.update(user);
   }

}

6. 测试类

public class Test01 {
	public static void main(String[] args) {
		ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
		UserServiceImpl userServiceImpl = context.getBean("userServiceImpl", UserServiceImpl.class);
		userServiceImpl.deleteSer(26);
		userServiceImpl.addSer(new User(0,"新建","1111","123@163.com"));
		userServiceImpl.updateSer(new User(25,"ADD","222222","22222"));
		List<User> users = userServiceImpl.selectAllSer();
		for(User user:users){
			System.out.println(user);
		}
	}
}

运行截图：