Spring框架整合jbdc/Dbutils/各种数据源

我们使用Spring框架就是为了更加快速高效的开发,之前我们使用java官方给的jbdc对数据库进行操作,我们可以使用Spring整合jdbc操作来简化使用.

使用步骤:

第一步:创建Maven项目,配置相关依赖(项目结构等方面不再赘述,可以看我之前的Maven博客或者一些其他案例).

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.offcn</groupId>
    <artifactId>ideaSpring-jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <!-- Spring相关依赖，版本5.1.5 -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.1.5.RELEASE</version>
        </dependency>

        <!-- MySQl：注意版本号 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>

        <!-- c3p0数据库连接池 -->
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>

        <!-- Spring整合JDBC：版本号与Spring保持一致 -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.1.5.RELEASE</version>
        </dependency>

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

第二步:创建实体类(Person):

package com.offcn.pojo;


import java.io.Serializable;

public class Person implements Serializable {

  private int pid;
  private String pname;
  private String adress;


  public int getPid() {
    return pid;
  }

  public void setPid(int pid) {
    this.pid = pid;
  }


  public String getPname() {
    return pname;
  }

  public void setPname(String pname) {
    this.pname = pname;
  }


  public String getAdress() {
    return adress;
  }

  public void setAdress(String adress) {
    this.adress = adress;
  }

  @Override
  public String toString() {
    return "Person{" +
            "pid=" + pid +
            ", pname='" + pname + '\'' +
            ", adress='" + adress + '\'' +
            '}';
  }
}

第三步:创建dao层,创建PersonDao接口和实现类PersonDaoImpl:

package com.offcn.dao;

import com.offcn.pojo.Person;

public interface PersonDao {
	//查询方法
    Person queryByid(int id);
    //修改方法
    int updateByid(Person person);
	//插入方法
    int insertPerson(Person person);
}

package com.offcn.dao;

import com.offcn.pojo.Person;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class PersonDaoImpl implements PersonDao {
    private JdbcTemplate jdbcTemplate;

    public PersonDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public JdbcTemplate getJdbcTemplate() {

        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public PersonDaoImpl() {
    }

    @Override
    public Person queryByid(int id) {
        String sql="select * from person where pid=?";
        Person person = jdbcTemplate.queryForObject(sql, new RowMapper<Person>() {

            @Override
            public Person mapRow(ResultSet resultSet, int i) throws SQLException {
                Person person1=new Person();
                person1.setPid(resultSet.getInt("pid"));
                person1.setPname(resultSet.getString("pname"));
                person1.setAdress(resultSet.getString("adress"));
                return person1;
            }
        },id);
        return person;
    }
	@Override
    public int updateByid(Person person) {
        String sql="update person set pname=?,adress=? where pid=?";
        int update =jdbcTemplate.update(sql,person.getPname(),person.getAdress(),person.getPid());
        return update;
    }

    @Override
    public int insertPerson(Person person) {
        String sql="insert into person(pname,adress) value (?,?)";
        int row = jdbcTemplate.update(sql,person.getPname(),person.getAdress());
        return row;
    }
}

第四步:使用Spring容器来管理jdbc整合对象和其他对象:

<?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">


    <!--dataSource数据源:c3p0-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass"
                  value="com.mysql.cj.jdbc.Driver"></property>
        <property
                name="jdbcUrl"
                value="jdbc:mysql://localhost:3306/java1026?characterEncoding=utf8&amp;serverTimezone=UTC">
        </property>
        <property name="user" value="root"></property>
        <property name="password" value="root"></property>
    </bean>
    <!--配置整合对象-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <!--Dao对象-->
    <bean id="persondao" class="com.offcn.dao.PersonDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>
</beans>

第五步,写测试方法:

  @Test
    public void m1(){
        ApplicationContext context=new ClassPathXmlApplicationContext("beans.xml");
        PersonDao persondao = (PersonDao) context.getBean("persondao");
        Person person = persondao.queryByid(9);
        System.out.println(person);

    }
     @Test
    public void m2(){
        ApplicationContext context=new ClassPathXmlApplicationContext("beans.xml");
        PersonDao persondao = (PersonDao) context.getBean("persondao");
        Person person=new Person();
        person.setPid(6);
        person.setPname("姜子牙");
        person.setAdress("广州");
        int row = persondao.updateByid(person);
        if (row>0){
            System.out.println("修改成功");
        }


    }

    @Test
    public void m3(){
        ApplicationContext context=new ClassPathXmlApplicationContext("beans.xml");
        PersonDao persondao = (PersonDao) context.getBean("persondao");
        Person person=new Person();
        person.setPname("露娜");
        person.setAdress("北京");
        int row = persondao.insertPerson(person);
        if (row>0){
            System.out.println("增加成功");
        }


    }

总结:

• 使用JdbcTemplate对象
• spring容器配置几种属性:数据库连接池数据源/JdbcTemplate对象/Dao对象

配置Druid数据源的注意点:

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="username" value="root"></property>
        <property name="password" value="root"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/java1026?characterEncoding=utf8"></property>
        <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    </bean>

• 使用驱动的name属性是driverClassName

配置DBCP数据源的注意点: