17.11 JDBC 2.0 操作(血干JAVA系类)

这里写目录标题

17.11.1可滚动的结果集
- - - 【例17.23］让结果集滚动起来（类似指针）
17.11.2使用结果集插入数据
- - - 【例17.24】直接在user表中增加数据
17.11.3使用结果集更新数据
- - - 【例17.25】使用结果集更新
17.11.4使用结果集删除数据
- - - 【例17.26］删除指定编号的数据
17.11.5批处理
- - - 【例17.27］批量插入数据

结果集：
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

17.11 JDBC 2.0 操作(血干JAVA系类)

Java相关栏目本月热门文章