String sql="update person set name=? where id=?";

package com.tfy.itheima.impl.test;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Clob;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;


import javax.sql.rowset.serial.SerialBlob;
import javax.sql.rowset.serial.SerialClob;


import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;


import com.tfy.itheima.domain.Person;
import com.tfy.itheima.jdbc.util.DbcpUtil;




public class QueryRunnerTest {
private QueryRunner qr=new QueryRunner(DbcpUtil.getDataSource());
@Test
public void addPerson(){
try {
String sql="insert into person (id,name,birthday) values(?,?,?)";
// Object []param={1,"tfy","1990-09-09"};
Object []param={2,"谭方勇",new Date()};



qr.update(sql, param);
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}

}
@Test
public void update(){
try {
String sql="update person set name=? where id=?";
Object params[]={"ty",2};

qr.update(sql, params);
} catch (SQLException e) {
e.printStackTrace();
}
}



@Test
public void queryOne(){
try {
String sql="select * from  person  where id=?";
Object params[]={2};

Person p = qr.query(sql,new BeanHandler<Person>(Person.class), params);
System.out.println(p);

} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void queryAll(){
try{
String sql="select * from person";
List<Person> ps = qr.query(sql, new BeanListHandler<Person>(Person.class));
for(Person p:ps){
System.out.println(p);
}

}catch(Exception e){
e.printStackTrace();
throw new RuntimeException(e);
}
}
@ Test
public void delete(){
try{
String sql="delete from person  where id=?";
qr.update(sql,2);
}catch(Exception e){
e.printStackTrace();
throw new RuntimeException(e);
}
}
@Test
public void bath(){
try{
String sql="insert into person (id,name,birthday) values (?,?,?)";
Object [][]params=new Object[100][];
for(int i=0;i<10;i++){
params[i]=new Object[]{i+2,"linlin"+(i+2),new Date()};
}
//System.out.println("QueryRunnerTest.bath()");
//long time=System.currentTimeMillis();
qr.batch(sql, params);
//System.out.println("执行时间为:"+(System.currentTimeMillis()-time));
}catch(Exception e){
e.printStackTrace();
throw new RuntimeException(e);
}
}
@Test
public void clobtxtTest(){
try{
String sql="insert into t1 (id,content) values(?,?)";
File file=new File("C:/a.txt");
Reader r=new FileReader(file);
char []ch=new char[(int)file.length()];

r.read(ch);
r.close();


Clob c=new SerialClob(ch);
Object []params={1,c};//Clob

qr.update(sql, params);
}catch(Exception e){
e.printStackTrace();
throw new RuntimeException(e);
}
}
@Test
public void BlobTest(){
try{
String sql="insert into t2 (id,content) values(?,?) ";
InputStream  in= new FileInputStream("c:/118f.jpg");
byte []b=new byte[in.available()];


in.read(b);
in.close();

SerialBlob sb=new SerialBlob(b);
Object []params={1,sb};
qr.update(sql, params);
}catch(Exception e){
e.printStackTrace();
throw new RuntimeException(e);
}
}
}