[java/DB] java에서 DB 데이터 INSERT, SELECT, DELETE (삽입,출력,삭제)

[java/DB] java에서 DB 데이터 INSERT, SELECT, DELETE (삽입,출력,삭제) 하기

'[java/DB] java에서 DB 데이터 읽어오기' (https://januarysecurity.tistory.com/42) 에 있는 DB 데이터 (Question2)를 사용합니다.

DB에 테이블, 데이터 꼭 삽입해주세요.

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;

}

}

work_user_Tbl_DTO.java

package acorn.conn;

import java.sql.Connection;

import java.sql.Date;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.text.ParseException;

import java.util.ArrayList;

public class work_DAO_single {

// singleton

// 스스로 인스턴스함 : 1회제한

private static work_DAO_single single;

private work_DAO_single() {

}

public static work_DAO_single getInstance() {

if (single == null) {

single = new work_DAO_single();

}

return single;

}

// 연결, 데이터 형변환

public boolean insert(work_user_Tbl_DTO n) {

boolean isSuccess = false; // insert의 성공 유무

Connection con = null;

PreparedStatement pstmt = null; // 쿼리의 실행문을 담음

int index = 1;

try {

// ResultSet : 레코드 세트를 담고 있는 객체

con = DBConnection_maria.getConnection();

StringBuffer sql = new StringBuffer();

sql.append(

"insert into user(userID, name, birthYear, addr, mobile1, mobile2, height, mDate) values(?,?,?,?,?,?,?,?)");

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

pstmt.setString(index++, n.getUserID());

pstmt.setString(index++, n.getName());

pstmt.setInt(index++, n.getBirthYear());

pstmt.setString(index++, n.getAddr());

pstmt.setString(index++, n.getMobile1());

pstmt.setString(index++, n.getMobile2());

pstmt.setInt(index++, n.getHeight());

// Date를 sqlDate로 형변환

Date sqlDate = new Date(n.getmDate().getTime());

pstmt.setDate(index++, sqlDate);

pstmt.executeUpdate(); // insert, update, delete

isSuccess = true;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

isSuccess = false;

} finally {

try {

if (pstmt != null)

pstmt.close();

if (con != null)

con.close();

} catch (SQLException e2) {

}

}

return isSuccess;

}

public boolean delete(String userID) {

boolean isSuccess = false;

Connection con = null;

PreparedStatement pstmt = null;

int index = 1;

try {

con = DBConnection_maria.getConnection();

StringBuffer sql = new StringBuffer();

sql.append("delete from user where userID = ?");

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

pstmt.setString(index++, userID);

pstmt.executeUpdate();

isSuccess = true;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

isSuccess = false;

} finally {

try {

if (pstmt != null)

pstmt.close();

if (con != null)

con.close();

} catch (SQLException e2) {

}

}

return isSuccess;

}

public boolean update(work_user_Tbl_DTO n) {

boolean isSuccess = false;

Connection con = null;

PreparedStatement pstmt = null;

int index = 1;

try {

con = DBConnection_maria.getConnection();

StringBuffer sql = new StringBuffer();

sql.append("update user set name=? where userID=?");

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

pstmt.setString(index++, n.getName());

pstmt.setString(index++, n.getUserID());

pstmt.executeUpdate();

isSuccess = true;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

isSuccess = false;

} finally {

try {

if (pstmt != null)

pstmt.close();

if (con != null)

con.close();

} catch (SQLException e2) {

// TODO Auto-generated catch block

e2.printStackTrace();

}

}

return isSuccess;

}

public ArrayList<work_user_Tbl_DTO> select(int start, int length) {

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

Connection con = null;

ResultSet rs = null;

PreparedStatement pstmt = 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 limit ?,?");

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

pstmt.setInt(index++, start);

pstmt.setInt(index++, length);

rs = pstmt.executeQuery();

while (rs.next()) {

index = 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++);

java.util.Date mDate = (java.util.Date) 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 (pstmt != null)

pstmt.close();

if (con != null)

con.close();

if (rs != null)

con.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return list;

}

public static void main(String[] args) throws ParseException {

work_DAO_single dao = new work_DAO_single();

java.text.SimpleDateFormat format = new java.text.SimpleDateFormat("yyyyMMdd");

java.util.Date date = format.parse("20191007");

work_user_Tbl_DTO worker = new work_user_Tbl_DTO("HHH", "원_본", 2019, "서울", "010", "00000000", 155, date);

work_user_Tbl_DTO worker2 = new work_user_Tbl_DTO("HHH", "수정본", 2019, "서울", "010", "00000000", 155, date);

dao.insert(worker);

dao.update(worker2);

dao.delete("HHH");

ArrayList<work_user_Tbl_DTO> list = dao.select(0, 12);

for (work_user_Tbl_DTO wo : list) {

System.out.printf("이름: %s, 아이디 : %s, 주소: %s, 휴대폰번호: %s, 신장: %d  ", wo.getName(), wo.getUserID(),

wo.getAddr(), (wo.getMobile1() + ")" + wo.getMobile2()), wo.getHeight());

System.out.println("생일: " + wo.getmDate());

}

}

}