以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