Java Jdbc编程优化练习

本篇博文（以SQLServer数据库为例）回答了以下几个问题：
对于JDBC的编程实践，
1、如何快速向表中插入100万条不同的记录？
2、如何快速删除表中所有的记录？
3、对于100万条数据，如何根据组合字段快速查询出满足要求的记录。

结论：
1、使用PreparedStatement批处理式地插入数据，并启用事务来管理提交。需要通过调用setAutoCommit(false)把连接的提交行为改为手动提交。批处理有两重循环，当一个内循环执行完成后，将调用PreparedStatement的executeBatch()方法，应该注意内循环的循环次数，如果设置得过大将增加Java虚拟机的负担，设置得过小就不能体现出批处理的优势。在executeBatch()后添加了Connection的方法调用commit()。将多条语句放在一个事务中将减少数据库对事务的处理，让数据库能得到更快的响应，提高了效率。
2、TRUNCATE TABLE提供了一个快速清空表中数据的命令。TRUNCATE比DELETE快的原因：TRUNCATE TABLE针对的是表和页来使用日志和锁，而DELETE针对的是行。如果在执行Delete语句时，也加入事务控制的话，那性能也会提高一半
3、先在表上为组合字段建立INDEX，然后使用方法I来执行查询，在SQL Server 上创建索引的步骤可以上网百度下。然后使用PreparedStatement查询。

假设现在新建一张表：

CREATE TABLE LocationMapping (
		LocationId            varchar (50)    NOT NULL,
		LocationPath          varchar (200)   NOT NULL,
		Rack                 int    NULL,
		Shelf                 int    NULL,
		Slot                  int    NULL,
		Port                  int    NULL,
		IpAddress             varchar (32)    NULL
)

完成：
1、向表LocationMapping中插入一条数据，并可以查询显示LocationMapping表中的所有数据
对于2、3、4,如何编码能够达到最快并实现，并计算任务执行的耗时
2、向表插入100万条不同的数据
3、删除刚才插入的100万条不同的数据
4、对100万条数据，根据“rack+shelf+slot”组合查询，查出满足条件的记录

代码示例：
整个代码涉及的功能比较全面，还包括资源的连接和释放也比较严谨，适合学习和复习相关知识。

package test;
import java.sql.*;
import java.util.Date;

public class JDBCTestDemo
{
     private static Connection getConn() {
        Connection con = null;
        String driverName="com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String dbURL="jdbc:sqlserver://机器名称\\SQLSERVER:1433;DatabaseName=UEP";//数据源
        String Name="sa";
        String Pwd="";
        try {
            Class.forName(driverName);
            con = DriverManager.getConnection(dbURL, Name, Pwd);
            System.out.println("连接数据库成功!");
        }catch (Exception e) {
            e.printStackTrace();
            System.out.println("连接数据库失败!");
        }
        return con;
    }
     
