본문 바로가기
Program/JAVA

JDBC, 쿼리문(Query)

LSC_98 2022. 12. 25. 01:54

22.07.12-JDBC, 쿼리문(Query)

IP 주소 확인

netstate -an : 연결가능한 포트나 주로를 확인

DB 저장 요령

  • 속성을 저장할때 타입을 일정하게 하는 것이 JAVA 와 연결할때 더 편리하다.

mysql 관련

MySQL :: MySQL Documentation

JDBC

운영체제를 Platform independent 선택후

jar 파일 import

코드

package jdbc_package;

import java.sql.*;

public class jdbc_play {
    public static void main(String[] args) {
        Connection con = null;

        String server = "localhost:3306";
        String database = "jdbc_db";
        String user_name = "root";
        String password = "";

        //드라이버 로딩
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch (ClassNotFoundException e ){
            System.err.println("JDBC err");
            e.printStackTrace();
        }
//연결
        try{
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sample","root","");

            //jdbc 를 이용해서 어떤 작업(저장,검색,삭제,수정을 실행하는 부분
            //jdbc는 프리페어드라라는 기능을 사용할 수 있음(Prepared)
            //이 기능은 SQL명령어에서 특정한 부분 (데이터가 들어가는)을 변수처리한다음, 나중에 처리하는 방식
            String str = "INSERT INTO jdbc_db(name,id,pass) VALUES (?,?,?)";
            PreparedStatement p_stmt;
            p_stmt = con.prepareStatement(str);

            p_stmt.setString(1,"LEE");
            p_stmt.setString(2,"admin");
            p_stmt.setString(3,"17171");

            //서버로 SQL 명령어 전송
            p_stmt.execute();

            System.out.println("success");
        }catch (SQLException e){
            System.err.println("con err");
            e.printStackTrace();
        }
////해제
//        try{
//            if(con != null){
//                con.close();
//            }
//        }catch (SQLException e){
//
//        }

    }
}

Member_Project_DB

package MemberPackage;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class MemberInfo_Class implements Serializable {
   String m_name, m_phone,m_id,m_pw;
    public MemberInfo_Class() {

    }

    public String getM_name() {
        return m_name;
    }

    public void setM_name(String m_name) {
        this.m_name = m_name;
    }

    public String getM_phone() {
        return m_phone;
    }

    public void setM_phone(String m_phone) {
        this.m_phone = m_phone;
    }

    public String getM_id() {
        return m_id;
    }

    public void setM_id(String m_id) {
        this.m_id = m_id;
    }

    public String getM_pw() {
        return m_pw;
    }

    public void setM_pw(String m_pw) {
        this.m_pw = m_pw;
    }

    public void putM_name(){
        System.out.println(m_name);
    }
    public void putM_id(){
        System.out.println(m_id);
    }
    public void putM_phone(){
        System.out.println(m_phone);
    }
    public void putM_pw(){
        System.out.println(m_pw);
    }

    void insertSQL(){
        Connection con = null;
        try{
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/member_db","root","");
            String str = "INSERT INTO member_t(m_name,m_phone,m_id,m_pw) VALUES (?,?,?,?)";
            PreparedStatement p_stmt;
            p_stmt = con.prepareStatement(str);

            p_stmt.setString(1,m_name);
            p_stmt.setString(2,m_phone);
            p_stmt.setString(3,m_id);
            p_stmt.setString(4,m_pw);

            //서버로 SQL 명령어 전송
            p_stmt.execute();

            System.out.println("success");
        }catch (SQLException e){
            System.err.println("con err");
            e.printStackTrace();
        }

        try{
            if(con != null){
                con.close();
            }
        }catch (SQLException e){

        }
    }
    }
}

package MemberPackage;

import java.io.*;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Scanner;

public class MemberInfo_Play {
    public static void main(String[] args) throws IOException, ClassNotFoundException {
      Scanner input = new Scanner(System.in);
      MemberInfo_Class memberInfo = new MemberInfo_Class();

        //메뉴 구성
        System.out.println("menu");
        System.out.println("===================================================");
        System.out.println("1. info input ");
        System.out.println("2. Delete info ");
        System.out.println("3. Print MemberPackage.Member ");
        System.out.println("==================================================");

        while(true){
            switch (Integer.parseInt(input.nextLine())){
                case 1 :
                    System.out.print("Enter name : ");
                    memberInfo.setM_name(input.nextLine());

                    System.out.print("Enter phone : ");
                    memberInfo.setM_phone(input.nextLine());

                    System.out.print("Enter id : ");
                    memberInfo.setM_id(input.nextLine());

                    System.out.print("Enter pw : ");
                    memberInfo.setM_pw(input.nextLine());

                    memberInfo.insertSQL();
                    break;
                case 2:

                    break;
                case 3 :
                    break;
                default:
                    System.err.println("Error");
                    continue;
            }
        }
    }
}

INSERT 문 (ResultSet)

https://m.blog.naver.com/hansoo0724/222630461953

SELECT문을 사용할때 메소드

Untitled

SELECT 문을 사용하면 ResultSet으로 반환 된다. 

void searchSQL(String input){
        Connection con = null;
        ResultSet rs =null;
        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/member_db","root","");

            String str = "SELECT * FROM member_t WHERE m_name =?";
            PreparedStatement p_stmt;
            p_stmt = con.prepareStatement(str);

            p_stmt.setString(1,input);

            rs = p_stmt.executeQuery();
            while(rs.next()){
//                인덱스가 1부터 시작
                System.out.println("name :"+rs.getString(1)+"\n"+
                        "phone : "+rs.getString(2)+"\n"+
                        "id : "+rs.getString(3)+"\n"+
                        "pw : "+rs.getString(4)
                );
            }

        } catch (SQLException e) {
            System.err.println("SQL error");
            e.printStackTrace();
        }
    }

