Java从零码起11-通过JDBC实现数据库的增删改查操作

需求：通过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(); }}