SSM——Spring——Spring整合JDBC——day05

最新推荐文章于 2024-09-12 18:30:12 发布

winterflower_

最新推荐文章于 2024-09-12 18:30:12 发布

阅读量109

点赞数

分类专栏： Spring学习文章标签： spring java

本文链接：https://blog.csdn.net/winterflower_/article/details/120571756

版权

Spring学习专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1、使用spring-jdbc操作数据库

1）创建一个项目spring04_jdbc

2）配置pom.xml文件

<dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <!--spring 核心依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.13.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>5.2.13.RELEASE</version>
        </dependency>

        <!--Spring连接jdbc的依赖包-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.13.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>

        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.5</version>
        </dependency>

    </dependencies>

    <build>
        <plugins> <!--编译插件-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.0</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

3) 编写一个测试类,来验证spring是否连接的上我们的mysql

 @Test
    public void test01() throws PropertyVetoException {
        //1.创建c3p0数据源
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1/spring_jdbc? serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
        dataSource.setUser("root");
        dataSource.setPassword("zhangjing1234");

        //2、使用JDBC template
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "insert into team(tname) value(?)";
        int num = jdbcTemplate.update(sql, "勇士队");
        System.out.println(num);

    }

2、改进Spring整合jdbc

1）创建一个实体类Team

public class Team {

    private Integer id;
    private String name;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

}

2）创建一个dao类：TeamDao

import com.zj.pojo.Team;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

//继承JdbcDaoSupport的目的是去直接使用JdbcTemplate，而不是去new Template的对象
public class TeamDao extends JdbcDaoSupport {
    public int insert(Team team){
        String sql = "insert into team(tname) value(?)";
        return this.getJdbcTemplate().update(sql,team.getName());
    }
}

3）编写配置文件application.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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">


    <!--创建 JdbcTemplate 的数据源-->
    <bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1/spring_jdbc? serverTimezone=UTC&amp;characterEncoding=utf8&amp;useUnicode=true&amp;useSSL=false"></property>
        <property name="user" value="root"></property>
        <property name="password" value="zhangjing1234"></property>
    </bean>

    <!--创建 JdbcTemplate 模板，并将我们创建的数据源放入其中-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>


    <bean id="teamDao" class="com.zj.dao.TeamDao">
        <!--向teamDao中赋值,teamDao中需要的就是一个JdbcTemplate-->
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>
</beans>

4）编写测试类

    @Test
    public void test02(){
        Team team = new Team();
        team.setName("快船队");
        //不要自己new对象，我们用Spring提供的方法获取对象
        //TeamDao teamDao = new TeamDao();
        ApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        TeamDao teamDao = (TeamDao) context.getBean("teamDao");
        int insert = teamDao.insert(team);
        System.out.println(insert);
    }

2、Spring整合 jdbc 的增删改操作

1)增加操作：

dao:

 public int insert(Team team){
        String sql = "insert into team(tname) value(?)";
        return this.getJdbcTemplate().update(sql,team.getName());
    }

测试类：

 @Test
    public void testInsert(){
        Team team = new Team();
        team.setName("快船队");
        //不要自己new对象，我们用Spring提供的方法获取对象
        //TeamDao teamDao = new TeamDao();
        ApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        TeamDao teamDao = (TeamDao) context.getBean("teamDao");
        int insert = teamDao.insert(team);
        System.out.println(insert);
    }

2）修改操作

dao

    public int update(Team team){
        String sql = "update team set tname=? where tid=?";
        return this.getJdbcTemplate().update(sql,team.getName(),team.getId());
    }

测试类

 @Test
    public void testUpdate(){
        Team team = new Team();
        team.setName("小牛");
        team.setId(2);
        ApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        TeamDao teamDao = (TeamDao) context.getBean("teamDao");
        int insert = teamDao.update(team);
        System.out.println(insert);
    }

3）删除操作

dao

  public int delete(int id){
        String sql = "delete from  team where tid=?";
        return this.getJdbcTemplate().update(sql,id);
    }

测试类

 @Test
    public void testDelete(){
        ApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        TeamDao teamDao = (TeamDao) context.getBean("teamDao");
        int insert = teamDao.delete(2);
        System.out.println(insert);
    }

3、Spring整合 JDBC 查询操作

1）查询单条数据

dao

 public Team findById(int id){
        String sql = "select * from team where tid= ?";
        //执行一行数据的查询使用：queryForObject方法，将结果集映射到对应到对应得实体类
        Team team = this.getJdbcTemplate().queryForObject(sql, new Object[]{id}, new RowMapper<Team>() {

            //得到结果之后，我们要去实现一行一行结果的映射
            @Override
            public Team mapRow(ResultSet resultSet, int i) throws SQLException {
                Team t = new Team();
                t.setId(resultSet.getInt("tid"));
                t.setName(resultSet.getString("tname"));
                return t;
            }
        });
        return team;
    }

测试类

 @Test
    public void testFindById(){
        ApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        TeamDao teamDao = (TeamDao) context.getBean("teamDao");
        Team team = teamDao.findById(1);
        System.out.println(team);
    }

2）全查

dao

/**
     * 用来处理结果集的方法
     * @param resultSet
     * @return
     * @throws SQLException
     */
    public Team HandlerResult(ResultSet resultSet) throws SQLException {
        Team team = new Team();
        team.setId(resultSet.getInt("tid"));
        team.setName(resultSet.getString("tname"));
        return team;
    }

    public List<Team> findAll(){
        String sql = "select * from team";
        List<Team> list = this.getJdbcTemplate().query(sql, new RowMapper<Team>() {

            @Override
            public Team mapRow(ResultSet resultSet, int i) throws SQLException {
                return HandlerResult(resultSet);
            }
        });
        return list;
    }

测试类

 @Test
    public void testFindAll(){
        ApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        TeamDao teamDao = (TeamDao) context.getBean("teamDao");
        List<Team> list = teamDao.findAll();
        for (Team team : list) {
            System.out.println(team);
        }
    }

3）查询数据库得总行数

dao

  public int getCount(){
        String sql = "select count(tid) from team";
        return this.getJdbcTemplate().queryForObject(sql,Integer.class);
    }

测试类

 @Test
    public void testGetCount(){
        ApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        TeamDao teamDao = (TeamDao) context.getBean("teamDao");
        int count = teamDao.getCount();
        System.out.println(count);
    }

4）查询多个值

dao

 public Map<String,Object> getMany(){
        String sql = "select max(tid),min(tid) from team";
        return this.getJdbcTemplate().queryForMap(sql);
    }

测试类

@Test
    public void testGetMany(){
        ApplicationContext context = new ClassPathXmlApplicationContext("application.xml");
        TeamDao teamDao = (TeamDao) context.getBean("teamDao");
        Map<String, Object> many = teamDao.getMany();
        Set<Map.Entry<String, Object>> set = many.entrySet();
        for (Map.Entry<String, Object> entry : set) {
            System.out.println(entry.getKey()+","+entry.getValue());
        }
    }