MySQL JDBC

Download mysql-connector-java-xxx.jar from mysql-connector-java

General Steps -

1. load driver
2. create connection
3. create statement (statement) / prepare statement (preparedStatement)
4. exeute query (select) / execute update (update/delete)
5. get resultset
6. close resultset/connection/connection

Sample code of MySQL JDBC access -

package com.mqin.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MySQL {
    private Connection conn = null;
    private Statement stmt = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;

    public void queryDataBase() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost/world?" + "user=root&password=root");

            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from world.city");
            printResultSet(rs);

            pstmt = conn.prepareStatement("insert into world.city values (default, ?, ?, ?, ?)");
            pstmt.setString(1, "AAA");
            pstmt.setString(2, "BBB");
            pstmt.setString(3, "CCC");
            pstmt.setInt(4, 1000);
            pstmt.executeUpdate();

            pstmt = conn.prepareStatement("SELECT id, name, countrycode, district, population from world.city");
            rs = pstmt.executeQuery();
            printResultSet(rs);

            pstmt = conn.prepareStatement("delete from world.city where name = ? ; ");
            pstmt.setString(1, "AAA");
            pstmt.executeUpdate();

            rs = stmt.executeQuery("select * from world.city");
            printResultSet(rs);
            printMetaData(rs);

        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            close();
        }

    }

    private void printMetaData(ResultSet rs) throws SQLException {
        System.out.println("The columns in the table are: ");

        System.out.println("Table: " + rs.getMetaData().getTableName(1));
        for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
            System.out.println("Column " + i + ": " + rs.getMetaData().getColumnName(i));
        }
    }

    private void printResultSet(ResultSet rs) throws SQLException {

        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String countrycode = rs.getString("countrycode");
            String district = rs.getString("district");
            int population = rs.getInt("population");

            System.out.printf("ID: %-5d Name: %-30s CountryCode: %-5s District: %-20s Population %-10d\n", id, name,
                    countrycode, district, population);
        }
    }

    private void close() {

        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                System.out.println("SQLException: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("VendorError: " + ex.getErrorCode());
            } catch (Exception ex) {
                ex.printStackTrace();
            }

            rs = null;
        }

        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                System.out.println("SQLException: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("VendorError: " + ex.getErrorCode());
            } catch (Exception ex) {
                ex.printStackTrace();
            }

            stmt = null;
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                System.out.println("SQLException: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("VendorError: " + ex.getErrorCode());
            } catch (Exception ex) {
                ex.printStackTrace();
            }

            conn = null;
        }

    }

    public static void main(String[] args) throws Exception {
        MySQL mysql = new MySQL();
        mysql.queryDataBase();
    }

}

You may meet error with MySQL delete/update query like -

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

You need to uncheck "safe update" in MySQL preference -

Edit -> Preferences -> SQL Queries -> uncheck "Safe Updates"
Query -> Reconnect to Server