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".
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.
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";
jdbc: part is fixed because it is a string indicating that it is JDBC.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.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.
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.
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.
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.
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).
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.
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).
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.
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 try to run this code, I get two cases.
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 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