现在我们的数据库信息如下,使用数据库mydb,使用其中的students表,表中内容如下:
增:
public void insert(String Id, String Name, String Score, String Residence) throws Exception{
String sql="INSERT INTO students(id,name,score,residence) VALUES( ? , ? , ? , ? );";
PreparedStatement ps=Main.link.prepareStatement(sql);
ps.setObject(1,Id);
ps.setObject(2,Name);
ps.setObject(3,Score);
ps.setObject(4,Residence);
int res=ps.executeUpdate();
if(res>0) System.out.println("插入成功");
else System.out.println("插入失败");
ps.close();
}
删:
public void delete(String Id)throws Exception{
String sql="DELETE FROM students WHERE id=?;";
PreparedStatement ps=Main.link.prepareStatement(sql);
ps.setObject(1,Id);
int res=ps.executeUpdate();
if(res>0) System.out.println("删除成功");
else System.out.println("删除失败");
ps.close();
}
改:
public void update(String Id,String Residence)throws Exception{
String sql="UPDATE students SET residence=? WHERE id=?;";
PreparedStatement ps=Main.link.prepareStatement(sql);
ps.setObject(1,Residence);
ps.setObject(2,Id);
int res=ps.executeUpdate();
if(res>0) System.out.println("更改成功成功");
else System.out.println("更改失败");
ps.close();
}
全代码展示:
操作类SqlOp:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class SqlOp {
public SqlOp() throws SQLException {
}
public void insert(String Id, String Name, String Score, String Residence) throws Exception{
String sql="INSERT INTO students(id,name,score,residence) VALUES( ? , ? , ? , ? );";
PreparedStatement ps=Main.link.prepareStatement(sql);
ps.setObject(1,Id);
ps.setObject(2,Name);
ps.setObject(3,Score);
ps.setObject(4,Residence);
int res=ps.executeUpdate();
if(res>0) System.out.println("插入成功");
else System.out.println("插入失败");
ps.close();
}
public void delete(String Id)throws Exception{
String sql="DELETE FROM students WHERE id=?;";
PreparedStatement ps=Main.link.prepareStatement(sql);
ps.setObject(1,Id);
int res=ps.executeUpdate();
if(res>0) System.out.println("删除成功");
else System.out.println("删除失败");
ps.close();
}
public void update(String Id,String Residence)throws Exception{
String sql="UPDATE students SET residence=? WHERE id=?;";
PreparedStatement ps=Main.link.prepareStatement(sql);
ps.setObject(1,Residence);
ps.setObject(2,Id);
int res=ps.executeUpdate();
if(res>0) System.out.println("更改成功成功");
else System.out.println("更改失败");
ps.close();
}
}
Main类:
import java.sql.*;
public class Main {
public static Connection link;
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
link=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb","root","222222");
SqlOp op1=new SqlOp();
op1.insert("100","小智","90","一号楼");//添加小智的信息
op1.update("111","二号楼");//小明改为住2号楼
op1.delete("333");//将小刚的信息删除
String sql="SELECT * FROM students ;";
ResultSet rs=link.createStatement().executeQuery(sql);
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getInt(3)+" "+rs.getString(4));
}
rs.close();
link.close();
}
}
insert,update,delete函数分别进行如下操作:添加id为'10'的学生的信息(即小李的信息),
更改id号为'111'的学生住所为'二号楼',删除id为'333'的学生信息。结果如下如所示: