Spring JdbcTemplate操作数据库

Pom中引入maven包

<dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>5.3.12</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>5.3.12</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.19</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.17</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.3.11.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.3.12</version>
        </dependency>
    </dependencies>

在xml中注入dataSource与jdbcTemplate

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
            destroy-method="close">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/userdb?serverTimezone=UTC&amp;
                  useUnicode=true&amp;characterEncoding=UTF-8&amp;useSSL=false"/>
        <property name="username" value="root"/>
        <property name="password" value="1234"/>
    </bean>


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

使用注解开发，开启注解扫描支持

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

创建Bean

@Repository
public class User {
    private String name;
    private int age;

    public User() {
    }

    public User(String name, int age) {
        this.name = name;
        this.age = age;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" +
                "name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}


@Service
public class UserService {

    @Autowired
    private UserDao userDao;

    //添加数据
    public void addUser(User user){
        int i=userDao.addUser(user);
        System.out.println(i);
    }

    //修改数据
    public void updateUser(User user){
     int i=userDao.updateUser(user);
        System.out.println(i);
    }

    //查询数据
    public void selectUser(String name){
        User user=userDao.selectUser(name);
        System.out.println(user);
    }
    //查询有多少条数据
    public void selectUserCount(){
        int i=userDao.selectUserCount();
        System.out.println(i);
    }
    //查询数据集合
    public void selectUserAll(){
        List<User> user=userDao.selectUserAll();
        for (User user1 : user) {
            System.out.println(user1);
        }
    }

    //删除数据
    public void deleteUser(User user){
        int i=userDao.deleteUser(user);
        System.out.println(i);
    }

}


@Repository
public interface UserDao {
    int addUser(User user);

    int updateUser(User user);


    User selectUser(String name);

    int deleteUser(User user);

    int selectUserCount();

    List<User> selectUserAll();
}


@Repository
public class UserDaoImpl implements UserDao{

    @Autowired
    private JdbcTemplate jdbcTemplate;


    public int addUser(User user) {
        int i=jdbcTemplate.update("insert into student values(?,?)",user.getName(),user.getAge());
        return i;
    }

    public int updateUser(User user) {
        Object[] ages = {user.getAge(),user.getName()};
        int i=jdbcTemplate.update("update student set  age=? where name=?",ages);
        return i;
    }

    public User selectUser(String name) {
        User user=jdbcTemplate.queryForObject("select * from student where name=?",new BeanPropertyRowMapper<User>(User.class),name);
        return user;
    }

    public int deleteUser(User user) {
        int i=jdbcTemplate.update("delete from student where name=?",user.getName());
        return i;
    }


//填写的sql语句与返回的类型
    public int selectUserCount() {
        int i=jdbcTemplate.queryForObject("select Count(*) from student",Integer.class);
        return i;
    }

//填写的sql语句与返回的类型，BeanPropertyRowMapper将类型解析并返回
    public List<User> selectUserAll() {
        List<User> userList = jdbcTemplate.query("select * from student", new BeanPropertyRowMapper<User>(User.class));
        return userList;
    }
}

实现类

public class UserPojo {
    ApplicationContext context=new ClassPathXmlApplicationContext("bean.xml");
    UserService userservice = context.getBean("userService", UserService.class);
    @Test
    public void addUser(){
        userservice.addUser(new User("heiheihei",22));
    }

    @Test
    public void updateUser(){
        userservice.updateUser(new User("heiheihei",18));
    }

    @Test
    public void selectUser(){
        userservice.selectUser("hua");
    }

    @Test
    public void selectUserCount(){
        userservice.selectUserCount();
    }


    @Test
    public void selectUserAll(){
        userservice.selectUserAll();
    }


    @Test
    public void deleteUser(){
        userservice.deleteUser(new User("heiheihei",22));
    }
}