JDBC中Statement与PreparedStatement的区别

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();