JDBC封装方法的使用

一.接口ResultSetMetaData用来获取结果集的元数据
1.获取结果集的元数据
ResultSetMetaData meta = rs.getMetaData();

2.遍历并显示结果集中所有列的名称
for(int i=1;i<=meta.getColumnCount();i++) {
    System.out.println(meta.getColumnName(i));
}

二.事务
1.设置事务的提交属性
关闭自动commit（true即为自动提交）
conn.setAutoCommit(false);
2.手动提交事务
conn.commit();
3.回滚事务
conn.rollback();

三.批量更新
1.把多条sql存入Statement对象的缓存,一次性发送给DB执行
String sql1="create table log1(id int,msg varchar(20))";
String sql2="create table log2(id int,msg varchar(20))";
Statement sta = conn.createStatement();
sta.addBatch(sql1);
sta.addBatch(sql2);
//一次性发送给数据库执行
//返回值>=0---成功，有结果,返回值-2---成功，没有结果,返回值-3---不成功
int[] arr=sta.executeBatch();

2.创建一个执行计划,把多条参数存入PreParedStatement对象缓存中,一次性发送给DB执行
String sql3="insert into log1 values(?,?)";
PreparedStatement ps = conn.prepareStatement(sql3);
ps.setInt(1, 1);
ps.setString(2, "lily");
ps.addBatch();

ps.setInt(1, 2);
ps.setString(2, "rose");
ps.addBatch();
int[] arr=ps.executeBatch();

四.获取自动生成的主键
String[] colNames={"id"};//自动生成值的列的列名
PreparedStatement ps =conn.prepareStatement(sql1, colNames); //必须与上面的命令一起使用 ResultSet rs = ps.getGeneratedKeys();//只能获得自动生成的列的值
while(rs.next()) {
    id=rs.getInt(1);
    System.out.println(id);
}

封装方法的类，下面的Demo将会使用：

