您当前的位置： 首页 > 学无止境 > 心得笔记 网站首页心得笔记

【第17章：Java数据库编程】_JDBC 2.0操作

发布时间：2021-01-11 18:17:31编辑：雪饮阅读（）

 ResultSet接口的absolute方法用于在结果集中定位行

import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.PreparedStatement ;
import java.sql.ResultSet ;
public class Hello{
       public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
       public static final String DBURL = "jdbc:mysql://localhost:3306/xynes" ;
       public static final String DBUSER = "root" ;
       public static final String DBPASS = "" ;
       public static void main(String args[]) throws Exception{
              Class.forName(DBDRIVER) ;
            Connection conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
             
              String sql = "select userid,username,password,roleid,encrypt,lastloginip from v9_admin" ;
              PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY) ;
              ResultSet rs = pstmt.executeQuery() ;
              rs.absolute(1) ;
              rs.next() ;
              int userid=rs.getInt(1);
              String username=rs.getString(2);
              String password=rs.getString(3);
              int roleid=rs.getInt(4);
              String encrypt=rs.getString(5);
              String lastloginip=rs.getString(6);
              System.out.print("userid:"+userid+" ");
              System.out.print("username:"+username+" ");
              System.out.print("password:"+password+" ");
              System.out.print("roleid:"+roleid+" ");
              System.out.print("encrypt:"+encrypt+" ");
              System.out.print("lastloginip:"+lastloginip+" ");
              conn.close() ;
       }
};

数据库中有两条数据
absolute定位到第一行，然后又由于next方法的作用，所以出现的结果就是第二行的数据了。
D:\>javac Hello.java
D:\>java Hello
userid:2 username:kasumi password:null roleid:0 encrypt:null lastloginip:null
注释掉next方法
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.PreparedStatement ;
import java.sql.ResultSet ;
public class Hello{
       public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
       public static final String DBURL = "jdbc:mysql://localhost:3306/xynes" ;
       public static final String DBUSER = "root" ;
       public static final String DBPASS = "" ;
       public static void main(String args[]) throws Exception{
              Class.forName(DBDRIVER) ;
            Connection conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
             
              String sql = "select userid,username,password,roleid,encrypt,lastloginip from v9_admin" ;
              PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY) ;
              ResultSet rs = pstmt.executeQuery() ;
              rs.absolute(1) ;
              //rs.next() ;
              int userid=rs.getInt(1);
              String username=rs.getString(2);
              String password=rs.getString(3);
              int roleid=rs.getInt(4);
              String encrypt=rs.getString(5);
              String lastloginip=rs.getString(6);
              System.out.print("userid:"+userid+" ");
              System.out.print("username:"+username+" ");
              System.out.print("password:"+password+" ");
              System.out.print("roleid:"+roleid+" ");
              System.out.print("encrypt:"+encrypt+" ");
              System.out.print("lastloginip:"+lastloginip+" ");
              conn.close() ;
       }
};
D:\>javac Hello.java
D:\>java Hello
userid:1 username:phpcms password:af9b78fa69aa5ee2a24ac767ec44d2e2 roleid:1 encrypt:eSsnJH lastloginip:127.0.0.1
发现注释掉next方法后数据同样能够取得，也就是说absolute方法与next方法返回类型相同都是行对象。由于没有next的影响，所以直接获取得了第一行的数据。
那么这里去除next方法与去除absolute方法都可以，只有保持只留其一即可。
关于Connection的prepareStatement方法的后两个参数的说明
resultSetType是设置ResultSet对象的类型可滚动，或者是不可滚动。取值如下：
ResultSet.TYPE_FORWARD_ONLY只能向前滚动
ResultSet.TYPE_SCROLL_INSENSITIVE和Result.TYPE_SCROLL_SENSITIVE这两个方法都能够实现任意的前后滚动，使用各种移动的ResultSet指针的方法。
二者的区别在于前者对于修改不敏感，而后者对于修改敏感。TYPE_SCROLL_SENSITIVE仅针对已经取出来的记录的更改（update、delete）敏感，对新增（insert）的数据不敏感。
resultSetConcurency是设置ResultSet对象能够修改的，取值如下：
       ResultSet.CONCUR_READ_ONLY 设置为只读类型的参数。
       ResultSet.CONCUR_UPDATABLE 设置为可修改类型的参数。
