Spring--JDBCTemplate常用方法使用

爱被打了一巴掌 2021-08-13 23:45 485阅读 0赞

#### JDBCTemplate ####

*  **execute(String sql)**：能够完成执行SQL语句的功能。
 *  **update()**： 可以完成插入、更新和删除操作，有多个重载方法
    
     *  int update(String sql):该方法是最简单的update形式，可以直接传入SQL语句并返回受影响的行数
     *  int update(preparedStatementCreator psc)：该方法 执行从preparedStatementCreator返回的语句，然后返回受影响的行数。
     *  int update(String sql, PreparedStatementSetter pass)：该方法通过PreparedStatementSetter设置SQL语句中的参数，并返回受影响的行数。
     *  int update(String sql, Object… args)：该方法使用Object数组设置SQL语句的参数，要求参数不能为空，并返回受影响的行数。
 *  **query()**：查询操作
    
     *  List query(String sql, PreparedStatementSetter pss, RowMapper rowMapper)：该方法根据String类型的参数提供的SQL语句创建PreparedStatement对象，通过RowMapper将结果返回到List中。
     *  List query(String sql, Object\[\] args, RowMapper rowMapper)：该方法使用Object\[\] 的值来设置SQL中的参数值，采用RowMapper回调方法可以直接返回List类型的数据。
     *  queryForObject(String sql, Object\[\] args, RowMapper rowMapper)：该方法将args参数绑定到SQL语句中，通过RowMapper返回单行记录，并转换为一个Object对象返回
     *  queryForList(String sql,Object\[\] args, classelementType)：该方法可以返回多行数据的结果，但必须是返回列表，elementType参数返回的是List元素类型。

#### 实例 ####

**创建Maven工程**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]

**导入依赖**  
pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>pers.zhang</groupId>
        <artifactId>SpringJDBCDemo</artifactId>
        <version>1.0-SNAPSHOT</version>
    
    
        <properties>
            <org.springframework.version>5.0.8.RELEASE</org.springframework.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-core</artifactId>
                <version>${org.springframework.version}</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-beans</artifactId>
                <version>${org.springframework.version}</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>${org.springframework.version}</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-aop</artifactId>
                <version>${org.springframework.version}</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>${org.springframework.version}</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-tx</artifactId>
                <version>${org.springframework.version}</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-test</artifactId>
                <version>${org.springframework.version}</version>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-expression</artifactId>
                <version>${org.springframework.version}</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.25</version>
            </dependency>
            <dependency>
                <groupId>commons-logging</groupId>
                <artifactId>commons-logging-api</artifactId>
                <version>1.1</version>
            </dependency>
            <dependency>
                <groupId>com.mchange</groupId>
                <artifactId>c3p0</artifactId>
                <version>0.9.5.2</version>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
                <scope>test</scope>
            </dependency>
        </dependencies>
    </project>

**创建实体类User：**

package pers.zhang.entity;
    
    /** * @author zhang * @date 2019/11/23 - 23:05 */
    public class User { 
        private int id;
        private String userName;
        private String password;
    
        public int getId() { 
            return id;
        }
    
        public void setId(int id) { 
            this.id = id;
        }
    
        public String getUserName() { 
            return userName;
        }
    
        public void setUserName(String userName) { 
            this.userName = userName;
        }
    
        public String getPassword() { 
            return password;
        }
    
        public void setPassword(String password) { 
            this.password = password;
        }
    
        @Override
        public String toString() { 
            return "User{" +
                    "id=" + id +
                    ", userName='" + userName + '\'' +
                    ", password='" + password + '\'' +
                    '}';
        }
    }

**创建UserDao和UserDaoImpl：**

package pers.zhang.dao;
    
    import pers.zhang.entity.User;
    
    import java.util.List;
    
    /** * @author zhang * @date 2019/11/23 - 23:06 */
    public interface UserDao { 
        //创建表
        public void createTable();
        //添加用户
        public int addUser(User user);
        //修改用户
        public int updateUser(User user);
        //删除用户
        public int deleteUser(int id);
        //根据id查询指定用户
        public User findUserById(int id);
        //查询所有用户
        public List<User> findAllUser();
    }

