623
No
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+"에 실패 하였습니다.");
}
}
}
댓글 ( 4)
댓글 남기기