需求：通过jdbc完成品牌数据库的增删改查操作
1.在数据库中添加一张Brand表，保存品牌的信息
-- 删除tb_brand表 如果存在
DROp TABLE IF EXISTS tb_brand;
-- 创建tb_brand表
CREATE TABLE tb_brand(
-- id主键
id INT PRIMARY KEY auto_increment,
-- 品牌名称
brand_name VARCHAr(32),
-- 企业名称
company_name VARCHAr(32),
-- 排序字段
ordered INT,
-- 描述信息
decription VARCHAr(64),
-- 状态 ：0-禁用 1-启用
statu tinyint
);
-- 添加数据
INSERT INTO tb_brand (brand_name,company_name,ordered,decription,status)
VALUES('三只松鼠','三只松鼠有限公司',5,'好吃不上火',0),
('华为','华为技术有限公司',100,'华为致力于把数字世界带入每个人、每个家庭、每个组织，构建万物互联的智能世界',1),
('小米','小米科技有限公司',100,'are you ok',1);
-- 查询表的数据
SELECT * FROM tb_brand;
2.创建Brand类，用于获取并创建品牌对象
public class Brand {
private Integer id;
private String brandName;
private String companyName;
private String description;
private Integer ordered;
private Integer status;
public Brand(){}
public Brand(Integer id, String brandName, String companyName, String description, Integer ordered, Integer status) {
this.id = id;
this.brandName = brandName;
this.companyName = companyName;
this.description = description;
this.ordered = ordered;
this.status = status;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getBrandName() {
return brandName;
}
public void setBrandName(String brandName) {
this.brandName = brandName;
}
public String getCompanyName() {
return companyName;
}
public void setCompanyName(String companyName) {
this.companyName = companyName;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public Integer getOrdered() {
return ordered;
}
public void setOrdered(Integer ordered) {
this.ordered = ordered;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
@Override
public String toString() {
return "id:" + id + ", 品牌名称:" + brandName + ", 企业全称:" + companyName + ", 企业描述:" + description + ", 企业地位:" + ordered + ", 经营状态:" + status+'n';
}
}
3.创建Demo类，实现对brand表增删改查的操作
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;
public class Demo {
public static void main(String[] args) throws Exception {
// 连接数据库
String url="jdbc:mysql:///panda?useSSL=false&useServerPrepStmts=true";
String user="root";
String password="1234";
Connection conn = DriverManager.getConnection(url, user, password);
// 执行sql
Scanner sc = new Scanner(System.in);
boolean flog=true;
while (flog) {
System.out.println("1.查询数据库所有数据,2.添加一条数据,3.修改一条数据,4.删除一条数据,0.退出");
switch (sc.nextInt()) {
case 1:
selectAll(conn);
break;
case 2:
addBrand(conn);
break;
case 3:
modify(conn);
break;
case 4:
delete(conn);
break;
case 0:
flog=false;
break;
default:
System.out.println("error");
}
}
// 释放资源
conn.close();
}
private static void selectAll(Connection conn) throws SQLException {
//定义sql
String sql = "select * from tb_brand";
//创建PreparedStatement对象
PreparedStatement ps = conn.prepareStatement(sql);
//设置参数
//执行sql
ResultSet res = ps.executeQuery();
//处理结果
List brands = new ArrayList<>();
while (res.next()) {
//获取结果
int id = res.getInt("id");
String brandName = res.getString("brand_name");
String companyName = res.getString("company_name");
int ordered = res.getInt("ordered");
String description = res.getString("description");
int status = res.getInt("status");
//封装Brand对象
Brand brand = new Brand(id, brandName, companyName, description, ordered, status);
//写入集合
brands.add(brand);
}
//输出集合
System.out.println(brands);
//释放资源
ps.close();
}
private static void addBrand(Connection conn) throws SQLException {
//定义sql语句
String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?)";
//创建PreparedStatement对象
PreparedStatement ps = conn.prepareStatement(sql);
//设置参数,从键盘获取
Scanner sc = new Scanner(System.in);
System.out.println("输入品牌名称:");
ps.setString(1, sc.next());
System.out.println("输入企业全称:");
ps.setString(2, sc.next());
System.out.println("输入企业排序:");
ps.setInt(3, sc.nextInt());
System.out.println("输入企业描述:");
ps.setString(4, sc.next());
System.out.println("输入企业状态:");
ps.setInt(5, sc.nextInt());
//执行sql
int count = ps.executeUpdate();
//输出结果
System.out.println(count > 0);
//释放资源
ps.close();
}
public static void modify(Connection conn) throws SQLException {
//定义sql语句
String sql = "update tb_brand set brand_name=? ,company_name=?,ordered=? ,description=? ,status=? where id=?";
//创建PreparedStatement
PreparedStatement ps = conn.prepareStatement(sql);
//设置参数,键盘输入
Scanner sc = new Scanner(System.in);
System.out.println("输入要修改的ID:");
ps.setInt(6, sc.nextInt());
System.out.println("输入品牌名称:");
ps.setString(1, sc.next());
System.out.println("输入企业全称:");
ps.setString(2, sc.next());
System.out.println("输入企业排序:");
ps.setInt(3, sc.nextInt());
System.out.println("输入企业描述:");
ps.setString(4, sc.next());
System.out.println("输入企业状态:");
ps.setInt(5, sc.nextInt());
//执行sql
int count = ps.executeUpdate();
System.out.println(count > 0);
//释放资源
ps.close();
}
public static void delete(Connection conn) throws SQLException {
//定义sql
String sql = "delete from tb_brand where id= ?";
//创建PreparedStatement
PreparedStatement ps = conn.prepareStatement(sql);
//设置参数
Scanner sc = new Scanner(System.in);
System.out.println("输入要删除的id:");
ps.setInt(1, sc.nextInt());
//执行sql
int count = ps.executeUpdate();
//处理结果
System.out.println(count > 0);
//释放资源
ps.close();
}
}