package pers.zhang.dao.impl;
    
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.jdbc.core.support.JdbcDaoSupport;
    import pers.zhang.dao.UserDao;
    import pers.zhang.entity.User;
    
    import java.util.List;
    
    /** * @author zhang * @date 2019/11/23 - 23:08 */
    public class UserDaoImpl extends JdbcDaoSupport implements UserDao{ 
    
        @Override
        public void createTable(){ 
            String sql = "create table user(id int primary key auto_increment,userName varchar(20),password varchar(32))";
            super.getJdbcTemplate().execute(sql);
            System.out.println("用户表user创建成功！");
        }
    
        @Override
        public int addUser(User user) { 
            String sql = "insert into user(userName,password) values(?,?)";
            //使用数组存储SQL语句中的参数
            Object[] objects = new Object[]{ user.getUserName(), user.getPassword()};
            int result = super.getJdbcTemplate().update(sql, objects);
            return result;
        }
    
        @Override
        public int updateUser(User user) { 
            String sql = "update user set username=?,password=? where id=?";
            Object[] objects = new Object[]{ user.getUserName(), user.getPassword(),user.getId()};
            int result = super.getJdbcTemplate().update(sql, objects);
            return result;
        }
    
        @Override
        public int deleteUser(int id) { 
            String sql = "delete from user where id=?";
            int result = super.getJdbcTemplate().update(sql, id);
            return result;
        }
    
        @Override
        public User findUserById(int id) { 
            String sql = "select * from user where id=?";
            //创建一个新的BeanPropertyRowMapper对象，将结果集通过Java的反射机制映射到Java对象中
            RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
            return (User) super.getJdbcTemplate().queryForObject(sql, rowMapper, id);
        }
    
        @Override
        public List<User> findAllUser() { 
            String sql = "select * from user";
            RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class);
            return super.getJdbcTemplate().query(sql, rowMapper);
        }
    }

**配置Spring依赖：**

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd ">
    
    
        
        <context:property-placeholder location="classpath:jdbc.properties"/>
    
        
        <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>
            <property name="driverClass" value="${jdbc.driverClass}"></property>
            <property name="user" value="${jdbc.user}"></property>
            <property name="password" value="${jdbc.password}"></property>
        </bean>
    
        
    
    
    
    
        
        <bean name="userDao" class="pers.zhang.dao.impl.UserDaoImpl">
            <property name="dataSource" ref="dataSource"></property>
        </bean>
    </beans>

**测试execute()：**

@Test
        public void createTableTest(){ 
            //初始化spring容器，加载applicationContext.xml
            ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
            UserDao userDao = (UserDao) ctx.getBean("userDao");
            userDao.createTable();
        }

![在这里插入图片描述][20191124093453211.png]  
**测试update()**

@Test
    public void addUserTest(){ 
        //初始化容器
        ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = (UserDao) ctx.getBean("userDao");
        User user = new User();
        user.setUserName("yzpc");
        user.setPassword("yzpc");
        int i = userDao.addUser(user);
        System.out.println(i);
    }

![在这里插入图片描述][20191124093549690.png]

@Test
    public void updateUserTest(){ 
        ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = (UserDao) ctx.getBean("userDao");
        User user = new User();
        user.setId(1);
        user.setUserName("zhangsan");
        user.setPassword("123456");
        int i = userDao.updateUser(user);
        if(i > 0){ 
            System.out.println("成功修改了" + i + "条数据！");
        }else { 
            System.out.println("修改操作执行失败！");
        }
    }

![在这里插入图片描述][20191124093624700.png]

@Test
    public void deleteUserTest(){ 
        ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = (UserDao) ctx.getBean("userDao");
        int i = userDao.deleteUser(1);
        if(i > 0){ 
            System.out.println("成功删除了" + i + "条数据！");
        }else { 
            System.out.println("删除操作执行失败！");
        }
    }

![在这里插入图片描述][20191124093652312.png]

**测试query()：**  
准备数据：  
![在这里插入图片描述][2019112409375270.png]

@Test
    public void findUserByIdTest(){ 
        ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = (UserDao) ctx.getBean("userDao");
        User user = userDao.findUserById(3);
        System.out.println(user);
    }

User{ id=3, userName='c', password='cc'}

@Test
    public void findAllUserTest(){ 
        ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserDao userDao = (UserDao) ctx.getBean("userDao");
        List<User> users = userDao.findAllUser();
        for (User u: users) { 
            System.out.println(u);
        }
    }

User{ id=1, userName='a', password='aa'}
    User{ id=2, userName='b', password='bb'}
    User{ id=3, userName='c', password='cc'}
    User{ id=4, userName='d', password='dd'}
    User{ id=5, userName='e', password='ee'}

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/12ab6dd02bc349f5846e9b64f1274017.png
[20191124093453211.png]: /images/20210813/2e20f62f5fb047ca8e7dcde10c56d907.png
[20191124093549690.png]: /images/20210813/d94b2e1c7c834a378e60e933ce749d96.png
[20191124093624700.png]: /images/20210813/484b997fb8ea4d78997afe2d812cacbf.png
[20191124093652312.png]: /images/20210813/860f7198f80748af8c331e26b451646c.png
[2019112409375270.png]: /images/20210813/a1ecef176e5947b7983bd60bc26e9917.png