java连接数据库的jdbc技术之statement的相关操作及excute和executeUpdate的用法和区别

以mysql数据库为基础，用junit做jdbc技术之statement的相关测试，以及探讨excute和executeUpdate的区别。

该测试所需的表book和stud，建表语句如下：

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `birth` datetime NOT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `stud` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);

该测试创建了一个数据库对象，用来操作数据库，附代码：

package com.cw.cw.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class Mysql {
	private static final String URL = "jdbc:mysql://localhost:3306/dbgirl";
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String USER = "root";
	private static final String PWD = "123456";
	
	public Connection conn = null;
	public Statement stmt = null;
	public PreparedStatement pstmt = null;
	public ResultSet rs = null;
	
	public Mysql() throws SQLException{
		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL,USER,PWD);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	public void getStatement() throws SQLException, ClassNotFoundException{
		stmt = conn.createStatement();
	}
	public void getPsStatement(String sql) throws SQLException, ClassNotFoundException{
		pstmt = conn.prepareStatement(sql);
	}
	
	public void close(){
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(pstmt!=null){
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

该测试创建的测试类，附代码：

package com.cw.cw;

import java.sql.SQLException;
import java.util.Scanner;

import org.junit.Test;

import com.cw.cw.mysql.Mysql;

public class JdbcStatementTest {
	
	Mysql mysql = null;
	
	public JdbcStatementTest(){
		try {
			mysql = new Mysql();
			mysql.getStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Statement的executeQuery
	 */
	@Test
	public void test1() throws Exception{ //mysql.rs.getXXX
		System.out.println("test1");
		String sql = "select * from book ";
		mysql.rs = mysql.stmt.executeQuery(sql);
		while(mysql.rs.next()){
			Integer id = mysql.rs.getInt(1);
			String name = mysql.rs.getString(2);
			double price = mysql.rs.getDouble("price");
			String birth = mysql.rs.getDate(4)+" "+ mysql.rs.getTime(4);//注意获取日期时间型数据的方式
			System.out.println(id+","+name+","+price+","+birth);
		}
		mysql.close();
		System.err.println("=================================================");
	}
	
	/**
	 * Statement的execute
	 * select查询时，execute返回true，可以像executeQuery一样，把数据取出来
	 * insert、update、delete时，execute返回false
	 */
	
	@Test
	public void test2() throws Exception{ 
		System.out.println("test2");
//		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 = mysql.stmt.execute(sql);
		System.out.println(boo);
		if(boo){
			mysql.rs = mysql.stmt.getResultSet();
			while(mysql.rs.next()){
				System.out.println(mysql.rs.getInt(1)+","+mysql.rs.getString(2));
			}
		}
		mysql.close();
		System.err.println("=================================================");
	}
	
	/**
	 * Statement的executeUpdate
	 * select查询时，executeUpdate直接报错
	 * insert、update、delete时，executeUpdate返回影响的行数
	 * 为啥执行insert的时候，先输出的"==="，再输出"1"？？？
	 */
	@Test
	public void test3() throws Exception{ 
		System.out.println("test3");
//		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=4";
		String sql = "select * from book";
		
		int num = mysql.stmt.executeUpdate(sql);//返回值是影响的行数
		System.out.println(num);
		mysql.close();
		System.err.println("=================================================");
	}

	/**
	 * Statement的insert
	 * 容易产生bug：如输入name值为: aa,b'c
	 * 插入带有'时,报语法错误，需要对'进行处理，例如：'分需转成''
	 */
	@SuppressWarnings("resource")
	@Test 
	public void test4() throws Exception{ 
		System.out.println("test4");
		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);
		mysql.stmt.execute(sql);
		mysql.close();
		System.err.println("=================================================");
	}

	/**
	 * Statement的select
	 * 登录时，容易被黑，如输入name值为: a' or '1'='1
	 */
	@SuppressWarnings("resource")
	@Test 
	public void test5() throws Exception{ 
		System.out.println("test5");
		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);
		mysql.rs = mysql.stmt.executeQuery(sql);
		//取一条数据
		mysql.rs.next();
		int n = mysql.rs.getInt(1);
		if(n<=0){
			System.out.println("登录失败...");
		}else{
			System.out.println("登录成功....");
		}
		mysql.close();
		System.err.println("=================================================");
		System.out.println("junit测试方法执行完毕");
	}
	
	//综上：如果sql语句由程序内部直接指定，那么用Statement没问题。

}
	

参考：http://m.blog.csdn.net/jrdgogo/article/details/52212553