package day03;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.SQLException;import java.util.Properties;import org.apache.commons.dbcp.BasicDataSource;public class DBUtils {private static String driver;private static String url;private static String username;private static String password;private static int init;private static int maxactive;private static BasicDataSource bds=null;static {bds=new BasicDataSource();Properties cfg=new Properties();InputStream inStream=DBUtils.class.getClassLoader().getResourceAsStream("db.properties");try {cfg.load(inStream);driver=cfg.getProperty("jdbc.driver");url=cfg.getProperty("jdbc.url");username=cfg.getProperty("jdbc.username");password=cfg.getProperty("jdbc.password");init=Integer.parseInt(cfg.getProperty("init"));maxactive=Integer.parseInt(cfg.getProperty("maxactive"));//设置BasicDataSource必要参数bds.setDriverClassName(driver);bds.setUrl(url);bds.setUsername(username);bds.setPassword(password);//设置BasicDataSource管理策略参数bds.setInitialSize(init);bds.setMaxActive(maxactive);} catch (IOException e) {e.printStackTrace();}}public static Connection getConnection() {Connection conn=null;try {conn=bds.getConnection();return conn;} catch (Exception e) {e.printStackTrace();throw new RuntimeException(e);}}public static void closeConnection(Connection conn) {if (conn!=null) {try {//把事务恢复成自动提交再归还conn.setAutoCommit(true);conn.close();} catch (SQLException e) {e.printStackTrace();}}}public static void rollback(Connection conn) {if(conn!=null) {try {conn.rollback();}catch(SQLException e1) {e1.printStackTrace();}}}}

获取结果集的元数据Demo1：

package day03;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;public class Demo1 {public static void main(String[] args) {Connection conn = null;String sql = "select id,name,password from dept where id<?";try {conn=DBUtils.getConnection();PreparedStatement ps = conn.prepareStatement(sql);ps.setInt(1, 100);ResultSet rs = ps.executeQuery();//获取结果集的元数据ResultSetMetaData meta = rs.getMetaData();//遍历并显示结果集中所有列的名称for(int i=1;i<=meta.getColumnCount();i++) {System.out.println(meta.getColumnName(i));}//关闭结果集rs.close();//关闭PS对象ps.close();} catch (SQLException e) {e.printStackTrace();}finally {DBUtils.closeConnection(conn);}}}

事务Demo2：

package day03;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;public class Demo2 {/** * 由于JDBC自动事务提交 * 1.关闭自动提交conn.setAutoCommit(false); * 2.try的最后部分手动提交conn.commit(); * 3.由于一出现错误，就会运行catch块 * 在catch中conn.rollback(); * 4.封装回滚方法 * @param args */static String sql1 ="update bal set money=money+? where id=?";static String sql2 ="select money from bal where id=?";public static void main(String[] args) {pay(2,1,1000);}public static void pay(int from,int to,double money) {Connection conn=null;try {conn=DBUtils.getConnection();//关闭自动commitconn.setAutoCommit(false);PreparedStatement ps = conn.prepareStatement(sql1);/** * 业务模块 * create table bal( * id int, * name varchar(20), * money double(8,2) * ); *///a-1000ps.setDouble(1, -money);ps.setInt(2, from);int n = ps.executeUpdate();//更新失败if(n!=1) {throw new Exception("减钱失败");}//b+1000ps.setDouble(1, money);ps.setInt(2, to);n = ps.executeUpdate();//更新失败if(n!=1) {throw new Exception("加钱失败");}ps.close();//检查a有没有1000ps=conn.prepareStatement(sql2);ps.setInt(1, from);ResultSet rs = ps.executeQuery();while(rs.next()) {double bal = rs.getDouble(1);if(bal<0) {throw new Exception("余额不足");}}//手动提交conn.commit();}catch(Exception e) {e.printStackTrace();DBUtils.rollback(conn);}finally {DBUtils.closeConnection(conn);}}}

批量更新Demo3：

package day03;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.Statement;import java.util.Arrays;public class Demo3 {public static void main(String[] args) {String sql1="create table log1(id int,msg varchar(20))";String sql2="create table log2(id int,msg varchar(20))";String sql3="create table log3(id int,msg varchar(20))";String sql4="create table log4(id int,msg varchar(20))";String sql5="create table log5(id int,msg varchar(20))";String sql6="insert into log1 values(?,?)";String sql7="insert into log2 values(?,?)";//1.获得连接对象Connection conn = null;try {conn=DBUtils.getConnection();//批量更新的第一种方法//把多个sql语句存入sta对象的缓存中Statement sta = conn.createStatement();sta.addBatch(sql1);sta.addBatch(sql2);sta.addBatch(sql3);sta.addBatch(sql4);sta.addBatch(sql5);//一次性发送给数据库执行//返回值>=0---成功，有结果//返回值-2---成功，没有结果//返回值-3---不成功int[] arr=sta.executeBatch();System.out.println(Arrays.toString(arr));//第二种批量更新的方式//使用ps固定一个执行计划//把一堆参数存入ps的缓存//一次性发送给DB，进行处理/*PreparedStatement ps = conn.prepareStatement(sql6);ps.setInt(1, 1);ps.setString(2, "lily");ps.addBatch();ps.setInt(1, 2);ps.setString(2, "rose");ps.addBatch();int[] arr=ps.executeBatch();System.out.println(Arrays.toString(arr));*//*PreparedStatement ps =conn.prepareStatement(sql7);for (int i = 1; i < 100; i++) {ps.setInt(1, i);ps.setString(2, "test"+i);ps.addBatch();if (i%8==0) {//97.98.99ps.executeBatch();}}ps.executeBatch();*/}catch(Exception e) {e.printStackTrace();}finally {DBUtils.closeConnection(conn);}}}

获得自动生成的主键Demo4：

package day03;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;public class Demo4 {static String sql1="insert into keyword values(null,?)";static String sql2="insert into post values(null,?,?)";public static void main(String[] args) {Connection conn = null;try {conn=DBUtils.getConnection();conn.setAutoCommit(false);//更改条目之后要返回的列的名称String[] colNames= {"id"};PreparedStatement ps = conn.prepareStatement(sql1,colNames);ps.setString(1, "wumai");int n = ps.executeUpdate();if(n!=1) {throw new Exception("关键字添加失败");}//上面就是插入keyword//获得keyword自动生成的主键int id=-1;ResultSet rs = ps.getGeneratedKeys();//只能获得自动生成的列的值while(rs.next()) {id=rs.getInt(1);System.out.println(id);}ps.close();//插入post表ps = conn.prepareStatement(sql2);ps.setString(1, "lily");ps.setInt(2, id);n=ps.executeUpdate();if(n!=1) {throw new Exception("创建话题错误");}//手动提交conn.commit();}catch(Exception e) {e.printStackTrace();}finally{DBUtils.closeConnection(conn);}}}