JDBC 와 MYSQL 연동하기 2) 간단한 쿼리문 실행해보기

sql
# ************************************************************
# Sequel Pro SQL dump
# Version 5446
#
# https://www.sequelpro.com/
# https://github.com/sequelpro/sequelpro
#
# Host: 127.0.0.1 (MySQL 8.0.23)
# Database: jsp
# Generation Time: 2021-04-15 12:27:02 +0000
# ************************************************************
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
SET NAMES utf8mb4;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
 
# Dump of table alba
# ------------------------------------------------------------
 
DROP TABLE IF EXISTS `alba`;
 
CREATE TABLE `alba` (
  `al_id` char(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'ex) ''A00000001''',
  `al_name` varchar(20) NOT NULL DEFAULT '',
  `al_age` int NOT NULL,
  `al_zip` varchar(7) NOT NULL DEFAULT '',
  `al_addr1` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `al_addr2` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `al_hp` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `gr_code` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `al_gen` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'ex) ''F'' / ''M''',
  `al_mail` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `al_career` varchar(200) DEFAULT NULL,
  `al_spec` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `al_desc` varchar(500) DEFAULT NULL,
  `al_img` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`al_id`),
  KEY `gradeCode` (`gr_code`),
  CONSTRAINT `gradeCode` FOREIGN KEY (`gr_code`) REFERENCES `grade` (`gr_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 
LOCK TABLES `alba` WRITE;
/*!40000 ALTER TABLE `alba` DISABLE KEYS */;
 
INSERT INTO `alba` (`al_id`, `al_name`, `al_age`, `al_zip`, `al_addr1`, `al_addr2`, `al_hp`, `gr_code`, `al_gen`, `al_mail`, `al_career`, `al_spec`, `al_desc`, `al_img`)
VALUES
    ('A0000001','김두한',30,'35036','대전','중구 석교동','010-1234-5678','G006','M','abc@gmail.com','경력 없음','싸움짱','4달러',NULL),
    ('A0000002','홍길동',40,'12345','미국','서구 엘에이','0401-859-994','G001','M','hong@naver.com','아버지','호형호제 가능',NULL,NULL);
 
/*!40000 ALTER TABLE `alba` ENABLE KEYS */;
UNLOCK TABLES;
 
 
# Dump of table grade
# ------------------------------------------------------------
 
DROP TABLE IF EXISTS `grade`;
 
CREATE TABLE `grade` (
  `gr_code` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `gr_name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`gr_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 
LOCK TABLES `grade` WRITE;
/*!40000 ALTER TABLE `grade` DISABLE KEYS */;
 
INSERT INTO `grade` (`gr_code`, `gr_name`)
VALUES
    ('G001','고졸'),
    ('G002','초대졸'),
    ('G003','대졸'),
    ('G004','대학원졸'),
    ('G005','석사'),
    ('G006','박사');
 
/*!40000 ALTER TABLE `grade` ENABLE KEYS */;
UNLOCK TABLES;
 
 
# Dump of table lic_alba
# ------------------------------------------------------------
 
DROP TABLE IF EXISTS `lic_alba`;
 
CREATE TABLE `lic_alba` (
  `al_id` char(8) NOT NULL,
  `lic_code` char(4) NOT NULL,
  `lic_date` varchar(30) NOT NULL,
  `lic_img` blob,
  KEY `alid` (`al_id`),
  KEY `lic` (`lic_code`),
  CONSTRAINT `alid` FOREIGN KEY (`al_id`) REFERENCES `alba` (`al_id`),
  CONSTRAINT `lic` FOREIGN KEY (`lic_code`) REFERENCES `license` (`lic_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 
LOCK TABLES `lic_alba` WRITE;
/*!40000 ALTER TABLE `lic_alba` DISABLE KEYS */;
 
INSERT INTO `lic_alba` (`al_id`, `lic_code`, `lic_date`, `lic_img`)
VALUES
    ('A0000001','L001','2001-01-01',NULL);
 
/*!40000 ALTER TABLE `lic_alba` ENABLE KEYS */;
UNLOCK TABLES;
 
 
# Dump of table license
# ------------------------------------------------------------
 
DROP TABLE IF EXISTS `license`;
 
CREATE TABLE `license` (
  `lic_code` char(4) NOT NULL,
  `lic_name` varchar(50) NOT NULL,
  PRIMARY KEY (`lic_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
 
LOCK TABLES `license` WRITE;
/*!40000 ALTER TABLE `license` DISABLE KEYS */;
 
INSERT INTO `license` (`lic_code`, `lic_name`)
VALUES
    ('L001','정보처리산업기사'),
    ('L002','정보처리기사'),
    ('L003','정보보안산업기사'),
    ('L004','정보보안기사'),
    ('L005','SQLD'),
    ('L006','SQLP');
 
/*!40000 ALTER TABLE `license` ENABLE KEYS */;
UNLOCK TABLES;
 
 
 
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

아래에서 사용할 간단한 테이블들을 위에 파일로 첨부해 두었습니다.

java
package kr.or.ddit.test;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
 
public class Test02Select {
 
    public static void main(String[] args) {
 
        String user = "접속아이디";
        String password = "접속비밀번호";
        String url = "jdbc:mysql://localhost:3306/디비정보";
        try(
            Connection conn = DriverManager.getConnection(url, user, password);
        ) {
 
            String sql = "select * from alba";
            Statement statement = conn.createStatement();
            ResultSet result = statement.executeQuery(sql);
 
            while(result.next()) {
                String al_id = result.getString(1);
                String al_name = result.getString(2);
                int al_age = result.getInt(3);
                String al_zip = result.getString(4);
                String al_addr1 = result.getString("al_addr1");
                String al_addr2 = result.getString("al_addr2");
                String al_hp = result.getString("al_hp");
                String gr_code = result.getString("gr_code");
                String al_gen = result.getString("al_gen");
                String al_mail =result.getString("al_mail");
                String al_career = result.getString("al_career");
                String al_spec = result.getString("al_spec");
                String al_desc = result.getString("al_desc");
                String al_img = result.getString("al_img");
                System.out.printf("%s %s %d %s %s %s %s %s %s %s %s %s %s %s \n",al_id,al_name,al_age,al_zip,al_addr1,al_addr2,al_hp,gr_code,al_gen,al_mail,al_career,al_spec,al_desc,al_img);
            }
 
 
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

기본적으로 Oracle에서 CRUD 했을때와 전부 똑같습니다.

getString이나 getInt도 가능하고, 인덱스 번호 혹은 컬럼 명을 기술해서 데이터를 받아 올 수 있습니다.

조회 결과입니다.