数据库 jdbc 增删改查

喜欢ヅ旅行 2021-12-05 01:32 408阅读 0赞

jdbc 增删改查  
//登录 相当于增删改查中的查  
注册相当于增。  
查 ：executeQuery 方法。  
public Product findByName(String productName) \{  
Product product=new Product();  
try \{  
conn=jdbc.getConnection();  
String sql=“select \* from product”;  
PreparedStatement st=conn.prepareStatement(sql);  
ResultSet rs=st.executeQuery();  
while (rs.next()) \{  
product.setName(rs.getString(“name”));  
product.setOrderid(rs.getInt(“orderid”));  
product.setOrdertime(rs.getString(“ordertime”));  
product.setQuantity(rs.getInt(“quantity”));  
product.setTotalprice(rs.getDouble(“totalprice”));  
\}  
rs.close();  
st.close();  
conn.close();  
\}catch (ClassNotFoundException e) \{  
e.printStackTrace();  
\} catch (SQLException e) \{  
e.printStackTrace();  
\}  
return product;  
\}  
//查询所有  
public ArrayList selectAll()\{  
ArrayList productlist=new ArrayList();  
try \{  
conn=jdbc.getConnection();  
String sql=“select \* from product”;  
PreparedStatement st=conn.prepareStatement(sql);  
ResultSet rs=st.executeQuery();  
while (rs.next()) \{  
Product product=new Product();  
product.setName(rs.getString(“name”));  
product.setOrderid(rs.getInt(“orderid”));  
product.setOrdertime(rs.getString(“ordertime”));  
product.setQuantity(rs.getInt(“quantity”));  
product.setTotalprice(rs.getDouble(“totalprice”));  
productlist.add(product);  
\}  
rs.close();  
st.close();  
conn.close();  
\}catch (ClassNotFoundException e) \{  
e.printStackTrace();  
\} catch (SQLException e) \{  
e.printStackTrace();  
\}  
return productlist;  
\}  
增：JDBCComon jdbc=new JDBCComon();  
Connection conn=null;  
/\*\*  
\* 增加  
\* @param product  
\* @return  
\*/  
public boolean add(Product product)\{  
boolean flag=false;  
try \{  
conn=jdbc.getConnection();  
String sql=“insert into product (name,category,price) values (?,?,?)”;  
PreparedStatement st=conn.prepareStatement(sql);  
st.setString(1, product.getName());  
st.setString(2, product.getCategory());  
st.setBigDecimal(3, product.getPrice());  
if(st.execute())\{  
flag=true;  
\}  
st.close();  
conn.close();  
\} catch (ClassNotFoundException e) \{  
// TODO Auto-generated catch block  
e.printStackTrace();  
\} catch (SQLException e) \{  
// TODO Auto-generated catch block  
e.printStackTrace();  
\}  
return flag;  
\};

删： public boolean remove(int orderid) \{  
boolean flag=false;  
try \{  
conn=jdbc.getConnection();  
String sql=“delete from product where orderid=?”;  
PreparedStatement st=conn.prepareStatement(sql);  
st.setInt(1, orderid);  
if (st.execute()) \{  
flag=true;  
\}  
st.close();  
conn.close();  
\}catch (ClassNotFoundException e) \{  
e.printStackTrace();  
\} catch (SQLException e) \{  
e.printStackTrace();  
\}  
return flag;  
改：public boolean update(Product product) \{  
boolean flag=false;  
try \{  
conn=jdbc.getConnection();  
String sql=“update product set name=?,ordertime=?,quantity=?,totalprice=? where orderid=?”;  
PreparedStatement st=conn.prepareStatement(sql);  
st.setString(1, product.getName());  
st.setString(2, product.getOrdertime());  
st.setInt(3, product.getQuantity());  
st.setDouble(4, product.getTotalprice());  
st.setInt(5, product.getOrderid());  
if (st.execute()) \{  
flag=true;  
\}  
st.close();  
conn.close();  
\}catch (ClassNotFoundException e) \{  
e.printStackTrace();  
\} catch (SQLException e) \{  
e.printStackTrace();  
\}  
return flag;  
sql语句，方法。  
连接数据库方法。

import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.SQLException;

public class JDBCComon \{

public Connection getConnection() throws ClassNotFoundException, SQLException\{

Class.forName("com.mysql.cj.jdbc.Driver");
    	//java10是数据库的名字
    	String url="jdbc:mysql://localhost:3306/java10?useSSL=false&serverTimezone=UTC";
    	//登录数据库用户名
    	String username="root";
    	//登录数据库密码
    	String pwd="ymk677467";
    	Connection conn = DriverManager.getConnection(url,username,pwd);
    	return conn;

运行测试  
ProductService ps=new ProductService();  
// 测试添加  
// Product p=new Product();  
// p.setOrdertime(“8点”);  
// p.setQuantity(2);  
// p.setName(“蒜香猪蹄饭”);  
// p.setTotalprice(38);  
// ps.add§;  
//测试删除

// ps.remove(5);  
//查询  
// Product p1=ps.findByName(“黄焖鸡米饭”);  
// System.out.println(p1.getOrdertime());  
// System.out.println(p1.getName());  
// System.out.println(p1.getQuantity());  
// System.out.println(p1.getTotalprice());  
//修改  
// Product p1=new Product();  
// p1.setTotalprice(50);  
// ps.update(p1);  
ArrayList ls=ps.selectAll();  
for(int i=0;i<ls.size();i++)\{  
Product p2=(Product) ls.get(i);  
System.out.println(p2.getName());  
System.out.println(p2.getOrdertime());  
System.out.println(p2.getQuantity());  
System.out.println(p2.getTotalprice());  
System.out.println("--------------------");  
\}

}