★JDBCConnection 코드

▶DB에서 데이터를 java로 가져와서 출력하는 코드

▶DB(mariaDB)에 테이블이 생성되어 있어야함

 

SQL코드

select userid, username, age from tbl_test

JAVA 코드

package com.test.jdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;


public class JDBCConnection {

	public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException {
		//DB 연결해서 자바에 불러들이는 작업
		String uri = "jdbc:mariadb://127.0.0.1:3306/webdev";
		String userid = "webmaster"; //마리아DB의 사용자 계정
		String userpw = "1111"; //마리아DB의 사용자 비밀번호
		String query = "select userid, username, age from tbl_test";
		
		Connection con;
		Statement stmt;
		ResultSet rs;
		
		Class.forName("org.mariadb.jdbc.Driver"); //(jdbc안에 Driver경로)
		
		con = DriverManager.getConnection(uri, userid, userpw);
		stmt = con.createStatement();
		rs = stmt.executeQuery(query);	//실행할 쿼리를 넣어줌
		
		List<Member> list = new ArrayList<>();
		
		while(rs.next()) {	//레코드를 계속 받아옴
			
			rs.getString("userid");
			
			list.add(new Member(rs.getString("userid"),
					rs.getString("username"),
					Integer.parseInt(rs.getString("age"))));	//생성자에는 int인테 받는건 string이라서 형변환 필요
		}
		
		//
		list.stream().map(MemberResponseDTO::new).collect(Collectors.toList());
		
		for(Member member: list)
			System.out.println("아이디 = " + member.getUserid()
					+", 이름 = " + member.getUsername()
					+", 나이 = " + member.getAge());
		
		if(rs != null)rs.close();
		if(stmt != null)stmt.close();
		if(con != null)con.close();
		
	}
}

class Member{
	
	private String userid;
	private String username;
	private int age;
	
	//--------------------Constructor---------------
	public Member(String userid, String username, int age) {
		super();
		this.userid = userid;
		this.username = username;
		this.age = age;
	}
	//--------------------getter setter---------------
	public String getUserid() {
		return userid;
	}
	public String getUsername() {
		return username;
	}
	public int getAge() {
		return age;
	}
}
//중간에 데이터를 받아서
class	MemberResponseDTO {
	
	private String userid;
	private String username;
	private int age;
	//생성자 부분을 조금 다르게//Member의 객체를 받아옴
	MemberResponseDTO(Member member){
		
		this.userid = member.getUserid();
		this.username = member.getUsername();
		this.age = member.getAge();
		
	}
}

 

+추가 코드

▶Web 프로젝트로 만든 구조(에러 해결 못한 코드)

package com.test.jdbc;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/jdbctest")
public class JDBCConnection extends HttpServlet {

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//자바 웹 서블릿
		response.setContentType("text/html; character=UTF-8");
		PrintWriter out = response.getWriter();
		
		String uri = "jdbc:mariadb://127.0.0.1:3306/webdev";
		String userid = "webmaster"; // 마리아DB의 사용자 계정
		String userpw = "1111"; // 마리아DB의 사용자 비밀번호
		String query = "select userid, username, age from tbl_test";

		Connection con = null;
		Statement stmt= null;
		ResultSet rs=null;
		
		try {
		Class.forName("org.mariadb.jdbc.Driver"); // (jdbc안에 Driver경로)
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		try {
			con = DriverManager.getConnection(uri, userid, userpw);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			stmt = con.createStatement();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			rs = stmt.executeQuery(query);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} // 실행할 쿼리를 넣어줌
		out.println("<html><head><title>TDBC TEST</title></head>");
		out.println("<body");
		out.println("<table><tr><td>아이디</td><td>이름</td><td>나이</td></tr>");
		
		
		try {
		while(rs.next()) 
			out.println("<tr><td>" + rs.getString("userid") + "</td>" + "<td>" 
					+ rs.getString("username") +"<td>" 
					+ rs.getString("age") + "</td></tr>");
		} catch(SQLException e) {
			e.printStackTrace();
		}
	
		
		/*
		if(rs != null) 
			try{
				rs.close();
			} catch (SQLException e) {
			e.printStackTrace();
			}
		if(stmt != null) 
			try{
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		if(con != null) 
			try{
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			*/
		}
	
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	
	}
}