java从Tomcat DBCP JDBC连接池获取连接对象

Question

我有一个DataSource，我从tomcat-dbcp获得：

   import java.sql.Connection

   public Connection initPooledConnection()
{
    try {
        conn=(Connection)ds.getConnection();
        if(conn==null)
        {
            System.out.println("Failed to initialize the connection");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return conn;
}

如何继续使用com.mysql.jdbc.Statement、com.mysql.jdbc.ResultSet、com.mysql.jdbc.PreparedStatement向mysql发出请求

Answer 1

下面是一个使用查询参数并使用结果的select查询示例

import com.mysql.jdbc.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DataSource {
    public static Connection getConnection() {
        // Return a connection from the pool
    }
}

public class UserDAO {

    /**
     * Queries for User objects and returns them as a List
     */
    public List<User> getUsersForGroupID( int groupId ) {
        List<User> users = new ArrayList<User>();

        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // Check out a new connection from DataSource
            connection = DataSource.getConnection();

            // Define our query
            String query = "SELECT * FROM Users WHERE group_id = ?";

            // Create a PreparedStatement from the connection
            ps = connection.prepareStatement( query );

            // Add the parameter values (values for the ?s in the query)
            // The first one has an index of 1. They are not 0-based.
            ps.setInt( 1, groupId );

            // Execute the query and keep the returned ResultSet
            rs = ps.executeQuery();

            while (rs.next()) {
                User user = new User();
                user.setUsername(rs.getString("username"));
                user.setFullName(rs.getString("fullname"));
                users.add(user);
            }
        } catch (SQLException e) {
            // Log exception here
        } finally {
            try {
                if ( ps != null && !ps.isClosed() ) {
                    ps.close();
                }
            } catch (Exception e) {
                // Log exception thrown by ps.close()
            }
            try {
                if ( connection != null && !connection.isClosed() ) {
                    connection.close();
                }
            } catch (Exception e) {
                // Log exception thrown by connection.close();
            }
        }

        return users;
    }

}