[java/DB] java에서 DB 데이터 읽어오기

일단, DB에서 Question2 데이터베이스를 생성합니다.

그 후, 다음과 같이 테이블과 데이터를 추가합니다.

Question2

 CREATE TABLE `user` (

`userID` CHAR(3) NOT NULL,

`name` VARCHAR(10) NOT NULL,

`birthYear` INT(4) NOT NULL,

`addr` VARCHAR(4) NOT NULL,

`mobile1` VARCHAR(3) NULL DEFAULT NULL,

`mobile2` VARCHAR(8) NULL DEFAULT NULL,

`height` INT(3) NOT NULL,

`mDate` DATE NOT NULL,

PRIMARY KEY (`userID`)

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB

;

insert into user(userID,name,birthYear,addr,mobile1,mobile2,height,mDate) values("BBK","바비킴",1973,"서울","010","00000000",176,"2013-05-05");

insert into user(userID,name,birthYear,addr,mobile1,mobile2,height,mDate) values("EJW","은지원",1972,"서울","011","88888888",174,"2014-03-03");

insert into user(userID,name,birthYear,addr,mobile1,mobile2,height,mDate) values("JKW","조관우",1965,"경기","018","99999999",172,"2010-10-10");

insert into user(userID,name,birthYear,addr,mobile1,mobile2,height,mDate) values("JYP","조용필",1950,"경기","011","44444444",166,"2009-04-04");

insert into user(userID,name,birthYear,addr,mobile1,mobile2,height,mDate) values("KBS","김범수",1979,"경남","011","22222222",173,"2012-04-04");

insert into user(userID,name,birthYear,addr,mobile1,mobile2,height,mDate) values("KKH","김경호",1971,"전남","019","33333333",177,"2007-07-07");

insert into user(userID,name,birthYear,addr,mobile1,mobile2,height,mDate) values("LJB","임재범",1963,"서울","016","66666666",182,"2009-09-09");

insert into user(userID,name,birthYear,addr,mobile1,mobile2,height,mDate) values("LSG","이승기",1987,"서울","011","11111111",182,"2008-08-08");

insert into user(userID,name,birthYear,addr,height,mDate) values("SSK","성시경",1979,"서울",186,"2013-12-12");

insert into user(userID,name,birthYear,addr,height,mDate) values("YJS","윤종신",1969,"경남",170,"2005-05-05");

 -- 2: 문제 ) 상품 id로 코드화 하고 상품을 입력하시요

 

CREATE TABLE `goodsTbl` (

`prodNo` CHAR(4) NOT NULL DEFAULT '',

`prodName` VARCHAR(50) NOT NULL,

`groupName` VARCHAR(50) NOT NULL,

`price` INT(11) NOT NULL,

PRIMARY KEY (`prodNo`),

INDEX `prodName` (`prodName`)

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB

;

 

insert into goodsTbl(prodNo,prodName,groupName,price) values("SHOE","운동화","의류",30);

insert into goodsTbl(prodNo,prodName,groupName,price) values("LAPT","노트북","전자",1000);

insert into goodsTbl(prodNo,prodName,groupName,price) values("MONI","모니터","전자",200);

insert into goodsTbl(prodNo,prodName,groupName,price) values("JEAN","청바지","의류",50);

insert into goodsTbl(prodNo,prodName,groupName,price) values("MEMO","메모리","전자",80);

insert into goodsTbl(prodNo,prodName,groupName,price) values("BOOK","책","서적",15);

 

-- 3) 문제 ) buy테이블에 상품 id, 유저 id 그리고 amount를 결정해서 입력  

 

CREATE TABLE `buyTbl` (

`num` INT(11) NOT NULL AUTO_INCREMENT,

`userID` CHAR(3) NOT NULL,

`prodNo` CHAR(4) NOT NULL,

`amount` INT(11) NOT NULL,

PRIMARY KEY (`num`),

INDEX `userID` (`userID`),

INDEX `prodNo` (`prodNo`),

CONSTRAINT `FK_buyTbl_user` FOREIGN KEY (`userID`) REFERENCES `user` (`userID`),

CONSTRAINT `FK_buyTbl_goodsTbl` FOREIGN KEY (`prodNo`) REFERENCES `goodsTbl` (`prodNo`)

)

COLLATE='utf8_general_ci'

ENGINE=InnoDB

AUTO_INCREMENT=13

;

 

insert into buyTbl(userID,prodNo,amount) values("KBS","SHOE",2);

insert into buyTbl(userID,prodNo,amount) values("KBS","LAPT",1);

insert into buyTbl(userID,prodNo,amount) values("JYP","MONI",1);

insert into buyTbl(userID,prodNo,amount) values("BBK","MONI",5);

insert into buyTbl(userID,prodNo,amount) values("KBS","JEAN",3);

