JDBC与DBCP配置文件（自用）

Aimer 星屑IG NB

已于 2023-05-16 17:08:25 修改

阅读量129

点赞数 2

文章标签： java spring 开发语言

于 2023-05-12 18:31:26 首次发布

本文链接：https://blog.csdn.net/qq_44704768/article/details/130648135

版权

每次用jdbc都要去搜索引擎里找，干脆就直接写个博客吧

一、JDBC

1.使用Maven导入依赖

<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.26</version>
</dependency>
<dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>4.3.13.RELEASE</version>
</dependency>

2.结构图如下

3.BaseDao.java

package com.it.www.util;

import java.sql.*;

/**
 * @projectName: spring_demo01
 * @package: com.it.www.util
 * @className: MybatisUtil
 * @author: Sun
 * @description: TODO
 * @date: 2023/5/12 17:49
 * @version: 1.0
 */
public class BaseDao {

    protected Connection conn = null;
    protected PreparedStatement pstmt = null;
    protected ResultSet resultSet = null;

    static {
        try {
            Class.forName(ConfigerManager.getInstance().getValue("driver"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取与数据库的连接
    private boolean getConn() {
        try {
            conn = DriverManager.getConnection(ConfigerManager.getInstance().getValue("url"), ConfigerManager.getInstance().getValue("username"), ConfigerManager.getInstance().getValue("password"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (conn != null) {
            return true;
        }
        return false;
    }

    //封装一个执行更新的方法--增删改
    protected int executeUpdate(String sql, Object... params) throws SQLException {
        int rows = 0;
        try {
            if (getConn()) {
                pstmt = conn.prepareStatement(sql);
                //给sql中的占位符绑定 数据
                if (params != null && params.length > 0) {
                    for (int i = 0; i < params.length; i++) {
                        pstmt.setObject(i + 1, params[i]);
                    }
                }
                //执行操作
                rows = pstmt.executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
        return rows;
    }

    //封装一个执行查询的方法 --查询
    protected ResultSet executeQuery(String sql, Object... params)throws   SQLException {

        try {
            if (getConn()) {
                pstmt = conn.prepareStatement(sql);
                //给sql中的占位符绑定 数据
                if (params != null && params.length > 0) {
                    for (int i = 0; i < params.length; i++) {
                        pstmt.setObject(i + 1, params[i]);
                    }
                }
                resultSet =  pstmt.executeQuery();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
        return resultSet;
    }
    //定义一个关闭资源的方法
    protected   void   closeResource(){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstmt!=null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

4.ConfigerManager

package com.it.www.util;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

/**
 * @projectName: spring_demo01
 * @package: com.it.www.util
 * @className: DriverManager
 * @author: Sun
 * @description: TODO
 * @date: 2023/5/12 17:56
 * @version: 1.0
 */
public class ConfigerManager {
    private static ConfigerManager driverManager;
    private static Properties properties;

    private ConfigerManager()
    {
        InputStream inputStream = null;
        try {
            properties = new Properties();
            inputStream = ConfigerManager.class.getClassLoader().getResourceAsStream("jdbc.properties");
            properties.load(inputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if (inputStream != null)
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
        }
    }

    public static synchronized ConfigerManager getInstance()
    {
        if (driverManager == null) {
            driverManager = new ConfigerManager();
        }
        return driverManager;
    }

    public String getValue(String key)
    {
        return properties.getProperty(key);
    }
}

5.jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test1?useSSL=false
username=root
password=root

6.userDao

public interface UserDao {
    User getUsers(int user_id);
}

7.userDaoImpl

package com.it.www.daoImpl;

import com.it.www.dao.UserDao;
import com.it.www.pojo.User;
import com.it.www.util.BaseDao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @projectName: spring_demo01
 * @package: com.it.www.daoImpl
 * @className: userDaoImpl
 * @author: Sun
 * @description: TODO
 * @date: 2023/5/12 17:00
 * @version: 1.0
 */
public class UserDaoImpl extends BaseDao implements UserDao {
    @Override
    public User getUsers(int user_id) {
        User user = new User();
        try {
            ResultSet rs = this.executeQuery("select * from user where user_id = ?",user_id);
            if (rs.next()){
               user = new User(
                        rs.getInt("user_id"),
                        rs.getString("user_name"),
                        rs.getString("user_password")
                );

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeResource();
        }
        return user;
    }
}

8.Test类

其中用到了spring的控制反转，所以通过spring容器代理创建userDaoImpl对象。

@Test
    public void getUserById(){
        ApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");
        UserDao userDao = (UserDaoImpl)context.getBean("userDaoImpl");
        User users = userDao.getUsers(1);
        System.out.println(users);
    }

二、DBCP

1.使用Maven导入依赖

<dependency>
      <groupId>commons-pool</groupId>
      <artifactId>commons-pool</artifactId>
      <version>1.5.4</version>
</dependency>
<dependency>
      <groupId>commons-dbcp</groupId>
      <artifactId>commons-dbcp</artifactId>
      <version>1.4</version>
</dependency>

2.DBCPUtil.java

package com.it.www.util;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;


public class DBCPUtil {

    private static DataSource dataSource = null;
    private static Connection connection= null;
    private static PreparedStatement preparedStatement =null;
    private static ResultSet resultSet = null;


    static {

        try {
            InputStream inputStream = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcp.properties");
            Properties properties = new Properties();
            properties.load(inputStream);

            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        //在连接池中获取
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static void close() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            connection = null;
        }

        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            preparedStatement = null;
        }

        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            resultSet = null;
        }

    }
    public static int executeUpdate(String sql, Object... params) throws SQLException {
        connection = DBCPUtil.getConnection();
        int row = 0;
        if(connection!= null){
            preparedStatement = connection.prepareStatement(sql);
            if (params!=null){
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i+1,params[i]);
                }
            }

            row = preparedStatement.executeUpdate();

        }


        return row;
    }

    public static ResultSet executeQuery(String sql, Object... params) throws SQLException {
        resultSet = null;
        connection = DBCPUtil.getConnection();
        if (connection!=null){
            PreparedStatement pstmt = connection.prepareStatement(sql);
            if (params!=null && params.length>0){
                for (int i = 0; i<params.length;i++){
                    pstmt.setObject(i+1,params[i]);
                }
            }
            resultSet = pstmt.executeQuery();
        }
        return resultSet;
    }
}

3.dbcp.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test1?useSSL=false
username=root
password=root

4.userDao

public interface UserDao {
    User getUsers(int user_id);
}

5.userDaoImpl

public class UserDaoImpl implements UserDao {
    @Override
    public User getUsers(int user_id) {
        User user = new User();
        try {
            ResultSet rs = DBCPUtil.executeQuery("select * from user where user_id = ?",user_id);
            if (rs.next()){
                user = new User(
                        rs.getInt("user_id"),
                        rs.getString("user_name"),
                        rs.getString("user_password")
                );

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBCPUtil.close();
        }
        return user;
    }
}