자바

 

SQL

--데이터 베이스 선택
use java ;

-- 점수 테이블
create table score (
	
	student_no varchar(50) primary key,
	name varchar(50) not null,
	kor int default 0,
	eng int default 0,
	mat int default 0,
	tot int default 0,
	avg double default 0

);

-- 레코드 입력

insert into score values ('1', '김철수', 90, 80, 70, 240, 80);

-- 확인

select student_no, name, kor,  eng , mat , ( kor+ eng+ mat) as tot , (( kor+eng+mat)/3.0) as avg from score;


update score set name='kim' , kor=99, eng=88,  mat=77 where student_no=1;


-- 검색 

select * from score where name='김영수' ;

-- 컬럼 like '검색키워드'
-- 이름이 김으로 시작하는 학생
select * from score where name like '김%';
-- 이름이 철수로 끝나는 학생
select * from score where name like '%철수';
-- %키워드% 위치에 관계없이 검색
select * from score where name like '%박%';


select * from score where name like  concat('%' , '김' , '%') ;

 

 

class DBConnectManager

package common.db;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class DBConnectManager {

	public static Connection dbConn(){
		Connection conn=null;
		try{
			//db 접속 정보가 저장되 db.prop 파일을 로딩
			FileInputStream fis =new FileInputStream("db.prop");
			Properties prop=new Properties();
			prop.load(fis);
			String url=prop.getProperty("url");
			String id=prop.getProperty("id");
			String password=prop.getProperty("password");

			//mysql에 접속 처리
			conn=DriverManager.getConnection(url, id, password);
		}catch(Exception e){
			e.printStackTrace();
		}
		return conn;
	}
	
	
	public static void close(Connection conn){
		try {
			if(conn!=null)conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void close(PreparedStatement pstmt){
		try {
			if(pstmt!=null)pstmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void close(ResultSet rs){
		try {
			if(rs!=null)rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void close(Connection conn, PreparedStatement pstmt ){
				
		try{
			if(pstmt!=null)pstmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		try {
			if(conn!=null)conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	
	}
	

	public static void close(Connection conn, PreparedStatement pstmt, ResultSet rs){

		try{
			if(rs!=null)rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}
				
		try{
			if(pstmt!=null)pstmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		try {
			if(conn!=null)conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	
	}
	
	
	
	public static void close(Connection conn, Statement stmt, ResultSet rs){

		try{
			if(rs!=null)rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}
				
		try{
			if(stmt!=null)stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		try {
			if(conn!=null)conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	
	}
	
	
	
	
	
}

 

 

class ScoreDTO

 

package java19.score;


//MVC pattern :
// Model(모델, 데이터 저장 /처리 로직)
// View(뷰, 화면 처리 클래스)
//Controller(컨트롤러, 프로그램의 흐름 제어)

public class ScoreDTO {
	private String student_no;
	private String name;
	private int kor;
	private int eng;
	private int mat;
	private int tot;
	private double avg;
	
	public ScoreDTO() {
		super();
	}
	public ScoreDTO(String student_no, String name, int kor, int eng, int mat) {
		this.student_no = student_no;
		this.name = name;
		this.kor = kor;
		this.eng = eng;
		this.mat = mat;
		tot=kor+eng+mat;
		avg=tot/3.0;
	}
	public String getStudent_no() {
		return student_no;
	}
	public void setStudent_no(String student_no) {
		this.student_no = student_no;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getKor() {
		return kor;
	}
	public void setKor(int kor) {
		this.kor = kor;
	}
	public int getEng() {
		return eng;
	}
	public void setEng(int eng) {
		this.eng = eng;
	}
	public int getMat() {
		return mat;
	}
	public void setMat(int mat) {
		this.mat = mat;
	}
	public int getTot() {
		return tot;
	}
	public void setTot(int tot) {
		this.tot = tot;
	}
	public double getAvg() {
		return avg;
	}
	public void setAvg(double avg) {
		this.avg = avg;
	}
	
	
	@Override
	public String toString() {
		return "ScoreDTO [student_no=" + student_no + ", name=" + name + ", kor=" + kor + ", eng=" + eng + ", mat="
				+ mat + ", tot=" + tot + ", avg=" + avg + "]";
	}
	
	
	
	
	
	
	
}

 

 

class ScoreDAO

package java19.score;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import common.db.DBConnectManager;

public class ScoreDAO {
 
	private static ScoreDAO dao;
	private ScoreDAO(){
		
	}
	public static ScoreDAO getInstance(){
		if(dao==null){
			dao =new ScoreDAO();
		}
		return dao;
	}
	
	//학생 점수를 벡테어 저장하여 리턴
	public Vector listScore(){
		Vector items =new Vector(); //모든 자료형을 담을 수 있음. 순서대로 저장
		Connection conn =null; //DB 접속 처리
		PreparedStatement pstmt=null; // sql 명령어 실행
		ResultSet rs =null; // select 명령어의 결과셋(레코드세)을 1레코드씩 읽기
		try {
			conn=DBConnectManager.dbConn();
			String sql ="select student_no, name, kor,  eng , mat , ( kor+ eng+ mat) as tot , (( kor+eng+mat)/3.0) as avg from score";
			pstmt=conn.prepareStatement(sql);//sql 명령어 실행 객체 생성
			rs=pstmt.executeQuery();//실행결과를 rs에 전달
			while(rs.next()){ //다음 레코드가 있으면 true, 없으면 false
				//결과셋.get자료형("컬럼이름")
				Vector row=new Vector();
				
				String student_no=rs.getString("student_no");
				String name=rs.getString("name");
				int kor=rs.getInt("kor");
				int eng=rs.getInt("eng");
				int mat=rs.getInt("mat");
				int tot=rs.getInt("tot");
				double avg=rs.getDouble("avg");
				row.add(student_no);
				row.add(name);
				row.add(kor);
				row.add(mat);
				row.add(eng);
				row.add(tot);
				row.add(kor);
				items.add(row);//벡터에 벡터를 저장
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBConnectManager.close(conn, pstmt, rs);
		}
		return items;
	}
	
	
	//학생 점수를 테이블에 저장
	public int insertScore(ScoreDTO dto){
		int result=0; 
		Connection conn=null;
		PreparedStatement pstmt=null;
		try{
			conn=DBConnectManager.dbConn();
			String sql="insert into score( student_no, name, kor, eng, mat, tot, avg)"
					+ " values (?, ?, ?, ?, ?, ?, ? )";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, dto.getStudent_no());
			pstmt.setString(2, dto.getName());
			pstmt.setInt(3, dto.getKor());
			pstmt.setInt(4, dto.getEng());
			pstmt.setInt(5, dto.getMat());
			pstmt.setInt(6, dto.getTot());
			pstmt.setDouble(7, dto.getAvg());
			result=pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnectManager.close(conn, pstmt);
		}
		return result;
	}
	
	
	//점수 수정
	public int updateScore(ScoreDTO dto){
		int result=0;
		Connection conn=null;
		PreparedStatement pstmt=null;
		try{
			conn=DBConnectManager.dbConn();
			String sql ="update score set name=? , kor=?, eng=?,  mat=?, tot=?, avg=? where student_no=?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, dto.getName());
			pstmt.setInt(2, dto.getKor());
			pstmt.setInt(3, dto.getEng());
			pstmt.setInt(4, dto.getMat());
			pstmt.setInt(5, dto.getTot());
			pstmt.setDouble(6, dto.getAvg());
			pstmt.setString(7, dto.getStudent_no());
			result=pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnectManager.close(conn, pstmt);
		}
		return result;
	}
	
	//점수 삭제
	public int deleteScore(String student_no){
		int result=0;
		Connection conn=null;
		PreparedStatement pstmt=null;
		try{
			conn=DBConnectManager.dbConn();
			String sql ="delete from score where student_no=?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1,student_no);
			result=pstmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBConnectManager.close(conn, pstmt);
		}
		
		return result;
	}
	
	
	//학생 검색
	public Vector  searchScore(String name){
		Vector items =new Vector(); //모든 자료형을 담을 수 있음. 순서대로 저장
		Connection conn =null; //DB 접속 처리
		PreparedStatement pstmt=null; // sql 명령어 실행
		ResultSet rs =null; // select 명령어의 결과셋(레코드세)을 1레코드씩 읽기
		try {
			conn=DBConnectManager.dbConn();
			String sql =" select * from score where name like  concat('%' , ? , '%') ";
			pstmt=conn.prepareStatement(sql);//sql 명령어 실행 객체 생성
			pstmt.setString(1, name);
			rs=pstmt.executeQuery();//실행결과를 rs에 전달
			while(rs.next()){ //다음 레코드가 있으면 true, 없으면 false
				//결과셋.get자료형("컬럼이름")
				Vector row=new Vector();
				row.add(rs.getString("student_no"));
				row.add(rs.getString("name"));
				row.add(rs.getInt("kor"));
				row.add(rs.getInt("eng"));
				row.add(rs.getInt("mat"));
				row.add(rs.getInt("tot"));
				row.add(rs.getDouble("avg"));
				items.add(row);//벡터에 벡터를 저장
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBConnectManager.close(conn, pstmt, rs);
		}
		return items;
	}
	
}






 

class ScoreList

package java19.score;

import java.awt.EventQueue;
import java.util.Vector;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.border.EmptyBorder;
import javax.swing.table.DefaultTableModel;
import javax.swing.JButton;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.awt.event.ActionEvent;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JTextField;

public class ScoreList extends JFrame {

	private JPanel contentPane;
	private JTable table;
	static ScoreList frame;
	
	
	private ScoreDAO dao;
	private Vector data, col; //data(점수리스트), col(테이블이 헤더)
	
	private JTextField tfStudentNo;
	private JTextField tfName;
	private JTextField tfKor;
	private JTextField tfMat;
	private JTextField tfEng;
	private JButton btnUpdate;
	private JButton btnDelete;
	private JButton btnSearch;
	private JTextField tfSearch;
	
	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					 frame = new ScoreList();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 */
	public ScoreList() {
		setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
		setBounds(100, 100, 680, 608);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);
		
		//dao 객체 생성
		dao=ScoreDAO.getInstance();
		//테이블의 제목 컬럼 지정
		col=new Vector();
		col.add("학번");
		col.add("이름");
		col.add("국어");
		col.add("영어");
		col.add("수학");
		col.add("총점");
		col.add("평균");
		
		
		//점수 리스트를 불러옴
		//테이블 모델 : JTable에 입력될 데이터
		//JTable 에 입력 가능한 데이터 :배열(수정, 삭제가 안됨), Object, Vector
		// new DefaultTableModel(데이터, 제목열)
		DefaultTableModel model=new DefaultTableModel(dao.listScore(), col){
			
			//테이블 내용을 편집하지 못하도록 막음
			@Override
			public boolean isCellEditable(int row, int column) {
				return false;
			}
		};
		
				
		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setBounds(28, 227, 581, 333);
		contentPane.add(scrollPane);
		
		
		table = new JTable(model);
		scrollPane.setViewportView(table);
		//JTable에 마우스 이벤트 추가
		table.addMouseListener(new MouseAdapter() {
			@Override
			public void mouseClicked(MouseEvent e) {
				//마우스 클릭한 행의 인덱스 값
				int idx=table.getSelectedRow();
				//테이블.getValueAt(행의 인덱스 , 컬럼의 인덱스)
				tfStudentNo.setText(table.getValueAt(idx, 0)+"");
				tfName.setText(table.getValueAt(idx, 1)+"");
				tfKor.setText(table.getValueAt(idx, 2)+"");
				tfEng.setText(table.getValueAt(idx, 3)+"");
				tfMat.setText(table.getValueAt(idx, 4)+"");
			}
		});
		
		JLabel lblNewLabel = new JLabel("학번");
		lblNewLabel.setBounds(28, 14, 57, 15);
		contentPane.add(lblNewLabel);
		
		JLabel lblNewLabel_1 = new JLabel("이름");
		lblNewLabel_1.setBounds(233, 13, 57, 15);
		contentPane.add(lblNewLabel_1);
		
		JLabel lblNewLabel_2 = new JLabel("국어");
		lblNewLabel_2.setBounds(28, 45, 57, 15);
		contentPane.add(lblNewLabel_2);
		
		JLabel lblNewLabel_3 = new JLabel("영어");
		lblNewLabel_3.setBounds(28, 76, 57, 15);
		contentPane.add(lblNewLabel_3);
		
		JLabel lblNewLabel_4 = new JLabel("수학");
		lblNewLabel_4.setBounds(233, 48, 57, 15);
		contentPane.add(lblNewLabel_4);
		
		tfStudentNo = new JTextField();
		tfStudentNo.setBounds(71, 11, 116, 21);
		contentPane.add(tfStudentNo);
		tfStudentNo.setColumns(10);
		
		tfName = new JTextField();
		tfName.setBounds(276, 10, 116, 21);
		contentPane.add(tfName);
		tfName.setColumns(10);
		
		tfKor = new JTextField();
		tfKor.setBounds(71, 42, 116, 21);
		contentPane.add(tfKor);
		tfKor.setColumns(10);
		
		tfEng = new JTextField();
		tfEng.setBounds(71, 73, 116, 21);
		contentPane.add(tfEng);
		tfEng.setColumns(10);
		
		tfMat = new JTextField();
		tfMat.setBounds(276, 45, 116, 21);
		contentPane.add(tfMat);
		tfMat.setColumns(10);
		
		JButton btnNewButton_1 = new JButton("저장");
		btnNewButton_1.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				
				
				int result=dao.insertScore(textInput());
				
				refresh(result , "저장");
				
			}
		});
		
		
		btnNewButton_1.setBounds(28, 115, 97, 23);
		contentPane.add(btnNewButton_1);
		
		btnUpdate = new JButton("수정");
		btnUpdate.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				
				int result=dao.updateScore(textInput());
				
				refresh(result, "수정");
				
			}
		});
		btnUpdate.setBounds(250, 115, 97, 23);
		contentPane.add(btnUpdate);
		
		btnDelete = new JButton("삭제");
		btnDelete.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				
				int response=JOptionPane.showConfirmDialog(ScoreList.this, "삭제 하시겠습니까?");
				
				if(response==JOptionPane.YES_OPTION){ //yes 클릭시
					ScoreDTO dto =textInput();
					int result=dao.deleteScore(dto.getStudent_no());
					
					refresh(result, "삭제");
						
				}
				
			}
		});
		btnDelete.setBounds(482, 115, 97, 23);
		contentPane.add(btnDelete);
		
		btnSearch = new JButton("학생조회");
		btnSearch.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				
				
				
				//테이블 모델을 갱신
				DefaultTableModel model=new DefaultTableModel(dao.searchScore(tfSearch.getText()), col){
					
					//테이블 내용을 편집하지 못하도록 막음
					@Override
					public boolean isCellEditable(int row, int column) {
						return false;
					}
				};
				//테이블에 모델을 적용
				table.setModel(model);
				
			}
		});
		btnSearch.setBounds(233, 181, 97, 23);
		contentPane.add(btnSearch);
		
		tfSearch = new JTextField();
		tfSearch.setBounds(43, 182, 116, 21);
		contentPane.add(tfSearch);
		tfSearch.setColumns(10);
	}
	
	
	//텍스트 값 가져오기
	private ScoreDTO textInput(){
		//사용자가 입력한 값
		ScoreDTO dto=new ScoreDTO();
		dto.setStudent_no(tfStudentNo.getText());
		dto.setName(tfName.getText());
		dto.setKor(Integer.valueOf(tfKor.getText()));
		dto.setEng(Integer.valueOf(tfEng.getText()));
		dto.setMat(Integer.valueOf(tfMat.getText()));
		
		return dto;
	}
	
	
	//메시지 다일로그 띄우기 및 갱신
	private void refresh(int result , String str){
		if(result==1){//insert 성공하면 1이 리턴
			JOptionPane.showMessageDialog(ScoreList.this, str+" 되었습니다");
			//테이블 모델을 갱신
			DefaultTableModel model=new DefaultTableModel(dao.listScore(), col){
				
				//테이블 내용을 편집하지 못하도록 막음
				@Override
				public boolean isCellEditable(int row, int column) {
					return false;
				}
			};
			//테이블에 모델을 적용
			table.setModel(model);
			
			
			//입력란 초기화
			tfStudentNo.setText("");
			tfName.setText("");
			tfKor.setText("");
			tfEng.setText("");
			tfMat.setText("");
			tfStudentNo.setFocusable(true);
			tfStudentNo.requestFocus();//입력포커스를 이동시킴
		}else{
			JOptionPane.showMessageDialog(ScoreList.this, str+"에 실패 하였습니다.");
		}
	}
	
	
	
}

 

 

 

 

 

 

 

 

about author

PHRASE

Level 60  머나먼나라

'정신의 자유'란 것은 정신에 의한 구속을 이르는 것이다. 왜냐하면 모든 자유는 곧 지배를 의미하기 때문이다. -게오르크 짐멜

댓글 ( 0)

댓글 남기기

작성
  •    
  •