一、Statement执行步骤：
public class Demo { public static void main(String[] args) { } private String url="jdbc:mysql://localhost:3306/day01"; private String user="root"; private String password="root"; @Test public void test(){ Connection conn=null; Statement stm=null; try { //1.创建驱动程序 Class.forName("com.mysql.jdbc.Driver"); //2.获取连接对象 conn=DriverManager.getConnection(url, user, password); //3.创建Statement stm=conn.createStatement(); //4.准备sql String sql="CREATE TABLE worker(id INT ,NAME VARCHAR(20) ,gender VARCHAR(2))"; //5.发送sql语句，执行sql语句，得到返回结果 int count=stm.executeUpdate(sql); System.out.println(count); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException(e); } finally{ //7.关闭连接 if(stm!=null){ try { stm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException(e); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException(e); } } } } } 二、Statement和PreparedStatement的对比
2.1、效率的比较：
2.2、Sql语句的风险问题：
案列：登陆模块，
输入用户名，密码！
注意，
要避免用户输入的恶意密码！
Mysql 数据库：
-- 创建数据库CREATE DATABASE jdbc_demo;USE jdbc_demo-- 创建表CREATE TABLE admin( id INT PRIMARY KEY AUTO_INCREMENT, userName VARCHAR(20), pwd VARCHAR(20))-- 插入数据INSERT INTO admin(userName,pwd) VALUES('jack','12346')-- 查询查看SELECT * FROM admin PreparedStatement :在传入参数的时候，能够自动过滤掉注释。
package js;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import org.junit.Test;public class Demo6 { private String url="jdbc:mysql://localhost:3306/jdbc_demo"; private String user="root"; private String password="root"; private Connection conn=null; private Statement stmt=null; private PreparedStatement pstmt=null; private ResultSet rs=null; //1 没有使用防止sql注入案例 @Test public void testLogin(){ //1.0 模拟登录的用户名，密码 String userName="tom"; String pwd=" ' or 1=1 -- "; //Sql 语句 String sql="select * from admin where userName='"+userName+"' and pwd='"+pwd+"' "; try { //1.1 加载驱动，创建连接 Class.forName("com.mysql.jdbc.Driver"); conn=DriverManager.getConnection(url,user,password); //1.2创建Statement stmt=conn.createStatement(); //1.3 执行查询 rs=stmt.executeQuery(sql); //1.4业务判断 if(rs.next()){ System.out.println("登录成功，编号为："+rs.getInt("id")); }else{ System.out.println("登录失败"); } } catch (Exception e) { throw new RuntimeException(e); } finally{ if(rs!=null) { try { rs.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } } //2防止sql注入案例 @Test public void testLogin1(){ //1.0 模拟登录的用户名，密码 String userName="tom"; String pwd=" ' or 1=1 -- "; //Sql 语句 String sql="select * from admin where userName=? and pwd=?"; try { //1.1 加载驱动，创建连接 Class.forName("com.mysql.jdbc.Driver"); conn=DriverManager.getConnection(url,user,password); //1.2创建Statement pstmt=conn.prepareStatement(sql); //设置占位符值 pstmt.setString(1, userName); pstmt.setString(2, pwd); //1.3 执行查询 rs=pstmt.executeQuery(); //1.4业务判断 if(rs.next()){ System.out.println("登录成功，编号为："+rs.getInt("id")); }else{ System.out.println("登录失败"); } } catch (Exception e) { throw new RuntimeException(e); } finally{ if(rs!=null) { try { rs.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } }} 结论：
使用预编译SQL语句的命令对象，好处：
- 避免了频繁sql拼接 (可以使用占位符)
- 可以防止sql注入
三、CallableStatement 操作存储过程
package js;import java.sql.CallableStatement;import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import org.junit.Test; /* * 带有输入的参数 存储过程 */ public class Demo5 { @Test public void test(){ Connection conn=null; CallableStatement stmt=null; ResultSet rs=null; try { //获取连接 conn=JdbcUtil.getConnection(); //准备sql语句 String sql="CALL pro_findById(?)"; //预编译 stmt=conn.prepareCall(sql); //设置输入参数 stmt.setInt(1,1); //发送参数 rs=stmt.executeQuery(); //遍历结果 while(rs.next()){ int id=rs.getInt("id"); String name=rs.getString("name"); System.out.println(id+" "+name); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException(e); }finally{ JdbcUtil.close(conn, stmt, rs); } } @Test public void test1(){ Connection conn=null; CallableStatement stmt=null; ResultSet rs=null; try { //获取连接 conn=JdbcUtil.getConnection(); //准备sql语句 String sql="CALL pro_findById3(?,?)"; //预编译 stmt=conn.prepareCall(sql); //设置输入参数 stmt.setInt(1,1); //设置输出参数(注册输出参数) /* * 参数一：参数的位置 * 参数二：存储过程中的输出参数的jdbc的类型 java.sql.Types */ stmt.registerOutParameter(2, java.sql.Types.VARCHAR); //发送参数 stmt.executeQuery(); //结果不是返回结果集的，而是在输出参数中接收的。 //得到输出参数的值 String result=stmt.getString(2); System.out.println(result); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException(e); }finally{ JdbcUtil.close(conn, stmt, rs); } } } 有返回
package js;import java.sql.CallableStatement;import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.junit.Test; public class app_call { private Connection conn=null; private Statement stmt=null; private PreparedStatement pstmt=null; private CallableStatement cstmt=null; private ResultSet rs=null; String sql="CALL proc_login()"; @Test public void testCall(){ try { conn=JdbcUtil.getConnection(); cstmt=conn.prepareCall("CALL proc_login()"); rs=cstmt.executeQuery(); if(rs.next()){ String name=rs.getString("userName"); String pwd=rs.getString("pwd"); System.out.println(name+" "+pwd); } } catch (SQLException e) { }finally{ } } }