PrepparedStatement--预编译

/*
 预编译 sql语句，代码全部背下来
use zou;

#预编译执行查询，这一步预编译只需要执行一次
prepare showUsersByLikeName from 'select * from test1011 where table_name like ?';

select * from test1011;

set @table_name = '%test%';

#预编译查询
execute showUsersByLikeName using @table_name;

#设置变量
set @table_name = 'test2';
execute showUsersByLikeName using @table_name;
 * 
 * */



package day10yue29;

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

/*
 * 2018-10-29
 * 功能：sql预编译执行
 * 
 * 接下来到过年，全部使用这台电脑学习编程
 * 
 * 
 * */

public class PrepaerdStatementTest {
	public void showUser()
	{
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try {
			
			Class.forName("com.mysql.cj.jdbc.Driver");
			connection  = DriverManager.getConnection("jdbc:mysql://localhost:3306/zou?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false", "root", "");
			String sql = " select * from test1011 where table_name = ? ";
			
			//对象包装sql，告诉mysql要预编译
			preparedStatement = connection.prepareStatement(sql);
			
			//参数初始化
			preparedStatement.setString(1, "test2");
			
			//发送给mysql进行执行查询
			resultSet =  preparedStatement.executeQuery();
			
			
			//执行这一条，会返回新的ResultSet 覆盖老的查询结果
			preparedStatement.setString(1, "zou");
			
			resultSet =  preparedStatement.executeQuery();
			System.out.println(resultSet.next());//false
			
			while(resultSet.next())
			{
				System.out.println(resultSet.getString(1)+"  "+resultSet.getString(2)+" "+resultSet.getString(3));
				
			}
			
			
			
			
		} catch (Exception e) {
			System.out.println("出现异常");
			System.out.println(e);
			
		}finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
			
			if (preparedStatement!=null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
			if (resultSet!=null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			}
			
		}
		
	}
	
	
	public static void main(String[] args) {
		PrepaerdStatementTest test = new PrepaerdStatementTest();
		test.showUser();
		
	}

}