JDBC操作数据库,比如修改电商数据库中的分类的id,让各商品随机
1 package CRM;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.sql.Statement;
8
9
10
11 public class JDBC {
12 public static void main(String[] args) throws ClassNotFoundException, SQLException {
13 //1、加载驱动
14 Class.forName("com.mysql.jdbc.Driver");
15
16 //2、建立连接
17 String url01="jdbc:mysql://localhost:13306/ecshop?"
18 + "user=root&password=123456&allowMultiQuerises=true;";
19
20 Connection con=null;
21 try {
22 con=DriverManager.getConnection(url01);
23 System.out.println("建立成功");
24 } catch (SQLException e) {
25 System.out.println("建立失败");
26 }
27
28
29 //3、操作句柄
30 String sql="select goods_name from ecs_goods where goods_name like '测试%';",
31 sql1="select cat_id from ecs_category;";
32 //sql2="update ecs_goods set cat_id =${catid} where goods_name";
33 Statement stmt=null,stmt1=null,stmt2=null;
34 try {
35 stmt=con.createStatement();
36 stmt1=con.createStatement();
37 stmt2=con.createStatement();
38 } catch (SQLException e) {
39 }
40
41
42 //进行数据库查询
43 ResultSet rs=stmt.executeQuery(sql),rs1=stmt1.executeQuery(sql1);
44
45 int n = rs1.getMetaData().getColumnCount(),n1 = rs.getMetaData().getColumnCount();
46 //(1)获取商品分类
47 String s="";
48 while (rs1.next()){
49 String s1=rs1.getString(n);
50 if (s==""){
51 s=s1;
52 }else{
53 s=s+","+s1;
54 }
55
56 }
57 System.out.println(s);
58 String [] s1= s.split(",");
59
60 //(2)获取需要修改的商品
61 s="";
62
63 while (rs.next()){
64 String s2=rs.getString(n);
65 if (s==""){
66 s=s2;
67 }else{
68 s=s+","+s2;
69 }
70
71 }
72
73 String [] s2= s.split(",");
74 System.out.println(s2[1]);
75
76 //(3)随机修改商品的分类
77 for(int i = 0 ; i<s2.length;i++){
78
79 stmt2.executeUpdate("update ecs_goods set cat_id ='"+s1[(int) (Math.random()*s1.length)]
80 +"'where goods_name='"+s2[i]+"';");
81
82 }
83
84
85
86 }
87 }