Spring5学习05:JdbcTemplate

迈不过友情╰ 2022-10-21 11:54 118阅读 0赞

## 1.JdbcTemplate ##

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

## 2.实战 ##

### 1.准备 ###

#### 1.引入依赖 ####

<dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.24</version>
            </dependency>
            
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>5.3.6</version>
            </dependency>
            
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-orm</artifactId>
                <version>5.3.6</version>
            </dependency>
            
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-tx</artifactId>
                <version>5.3.6</version>
            </dependency>

#### 2.配置连接池 ####

<context:property-placeholder
            location="classpath:jdbc.properties" />
    
        
        <bean id="dataSource"
            class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
            <property name="url" value="${prop.url}" />
            <property name="username" value="${prop.userName}" />
            <property name="password" value="${prop.password}" />
            <property name="driverClassName" value="${prop.driverClass}" />
        </bean>

#### 3.配置 JdbcTemplate 对象，注入 DataSource ####

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

#### 4.扫描注解 ####

<context:component-scan
            base-package="cn.zj.aop.an"></context:component-scan>

#### 5.创建 service 类，创建 dao 类，在 dao 注入 jdbcTemplate 对象 ####

@Repository
    public class UserDaoImpl implements UserDao {
    
        //注入 JdbcTemplate
        @Autowired
        private JdbcTemplate jdbcTemplate;
    }
    
    
    
    @Service
    public class UserService {
    
        // 注入 dao
        @Autowired
        private UserDao userDao;
        
    }

### 2.操作(CRUD) ###

实体类

public class User {
    
        private String userId;
        private String username;
        private String ustatus;
    
        @Override
        public String toString() {
            return "User [userId=" + userId + ", username=" + username + ", ustatus=" + ustatus + "]";
        }
    
        public String getUserId() {
            return userId;
        }
    
        public void setUserId(String userId) {
            this.userId = userId;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public String getUstatus() {
            return ustatus;
        }
    
        public void setUstatus(String ustatus) {
            this.ustatus = ustatus;
        }
    
    }

#### 1.添加 ####

service
    //添加
        public void addUser(User user) {
            userDao.add(user);
        }
        
    dao
    @Override
        public void add(User user) {
            // 1 创建 sql 语句
            String sql = "insert into t_user values(?,?,?)";
            // 2 调用方法实现
            Object[] args = { user.getUserId(), user.getUsername(), user.getUstatus() };
            int update = jdbcTemplate.update(sql, args);
            System.out.println(update);
    
        }
        
    测试
    
    @Test
        public void test1() {
            ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
            UserService userService = context.getBean("userService", UserService.class);
            User user = new User();
            user.setUserId("1");
            user.setUsername("java");
            user.setUstatus("a");
            userService.addUser(user);
        }

结果

![image.png][]

#### 2.修改 ####

service
    
    //修改
        public void updateUser(User user) {
            userDao.updateUser(user);
        }
    
    dao
    @Override
        public void updateUser(User user) {
            // TODO Auto-generated method stub
            String sql = "update t_user set username=?,ustatus=? where userId=?";
            // 2 调用方法实现
            Object[] args = { user.getUsername(), user.getUstatus() ,user.getUserId()};
            int update = jdbcTemplate.update(sql, args);
            System.out.println(update);
        }
    测试
    @Test
        public void test2() {
            ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
            UserService userService = context.getBean("userService", UserService.class);
            User user = new User();
            user.setUserId("1");
            user.setUsername("javaScrip");
            user.setUstatus("abc");
            userService.updateUser(user);
        }

![image.png][image.png 1]

#### 3.删除 ####

// 删除
        public void deleteUser(String id) {
            userDao.deleteUser(id);
        }
    
    
    @Override
        public void deleteUser(String id) {
            String sql="delete from t_user where userId=?";
            int update=jdbcTemplate.update(sql, id);
            System.out.println(update);
        }
    
    
    @Test
        public void test3() {
            ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
            UserService userService = context.getBean("userService", UserService.class);
            
            userService.deleteUser("1");
        }

![image.png][image.png 2]

#### 4.查询 ####

##### 1.查询总记录数 jdbcTemplate.queryForObject #####

@Test
        public void test4() {
            ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
            UserService userService = context.getBean("userService", UserService.class);
            
            int count = userService.selectUserCount();
            System.out.println("数据库中共有记录:"+count);
        }
    
    
    //查询记录数
        public int  selectUserCount() {
            return userDao.selectCount();
        }
    
    
        @Override
        public int selectCount() {
            String sql = "select count(0) from t_user";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
            return count;
        }

##### 2.查询返回对象 #####

@Test
        public void test5() {
            ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
            UserService userService = context.getBean("userService", UserService.class);
            
            User user=userService.findUserInfo("1");
            System.out.println(user);
        }
    
