21장 JDBC 프로그래밍 설명
MySQL 설정 방법..
MySQL MySQL 5.1 Downloads
mysql-connector-java-5.1.7.zipDataBase 접속에 관련된 클래스 DBConnector
import java.sql.*;
public class DBConnector {
public static Connection getConnection() {
Connection conn = null ;
try {
Class.forName("com.mysql.jdbc.Driver") ;
String url = "jdbc:mysql://localhost:3306/shoppingmall" ;
String username = "www" ;
String password = "www" ;
conn = DriverManager.getConnection(url , username , password) ;
}catch(Exception e) { e.printStackTrace() ; }
return conn ;
}
}
데이터 베이스 작업을 실제로 수행하게 되는 클래스 - DataHandler
import java.sql.*;
import java.util.*;
public class DataHandler {
public Vector
Vector
Connection connection = DBConnector.getConnection() ;
Statement st = null ;
try {
st = connection.createStatement() ;
ResultSet rs = st.executeQuery("select * from users") ;
while(rs.next()) {
User user = new User(rs.getString("userid") , rs.getString("username") ,
rs.getString("email") , rs.getString("phone") ,
rs.getString("address")) ;
list.add(user) ;
}
}catch(Exception e) { e.printStackTrace() ; }
finally {
try {
st.close(); connection.close() ;
}catch(Exception e) {}
}
return list;
}
public void addUser(User user) {
Connection conn = DBConnector.getConnection() ;
Statement st = null ;
try {
st = conn.createStatement() ;
String sql = "insert into users values('" + user.getUserid() + "','" ;
sql += user.getUsername() + "','" ;
sql += user.getEmail() + "','" ;
sql += user.getPhone() + "','" ;
sql += user.getAddress() + "')" ;
st.executeUpdate(sql) ;
st.close() ;
conn.close();
}catch(Exception e) {
e.printStackTrace() ;
}
}
public void removeUser(User user) {
Connection conn = DBConnector.getConnection() ;
Statement st = null ;
try {
st = conn.createStatement() ;
String sql = "delete from users where userid='" + user.getUserid() + "'" ;
st.executeUpdate(sql) ;
st.close() ;
conn.close();
}catch(Exception e) {
e.printStackTrace() ;
}
}
public void updateUser(User user) {
Connection conn = DBConnector.getConnection() ;
Statement st = null ;
try {
st = conn.createStatement() ;
String sql = "update users set username='" + user.getUsername() + "'," ;
sql += " email='" + user.getEmail() + "'," ;
sql += " phone='" + user.getPhone() + "'," ;
sql += " address='" + user.getAddress() +
"' where userid ='" + user.getUserid() + "'" ;
System.out.println(sql) ;
st.executeUpdate(sql) ;
st.close() ;
conn.close();
}catch(Exception e) {
e.printStackTrace() ;
}
}
}
사용자 정보를 담고 있어야 할 클래스 - User
public class User {
public User(String userid, String username, String email, String phone,
String address) {
super();
this.userid = userid;
this.username = username;
this.email = email;
this.phone = phone;
this.address = address;
}
String userid , username , email , phone , address ;
public String getUserid() {
return userid;
}
public void setUserid(String userid) {
this.userid = userid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
실제로 사용자 관리를 위해 필요한 구동 애플리케이션 클래스 - UserManager
import javax.swing.*;
import java.awt.event.*;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.ListSelectionListener;
import javax.swing.table.*;
import java.util.*;
import java.awt.* ;
public class UserManager extends JFrame implements ActionListener {
private DataHandler handler ;
private JTable list;
private DefaultTableModel model ;
private static int NEW = 0 ;
private static int MODIFY = 1 ;
private static int STANDBY = -1 ;
private int mode = STANDBY ;
private int currentEditRow = -1 ;
private JButton newButton , updateButton , deleteButton ;
private JTextField userid , username , email , phone , address ;
public UserManager() {
handler = new DataHandler() ;
String [] columnNames = {"userid" , "username" , "email" ,
"phone","address"};
model = new DefaultTableModel(columnNames , 0) ;
list = new JTable(model) ;
add(list , BorderLayout.NORTH) ;
fillUserList(handler.getUserList()) ;
JPanel panel = new JPanel() ;
panel.setLayout(new GridLayout(5,2)) ;
JLabel lbl_id = new JLabel("User ID") ;
panel.add(lbl_id) ;
userid = new JTextField(40) ;
panel.add(userid) ;
JLabel lbl_name = new JLabel("User Name") ;
panel.add(lbl_name) ;
username = new JTextField(40) ;
panel.add(username) ;
JLabel lbl_email = new JLabel("Email") ;
panel.add(lbl_email) ;
email = new JTextField(40) ;
panel.add(email) ;
JLabel lbl_phone = new JLabel("Phone") ;
panel.add(lbl_phone) ;
phone = new JTextField(40) ;
panel.add(phone) ;
JLabel lbl_address = new JLabel("Address") ;
panel.add(lbl_address) ;
address = new JTextField(40) ;
panel.add(address) ;
add(panel , BorderLayout.CENTER) ;
JPanel buttonPanel = new JPanel() ;
buttonPanel.setLayout(new GridLayout(1,3)) ;
newButton = new JButton("New") ;
updateButton = new JButton("Update") ;
deleteButton = new JButton("Delete") ;
buttonPanel.add(newButton) ; buttonPanel.add(updateButton) ;
buttonPanel.add(deleteButton) ;
add(buttonPanel , BorderLayout.SOUTH) ;
newButton.addActionListener(this) ;
updateButton.addActionListener(this) ;
deleteButton.addActionListener(this) ;
list.setColumnSelectionAllowed(false) ;
list.getSelectionModel().addListSelectionListener(new ListSelectionListener() {
public void valueChanged(ListSelectionEvent e) {
int rownum = list.getSelectedRow() ;
if( rownum >= 0 ) {
userid.setText(model.getValueAt(rownum , 0).toString()) ;
username.setText(model.getValueAt(rownum , 1).toString()) ;
email.setText(model.getValueAt(rownum, 2).toString()) ;
phone.setText(model.getValueAt(rownum, 3).toString()) ;
address.setText(model.getValueAt(rownum,4).toString()) ;
mode = MODIFY ;
userid.setEditable(false) ;
currentEditRow = rownum ;
}
}
}) ;
}
public static void main(String[] args) {
UserManager app = new UserManager() ;
app.setSize(800,600) ;
app.setVisible(true) ;
}
public void fillUserList(Vector list) {
Iterator
while(iter.hasNext()) {
User current = iter.next();
Object[] o = new Object[5] ;
o[0] = current.getUserid() ;
o[1] = current.getUsername() ;
o[2] = current.getEmail() ;
o[3] = current.getPhone() ;
o[4] = current.getAddress() ;
model.addRow(o) ;
}
}
public void actionPerformed(ActionEvent e) {
String command = e.getActionCommand() ;
if( command.equals("New")) {
mode = NEW ;
clearFields() ;
} else if( command.equals("Update")) {
if( mode == NEW ) {
User current= getUserFromFields() ;
handler.addUser( current ) ;
model.addRow(getObjectFromUser(current)) ;
userid.setEditable(true) ;
clearFields() ;
} else if( mode == MODIFY) {
User current= getUserFromFields() ;
model.setValueAt(current.userid, currentEditRow, 0) ;
model.setValueAt(current.username, currentEditRow, 1) ;
model.setValueAt(current.email, currentEditRow, 2) ;
model.setValueAt(current.phone,currentEditRow,3) ;
model.setValueAt(current.address , currentEditRow , 4) ;
handler.updateUser(current) ;
}
} else if( command.equals("Delete")) {
if( mode == MODIFY ) {
handler.removeUser(getUserFromFields()) ;
model.removeRow(currentEditRow) ;
clearFields() ;
mode = STANDBY ;
}
}
}
public void clearFields() {
username.setText("") ; userid.setText("") ; email.setText("") ;
phone.setText("") ; address.setText("") ;
}
public Object [] getObjectFromUser(User user) {
Object [] o = new Object[5] ;
o[0] = user.getUserid() ;
o[1] = user.getUsername() ;
o[2] = user.getEmail() ;
o[3] = user.getPhone() ;
o[4] = user.getAddress() ;
return o ;
}
public User getUserFromFields() {
User user = new User(userid.getText(), username.getText(),
email.getText(), phone.getText(),
address.getText()) ;
return user ;
}
}