可滚动的结果集
让结果集滚动起来
//================================================= // File Name : JDBC20_demo //------------------------------------------------------------------------------ // Author : Common import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.sql.*; import java.text.SimpleDateFormat; import java.util.Scanner; //主类 //Function : JDBC20_demo public class JDBC20_demo { //定义MySQL的数据库驱动程序 public static final String DBDRIVER = "org.gjt.mm.mysql.Driver"; //定义MySQL数据库的连接地址 public static final String DBURL = "jdbc:mysql://localhost:3306/mysql_demo"; //MySQL数据库的连接用户名 public static final String DBUSER = "root"; //MySQL数据库的连接密码 public static final String DBPASS = "123456"; public static void main(String[] args) throws Exception{ // TODO 自动生成的方法存根 Connection conn = null; //数据库连接 PreparedStatement pstmt = null; //数据库操作 ResultSet rs = null; //保存结果集 String sql = "SELECT id,name,password,age,sex,birthday "+ " FROM user"; Class.forName(DBDRIVER); //加载驱动程序 //连接MySQL数据库时,要写上连接的用户名和密码 conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS); //实例化PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY); rs = pstmt.executeQuery(); //查询 System.out.println("第二条数据:"); rs.absolute(1); //输出第2条,从0开始 print(rs,1); System.out.println("第一条数据:"); rs.beforeFirst(); //输出第1条 print(rs,1); System.out.println("第二条数据:"); rs.afterLast(); //输出最后1条 print(rs,-1); } public static void print(ResultSet rs,int re) throws Exception{ if(re>0){ rs.next(); //由前向后输出 }else{ rs.previous(); //有后向前输出 } int id = rs.getInt(1); String name = rs.getString(2); String pass = rs.getString(3); int age = rs.getInt(4); String sex = rs.getString(5); java.util.Date d = rs.getDate(6); System.out.println("编号:"+ id); System.out.println("姓名:"+name); System.out.println("密码:"+pass); System.out.println("年龄:"+age); System.out.println("性别:"+sex); System.out.println("生日:"+d); } }
使用结果集插入数据
直接在user表中增加数据
//================================================= // File Name : JDBC20_demo //------------------------------------------------------------------------------ // Author : Common import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.sql.*; import java.text.SimpleDateFormat; import java.util.Scanner; //主类 //Function : JDBC20_demo public class JDBC20_demo { //定义MySQL的数据库驱动程序 public static final String DBDRIVER = "org.gjt.mm.mysql.Driver"; //定义MySQL数据库的连接地址 public static final String DBURL = "jdbc:mysql://localhost:3306/mysql_demo"; //MySQL数据库的连接用户名 public static final String DBUSER = "root"; //MySQL数据库的连接密码 public static final String DBPASS = "123456"; public static void main(String[] args) throws Exception{ // TODO 自动生成的方法存根 Connection conn = null; //数据库连接 PreparedStatement pstmt = null; //数据库操作 ResultSet rs = null; //保存结果集 String sql = "SELECT id,name,password,age,sex,birthday "+ " FROM user"; Class.forName(DBDRIVER); //加载驱动程序 //连接MySQL数据库时,要写上连接的用户名和密码 conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS); //实例化PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); rs = pstmt.executeQuery(); //实例化ResulitSet对象 rs.moveToInsertRow(); //移动到可以插入的数据行 rs.updateString("name", "张三"); rs.updateString("password", "zhangsan"); rs.updateInt("age", 14); rs.updateString("sex", "女"); rs.updateDate("birthday", new java.sql.Date(new java.util.Date().getTime())); rs.insertRow(); //插入数据 rs.close(); pstmt.close(); conn.close(); } public static void print(ResultSet rs,int re) throws Exception{ if(re>0){ rs.next(); //由前向后输出 }else{ rs.previous(); //有后向前输出 } int id = rs.getInt(1); String name = rs.getString(2); String pass = rs.getString(3); int age = rs.getInt(4); String sex = rs.getString(5); java.util.Date d = rs.getDate(6); System.out.println("编号:"+ id); System.out.println("姓名:"+name); System.out.println("密码:"+pass); System.out.println("年龄:"+age); System.out.println("性别:"+sex); System.out.println("生日:"+d); } }
使用结果集更新数据
使用限定查询,查询id编号为3的用户信息,并更新其信息
查询到了才能更新,如果没有就不能更新
//================================================= // File Name : JDBC20_demo //------------------------------------------------------------------------------ // Author : Common import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.sql.*; import java.text.SimpleDateFormat; import java.util.Scanner; //主类 //Function : JDBC20_demo public class JDBC20_demo { //定义MySQL的数据库驱动程序 public static final String DBDRIVER = "org.gjt.mm.mysql.Driver"; //定义MySQL数据库的连接地址 public static final String DBURL = "jdbc:mysql://localhost:3306/mysql_demo"; //MySQL数据库的连接用户名 public static final String DBUSER = "root"; //MySQL数据库的连接密码 public static final String DBPASS = "123456"; public static void main(String[] args) throws Exception{ // TODO 自动生成的方法存根 Connection conn = null; //数据库连接 PreparedStatement pstmt = null; //数据库操作 ResultSet rs = null; //保存结果集 String sql = "SELECT id,name,password,age,sex,birthday "+ " FROM user WHERE id=?"; Class.forName(DBDRIVER); //加载驱动程序 //连接MySQL数据库时,要写上连接的用户名和密码 conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS); //实例化PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); pstmt.setInt(1, 2); //更新3号用户 rs = pstmt.executeQuery(); //实例化ResulitSet对象 rs.last(); //移动到最后一行 rs.updateString("name", "王小明"); rs.updateString("password", "wangxiaoming"); rs.updateInt("age", 24); rs.updateString("sex", "男"); rs.updateDate("birthday", new java.sql.Date(new java.util.Date().getTime())); rs.updateRow(); //更新数据 rs.close(); //关闭结果集 pstmt.close(); conn.close(); } public static void print(ResultSet rs,int re) throws Exception{ if(re>0){ rs.next(); //由前向后输出 }else{ rs.previous(); //有后向前输出 } int id = rs.getInt(1); String name = rs.getString(2); String pass = rs.getString(3); int age = rs.getInt(4); String sex = rs.getString(5); java.util.Date d = rs.getDate(6); System.out.println("编号:"+ id); System.out.println("姓名:"+name); System.out.println("密码:"+pass); System.out.println("年龄:"+age); System.out.println("性别:"+sex); System.out.println("生日:"+d); } }
使用结果集删除数据
使用deleteRow()方法
//================================================= // File Name : JDBC20_demo //------------------------------------------------------------------------------ // Author : Common import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.sql.*; import java.text.SimpleDateFormat; import java.util.Scanner; //主类 //Function : JDBC20_demo public class JDBC20_demo { //定义MySQL的数据库驱动程序 public static final String DBDRIVER = "org.gjt.mm.mysql.Driver"; //定义MySQL数据库的连接地址 public static final String DBURL = "jdbc:mysql://localhost:3306/mysql_demo"; //MySQL数据库的连接用户名 public static final String DBUSER = "root"; //MySQL数据库的连接密码 public static final String DBPASS = "123456"; public static void main(String[] args) throws Exception{ // TODO 自动生成的方法存根 Connection conn = null; //数据库连接 PreparedStatement pstmt = null; //数据库操作 ResultSet rs = null; //保存结果集 String sql = "SELECT id,name,password,age,sex,birthday "+ " FROM user WHERE id=?"; Class.forName(DBDRIVER); //加载驱动程序 //连接MySQL数据库时,要写上连接的用户名和密码 conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS); //实例化PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); pstmt.setInt(1, 2); //更新3号用户 rs = pstmt.executeQuery(); //实例化ResulitSet对象 rs.last(); //移动到最后一行 rs.deleteRow(); //删除数据 rs.close(); //关闭结果集 pstmt.close(); conn.close(); } public static void print(ResultSet rs,int re) throws Exception{ if(re>0){ rs.next(); //由前向后输出 }else{ rs.previous(); //有后向前输出 } int id = rs.getInt(1); String name = rs.getString(2); String pass = rs.getString(3); int age = rs.getInt(4); String sex = rs.getString(5); java.util.Date d = rs.getDate(6); System.out.println("编号:"+ id); System.out.println("姓名:"+name); System.out.println("密码:"+pass); System.out.println("年龄:"+age); System.out.println("性别:"+sex); System.out.println("生日:"+d); } }
批处理
//================================================= // File Name : JDBC20_demo //------------------------------------------------------------------------------ // Author : Common import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.sql.*; import java.text.SimpleDateFormat; import java.util.Scanner; //主类 //Function : JDBC20_demo public class JDBC20_demo { //定义MySQL的数据库驱动程序 public static final String DBDRIVER = "org.gjt.mm.mysql.Driver"; //定义MySQL数据库的连接地址 public static final String DBURL = "jdbc:mysql://localhost:3306/mysql_demo"; //MySQL数据库的连接用户名 public static final String DBUSER = "root"; //MySQL数据库的连接密码 public static final String DBPASS = "123456"; public static void main(String[] args) throws Exception{ // TODO 自动生成的方法存根 Connection conn = null; //数据库连接 PreparedStatement pstmt = null; //数据库操作 String sql = "INSERT INTO user (name,password,age,sex,birthday) "+ " VALUES (?,?,?,?,?)"; Class.forName(DBDRIVER); //加载驱动程序 //连接MySQL数据库时,要写上连接的用户名和密码 conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS); //实例化PreparedStatement pstmt = conn.prepareStatement(sql); for(int i=0;i<10;i++){ pstmt.setString(1, "张三—"+i); pstmt.setString(2, "mima—"+i); pstmt.setInt(3, 20+i); pstmt.setString(4, "男"); pstmt.setDate(5, new Date(new java.util.Date().getTime())); pstmt.addBatch(); //加入批处理等待执行 } int temp[] = pstmt.executeBatch(); //批量执行 pstmt.close(); conn.close(); } public static void print(ResultSet rs,int re) throws Exception{ if(re>0){ rs.next(); //由前向后输出 }else{ rs.previous(); //有后向前输出 } int id = rs.getInt(1); String name = rs.getString(2); String pass = rs.getString(3); int age = rs.getInt(4); String sex = rs.getString(5); java.util.Date d = rs.getDate(6); System.out.println("编号:"+ id); System.out.println("姓名:"+name); System.out.println("密码:"+pass); System.out.println("年龄:"+age); System.out.println("性别:"+sex); System.out.println("生日:"+d); } }