Connecting to a database with Java (Part 1) Maybe the basic method

How to connect to a database with Java?

As a method to use Java code without depending on an external library, the method of using a system called JDBC is often used as a basic method. This is prepared as a Java standard, and when connecting to an actual database, a library called a connector is also used.

Although it is used together, the code itself required for connection can basically be written within the scope of JDBC, so if you prepare multiple connectors, you can switch database systems relatively easily.

First, let's write the code when using Derby that comes standard with the Java Development Kit (JDK). Here, we will use Derby, which comes standard with the JDK. Make sure to add the Derby library to your project. As a reference, please also use "Let's put together Derby in the user library".

Basic code

Now let's get the basic code. For the time being, leave the development environment as Eclipse. Let's create a suitable project and use Sample1 as the class.

/**
 *Database connection sample(Part 1:Statement method)
 * 
 * @author Sato Daisuke
 */

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

public class Sample1 {
    public static void main(String[] args) throws SQLException {
        // (1)Prepare a URI for connection(Authentication instruction user if necessary/Add password)
        String uri = "jdbc:derby:memory:sample;create=true";

        // (2)Connect with the method of DriverManager class
        Connection conn = DriverManager.getConnection(uri);

        // (3)Creating an instance for sending SQL
        Statement st = conn.createStatement();

        // (4)SQL send
        st.executeUpdate("create table sample(id integer primary key)");
        Long start = System.currentTimeMillis();
        Long delta = start;

        //For the time being, 10,000 loops
        for (int i = 0; i < 10000; i++) {
            st.executeUpdate("insert into sample values(" + i + ")");

            //Split calculation
            if (i % 1000 == 0) {
                Long now = System.currentTimeMillis();
                Long split = now - delta;
                System.out.println("" + i + ": " + split + "ms.");
                delta = now;
            }
        }
        Long end = System.currentTimeMillis();
        System.out.println("Time required: " + (end - start) + "ms.");

        // (5)Clean up(Successful closure of instance)
        st.close();
        conn.close();
    }
}

Let's explain it in a chat.

Preparation of connection string

JDBC makes it harder to depend on a specific DB, but you can't connect unless you know which database system to connect to. Therefore, a character string for the connection (here, this is called a "connection character string") is prepared, and the connection method is acquired with the symbol corresponding to the connector in it.

        // (1)Prepare a URI for connection(Authentication instruction user if necessary/Add password)
        String uri = "jdbc:derby:memory:sample;create=true";
  1. The jdbc: part is fixed because it is a string indicating that it is JDBC.
  2. The derby part will be the name of the connector. When the connector is recognized by JDBC, the "name" of each will be registered, so you will need to compare it to select the connector to connect to.
  3. The following part (after memory in the above code) is a code that depends on the connector.

In the case of Derby, if it is a library type that is conscious of embedded use, it can be written in this form. On the other hand, in the case of client / server type (C / S), it will be written like a URL. For example, jdbc: derby: // hogeserver / fuga. In both cases, a memory database is available, and by entering the memory keyword, a temporary DB is created in memory instead of storage. In this case, it disappears when the system with DB is terminated (program termination if embedded type, server termination if C / S, etc.).

In the case of MySQL, it is basically C / S, so it will be in the format jdbc: mysql: // hogeserver / fuga.

Get connection status

After deciding the connection string, actually connect. However, it is difficult to write the connection completely on the user side, so there is an existence to have it acted for, that is the DriverManager class. Let's get this guy connected. Use the static method getConnection () to literally get the ** Connection behavior **.

// (2)Connect with the method of DriverManager class
Connection conn = DriverManager.getConnection(uri);

The generated instance is a Connection class as the method name suggests, so catch it with a variable of that class.

Note that this method creates a send when the connection fails. As a countermeasure, either delegate it to the parent with the throws declaration, or use try ~ catch. The sample code is written in throws.

Creating a statement for sending SQL

Once you have a connection, you'll create something called a ** statement ** to populate that connection with SQL.

// (3)Creating an instance for sending SQL
Statement st = conn.createStatement();

For the connected instance (here, it is received by the variable conn), it feels like" Send SQL to you and prepare a window. " It is an instance of a class called Statement. This instance can be used repeatedly, so once you create it, you will probably have it for the time being.

Send SQL

If you can get the statement, you can pass the SQL statement here and have it evaluated. The ʻexecute` method is used at this time, but there are three types. You should choose it appropriately.

ʻExecute Update (SQL statement) `

It is used to send update SQL, it is called update system, but in the extreme, it may be better to say ** other than SELECT **. If it works, you will get a return value of ** how many cases have been processed ** (integer value).

ʻExecute Query (SQL statement)

Used to send SQL to get the result in the form of a table. It's about a blunt SELECT statement. If it works, you can get the table. This table is an instance of a class called ResultSet, but we'll deal with this separately.

ʻExecute (SQL statement) `

In the above two methods, it was necessary to consider the type of SQL and use them properly, but this is the one used by a person named ** Such a troublesome **. It seems to be easy to use, but it will probably not be extremely easy because it is necessary to exercise another result acquisition method depending on the type of query (whether it is an update system or not).

Actual use

Since the update system is used here, ʻexecuteUpdate ()` is used.

