JAVA连接MYSQL类［长连接情况］

package cn.ty;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mysql.jdbc.PreparedStatement;
public class DBUtils {
    /**
     * 初始化的时候就会创建一个数据库连接。等处理完了之后再关闭
     * 情况：长连接
     * */
    private Connection conn = null;
    static {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block

            e.printStackTrace();
        }
    }
    public DBUtils() {
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123");
        } catch (SQLException e) {
            // TODO Auto-generated catch block

            e.printStackTrace();
        }
    }
    public void closeConnection() {
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block

            e.printStackTrace();
        }
        conn = null;
    }
    public ResultSet myResultSet(String sqls) {
        Statement statement = null;
        ResultSet rs = null;
        try {
            statement = conn.createStatement();
            rs = statement.executeQuery(sqls);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            while((e = e.getNextException()) != null) {
                System.out.println(e.getMessage());
            }

        } finally {
            statement = null;
        }
        return rs;
    }
    public int executeSQL(String sqls) {
        Statement statement = null;
        int count = 0;
        try {
            if (conn.getAutoCommit()) {
                conn.setAutoCommit(false);
            }
            statement = conn.createStatement();
            count = statement.executeUpdate(sqls);
            statement.executeUpdate("insert into innodb(name) values('e')");
            statement.executeUpdate("insert into innodb(names) values('e')");
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block

                e1.printStackTrace();
            }
            System.out.println(e.getMessage());
            while((e = e.getNextException()) != null) {
                System.out.println(e.getMessage());
            }

        } finally {
            statement = null;
        }
        return count;
    }
    public boolean executeMethod(String sqls) {
        Statement statement = null;
        boolean results = false;
        try {
            statement = conn.createStatement();
            results = statement.execute(sqls);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            while((e = e.getNextException()) != null) {
                System.out.println(e.getMessage());
            }

        } finally {
            statement = null;
        }
        return results;
    }
    private void init() throws SQLException {
        PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement("insert into innodb(name) value(?)");
        pstmt.clearParameters();
        pstmt.setString(1, "hko");
        pstmt.executeUpdate();
    }
    public static void main(String[] args) throws SQLException, InterruptedException {
        DBUtils tool = new DBUtils();
        long i=0;
        while(true) {
            System.out.println("**");
            tool.init();
            i++;
            if (i>100000) break;
        }
        tool.closeConnection();
    }
}


测试了发现这种情况非常适用于做长连接处理。需要频繁地操作数据的时候我们可以用这种情况。不要频繁地开关数据库！
下一个来看一下短连接的情况
以下是短连接的情况

package cn.ty;

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

import com.mysql.jdbc.PreparedStatement;

public class DBUtils {
    /**
     * 初始化的时候就会创建一个数据库连接。等处理完了之后再关闭
     * 情况：长连接
     * */
    static {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block

            e.printStackTrace();
        }
    }
    private Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root","123");
        } catch (SQLException e) {
            if ((e = e.getNextException()) != null) {
                System.out.println(e.getMessage());
            }
        }
        return conn;
    }
    
    
    public DBUtils() {
        super();
    }

    public ResultSet myResultSet(String sqls) {
        Statement statement = null;
        ResultSet rs = null;
        Connection conn = getConnection();
        try {
            statement = conn.createStatement();
            rs = statement.executeQuery(sqls);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            while((e = e.getNextException()) != null) {
                System.out.println(e.getMessage());
            }

        } finally {
            statement = null;
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception e) {
                    System.out.println(e.toString());
                }
            }
        }
        return rs;
    }
    public int executeSQL(String sqls) {
        Statement statement = null;
        int count = 0;
        Connection conn = getConnection();
        try {
            if (conn.getAutoCommit()) {
                conn.setAutoCommit(false);
            }
            statement = conn.createStatement();
            count = statement.executeUpdate(sqls);
            statement.executeUpdate("insert into innodb(name) values('e')");
            statement.executeUpdate("insert into innodb(names) values('e')");
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block

                e1.printStackTrace();
            }
            System.out.println(e.getMessage());
            while((e = e.getNextException()) != null) {
                System.out.println(e.getMessage());
            }

        } finally {
            statement = null;
            if(conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception e) {
                    // TODO: handle exception

                }
            }
            
        }
        return count;
    }
    private void init() throws SQLException {
        Connection conn = getConnection();
        PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement("insert into innodb(name) value(?)");
        pstmt.clearParameters();
        pstmt.setString(1, "hko");
        pstmt.executeUpdate();
        conn.close();
        conn=null;
    }
    
    public static void main(String[] args) throws SQLException, InterruptedException {
        DBUtils tool = new DBUtils();
        long i=0;
        while(true) {
            //System.out.println("**");

            tool.init();
            i++;
            if (i>10000) break;
        }
        
    }
}


测试发现如果使用短连接的话大概跑2000--3000次就会出现NullPointerException
请看：

长连接主要用于在少数客户端与服务端的频繁通信，因为这时候如果用短连接频 繁通信常会发生Socket出错，并且频繁创建Socket连接也是对资源的浪费。

但是对于服务端来说，长连接也会耗费一定的资源，需要专门的线程（unix下可以用进程管理）来负责维护连接状态。

看到没有。如果频繁地开关数据库连接的话就会很容易造成SOCKET出错。所以如果在像做什么数据分析爬虫入库等应用的时候因为是少数客户端而且是经常非常频繁地与服务器通信。所以肯定要使用长连接。即第一种方式了。如果是多个客户端与一个服务端通讯的话就使用短连接。处理完了一个再处理另外一个。

注意：如果我们想要返回一个ResultSet的方法。而这个方法里面的finally块有关闭数据库连接或语句连接的话其实我们的ResultSet对象已经被关闭掉了。所以返回来的值其实是null最好的办法是将其内容取出来放到List这样的数据结构里面去！