insert into buyTbl(userID,prodNo,amount) values("BBK","MEMO",10);

insert into buyTbl(userID,prodNo,amount) values("SSK","BOOK",5);

insert into buyTbl(userID,prodNo,amount) values("EJW","BOOK",2);

insert into buyTbl(userID,prodNo,amount) values("EJW","JEAN",1);

insert into buyTbl(userID,prodNo,amount) values("BBK","SHOE",2);

insert into buyTbl(userID,prodNo,amount) values("EJW","BOOK",1);

insert into buyTbl(userID,prodNo,amount) values("BBK","SHOE",2);

만들어진 데이터의 모습이다.

이제 eclipse로 다시 돌아가서 코딩을 진행하자.

//접속부

DBConnection_maria.java

package acorn.conn;

import java.sql.Connection; //접속 정보를 담고 있는 클래스

import java.sql.DriverManager; //Drivermanager

import java.sql.SQLException; //sql 쿼리의 예외처리를 위한 import

public class DBConnection_maria {

private static final String DB_DRIVER_CLASS = "org.mariadb.jdbc.Driver";

private static final String DB_URL = "jdbc:mariadb://192.168.0.250:3306/Question2";

private static final String DB_USERNAME = "root";

private static final String DB_PASSWORD = "root";

//접속부

public static Connection getConnection() {

Connection con = null; //connection 객체

try {

Class.forName(DB_DRIVER_CLASS);

con = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return con;

}

}

//데이터의 저장부

work_user_Tbl_DTO.java

package acorn.conn;

import java.util.Date;

public class work_user_Tbl_DTO {

private String userID;

private String name;

private int birthYear;

private String addr;

private String mobile1;

private String mobile2;

private int height;

private Date mDate;

//생성자 오버로딩

work_user_Tbl_DTO(String userID, String name, int birthYear, String addr, String mobile1, String mobile2, int height, Date mDate) {

this.userID = userID;

this.name = name;

this.birthYear = birthYear;

this.addr = addr;

this.mobile1 = mobile1;

this.mobile2 = mobile2;

this.height = height;

this.mDate = mDate;

}

public String getUserID() {

return userID;

}

public void setUserID(String userID) {

this.userID = userID;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getBirthYear() {

return birthYear;

}

public void setBirthYear(int birthYear) {

this.birthYear = birthYear;

}

public String getAddr() {

return addr;

}

public void setAddr(String addr) {

this.addr = addr;

}

public String getMobile1() {

return mobile1;

}

public void setMobile1(String mobile1) {

this.mobile1 = mobile1;

}

public String getMobile2() {

return mobile2;

}

public void setMobile2(String mobile2) {

this.mobile2 = mobile2;

}

public int getHeight() {

return height;

}

public void setHeight(int height) {

this.height = height;

}

public Date getmDate() {

return mDate;

}

public void setmDate(Date mDate) {

this.mDate = mDate;

}

}

//데이터조작부

MainClass.java

package acorn.conn;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Date;

//DB에서는 table, Client에서는 ArrayList로 데이터 관리

public class MainClass {

//연결객체(Connection), 쿼리 전달 객체(PreparedStatement[쿼리문 전달시 사용]과 CallableStatement[프로시저 호출시 사용]), 결과 리턴 객체(ResultSet)가 필요.

public static void main(String[] args) {

// TODO Auto-generated method stub

Connection conn = new DBConnection_maria().getConnection();

ArrayList<work_user_Tbl_DTO> list = new ArrayList<work_user_Tbl_DTO>();

Connection con = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

int index = 1;

try {

con = DBConnection_maria.getConnection();

StringBuffer sql = new StringBuffer();

sql.append("select userID, name, birthYear, addr, mobile1, mobile2, height, mDate from user order by mDate asc");

pstmt = con.prepareStatement(sql.toString());

//insert, update, delete, select

rs = pstmt.executeQuery(); //쿼리 실행

while(rs.next()) { //데이터베이스형식과 java 형식이 다름.

index = 1; //1부터 시작

String userID = rs.getString(index++);

String name = rs.getString(index++);

int birthYear = rs.getInt(index++);

String addr = rs.getString(index++);

String mobile1 = rs.getString(index++);

String mobile2 = rs.getString(index++);

int height = rs.getInt(index++);

Date mDate = rs.getDate(index++);

list.add(new work_user_Tbl_DTO(userID, name, birthYear, addr, mobile1, mobile2, height, mDate));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

try {

if(rs != null) rs.close();

if(pstmt != null) pstmt.close();

if(con != null) con.close();

} catch (SQLException e2) {

// TODO Auto-generated catch block

e2.printStackTrace();

}

}

for(work_user_Tbl_DTO model : list) {

System.out.println("이름: "+model.getName()+"  아이디: "+model.getUserID()+"  주소:"+model.getAddr());

}

}

}

실행 결과