Java EE WEB工程师培训-JDBC+Servlet+JSP整合开发之06.JDBC PreparedStatement-优快云博客

本文介绍如何使用PreparedStatement动态执行SQL语句，包括插入、删除、更新和查询操作，并展示了具体的Java代码实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

–PreparedStatement
–为占位符“？”赋值
–使用PreparedStatement动态执行SQL语句

####################Michael分割线######################

• PreparedStatement
–Statement只能静态操作SQL语句，如果要想动态操作SQL语句又该如何实现呢？例如：注册会员
–这里可以使用PreparedStatement来动态操作SQL语句
–PreparedStatement通过使用占位符“？”，来预生成SQL语句，从而达到动态操作的功能

• 为占位符“？”赋值
–根据当前SQL的数据类型

调用相应的如下方法

• 使用PreparedStatement动态执行SQL语句
–insert

ConnectionUtil.java

package com.michael.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class ConnectionUtil {
         //第一种方法
         public Connection getConnection(){
                Connection conn = null;
                 try {
                         //Class.forName加载驱动
                        Class.forName( "com.mysql.jdbc.Driver");
                         //DriverManager获得连接
                        conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/jdbc_db","root","mysqladmin");
                        return conn;
                } catch (Exception e) {
                        e.printStackTrace();
                }
                return null;
        }
        //第二种方法
        public Connection getConnection(String driver,String url,String user,String password){
                Connection conn = null;
                try {
                        //Class.forName加载驱动
                        Class.forName(driver);
                        //DriverManager获得连接
                        conn = DriverManager.getConnection(url,user,password);
                        return conn;
                } catch (Exception e) {
                        e.printStackTrace();
                }
                return null;
        }
        //第三种方法
        public Connection openConnection(){
                String driver = "";
                String url = "";
                String user = "";
                String password = "";
                Properties prop = new Properties();
                Connection conn = null;
                try {
                        //加载属性文件
                        prop.load(this.getClass().getClassLoader().getResourceAsStream("DBConfig.properties"));
                        driver = prop.getProperty("driver");
                        url = prop.getProperty("url");
                        user = prop.getProperty("user");
                        password = prop.getProperty("password");
                        //Class.forName加载驱动
                        Class.forName(driver);
                        //DriverManager获得连接
                        conn = DriverManager.getConnection(url,user,password);
                        return conn;
                } catch (Exception e) {
                        e.printStackTrace();
                }
                return null;
        }

}

Customer.java

package com.michael.jdbc;

public class Customer {
         private int id;
         private String name;
         private String email;
         public String getEmail() {
                 return email;
        }
         public void setEmail(String email) {
                 this.email = email;
        }
         public int getId() {
                 return id;
        }
         public void setId( int id) {
                 this.id = id;
        }
         public String getName() {
                 return name;
        }
         public void setName(String name) {
                 this.name = name;
        }
}

TestPrepareStatement.java

Main.java

package com.michael.main;

import com.michael.jdbc.Customer;
import com.michael.jdbc.TestPrepareStatement;

public class Main {

         /**
         * @param args
         */
         public static void main(String[] args) {
                Customer c = new Customer();
                c.setName( "51blog");
                c.setEmail( "51blog@51blog.com");
                TestPrepareStatement.add(c);
        }

}

可以删除所选的用户，现在我们删除ID为2的用户

TestPrepareStatement.java

Main.java

package com.michael.main;

import com.michael.jdbc.TestPrepareStatement;

public class Main {

         /**
         * @param args
         */
         public static void main(String[] args) {
                 /*
                Customer c = new Customer();
                c.setName("51blog");
                c.setEmail("51blog@51blog.com");
                TestPrepareStatement.add(c);
                */
                 //删除ID为2的用户
                TestPrepareStatement.delete(2);
        }

}

也可以更新数据，现在我们更新所有用户名为Michael

TestPrepareStatement.java

package com.michael.jdbc;

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

public class TestPrepareStatement {
         public static void update(String name){
                Connection conn = new ConnectionUtil().openConnection();
                String sql = "update CustomerTbl set name = ?";
                 try {
                        PreparedStatement pstmt = conn.prepareStatement(sql);
                        pstmt.setString(1, name);
                        pstmt.executeUpdate();
                } catch (SQLException e) {

                        e.printStackTrace();
                }
        }
}

Main.java

package com.michael.main;

import com.michael.jdbc.TestPrepareStatement;

public class Main {

         /**
         * @param args
         */
         public static void main(String[] args) {
                 /*
                Customer c = new Customer();
                c.setName("51blog");
                c.setEmail("51blog@51blog.com");
                TestPrepareStatement.add(c);
                */
                 //删除ID为2的用户
                 //TestPrepareStatement.delete(2);
                TestPrepareStatement.update( "Michael");
        }

}

##############Michael分割线##################

更新name为51cto的用户名为alibaba

TestPrepareStatement.java

package com.michael.jdbc;

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

public class TestPrepareStatement {
         public static void update(String name1,String name2){
                Connection conn = new ConnectionUtil().openConnection();
                String sql = "update CustomerTbl set name = ? where name = ?";
                 try {
                        PreparedStatement pstmt = conn.prepareStatement(sql);
                        pstmt.setString(1, name1);
                        pstmt.setString(2, name2);
                        pstmt.executeUpdate();
                } catch (SQLException e) {

                        e.printStackTrace();
                }
        }
}

Main.java

package com.michael.main;

import com.michael.jdbc.TestPrepareStatement;

public class Main {

         /**
         * @param args
         */
         public static void main(String[] args) {
                 /*
                Customer c = new Customer();
                c.setName("51blog");
                c.setEmail("51blog@51blog.com");
                TestPrepareStatement.add(c);
                */
                 //删除ID为2的用户
                 //TestPrepareStatement.delete(2);
                 //TestPrepareStatement.update("Michael");
                TestPrepareStatement.update( "Michael", "51cto");
        }

}

####################Michael分割线######################

• 使用PreparedStatement动态执行SQL语句
–query

TestPrepareStatement.java

package com.michael.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TestPrepareStatement {
         public static List query(String name){
                Connection conn = new ConnectionUtil().openConnection();
                String sql = "select * from CustomerTbl where name = ?";
                 try {
                        PreparedStatement pstmt = conn.prepareStatement(sql);
                        pstmt.setString(1, name);
                        ResultSet rs = pstmt.executeQuery();
                        List list = new ArrayList();
                         while(rs.next()){
                                 int id = rs.getInt(1);
                                String email = rs.getString(3);
                                Customer c = new Customer();
                                c.setId(id);
                                c.setName(name);
                                c.setEmail(email);
                                list.add(c);
                        }
                        System.out.println(list.size());
                         return list;
                } catch (SQLException e) {
                        e.printStackTrace();
                }
                 return null;
        }
}

Main.java