目录
新内容:PreparedStatement可以操作Blob类型,而Statement不行,下面通过PreparedStatement插入一个图片
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;j<args.length;j++) {
ps.setObject(j+1, args[j]);
}
//4.执行
/* ps.execute()
* 如果执行的是查询操作,有返回结果,则此方法返回true
* 如果执行的是增删改操作,没有返回结果,则次方法放回falase
* */
// ps.execute();
//此时用:ps.executeUpdate()无参数的,不要插入有参数的,那个是给statement用的
return ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCconn.closeResource(conn, ps, null);
}
return 0;
}
@Test
public void test() throws Exception {
Scanner sc = new Scanner(System.in);
System.out.print("请输入ID:");
int id = sc.nextInt();
System.out.print("请输入名字:");
String name = sc.next();
System.out.print("请输入邮箱地址:");
String email = sc.next();
SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd");
System.out.print("请输入格式为(yyyy-MM-dd)的日期:");
Date data = s.parse(sc.next());
String sql = "insert into customers(id,name,email,birth) values(?,?,?,?) ";
int update = Update(sql,id,name,email,data);
if(update>0) {
System.out.println("操作成功");
}else {
System.out.println("操作失败");
}
}
}
//注意4.执行
/* ps.execute()
* 如果执行的是查询操作,有返回结果,则此方法返回true
* 如果执行的是增删改操作,没有返回结果,则次方法放回falase
* */
// ps.executeUpdate();
//此时用:ps.executeUpdate()无参数的,不要插入有参数的,那个是给statement用的
//executeUpdate()返回一个int类型的变量,若此变量大于0则操作成功,否则则失败
第二题:通过输入身份证或者准考证查询考生
public class Exercise2 {
public <E> E query(Class<E> 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);
}
}