07 JdbcTemplate

1、相关配置

1.1、pom.xml文件

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.0</version>
        </dependency>

        <!--MySQL连接的依赖包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>

1.2、applicationContext.xml

    <!-- mySql 数据库链接配置 -->
    <bean id="mySqlDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    	<property name="driverClassName" value="com.mysql.cj.jdbc.Driver"></property>
    	<property name="url" value="jdbc:mysql://localhost:3306/springStudy?useSSL=false&serverTimezone=UTC"></property>
    	<property name="username" value="root"></property>
    	<property name="password" value="root"></property>
    </bean>
    
    <!-- jdbcTemplate 注入数据库链接配置 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    	<property name="dataSource" ref="mySqlDataSource"></property>
    </bean>

2、代码测试

2.1、模型类Employee.java

package cn.sjxy.Domain;

import java.sql.Date;

import org.springframework.stereotype.Component;

@Component(value = "employee")
public class Employee {
	private String empId;
	private String name;
	private boolean gender;
	private Date hireDate;
	private int salary;
	
	public String getEmpId() {
		return empId;
	}
	public void setEmpId(String empId) {
		this.empId = empId;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public boolean getGender() {
		return gender;
	}
	public void setGender(boolean gender) {
		this.gender = gender;
	}
	public Date getHireDate() {
		return hireDate;
	}
	public void setHireDate(Date hireDate) {
		this.hireDate = hireDate;
	}
	public int getSalary() {
		return salary;
	}
	public void setSalary(int salary) {
		this.salary = salary;
	}


	@Override
	public String toString() {
		return "Employee{" +
				"empId='" + empId + '\'' +
				", name='" + name + '\'' +
				", gender=" + gender +
				", hireDate=" + hireDate +
				", salary=" + salary +
				'}';
	}
}

2.2、接口类EmployeeDao

package cn.sjxy.Dao;

import java.util.List;
import java.util.Map;

import cn.sjxy.Domain.Employee;

//数据访问接口
public interface EmployeeDao {
	//新增数据
	public boolean insert(Employee emp);

	//更新数据
	public boolean update(Employee emp);

	//删除数据
	public void deleteByEmplId(String empId);

	//查询单个对象
	public Employee queryObjectByEmplId(String empId);

	//查询对个对象-Map
	public Map<String,Object> queryMapByEmplId(String empId);

	//查询多个对象
	public List<Employee> queryObjectListBySalary(int start,int end);

	//查询多个对象-Map
	public List<Map<String,Object>> queryMapListBySalary(int start,int end);

}

2.3、接口实现类EmployeeDaoImpl

package cn.sjxy.DaoImpl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

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

import cn.sjxy.Dao.EmployeeDao;
import cn.sjxy.Domain.Employee;


@Repository(value="employeeDaoImpl")
public class EmployeeDaoImpl implements EmployeeDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public boolean insert(Employee emp) {
		String sql = " insert into employee(empId,name,gender,hireDate,salary) values(?,?,?,?,?) ";
		Object[] para = {emp.getEmpId(),emp.getName(),emp.getGender(),emp.getHireDate(),emp.getSalary()};
		int result = jdbcTemplate.update(sql,para);
		if(result>0)
			return true;
		return false;
	}

	@Override
	public boolean update(Employee emp) {
		String sql = " update employee set salary=?,`name`=? where empId=? ";
		Object[] para = {emp.getSalary(),emp.getName(),emp.getEmpId()};
		int result = jdbcTemplate.update(sql,para);
		if(result>0)
			return true;
		return false;
	}

	@Override
	public void deleteByEmplId(String empId) {
		String sql = " DELETE from employee where employee.empId=? ";
		int result = jdbcTemplate.update(sql,empId);
	}

	@Override
	public Employee queryObjectByEmplId(String empId) {
		String sql = " select * from employee where empId=? ";
		Employee result = jdbcTemplate
				.queryForObject(sql, new BeanPropertyRowMapper<Employee>(Employee.class), empId);
		return result;
	}

	@Override
	public Map<String, Object> queryMapByEmplId(String empId) {
		String sql = " select * from employee where empId=? ";
		Map<String, Object> result = jdbcTemplate
				.queryForMap(sql, empId);
		return result;
	}

	@Override
	public List<Employee> queryObjectListBySalary(int start, int end) {
		String sql = " select * from employee where salary between ? and ? ";
		Object[] para = {start,end};
		List<Employee> result = jdbcTemplate
				.query(sql, para, new RowMapper<Employee>() {
					@Override
					public Employee mapRow(ResultSet resultSet, int i) throws SQLException {
						Employee e = new Employee();
						e.setEmpId(resultSet.getString(1));
						e.setName(resultSet.getString(2));
						e.setGender(resultSet.getBoolean(3));
						e.setHireDate(resultSet.getDate(4));
						e.setSalary(resultSet.getInt(5));
						return e;
					}
				});
		return result;
	}

	@Override
	public List<Map<String, Object>> queryMapListBySalary(int start, int end) {
		String sql = " select * from employee where salary between ? and ? ";
		Object[] para = {start,end};
		List<Map<String,Object>> result = jdbcTemplate
				.queryForList(sql, para);
		return result;
	}
}

2.4、测试类

package cn.sjxy.Test;

import cn.sjxy.Domain.Employee;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import cn.sjxy.DaoImpl.EmployeeDaoImpl;

import javax.xml.transform.Source;
import java.util.List;
import java.util.Map;

public class SpringTest {
    public static void main(String[] args) {
        ApplicationContext context
                = new ClassPathXmlApplicationContext("applicationContext.xml");
        EmployeeDaoImpl impl = (EmployeeDaoImpl) context.getBean("employeeDaoImpl");
        //新增
//		 {
//			 Employee emp = new Employee();
//			 emp.setEmpId("002");
//			 emp.setName("王刚");
//			 emp.setGender(false);
//			 emp.setHireDate(new Date(2022, 1, 1));
//			 emp.setSalary(20);
//			 impl.insert(emp);	 
//		 }
        //修改
//		 {
//			 Employee emp = new Employee();
//			 emp.setEmpId("001");
//			 emp.setName("曹阳");
//			 emp.setSalary(200);
//			 impl.update(emp);
//		 }
        //查询单个实体
//		{
//			Employee emp = impl.queryObjectByEmplId("002");
//			System.out.println(emp.toString());
//		}
        //查询单个实体-Map
//		{
//			Map<String,Object> result = impl.queryMapByEmplId("002");
//			for(String key:result.keySet())
//            {
//                System.out.print(key+"="+result.get(key)+",");
//            }
//            System.out.println();
//		}
        //查询多个数据-Map
//        {
//            List<Map<String, Object>> list = impl.queryMapListBySalary(10,300);
//            for(Map<String,Object> map:list)
//            {
//                for(String key:map.keySet())
//                {
//                    System.out.print(key+"="+map.get(key)+",");
//                }
//                System.out.println();
//            }
//        }
        //查询多个数据
        {
            List<Employee> list = impl.queryObjectListBySalary(10,300);
            for(Employee emp:list)
            {
                System.out.println(emp.toString());
            }
        }
        System.out.println("执行结束");
    }
}