Delete 문

package MemberPackage;

import java.io.Serializable;
import java.sql.*;

public class MemberInfo_Class implements Serializable {
   String m_name, m_phone,m_id,m_pw;
    public MemberInfo_Class() {

    }

    public String getM_name() {
        return m_name;
    }

    public void setM_name(String m_name) {
        this.m_name = m_name;
    }

    public String getM_phone() {
        return m_phone;
    }

    public void setM_phone(String m_phone) {
        this.m_phone = m_phone;
    }

    public String getM_id() {
        return m_id;
    }

    public void setM_id(String m_id) {
        this.m_id = m_id;
    }

    public String getM_pw() {
        return m_pw;
    }

    public void setM_pw(String m_pw) {
        this.m_pw = m_pw;
    }

    public void putM_name(){
        System.out.println(m_name);
    }
    public void putM_id(){
        System.out.println(m_id);
    }
    public void putM_phone(){
        System.out.println(m_phone);
    }
    public void putM_pw(){
        System.out.println(m_pw);
    }

    void insertSQL(){
        Connection con = null;
        try{
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/member_db","root","");
            String str = "INSERT INTO member_t(m_name,m_phone,m_id,m_pw) VALUES (?,?,?,?)";
            PreparedStatement p_stmt;
            p_stmt = con.prepareStatement(str);

            p_stmt.setString(1,m_name);
            p_stmt.setString(2,m_phone);
            p_stmt.setString(3,m_id);
            p_stmt.setString(4,m_pw);

            //서버로 SQL 명령어 전송
            p_stmt.execute();

            System.out.println("success");
        }catch (SQLException e){
            System.err.println("con err");
            e.printStackTrace();
        }

        try{
            if(con != null){
                con.close();
            }
        }catch (SQLException e){

        }
    }

    void searchSQL(String input){
        Connection con = null;
        ResultSet rs =null;
        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/member_db","root","");

            String str = "SELECT * FROM member_t WHERE m_name =?";
            PreparedStatement p_stmt;
            p_stmt = con.prepareStatement(str);

            p_stmt.setString(1,input);

            rs = p_stmt.executeQuery();
            while(rs.next()){
//                인덱스가 1부터 시작
                System.out.println("name :"+rs.getString(1)+"\n"+
                        "phone : "+rs.getString(2)+"\n"+
                        "id : "+rs.getString(3)+"\n"+
                        "pw : "+rs.getString(4)
                );
            }

            System.out.println("success");
        }catch (SQLException e){
            System.err.println("con err");
            e.printStackTrace();
        }

        try{
            if(con != null){
                con.close();
            }
        }catch (SQLException e){

        }
    }
    void deleteSQL(String input){
        Connection con =null;
        try{
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/member_db","root","");
            String str = "DELETE FROM member_t WHERE m_name = ?";
            PreparedStatement p_stmt;
            p_stmt = con.prepareStatement(str);
            p_stmt.setString(1, input);
            p_stmt.execute();

            System.out.println("success");
        }catch (SQLException e){
            System.err.println("con err");
            e.printStackTrace();
        }

        try{
            if(con != null){
                con.close();
            }
        }catch (SQLException e){

        }
    }
}

package MemberPackage;

import java.io.*;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Scanner;

public class MemberInfo_Play {
    public static void main(String[] args) throws IOException, ClassNotFoundException {
      Scanner input = new Scanner(System.in);
      MemberInfo_Class memberInfo = new MemberInfo_Class();

        //메뉴 구성
        System.out.println("menu");
        System.out.println("===================================================");
        System.out.println("1. info input ");
        System.out.println("2. search info ");
        System.out.println("3. delete info ");
        System.out.println("==================================================");

        while(true){
            switch (Integer.parseInt(input.nextLine())){
                case 1 :
                    System.out.print("Enter name : ");
                    memberInfo.setM_name(input.nextLine());

                    System.out.print("Enter phone : ");
                    memberInfo.setM_phone(input.nextLine());

                    System.out.print("Enter id : ");
                    memberInfo.setM_id(input.nextLine());

                    System.out.print("Enter pw : ");
                    memberInfo.setM_pw(input.nextLine());

                    memberInfo.insertSQL();
                    break;
                case 2:
                    System.out.print("Enter Search name");
                    memberInfo.searchSQL(input.nextLine());
                    break;
                case 3 :
                    System.out.print("Enter Delete name : ");
                    memberInfo.deleteSQL(input.nextLine());
                    break;
                default:
                    System.err.println("Error");
                    continue;
            }
        }
    }
}

'Program > JAVA' 카테고리의 다른 글

JAVA 버전, JAVA 구조, 객체, NULL, \ , static, final, 이름 설정 방법, 오버라이딩, 오버로딩, 자료형 연산, continue, break, for문 이름 설정  (0) 2022.12.25
주차장 관리 프로그램  (0) 2022.12.25
Hash Map, 파일 관리  (0) 2022.12.25
플러시(Flush)  (0) 2022.12.25
Bank Project, CMD에서 JAVA실행  (2) 2022.08.28

'Program/JAVA' Related Articles

티스토리툴바