使用JDBC连接数据库

这里提供的是最简单的连接方法，如需长期稳定的连接池，请移步此处（还没写~一会发~）。

示例连接的是MySQL~如果需要连接别的数据库，可以参考本文的后半部分~

示例代码：

/* @(#) DBConnection.java
 * 
 * Date: 2011-11-19
 *
 * Author: Rainisic
 */
package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.logging.Logger;

/**
 * @author Rainisic
 * 
 */
public class DBConnection {

    /** The database URL. */
    private static final String DB_URL = "jdbc:mysql://localhost/interview";

    /** The user name of database. */
    private static final String DB_USERNAME = "root";

    /** The password of database. */
    private static final String DB_PASSWORD = "123456";

    /** Define a singleton database connection. */
    private static DBConnection dbConnection = null;

    /** Define the logger. */
    private static Logger logger = Logger.getLogger("DBConnection");

    /** Define the connection. */
    private Connection connection = null;

    /** Define the prepared statement. */
    private PreparedStatement preparedStatement = null;

    /**
     * Private constructor.
     */
    private DBConnection() {
    }

    /**
     * Get a instance of database connection.
     * 
     * @return
     */
    public static DBConnection GetInstance() {

        if (dbConnection == null) {
            dbConnection = new DBConnection();
        }
        return dbConnection;
    }

    /**
     * Connect to the database.
     */
    public void connect() {

        try {

            // Load the JDBC Driver.
            Class.forName("com.mysql.jdbc.Driver");

            // Get a connection.
            connection = DriverManager.getConnection(DB_URL, DB_USERNAME,
                    DB_PASSWORD);

        } catch (ClassNotFoundException | SQLException e) {
            logger.severe(e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * Save the data.
     * 
     * @param data
     * @return
     */
    public boolean save(Data data) {

        // Define the SQL statement.
        String sql = "INSERT INTO baidu (username, password) VALUES (?, ?);";

        try {

            // Prepare the SQL statement.
            preparedStatement = connection.prepareStatement(sql);

            // Set the statement value.
            preparedStatement.setString(1, data.getUsername());
            preparedStatement.setString(2, data.getPassword());

            // Execute the statement.
            if (preparedStatement.execute()) {
                return true;
            }

        } catch (SQLException e) {
            logger.severe(e.getMessage());
            e.printStackTrace();
        }

        return false;
    }

    /**
     * Load data from database.
     * 
     * @param username
     * @return
     */
    public List<Data> load(String username) {

        // Define the SQL statement.
        String sql = "SELECT * FROM baidu WHERE username = ?;";

        // Create a result list.
        List<Data> result = new LinkedList<Data>();

        try {

            // Prepare the SQL statement.
            preparedStatement = connection.prepareStatement(sql);

            // Set the statement value.
            preparedStatement.setString(1, username);

            // Execute query.
            ResultSet resultSet = preparedStatement.executeQuery();

            // Analyze the result set.
            while (resultSet.next()) {
                result.add(new Data(resultSet.getString(1), resultSet
                        .getString(2)));
            }

        } catch (SQLException e) {
            logger.severe(e.getMessage());
            e.printStackTrace();
        }

        return result;
    }

    /**
     * Update data.
     * 
     * @param data
     * @return
     */
    public boolean update(Data data) {

        // Define the SQL statement.
        String sql = "UPDATE baidu SET password = ? WHERE username = ?";

        try {

            // Prepare the SQL statement.
            preparedStatement = connection.prepareStatement(sql);

            // Set the statement value.
            preparedStatement.setString(1, data.getPassword());
            preparedStatement.setString(2, data.getUsername());

            // Execute the statement.
            if (preparedStatement.execute()) {
                return true;
            }

        } catch (SQLException e) {
            logger.severe(e.getMessage());
            e.printStackTrace();
        }

        return false;
    }

    /**
     * Delete from database.
     * 
     * @param username
     * @return
     */
    public boolean delete(String username) {

        // Define the SQL statement.
        String sql = "DELETE FROM baidu WHERE username = ?;";

        try {

            // Prepare the SQL statement.
            preparedStatement = connection.prepareStatement(sql);

            // Set the statement value.
            preparedStatement.setString(1, username);

            // Execute the statement.
            if (preparedStatement.execute()) {
                return true;
            }

        } catch (SQLException e) {
            logger.severe(e.getMessage());
            e.printStackTrace();
        }

        return false;
    }

    /**
     * Disconnect to the database.
     */
    public void disconnect() {

        try {

            // Close the connection.
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }

        } catch (SQLException e) {
            logger.severe(e.getMessage());
            e.printStackTrace();
            if (connection != null) {
                connection = null;
            }
        }
    }
}

如需连接其他类型数据库，请根据数据库类型进行如下修改：

• 下载对应JDBC驱动包

  Access：JDK自带驱动包，无需下载。

  MySQL：点此进入下载页（该页为下载页面，为了防止链接失效，故直接贴下载链接，请见谅）

  Microsoft SQL Server：由于不同版本SQLServer的驱动下载不同，请到Microsoft官网下载

  Oracle：Oracle中自带驱动，如果已经安装Oracle则无需下载。

  SQLite：点此进入下载页（该页为下载页面，为了防止链接失效，故直接贴下载链接，请见谅）

• 修改JDBC驱动类

  /** Access. */
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  
  /** MySQL. */
  Class.forName("com.mysql.jdbc.Driver");
  
  /** Microsoft SQL Server. */
  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
  
  /** Oracle. */
  Class.forName("oracle.jdbc.driver.OracleDriver");
  
  /** SQLite. */
  Class.forName("org.sqlite.JDBC");

• 修改JDBC URL

  /** Access. */
  private static final String DB_URL = "jdbc:odbc:[dataSource]";
  
  /** MySQL. */
  private static final String DB_URL = "jdbc:mysql://[hostname]:[port]/[dbname]";
  
  /** Microsoft SQL Server. */
  private static final String DB_URL = "jdbc:sqlserver://[hostname]:[port];DatabaseName=[dbname]";
  
  /** Oracle. */
  private static final String DB_URL = "jdbc:oracle:thin:@[hostname]:[port]#:[oracleBDSID]";
  
  /** SQLite. */
  private static final String DB_URL = "jdbc:sqlite:[database.db]";

• 修改SQL语句

   根据不同数据库语法修改SQL。