    //查询对象
        public User findUserInfo(String id) {
            // TODO Auto-generated method stub
            return userDao.findUserInfo(id);
        }
    
    @Override
        public User findUserInfo(String id) {
            String sql = "select userId,username,ustatus from t_user where userId=?";
            User user = jdbcTemplate.queryForObject(sql, new
                    BeanPropertyRowMapper<User>(User. class),id);
            return user;
        }

##### 3.查询返回集合 #####

@Test
        public void test6() {
            ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
            UserService userService = context.getBean("userService", UserService.class);
            
            List<User> list=userService.findAllUser();
            System.out.println(list);
        }
    
        //查询返回集合
        public List<User> findAllUser(){
            return userDao.findAllUser();
        }
    
    @Override
        public List<User> findAllUser() {
            String sql = "select userId,username,ustatus from t_user";
            List<User> list = jdbcTemplate.query(sql, new
                    BeanPropertyRowMapper<User>(User. class));
            return list;
        }

#### 5.批量操作 jdbcTemplate.batchUpdate ####

##### 1.添加 #####

@Test
        public void test7() {
            ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
            UserService userService = context.getBean("userService", UserService.class);
            List<Object[]> list =  new ArrayList<>();
            Object[] o1 = { "11", "易语言", "中文"};
            Object[] o2 = { "12", "c++", "cc"};
            Object[] o3 = { "13", "MySQL", "数据库"};
            list.add(o1);
            list.add(o2);
            list.add(o3);
            userService.batchAdd(list);
        }
    
    //批量添加
        public void batchAdd(List<Object[]> list){
            userDao.batchAdd(list);
        }
    
    @Override
        public void batchAdd(List<Object[]> list) {
            String sql = "insert into t_user values(?,?,?)";
            int[] ints = jdbcTemplate.batchUpdate(sql, list);
            System.out.println(ints);
        }

##### 2.修改 #####

@Test
        public void test8() {
            ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
            UserService userService = context.getBean("userService", UserService.class);
            List<Object[]> list =  new ArrayList<>();
            Object[] o1 = {  "易语言易", "中文语言","11"};
            Object[] o2 = { "c++c", "ccccc","12"};
            Object[] o3 = {"MySQL+ORACle", "数据库数据", "13"};
            list.add(o1);
            list.add(o2);
            list.add(o3);
            userService.batchUpdate(list);
        }
    
    
        //批量修改
        public void batchUpdate(List<Object[]> list) {
            userDao.batchUpdate(list);
        }
    
    @Override
        public void batchUpdate(List<Object[]> list) {
            String sql = "update t_user set username=?,ustatus=? where userId=?";
            int[] ints = jdbcTemplate.batchUpdate(sql, list);
            System.out.println(ints);
        }

##### 3.删除 #####

@Test
        public void test9() {
            ApplicationContext context = new ClassPathXmlApplicationContext("bean1.xml");
            UserService userService = context.getBean("userService", UserService.class);
            List<Object[]> list =  new ArrayList<>();
            Object[] o1 = { "11"};
            Object[] o2 = { "12"};
            Object[] o3 = { "13"};
            list.add(o1);
            list.add(o2);
            list.add(o3);
            userService.batchDelete(list);
        }
    
        //批量删除
        public void batchDelete(List<Object[]> list) {
            userDao.batchDelete(list);
        }
    
    @Override
        public void batchDelete(List<Object[]> list) {
            String sql = "delete from t_user where userId=?";
            int[] ints = jdbcTemplate.batchUpdate(sql, list);
            System.out.println(ints);
        }

[image.png]: /images/20221021/2eb00a56aa4346ee9a20618d244bcb6a.png
[image.png 1]: /images/20221021/66eb62e3064e4084be3198d2e50e0217.png
[image.png 2]: /images/20221021/2249747fb6144f5cb39280505e3c908e.png