一PreparedStatement介绍
PreparedStatement为预定义语句,继承Statment。
例如:我们注册会员时该怎么办?
—-这时我们用到PreparedStatement来动态操作SQL语句,通过使用占位符“?”来生成SQL语句,从而达到动态操作的功能。
二、插入数据(insert)
1、第一种
(1)创建Test4.java
package com;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.text.DefaultEditorKit.InsertBreakAction;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class Test4 {
public static void insert(String name,String email){
//数据库链接connection
Connection conn=null;
//PreparedStatement为动态操作,预定义SQL语句
PreparedStatement pSta=null;
//结果集ResultSet
ResultSet rSet=null;
conn=DBUtil.open();
//values(?,?)为占位符,需要一个变量替换这个"?"
String sql="insert into c(name,email)values(?,?)";
try {
pSta=(PreparedStatement)conn.prepareStatement(sql);
//设置变量值(参数索引,参数内容)
pSta.setString(1, name);
pSta.setString(2, email);
pSta.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
DBUtil.close(rSet, pSta, conn);
}
}
public static void main(String[] args) {
nsert("cxg12345","123@qq.com");
}
}
(2)创建DBUtil.java进行数据库链接
package com;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.Reader;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class DBUtil {
//定义成不可修改的
private static String driver;
private static String url;
private static String username;
private static String password;
//把值都按要求填入
static {
Properties p=new Properties();
Reader reader;
try {
reader = new FileReader("src//config.properties");
p.load(reader);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
driver="com.mysql.jdbc.Driver";
url="jdbc:mysql://localhost:3306/cxg2850105498";
username="root";
password="cxg200888";
}
//打开数据库
public static Connection open() {
try {
//注册加载驱动
Class.forName(driver);
//链接数据库
return (Connection) DriverManager.getConnection(url,username,password);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
//关闭数据库
public static void close(ResultSet rSet,PreparedStatement pSta,Connection conn) {
if(conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(rSet!=null){
try {
rSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pSta!=null){
try {
pSta.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
2、第二种
(1)创建Test4.java处理数据库
package com;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.text.DefaultEditorKit.InsertBreakAction;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
//添加对象的方法
static void insert(Cuser c){
//数据库链接connection
Connection conn=null;
//PreparedStatement为动态操作,预定义SQL语句
PreparedStatement pSta=null;
//结果集ResultSet
ResultSet rSet=null;
conn=DBUtil.open();
//values(?,?)为占位符,需要一个变量替换这个"?"
String sql="insert into c(name,email)values(?,?)";
try {
pSta=(PreparedStatement)conn.prepareStatement(sql);
//设置变量值(参数索引,参数内容),调用Cuser中getName()方法
pSta.setString(1, c.getName());
pSta.setString(2, c.getEmail());
pSta.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
DBUtil.close(rSet, pSta, conn);
}
}
public static void main(String[] args) {
Cuser c=new Cuser();
c.setEmail("232@qq.com");
c.setName("556cxg");
insert(c);
}
}
(2)创建数据库链接DBUtil.java
如上DBUtil.java文件
(3)创建Cuser.java类,封装需要的属性属性
package com;
public class Cuser {
private String name;
private String email;
private int id;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
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;
}
@Override
public String toString() {
// TODO Auto-generated method stub
return id+","+name+","+email;
}
}
三、更新(Update)
1、创建Test4.java
package com;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.text.DefaultEditorKit.InsertBreakAction;
import org.apache.taglibs.standard.lang.jstl.test.StaticFunctionTests;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class Test4 {
//更新
static void Update(Cuser c){
PreparedStatement pSta=null;
ResultSet rSet=null;
Connection conn=DBUtil.open();
String sql="update c set name=?,email=? where id=?";
try {
pSta=(PreparedStatement) conn.prepareStatement(sql);
pSta.setString(1, c.getName());
pSta.setString(2, c.getEmail());
pSta.setInt(3, c.getId());
pSta.execute();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
DBUtil.close(rSet, pSta, conn);
}
}
public static void main(String[] args) {
Cuser c=new Cuser();
c.setId(2);
c.setName("200888");
c.setEmail("1929@qq.com");
Update(c);
}
}
(2)创建数据库链接DBUtil.java
如上DBUtil.java文件
(3)创建Cuser.java类,封装需要的属性属性
如上Cuser.java文件
四、删除(delete)
(1)创建Test4.java
package com;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.text.DefaultEditorKit.InsertBreakAction;
import org.apache.taglibs.standard.lang.jstl.test.StaticFunctionTests;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class Test4 {
static void delete(int id){
Connection conn=null;
PreparedStatement pSta=null;
ResultSet rSet=null;
conn=DBUtil.open();
String sql="delete from c where id=?";
try {
pSta=(PreparedStatement) conn.prepareStatement(sql);
pSta.setInt(1, id);
pSta.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.close(rSet, pSta, conn);
}
}
public static void main(String[] args) {
delete(7);
}
}
(2)创建数据库链接DBUtil.java
如上DBUtil.java文件
(3)创建Cuser.java类,封装需要的属性属性
如上Cuser.java文件
五、查询(query)通过id查询
(1)创建Test4.java
package com;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.text.DefaultEditorKit.InsertBreakAction;
import org.apache.taglibs.standard.lang.jstl.test.StaticFunctionTests;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
public class Test4 {
static Cuser query(int id){
Connection conn=null;
ResultSet rSet=null;
PreparedStatement pSta=null;
conn=DBUtil.open();
String sql="select id,name,email from c where id=?";
try {
pSta=(PreparedStatement)conn.prepareStatement(sql);
pSta.setInt(1, id);
rSet=pSta.executeQuery();
if(rSet.next()){
String name=rSet.getString(2);
String email=rSet.getString(3);
Cuser c=new Cuser();
c.setEmail(email);
c.setId(id);
c.setName(name);
return c;
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.close(rSet, pSta, conn);
}
return null;
}
public static void main(String[] args) {
Cuser c=query(5);
System.out.println(c.getId()+","+c.getEmail()+","+c.getName());
}
}