JdbcTemplate的使用

2021-02-07 分类 Spring 作者 Nayuki ~8.71K 字

JdbcTemplate的介绍

Spring框架对JDBC的封装
需要引入相关jar包
- mysql-connector
- jdbc
- orm
- tx
- druid

JdbcTemplate的配置

bean.xml中配置数据库连接池

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
	destroy-method="close">
	<property name="url" value="jdbc:mysql:///test" />
	<property name="username" value="root" />
	<property name="password" value="root" />
	<property name="driverClassName" value="com.mysql.jdbc.Driver" />
</bean>

bean.xml中配置JdbcTemplate对象

1
2
3

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

JdbcTemplate的使用

bean.xml

1	<context:component-scan base-package="com.atguigu"></context:component-scan>

创建UserDao接口

1 2	public interface UserDao { }

创建UserDaoImpl实现类

@Repository
public class UserDaoImpl implements UserDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;
}

创建UserService类

@Service
public class UserService {
    UserDao userDao;
}

JdbcTemplate操作数据库

创建User实体类

public class User {
    int id;
    String name;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
}

增加

创建UserDao接口

1
2
3

public interface UserDao {
    void add(User user);
}

创建UserDaoImpl实现类

@Repository()
public class UserDaoImpl implements UserDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void add(User user) {
        String sql = "insert into user(name) values(?)";
        Object[] args = {user.getName()};
        jdbcTemplate.update(sql,args);
    }
}

创建UserService类

@Service
public class UserService {
    @Autowired
    UserDao userDao;
    public void addUser(User user){
        userDao.add(user);
    }
}

测试

@Test
public void testAdd(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService =context.getBean("userService", UserService.class);
    User user=new User();
    user.setName("Nayuki");
    userService.addUser(user);
}

修改

UserService类和UserDao类的创建与上述类似
创建UserDaoImpl实现类

@Repository()
public class UserDaoImpl implements UserDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void update(User user) {
        String sql = "update user set name=? where id=?";
        Object[] args = {user.getName(),user.getId()};
        jdbcTemplate.update(sql,args);
    }
}

测试

@Test
public void testAdd(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService =context.getBean("userService", UserService.class);
    User user=new User();
    user.setId(1);
    user.setName("NAYUKI");
    userService.updateUser(user);
}

删除

UserService类和UserDao类的创建与上述类似
创建UserDaoImpl实现类

@Repository()
public class UserDaoImpl implements UserDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void delete(User user) {
        String sql = "delete from user where id=?";
        Object[] args = {user.getId()};
        jdbcTemplate.update(sql,args);
    }
}

测试

@Test
public void testAdd(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService =context.getBean("userService", UserService.class);
    User user=new User();
    user.setId(1);
    userService.deleteUser(user);
}

查询值

UserService类和UserDao类的创建与上述类似
创建UserDaoImpl实现类

@Repository()
public class UserDaoImpl implements UserDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public int query() {
        String sql="select count(*) from user";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }
}

测试

@Test
public void testAdd(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService =context.getBean("userService", UserService.class);
    System.out.println(userService.queryCount());
}

查询对象

UserService类和UserDao类的创建与上述类似
创建UserDaoImpl实现类

@Repository()
public class UserDaoImpl implements UserDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public User queryUser(int id) {
        String sql="select * from user where id=?";
        User user=jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);
        return user;
    }
}

测试

@Test
public void testAdd(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService =context.getBean("userService", UserService.class);
    System.out.println(userService.queryUser(1).getName());
}

查询集合

UserService类和UserDao类的创建与上述类似
创建UserDaoImpl实现类

@Repository()
public class UserDaoImpl implements UserDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public List<User> getAllUser() {
        String sql="select * from user";
        List<User> users=jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return users;
    }
}

测试

@Test
public void testAdd(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService =context.getBean("userService", UserService.class);
    System.out.println(userService.getAllUser().get(0).getName());
}

批量增加

UserService类和UserDao类的创建与上述类似
创建UserDaoImpl实现类

@Repository()
public class UserDaoImpl implements UserDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void batchAdd(List<Object[]> users) {
        String sql = "insert into user(name) values(?)";
        jdbcTemplate.batchUpdate(sql,users);
    }
}

测试

@Test
public void testAdd(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService =context.getBean("userService", UserService.class);
    List<Object[]> batchArgs = new ArrayList<>();
    Object[] o1 = {"Nayuki"};
    Object[] o2 = {"NAYUKI"};
    batchArgs.add(o1);
    batchArgs.add(o2);
    userService.batchAddUser(batchArgs);
}

批量修改

UserService类和UserDao类的创建与上述类似
创建UserDaoImpl实现类

@Repository()
public class UserDaoImpl implements UserDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void batchUpdate(List<Object[]> users) {
        String sql = "update user set name=? where id=?";
        jdbcTemplate.batchUpdate(sql,users);
    }
}

测试

@Test
public void testAdd(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService =context.getBean("userService", UserService.class);
    List<Object[]> batchArgs = new ArrayList<>();
    Object[] o1 = {"NAYUKI",3};
    Object[] o2 = {"Nayuki",4};
    batchArgs.add(o1);
    batchArgs.add(o2);
    userService.batchUpdateUser(batchArgs);
}

批量删除

UserService类和UserDao类的创建与上述类似
创建UserDaoImpl实现类

@Repository()
public class UserDaoImpl implements UserDao{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public void batchDelete(List<Object[]> users) {
        String sql = "delete from user where id=?";
        jdbcTemplate.batchUpdate(sql,users);
    }
}

测试

@Test
public void testAdd(){
    ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
    UserService userService =context.getBean("userService", UserService.class);
    List<Object[]> batchArgs = new ArrayList<>();
    Object[] o1 = {3};
    Object[] o2 = {4};
    batchArgs.add(o1);
    batchArgs.add(o2);
    userService.batchDeleteUser(batchArgs);
}