ResultSet的beforeFirst方法用于将游标移动到第一行之前
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.PreparedStatement ;
import java.sql.ResultSet ;
public class Hello{
       public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
       public static final String DBURL = "jdbc:mysql://localhost:3306/xynes" ;
       public static final String DBUSER = "root" ;
       public static final String DBPASS = "" ;
       public static void main(String args[]) throws Exception{
              Class.forName(DBDRIVER) ;
            Connection conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
             
              String sql = "select userid,username,password,roleid,encrypt,lastloginip from v9_admin" ;
              PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY) ;
              ResultSet rs = pstmt.executeQuery() ;
              rs.absolute(1) ;
              rs.beforeFirst();
              rs.next() ;
              int userid=rs.getInt(1);
              String username=rs.getString(2);
              String password=rs.getString(3);
              int roleid=rs.getInt(4);
              String encrypt=rs.getString(5);
              String lastloginip=rs.getString(6);
              System.out.print("userid:"+userid+" ");
              System.out.print("username:"+username+" ");
              System.out.print("password:"+password+" ");
              System.out.print("roleid:"+roleid+" ");
              System.out.print("encrypt:"+encrypt+" ");
              System.out.print("lastloginip:"+lastloginip+" ");
              conn.close() ;
       }
};
D:\>javac Hello.java
D:\>java Hello
userid:1 username:phpcms password:af9b78fa69aa5ee2a24ac767ec44d2e2 roleid:1 encrypt:eSsnJH lastloginip:127.0.0.1
这里虽然定位游标到第一行，但是用beforeFirst方法将游标移动到第一行之前，所以再次next之后读取的数据还是第一行的数据。
ResultSet 的previous方法用于获取上一次游标位置
可以将ResultSet的指针向前移动一个
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.PreparedStatement ;
import java.sql.ResultSet ;
public class Hello{
       public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
       public static final String DBURL = "jdbc:mysql://localhost:3306/xynes" ;
       public static final String DBUSER = "root" ;
       public static final String DBPASS = "" ;
       public static void main(String args[]) throws Exception{
              Class.forName(DBDRIVER) ;
            Connection conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
             
              String sql = "select userid,username,password,roleid,encrypt,lastloginip from v9_admin" ;
              PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY) ;
              ResultSet rs = pstmt.executeQuery() ;
              rs.absolute(1) ;
              rs.next() ;
              rs.previous();
              int userid=rs.getInt(1);
              String username=rs.getString(2);
              String password=rs.getString(3);
              int roleid=rs.getInt(4);
              String encrypt=rs.getString(5);
              String lastloginip=rs.getString(6);
              System.out.print("userid:"+userid+" ");
              System.out.print("username:"+username+" ");
              System.out.print("password:"+password+" ");
              System.out.print("roleid:"+roleid+" ");
              System.out.print("encrypt:"+encrypt+" ");
              System.out.print("lastloginip:"+lastloginip+" ");
              conn.close() ;
       }
};
定位到第一行，next操作后是第二行，然后用previous回到上一行，所以又是第一行数据了
D:\>javac Hello.java
D:\>java Hello
userid:1 username:phpcms password:af9b78fa69aa5ee2a24ac767ec44d2e2 roleid:1 encrypt:eSsnJH lastloginip:127.0.0.1
ResultSet 的afterLast方法用于将游标移动到最后一条数据之后
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.PreparedStatement ;
import java.sql.ResultSet ;
public class Hello{
       public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
       public static final String DBURL = "jdbc:mysql://localhost:3306/xynes" ;
       public static final String DBUSER = "root" ;
       public static final String DBPASS = "" ;
       public static void main(String args[]) throws Exception{
              Class.forName(DBDRIVER) ;
            Connection conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
             
              String sql = "select userid,username,password,roleid,encrypt,lastloginip from v9_admin" ;
              PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY) ;
              ResultSet rs = pstmt.executeQuery();        
              rs.afterLast();
              rs.previous();
              int userid=rs.getInt(1);
              String username=rs.getString(2);
              String password=rs.getString(3);
              int roleid=rs.getInt(4);
              String encrypt=rs.getString(5);
              String lastloginip=rs.getString(6);
              System.out.print("userid:"+userid+" ");
              System.out.print("username:"+username+" ");
              System.out.print("password:"+password+" ");
              System.out.print("roleid:"+roleid+" ");
              System.out.print("encrypt:"+encrypt+" ");
              System.out.print("lastloginip:"+lastloginip+" ");
              conn.close() ;
       }
};
D:\>javac Hello.java
D:\>java Hello
userid:2 username:kasumi password:null roleid:0 encrypt:null lastloginip:null
先将游标移动到最后一条之后，然后再取当前游标位置之前就到了最后一条数据所在行。
ResultSet数据插入操作
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.PreparedStatement ;
import java.sql.ResultSet ;
public class Hello{
       public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
       public static final String DBURL = "jdbc:mysql://localhost:3306/xynes" ;
       public static final String DBUSER = "root" ;
       public static final String DBPASS = "" ;
       public static void main(String args[]) throws Exception{
              Class.forName(DBDRIVER) ;
            Connection conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
             
              String sql = "select userid,username,password,roleid,encrypt,lastloginip from v9_admin" ;
              //ResultSet.CONCUR_UPDATABLE 表示结果集可以更新
              PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE) ;
              ResultSet rs = pstmt.executeQuery();
              //移动到插入行
              rs.moveToInsertRow();
              //准备插入数据
              rs.updateString("username","snowDrink");
              rs.updateString("password","sb");
              rs.updateInt("roleid",4);
              rs.updateString("encrypt","盐");
              rs.updateDate("lastloginip",new java.sql.Date(new java.util.Date().getTime()));
              //插入
              rs.insertRow() ;
             
