我们前面已经连接到数据库之后,就要对数据库进行进一步的操作,最简单的便是增删改查操作
1.查
public void QueeyAll() throws Exception{
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
stmt = conn.createStatement();
String sql = "select * from product";
result = stmt.executeQuery(sql);
while(result.next()){
int proid = result.getInt("proid");
String proname = result.getString("proname");
String proflag = result.getString("proflag");
double procost = result.getDouble("procost");
System.out.print(proid + "\t");
System.out.print(proname + "\t");
System.out.print(proflag + "\t");
System.out.print(procost + "\n");
}
result.close();
stmt.close();
conn.close();
}
我们通过定义一个结果集返回数据库中的数据。
2.增
public int InsertPro(int proid,String proname,double procost,String proflag) throws Exception{
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
stmt = conn.createStatement();
String sql = "insert into product (proid,proname,procost,proflag) values(null,?,?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1,proname);
preparedStatement.setDouble(2,procost);
preparedStatement.setString(3,proflag);
int i = preparedStatement.executeUpdate();
System.out.println(i);
return i;
}
通过定义受影响行数判断是否插入成功
3.删
public int DeleteByID(int proID) throws Exception{
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
String sql = "delete from product where proid = ?";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setInt(1,proID);
//输出受影响行数判断删除是否成功
int i = preparedStatement.executeUpdate();
System.out.println(i);
preparedStatement.close();
conn.close();
return i;
}
删除操作跟增加是一样的,也是通过定义受影响行数判断是否删除成功
4.改
public int UpdatePro(int proid,String proname,double procost,String proflag) throws Exception{
Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
stmt = conn.createStatement();
String sql = "update product set proname = ? , procost = ? ,proflag = ? where proid = ?";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, proname);
preparedStatement.setDouble(2, procost);
preparedStatement.setString(3, proflag);
preparedStatement.setInt(4, proid);
int i = preparedStatement.executeUpdate();
System.out.println(i);
return i;
5.小结
需要注意参数索引的顺序要一一对应