编程技术-Spring整合JDBC

1、引入依赖

 <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>
        <!--  测试依赖   -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!--核心依赖-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.13.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.2.13.RELEASE</version>
        </dependency>
<!--        mysql依赖-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.29</version>
        </dependency>
<!--        数据源依赖-->
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</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>

2、测试连接

连接数据库并且操作的步骤如下 ，连接对应的数据库，前提是本机中存在mysql并且运行以及创建对应的数据库。

然后将四大参数放入，在DriverClass参数中mysql8以上才会由中间的.cj.，8以下没有。url中数据库问号后的内容为字符集的相关设置。

然后通过JdbcTemplate可以对数据库进行相关的操作

 @Test
    public void test01( ) throws PropertyVetoException {
        // 创建数据库
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/springJDBC?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false");
        dataSource.setUser("root");
        dataSource.setPassword("123456");
        // 使用
        JdbcTemplate template = new JdbcTemplate(dataSource);
        String sql = "INSERT INTO team (tname , location) VALUES (?, ?)";
        int update = template.update(sql, "AI2", "郑州2");
        System.out.println("插入结果: " + update);
    }

 3、spring管理JdbcTemplate

spring整合jdbc可以让dao层继承Spring提供的JdbcDaoSupport类，该类中提供了jdbcTemplate模板可以用来使用。

handlResult函数：是因为在查找的操作中，由重复性的操作，单独拿出来进行封装，用来简化代码。

非查找语句的执行中，都是通过调用JdbcTemplate中的update函数，第一个参数为sql语句，后面跟不定量的参数用来填补sql语句中占位符的位置。

查找语句：
        返回数据只有一行的时候使用qyeryForObject函数，第一个位置为sql语句，第二个Object数组内容为参数，用来填补占位符，第三个位置为RowMapper接口用来处理返回的每一行数据，处理结果为需要的数据类型。
        返回数据有多行的情况使用query函数，参数类型同上，区别就是query函数的返回值类型为list数组。
        返回数据只有一列的情况，第二个参数可以直接用对应类型的类。
        返回数据只有一行的情况，并且不是一个类等，可以用Map来存取返回值，使用qyeryForMay，第一个位置为sql语句，第二个Object数组内容为参数，用来填补占位符。

public class TeamDao extends JdbcDaoSupport {
    public Team handlResult(ResultSet resultSet) throws SQLException {
        Team team = new Team();
        team.settId(resultSet.getInt("tid"));
        team.setLocation(resultSet.getString("location"));
        team.settName(resultSet.getString("tname"));
        return team;
    }
    public int insert(Team team) {
        String sql = "insert team (tname, location) values (?, ?)";
        int update = this.getJdbcTemplate().update(sql, team.gettName(), team.getLocation());
        return update;
    }
    public int update(Team team) {
        String sql = "update team set tname=?, location=? where tid=?";
        return this.getJdbcTemplate().update(sql, team.gettName(), team.getLocation(), team.gettId());
    }
    public int del(int id) {
        String sql = "delete from team where tid=?";
        return this.getJdbcTemplate().update(sql, id);
    }
    public Team getTeamById(int id) {
        String sql = "select * from team where tid=?";
        Team team = (Team) this.getJdbcTemplate().queryForObject(sql, new Object[] {id}, new RowMapper<Object>() {
            @Override
            public Object mapRow(ResultSet resultSet, int i) throws SQLException {

                return handlResult(resultSet);
            }
        });
        return team;
    }
    public List<Team> getTeamAll() {
        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 handlResult(resultSet);
            }
        });
        return list;
    }
    public int getCount() {
        String sql = "select count(*) from team";
        // 如果查询的列只有唯一一列,queryForObject (sql语句,为一列的数据类型)
        return this.getJdbcTemplate().queryForObject(sql,  Integer.class);
    }
    public Map<String, Object> getMany() {
        String sql = "select max(tid) as max, min(tid) as min from team";
        // 如果查询的列只有唯一一列,queryForObject (sql语句,为一列的数据类型)
        return this.getJdbcTemplate().queryForMap(sql);
    }
}

spring的配置文件application.xml中需要创建数据源和给TeamDao中的jdbcTemplate赋值

<?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
">
    <!-- 创建数据源 -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
        <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/springJDBC?serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8&amp;useUnicode=true&amp;useSSL=false"/>
        <property name="user" value="root"/>
        <property name="password" value="123456"/>
    </bean>
    
    <!-- 创建jdbcTemplate对象，给类中dataSource赋值 -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!-- 创建teamDao对象，给类中jdbcTemplate赋值 -->
    <bean id="teamDao" class="com.AE.dao.TeamDao">
        <property name="jdbcTemplate" ref="jdbcTemplate"/>
    </bean>
</beans>

4、测试

public class test01 {
//    private TeamDao teamDao;
    @Test
    public void test01( ) throws PropertyVetoException {
        // 创建数据库
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/springJDBC?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useUnicode=true&useSSL=false");
        dataSource.setUser("root");
        dataSource.setPassword("123456");
        // 使用
        JdbcTemplate template = new JdbcTemplate(dataSource);
        String sql = "INSERT INTO team (tname , location) VALUES (?, ?)";
        int update = template.update(sql, "AI2", "郑州2");
        System.out.println("插入结果: " + update);
    }
    @Test
    public void test02(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao = (TeamDao) ac.getBean("teamDao");
        Team team = new Team();
        team.setLocation("南阳");
        team.settName("张淏");
        int insert = teamDao.insert(team);
        System.out.println(insert);
    }
    @Test
    public void test03(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao = (TeamDao) ac.getBean("teamDao");
        Team team = new Team();
        team.settId(5);
        team.setLocation("郑州3");
        team.settName("AI3");
        int update = teamDao.update(team);
        System.out.println(update);
    }
    @Test
    public void test04(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao = (TeamDao) ac.getBean("teamDao");
        int update = teamDao.del(5);
        System.out.println(update);
    }
    @Test
    public void test05(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao = (TeamDao) ac.getBean("teamDao");
        Team team = teamDao.getTeamById(2);
        System.out.println(team);
    }
    @Test
    public void test06(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao = (TeamDao) ac.getBean("teamDao");
        List<Team> list = teamDao.getTeamAll();
        for(Team team : list) {
            System.out.println(team);
        }
        System.out.println(list);
    }
    @Test
    public void test07(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao = (TeamDao) ac.getBean("teamDao");
        int a = teamDao.getCount();
        System.out.println(a);
    }
    @Test
    public void test08(){
        ApplicationContext ac = new ClassPathXmlApplicationContext("spring.xml");
        TeamDao teamDao = (TeamDao) ac.getBean("teamDao");
        Map<String, Object> many = teamDao.getMany();
        for(String a : many.keySet()) {
            System.out.println(a + "=" + many.get(a));
        }
    }
}