// (4)SQL send
st.executeUpdate("create table sample(id integer primary key)");
...(Omission)...
st.executeUpdate("insert into sample values(" + i + ")");

The former code sends a query to create a table. The return value (numerical value) is zero because there is actually no change on the table. In the latter, we are inserting values into the table, but we are creating and sending strings on the fly. The merits and demerits of this act will be dealt with elsewhere.

Termination of use, release

At the end of use, it is better to close it according to the correct procedure. There may be processing omissions, so we will handle them properly and finish with the correct procedure.

// (5)Clean up(Successful closure of instance)
st.close();
conn.close();

In the case of Java, there is no clear standard that an object disappears when it exits a block (a subtle Schrodinger state whether it is alive or dead until it is garbage collected), so a destructor cannot be expected. Make sure to write the termination process properly.

When I run it

When I try to run this code, I get two cases.

If executed normally

If it works normally, the output will be like this.

0: 38ms.
1000: 1142ms.
2000: 892ms.
3000: 748ms.
4000: 634ms.
5000: 560ms.
6000: 595ms.
7000: 667ms.
8000: 681ms.
9000: 537ms.

Time required: 7040ms.

It is possible to put out laps every 1000 times. Of course, it will change considerably depending on the machine power.

If it cannot be executed normally

If you omit code typos, you will usually encounter this error once.

Exception in thread "main" java.sql.SQLException: No suitable driver found 
                                          for jdbc:derby:memory:sample;create=true
	at java.sql.DriverManager.getConnection(DriverManager.java:689)
	at java.sql.DriverManager.getConnection(DriverManager.java:270)
	at dbsample1.Sample1.main(Sample1.java:20)

The error is "No suitable driver found". A screwdriver is a connector. Since the connector is read at runtime, it is basically impossible to inspect it before execution. The connector is distributed in Java class archive (jar) format and the runtime classpath must include the archive.

When doing it in Eclipse, it is better to create a Derby library (user library). If you create it, add it to your build path, and rerun it, it will probably work.

The same applies when using MySQL. In this case as well, it will be easier to work with the user library.

Recommended Posts

Connecting to a database with Java (Part 1) Maybe the basic method
Try connecting to the Autonomous Database with JDK6 (Java) + JDBC OCI Driver (type 2).
Create a method to return the tax rate in Java
Java to learn with ramen [Part 1]
Replace with a value according to the match with a Java regular expression
[Java] I tried to make a maze by the digging method ♪
[Java] How to compare with equals method
Concurrency Method in Java with basic example
[Java] How to search for a value in an array (or list) with the contains method
I tried to make Basic authentication with Java
[Rails] rails new to create a database with PostgreSQL
Come out with a suffix on the method
Come out with a suffix on the method 2
How to use the replace () method (Java Silver)
[Java / PostgreSQL] Connect the WEB application to the database
I tried to break a block with java (1)
The road to creating a Web service (Part 1)
Java engineers now compare to learn the basic grammar of Ruby Part 1 (Basic, Variables)
Until you run a Java program with the AWS SDK local to Windows
Java engineers now compare to learn the basic grammar of Ruby Part 2 (classes, methods)
3. Create a database to access from the web module
Submit a job to AWS Batch with Java (Eclipse)
[Beginner] Create a competitive game with basic Java knowledge
Call a method with a Kotlin callback block from Java
How to save a file with the specified extension under the directory specified in Java to the list
HTTPS connection with Java to the self-signed certificate server
Using the database (SQL Server 2014) from a Java program 2018/01/04
Try connecting to AzureCosmosDB Emulator for Docker with Java
[Java] Basic method notes
[Java] Cut out a part of the character string with Matcher and regular expression
[Java] How to start a new line with StringBuilder
Replace only part of the URL host with java
Summary of how to use the proxy set in IE when connecting with Java
Deploy a Java application developed locally with the Cloud Toolkit to an Alibaba Cloud ECS instance
I want to return a type different from the input element with Java8 StreamAPI reduce ()
How to reduce the load on the program even a little when combining characters with JAVA
[Rails] What to do when the view collapses when a message is displayed with the errors method
How to get the class name / method name running in Java
How to take a screenshot with the Android Studio emulator
I tried to create a java8 development environment with Chocolatey
Tutorial to create a blog with Rails for beginners Part 1
I tried to modernize a Java EE application with OpenShift.
[JDBC] I tried to access the SQLite3 database from Java.
[Beginner] Try to make a simple RPG game with Java ①
I want to expand the clickable part of the link_to method
I want to make a list with kotlin and java!
I want to call a method and count the number
I want to make a function with kotlin and java!
How to test a private method with RSpec for yourself
Even in Java, I want to output true with a == 1 && a == 2 && a == 3
Tutorial to create a blog with Rails for beginners Part 2
About the behavior when doing a file map with java
A memorandum to reach the itchy place for Java Gold
A memo to start Java programming with VS Code (2020-04 version)
Be sure to compare the result of Java compareTo with 0
I tried OCR processing a PDF file with Java part2
Tutorial to create a blog with Rails for beginners Part 0
Modeling a Digimon with DDD for the first time Part 1
Basic Authentication with Java 11 HttpClient
Java to play with Function
Output about the method Part 1