spring配置数据源(c3p0)手写DBUtils以及测试数据库连接查询数据

spring结合c3p0做的DButils

适用于初学者
废话不多说直接上代码

工欲善其事必先利其器

maven项目自然少不了用到的jar的坐标了

spring的核心jar包

  <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.0.2.RELEASE</version>
        </dependency>

c3p0和数据库驱动

  <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.41</version>
        </dependency>

单元测试

 <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

1. 首先要配置一个spring的xml，我取名为bean.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 http://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="com.java"></context:component-scan>
    <!-- 配置c3p0连接池 -->
    <bean id="c3p0" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
        <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/db_student_ssm"></property>
        <property name="user" value="root"></property>
        <property name="password" value="123456"></property>
    </bean>
</beans>

2. 配置完数据源之后开始写DBUtils
   2.1取名为DBUtils.java

package com.java.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.*;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
*@作者：wanwgei
*/
public class DBUtils {

    private static ComboPooledDataSource DataSource;
    private static Connection conn;
    private static PreparedStatement ps;
    private static ResultSet rs;

    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;

    static {
        ApplicationContext context = new ClassPathXmlApplicationContext("bean.xml");
        DataSource = (ComboPooledDataSource) context.getBean("c3p0");
        driverClass = DataSource.getDriverClass();
        url = DataSource.getJdbcUrl();
        username = DataSource.getUser();
        password = DataSource.getPassword();
        try {
            //注册驱动
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConn() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    /**
     * 关闭数据库连接
     */
    public static void close(Connection con, PreparedStatement ps, ResultSet rs) {
        try {
            if (con != null)
                con.close();
            if (ps != null)
                ps.close();
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

3. 写完数据库连接工具进行测试
   3.1. 实体类User.java

package com.java.entity;

/**
*实体类
*@作者：wanwgei
*/
public class User {

    private String UerName;
    private String Password;

    public User() {
        super();
    }

    public String getUerName() {
        return UerName;
    }

    public void setUerName(String uerName) {
        UerName = uerName;
    }

    public String getPassword() {
        return Password;
    }

    public void setPassword(String password) {
        Password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "UerName='" + UerName + '\'' +
                ", Password='" + Password + '\'' +
                '}';
    }
}

	3.2.dao和service由于测试工具就没有按照规格化的代码流程来书写，直接在service里面写测试，写一个service接口UserService.java

package com.java.service;

import com.java.entity.User;

import java.util.List;

/**
*@作者：wanwgei
*/
public interface UserService {
    public List<User> findAll() throws Exception;
}

	3.3实现类UserServiceImpl.java

package com.java.service.impl;

import com.java.entity.User;
import com.java.service.UserService;
import com.java.utils.DBUtils;
import org.junit.Test;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
*@作者：wanwgei
*/
@Service
public class UserServiceImpl implements UserService {

    User user;
    List<User> userList;
    Connection conn;
    PreparedStatement ps;
    ResultSet rs;

    public List<User> findAll() throws SQLException {

        try {
            conn = DBUtils.getConn();

            ps = conn.prepareStatement("select * from user");
            userList = new ArrayList<User>();
            rs = ps.executeQuery();
            while (rs.next()) {
                user = new User();
                user.setUerName(rs.getString(1));
                user.setPassword(rs.getString(2));
                userList.add(user);
                System.out.println(user.toString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.close(conn,ps,rs);
            System.out.println("数据库连接关闭！！！");
        }
        return userList;
    }

    @Test
    public void test() throws Exception {
        UserService userService = new UserServiceImpl();
        userService.findAll();
    }
}

这里我采用的是单元测试，对于初学者来说，spring和数据源的使用，关键是要学会使用spring上下文怎么取bean以及怎么把数据库连接上，我看网上并没有这个教程，我就自己写了一个教程并且附带上自己的代码，谢谢大家指导。相互学习。
PS：如果要spring去管理对象，也就是在不需要new，只需要加上@service和@Autowired就可以不需要在去new对象，而是交给spring去管理创建。