java--使用jdbcTemplate进行增删改查

一、设置数据库连接信息 

文件名：jdbc.propries
jdbc.url=jdbc:mysql://10.0.2.4:63306/test1111111?useUnicode=true&characterEncoding=utf-8&useSSL=false
jdbc.username=gac_trav
jdbc.password=gac@6666
jdbc.driverClassName=com.mysql.cj.jdbc.Driver

 二、配置druid数据源信息，获取jdbcTemplate对象

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

<!--引入外部属性文件，创建数据源对象-->
    <context:property-placeholder location="classpath:jdbc.propries"></context:property-placeholder>
    <bean id="druidDataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="url" value="${jdbc.url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
        <property name="driverClassName" value="${jdbc.driverClassName}"></property>
    </bean>
<!--创建jdbcTemplate对象，注入数据源-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="druidDataSource"></property>
    </bean>
</beans>

三、获取jdbcTemplate对象，进行数据相关操作

package com.ruqi.jdbctemplate;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;

import java.util.List;

@SpringJUnitConfig(locations = "classpath:beans.xml")
public class JdbcTemplateTest {

    @Autowired
    private JdbcTemplate jdbcTemplate; //拿到数据连接对象后，就可以进行增删改查

    // 增删改操作
    @Test
    public void testJdbcTemplate(){
        String sql = "INSERT INTO t_emp VALUES(null,?,?,?);";
        int rows = jdbcTemplate.update(sql, "小明", 20, "男");
        System.out.println(rows);
    }

    //查询结果设置到对象中
    @Test
    public void testSelectObject(){
        String sql = "select id, name, age, sex from t_emp where id = ?";
//        写法一
        User returnUser1 = jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
            User user = new User();
            user.setAge(rs.getInt("age"));
            user.setName(rs.getString("name"));
            user.setId(rs.getInt("id"));
            user.setSex(rs.getString("sex"));
            return user;
        }, 1);
        System.out.println(returnUser1);
//        写法二，本质上跟写法一是一样的
        User returnUser2 = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), 2);
        System.out.println(returnUser2);
    }

    //返回多条数据放到一个集合
    @Test
    public void selectObjectList(){
        String sql = "select id, name, age, sex from t_emp;";
        List<User> userList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
        System.out.println(userList);
    }

    // 返回单个值，第二个参数填写返回的数据类型
    @Test
    public void selectValueObject(){
        String sql = "select count(1) from t_emp;";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(count);
    }
}

四、全注解方式

package com.ruqi.serverlettest.book;


import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

@Configuration
@ComponentScan("com.ruqi.serverlettest.book")
@EnableTransactionManagement //开启事务管理
public class SpringConfig {

    @Bean
    public DataSource getDataSource(){
        DruidDataSource dataSource1 = new DruidDataSource();
        dataSource1.setUrl("jdbc:mysql://10.0.2.4:63306/test1111111?useUnicode=true&characterEncoding=utf-8&useSSL=false");
        dataSource1.setUsername("gac_travel_dev");
        dataSource1.setPassword("gac@6666");
        dataSource1.setDriverClassName("com.mysql.cj.jdbc.Driver");
        return dataSource1;
    }

    @Bean(name = "jdbcTemplate")
    public JdbcTemplate getJdbcTemplate(DataSource dataSource){
        JdbcTemplate jdbcTemplate = new JdbcTemplate();
        jdbcTemplate.setDataSource(dataSource);
        return jdbcTemplate;
    }

    @Bean
    public DataSourceTransactionManager getDataSourceTransactionManager(DataSource dataSource){
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dataSource);
        return dataSourceTransactionManager;
    }
}


=====================使用=====================

package com.ruqi.serverlettest.book;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class BookDaoImpl implements BookDao{

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Override
    public Integer querqyPrice(Integer bookId) {
        String sql = "select price from t_book where book_id = ?";
        return jdbcTemplate.queryForObject(sql, Integer.class, bookId);
    }