JDBC增删改查练习案例

目录

先创建数据库表

查询全部信息

步骤： 非红色为不变的步骤

添加

根据ID更新数据

根据ID删除数据

---

先创建数据库表

CREATE DATABASE if not EXISTS  info char SET utf8;
use info;

CREATE table tb_brand(
	id  int PRIMARY key auto_increment,
	brand_name varchar(20) COMMENT '商品名称',
  company_name VARCHAR(20) COMMENT' 企业名称',
	ordered int COMMENT '排序字段',
	description VARCHAR(100) COMMENT'描述信息',
	`status` int -- 状态信息，  0禁用，1启用
);

INSERT into tb_brand (brand_name,company_name,ordered,description,status) 
VALUES('苹果手机','苹果公司', 5,'苹果无敌',0),
			('华为手机','华为公司', 100,'华为无敌',1),
			('小米手机','小米公司',50 ,'小米无敌',1);
			
SELECT * FROM tb_brand;

查询全部信息

步骤： 非红色为不变的步骤

1: 获取connection

2：定义 sql

3：获取 PrepareStatement对象

4：设置参数

5：执行sql

6 处理结果  List<Brand>  并封装到ArrayList集合

7:释放资源

创建brank信息类

//在实体类中，基本数据类型建议使用对应的包装类型，防止默认值影响功能
public class Brand {
	private Integer id   ;
	private String	brandName ; //'商品名称'
	private String	companyName ;//' 企业名称'
	private Integer	ordered ; //'排序字段',
	private String	description; //'描述信息'
	private Integer	status ;//-- 状态信息，  0禁用，1启用
	/**
	 * @return the id
	 */
	public Integer getId() {
		return id;
	}
	/**
	 * @param id the id to set
	 */
	public void setId(Integer id) {
		this.id = id;
	}
	/**
	 * @return the brandName
	 */
	public String getBrandName() {
		return brandName;
	}
	/**
	 * @param brandName the brandName to set
	 */
	public void setBrandName(String brandName) {
		this.brandName = brandName;
	}
	/**
	 * @return the companyName
	 */
	public String getCompanyName() {
		return companyName;
	}
	/**
	 * @param companyName the companyName to set
	 */
	public void setCompanyName(String companyName) {
		this.companyName = companyName;
	}
	/**
	 * @return the ordered
	 */
	public Integer getOrdered() {
		return ordered;
	}
	/**
	 * @param ordered the ordered to set
	 */
	public void setOrdered(Integer ordered) {
		this.ordered = ordered;
	}
	/**
	 * @return the description
	 */
	public String getDescription() {
		return description;
	}
	/**
	 * @param description the description to set
	 */
	public void setDescription(String description) {
		this.description = description;
	}
	/**
	 * @return the status
	 */
	public Integer getStatus() {
		return status;
	}
	/**
	 * @param status the status to set
	 */
	public void setStatus(Integer status) {
		this.status = status;
	}
	@Override
	public String toString() {
		return "Brand [id=" + id + ", brandName=" + brandName + ", companyName=" + companyName + ", ordered=" + ordered
				+ ", description=" + description + ", status=" + status + "]";
	}
	public Brand(Integer id, String brandName, String companyName, Integer ordered, String description,
			Integer status) {
		super();
		this.id = id;
		this.brandName = brandName;
		this.companyName = companyName;
		this.ordered = ordered;
		this.description = description;
		this.status = status;
	}
	public Brand() {
		super();
	}
	

}

开始查询

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class BrandTest {
	public static void main(String[] args) throws Exception {
		//获取连接 
		String url="jdbc:mysql:///info";
		String userName="root";
		String passWord="666";
		Connection con=DriverManager.getConnection(url, userName, passWord);
		
		String sql1="select * from tb_brand";
		PreparedStatement ps=con.prepareStatement(sql1);
		ResultSet rs=ps.executeQuery();
		
		Brand brand=null;
		List< Brand> list=new ArrayList<>();
		while(rs.next()) {
			int id=rs.getInt("id");
			String  brandName =rs.getString("brand_name");
			String  companyName= rs.getString("company_name");
			int ordered= rs.getInt("ordered");
			String description= rs.getString("description");
			int status= rs.getInt("status");	
			list.add(new Brand(id, brandName, companyName, ordered, description, status));
		}
		System.out.println(list);
		rs.close();
		ps.close();
		con.close();

	}

}

添加

1编写SQL语句

2是否需要参数？需要：除id以外的所有数据

3：返回结果如何封装？ Boolean

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Test3 {
	
	public static void main(String[] args) throws Exception {
		String url="jdbc:mysql:///info";
		String userName="root";
		String password="666";
		Connection con=DriverManager.getConnection(url, userName, password);
		//模拟网页传来的信息
		String brandName="香飘飘";
		String companyName="香飘飘公司";
		int ordered=5;
		String description="绕地球一圈";
		int status=0;
		String sql="INSERT into tb_brand (brand_name,company_name,ordered,description,status) VALUES(?,?,?,?,?) ";
		PreparedStatement ps=con.prepareStatement(sql);
		//设置参数
		ps.setString(1, brandName);
		ps.setString(2, companyName);
		ps.setInt(3, ordered);
		ps.setString(4, description);
		ps.setInt(5, status);
		
		int affect=ps.executeUpdate();
		System.out.println(affect>0);
	    ps.close();
		con.close();

	}

}

根据ID更新数据

1 编写sql语句

2 是否需要参数？需要：Brand对象的所有数据

3： 返回结果如何封装Boolean

根据ID删除数据

1 编写sql语句

2是否需要参数 需要id

3 返回结果如何封装 boolean

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class deleteTest {
	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		String url="jdbc:mysql:///info";
		String userName="root";
		String password="666";
		Connection con=DriverManager.getConnection(url, userName, password);
		//模拟网页传来的信息
		int id=1;
		String sql="DELETE FROM tb_brand WHERE id=?;";
		PreparedStatement ps=con.prepareStatement(sql);
		//设置参数
		ps.setInt(1, id);	
		int affect=ps.executeUpdate();
		System.out.println(affect>0);
		ps.close();
		con.close();
	}

}