Java数据库操作之JDBC

Connect.java

package com.pcf.lab_jsp_3;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Connect {
	private static final String userName = "root";
	private static final String password = "123456";
	private static final String dbName = "lab_jsp_3";
	private static final String driver = "com.mysql.jdbc.Driver";
	private static final String url = "jdbc:mysql://127.0.0.1:3306/" + dbName + "?useSSL=false";

	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		Class.forName(driver);
		return DriverManager.getConnection(url, userName, password);
	}
}

JDBCUtils.java

/*
 * JDBCUtils
 *
 * $Id$
 * $HeadURL$
 */

import java.lang.reflect.InvocationTargetException;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * JDBC Connection and Statement utility functions.
 *
 * @author jmfee
 */
public class JDBCUtils {

    /**
     * Mysql Driver.
     */
    public static final String MYSQL_DRIVER_CLASSNAME = "com.mysql.jdbc.Driver";

    /**
     * SQLite Driver.
     */
    public static final String SQLITE_DRIVER_CLASSNAME = "org.sqlite.JDBC";

    /**
     * Create a new JDBC Connection.
     *
     * @param driver driver class name.
     * @param url    driver specific url.
     * @return Connection to database.
     * @throws ClassNotFoundException    if driver class is not found.
     * @throws IllegalAccessException    if driver empty constructor is not public.
     * @throws InstantiationException    if an exception occurs while instantiating driver.
     * @throws InvocationTargetException if an exception occurs with invoked method
     * @throws NoSuchMethodException     if method cannot be found
     * @throws SQLException              if an error occurs while making connection.
     */
    public static Connection getConnection(final String driver, final String url)
            throws ClassNotFoundException, IllegalAccessException,
            InstantiationException, InvocationTargetException,
            NoSuchMethodException, SQLException {
        // create driver class, which registers with DriverManager
        Class.forName(driver).getConstructor().newInstance();

        // request connection from DriverManager
        return DriverManager.getConnection(url);
    }

    /**
     * Set a JDBC prepared statement parameter.
     * <p>
     * Either calls statement.setNull if object is null, or sets the appropriate
     * type based on the object. If the object is not null, type is ignored.
     *
     * @param statement statement with parameters to set.
     * @param index     index of parameter being set.
     * @param object    value of parameter being set.
     * @param type      java.sql.Types constant for column type.
     * @throws SQLException if an error occurs while making connection.
     */
    public static void setParameter(final PreparedStatement statement,
                                    final int index, final Object object, final int type)
            throws SQLException {

        if (object == null) {
            statement.setNull(index, type);
        } else if (object instanceof Boolean) {
            statement.setBoolean(index, (Boolean) object);
        } else if (object instanceof Byte) {
            statement.setByte(index, (Byte) object);
        } else if (object instanceof Character) {
            statement.setString(index, ((Character) object).toString());
        } else if (object instanceof Double) {
            statement.setDouble(index, (Double) object);
        } else if (object instanceof Float) {
            statement.setFloat(index, (Float) object);
        } else if (object instanceof Integer) {
            statement.setInt(index, (Integer) object);
        } else if (object instanceof Long) {
            statement.setLong(index, (Long) object);
        } else if (object instanceof Short) {
            statement.setShort(index, (Short) object);
        } else if (object instanceof String) {
            statement.setString(index, (String) object);
        } else {
            statement.setObject(index, object, type);
            System.err.printf(
                    "Unsupported object type (%s): index=%d, value=%s\n",
                    object.getClass().getName(), index, object.toString());
        }
    }

    /**
     * Get a mysql connection from a URL.
     * <p>
     * Calls getConnection(MYSQL_DRIVER_CLASSNAME, url).
     *
     * @param url a Mysql URL.
     * @return a Connection to a Mysql database.
     * @throws SQLException              if an error occurs while making connection.
     * @throws ClassNotFoundException    if driver class is not found.
     * @throws IllegalAccessException    if driver empty constructor is not public.
     * @throws InstantiationException    if an exception occurs while instantiating driver.
     * @throws InvocationTargetException if an exception occurs with invoked method
     * @throws NoSuchMethodException     if method cannot be found
     */
    public static Connection getMysqlConnection(final String url)
            throws SQLException, ClassNotFoundException,
            IllegalAccessException, InstantiationException,
            InvocationTargetException, NoSuchMethodException {
        return getConnection(MYSQL_DRIVER_CLASSNAME, url);
    }

    /**
     * Get a sqlite connection from a file.
     * <p>
     * Builds a sqlite file url and calls getSqliteConnection(url).
     *
     * @param file sqlite database file.
     * @return connection to sqlite database file.
     * @throws SQLException              if an error occurs while making connection.
     * @throws ClassNotFoundException    if driver class is not found.
     * @throws IllegalAccessException    if driver empty constructor is not public.
     * @throws InstantiationException    if an exception occurs while instantiating driver.
     * @throws InvocationTargetException if an exception occurs with invoked method
     * @throws NoSuchMethodException     if method cannot be found
     */
    public static Connection getSqliteConnection(final File file)
            throws SQLException, ClassNotFoundException,
            IllegalAccessException, InstantiationException,
            InvocationTargetException, NoSuchMethodException {
        String sqliteFileURL = "jdbc:sqlite:" + file.getAbsolutePath();
        return getSqliteConnection(sqliteFileURL);
    }

    /**
     * Get a sqlite connection from a URL.
     * <p>
     * Calls getConnection(SQLITE_DRIVER_CLASSNAME, url).
     *
     * @param url sqlite database URL.
     * @return a Connection to a sqlite database.
     * @throws SQLException              if an error occurs while making connection.
     * @throws ClassNotFoundException    if driver class is not found.
     * @throws IllegalAccessException    if driver empty constructor is not public.
     * @throws InstantiationException    if an exception occurs while instantiating driver.
     * @throws InvocationTargetException if an exception occurs with invoked method
     * @throws NoSuchMethodException     if method cannot be found
     */
    public static Connection getSqliteConnection(final String url)
            throws SQLException, ClassNotFoundException,
            IllegalAccessException, InstantiationException,
            InvocationTargetException, NoSuchMethodException {
        return getConnection(SQLITE_DRIVER_CLASSNAME, url);
    }

}