(十二) 整合DEMO

package com.shaogq.review.jdbc.connectionDB;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class Main {

	private static final Log log = LogFactory.getLog(Main.class);
	
	public static void main(String[] args) {
		
		String url = "jdbc:mysql://localhost:3306/corejava";
		String username = "root";
		String password = "12345678";
		// 1.注册驱动器类
		// 1.(1)自动注册驱动器类(Java Standard Edition Service Provider)
		// 包含META-INF/services/java.sql.Driver文件的JAR文件可以自动注册
		// 1.(2)在JAVA程序中加载驱动器类
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		// 1.(3)通过设置jdbc.drivers属性,并用冒号将他们分割
		// System.setProperty("jdbc.drivers",
		// "com.mysql.jdbc.Driver:oracle.jdbc.driver.OracleDriver");
		// 1.(4)直接通过DriverManager.registerDriver
		// try {
		// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		// } catch (SQLException e) {
		// e.printStackTrace();
		// }

		try {
			// 2.建立数据库连接
			// 2.(1)建立Connection空对象
			Connection conn = null;
			// 3.建立Statement对象,用于执行静态SQL语句并返回它所生成的结果对象
			// 3.(1)建立Statement空对象
			Statement stat = null;
			try {
				// 2.(2)通过DriverManager.getConnection方法所获得的Connection对象
				conn = DriverManager.getConnection(url, username, password);
				// 7.(1)事务:关闭自动提交模式  
				conn.setAutoCommit(false);
				try {
					/**
					 * 3.(2)创建可滚动和可更新的结果集
					 * ResultSet的type值:
					 * TYPE_FORWARD_ONLY 结果集不能滚动; 
					 * TYPE_SCROLL_INSENSITIVE 结果集可以滚动，但对数据库的变化不敏感;
					 * TPYE_SCROLL_SENSITIVE 结果集可以滚蛋，且对数据库的变化敏感.
					 * 
					 * ResultSet的Concurrency值: 
					 * CONCUR_READ_ONLY 结果集不能用于更新数据库(默认值);
					 * CONCUR_UPDATABLE 结果集可以用于更新数据库.
					 * 
					 */
					Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
							ResultSet.CONCUR_READ_ONLY);

					// 4.准备要执行的SQL语句
					// 4.(1)executeUpdate 用于执行除SELECT外的DML(UPDATE、INSERT、DELETE)
					//      			    和DDL(CREATE、ALTER、DROP等);
					// 		executeUpdate 对于 SQL 数据操作语言 (DML) 语句，返回行计数 (2) 
					//					    对于什么都不返回的 SQL 语句，返回 0
					// 5.执行SQL语句
					// 5.(1)通过executeUpdate执行删表语句
					String DropTable = "DROP TABLE IF EXISTS test_table";
					int DropTableResult = stmt.executeUpdate(DropTable);
					log.info("DropTableResult = " + DropTableResult + "== 0");
					// 5.(2)通过executeUpdate执行建表表语句
					String createTable = "CREATE TABLE IF NOT EXISTS test_table ("
							+ "id  INT(11) NOT NULL AUTO_INCREMENT, "
							+ "name varchar(20), " + "PRIMARY KEY (`id`))";
					int createTableResult = stmt.executeUpdate(createTable);
					log.info("createTableResult = " + createTableResult + "== 0");
					// 5.(3)通过executeUpdate执行插入操作
					String insertData = "INSERT INTO test_table (name) VALUES('a')";
					int insertDataResult = stmt.executeUpdate(insertData);
					log.info("insertDataResult = " + insertDataResult + "== 0");
					
					// 3.(3)或预备语句PreparedStatement :
					// PreparedStatement prepStmt =
					// conn.prepareStatement(command, type, concurrency);
					String insertPreparedData = "INSERT INTO test_table (name) VALUES(?)";
					PreparedStatement prepStmt = conn.prepareStatement(
							insertPreparedData, ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
					String[] names = new String[]{"b", "c", "d"}; 
					// 5.(4)通过PreparedStatement执行批量插入操作
	                for(int i=0;i<names.length;i++){  
	                    prepStmt.setString(1, names[i]);  
	                    //将一组参数添加到此 PreparedStatement 对象的批处理命令中
	                    prepStmt.addBatch();  
	                }
	                
	                int[] executeBatchResults = prepStmt.executeBatch(); 
	                for(int i=0;i<executeBatchResults.length;i++){
	                	log.info(i + " value : " + executeBatchResults[i]);
	                }
	                prepStmt.close(); 

					// 4.(2)执行SELECT方法必须使用executeQuery方法
					String query = "SELECT id,name FROM test_table";
					// 5.(5)通过executeQuery查询结果集
					ResultSet queryResult = stmt.executeQuery(query);
					while(queryResult.next()){  
						log.info(queryResult.getInt(1) + " " +queryResult.getString(2));
					}  
					
					// 5.(6)通过execute进行查询,并确定是否存在结果集
					// 如果有,则通过元数据遍历结果集的表头和值
					log.info("execute");
					boolean hasResult = stmt.execute(query);
					if(hasResult){
						queryResult = stmt.getResultSet();
						// 6.(1)得到ResultSet的元数据
						ResultSetMetaData metaData = queryResult.getMetaData();
						int columnCount = metaData.getColumnCount();  
				        log.info("columnCount = " + columnCount);  

						while (queryResult.next()) {
							for (int i = 1; i <= columnCount; i++) {
								log.info("第" + i + "行 "
										+ metaData.getColumnLabel(i) + " = "
										+ queryResult.getString(i));
							}
						}
				          
				        queryResult.close();  
					}
					
					// 6.(2)得到Connection的元数据DatabaseMetaData,并打印表的信息
					log.info("DatabaseMetaData");
					DatabaseMetaData meta = conn.getMetaData();     
					// 从数据库连接中获取一个DatabaseMetaData对象
					ResultSet result = meta.getTables(null, null, null,
							new String[] { "TABLE" });
					while (result.next()) {
				    	//该结果集每行包含数据库中一张表的信息，第三列是表的名称。
				        log.info(result.getString(3));      
				    }  
				} catch (Exception e) {
					// 7.(2)事务:回滚
					conn.rollback();
					e.printStackTrace();
				}
			} finally {
				// 7.(2)事务:提交事务
				conn.commit();
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}