JDBC中Statement与PreparedStatement的区别
PreparedStatement继承自Statement,JDBC提供了Statement、PreparedStatement来执行查询语句。
准备工作
- 数据库配置文件:config.properties
driver=com.mysql.jdbc.Driver
url=填上自己数据库的url
username=root
password=*******
- 连接数据库类DBUtil
import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DBUtil {
private static String driver;
private static String url;
private static String username;
private static String password;
static {
Properties prop=new Properties();
try {
Reader in=new FileReader("src\\config.properties");
prop.load(in);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
driver=prop.getProperty("driver"); //获取数据驱动
url=prop.getProperty("url"); //获取数据url
username=prop.getProperty("username"); //获取数据库用户名
password=prop.getProperty("password"); //获取数据库用户名密码
}
public static Connection open() {
try {
Class.forName(driver); //加载驱动
return DriverManager.getConnection(url, username, password); //连接数据库
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public static void close(Connection conn) {
if(conn!=null) {
try {
conn.close(); //关闭连接
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
1、Statement
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class StatementTest {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn=DBUtil.open(); //连接数据库
String sql="select * from user"; //sql语句
try {
Statement state=conn.createStatement();
ResultSet result=state.executeQuery(sql); //查询,返回结果集
while(result.next()) { //遍历结果集
String name=result.getString("username");
String password=result.getString("password");
String email=result.getString("email");
System.out.println(name+":"+password);
System.out.println(email);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.close(conn); //关闭连接
}
}
}
2、PreparedStatement
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class PreparedStatementTest {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn=DBUtil.open();
String sql="select * from user";
// try {
Statement state=conn.createStatement();
ResultSet result=state.executeQuery(sql); //Statement形式,注意sql语句的位置
// PreparedStatement preparedState=conn.prepareCall(sql);
// ResultSet result=preparedState.executeQuery();
// while(result.next()) {
// String name=result.getString("username");
// String password=result.getString("password");
// String email=result.getString("email");
// System.out.println(name+":"+password);
// System.out.println(email);
// }
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// } finally {
// DBUtil.close(conn);
// }
String str="insert into user values('tcrush','123456','tcrush@gmail.com')";
String s="insert into user values(?,?,?)";
try {
PreparedStatement preparedState=conn.prepareCall(s); //预编译
preparedState.setString(1, "tcrush"); //设置字段的值
preparedState.setString(2, "123456");
preparedState.setString(3,"tcrush@gmail.com");
int result=preparedState.executeUpdate();
if(result>0) {
System.out.println("更新操作成功!");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBUtil.close(conn);
}
}
}
Connection产生PreparedStatement对象:prepareStatement();
Connection产生PreparedStatement对象:prepareCall();