【JAVA】JdbcTemplate——Java学习笔记（三）

JdbcTemplate

1、JdbcTemplate准备

Spring框架对JDBC进行封装，使用JdbcTemplate方便是按对数据库操作

1.1、引入相关jar包

druid-1.1.9.jar
mysql-connector-java-5.1.7-bin.jar
spring-jdbc-5.2.6.RELEASE.jar
//整合其他持久化层框架时必须的依赖
spring-orm-5.2.6.RELEASE.jar
//实务操作相关依赖
spring-tx-5.2.6.RELEASE.jar

1.2、在spring配置文件中配置数据库连接池

    <!-- 数据库连接池 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          destroy-method="close">
        <property name="url" value="jdbc:mysql:///book" />
        <property name="username" value="root" />
        <property name="password" value="root" />
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    </bean>

1.3、配置JdbcTemplate对象，注入DataSource

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

1.4、创建service类，dao类，在dao中注入JdbcTemplate对象

• 在配置文件中开启扫描

<!--开启注解扫描-->

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

• service

@Service(value = "userService")
public class UserService {

    //注入dao
    @Autowired
    private Userdao userdao;
}

• dao

@Repository
public class UserDaoImpl implements Userdao{

    //注入JdbcTemplate
    @Autowired
    private JdbcTemplate jdbcTemplate;
}

2、JdbcTemplate操作数据库

• user

public class User {

    private String username;
    private Integer id;
    private String password;
    private String email;

    public User() {
    }

    public User(String username, String password, String email) {
        this.username = username;
        this.password = password;
        this.email = email;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getEmail() {
        return email;
    }

    public void setEmil(String emil) {
        this.email = emil;
    }

    @Override
    public String toString() {
        return "User{" +
                "username='" + username + '\'' +
                ", id=" + id +
                ", password='" + password + '\'' +
                ", emil='" + email + '\'' +
                '}';
    }
}

2.1、增加、修改、删除

• dao

    @Override
    public int add(User user) {
        System.out.println("dao add...");
        String sql = "insert into t_user (`username`, `password`, `email`) VALUES (?, ?, ?)";
        Object[] args =  {user.getUsername(), user.getPassword(),user.getEmail()};
        //返回的int类型代表影响行数
        int update = jdbcTemplate.update(sql,args);
        return update;
    }

    @Override
    public void update(User user) {
        System.out.println("dao update...");
        System.out.println(user.getId());
        String sql ="update t_user set username=?,password=?,email=? where id = ? ";
        Object[] args =  {user.getUsername(), user.getPassword(),user.getEmail(),user.getId().intValue()};
        int update = jdbcTemplate.update(sql,args);
        System.out.println(update);
    }

    @Override
    public void delete(Integer id) {
        System.out.println("dao delete...");
        System.out.println(id);
        String sql ="delete from t_user where id = ?";
        Object[] args =  {id.intValue()};
        int update = jdbcTemplate.update(sql,args);
        System.out.println(update);
    }

• service

  public void add(User user){
        System.out.println("service add......");
        userdao.add(user);
    }

    public void update(User user){
        System.out.println("service update...");
        userdao.update(user);
    }

    public void delete(Integer id){
        System.out.println("service delete...");
        userdao.delete(id);
    }

• test

 @Test
    public void testAdd(){
        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean.xml");
        UserService userService = context.getBean("userService", UserService.class);
        User user = new User("萧炎","123456","douqihuayi");
        userService.add(user);
    }

    @Test
    public void testUpdate(){
        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean.xml");
        UserService userService = context.getBean("userService", UserService.class);
        User user = new User("萧炎123","123456","douqihuayi");
        user.setId(9);
        userService.update(user);
    }

    @Test
    public void testDelete(){
        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean.xml");
        UserService userService = context.getBean("userService", UserService.class);
        Integer id = 9;
        userService.delete(id);
    }

2.2、查询

2.2.1、查询返回某个值

• dao

    @Override
    public int selectCount() {
        String sql = "select count(*) from t_user";
        //queryForObject(String sql, Class<T> requiredType)
        //第一个参数：sql语句
        //第二个参数:返回类型Class
        Integer integer = jdbcTemplate.queryForObject(sql, Integer.class);
        return integer;
    }

• service

   public int selectCount(){
        return userdao.selectCount();
    }

• test

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

2.2.2、查询返回对象

• dao

    @Override
    public User selectUserInfo(Integer id) {
        String sql = "select * from t_user where id = ?";
        //queryForObject(String sql,RowMapper<T> rowMapper,Object...args)
        //第一个参数：sql语句
        //第二个参数：RowMapper是接口，可根据返回数据的不同，在实现类中完成数据封装
        //第三个参数：sql语句值
        User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), id);//BeanPropertyRowMapper是RowMapper的实现类
        return user;
    }

• service

    public User selectUserInfo(Integer id){
        return userdao.selectUserInfo(id);
    }

• test

    @Test
    public void testSelectUserInfo(){
        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean.xml");
        UserService userService = context.getBean("userService", UserService.class);
        User user = userService.selectUserInfo(1);
        System.out.println(user);
    }

2.2.3、查询返回集合

• dao

    @Override
    public List<User> selectAll() {
        String sql = "select * from t_user";
        //query(String sql,RowMapper<T> rowMapper,Object...args)
        //第一个参数：sql语句
        //第二个参数：RowMapper是接口，可根据返回数据的不同，在实现类中完成数据封装
        //第三个参数：sql语句值
        List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<User>(User.class));
        return userList;
    }

• service

    public List<User> selectAll(){
        return userdao.selectAll();
    }

• test

    @Test
    public void testSelectAll() {
        ApplicationContext context =
                new ClassPathXmlApplicationContext("bean.xml");
        UserService userService = context.getBean("userService", UserService.class);
        for (User user : userService.selectAll()) {
            System.out.println(user);
        }

    }

3、批量操作

使用batchUpdate方法对数据库进行批量操作

//batchUpdate(String sql, List<Object[]> batchArgs)
//第一个参数：sql语句
//第二个参数：List集合，操作多记录
//int[]数组返回受影响的行数数组
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);