投稿
  1. 首页
  2. 随笔

spring整合jdbc

京卫大药房•2022-12-18•随笔•阅读11

spring整合jdbc,第1张

spring整合jdbc 准备工作及测试数据  1.准备工作  1.pom.xml文件导入依赖包 


    
    
      mysql
      mysql-connector-java
      8.0.23
    

    
    
      com.mchange
      c3p0
      0.9.5.2
    

    
    
      org.springframework
      spring-jdbc
      5.3.15
    

    
      org.springframework
      spring-context
      5.3.15
    
    
      org.springframework
      spring-aspects
      5.3.15
    

    
      junit
      junit
      4.11
      test
 2.配置文件spring.xml
3.测试是否能够连接数据库 
package com.lr.spring;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;
import java.beans.PropertyVetoException;

public class AppTest {
    @Test
    public void test01() throws PropertyVetoException {
        //测试是否能够连接数据库
        ComboPooledDataSource dataSource=new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimezone=UTC");
        dataSource.setUser("root");
        dataSource.setPassword("1380341");
        System.out.println(dataSource);
    }
}

 

4.测试 JdbcTemplate方法

JdbcTemplate是Spring对JDBC的封装，目的是使JDBC更加易于使用。

JdbcTemplate是Spring的一部分。

JdbcTemplate处理了资源的建立和释放。我们只需要提供SQL语句和提取结果。

//JdbcTemplate是Spring对JDBC的封装，目的是使JDBC更加易于使用。JdbcTemplate是Spring的一部分。
        // JdbcTemplate处理了资源的建立和释放。我们只需要提供SQL语句和提取结果。
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "insert into team(teamName,location) values(?,?)";
        int num = jdbcTemplate.update(sql,"火箭","休斯顿");
        System.out.println(num);

 

 插入数据成功，使用spring中的JdbcTemplate()直接 *** 作数据库

 使用ioc注入的方式整合jdbc

 第一步：创建team实体类（实际上应该创个bean包） 
package com.lr.spring;

import java.io.Serializable;
import java.util.Date;

public class Team implements Serializable {
    private Integer teamId;
    private String teamName;
    private String location;
    private Date createTime;

    public Team() {
    }

    public Integer getTeamId() {
        return teamId;
    }

    public void setTeamId(Integer teamId) {
        this.teamId = teamId;
    }

    public String getTeamName() {
        return teamName;
    }

    public void setTeamName(String teamName) {
        this.teamName = teamName;
    }

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    @Override
    public String toString() {
        return "Team{" +
                "teamid=" + teamId +
                ", teamName='" + teamName + ''' +
                ", location='" + location + ''' +
                ", createTime=" + createTime +
                '}';
    }
}
 第二步：创建dao包写接口及实现类 TeamDao接口 
package com.lr.spring.dao;

import com.lr.spring.Team;

public interface TeamDao {
    int insertTeam(Team team);
    int updateTeam(Team team);
    int deleteTeam(Team team);
}
 TeamDaoImpl实现类 
package com.lr.spring.dao;

import com.lr.spring.Team;
import org.springframework.jdbc.core.JdbcTemplate;

public class TeamDaoImpl implements TeamDao {
    private JdbcTemplate jdbcTemplate;
    //使用set注入的方法
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public int insertTeam(Team team) {
        return 0;
    }

    @Override
    public int updateTeam(Team team) {
        return 0;
    }

    @Override
    public int deleteTeam(Team team) {
        return 0;
    }
}

配置文件 
第三步：功能实现  TeamDaoImpl实现类 
package com.lr.spring.dao;

import com.lr.spring.Team;
import org.springframework.jdbc.core.JdbcTemplate;

public class TeamDaoImpl implements TeamDao {
    private JdbcTemplate jdbcTemplate;
    //使用set注入的方法
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public int insertTeam(Team team) {
        String spl="insert into team(teamName,location) values(?,?)";

        return jdbcTemplate.update(spl,team.getTeamName(),team.getLocation());
    }

    @Override
    public int updateTeam(Team team) {
        String sql = "update team set teamName = ? ,location = ? where teamId = ?";

        return jdbcTemplate.update(sql,team.getTeamName(),team.getLocation(),team.getTeamId());
    }

    @Override
    public int deleteTeam(Team team) {
        String sql = "delete from team where teamId = ?";
        return jdbcTemplate.update(sql,team.getTeamId());
    }

    @Override
    public int getCount() {
        String sql = "select count(teamId) from team";
        return jdbcTemplate.queryForObject(sql,Integer.class);
    }
}

第四步：测试方法  1.测试新增 
 @Test
    public void test02(){
        ApplicationContext context=new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao=(TeamDao) context.getBean("teamDao");
        teamDao.insertTeam(new Team("灰熊","孟菲斯" ));
    }

插入成功 

 2.测试修改 
@Override
    public int updateTeam(Team team) {
        String sql = "update team set teamName = ? ,location = ? where teamId = ?";

        return jdbcTemplate.update(sql,team.getTeamName(),team.getLocation(),team.getTeamId());
    }

 

