JavaでDB接続 - HTN20190109の日記

MySQL

(8.0.22)
接続元：CentOS7
接続先：CentOS7

https://techacademy.jp/magazine/34617
https://fantasiabaetica.hatenablog.com/entry/2019/07/13/222334

-- 1. 環境設定
-- 1.1. Javaのインストール

yum install java-1.8.0-openjdk java-1.8.0-openjdk-devel
java -version
javac -version

-- 1.2. MySQL JDBCドライバのダウンロード

wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-8.0.22-1.el7.noarch.rpm
rpm -qlp mysql-connector-java-8.0.22-1.el7.noarch.rpm
rpm -ivh mysql-connector-java-8.0.22-1.el7.noarch.rpm
cp -p /usr/share/java/mysql-connector-java.jar .

-- 2. テストテーブル作成

drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 timestamp);
insert into tab1 values(1,'A',current_timestamp());
insert into tab1 values(2,'B',current_timestamp());
select * from tab1;

-- 3. DB接続確認

vim a.java

---

import java.sql.*;

public class a {
public static void main(String args) throws Exception {

Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
int ret = 0;

conn = DriverManager.getConnection("jdbc:mysql://mmm066:3306/test","test","test");
conn.setAutoCommit(false);

System.out.println("---- select(バインド変数有) ---- ");
ps = conn.prepareStatement("select * from tab1 where col1 >= ? ");
ps.setInt(1, 1);
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("col1") + "\t" + rs.getString("col2") + "\t" + rs.getString("col3"));
}
rs.close();
System.out.println("---- ");
ps.setInt(1, 2);
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("col1") + "\t" + rs.getString("col2") + "\t" + rs.getString("col3"));
}
rs.close();
ps.close();

System.out.println("---- select(バインド変数なし) ---- ");
ps = conn.prepareStatement("select * from tab1 where col1 >= 1 ");
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("col1") + "\t" + rs.getString("col2") + "\t" + rs.getString("col3"));
}
rs.close();
ps.close();

System.out.println("---- insert(バインド変数有) ---- ");
Timestamp timestamp = Timestamp.valueOf("2021-01-01 00:00:00.0");
ps = conn.prepareStatement("insert into tab1(col1,col2,col3) values(?,?,?)");
ps.setInt(1, 100);
ps.setString(2, "X");
ps.setTimestamp(3,timestamp);
ret = ps.executeUpdate();
conn.commit();
System.out.println("ret = " + ret);
ps.close();

System.out.println("---- insert(バインド変数なし) ---- ");
ps = conn.prepareStatement("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')");
ret = ps.executeUpdate();
conn.commit();
System.out.println("ret = " + ret);
ps.close();

conn.close();
}
}

---

javac -classpath .:./mysql-connector-java.jar a.java
java -classpath .:./mysql-connector-java.jar a

Oracle

(19c)
接続元：CentOS7
接続先：CentOS7

https://itsakura.com/java-oracle
https://qiita.com/linxuesong/items/7b3b5d7b15de6c035b9e
https://www.javalife.jp/2018/02/01/post-392/
https://www.atmarkit.co.jp/ait/articles/0107/11/news001.html
https://itsakura.com/java-oracle-insert
https://www.codeflow.site/ja/article/jdbc__how-to-insert-timestamp-value-in-preparedstatement

-- 1. 環境設定

-- 1.1. Javaのインストール

yum install java-1.8.0-openjdk java-1.8.0-openjdk-devel
java -version
javac -version

-- 1.2. Oracle JDBCドライバのダウンロード

wget https://download.oracle.com/otn-pub/otn_software/jdbc/198/ojdbc8.jar

-- 2. テストテーブル作成

drop table tab1 purge;
create table tab1(col1 int,col2 varchar2(10),col3 timestamp);
insert into tab1 values(1,'A',sysdate);
insert into tab1 values(2,'B',sysdate);
commit;
select * from tab1;

-- 3. DB接続確認

vim a.java

---

import java.sql.*;

public class a {
public static void main(String args) throws Exception {

Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
int ret = 0;

conn = DriverManager.getConnection("jdbc:oracle:thin:@mmm065:1521/pdb1.example.com","test","test");
conn.setAutoCommit(false);

System.out.println("---- select(バインド変数有) ---- ");
ps = conn.prepareStatement("select * from tab1 where col1 >= ? ");
ps.setInt(1, 1);
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("col1") + "\t" + rs.getString("col2") + "\t" + rs.getString("col3"));
}
rs.close();
System.out.println("---- ");
ps.setInt(1, 2);
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("col1") + "\t" + rs.getString("col2") + "\t" + rs.getString("col3"));
}
rs.close();
ps.close();

conn.close();
}
}

---

javac -classpath .:./ojdbc8.jar a.java
java -classpath .:./ojdbc8.jar a

PostgreSQL

(13)
接続元：CentOS7
接続先：CentOS8

https://qiita.com/mimitaro/items/7628c86ad8c69dfd3f03
https://itsakura.com/java-postgresql-insert

-- 1. 環境設定
-- 1.1. Javaのインストール

yum install java-1.8.0-openjdk java-1.8.0-openjdk-devel
java -version
javac -version

-- 1.2. PostgreSQL JDBCドライバのダウンロード

wget https://jdbc.postgresql.org/download/postgresql-42.2.18.jar

-- 2. テストテーブル作成

drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 timestamp);
insert into tab1 values(1,'A',clock_timestamp());
insert into tab1 values(2,'B',clock_timestamp());
select * from tab1;

-- 3. DB接続確認

vim a.java

---

import java.sql.*;

public class a {
public static void main(String args) throws Exception {

Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
int ret = 0;

conn = DriverManager.getConnection("jdbc:postgresql://mmm067:5432/test","postgres","postgres");
conn.setAutoCommit(false);

System.out.println("---- select(バインド変数有) ---- ");
ps = conn.prepareStatement("select * from tab1 where col1 >= ? ");
ps.setInt(1, 1);
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("col1") + "\t" + rs.getString("col2") + "\t" + rs.getString("col3"));
}
rs.close();
System.out.println("---- ");
ps.setInt(1, 2);
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("col1") + "\t" + rs.getString("col2") + "\t" + rs.getString("col3"));
}
rs.close();
ps.close();

conn.close();
}
}

---

javac -classpath .:./postgresql-42.2.18.jar a.java
java -classpath .:./postgresql-42.2.18.jar a

SQL Server

(2019)
接続元：CentOS7
接続先：Windows Server 2016

https://docs.microsoft.com/ja-jp/sql/connect/jdbc/connection-url-sample?view=sql-server-ver15

-- 1. 環境設定
-- 1.1. Javaのインストール

yum install java-1.8.0-openjdk java-1.8.0-openjdk-devel
java -version
javac -version

-- 1.2. SQL Server JDBCドライバのダウンロード

tar xvzf sqljdbc_8.4.1.0_jpn.tar.gz
cd sqljdbc_8.4/jpn

-- 2. テストテーブル作成

drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 datetime2);
insert into tab1 values(1,'A',getdate());
insert into tab1 values(2,'B',getdate());
select * from tab1;

-- 3. DB接続確認
vim a.java

---

import java.sql.*;

public class a {
public static void main(String args) throws Exception {

Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
int ret = 0;

conn = DriverManager.getConnection("jdbc:sqlserver://mmm064:1433;databaseName=test;user=sa;password=Aaa!1234");
conn.setAutoCommit(false);

conn.close();
}
}

---

javac -classpath .:./mssql-jdbc-8.4.1.jre8.jar a.java
java -classpath .:./mssql-jdbc-8.4.1.jre8.jar a