spring-JdbcTemplate

JdbcTemplate

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

配置如下

1.新建一个maven工程
2.一个User实体类、字段为userNo、userName、userSex、userAge,get 、set方法
3.一个测试类
4.一个User表,字段 userName、userSex、userAge、userNo
5.ApplicationContext配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd">

<!--    数据源-->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://192.168.x.x:3306/school?characterEncoding=utf8&amp;useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value="xxR)xx97aod_oJxx!"/>
    </bean>

<!--    引用数据源-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
</beans>

实操代码

新增数据：?代表具体占位符，updata后的参数是sql里面的值，根据顺序排列

BeanFactory factory = new ClassPathXmlApplicationContext("ApplicationContext.xml");
        JdbcTemplate jdbcTemplate = (JdbcTemplate) factory.getBean("jdbcTemplate");

        String sql = "INSERT INTO `school`.`User`(`userNo`, `userName`, `userAge`, `userSex`) VALUES (?, ?, ?, ?);";
        int count = jdbcTemplate.update(sql,2005,"两三",211,"男");
        System.out.println(count);//表示被修改的数据的条数，因为新增一条结果为0

删除数据：

        String sqldelete = "delete from `school`.`User` where userNo = ?";
        int countdelete = jdbcTemplate.update(sqldelete,2004);
        System.out.println(countdelete);//同新增，如果删除的id不存在则条数为0

更新数据：

        String sqlUpdate = "update `school`.`User` set userName=?,userSex=?,userAge=where userNo=?;";
        int countUpdate = jdbcTemplate.update(sqlUpdate,"八两","女",21,2005);
        System.out.println(countUpdate);

查询数据：

queryForObject()只能查询一列

       String sqlselectOne = "select userName from `school`.`User` where userNo = ?";
        String userName = jdbcTemplate.queryForObject(sqlselectOne,String.class,2005);
        System.out.println(userName);

queryForObject()可以查询所有列，但是需要给个策略（什么值赋值到什么字段）

       //查所有字段
        String sqlselect = "select * from `school`.`User` where userNo = ?";
        //new RowMapper<User>()实现这个接口中的方法，给一个策略
        User user =jdbcTemplate.queryForObject(sqlselect, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setUserNo(rs.getInt("userNo"));
                user.setUserAge(rs.getInt("userAge"));
                user.setUserName(rs.getString("userName"));
                user.setUserSex(rs.getString("userSex"));
                return user;
            }
        }, 2005);
        System.out.println(user);

queryForList()查询多行数据

        String sqls = "select * from `school`.`User`";
        //queryForList方法存入map中，不需要给策略
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sqls);
        System.out.println(list);

query()查询多行数据

public class Celue implements RowMapper {
    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setUserNo(rs.getInt("userNo"));
        user.setUserAge(rs.getInt("userAge"));
        user.setUserName(rs.getString("userName"));
        user.setUserSex(rs.getString("userSex"));
        return user;
    }
}

        String sqls = "select * from `school`.`User`";
        //query存入实体类对象，需要给策略（什么值存入什么字段中）
        List<User> list1 = jdbcTemplate.query(sqls,new Celue());
        System.out.println(list1);