Spring JDBC

Spring JDBC

简化对数据库的操作管理

---

前言

基于idea2021.03

---

一、JDBC？

SpringJDBC是Spring对JDBC的封装，可以避免使用JDBC时的一些重复代码，如获取连接、关闭连接等操作。

二、使用步骤

1.引入库

导入依赖：

    <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.0.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.2.0.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.20</version>
    </dependency>
	<!--  连接方式-->
    <dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.5.2</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>5.2.1.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjweaver</artifactId>
      <version>1.9.5</version>
    </dependency>
	<!--    事务支持-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>5.2.0.RELEASE</version>
    </dependency>

jdbc.peoperties配置：

//cj 对应的是mysql 8.0及以上版本 低版本的应删除
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/{对应数据库的名称}?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
jdbc.username=root
jdbc.password=123456

spring.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"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        https://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context.xsd">
    <context:component-scan base-package="org.example"></context:component-scan>
	//引入jdbc.properties
    <context:property-placeholder location="jdbc.properties"></context:property-placeholder>
    //对应实例化
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.url}"></property>
        <property name="user" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>
    //实例化jdbcTemplate
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    		//注入bean
        <property name="dataSource" ref="dataSource"></property>
    </bean>
</beans>

2.读入数据

测试数据：

package org.example;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;
public class Basejdbc2 {
    private JdbcTemplate jdbcTemplate;
    @Test
    public void test(){
        ApplicationContext applicationContext =new ClassPathXmlApplicationContext("spring.xml");
        jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
        String Sql ="select count(1) from tb_account";
        Integer title =jdbcTemplate.queryForObject(Sql,Integer.class);
        System.out.println(title);
    }

}

测试成功，简化操作：创建一个父类 Basejdbc.java

package org.example;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(locations = {"classpath:spring.xml"})
public class Basejdbc {
    public void test() {
    }
}

后面的测试类都继承该父类

package org.example;

import org.example.po.Account;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
@Repository
public class springJdbcText extends Basejdbc {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Test
    //查询总数
    public void test(){

        String Sql ="select count(1) from tb_account";
        Integer title =jdbcTemplate.queryForObject(Sql,Integer.class);
        System.out.println(title);
    }

    @Test
    //含参查询
    public void test1(){

        String Sql ="select count(1) from tb_account where account_id =? and  account_name =?";
        Integer title =jdbcTemplate.queryForObject(Sql,Integer.class,2,"admin");
        System.out.println(title);
    }
    @Test
    public void  test01() {
        String Sql = "select account_name from tb_account where account_id=?";
//        String name=jdbcTemplate.queryForObject(Sql,new String[]{"admin"},String.class);
        String name1 = jdbcTemplate.queryForObject(Sql, String.class, 1);
        System.out.println(name1);

    }
    @Test
            public void test02(){
       //查询集合
        String Sql12="select * from tb_account where account_id=?";
//方式1 RowMapper 创建一个对象加载
        /*RowMapper rowMapper =new RowMapper() {
            @Override
            public Object mapRow(ResultSet resultSet, int i) throws SQLException {
                Account account = new Account();
                account.setAccountId(resultSet.getInt("account_id"));
                account.setAccountName(resultSet.getString("account_name"));
                account.setMoney(resultSet.getDouble("money"));
                return account;
            }
        };
       Account account1 = (Account) jdbcTemplate.queryForObject(Sql12,rowMapper,1);
        System.out.println(account1);*/


//        RowMapper rowMapper=(resultSet, i) -> {
//            Account account = new Account();
//            account.setAccountId(resultSet.getInt("account_id"));
//            account.setAccountName(resultSet.getString("account_name"));
//            account.setMoney(resultSet.getDouble("money"));
//            return account;
//        };
//        Account account1 = (Account) jdbcTemplate.queryForObject(Sql12,rowMapper,1);
//        System.out.println(account1);


    Account account1 = (Account) jdbcTemplate.queryForObject(Sql12,(resultSet, i) -> {
            Account account = new Account();
            account.setAccountId(resultSet.getInt("account_id"));
            account.setAccountName(resultSet.getString("account_name"));
            account.setMoney(resultSet.getDouble("money"));
            return account;
        },1);
        System.out.println(account1);

}


        @Test
        public void test03(){
        String Sql02="insert into tb_account(account_name,account_type,money,remark) value (?,?,?,?)";
        Integer row =jdbcTemplate.update(Sql02,"lisa","中国银行",1000,"零花钱");
            System.out.println(row);
        }


        @Test
        public void test04(){
        String Sql03="update tb_account set money = money -? where account_id=?";
        Integer row=jdbcTemplate.update(Sql03,2000,3);
            System.out.println(row);
        }
//        @Test
//        public void test05(){
//        String Sql04 ="delete  where account_id=?";
//        Integer row =jdbcTemplate.update(Sql04,3);
//        }

}

---

总结

springjdbc的增删改插对应的文档在spring.io Data access