目录
练习题一：在控制台中操作查一条数据到数据库
第二题：通过输入身份证或者准考证查询考生
新内容:PreparedStatement可以操作Blob类型，而Statement不行，下面通过PreparedStatement插入一个图片
二：从数据库中读取Blob文件并下载到本地
新内容二:批量的增删改，主要针对插入操作
此时的批量操作，主要指的是批量插入，使用PreparedStatement 如何实现更高效的批量插入? 方式一：使用Statement Connection conn = JDBCutil.getConn(); Statement st = conn.createStatement(); for(){ //具体操作 }
//方式二：使用PerparedStatement 效率低
方式方式三:addBAtch(),executeBatch(),clearBatch() mysql服务器默认是关闭批处理的，我们需要通过一个参数，让 mysql开启批处理的支持 ?rewriteBatchedStatements=true 写在配置文件的url后面 3.更新新的mysql驱动mysql-connector-java-5.1.37-bin.jar
终极版:设置数据不允许自动提交
setAutoCommit(false)等数据操作好后commit(),通过这两个方法对方式三进行改进
练习题一：在控制台中操作查一条数据到数据库
package Exercise;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
import org.junit.Test;
import JDBCutil.JDBCconn;
public class Exercise1 {
public int Update(String sql,Object...args){
Connection conn=null;
PreparedStatement ps=null;
try {
Scanner sc = new Scanner(System.in);
//1.获取数据库的链接
conn = JDBCconn.getConn();
//2.预编译sql语句,返回PreparedStatement的实例
ps = conn.prepareStatement(sql);
//3.填充占位符
for(int j =0;j0) {
System.out.println("操作成功");
}else {
System.out.println("操作失败");
}
}
}
//注意4.执行
// ps.executeUpdate();
//此时用：ps.executeUpdate()无参数的,不要插入有参数的,那个是给statement用的
//executeUpdate()返回一个int类型的变量,若此变量大于0则操作成功,否则则失败
第二题：通过输入身份证或者准考证查询考生
public class Exercise2 {
public E query(Class clazz, String sql, Object... args) throws Exception {
Connection conn = JDBCconn.getConn();
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
ResultSet rc = ps.executeQuery();
ResultSetmetaData data = rc.getmetaData();
int count = data.getColumnCount();
if (rc.next()) {
E e = clazz.newInstance();
for (int i = 0; i < count; i++) {
Object columnValue = rc.getObject(i + 1);
String columnLabel = data.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(e, columnValue);
}
return e;
}
return null;
}
@Test
public void test1() {
Scanner sc = new Scanner(System.in);
System.out.println("请选择您要输入的类型");
System.out.println("a身份证号：");
System.out.println("b准考证号：");
String stu1 = sc.next();
if("a".equals(stu1)) {
System.out.print("请输入身份证号:");
String sql = "Select * from examstudent where IDcard= ?";
ExamStudentTble query=null;
try {
query = query(ExamStudentTble.class, sql, sc.next());
} catch (Exception e) {
System.out.println("输入错误");
}
if(query!=null) {
System.out.println(query);
}else {
System.out.println("查无此人");
}
}else if("b".equals(stu1)) {
System.out.print("请输入准考证号:");
String sql = "Select * from examstudent where ExamCard= ?";
ExamStudentTble query=null;
try {
query = query(ExamStudentTble.class, sql, sc.next());
} catch (Exception e) {
System.out.println("输入错误");
}
if(query!=null) {
System.out.println(query);
}else {
System.out.println("查无此人");
}
}else {
System.out.println("输入错误");
}
}
新内容:PreparedStatement可以操作Blob类型，而Statement不行，下面通过PreparedStatement插入一个图片
public void testInsert() throws Exception {
Connection conn = JDBCconn.getConn();
String sql ="insert into customers(name,email,birth,photo) values(?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, "漫画");
ps.setObject(2, "man@qq.com");
ps.setObject(3, "1992-09-08");
//使用流插入图片
ps.setBlob(4,new FileInputStream(new File("F:\123.png")));
ps.execute();
JDBCconn.closeResource(conn, ps, null);
}
二：从数据库中读取Blob文件并下载到本地
public void testQuery() {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
InputStream s=null;
FileOutputStream fos=null;
try {
conn = JDBCconn.getConn();
String sql ="select id,name,email,birth,photo from customers where id =?" ;
ps = conn.prepareStatement(sql);
ps.setInt(1, 23);
rs = ps.executeQuery();
if(rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
QueryBean s1 =new QueryBean(id,name,email,birth);
System.out.println(s1);
Blob blob = rs.getBlob("photo");
s = blob.getBinaryStream();
fos = new FileOutputStream("F:\2.jpg");
int len;
byte [] arr = new byte[1024];
while((len = s.read(arr))!=-1) {
fos.write(arr, 0, len);
}
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
if(s!=null)
s.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(fos!=null)
fos.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
JDBCconn.closeResource(conn, ps, rs);
}
}
新内容二:批量的增删改，主要针对插入操作
此时的批量操作，主要指的是批量插入，使用PreparedStatement
如何实现更高效的批量插入?
方式一：使用Statement
Connection conn = JDBCutil.getConn();
Statement st = conn.createStatement();
for(){
//具体操作
}
//方式二：使用PerparedStatement 效率低
public class InsertTest {
//方式二：使用PerparedStatement 效率低
@Test
//29807
public void testINsert1() {
Connection conn = null;
PreparedStatement ps=null;
try {
long millis = System.currentTimeMillis();
conn = JDBCconn.getConn();
String sql ="insert into goods(name)values(?)";
ps= conn.prepareStatement(sql);
for(int i =1;i<=20000;i++) {
ps.setObject(1,"name_"+i);
ps.execute();
}
long milli = System.currentTimeMillis();
System.out.println(milli-millis);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCconn.closeResource(conn, ps, null);
}
}
方式方式三:addBAtch(),executeBatch(),clearBatch()
mysql服务器默认是关闭批处理的，我们需要通过一个参数，让 mysql开启批处理的支持
?rewriteBatchedStatements=true 写在配置文件的url后面
3.更新新的mysql驱动mysql-connector-java-5.1.37-bin.jar
//571
public void testINsert2() {
Connection conn = null;
PreparedStatement ps=null;
try {
long millis = System.currentTimeMillis();
conn = JDBCconn.getConn();
String sql ="insert into goods(name)values(?)";
ps= conn.prepareStatement(sql);
for(int i =1;i<=20000;i++) {
ps.setObject(1,"name_"+i);
ps.addBatch();
if(i%500==0) {
ps.executeBatch();
ps.clearBatch();
}
}
long milli = System.currentTimeMillis();
System.out.println(milli-millis);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCconn.closeResource(conn, ps, null);
}
}
终极版:设置数据不允许自动提交
setAutoCommit(false)等数据操作好后commit(),通过这两个方法对方式三进行改进
public void testINsert3() {
Connection conn = null;
PreparedStatement ps=null;
try {
long millis = System.currentTimeMillis();
conn = JDBCconn.getConn();
//设置不准自动提交数据
conn.setAutoCommit(false);
String sql ="insert into goods(name)values(?)";
ps= conn.prepareStatement(sql);
for(int i =1;i<=2000000;i++) {
ps.setObject(1,"name_"+i);
//先攒sql
ps.addBatch();
if(i%500==0) {
//500条的时候处理并清空batch
ps.executeBatch();
ps.clearBatch();
}
}
//提交数据
conn.commit();
long milli = System.currentTimeMillis();
System.out.println(milli-millis);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCconn.closeResource(conn, ps, null);
}
}
如何实现更高效的批量插入?
方式一：使用Statement
Connection conn = JDBCutil.getConn();
Statement st = conn.createStatement();
for(){
//具体操作
}
//方式二：使用PerparedStatement 效率低
public class InsertTest {
//方式二：使用PerparedStatement 效率低
@Test
//29807
public void testINsert1() {
Connection conn = null;
PreparedStatement ps=null;
try {
long millis = System.currentTimeMillis();
conn = JDBCconn.getConn();
String sql ="insert into goods(name)values(?)";
ps= conn.prepareStatement(sql);
for(int i =1;i<=20000;i++) {
ps.setObject(1,"name_"+i);
ps.execute();
}
long milli = System.currentTimeMillis();
System.out.println(milli-millis);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCconn.closeResource(conn, ps, null);
}
}
方式方式三:addBAtch(),executeBatch(),clearBatch()
mysql服务器默认是关闭批处理的，我们需要通过一个参数，让 mysql开启批处理的支持
?rewriteBatchedStatements=true 写在配置文件的url后面
3.更新新的mysql驱动mysql-connector-java-5.1.37-bin.jar
//571
public void testINsert2() {
Connection conn = null;
PreparedStatement ps=null;
try {
long millis = System.currentTimeMillis();
conn = JDBCconn.getConn();
String sql ="insert into goods(name)values(?)";
ps= conn.prepareStatement(sql);
for(int i =1;i<=20000;i++) {
ps.setObject(1,"name_"+i);
ps.addBatch();
if(i%500==0) {
ps.executeBatch();
ps.clearBatch();
}
}
long milli = System.currentTimeMillis();
System.out.println(milli-millis);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCconn.closeResource(conn, ps, null);
}
}
终极版:设置数据不允许自动提交
setAutoCommit(false)等数据操作好后commit(),通过这两个方法对方式三进行改进
public void testINsert3() {
Connection conn = null;
PreparedStatement ps=null;
try {
long millis = System.currentTimeMillis();
conn = JDBCconn.getConn();
//设置不准自动提交数据
conn.setAutoCommit(false);
String sql ="insert into goods(name)values(?)";
ps= conn.prepareStatement(sql);
for(int i =1;i<=2000000;i++) {
ps.setObject(1,"name_"+i);
//先攒sql
ps.addBatch();
if(i%500==0) {
//500条的时候处理并清空batch
ps.executeBatch();
ps.clearBatch();
}
}
//提交数据
conn.commit();
long milli = System.currentTimeMillis();
System.out.println(milli-millis);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCconn.closeResource(conn, ps, null);
}
}
mysql服务器默认是关闭批处理的，我们需要通过一个参数，让 mysql开启批处理的支持
?rewriteBatchedStatements=true 写在配置文件的url后面
3.更新新的mysql驱动mysql-connector-java-5.1.37-bin.jar
//571
public void testINsert2() {
Connection conn = null;
PreparedStatement ps=null;
try {
long millis = System.currentTimeMillis();
conn = JDBCconn.getConn();
String sql ="insert into goods(name)values(?)";
ps= conn.prepareStatement(sql);
for(int i =1;i<=20000;i++) {
ps.setObject(1,"name_"+i);
ps.addBatch();
if(i%500==0) {
ps.executeBatch();
ps.clearBatch();
}
}
long milli = System.currentTimeMillis();
System.out.println(milli-millis);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCconn.closeResource(conn, ps, null);
}
}
终极版:设置数据不允许自动提交
setAutoCommit(false)等数据操作好后commit(),通过这两个方法对方式三进行改进
public void testINsert3() {
Connection conn = null;
PreparedStatement ps=null;
try {
long millis = System.currentTimeMillis();
conn = JDBCconn.getConn();
//设置不准自动提交数据
conn.setAutoCommit(false);
String sql ="insert into goods(name)values(?)";
ps= conn.prepareStatement(sql);
for(int i =1;i<=2000000;i++) {
ps.setObject(1,"name_"+i);
//先攒sql
ps.addBatch();
if(i%500==0) {
//500条的时候处理并清空batch
ps.executeBatch();
ps.clearBatch();
}
}
//提交数据
conn.commit();
long milli = System.currentTimeMillis();
System.out.println(milli-millis);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCconn.closeResource(conn, ps, null);
}
}
public void testINsert3() {
Connection conn = null;
PreparedStatement ps=null;
try {
long millis = System.currentTimeMillis();
conn = JDBCconn.getConn();
//设置不准自动提交数据
conn.setAutoCommit(false);
String sql ="insert into goods(name)values(?)";
ps= conn.prepareStatement(sql);
for(int i =1;i<=2000000;i++) {
ps.setObject(1,"name_"+i);
//先攒sql
ps.addBatch();
if(i%500==0) {
//500条的时候处理并清空batch
ps.executeBatch();
ps.clearBatch();
}
}
//提交数据
conn.commit();
long milli = System.currentTimeMillis();
System.out.println(milli-millis);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCconn.closeResource(conn, ps, null);
}
}
