First JDBC Program

Maven

The most important detail is the JDBC Driver library for MySQL server itself. It just won't show up on your computer, so you need to add it there.

If you are using Maven, then you need to help him by pointing out the right library.

MySQL JDBC driver :

   	<dependency>
        	<groupId>mysql</groupId>
        	<artifactId>mysql-connector-java</artifactId>
        	<version>8.0.29</version>
    	</dependency>

Just add this code to your pom.xml .

I will also give a few more popular drivers.

JDBC Driver for PostgeSQL :

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.4.0</version>
</dependency>

JDBC driver for Oracle :

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>21.5.0.0</version>
</dependency>

JDBC driver for H2 :

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.1.214</version>
</dependency>

First database query

All the required libraries are included, now it's time to run your first database access program. We will write all the code in the main() method .

Stage 1 . Add a few imports before the main() method - this will make your life much easier:

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

Stage 2 . First we need to create a database connection. To do this, in the main() method , write the following code:

Connection connection  = DriverManager.getConnection(
           "jdbc:mysql://localhost:3306/test",
           "login", "password");

You can also do it in one line - whichever is more convenient for you. Login and password, of course, you need to substitute the real ones from your local MySQL server.

Stage 3 . We create a query to the database. Let's get all the users from the user table. Then you will need to add this line of code:

Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery("SELECT * FROM user");

That's two lines. In the first we create an objectStatement, and in the second, we use it to query the database. The executeQuery() method executes a database query and returns an object of typeresult set.

Stage 4 . Display the data contained in the objectresult set.

result set- it's not a set, it's just called that. It stores the result of the query execution. This object is somewhat similar to an iterator: it allows you to set / change the current row of the result, and then you can get data from this current row. Add the following code to your example:

while (results.next()) {
        	Integer id = results.getInt(1);
        	String name = results.getString(2);
        	System.out.println(results.getRow() + ". " + id + "\t"+ name);
}

The next() method changes the current result row to the next one. It returns true if there is such a line, and false if there are no more lines.

Then from the current line of the objectresult setyou can get data from its columns:

• getRow() - returns the number of the current row in the objectresult set
• getInt(N) - will return the data of the Nth column of the current row as an int
• getString(N) - will return the data of the Nth column of the current row as a String

Full program listing

To minimize errors, here is a complete listing of the program:

package org.example;

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

public class JdbcApplicatin {
    public static void main(String[] args) throws  Exception{
 	   Connection connection  = DriverManager.getConnection(
          	"jdbc:mysql://localhost:3306/test",
          	"root", "secret");

        Statement statement = connection.createStatement();
    	ResultSet results = statement.executeQuery("SELECT * FROM user");

    	while (results.next()) {
        	Integer id = results.getInt(1);
        	String name = results.getString(2);
        	System.out.println(results.getRow() + ". " + id + "\t"+ name);
    	}
    	connection.close();
    }
}

And my screen output after running the program:

1

Task

Module 4. Working with databases, level 7, lesson 1

Locked

task0701

task0701

Open

1

Task

Module 4. Working with databases, level 7, lesson 1

Locked

task0702

task0702

Open

1

Task

Module 4. Working with databases, level 7, lesson 1

Locked

task0703

task0703

Open