              rs.close() ;
              pstmt.close() ;
              conn.close() ;
       }
};
这种先查后插入的操作真的是神逻辑。。。
D:\>javac Hello.java
D:\>java Hello
ResultSet更新数据
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.PreparedStatement ;
import java.sql.ResultSet ;
public class Hello{
       public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
       public static final String DBURL = "jdbc:mysql://localhost:3306/xynes" ;
       public static final String DBUSER = "root" ;
       public static final String DBPASS = "" ;
       public static void main(String args[]) throws Exception{
              Class.forName(DBDRIVER) ;
            Connection conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
             
              String sql = "select userid,username,password,roleid,encrypt,lastloginip from v9_admin" ;
              //ResultSet.CONCUR_UPDATABLE 表示结果集可以更新
              PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE) ;
              ResultSet rs = pstmt.executeQuery();
              //到最后一行
              rs.last();
              //准备更新数据
              rs.updateString("username","ayane");
              rs.updateString("password","sb2");
              rs.updateInt("roleid",5);
              rs.updateString("encrypt","盐2");
              rs.updateDate("lastloginip",new java.sql.Date(new java.util.Date().getTime()));
              //更新
              rs.updateRow() ;
             
              rs.close() ;
              pstmt.close() ;
              conn.close() ;
       }
};
D:\>javac Hello.java
D:\>java Hello
ResultSet的cancelRowUpdates方法用于取消更新
当该方法在ResultSet的updateRow方法之前先执行了，则ResultSet的updateRow方法无法完成数据更新，虽然也没有错误产生（好像也算是正常操作，没有错误产生则也不应该又问题）
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.PreparedStatement ;
import java.sql.ResultSet ;
public class Hello{
       public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
       public static final String DBURL = "jdbc:mysql://localhost:3306/xynes" ;
       public static final String DBUSER = "root" ;
       public static final String DBPASS = "" ;
       public static void main(String args[]) throws Exception{
              Class.forName(DBDRIVER) ;
            Connection conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
             
              String sql = "select userid,username,password,roleid,encrypt,lastloginip from v9_admin" ;
              PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE) ;
              ResultSet rs = pstmt.executeQuery();
             
              rs.last();
             
              rs.updateString("username","snowDrink");
              rs.updateString("password","sb6");
              rs.updateInt("roleid",6);
              rs.updateString("encrypt","盐6");
              rs.updateDate("lastloginip",new java.sql.Date(new java.util.Date().getTime()));
              //取消更新
              rs.cancelRowUpdates() ;
              rs.updateRow() ;
             
              rs.close() ;
              pstmt.close() ;
              conn.close() ;
       }
};
D:\>javac Hello.java
D:\>java Hello
ResultSet的deleteRow方法用于删除一行数据
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.PreparedStatement ;
import java.sql.ResultSet ;
public class Hello{
       public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
       public static final String DBURL = "jdbc:mysql://localhost:3306/xynes" ;
       public static final String DBUSER = "root" ;
       public static final String DBPASS = "" ;
       public static void main(String args[]) throws Exception{
              Class.forName(DBDRIVER) ;
            Connection conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
             
              String sql = "select userid,username,password,roleid,encrypt,lastloginip from v9_admin" ;
              PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE) ;
              ResultSet rs = pstmt.executeQuery();
             
              rs.absolute(3);
              rs.deleteRow();
             
              rs.close() ;
              pstmt.close() ;
              conn.close() ;
       }
};
D:\>javac Hello.java
D:\>java Hello
PreparedStatement的addBatch方法与PreparedStatement的executeBatch方法共同完成批处理
这里以批量插入为例
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.PreparedStatement ;
public class Hello{
       public static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
       public static final String DBURL = "jdbc:mysql://localhost:3306/xynes" ;
       public static final String DBUSER = "root" ;
       public static final String DBPASS = "" ;
       public static void main(String args[]) throws Exception{
              Class.forName(DBDRIVER) ;
            Connection conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS) ;
             
              String sql = "insert into v9_admin(username,password,roleid,encrypt,lastloginip) values(?,?,?,?,?)" ;
              PreparedStatement pstmt = conn.prepareStatement(sql) ;
             
              for(int i=0;i<10;i++){
                     pstmt.setString(1,"momiji" + i) ;
                     pstmt.setString(2,"momiji kiss snowDrink " + i) ;
                     pstmt.setInt(3,i) ;
                     pstmt.setString(4,"盐"+i) ;
                     pstmt.setDate(5,new java.sql.Date(new java.util.Date().getTime())) ;
                     // 加入批处理，等待执行
                     pstmt.addBatch() ;
              }
              int temp[] = pstmt.executeBatch() ;
              System.out.println("新增了" + temp.length + "条数据。") ;
             
              pstmt.close() ;
              conn.close() ;
       }
};
D:\>javac Hello.java
D:\>java Hello
新增了10条数据。