07 JdbcTemplate

1、相关配置

1.1、pom.xml文件

?
1
2
3
4
5
6
7
8
9
10
11
12
13
<!-- 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

?
1
2
3
4
5
6
7
8
9
10
11
12
<!-- 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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
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、测试类

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
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("执行结束");
    }
}

　　


欢迎阅读，有错误请留言指正。
posted @   草莓爸  阅读(20)  评论(0)  编辑  收藏  举报
相关博文：
·  myBatis-03 主配置文件
·  myBatis-01 基本使用
·  Spring 使用 JdbcTemplate 操作数据库
·  Spring基础--ioc,AOP,事务等
·  spring下的jdbcTemplate对数据库的操作
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
点击右上角即可分享
微信分享提示