JDBC 连接示例

创建JDBC的基础类

 

import java.sql.Connection;
import java.sql.DriverManager;

/**
 * 2012-8-9 tracy.liuy
 */
public class DbConnection {


    public Connection conn = null;

    public DbConnection(String driver, String url, String user, String password){
        try {
            // 注册驱动程序类
            Class.forName(driver);
            // 1、初始化连接对象
            conn = DriverManager.getConnection(url, user, password);
            // 2、设置连接对象的自动提交模式
            conn.setAutoCommit(false);
        } catch (Exception e) {
            throw new RuntimeException("get connect failed", e);
        }
    }
}

 

连接Mysql数据库的示例

 

import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.io.BufferedReader;


/**
 * 2012-8-9 tracy.liuy
 */
public class TestMysql {

    public static void main(String[] args) {
    
        DbConnection dbC = new DbConnection("com.mysql.jdbc.Driver", "address",
                                            "username", "password");

        Statement stat = null;

       //要执行的sql语句从文件中读出来
        File f = new File("e:/mysql.update_customers.sql");

        BufferedReader br = null;

        Date date = null;

        List<String> lines = new LinkedList<String>();
        String line;
        try {
            stat = dbC.conn.createStatement();
            br = new BufferedReader(new FileReader(f));
            line = br.readLine();
            while (line != null) {
                lines.add(line);
                line = br.readLine();
            }

            System.out.println("Start");

            date = new Date();

            for (String s : lines) {
                stat.executeUpdate(s);
            }

            //connection设置为自动不提交，所以手动提交
            dbC.conn.commit();

        } catch (IOException e) {

            e.printStackTrace();
        } catch (SQLException e) {

            e.printStackTrace();
        } finally {
            try {
                dbC.conn.close();
                br.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        System.out.println("end. Duration is " + getDuration(date) + ", line is " + lines.size());

    }

    public static String getDuration(Date startTime) {
        long duration = new Date().getTime() - startTime.getTime();
        return duration / (1000 * 60) + "分" + (duration % (1000 * 60)) / 1000 + "秒";
    }

}

 

连接Oracle的示例

 

 

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

/**
 * 2012-8-9 tracy.liuy
 */
public class TestOracle {

    public static void main(String[] args) {

        DbConnection dbC = new DbConnection("oracle.jdbc.OracleDriver", "jdbc:oracle:thin:@ip:port:sid",
                                            "username", "password");

        Statement stat = null;

        File f = new File("e:/oracle.update_customers.sql");

        BufferedReader br = null;
        Date date = null;

        List<String> lines = new LinkedList<String>();
        String line;
        try {
            stat = dbC.conn.createStatement();
            br = new BufferedReader(new FileReader(f));
            line = br.readLine();
            while (line != null) {
                lines.add(line);
                line = br.readLine();
            }

            System.out.println("Start");

            date = new Date();

            for (String s : lines) {
                stat.executeUpdate(s);
            }

            dbC.conn.commit();

        } catch (IOException e) {

            e.printStackTrace();
        } catch (SQLException e) {

            e.printStackTrace();
        } finally {
            try {
                dbC.conn.close();
                br.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        System.out.println("end. Duration is " + getDuration(date) + ", line is " + lines.size());

    }

    public static String getDuration(Date startTime) {
        long duration = new Date().getTime() - startTime.getTime();
        return duration / (1000 * 60) + "分" + (duration % (1000 * 60)) / 1000 + "秒";
    }

}