CREATE DATABASE produces;

USE produces;

--创建商品信息表goods

CREATE TABLE goods(

id INT AUTO_INCREMENT KEY,

NAME VARCHAR(20)NOT NULL,

price FLOAT(5,1) NOT NULL,

desp VARCHAR(30) NOT NULL

)ENGINE=INNODB CHARSET='UTF8';

--为商品信息表添加内容

INSERT INTO goods(NAME,price,desp) VALUES('手机',2000,'黑色，存储容量为32G'),

('冰箱',1500,'银色，对开门'),

('洗衣机',3000,'滚筒'),

('空调',4000,'变频空调');

package com.imooc.jdbc.demo1;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import org.junit.Test;

import com.mysql.jdbc.Driver;

public class Goodstest3 {

@Test

public void goods() {

Connection conn=null;

Statement stmt=null;

try {

//1.注册驱动

Class.forName("com.mysql.jdbc.Driver");

//2.获得连接

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/produces", "root", "123");

//3.获得执行sql语句的对象

stmt = conn.createStatement();

//4.编写sql语句 

//为goods表删除一条数据

String sql = "delete from goods where NAME='耳机'";

// String sql = "delete from goods where NAME='空调'";

//执行sql语句

int i = stmt.executeUpdate(sql);

if(i > 0) {

sql = "select * from goods";

ResultSet resultSet = stmt.executeQuery(sql);

while(resultSet.next()) {

int id = resultSet.getInt("id");

String name = resultSet.getString("NAME");

String price = resultSet.getString("price");

String desp = resultSet.getString("desp");

System.out.println(id+"            "+name+"           "+price+"           "+desp);

}

}

else {

System.out.println("删除失败");

}

}catch(Exception e) {

e.printStackTrace();

}finally {

if(stmt != null) {

}try {

stmt.close();

}catch(Exception e) {

e.printStackTrace();

}

stmt = null;

if(conn != null) {

}try {

conn.close();

}catch(Exception e) {

e.printStackTrace();

}

}

}

}