package com.nhfc99.login;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

//import com.mysql.jdbc.Statement;

public class ConnectSql {
private Connection conn = null;

public void connectMysql() throws ClassNotFoundException, SQLException {
    // 基本的连接数据库方式
    // Statement stmt = null;
    // // 注册 JDBC 驱动器
    // Class.forName("com.mysql.jdbc.Driver");
    // // 打开一个连接
    // conn =
    // DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mysql?characterEncoding=utf8&useSSL=true",
    // "root", "****");
    // if (conn == null) {
    // System.out.println("连接失败");
    // } else {
    // System.out.println("连接成功");
    // String sql = "SELECT * FROM user";
    // stmt = (Statement) conn.createStatement();
    // ResultSet rs = stmt.executeQuery(sql);
    // while (rs.next()) {
    // System.out.println("User = " + rs.getString("User") + "\n");
    // System.out.println("Host = " + rs.getString("Host"));
    // }
    // }

    // 使用数据库连接池进行获取数据库的连接
    try {
        Context context = new InitialContext();
        Context envContext = (Context) context.lookup("java:comp/env");
        DataSource ds = (DataSource) envContext.lookup("jdbc/mysqlds");
        conn = ds.getConnection();
        if (conn == null) {
            System.out.println("连接数据库失败\n");
        } else {
            System.out.println("连接数据库成功\n");
        }
        // 检索用户信息
        Statement statement = (Statement) conn.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT * FROM Users");
        boolean isHaveNextValue = resultSet.next();
        if (isHaveNextValue) {
            do {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                int age = resultSet.getInt("age");
                System.out.println("id = " + id + ", username = " + username + ", age = " + age);
                isHaveNextValue = resultSet.next();
            } while (isHaveNextValue);
        }
        //清除
        statement.close();
        //关闭数据库连接交回连接池
        closeSqlConnection();
    } catch (NamingException e) {
        // TODO: handle exception
        System.out.println("getExplanation = " + e.getMessage());
    }
}

public void closeSqlConnection() {
    try {
        conn.close();
    } catch (SQLException e) {
        // TODO: handle exception
    }
}

}

//context.xml
<?xml version="1.0" encoding="UTF-8"?>
<Context reloadable="true">
<Resource
name="jdbc/mysqlds"
auth="Container"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="30"
maxWaite="10000"
username="root"
password="****"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1:3306/TestDatabase" />
</Context>