Java JDBCでMySQLに接続

12820 ワード

0.準備
(1)から
http://dev.mysql.com/downloads/connector/最新のMySQLデータベースドライバをダウンロードします.
mysql-connector-java-5.1.25-bin.jar
ダウンロードが完了したら、JDKのjrelibextの下に置きます.例えば、D:devJavajdk 1.7.0_21\jre\lib\ext
Java使用JDBC连接MySQL_第1张图片
(2)mysqlサービスの開始
net start mysql5

1.簡単な形式:
ConnMySql.java

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ConnMySql{
    public static void main(String[] args){
        Connection conn = null;
        Statement smt = null;
        ResultSet rs = null;
        try{
            //1.       
            Class.forName("com.mysql.jdbc.Driver");//   ClassNotFoundException
            //2.  DriverManager       
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root", "");//   SQLException
            System.out.println("       : "+conn);
            //3.  Connection     Statement  
            smt = conn.createStatement();//   SQLException
            //4.  Statement    SQL  
            /*
            Statement     sql     :
            1. execute     SQL  --    boolean 
                           ResultSet,   true,    false
            2. executeQuery   Select  --         
            3. executeUpdate     DML   DDL  --      ,  DML   SQL         ;  DDL    0
            */
            rs = smt.executeQuery("select * from test");//   SQLException
            //5.     
            /*
            ResultSet              
            1. next()         ,first(),last() 
            2. getXxx(   |  )          ,     
            */
            while(rs.next()){//   SQLException
                System.out.println(rs.getInt(1) + "\t"
                    + rs.getString(2)+"\t"
                    + rs.getString(3));
            }
        }
        catch(ClassNotFoundException e){
            e.printStackTrace();
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        finally{
            try{
                //6.        
                if(rs != null){
                    //  ResultSet
                    rs.close();//   SQLException
                }
                if(smt != null){
                    //  Statement
                    smt.close();
                }
                if(conn != null){
                    //  Connection
                    conn.close();
                }      
            }
            catch(SQLException e){
                e.printStackTrace();
            }  
        }
    }
}

実行結果:
Java使用JDBC连接MySQL_第2张图片
2.データベース接続情報をdbconfigに保存する.propertiesプロファイル
開発環境から本番環境にアプリケーションを移植する必要がある場合、ソースコードを変更する必要がなく、dbconfigを変更する必要があるというメリットがあります.propertiesプロファイルでいいです.
(1)dbconfig.properties(このファイルはoracle接続情報をmysqlの接続情報に変更する):

#DB_CLASS_NAME(         )
#DB_CLASS_NAME=oracle.jdbc.OracleDriver
DB_CLASS_NAME=com.mysql.jdbc.Driver

#DB_URL(         )
#DB_URL=jdbc:oracle:thin:@192.168.1.180:1521:orcl
DB_URL=jdbc:mysql://127.0.0.1:3306/test

#USERNAME(     )
#USERNAME=scott
USERNAME=root

#PASSWORD(  )
#PASSWORD=tiger
PASSWORD=

(2)DBConn 1.java(このファイルはOracle編と完全に一致しています):

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.io.FileInputStream;
import java.util.Properties;

public class DBConn1{
    private Connection conn = null;
    private Statement smt = null;
    private ResultSet rs = null;
    //private static String DB_CLASS_NAME = "oracle.jdbc.driver.OracleDriver";
    private static String DB_CLASS_NAME = "oracle.jdbc.OracleDriver";              //          
    private static String DB_URL = "jdbc:oracle:thin:@192.168.1.180:1521:orcl";    //   URL
    private static String USERNAME = "scott";                                      //         
    private static String PASSWORD = "tiger";                                      //  
    
    private static String propFileName = "dbconfig.properties";	                   //           
	private static Properties props = new Properties();
    
    public DBConn1(){
	    try{
	        //  Properties        
	        FileInputStream in=new FileInputStream(propFileName);
	        props.load(in);
	        DB_CLASS_NAME = props.getProperty("DB_CLASS_NAME");
	        DB_URL = props.getProperty("DB_URL");
	        USERNAME = props.getProperty("USERNAME");
	        PASSWORD = props.getProperty("PASSWORD");
	    }
	    catch (Exception e) {
	        e.printStackTrace();
	    }
	}
    
    /*       */
    private void getConn(){
        try{
            //1        
            Class.forName(DB_CLASS_NAME);
            //2   DriverManager       
            conn = DriverManager.getConnection(DB_URL, USERNAME, PASSWORD);
        }
        catch(ClassNotFoundException e){
            e.printStackTrace();
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        if(conn == null){
            System.err.println("  :DriverManager.getConnection()         .\r
\r
:" + DB_CLASS_NAME + "\r
:" + DB_URL); } //System.out.println(" : "+conn); } /* */ public ResultSet executeQuery(String sql){ getConn(); System.out.println("SQL:> "+sql);// , SQL try{ //3 Connection Statement smt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); //4 Statement SQL rs = smt.executeQuery(sql); } catch(SQLException e){ e.printStackTrace(); } return rs; } /* DDL DML */ public int executeUpdate(String sql){ int result = 0; getConn(); System.out.println("SQL:> "+sql);// , SQL try{ //3 Connection Statement smt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); //4 Statement SQL result = smt.executeUpdate(sql); } catch(SQLException e){ e.printStackTrace(); } return result; } /* */ public void close(){ if(rs != null){ // ResultSet try{ rs.close(); } catch(SQLException e){ e.printStackTrace(); } } if(smt != null){ // Statement try{ smt.close(); } catch(SQLException e){ e.printStackTrace(); } } if(conn != null){ // Connection try{ conn.close(); } catch(SQLException e){ e.printStackTrace(); } } //System.out.println(" : "+conn); } }

(3)DBConn1_Demo2.java:

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBConn1_Demo2{
    public static void main(String[] args){
        /* 1        */
        DBConn1 db1 = new DBConn1();
        String sql1 = "select * from test";//     sql         
        //  SQL  ,     
        ResultSet rs1 = db1.executeQuery(sql1);
        //     
        try{
            while(rs1.next()){
                System.out.println(rs1.getInt(1) + "\t"
                    + rs1.getString(2)+"\t"
                    + rs1.getString(3));
            }
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        db1.close();
        
        /* 2   DDL   */
        DBConn1 db2 = new DBConn1();
        String sql2 = "create table demo(id int, name varchar(10), age int)";
        int result1 = db2.executeUpdate(sql2);
        System.out.println(" " + result1 + "       !");
        db2.close();   
        
        /* 3        */
        DBConn1 db3 = new DBConn1();
        String sql3 = "insert into demo values(1, 'huhu', 18)";
        int result2 = db3.executeUpdate(sql3);
        System.out.println(" " + result2 + "       !");
        db3.close();
        
        /* 4        */
        DBConn1 db4 = new DBConn1();
        String sql4 = "select * from demo";//     sql         
        //  SQL  ,     
        ResultSet rs2 = db4.executeQuery(sql4);
        //     
        try{
            while(rs2.next()){
                System.out.println(rs2.getInt(1) + "\t"
                    + rs2.getString(2)+"\t"
                    + rs2.getString(3));
            }
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        db4.close();
        
        /* 5        */
        DBConn1 db5 = new DBConn1();
        String sql5 = "delete from demo";
        int result3 = db5.executeUpdate(sql5);
        System.out.println(" " + result3 + "       !");
        db5.close();
        
        /* 6        */
        DBConn1 db6 = new DBConn1();
        String sql6 = "select * from demo";//     sql         
        //  SQL  ,     
        ResultSet rs3 = db6.executeQuery(sql6);
        //     
        try{
            while(rs3.next()){
                System.out.println(rs3.getInt(1) + "\t"
                    + rs3.getString(2)+"\t"
                    + rs3.getString(3));
            }
        }
        catch(SQLException e){
            e.printStackTrace();
        }
        db6.close(); 
        
        /* 7   DDL   */
        DBConn1 db7 = new DBConn1();
        String sql7 = "drop table demo";
        int result = db7.executeUpdate(sql7);
        System.out.println(" " + result + "       !");
        db7.close();
    }	
}

実行結果:
Java使用JDBC连接MySQL_第3张图片