这里写目录标题
结果集:
1.滚动,
2.插入,更新,删除,
3.批处理
17.11.1可滚动的结果集
【例17.23]让结果集滚动起来(类似指针)
PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = pstmt.executeQuery();
rs.absolute(1);
rs.beforeFirst();
rs.afterLast();
package file;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
public class demo
{
public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
public static final String DBURL = "jdbc:mysql://localhost:3306/java_mysql";
public static final String DBUSER = "root";
public static final String DBPASS = "dyh20011022";
public static void main(String[] args) throws Exception
{
Class.forName(DBDRIVER);//驱动
String sql = "select id,name,password,age,sex,birthday from user";
Connection conn =DriverManager.getConnection(DBURL, DBUSER, DBPASS);
PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = pstmt.executeQuery();
System.out.println("第二条:");
rs.absolute(1);
print(rs,1);
System.out.println("第一条:");
rs.beforeFirst();
print(rs,1);
System.out.println("最后一条:");
rs.afterLast();
print(rs,-1);
rs.close();
pstmt.close();
conn.close();
}
public static void print(ResultSet rs,int t) throws SQLException
{
if(t>0)rs.next();
else rs.previous();
int id = rs.getInt(1);
String name = rs.getString(2);
String password = rs.getString(3);
int age =rs.getInt(4);
String sex = rs.getString(5);
java.util.Date bir = rs.getDate(6);
// System.out.println("==================================================");
System.out.println(id+"\t"+name+"\t"+password+"\t"+age+"\t"+sex+"\t"+bir);
}
}
17.11.2使用结果集插入数据
【例17.24】直接在user表中增加数据
String sql = "select id,name,password,age,sex,birthday from user";
PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs.moveToInsertRow();
rs.updateString("name","李虎");
rs.insertRow();
package file;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
public class demo
{
public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
public static final String DBURL = "jdbc:mysql://localhost:3306/java_mysql";
public static final String DBUSER = "root";
public static final String DBPASS = "dyh20011022";
public static void main(String[] args) throws Exception
{
Class.forName(DBDRIVER);//驱动
//注意sql语句:select
String sql = "select id,name,password,age,sex,birthday from user";
Connection conn =DriverManager.getConnection(DBURL, DBUSER, DBPASS);
PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
//查询
ResultSet rs = pstmt.executeQuery();
//插入
rs.moveToInsertRow();
rs.updateString("name","李虎");
rs.updateString("password", "helloworld><");
rs.updateInt("age", 66);
rs.updateString("sex","女");
rs.updateDate("birthday",new java.sql.Date((int)new SimpleDateFormat("yyyy-MM-dd").parse("2000-05-30").getTime()));
rs.insertRow();
//关闭
rs.close();
pstmt.close();
conn.close();
}
}
Date有bug
17.11.3使用结果集更新数据
【例17.25】使用结果集更新
String sql = "select id,name,password,age,sex,birthday from user where id = ?";
pstmt.setInt(1, 7);//id=7
rs.last();//最后一行:指向修改行
rs.updateString("name","爬满虎");
...
rs.updateRow();//更新
package file;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
public class demo
{
public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
public static final String DBURL = "jdbc:mysql://localhost:3306/java_mysql";
public static final String DBUSER = "root";
public static final String DBPASS = "dyh20011022";
public static void main(String[] args) throws Exception
{
Class.forName(DBDRIVER);//驱动
//注意sql语句:select
String sql = "select id,name,password,age,sex,birthday from user where id = ?";
Connection conn =DriverManager.getConnection(DBURL, DBUSER, DBPASS);
PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
//查询
pstmt.setInt(1, 7);//id=7
ResultSet rs = pstmt.executeQuery();
//修改
rs.last();//最后一行
rs.updateString("name","爬满虎");
rs.updateString("password", "======");
rs.updateInt("age", 666);
rs.updateString("sex","女");
rs.updateDate("birthday",new java.sql.Date(new java.util.Date().getTime()));
rs.updateRow();
//关闭
rs.close();
pstmt.close();
conn.close();
}
}
17.11.4使用结果集删除数据
【例17.26]删除指定编号的数据
String sql = "select id,name,password,age,sex,birthday from user where id = ?";
pstmt.setInt(1, 7);//id=7
rs.last();
rs.deleteRow();
package file;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
public class demo
{
public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
public static final String DBURL = "jdbc:mysql://localhost:3306/java_mysql";
public static final String DBUSER = "root";
public static final String DBPASS = "dyh20011022";
public static void main(String[] args) throws Exception
{
Class.forName(DBDRIVER);//驱动
//注意sql语句:select
String sql = "select id,name,password,age,sex,birthday from user where id = ?";
Connection conn =DriverManager.getConnection(DBURL, DBUSER, DBPASS);
PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
//查询
pstmt.setInt(1, 7);//id=7
ResultSet rs = pstmt.executeQuery();
//删除
rs.last();
rs.deleteRow();
//关闭
rs.close();
pstmt.close();
conn.close();
}
}
17.11.5批处理
【例17.27]批量插入数据
String sql = "insert into user(name,password,age,sex,birthday) values(?,?,?,?,?)";
for(int i=0;i<5;i++)
{
pstmt.setString(1, "张红"+i);
...
pstmt.addBatch();
}
int temp[] = pstmt.executeBatch();
package file;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
public class demo
{
public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
public static final String DBURL = "jdbc:mysql://localhost:3306/java_mysql";
public static final String DBUSER = "root";
public static final String DBPASS = "dyh20011022";
public static void main(String[] args) throws Exception
{
Class.forName(DBDRIVER);//驱动
//注意sql语句
String sql = "insert into user(name,password,age,sex,birthday) values(?,?,?,?,?)";
Connection conn =DriverManager.getConnection(DBURL, DBUSER, DBPASS);
PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
for(int i=0;i<5;i++)
{
pstmt.setString(1, "张红"+i);
pstmt.setString(2, "password"+i);
pstmt.setInt(3, 33+i);
pstmt.setString(4, "女");
pstmt.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
pstmt.addBatch();
}
int temp[] = pstmt.executeBatch();
for(int i=0;i<temp.length;i++)
System.out.println("========="+temp);
System.out.println("执行了"+temp.length+"条语句。");
//关闭
pstmt.close();
conn.close();
}
}