자바 jdbc 라이믹스 게시판 insert - mariadb-java-client-2.4.0.jar

자바 jdbc 라이믹스 게시판 insert - mariadb-java-client-2.4.0.jar 드라이버를 이용해 insert하는 자바 코드이다.

이 앞글에서 mariadb-java-client-1.1.9.jar 을 사용했더니 getGenerateKey 사용부분이 달라져서 오류가 발생하였다.

또한 테이블 기본값을 사용하는 것도 1.1.9는 테이블 컬럼에 기본값 없음으로 되있어도 값을 안넣어도 오류가 발생하지 않았지만 2.4.0은 값이 없으면 오류가 발생하였다.

그래서 테이블 컬럼 기본값 없음으로 되어 있으면 일일히 값을 다 넣어줘야만 했다.

그래서 1.1.9 -> 2.4.0 jdbc 드라이버 사용한 코드이다.

package naver_news;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;


public class Jdbc {
	
	final static int MODULE_SRL = 52;
	final String DRIVER = "org.mariadb.jdbc.Driver";
	final String DB_IP = "localhost";
	final String DB_PORT = "3306";
	final String DB_NAME = "rx";
	final String DB_USER_ID = "rx";
	final String DB_PW = "YOUR_PASSWORD";
	final String DB_URL = 
			"jdbc:mariadb://" + DB_IP + ":" + DB_PORT + "/" + DB_NAME;
	
	List<String> listNick=null;
	
	public Jdbc() {
		listNick=new ArrayList<>();
		listNick.add("홍길동");
		listNick.add("성춘향");
		listNick.add("이몽룡");
	}
	
	public Connection getConn(){
		Connection conn = null;
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(DB_URL, DB_USER_ID, DB_PW);
			if (conn != null) {
				System.out.println("DB연결 성공");
			}
		} catch (ClassNotFoundException e) {
			System.out.println("클래스 없음 오류");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("DB SQL 에러");
			e.printStackTrace();
		}
		return conn;
	}
	
	public int getSeq() {
		Connection conn = null;
		conn = getConn();
		int seq = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "insert into rx_sequence values(?)";
			//pstmt = conn.prepareStatement(sql);
			pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			pstmt.setString(1, "0");
			

					
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			while (rs.next()) {
				seq = rs.getInt(1);
			}
		} catch (Exception e) {
			System.out.println("error: " + e);
		} finally {
			try {
				if (rs != null) { rs.close(); }
				if (pstmt != null) { pstmt.close();	}
				if (conn != null) {	conn.close(); }
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return seq;
	}
	
	public void insertDb(String tit, String cont) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			LocalDateTime now=LocalDateTime.now();
			String date=now.format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss"));
			
			int document_srl = getSeq();
			System.out.println("document_srl="+document_srl);
			int module_srl = MODULE_SRL;
			String lang_code = "ko";
			String is_notice = "N";
			String title = tit;
			String title_bold = "N";
			String title_color = "N";
			String content = cont;
			
			Random random = new Random();
			int i = random.nextInt(listNick.size());
			String nick=listNick.get(i);

			String user_name = nick;
			String nick_name = nick;
			int member_srl = 4;
			
			String email_address = "jung945@hanmail.net";
			String homepage = "";
			String ipaddress = "127.0.0.1";
			String regdate = date;
			String last_update = date;

			int list_order = -document_srl;
			int update_order = -document_srl;
			
			String allow_trackback = "N";
			String notify_message = "N";
			String status = "PUBLIC";
			String comment_status = "ALLOW";
		
			conn = getConn();
			
			String sql = "insert into rx_documents (document_srl, module_srl, lang_code, is_notice, title"
					+ ", title_bold, title_color, content, user_name, nick_name"
					+ ", member_srl, email_address, homepage, ipaddress, list_order"
					+ ", regdate, last_update, update_order, allow_trackback, notify_message"
					+ ", status, comment_status)"
	                     + " values("
	                     + "?,?,?,?,?"
	                     + ",?,?,?,?,?"
	                     + ",?,?,?,?,?"
	                     + ",?,?,?,?,?"
	                     + ",?,?"
	                     + ")";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, document_srl);
			pstmt.setInt(2, module_srl);
			pstmt.setString(3, lang_code);
			pstmt.setString(4, is_notice);
			pstmt.setString(5, title);
			
			pstmt.setString(6,title_bold);
			pstmt.setString(7,title_color);
			pstmt.setString(8,content);
			pstmt.setString(9,user_name);
			pstmt.setString(10,nick_name);
			
			pstmt.setInt(11,member_srl);
			pstmt.setString(12,email_address);
			pstmt.setString(13,homepage);
			pstmt.setString(14,ipaddress);
			pstmt.setInt(15,list_order);
			
			pstmt.setString(16,regdate);
			pstmt.setString(17,last_update);
			pstmt.setInt(18,update_order);
			pstmt.setString(19,allow_trackback);
			pstmt.setString(20,notify_message);
			
			pstmt.setString(21,status);
			pstmt.setString(22,comment_status);
			
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			System.out.println("error: " + e);
		} finally {
			try {
				if (rs != null) { rs.close(); }
				if (pstmt != null) { pstmt.close();	}
				if (conn != null) {	conn.close(); }
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String[] args) throws Exception{
		System.out.println("시작");
		Jdbc jdbc=new Jdbc();
		//jdbc.getConn();
		
		jdbc.insertDb("제목","본문");
		System.out.println("끝");
		
	}

}