[java + mysql] select, insert, update 예제

[java + mysql] select, insert, update 예제

 

1. mysql 구조

 

--
-- 테이블 구조 `test`
--
 
CREATE TABLE IF NOT EXISTS `test` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `memo` text NOT NULL,
  `regdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`no`)
) ENGINE=MyISAM  DEFAULT CHARSET=euckr;
 

 

 

2. java + mysql 연동 예제

 

import java.sql.*;
 
public class mysql {
 
 
public static void main(String[] args) {
 
Connection conn;
 Statement stmt;
 
try {
 Class.forName("com.mysql.jdbc.Driver");//드라이버 로딩: DriverManager에 등록
}catch(ClassNotFoundException e) {
 System.err.print("ClassNotFoundException: ");
 }
 
try {
 //String jdbcUrl = "jdbc:mysql://localhost:3306/crm_db?useUnicode=true&useUnicode=true&characterEncoding=euc_kr";//사용하는 데이터베이스명을 포함한 url
 String jdbcUrl = "jdbc:mysql://localhost:3306/crm_db?&characterEncoding=euckr";//사용하는 데이터베이스명을 포함한 url
 String userId = "root";//사용자계정
 String userPass = "비밀번호";//사용자 패스워드
 
conn = DriverManager.getConnection(jdbcUrl, userId, userPass);//Connection 객체를 얻어냄
stmt = conn.createStatement();//Statement 객체를 얻어냄
 
System.out.println("제대로 연결되었습니다");//성공시 화면에 표시됨
 
     ResultSet rs = stmt.executeQuery("SELECT * FROM `test` where 1");
     int no;
     String name,memo;
     while(rs.next()){
         no = rs.getInt("no");
         name = rs.getString("name");
         memo = rs.getString("memo");
         System.out.println(no+" "+name+" "+memo);
         //System.out.println(no+" "+toUnicode(name)+" "+toUnicode(memo)); // 문자열이 latin1 일때 
     }
     
         name ="홍길동";
     
        String sqlStr = "insert into  test (`name`,`memo`)"
                    + "values('"+ name +"','');";    
        
        //stmt.executeUpdate(sqlStr); // 바로 insert 실행 
 
        PreparedStatement ps = (PreparedStatement) conn.prepareStatement( sqlStr, Statement.RETURN_GENERATED_KEYS);
        ps.execute();
        ResultSet rs3 = ps.getGeneratedKeys(); // insert_id 값 가져 오기
 
        int insert_no = 0;
         if(rs3.next()){
             insert_no = rs3.getInt(1);
            System.out.println(insert_no);
         }
     
        String upsqlStr = "update `test` set memo ='메모넣기' where no ='"+ insert_no +"'";
        //String upsqlStr = "update `test` set memo ='"+ toLatin("메모넣기") +"' where no ='"+ insert_no +"'"; // 문자열이 latin1 일때 
            
        
        System.out.println("upsqlStr"+upsqlStr);   
         stmt.executeUpdate(upsqlStr);  
         
         
}catch(SQLException e) {
     System.out.println("SQLException: " + e.getMessage());
    } 
}
 
private static String toUnicode(String str) {    // 문자열 깨짐 유니코드로 mysql값 가져오기 
    try {
        byte[] b = str.getBytes("ISO-8859-1");
        return new String(b);
    }
    catch (java.io.UnsupportedEncodingException uee) {
        System.out.println(uee.getMessage());
        return null;
    }
}
 
 
public static String toLatin(String str){  // 문자열 깨짐 mysql 값 보내기  characterEncoding=lantin1 으로 꼭 보낼것
    try{ 
           byte[] b = str.getBytes(); 
       return new String(b, "ISO-8859-1"); //byte형식을 " " 형식으로 
} catch (java.io.UnsupportedEncodingException uee) { 
     System.out.println(uee.getMessage());
     return null; 
} 
} 
 
 }