Loading the JDBC driver only needs to be done the first time you connect to the database. After java6 we can load it directly like this: * I used mysql as an example in this series.

  1. Mysql-connector-java-5.0.8-bin.jar (mysql-connector-java-5.0.8-bin.jar)
  2. For web applications, put the JAR package in WebRoot/ web-INF /lib/
  3. For a normal Java project, import the JAR package into your project’s lib library.
  4. Then load the driver as follows
Class.forName("com.mysql.jdbc.Driver");
Copy the code

To open a connection, call the getConnection() method of the DriverManager class, which has three overloaded methods. As shown below.

public static Connection getConnection(String url,
        java.util.Properties info) throws SQLException {

        return (getConnection(url, info, Reflection.getCallerClass()));
    }

public static Connection getConnection(String url,
        String user, String password) throws SQLException {
        java.util.Properties info = new java.util.Properties();

        if(user ! = null) { info.put("user", user);
        }
        if(password ! = null) { info.put("password", password);
        }

        return (getConnection(url, info, Reflection.getCallerClass()));
}


    public static Connection getConnection(String url)
        throws SQLException {

        java.util.Properties info = new java.util.Properties();
        return (getConnection(url, info, Reflection.getCallerClass()));
    }
Copy the code

If you look at the parameter name you should know what it needs. I will only explain the first method here. The Properties info parameter is actually a package of user and password. It’s actually the same thing as method two.

# # # # # # instances:

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/user";
        String user = "root";
        String password = "root"; Connection connection = null; Statement statement = null; ResultSet resultSet = null; Try {// 1. Load Driver // com.mysql.jdbc.driver class.forname ("com.mysql.jdbc.Driver"); / / 2. Get connected connection = DriverManager. GetConnection (url, user, password); / / 3. Get used to send the database SQL Statement object Statement = connection. The createStatement (); ResultSet = statement.executeQuery("SELECT * FROM user;"); // Parse the datawhile (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("username");
                String psd = resultSet.getString("birthday");
                String email = resultSet.getString("sex");
                String birthday = resultSet.getString("address");

                System.out.println("" + name + "" + psd + "" + email
                        + ""+ birthday); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally {//5. Close the connection to release resourcesif(resultSet ! = null) { try { resultSet.close(); } catch (SQLException e) { e.printStackTrace(); } resultSet = null; }if(statement ! = null) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } statement = null; }if(connection ! = null) { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } connection = null; }}}}Copy the code

If you want to create a Statement object, you need to create a Statement object.

Statement statement = connection.createStatement();
Copy the code

The Statement object can execute SQL

String sql = "select * from user";

ResultSet resultSet = statement.executeQuery(sql);
Copy the code

When you execute an SQL query, you get a ResultSet object that holds the results of the query.

ResultSet resultSet = statement.executeQuery("SELECT * FROM user;");
Copy the code

How do I get the corresponding column in the user table?

    resultSet .getString    ("columnName");
    resultSet .getLong      ("columnName");
    resultSet .getInt       ("columnName");
    resultSet .getDouble    ("columnName");
    resultSet .getBigDecimal("columnName");
Copy the code

Or query by column number.

    resultSet .getString    (1);
    resultSet .getLong      (2);
    resultSet .getInt       (3);
    resultSet .getDouble    (4);
    resultSet .getBigDecimal(5);
Copy the code

You can do this if you want to know the index value of the corresponding column name

 int columnIndex = resultSet .findColumn("columnName");
Copy the code

### Data modification/deletion

Why should we mention modification and deletion together, because they are not the same methods as query calls. To query we call the executeQuery() method, to modify and delete we need to use the executeUpdate() method. Here’s a simple example:

// Update data (also called modify data) String SQL ="update user set name='fantj' where id=1"; int rowsAffected = statement.executeUpdate(sql); //rowsAffected is used to affect the number of rows"delete from user where id=123";

int rowsAffected    = statement.executeUpdate(sql);
Copy the code

For security and project performance, we try to close the connection after the operation (although older JVMS close it automatically, this also requires detection of wasted CPU resources). Closing the connection is divided into three parts.

  • resultSet.close();
  • statement.close();
  • connection.close();

Pay attention to priorities.