Spring框架之JdbcTemplate

JdbcTemplate

1、什么是 JdbcTemplate

（1）Spring 框架对 JDBC 进行封装，使用 JdbcTemplate 方便实现对数据库操作

2、准备工作

（1）引入相关 依赖

<dependencies>
    <dependency>
        <groupId>aopalliance</groupId>
        <artifactId>aopalliance</artifactId>
        <version>1.0</version>
    </dependency>
    <dependency>
        <groupId>org.aspectj</groupId>
        <artifactId>aspectjweaver</artifactId>
        <version>1.9.5</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.16</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.28</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.2.2.RELEASE</version>
    </dependency>
    <!--针对事物的一些操作依赖-->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>5.2.2.RELEASE</version>
    </dependency>
    <!--整合jdbc，mybatis数据库需要这个依赖-->
    <dependency>
        <groupId>springframework</groupId>
        <artifactId>spring-orm</artifactId>
        <version>1.2.6</version>
    </dependency>
</dependencies>

 （2）.在配置文件中配置数据库连接池

<!--连接数据库-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driver" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/mybatis?user=true&amp;useUnicode=true&amp;characterEncoding=utf8"/>
        <property name="username" value="root"/>
        <property name="password" value="123456abcd"/>
    </bean>

（3）配置jdbcTemplate对象，注入DataSource

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

（4）创建Dao、service类在dao注入jdbcTemplate对象 

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

@Service
public class UserService {
    @Resource
    private UserDao userDao;

 jdbctemplate操作数据库

（1）写实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
    private int id;
    private String name;
    private String pwd;
}

（2）在dao进行数据库增删改查操作（比尚硅谷黑马还牛逼的增删改查）

dao

public interface Userdao {
    List<Map<String, Object>> selectUser();
    int  insertUser();
    void updateUser();
    void  deleteUser();
}

dao接口

@Repository
public class UserdaoImpl implements Userdao {
    @Autowired
    private JdbcTemplate jdbcTemplate;


    @Override
    public List<Map<String, Object>> selectUser() {
        String sql="select  * from mybatis.user ";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        return maps;
    }

    @Override
    public int insertUser() {
        String sql="insert into mybatis.user set id=?,name=?,pwd=?";
        int update = jdbcTemplate.update(sql,13,"秦始皇","128946");
return update;
    }

    @Override
    public void updateUser() {
        String sql="update mybatis.user set name=?,pwd=? where id=?";
        jdbcTemplate.update(sql,"赵敏","123456",1);

    }

    @Override
    public void deleteUser() {
        String sql="delete from mybatis.user where id=?";
        int update = jdbcTemplate.update(sql, 10);

    }
}

service

@Service("userservice")
public class UserService {
    @Autowired
    private Userdao userdao;
    //查询操作
    public List<Map<String, Object>> querylist(){
        List<Map<String, Object>> maps = userdao.selectUser();
        return maps;
    }
//增操作
    public  int insertUser(){
        int i = userdao.insertUser();
        return i;
    }
    //修改操作
    public  void updateUser(){
        userdao.updateUser();
    }
    //删除操作
    public  void deleteUser(){
        userdao.deleteUser();
    }
}

 测试

public class Mytest {
    @Test
    public  void query(){
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        UserService userservice = context.getBean("userservice", UserService.class);
        List<Map<String, Object>> querylist = userservice.querylist();
        for (Map<String, Object> map : querylist) {
            System.out.println(map);
        }
    }
    @Test
    public  void insert(){
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        UserService userservice = context.getBean("userservice", UserService.class);
       userservice.insertUser();
        }
        @Test
    public  void update(){
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        UserService userservice = context.getBean("userservice", UserService.class);
       userservice.updateUser();
        }
        @Test
    public  void delete(){
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        UserService userservice = context.getBean("userservice", UserService.class);
       userservice.deleteUser();
        }

}

jdbcTemplate的批量操作

1.批量操作：操作表里面的多条数据

2.jdbctemplate实现批量操作

public interface Userdao {
   


    void batchadd();

    void batchupdate();
}

dao接口

   //批量操作
    @Override
    public void batchadd() {
        List<Object[]> batchArgs=new ArrayList<>();
        String sql="insert into mybatis.user values(?,?,?)";
        Object[] o1={16,"赵华","124535"};
        Object[] o2={14,"赵华","124535"};
        Object[] o3={15,"赵华","124535"};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
         jdbcTemplate.batchUpdate(sql,batchArgs);
    } //批量操作
    @Override
    public void batchupdate() {
        List<Object[]> batchArgs=new ArrayList<>();
        String sql="update mybatis.user set name=?,pwd=? where id=?";
        Object[] o1={"李二狗","124535",1};
        Object[] o2={"赵汉三","124543",2};
        Object[] o3={"李寒苦","454656",3};
        batchArgs.add(o1);
        batchArgs.add(o2);
        batchArgs.add(o3);
         jdbcTemplate.batchUpdate(sql,batchArgs);
    }

service

//批量增加
    public  void  batchAddUser(){
       userdao.batchadd();
    }
    //批量修改
    public  void  batchupdateUser(){
       userdao.batchupdate();
    }

测试

     @Test
    public  void batchadd(){
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        UserService userservice = context.getBean("userservice", UserService.class);
       userservice.batchAddUser();
        }@Test
    public  void batchupdate(){
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        UserService userservice = context.getBean("userservice", UserService.class);
       userservice.batchupdateUser();
        }