jdbc连接mysql的方法

c3p0,mybitas,hibernate,spring有关数据库的部分都是对jdbc的封装.所以只要把jdbc弄会了,再看那些框架心里就有底气.
孙猴子是跳不出如来佛手掌心的.
总的来说.jdbc连接数据库有2种方法,1种是用DriverManager,1种是DataSource.分开看.
1. DriverManager
DriverManager不支持连接池.是一种旧的用法.

public class DriveManagerTest {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        String cs = "jdbc:mysql://localhost:3306/testdb";
        String user = "root";
        String password = "@root1";
        try {
            con = DriverManager.getConnection(cs, user, password);
            st = con.createStatement();
            for (int i = 0; i < 3; i++) {
                String query = "INSERT INTO student(stu_num, stu_name) VALUES(" +
                        i + ",\"name" + i + "\")";
                System.out.println("sql=" + query);
                st.executeUpdate(query);
            }
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(DriveManagerTest.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(DriveManagerTest.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
        }
    }
}

2. DataSource
2.1 基本DataSource
对于mysql而言,基本的DataSource的实现是MysqlDataSource.代码如下:

public class MySqlDataSource {
    public static void main(String[] args) {
        Connection con = null;
        try {
            // Setting up the DataSource object
            MysqlDataSource ds
                    = new MysqlDataSource();
            ds.setServerName("localhost");
            ds.setPortNumber(3306);
            ds.setDatabaseName("testdb");
            ds.setUser("root");
            ds.setPassword("@root1");

            // Getting a connection object
            con = ds.getConnection();

            // Getting database info
            DatabaseMetaData meta = con.getMetaData();
            System.out.println("Server name: "
                    + meta.getDatabaseProductName());
            System.out.println("Server version: "
                    + meta.getDatabaseProductVersion());

            // Closing the connection
            con.close();
        } catch (Exception e) {
            System.err.println("Exception: " + e.getMessage());
        }
    }
}

基本的DataSource功能和DriverManager一样,也不支持连接池.
2.2 ConnectPoolDataSource
支持连接池.

public class MysqlConPoolDataSourceTest {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        MysqlConnectionPoolDataSource ds = null;
        try {
            ds = new MysqlConnectionPoolDataSource();
            ds.setURL("jdbc:mysql://localhost:3306/testdb");
            ds.setUser("root");
            ds.setPassword("@root1");
            con = ds.getConnection();
            st = con.createStatement();
            for (int i = 0; i < 3; i++) {
                String query = "INSERT INTO student(stu_num, stu_name) VALUES(" +
                        i + ",\"name" + i + "\")";
                System.out.println("sql=" + query);
                st.executeUpdate(query);
            }
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(DriveManagerTest.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } finally {
            try {
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger lgr = Logger.getLogger(DriveManagerTest.class.getName());
                lgr.log(Level.SEVERE, ex.getMessage(), ex);
            }
        }
    }
}

2.3 XADataSource
支持分布式事务.

public class MysqlXADataSourceTest {
    public static XADataSource getXADataSource() {
        MysqlXADataSource dataSource = new MysqlXADataSource();
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/testdb");
        dataSource.setUser("root");
        dataSource.setPassword("@root1");
        return dataSource;
    }
    public static XAConnection getXAConnetion(XADataSource dataSource) {
        XAConnection XAConn = null;
        try {
            XAConn = dataSource.getXAConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return XAConn;
    }
    public static Connection getConnection(XAConnection XAConn) {
        Connection conn = null;
        try {
            conn = XAConn.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    public static void closeConnection(Connection conn) {
        try {
            conn.close();
        } catch (SQLException e) {
            System.out.println("连接关闭失败");
        }
    }
    public static void main(String[] args) {
        XADataSource dataSource = MysqlXADataSourceTest.getXADataSource();
        XAConnection xaConn = MysqlXADataSourceTest.getXAConnetion(dataSource);
        xaConn.addConnectionEventListener(new ConnectionEventListener() {
            public void connectionClosed(ConnectionEvent event) {
                System.out.println("连接被关闭");
            }
            public void connectionErrorOccurred(ConnectionEvent event) {
                System.out.println("连接发生错误");
            }
        });
        Connection conn = MysqlXADataSourceTest.getConnection(xaConn);
        try {
            Statement statement=conn.createStatement();
            String query = "INSERT INTO student(stu_num, stu_name) VALUES" +
                   "(1000, 'name1')";
            statement.executeUpdate(query);
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        try {
            //关闭连接，但物理连接没有关闭，
            closeConnection(conn);
            //再次获得连接；
            conn=xaConn.getConnection();
            Statement statement2=conn.createStatement();
            String query = "INSERT INTO student(stu_num, stu_name) VALUES" +
                    "(1000, 'name1')";
            statement2.executeUpdate(query);
        } catch (SQLException e) {
            System.out.println("关闭连接失败");
        }
    }
}