修改成功 

 3.测试删除 
 @Test
    public void test02(){
        ApplicationContext context=new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao=(TeamDao) context.getBean("teamDao");
        //teamDao.insertTeam(new Team("灰熊","孟菲斯" ));
        //teamDao.updateTeam(new Team(1137,"骑士","金州"));
        teamDao.deleteTeam(new Team(1137,"骑士","金州"));
    }

 

删除成功

 ps：测试使用一参，id删除

@Test
    public void test02(){
        ApplicationContext context=new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao=(TeamDao) context.getBean("teamDao");
        //teamDao.insertTeam(new Team("灰熊","孟菲斯" ));
        //teamDao.updateTeam(new Team(1137,"骑士","金州"));
        teamDao.deleteTeam(new Team(1136));
    }

 删除成功

4. 测试查询 
 @Test
    public void test02(){
        ApplicationContext context=new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao=(TeamDao) context.getBean("teamDao");
        //teamDao.insertTeam(new Team("灰熊","孟菲斯" ));
        //teamDao.updateTeam(new Team(1137,"骑士","金州"));
        //测试使用一参删除
        //teamDao.deleteTeam(new Team(1136));
        System.out.println(teamDao.getCount());
    }

 

 多个返回值类型 1.查询球队编号最大值，最小值 1.接口方法 
   //查询球队编号最大值，最小值
    Map getMap();
2.实现接口  
 //查询最大值和最先值
    @Override
    public Map getMap() {
        String sql = "select max(teamId) 球队编号最大值,min(teamId) 球队编号最小值 from team";
        return jdbcTemplate.queryForMap(sql);
    }
3.测试程序 
public void test02(){
        ApplicationContext context=new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao=(TeamDao) context.getBean("teamDao");
        Map map=teamDao.getMap();
        //map的遍历思路：map集合变成set集合遍历entrySet()方法
        //Set>Map.entrySet();
        //entry(Entry)就是map中的一个元素
        Set> set =map.entrySet();
        for (Map.Entry entry:set){
            System.out.println(entry.getKey()+"="+entry.getValue());
        }
     }
}

2.查询单个team的所有信息 1.方法接口 
//查询team信息
    Team findTeamById(int id);
2.实现接口  
//查询信息
    @Override
    public Team findTeamById(int id) {
        String sql = "select teamId,teamName,location from team where teamId = ?";
        return jdbcTemplate.queryForObject(sql,new Object[]{id} ,new RowMapper() {
            @Override
            public Team mapRow(ResultSet rs, int i) throws SQLException {
                Team team=new Team();
                team.setTeamId(rs.getInt("teamId"));
                team.setTeamName(rs.getString("teamname"));
                team.setLocation(rs.getString("location"));
                return team;
            }
        });
    }
3.测试程序 
@Test
    public void test02(){
        ApplicationContext context=new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao=(TeamDao) context.getBean("teamDao");
        //teamDao.insertTeam(new Team("灰熊","孟菲斯" ));
        //teamDao.updateTeam(new Team(1137,"骑士","金州"));
        //测试使用一参删除
        //teamDao.deleteTeam(new Team(1136));
//        System.out.println(teamDao.getCount());
        Map map=teamDao.getMap();
        //map的遍历思路：map集合变成set集合遍历entrySet()方法
        //Set>Map.entrySet();
        //entry(Entry)就是map中的一个元素
        Set> set =map.entrySet();
        for (Map.Entry entry:set){
            System.out.println(entry.getKey()+"="+entry.getValue());
        }

        Team team= null;
        try {
            team = teamDao.findTeamById(1130);
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println(team);
    }
}
  返回多条数据 查询所有team的信息 方法接口 
    //返回一组数据
    
    List findTeams();
实现接口 
//查询所有对象的信息，返回一组数据
    @Override
    public List findTeams() {
        String sql="select teamId,teamName,location from team";
        return jdbcTemplate.query(sql, new RowMapper() {
            @Override
            public Team mapRow(ResultSet rs, int i) throws SQLException {
                Team team = new Team();
                team.setTeamId(rs.getInt("teamId"));
                team.setTeamName(rs.getString("teamName"));
                team.setLocation(rs.getString("location"));
                return team;
            }
        });
    }
测试程序  
  @Test
    public void test02(){
        ApplicationContext context=new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao=(TeamDao) context.getBean("teamDao");
        Map map=teamDao.getMap();
        List list = teamDao.findTeams();
        for (Team team:list){
            System.out.println(team);
        }

    }

 

 

 

   

欢迎分享，转载请注明来源：内存溢出

原文地址:https://www.54852.com/zaji/5707403.html

赞 (0)
打赏 微信扫一扫微信扫一扫 支付宝扫一扫支付宝扫一扫
京卫大药房京卫大药房一级用户组
00
上一篇 2022-12-17
下一篇2022-12-17

发表评论

登录后才能评论

评论列表（0条）

    保存