    private static void closeConn(Connection con) {
    	
		try {
			if(con != null) {
				con.close();
				con = null;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}

    }
    
    private static void closePst(PreparedStatement pStat) {
    	try {
			if(pStat != null) {
				pStat.close();
				pStat = null;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
    }
    
    private static void closeRS(ResultSet rs) {
    	try {
    		if(rs != null) {
    			rs.close();
    			rs = null;
    		}
    	}catch(SQLException e) {
    		e.printStackTrace();
    	}
    }
    
    private static void insertOne() {
    	Connection con = getConn();
    	PreparedStatement pStat = null;
    	if(con == null) return;
        try {
            String sql = "insert into LocationMapping values (?,?,?,?,?,?,?)";
            pStat = con.prepareStatement(sql);
            pStat.setString(1, "id1000001");
            pStat.setString(2, "path1000001");
            pStat.setInt(3, 1000001);
            pStat.setInt(4, 1000001);
            pStat.setInt(5, 1000001);
            pStat.setInt(6, 1000001);
            pStat.setString(7, "ip1000001");
            pStat.execute();
            
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
        	closePst(pStat);
        	closeConn(con);
        	System.out.println("数据库连接已关闭");
        }
    }
    
    private static void selectAll() {
    	Connection con = getConn();
    	PreparedStatement pStat = null;
    	ResultSet rs = null;
    	if(con == null) return;
    	try {
    		String sql = "select * from LocationMapping";
            pStat = con.prepareStatement(sql);
            rs = pStat.executeQuery();
            System.out.println("LocationId	LocationPath	Rack	Shelf	Slot	Port	IpAddress");
            System.out.println("-----------------------------------------------------------------------");
            while(rs.next()) {
            	String LocationId = rs.getString("LocationId");
            	String LocationPath = rs.getString("LocationPath");
            	int Rack = rs.getInt("Rack");
            	int Shelf = rs.getInt("Shelf");
            	int Slot = rs.getInt("Slot");
            	int Port = rs.getInt("Port");
            	String IpAddress = rs.getString("IpAddress");
            	System.out.println(LocationId + "		" + LocationPath + "		"+Rack +"		"+  Shelf +"		"+ Slot +"		"+ Port +"		"+ IpAddress);	
            }
    	}catch(SQLException e) {
    		e.printStackTrace();
    	}finally {
        	closeRS(rs); 
        	closePst(pStat);
        	closeConn(con);
        	System.out.println("数据库连接已关闭");
    		
    	}
    }
    
    private static void insertBatch() {
    	Connection con = getConn();
    	PreparedStatement pStat = null;
    	if(con == null) return;
        Long begin = new Date().getTime();
        String sql = "insert into LocationMapping values (?,?,?,?,?,?,?)";
        int cnt = 0;
    	try {
    		con.setAutoCommit(false);
    		pStat = con.prepareStatement(sql);
    		int acc = 0;
    		for(int i=0;i<100;i++) {
    			for(int j=0;j<10000;j++) {
    				pStat.setString(1, "id"+acc);
    				pStat.setString(2, "path"+acc);
    				pStat.setInt(3, acc);
    	            pStat.setInt(4, acc);
    	            pStat.setInt(5, acc);
    	            pStat.setInt(6, acc);
    	            pStat.setString(7, "ip"+acc);
    	            pStat.addBatch();
    	            acc++;
    			}
    			 int[] updatedArray = pStat.executeBatch();
    			 cnt += updatedArray.length;
    			 con.commit();
    			 pStat.clearBatch();
    		}
    	}catch(SQLException e) {
            e.printStackTrace();
        }finally {
        	closePst(pStat);
        	closeConn(con);
        	System.out.println("数据库连接已关闭");
        }
        Long end = new Date().getTime();
        System.out.println("插入"+cnt+"条数据耗时(秒) : " + (end - begin) / 1000.0 + " s");
    	
    }
    private static void deleteAll() {
    	Connection con = getConn();
    	PreparedStatement pStat = null;
    	if(con == null) return;
    	Long begin = new Date().getTime();
        //String sql = "delete from LocationMapping";
    	String sql = "truncate table LocationMapping";
    	try {
    		
    		pStat = con.prepareStatement(sql);
    		pStat.executeUpdate();
    		
    	}catch(SQLException e) {
            e.printStackTrace();
        }finally {
        	closePst(pStat);
        	closeConn(con);
        	System.out.println("数据库连接已关闭");
        }
        Long end = new Date().getTime();
        System.out.println("删除100万条数据耗时(秒) : " + (end - begin) / 1000.0 + " s");
    }
    
//    private static void deleteBatch(Connection con) {
//    	Long begin = new Date().getTime();
//        String sql = "delete from LocationMapping where LocationId = ?";
//        int cnt = 0;
//    	try {
//    		con.setAutoCommit(false);
//    		PreparedStatement pStat = con.prepareStatement(sql);
//    		int acc = 0;
//    		
//    		for(int i=0;i<100;i++) {
//    			for(int j=0;j<10000;j++) {
//    				pStat.setString(1, "id"+acc);
//    				pStat.addBatch();
//    	            acc++;
//    			}
//    			 int[] updatedArray = pStat.executeBatch();
//    			 cnt += updatedArray.length;
//    			 con.commit();
//    			 pStat.clearBatch();
//    		}
//    		pStat.close();
//            con.close();
//    	}catch(SQLException e) {
//            e.printStackTrace();
//        }
//        Long end = new Date().getTime();
//        System.out.println("删除"+cnt+"条数据耗时(秒) : " + (end - begin) / 1000.0 + " s");
//    }
    private static void selectOne() {
    	Connection con = getConn();
    	PreparedStatement pStat = null;
    	if(con == null) return;
    	Long begin = new Date().getTime();
        String sql = "SELECT * FROM LocationMapping WHERE Rack=? AND Shelf=? AND Slot=?";
        int loopTimes = 1;
        int start = 10000;
        try {
        	pStat = con.prepareStatement(sql);
        	for (int i = 0; i < loopTimes; i++)
        	{
        		pStat.setInt(1, start);
        	    pStat.setInt(2, start);
        	    pStat.setInt(3, start);
        	    pStat.executeQuery();
        	    start++;
        	}
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
        	closePst(pStat);
        	closeConn(con);
        	System.out.println("数据库连接已关闭");
        }
        Long end = new Date().getTime();
        System.out.println("查询"+loopTimes+"条数据耗时(秒) : " + (end - begin) / 1000.0 + " s");
    }
    
    public static void main(String[] args) {
    	deleteAll();
        insertOne();
        selectAll();
        insertBatch();
        deleteAll();
        insertBatch();
        selectOne();
    }

}