JDBC高级用法

JDBC高级用法

1、批处理的使用：同时执行多个sql语句。可以用事务进行管理。用法参考代码：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class BatchDemo {
	public static void main(String[] args) {
		final String driver="com.mysql.jdbc.Driver";
		try {
			Class.forName(driver);
			Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/sh","sh","123456");
			Statement stmt=conn.createStatement();			
			//关闭自动提交 默认自动提交事务，后续代码使用手动添加事务进行处理
			conn.setAutoCommit(false);
			//把以下三个insert语句作为一个批处理
			stmt.addBatch("insert into employee values(161,'李',27,15000,'欧洲',101,105)");
			stmt.addBatch("insert into employee values(162,'李',27,15000,'欧洲',101,105)");
			stmt.addBatch("insert into employee values(163,'李',27,15000,'欧洲',101,105)");			
			//执行批处理，接收每条语句的执行结果 result数组中会存在一组0和1
			int[] result=stmt.executeBatch();
			for(int i:result){
				System.out.println(i);
			}
			//提交事务
			conn.commit();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

2、元数据 MetaData：描述数据库中的一些数据。

1）DataBase MetaData：数据库的元数据。用法参考代码：

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class MetadataDemo {
	public static void main(String[] args) {
		final String driver="com.mysql.jdbc.Driver";
		try {
			Class.forName(driver);
			Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/sh","sh","123456");
			//数据的元数据：存放数据库的信息或者DBMS的信息
			DatabaseMetaData dameta=conn.getMetaData();
			System.out.println("驱动的名字"+dameta.getDriverName());
			System.out.println("驱动的主版本"+dameta.getDatabaseMajorVersion());
			System.out.println("驱动的小版本"+dameta.getDatabaseMinorVersion());
			ResultSet rs=dameta.getTables(null, null, "employee", new String[] {"TABLE"});
			//rs 结果集里每一列代表返回表中不同的信息
			while(rs.next()){
				System.out.println(rs.getString(1));
				System.out.println(rs.getString(2));
				System.out.println(rs.getString(3));//返回表名
				System.out.println(rs.getString(4));				
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

其中getTables返回的列包含以下数据：

1. TABLE_CAT String => table catalog (may be null)
2. TABLE_SCHEM String => table schema (may be null)
3. TABLE_NAME String => table name
4. TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
5. REMARKS String => explanatory comment on the table
6. TYPE_CAT String => the types catalog (may be null)
7. TYPE_SCHEM String => the types schema (may be null)
8. TYPE_NAME String => type name (may be null)
9. SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed table (may be null)
10. REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED". (may be null)

2）ResultBase MetaData：结果集的元数据。用法参考代码：

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class MetadataDemo {
    public static void main(String[] args) {
        final String driver="com.mysql.jdbc.Driver";
        try {
            Class.forName(driver);
            Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/sh","sh","123456");
            //结果集的元数据
            Statement stmt=conn.createStatement();
            ResultSet rs1=stmt.executeQuery("select * from employee");
            //获取结果集的元数据
            ResultSetMetaData rsMeta=rs1.getMetaData();
            System.out.println(rsMeta.getColumnCount());//返回结果集列数
            System.out.println(rsMeta.getColumnName(3));//返回结果集第三列的列名
            System.out.println(rsMeta.getTableName(2));//返回结果集第2列的表名
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}