statement文アクション
9328 ワード
/*Statement 4
* 1, executeQuery() :
* 2, executeUpdate(): 、 、 ,
* 3, execute(): 、 、 、 。 ， ， " st.getResultSet()"
* 4, executeBatch()
*/
@Test
public void results() throws Exception{ //res.getXXX
Statement st = ConnFactory.getConn().createStatement();
String sql = "select * from book ";
ResultSet res = st.executeQuery(sql);
while(res.next()){
Integer id = res.getInt(1);
String name = res.getString(2);
double price = res.getDouble("price");
String birth = res.getDate(4)+" "+ res.getTime(4);//
System.out.println(id+","+name+","+price+","+birth);
}
ConnFactory.getConn().close();
}
@Test
public void execute() throws Exception{
Statement st = ConnFactory.getConn().createStatement();
//String sql = "insert into book(name,price,birth) values('XML',23.30,'2014-09-08 12:00:05' )";
//String sql = "update book set price=price*1.1 ";
//String sql = "delete from book where id=3";
String sql = "select * from book";
boolean boo = st.execute(sql);
System.out.println(boo);
if(boo){
ResultSet rs = st.getResultSet();
while(rs.next()){
System.out.println(rs.getInt(1)+","+rs.getString(2));
}
}
}
@Test
public void executeUpdate() throws Exception{
Statement st = ConnFactory.getConn().createStatement();
//String sql = "insert into book(name,price,birth) values(' ',85.66,'2013-10-08 12:00:05' )";
//String sql = "update book set price=price*1.1 ";
//String sql = "delete from book where id=1";
String sql = "select * from book";
int num = st.executeUpdate(sql);//
System.out.println(num);
}
@Test // bug： name : aa,b'c
public void reg() throws Exception{
Statement st = ConnFactory.getConn().createStatement();
Scanner sc = new Scanner(System.in);
String id = sc.nextLine();
String name = sc.nextLine();
int age = Integer.parseInt(sc.nextLine());
//String sql = "insert into stud values('P2001','kobe',25) ";
String sql = "insert into stud values('"+id+"','"+name+"',"+age+") ";
System.out.println(sql);
st.execute(sql);
ConnFactory.getConn().close();
}
@Test // ： name : a' or '1'='1
public void login() throws Exception{
Statement st = ConnFactory.getConn().createStatement();
Scanner sc = new Scanner(System.in);
String id = sc.nextLine();
String name = sc.nextLine();
//String sql = "select count(*) from stud where id='P2001' and name='kobe' ";
String sql = "select count(*) from stud where id='"+id+"' and name='"+name+"' ";
System.out.println(sql);
ResultSet rs = st.executeQuery(sql);
rs.next();
int n = rs.getInt(1);
if(n<=0){
System.out.println(" ...");
}else{
System.out.println(" ....");
}
ConnFactory.getConn().close();
}
////// ： sql ， Statement 。////
// PrepareStatement
@Test // ： name : a' or '1'='1
public void login2() throws Exception{
Connection con = ConnFactory.getConn();
Scanner sc = new Scanner(System.in);
String id = sc.nextLine();
String name = sc.nextLine();
//
String sql = "select count(*) from stud where id=? and name=? ";// ， “?” ( )
PreparedStatement pst = con.prepareStatement(sql);
// ---
pst.setString(1, id); // 1
pst.setString(2, name); // 2
ResultSet rs = pst.executeQuery();// sql
rs.next();
int n = rs.getInt(1);
if(n<=0){
System.out.println(" ...");
}else{
System.out.println(" ....");
}
con.close();
}
@Test // bug： name : aa,b'c
public void reg2() throws Exception{
Scanner sc = new Scanner(System.in);
String id = sc.nextLine();
String name = sc.nextLine();
int age = Integer.parseInt(sc.nextLine());
//String sql = "insert into stud values('P2001','kobe',25) ";
String sql = "insert into stud values(?,?,?) ";
PreparedStatement pst = ConnFactory.getConn().prepareStatement(sql);
pst.setString(1, id);
pst.setString(2, name);
pst.setInt(3, age);
pst.executeUpdate();
ConnFactory.getConn().close();
}
@Test //
public void getAuto() throws Exception{
String sql = "insert into book(name,price,birth) values(' ',45.66,'2012-10-18 12:00:05' )";
Statement st = ConnFactory.getConn().createStatement();
st.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
ResultSet rs = st.getGeneratedKeys();
if(rs.next()){
int id = rs.getInt(1);
System.out.println(" :"+id);
}
ConnFactory.getConn().close();
}
@Test // --PreparedStatement
public void getAuto2() throws Exception{
String sql = "insert into book(name,price,birth) values(?,?,'2012-10-18 12:00:05' )";
PreparedStatement pst = ConnFactory.getConn().prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
pst.setString(1, " ");
pst.setDouble(2, 123.23);
pst.executeUpdate();
ResultSet rs = pst.getGeneratedKeys();
if(rs.next()){
int id = rs.getInt(1);
System.out.println(" :"+id);
}
ConnFactory.getConn().close();
}
@Test // --- ， sql ， sql ， 。 ， :con.setAutoCommit(false)+try-cacth+ rollback/commit
public void batchDemo() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "insert into book(name,price,birth) values('aaa',11.11,'2013-11-28 19:00:15' )";
Statement st = con.createStatement();
for(int i=0;i<10;i++){
if(i==5){
//sql = "insert into book(name,price,birth) values('aaa','aa','2013-11-28 19:00:15' )";
}
st.addBatch(sql);
}
sql = "update book set price =price*1.1 where price<30";
st.addBatch(sql);
int a[] = st.executeBatch();
for(int x:a){
System.out.println(x);
}
ConnFactory.getConn().close();
}
@Test // ---
public void preBatchDemo() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "insert into book(name,price,birth) values(?,?,'2013-11-28 19:00:15' )";
PreparedStatement pst = con.prepareStatement(sql);
for(int i=0;i<10;i++){
pst.setString(1, "bb"+i);
pst.setDouble(2, 25+i);
pst.addBatch();
}
sql = "update book set price =price*1.1 where price<30";
pst.addBatch(sql);
int a[] = pst.executeBatch();
for(int x:a){
System.out.println(x);
}
ConnFactory.getConn().close();
}
} ：
create table img(
id int,
img blob
);
TinyBlob 255
Blob 65k
MediumBlob 16M
LongBlob 4G
*/
public class LobDemoImg {
@Test //
public void writeLob() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "insert into img values(?,?)";
PreparedStatement pst = con.prepareStatement(sql);
pst.setInt(1, 1);
InputStream in = LobDemoImg.class.getClassLoader().getResourceAsStream("2.jpg");
pst.setBinaryStream(2, in);
pst.executeUpdate();
con.close();
}
@Test //
public void readLob() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "select * from img where id=1";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
if(rs.next()){
InputStream in = rs.getBinaryStream(2);
FileOutputStream out = new FileOutputStream("d:/a/a2.jpg");
byte buf[] = new byte[512];
int len=0;
while((len=in.read(buf))!=-1){
out.write(buf, 0, len);
}
in.close();
out.close();
}
con.close();
}ストアド・プロシージャ処理:
@Test//
public void callProcedureDemo() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "call p1() ";
CallableStatement cst = con.prepareCall(sql);
ResultSet rs = cst.executeQuery();
while(rs.next()){
System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getInt(3));
}
}
@Test//
public void callProcedureDemo2() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "call p2(?,?,?) ";
CallableStatement cst = con.prepareCall(sql);
cst.setString(1, "P3001");
cst.setString(2, " ");
cst.setInt(3, 50);
ResultSet rs = cst.executeQuery();
while(rs.next()){
System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getInt(3));
}
}
@Test// ----- 4
public void callProcedureDemo3() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "call p3(?,?,?,?) ";
CallableStatement cst = con.prepareCall(sql);
cst.setString(1, "P3002");
cst.setString(2, " ");
cst.setInt(3, 40);
cst.registerOutParameter(4, Types.INTEGER);
cst.execute();
int count = cst.getInt(4);
System.out.println(count);
}