JDBC学习笔记（4）—PreparedStatement执行SQL语句

1.PreparedStatement与PreparedStatement区别

PreparedStatement和Statement的区别主要有三个：（1）PreparedStatement比Statement效率高。（2）语法不一样。                                                                （3）PreparedStatement数据更安全，可以防止mysql注入。

2.PreparedStatemnt执行DML语句

2.1数据库连接代码

package com.JDBC;import java.sql.*;public class JDBCconnection {    private String url="jdbc:mysql://localhost:3306/user";    private String user="root";    private String password="123456";    private Connection conn=null;    private PreparedStatement ps=null;    private ResultSet rs=null;    public Connection getConn() throws ClassNotFoundException, SQLException {        Class.forName("com.mysql.jdbc.Driver");        conn = DriverManager.getConnection(url,user,password);        System.out.println(conn);        return conn;    }    public void close(PreparedStatement ps,Connection conn) {        if(ps!=null) {            try {                ps.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if(conn!=null) {            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }    public void close(ResultSet rs,PreparedStatement ps,Connection conn) {        if(rs!=null) {            try {                rs.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if(ps!=null) {            try {                ps.close();            } catch (SQLException e) {                e.printStackTrace();            }        }        if(conn!=null) {            try {                conn.close();            } catch (SQLException e) {                e.printStackTrace();            }        }    }}

2.2插入数据

package com.JDBC;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;public class DMLPSInsert extends JDBCconnection{    private Connection conn=null;    private PreparedStatement ps=null;    public void insert() throws ClassNotFoundException, SQLException {        //数据库连接        conn=super.getConn();        //编写SQL语句        String sql="insert into login(username,password) values(?,?)";        //执行预编译SQL语句        ps=conn.prepareStatement(sql);        //设置参数        ps.setString(1, "zzw");        ps.setString(2, "234567");        int count=ps.executeUpdate();        System.out.println(count);        if(count>0) {            System.out.println("数据插入成功！");        }else {            System.out.println("数据插入失败！");        }        super.close(ps, conn);    }}

程序运行结果：

数据库结果：

2.3更新数据

package com.JDBC;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;public class DMLPSUpdate extends JDBCconnection{    private Connection conn=null;    private PreparedStatement ps=null;    public void update() throws ClassNotFoundException, SQLException {        //数据库连接        conn=super.getConn();        //编写SQL语句        String sql="update login set password=? where username=?";        //执行预编译SQL语句        ps=conn.prepareStatement(sql);        //设置参数        ps.setString(1, "hahaha");        ps.setString(2, "zzw");        int count=ps.executeUpdate();        System.out.println(count);        if(count>0) {            System.out.println("数据更新成功！");        }else {            System.out.println("数据更新失败！");        }        super.close(ps, conn);    }}

程序运行结果：

数据库结果：

2.4删除数据

package com.JDBC;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;public class DMLPSDelete extends JDBCconnection{    private Connection conn=null;    private PreparedStatement ps=null;    public void delete() throws ClassNotFoundException, SQLException {        //数据库连接        conn=super.getConn();        //编写SQL语句        String sql="delete from login where username=?";        //执行预编译SQL语句        ps=conn.prepareStatement(sql);        //设置参数        ps.setString(1, "zzw");        int count=ps.executeUpdate();        System.out.println(count);        if(count>0) {            System.out.println("数据删除成功！");        }else {            System.out.println("数据删除失败！");        }        super.close(ps, conn);    }}

程序运行结果：

数据库结果：

3.PreparedStatment执行DQL语句

  package com.JDBC;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class DQLPSselect extends JDBCconnection{    protected Connection conn=null;    protected PreparedStatement ps=null;    protected ResultSet rs=null;    public void selectAll() throws ClassNotFoundException, SQLException {        //数据库连接        conn=super.getConn();        //编写SQL语句        String sql="select * from login";        //预编译SQL语句        ps=conn.prepareStatement(sql);        ResultSet rs=ps.executeQuery(sql);        while(rs.next()) {            System.out.println("用列名称取值： id："+rs.getInt("id")+" username:"+rs.getString("username")+" password:"+rs.getString("password"));            System.out.println("用索引取值：  id："+rs.getInt(1)+" username:"+rs.getString(2)+" password:"+rs.getString(3));        }        super.close(rs, ps, conn);    }}

程序运行结果：

数据库结果：