数据库初始化及SQL语句

以下是创建数据库和表的SQL脚本：

CREATE DATABASE study;

CREATE TABLE admin(
id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	PASSWORD VARCHAR(20)
)CHARSET=utf8;

SELECT * FROM admin;
SELECT COUNT(*) FROM admin;
DROP TABLE admin;

1. 使用Druid连接池

1.1 配置文件

在src目录下创建一个名为druid.properties的文件，内容如下：

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/study?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
username=root
password=你的密码

initialSize=10
minIdle=5
maxActive=50
maxWait=5000

1.2 封装工具类

创建一个工具类来管理Druid连接池：

package com.example.jdbc;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class DruidUtil {
    private static javax.sql.DataSource dataSource;

    static {
        try {
            Properties props = new Properties();
            InputStream is = DruidUtil.class.getClassLoader().getResourceAsStream("druid.properties");
            props.load(is);
            dataSource = DruidDataSourceFactory.createDataSource(props);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try { rs.close(); } catch (SQLException ignored) {}
        }
        if (stmt != null) {
            try { stmt.close(); } catch (SQLException ignored) {}
        }
        if (conn != null) {
            try { conn.close(); } catch (SQLException ignored) {}
        }
    }
}

1.3 数据操作示例

以下是一个查询数据的示例：

package com.example.jdbc;

import java.sql.*;

public class AdminQuery {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DruidUtil.getConnection();
            String sql = "SELECT id, username, password FROM admin";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("username");
                String pwd = rs.getString("password");
                System.out.println("ID: " + id + ", 用户名: " + name + ", 密码: " + pwd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(conn, pstmt, rs);
        }
    }
}

2. C3P0连接池配置

2.1 配置文件

在src目录下创建c3p0-config.xml：

    
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/study</property>
        <property name="user">root</property>
        <property name="password">你的密码</property>

        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">50</property>
        <property name="checkoutTimeout">5000</property>
    

2.2 使用C3P0

通过C3P0获取连接并执行SQL：

package com.example.jdbc;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3P0Example {
    public static void main(String[] args) {
        ComboPooledDataSource cpds = new ComboPooledDataSource("example");
        try (Connection conn = cpds.getConnection()) {
            System.out.println("成功获取连接！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3. 普通JDBC连接

3.1 工具类封装

通过配置文件封装普通JDBC连接：

package com.example.jdbc;

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

public class JdbcHelper {
    private static String driver, url, user, password;

    static {
        try {
            Properties props = new Properties();
            InputStream is = JdbcHelper.class.getClassLoader().getResourceAsStream("db.properties");
            props.load(is);

            driver = props.getProperty("driver");
            url = props.getProperty("url");
            user = props.getProperty("user");
            password = props.getProperty("password");

            Class.forName(driver);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

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

    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) { try { rs.close(); } catch (SQLException ignored) {} }
        if (stmt != null) { try { stmt.close(); } catch (SQLException ignored) {} }
        if (conn != null) { try { conn.close(); } catch (SQLException ignored) {} }
    }
}

3.2 数据增删改查

以下是一个简单的增删改查示例：

package com.example.jdbc;

import java.sql.*;

public class CrudExample {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JdbcHelper.getConnection();

            // 插入数据
            String insertSql = "INSERT INTO admin (username, password) VALUES (?, ?)";
            pstmt = conn.prepareStatement(insertSql);
            pstmt.setString(1, "testUser");
            pstmt.setString(2, "testPass");
            pstmt.executeUpdate();

            // 查询数据
            String querySql = "SELECT * FROM admin WHERE username = ?";
            pstmt = conn.prepareStatement(querySql);
            pstmt.setString(1, "testUser");
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("username"));
            }

            // 删除数据
            String deleteSql = "DELETE FROM admin WHERE username = ?";
            pstmt = conn.prepareStatement(deleteSql);
            pstmt.setString(1, "testUser");
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcHelper.close(conn, pstmt, null);
        }
    }
}