–PreparedStatement
–为占位符“？”赋值
–使用PreparedStatement动态执行SQL语句
–为占位符“？”赋值
–使用PreparedStatement动态执行SQL语句
####################Michael分割线######################
• PreparedStatement
–Statement只能静态操作SQL语句，如果要想动态操作SQL语句又该如何实现呢？例如：注册会员
–这里可以使用PreparedStatement来动态操作SQL语句
–PreparedStatement通过使用占位符“？”，来预生成SQL语句，从而达到动态操作的功能
–Statement只能静态操作SQL语句，如果要想动态操作SQL语句又该如何实现呢？例如：注册会员
–这里可以使用PreparedStatement来动态操作SQL语句
–PreparedStatement通过使用占位符“？”，来预生成SQL语句，从而达到动态操作的功能
• 为占位符“？”赋值
–根据当前SQL的数据类型
–根据当前SQL的数据类型
调用相应的如下方法
• 使用PreparedStatement动态执行SQL语句
–insert
–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;
}
}
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;
}
}
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
package com.michael.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestPrepareStatement {
public static void add(Customer c){
Connection conn = new ConnectionUtil().openConnection();
String sql = "insert into CustomerTbl(name,email) values(?,?)";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, c.getName());
pstmt.setString(2, c.getEmail());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestPrepareStatement {
public static void add(Customer c){
Connection conn = new ConnectionUtil().openConnection();
String sql = "insert into CustomerTbl(name,email) values(?,?)";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, c.getName());
pstmt.setString(2, c.getEmail());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
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);
}
}
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
package com.michael.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestPrepareStatement {
public static void delete( int id){
Connection conn = new ConnectionUtil().openConnection();
String sql = "delete from CustomerTbl where id = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,id);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestPrepareStatement {
public static void delete( int id){
Connection conn = new ConnectionUtil().openConnection();
String sql = "delete from CustomerTbl where id = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,id);
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);
}
}
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");
}
}
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");
}
}
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
–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;
}
}
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
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");
TestPrepareStatement.query( "Michael");
}
}
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");
TestPrepareStatement.query( "Michael");
}
}
显示有两个客户名为Michael
####################Michael分割线######################
附件：http://down.51cto.com/data/2352831
本文转自redking51CTO博客，原文链接：http://blog.51cto.com/redking/163057，如需转载请自行联系原作者
