JDBC、JDBC连接数据库、连接池DBCP

三月计划

已于 2024-02-27 21:39:29 修改

阅读量534

点赞数 14

文章标签：数据库 JDBC DBCP

于 2024-02-27 21:38:49 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_44518089/article/details/136325835

版权

JDBC

sun公司为了简化开发人员对数据库的统一操作，提供一个java操作数据库的规范

第一个JDBC程序

备注：
mysql5：Class.forName(“com.mysql.jdbc.Driver”);
mysq8：Class.forName(“com.mysql.cj.jdbc.Driver”);

练习数据库


CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;

CREATE TABLE `users`(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);
delete from users where id=4;
update users set `NAME`='tytyty' where id=3
INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

package cn.ty.jdbc;

import java.sql.*;

public class jdbc {
    public static void main(String[] args){
        //1.加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.用户信息和url
            String url = "jdbc:mysql://localhost:3306/jdbcstudy?useSSL=false&characterEncoding=utf-8&serverTimezone=Hongkong";
            String username = "root";
            String password = "ty147258";
            //    3.连接成功，数据库对象 connection 数据库
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            //       4.执行sql
            String sql = "select * from users";
            /// 5.执行sql的对象去执行sql，存在结果，但会结果
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println("id="+resultSet.getInt("id"));
                System.out.println("NAME="+resultSet.getObject("NAME"));
                System.out.println("PASSWORD="+resultSet.getObject("PASSWORD"));
                System.out.println("email="+resultSet.getObject("email"));
                System.out.println("bbirth="+resultSet.getObject("birthday"));
                System.out.println("=========================");
            }
            //    6.释放连接
            resultSet.close();
            statement.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

CRUD

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useSSL=false&characterEncoding=utf-8&serverTimezone=Hongkong
username=root
password=147258

JDBC工具类

package cn.ty.jdbc;

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

public class JDBCUtils {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    static {
        InputStream in = JDBCUtils.class.getClassLoader().getResourceAsStream("cn/jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(in);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConn(){
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
    }
    //释放资源
    public static void release(Connection connection,Statement statement,ResultSet resultSet){
        if (resultSet!= null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

增加

package cn.ty.jdbc;

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

public class insertTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            connection = JDBCUtils.getConn();
            st = connection.createStatement();
            int i = st.executeUpdate("INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)\n" +
                    "VALUES(4,'tt','126','tt@sina.com','1988-12-04')");
            if (i>0){
                System.out.println("添加成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
           JDBCUtils.release(connection,st,rs);
        }
    }
}

删除

package cn.ty.jdbc;

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

public class deleteTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            connection = JDBCUtils.getConn();
            statement = connection.createStatement();
            String sql = "delete from users where id=4";
            int i = statement.executeUpdate(sql);
            if (i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.release(connection,statement,rs);
        }
    }
}

修改

package cn.ty.jdbc;

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

public class updateTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        connection = JDBCUtils.getConn();
        try {
            statement = connection.createStatement();
            int result = statement.executeUpdate("update users set `NAME`='tytyty' where id=3");
            if (result > 0){
                System.out.println("修改成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.release(connection,statement,rs);
        }
    }
}

package cn.ty.jdbc;

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

public class selectTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        connection = JDBCUtils.getConn();
        try {
            ResultSet resultSet = connection.createStatement().executeQuery("select NAME from users where id = 1 ");
            while (resultSet.next()){
                System.out.println(resultSet.getString("NAME"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.release(connection,statement,rs);
        }
    }
}

数据库连接池

预先准备好资源，数据过来连接直接连接
最小连接数：10
最大连接数：15
等待超时：100ms

创建dbcp.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSl=true
username=root
password=147258
#初始化连接数
initialSize=10
#最大连接数
maxActive=50
#最大空闲连接
maxIdle=20
#最小空闲连接
minIdle=5
#最长等待超时时间 以毫秒为单位
maxWait=60000

connectionProperties=useUnicode=true;characterEncoding=UTF8

defaultAutoCommit=true

defaultReadOnly=

defaultTransactionIsolation=READ_UNCOMMITTED

编写DBCPUtils

package cn.ty.jdbc;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class DBCPUtils {
    private static BasicDataSource dataSource = null;
    static {
        InputStream in = DBCPUtils.class.getClassLoader().getResourceAsStream("cn/dbcp.properties");
        System.out.println(in);
        Properties properties = new Properties();
        try {
            properties.load(in);
            //创造数据源 工厂模式
            dataSource = BasicDataSourceFactory.createDataSource(properties);
            System.out.println("==============");
            //dataSource.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
    }
    }