statement语句操作

/*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);
	}