JAVA基础--JDBC
一、JDBC
一、JDBC概述
JDBC和ODBC都基于一个思想：根据API编写的程序都可以与驱动管理器进行通信，而驱动管理器则通过驱动程序和实际的数据库进行通信。
在三层应用模式中，客户端不直接调用数据库，而是调用服务器上的中间件层，最后由中间层完成对数据库的操作
连接到数据库：
String driver = "com.mysql.jdbc.Driver"; String dbName = "bookdb"; String passwrod = "root"; String userName = "123456"; String url = "jdbc:mysql://localhost:3308/" + dbName; String sql = "select * from users"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, userName, passwrod); 4.关闭数据库： 操作完成以后要把所有使用的JDBC对象全都关闭，以释放JDBC资源，关闭顺序和声 明顺序相反： 1、关闭记录集 2、关闭声明 3、关闭连接对象 if(rs != null){ // 关闭记录集 try{ rs.close() ; }catch(SQLException e){ e.printStackTrace() ; } } if(stmt != null){ // 关闭声明 try{ stmt.close() ; }catch(SQLException e){ e.printStackTrace() ; } } if(conn != null){ // 关闭连接对象 try{ conn.close() ; }catch(SQLException e){ e.printStackTrace() ; } }二、执行SQL语句
创建Statement对象 Statement st = conn.createStatement();
调用Statement接口executeUpdate方法st.executeUpdate(sql);
1】此方法返回受SQL命令影响的行数，或者如果没影响即返回0.
2】此方法可以执行INSERT、UPDATE、DELETE、CREATE、TABLE、DROP.但是不可以执行SELECT查询
execute方法可以执行任意的SQL语句。
executeQuery方法可以执行查询操作，返回一个ResultSet类型的对象。
ResultSet rs = st.executeQuery("SELECT * from table");
while(re.next()){
System.out.println("ID:"+rs.getInt("ID")+"name :"+rs.getString(2));
}
rs代表一行，通过数字型参数和字符串参数可以获得到数据。索引是从1开始
5.每个Connection对象都可以创建一个或多个Statement对象。同一个Statement对象可以用于多个不相干的命令和查询，但是一个Statement对象最多只能有一个打开的结果集。
三、执行查询操作
预备语句
String sql = "insert into students (Name,Sex,Age) values(?,?,?)";
PreparedStatement pstmt;
pstmt = (PreparedStatement)conn.prepareStatement(sql);//使用预处理的方式创建对象
pstmt.setString(1, student.getName());//第一个？号的内容
pstmt.setInt(2, student.getSex());
pstmt.setDate(3, student.getAge());
int r = pstmt.executeUpdate();//执行SQL 语句，更新数据库
变量用？来表示，通过setXXX来对变量赋值。如果是查询那就调用pstmt.executeQuery();得到一个ResultSet
2.读写LOB：
数据库还可以存储大对象，二进制大对象成为BLOB，字符型大对象成为CLOB。
四、可滚动和可更新的结果集
1.设置
要让ResultSet可以滚动个和更新，必须在创建Statement对象的时候使用下面的方式指定对应的参数：
Statement stmt = conn.createStatement(type, concurrency);
对于PreparedStatement，使用下面的方式指定参数：
PreparedStatement pstmt = conn.prepareStatement(sql, type, concurrency);
其中，type表示ResuleSet的类型，而concurrency表示是否可以使用ResuleSet来更新数据库。
type和concurrency的取值以及含义：
ResultSet.TYPE_FORWARD_ONLY - 结果集不能滚动，这事默认值；
ResultSet.TYPE_SCROLL_INSENSITIVE - 结果集可以滚动，但ResuleSet对数据库中发送的数据改变不敏感；
ResultSet.TYPE_SCROLL_SENSITIVE - 结果集可以滚动，并且ResuleSet对数据库中发生的改变敏感
ResultSet.CONCUR_READ_ONLY - 只读结果集，不能用于更新数据库；
ResultSet.CONCUR_UPDATABLE - 可更新结果集，可以用于更新数据库；
当使用TYPE_SCROLL_INSENSITIVE或者TYPE_SCROLL_SENSITIVE来创建Statement对象时，可以使用ResultSet 的 first()/last()/beforeFirst()/afterLast()/relative()/absolute()等方法在结果集中随意前后移动。
提示：即使使用了CONCUR_UPDATABLE参数来创建Statement，得到的记录集也并非一定是"可更新的"，如果你的记录集来自于合并查询，即该查询的结果来自多个表格，那么这样的结果集就可能不是可更新的结果集。可以使用ResuleSet类的getConcurrency()方法来确定是否为可更新的的结果集。
如果结果集是可更新的，那么可使用ResultSet的updateRow(),insertRow(),moveToCurrentRow(),deleteRow(),cancelRowUpdates() 等方法来对数据库进行更新。
如果没有设置可更新结果集 那将抱com.microsoft.sqlserver.jdbc.SQLServerException: 结果集不可更新
【方法】
next()，使游标向下一条记录移动。
previous() ，使游标上一条记录移动，前提前面还有记录。
absolute(int row)，可以使用此方法跳到指定的记录位置。定位成功返回true，
不成功返回false，返回值为false，则游标不会移动。
afterLast() ，游标跳到最后一条记录之后。
beforeFirst() ，游标跳到第一条记录之前。（跳到游标初始位）
first()，游标指向第一条记录。
last()，游标指向最后一条记录。
relative(int rows) ，相对定位方法，参数值可正可负，参数为正，游标从当前位置向下移动指定值，参数为负，
游标从当前位置向上移动指定值。
2.更新
【1】Statement stm = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
这样调用executeQuery得到的结果集就是可更新的结果集
【2】插入新行：
ResultSet结果集中，1.先使用moveToInsertRow()，将游标移到和结果集结构类似的缓冲区中
2.然后可以使用updateXxx(int column,columnType value)方法来更新指定列数据，
3.再使用insertRow() 方法插入记录，
4.最后将游标指回原位，moveToCurrentRow() 。
rs.first();
//将光标移动到插入行上
rs.moveToInsertRow();
//构建行数据
rs.updateString(2, "xxxx");
rs.updateInt(3, "x");
//插入一行
rs.insertRow();
五、行集
1）行集是从表格式数据源中检索出来的一行或多行数据：
与结果集(ResultSet)类似(RowSet接口继承了ResultSet接口)
但是使用结果集时与数据库的连接不能断开，而行集是可以在关闭连接的情况下存在的，行集一般在关闭连接的情况下使用，只有在进行一些特殊操作时需要才建立连接。
2）行集中的数据来源：
使用JDBC驱动程序从数据库检索的数据、从其他数据源获得的数据，如文件数据
行集(Row Set)的优点：
1）可以断开数据库连接操作数据
2）可以在分布式系统中的不同组件之间传递
3）默认可更新，可滚动，可序列化，可以方便的在网络间传输
4）可以方便的使数据在行集与JavaBean对象之间进行转换
行集中的一行数据可以封装为一个JavaBean对象
JavaBean是一个类，类中有若干私有属性，然后有与属性相关的公有的get和set方法。这样，我们可以将一行数据存入一个JavaBean对象中
行集相关接口：
javax.sql.RowSet：所有行集的父接口
Java.sql.rowset.CachedRowSet：数据行容器，可以在内存中缓存各行数据，在对数据进行操作时不需要连接到数据源。可以修改CachedRowSet对象中的数据，这些修改随后可以被更新到数据库。同时，也是一个JavaBean组件，可滚动，可更新，可序列化。
java.sql.rowset.JDBCRowSet：数据行容器，可滚动，可更新。始终连接到数据库。
java.sql.rowset.WebRowSet：被缓存的行集，该行集数据可以保存为xml文件。
java.sql.rowset.JoinRowSet：数据行容器，这些数据取自那些形成SQL JOIN关系的RowSet对象，有连接和无连接的RowSet对象都可成为JOIN的一部分。
java.sql.rowset.FilteredRowSet：数据行容器，可以对数据进行条件过滤。
行集的填充：
1）传统JDBC方式
Class.forName("com.mysql.jdbc.Driver");
String connectionUrl = "jdbc:mysql://localhost:3306/test?user=root&password=root ";
Connection connection = DriverManager.getConnection(connectionUrl);
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
CachedRowSetImpl rowset = new CachedRowSetImpl();//得到行集
rowset.populate(rs);
rs.close(); statement.close();connection.close();
2）设置行集属性连接数据库并检索数据
CachedRowSetImpl rowset = new CachedRowSetImpl();
rowset.setUrl("jdbc:mysql://127.0.0.1:3306/test");
rowset.setUsername("root");
rowset.setPassword("test");
rowset.setCommand("select * from student");
rowset.execute();
-------------------------------------------------------------------------------------------------------------------------------
RowSet的使用：
1、以下行集类使用Java的默认实现类
CachedRowSetImpl rowset = new CachedRowSetImpl();
//CachedRowSetImpl是SUN定义的CachedRow接口默认实现类
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("select * from table1");
rowset.populate(rs); //填充行集
rs.close(); statement.close();connection.close(); //关闭连接
//显示CachedRow数据，使用从结果集继承的方法
while (rowset.next()) {
System.out.print(rowset.getString(1) + " : "); System.out.println(rowset.getString("CompanyName"));
}
2、更新CachedRow数据(conn.setAutoCommit(false);)
crs.last();
crs.updateShort(3, 58);
crs.updateInt(4, 150000);
crs.updateRow(); //更新行集
crs.acceptChanges(conn); //更新数据库
注意事项：
使用行集修改数据与行集中的数据填充方式无关。但是要保证acceptChanges()方法执行时有可用的连接对象，如果行集中有可用的连接对象可以调用acceptChanges()，如果行集中没有可用的连接对象，需要调用acceptChanges(Connection)方法。
使用行集对数据进行添加、修改、删除时，必须保证事务提交为非自动提交(acceptChanges()方法会调用commit()方法)。
3、添加数据
crs.setTableName("student"); //添加数据必须指定
crs.moveToInsertRow(); //标识指针的位置
crs.updateInt("id",33); //添加数据时主键必须指定
crs.updateString("name", "Shakespeare");
crs.updateShort("age", 58);
crs.insertRow(); //更新行集
crs.moveToCurrentRow(); //让指针回到标识的位置
crs.acceptChanges(conn); //更新数据库
//删除数据
crs.first();
crs.deleteRow(); //删除行集数据
crs.acceptChanges(conn); //更新数据库
4、分页1：使用结果集填充行集
rs = stm.executeQuery("select * from student");
crs.setPageSize(4); //设置每页行数
crs.populate(rs, 10); //从结果集的第十行开始取4行数据填充行集
…
crs.nextPage(); //获得后续页数据，如果有数据返回true
…
注意：
此时resultset，statement(preparedstatement)，connection不能关闭，否则行集无法取得后续页数据
5、分页2：行集建立连接从数据库读取数据
CachedRowSetImpl crs= new CachedRowSetImpl();
crs.setUrl("jdbc:mysql://127.0.0.1:3306/test");
crs.setUsername("root");
crs.setPassword("root");
crs.setCommand("select * from student");
crs.setPageSize(4); //每页行数，一定要在execute()方法执行前设置，否则无效
crs.execute();
……
crs.nextPage(); //获得下一页的数据，与结果集，连接对象无关
public class JDBC3 { public static void main(String[] args) { Connection conn=null; PreparedStatement pst=null; ResultSet rs=null; DBUtil util=new DBUtil(); String sql="select*from score"; try { conn=util.getConn(); pst=conn.prepareStatement(sql); //必须设置非自动提交 conn.setAutoCommit(false); rs=pst.executeQuery(); //创建行集实例 CachedRowSetImpl rowset=new CachedRowSetImpl(); //填充 rowset.populate(rs); rs.close(); pst.close(); rowset.absolute(5); //rowset.updateInt("id", 5); rowset.updateInt("English", 55); //更新 rowset.updateRow(); //提交 rowset.acceptChanges(conn); //输出结果集之前，关闭连接 conn.close(); //输出行集数据 System.out.println("id\tChinese\tEnglish\thistory"); while(rowset.next()){ System.out.print(rowset.getInt("id")+"\t"); System.out.print(rowset.getInt("Chinese")+"\t"); System.out.print(rowset.getInt("English")+"\t"); System.out.println(rowset.getInt("history")); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } }public class JDBC4 { public static void main(String[] args) { try { CachedRowSetImpl rowset=new CachedRowSetImpl(); rowset.setUrl("jdbc:mysql://localhost:3308/test"); rowset.setUsername("root"); rowset.setPassword("123456"); rowset.setCommand("select*from score;"); rowset.execute(); //输出行集数据 System.out.println("id\tChinese\tEnglish\thistory"); while(rowset.next()){ System.out.print(rowset.getInt("id")+"\t"); System.out.print(rowset.getInt("Chinese")+"\t"); System.out.print(rowset.getInt("English")+"\t"); System.out.println(rowset.getInt("history")); } } catch (SQLException e) { e.printStackTrace(); } } }public class JDBC6 { public static void main(String[] args) { try { CachedRowSetImpl rowset=new CachedRowSetImpl(); rowset.setUrl("jdbc:mysql://localhost:3308/test"); rowset.setUsername("root"); rowset.setPassword("123456"); rowset.setCommand("select*from score;"); //设置每页显示的数据条数 rowset.setPageSize(3); rowset.execute(); int i=2; System.out.println("第1页"); System.out.println("id\tChinese\tEnglish\thistory"); while(rowset.next()){ System.out.print(rowset.getInt("id")+"\t"); System.out.print(rowset.getInt("Chinese")+"\t"); System.out.print(rowset.getInt("English")+"\t"); System.out.println(rowset.getInt("history")); } while(rowset.nextPage()){ System.out.println("第"+i+"页"); i++; System.out.println("id\tChinese\tEnglish\thistory"); while(rowset.next()){ System.out.print(rowset.getInt("id")+"\t"); System.out.print(rowset.getInt("Chinese")+"\t"); System.out.print(rowset.getInt("English")+"\t"); System.out.println(rowset.getInt("history")); } } } catch (SQLException e) { e.printStackTrace(); } } }六、事务和元数据
事务是指一个工作单元，它包含了一组添加，删除，修改等数据操作命令，这组命令作为一个整体向系统提交执行，要么都执行成功，要么全部恢复
在JDBC中使用事务
1）con.setAutoCommit(false)，取消自动提交
2）对数据库执行一个或多个操作(一个或多个SQL语句)
3）con.commit()，提交事务(上面的第二部的多个操作就作为一个整体提交执行)
4）如果某个操作失败，通过con.rollback()回滚所有操作(撤销以上的操作，将数据恢复为执行前状态)
事务处理依赖于底层的数据库实现，不同的驱动程序对事务处理的支持程度可能不同
【事务特性】
1.事务(Transaction)的四个属性(ACID)
原子性(Atomic) 对数据的修改要么全部执行，要么全部不执行。
一致性(Consistent) 在事务执行前后，数据状态保持一致性。
隔离性(Isolated) 一个事务的处理不能影响另一个事务的处理。
持续性(Durable) 事务处理结束，其效果在数据库中持久化。
2.事务并发处理可能引起的问题
脏读(dirty read) 一个事务读取了另一个事务尚未提交的数据，
不可重复读(non-repeatable read) 一个事务的操作导致另一个事务前后两次读取到不同的数据
幻读(phantom read) 一个事务的操作导致另一个事务前后两次查询的结果数据量不同。
举例：
事务A、B并发执行时，
当A事务update后，B事务select读取到A尚未提交的数据，此时A事务rollback，则B读到的数据是无效的"脏"数据。
当B事务select读取数据后，A事务update操作更改B事务select到的数据，此时B事务再次读去该数据，发现前后两次的数据不一样。
当B事务select读取数据后，A事务insert或delete了一条满足A事务的select条件的记录，此时B事务再次select，发现查询到前次不存在的记录("幻影")，或者前次的某个记录不见了。
JDBC的事务支持
JDBC对事务的支持体现在三个方面：
1.自动提交模式(Auto-commit mode)
Connection提供了一个auto-commit的属性来指定事务何时结束。
a.当auto-commit为true时，当每个独立SQL操作的执行完毕，事务立即自动提交，也就是说每个SQL操作都是一个事务。
一个独立SQL操作什么时候算执行完毕，JDBC规范是这样规定的：
对数据操作语言(DML，如insert,update,delete)和数据定义语言(如create,drop)，语句一执行完就视为执行完毕。
对select语句，当与它关联的ResultSet对象关闭时，视为执行完毕。
对存储过程或其他返回多个结果的语句，当与它关联的所有ResultSet对象全部关闭，所有update count(update,delete等语句操作影响的行数)和output parameter(存储过程的输出参数)都已经获取之后，视为执行完毕。
b. 当auto-commit为false时，每个事务都必须显示调用commit方法进行提交，或者显示调用rollback方法进行回滚。auto-commit默认为true。
JDBC提供了5种不同的事务隔离级别，在Connection中进行了定义。
2.事务隔离级别(Transaction Isolation Levels)
JDBC定义了五种事务隔离级别：
TRANSACTION_NONE JDBC驱动不支持事务
TRANSACTION_READ_UNCOMMITTED 允许脏读、不可重复读和幻读。
TRANSACTION_READ_COMMITTED 禁止脏读，但允许不可重复读和幻读。
TRANSACTION_REPEATABLE_READ 禁止脏读和不可重复读，单运行幻读。
TRANSACTION_SERIALIZABLE 禁止脏读、不可重复读和幻读。
3.保存点(SavePoint)
JDBC定义了SavePoint接口，提供在一个更细粒度的事务控制机制。当设置了一个保存点后，可以rollback到该保存点处的状态，而不是rollback整个事务。
Connection接口的setSavepoint和releaseSavepoint方法可以设置和释放保存点。
JDBC规范虽然定义了事务的以上支持行为，但是各个JDBC驱动，数据库厂商对事务的支持程度可能各不相同。如果在程序中任意设置，可能得不到想要的效果。为此，JDBC提供了DatabaseMetaData接口，提供了一系列JDBC特性支持情况的获取方法。比如，通过DatabaseMetaData.supportsTransactionIsolationLevel方法可以判断对事务隔离级别的支持情况，通过DatabaseMetaData.supportsSavepoints方法可以判断对保存点的支持情况。
语法
Connection con = DriverManger.getConnection(urlString);con.setAutoCommit(false);//取消自动提交Statement stm = con.createStatement();stm.executeUpdate(sqlString);con.transactionEndMethod; //事务方法成功则提交、失败则回滚con.commit() or con.rollback();
示例:
try{ Class.forName(drv).newInstance();//加载驱动 conn = DriverManager.getConnection(url,user,pwd); conn.setAutoCommit(false);//禁止自动提交 stmt = conn.createStatement(); //插入数据操作 stmt.executeUpdate("insert into(username,password,email) user values('admin','admin','admin@123.com')"); //更新数据操作 stmt.executeUpdate("update user set email='admin@163.com'"); //事务提交 conn.commit(); }catch(Exception ex){ try{ //如果失败则事务回滚 conn.rollback(); }catch(Exception e){ e.printStackTrace(); } }finally{ //关闭连接 if(conn != null){ try{ conn.close(); }catch(Exception e){ e.printStackTrace(); } } }
