spring | JdbcTemplate增删改查

JdbcTemplate增删改查

1.项目目录

2.Emp.java

package com.atguigu.jdbctemplate;

public class Emp {
    private Integer eid;
    private String ename;
    private Integer age;
    private String sex;
    public Integer getEid() {
        return eid;
    }
    public void setEid(Integer eid) {
        this.eid = eid;
    }
    public String getEname() {
        return ename;
    }
    public void setEname(String ename) {
        this.ename = ename;
    }
    public Integer getAge() {
        return age;
    }
    public void setAge(Integer age) {
        this.age = age;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    @Override
    public String toString() {
        return "Emp [eid=" + eid + ", ename=" + ename + ", age=" + age + ", sex=" + sex + "]";
    }
    
}

3.ssm表

4.db.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm
jdbc.username=root
jdbc.password=root

5.jdbc.xml

<?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-4.0.xsd">
    
    <!-- 引入属性文件 -->
    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location" value="db.properties"></property>
    </bean>
    
    <!-- 引入属性文件 -->
    <context:property-placeholder location="db.properties"/>
    
    <!-- 创建数据源 -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driver}"></property>
        <property name="url" value="${jdbc.url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>
    
    <!-- 通过数据源配置JdbcTemplate -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    
</beans>

6.TestJdbcTemplate.java

package com.atguigu.jdbctemplate;

import static org.junit.Assert.*;

import java.util.ArrayList;
import java.util.List;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class TestJdbcTemplate {
    ApplicationContext ac = new ClassPathXmlApplicationContext("jdbc.xml");
    JdbcTemplate jdbcTemplate = ac.getBean("jdbcTemplate", JdbcTemplate.class);
    
    @Test
    public void test() {
        //单个增删改
        //System.out.println(jdbcTemplate);//org.springframework.jdbc.core.JdbcTemplate@6483f5ae
        //jdbcTemplate.update("insert into emp values(1,'zhangsan',23,'男')");
        
        //增加记录
        //String sql = "insert into emp values(2, ?, ?, ?)";
        //jdbcTemplate.update(sql, "lisi", 25, "男");
        
        //删除记录
        //String sql = "delete from emp where eid=?";
        //jdbcTemplate.update(sql,2);
        
        //修改记录
        //String sql = "update emp set ename=? where eid=?"; 
        //jdbcTemplate.update(sql, "lisi", 1);
        
        //不能使用
        //String sql = "delete from emp where eid in (?)";
        //String eids = "3, 4, 5";
        //jdbcTemplate.update(sql,eids);
        
        //可以使用
        //String eids = "3,4,5";
        //String sql = "delete from emp where eid in ("+eids+")";
        //jdbcTemplate.update(sql);
        
        //String sql = "select * from emp where ename like '%?%'";//不能使用
        //String sql = "select * from emp where ename like concat('%',?,'%')";//可以使用
    }
    
    @Test
    public void testBatchUpdate(){
        //批量增删改
//        String sql = "insert into emp values(?,?,?,?)";
//        List<Object[]> list = new ArrayList<>();
//        list.add(new Object[]{3,"a1",1,"男"});
//        list.add(new Object[]{4,"a2",2,"男"});
//        list.add(new Object[]{5,"a3",3,"男"});
//        jdbcTemplate.batchUpdate(sql, list);
    }
    
    @Test
    public void testQueryForObject(){
        //jdbcTemplate.queryForObject(sql, requiredType);//用来获取单个的值
        //jdbcTemplate.queryForObject(sql, rowMapper);//用来获取单条数据
        
//        String sql= "select eid,ename,age,sex from emp where eid=?";
//        RowMapper<Emp> rowMapper = new BeanPropertyRowMapper<>(Emp.class);//将列名（字段名或字段名的别名）与属性名进行映射
//        Emp emp = jdbcTemplate.queryForObject(sql, new Object[]{3}, rowMapper);
//        System.out.println(emp);
        
        String sql = "select count(*) from emp";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        System.out.println(count);
    }
    
    @Test
    public void testQuery(){
        String sql = "select eid,ename,age,sex from emp";
        RowMapper<Emp> rowMapper = new BeanPropertyRowMapper<>(Emp.class);
        List<Emp> list = jdbcTemplate.query(sql, rowMapper);
        for(Emp emp:list){
            System.out.println(emp);
        }
    }

}