Java 8 connect select and insert to MySQL

Problem

I need to connect to MySQL and to insert some values or to select some using Java 8.

Solution

Bellow you can find program that use properties to connect to DB. Data base is test:

  • url = "jdbc:mysql://localhost:3306/test";
  • user = "test";
  • password = "test";

and this dependency in the POM.xml file:

    <dependencies>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.6</version>
        </dependency>
    </dependencies>
</project>

This is the code which has 4 methods:

  • init() open new connection to the db
  • insert(con) - insert new record
  • select(con) - read first or all records
  • close(con) - close DB connection at the end
package db;

import jdk.nashorn.internal.runtime.Version;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Statement;
import java.sql.ResultSet;

class ConnectMySQL {

    public static void main(String[] args) {
        Connection con = init();
        insert(con);
        select(con);
        close(con);
    }
    //
    // Open connection do MySQL db
    //
    static Connection init() {
        Connection con = null;
        
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "test";
        String password = "test";
        
        Connection connection;
        try {
            con = DriverManager.getConnection(url, user, password);
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Version.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
        return con;
    }
    //
    // Insert a record in the DB
    //
    static void insert(Connection con) {
        Statement st = null;
        ResultSet rs = null;
        try {
            st = con.createStatement();
            st.executeUpdate("insert into test.table2 values (7)");
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Version.class.getName());
            lgr.log(Level.WARNING, ex.getMessage(), ex);
        }
    }
    //
    // Insert a record in the DB
    //
    static void select(Connection con) {
        Statement st = null;
        ResultSet rs = null;
        try {
            st = con.createStatement();
            rs = st.executeQuery("SELECT * from test.table2");
            //while (rs.next()) {   //select all rows
            if (rs.next()) {        //select first row
                System.out.println(rs.getString(1));
            }
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Version.class.getName());
            lgr.log(Level.WARNING, ex.getMessage(), ex);
        }
    }
    static void close (Connection con){
        if (con != null) {
            try {
                con.close();
            }catch (SQLException ex) {
                Logger lgr = Logger.getLogger(Version.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}

Related Article