准备工作
最底层数据库表tb_brand
创建实体类Brand来和数据库表对接
创建测试用例.java
数据库操作
准备数据库表tb_brand
如图所示,创建一个表,并且插入若干信息
实体类Brand
1,在pojo层里面创建文件Brand.java,并且输入与数据库对应的表格内容
在实体类中,基本数据类型需要使用其对应的包装类型
如下所示
在Brand括号内按alt+ins来选择setter和getter以及toString来生成以下
测试文件
写一个测试用例example文件夹下的BrandTest.java
增删查改
查询:查询所有数据
添加:添加品牌
修改:根据id修改
删除:根据id删除
查询流程
1.获取Connection
2.写SQL语句
3.获取PreparedStatement对象
4.设置参数
5.执行SQL
6.处理结果List< Brand>
7.释放资源
在example文件夹下的BrandTest.java中这么写:
package com.itheima.example;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.itheima.pojo.Brand;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
//品牌数据的增删改查操作
//下面就写怎删改查的操作方法就好了
public class BrandTest {
// 查询所有
// 1.SQL:select * from tb_brand;
// 2.参数:不需要
// 3.结果:List<Brand>
@Test
public void testSelectAll() throws Exception {
//1.获取connection对象
//加载配置文件,如果文件相对路径报错就用绝对路径!
Properties prop = new Properties();
prop.load(new FileInputStream("C:\\Users\\Lenovo\\Desktop\\jdbc-demo\\src\\druid.properties"));
//获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//2.定义SQL语句
String sql = "select * from tb_brand;";
//3.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4.设置参数
//5.执行sql
ResultSet rs = pstmt.executeQuery();
//6.处理结果List<Brand>,封装Brand对象,然后加载到List集合中去
Brand brand = null;
List<Brand> brands = 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");
//封装Brand对象
brand = new Brand();
brand.setId(id);
brand.setBrandName(brandName);
brand.setCompanyName(companyName);
brand.setOrdered(ordered);
brand.setDescription(description);
brand.setStatus(status);
//装载到List集合去
brands.add(brand);
}
System.out.println(brands);
//7.释放资源
rs.close();
pstmt.close();
conn.close();
}
}
然后运行BrandTest会发现输出成功,所以查询成功
添加流程
1.写sql语句
2.参数(除了id之外的所有数据)
3.返回结果的封装(T/F即可)
// 增加
// 1.SQL:insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?);
// 2.参数:需要除id以外的所有参数
// 3.结果:boolean类型
@Test
public void testAdd() throws Exception {
//模拟接收页面提交参数
String brandName = "哇哈哈哈哈";
String companyName = "哇哈哈集集团团";
int ordered = 1;
String description = "一万年";
int status = 1;
//1.获取connection对象
//加载配置文件,如果文件相对路径报错就用绝对路径!
Properties prop = new Properties();
prop.load(new FileInputStream("C:\\Users\\Lenovo\\Desktop\\jdbc-demo\\src\\druid.properties"));
//获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//2.定义SQL语句
String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?);";
//3.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4.设置参数
pstmt.setString(1, brandName);
pstmt.setString(2, companyName);
pstmt.setInt(3, ordered);
pstmt.setString(4, description);
pstmt.setInt(5, status);
//5.执行sql
int count = pstmt.executeUpdate();//影响行数
//6.处理结果
System.out.println(count > 0);
//7.释放资源
pstmt.close();
conn.close();
}
修改流程
1.写sql语句
2.参数(所有数据)
3.返回结果的封装(T/F即可)
// 修改
// 1.SQL:update tb_brand set brand_name = ?,company_name = ?,ordered = ?,description = ?,status = ? where id = ?;
// 2.参数:需要所有参数
// 3.结果:boolean类型
@Test
public void testUpdate() throws Exception {
//模拟接收页面提交参数
String brandName = "哇哈哈";
String companyName = "哇哈哈集集团团";
int ordered = 1000;
String description = "一千万年";
int status = 1;
int id = 8;
//1.获取connection对象
//加载配置文件,如果文件相对路径报错就用绝对路径!
Properties prop = new Properties();
prop.load(new FileInputStream("C:\\Users\\Lenovo\\Desktop\\jdbc-demo\\src\\druid.properties"));
//获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//2.定义SQL语句
String sql = "update tb_brand " +
" set brand_name = ?," +
" company_name = ?," +
" ordered = ?," +
" description = ?," +
" status = ? " +
" where id = ?;";
//3.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4.设置参数
pstmt.setString(1, brandName);
pstmt.setString(2, companyName);
pstmt.setInt(3, ordered);
pstmt.setString(4, description);
pstmt.setInt(5, status);
pstmt.setInt(6, id);
//5.执行sql
int count = pstmt.executeUpdate();//影响行数
//6.处理结果
System.out.println(count > 0);
//7.释放资源
pstmt.close();
conn.close();
}
}
删除流程
1.写sql语句
2.参数(id)
3.返回结果的封装(T/F即可)
// 删除
// 1.SQL:delete from tb_brand where id = ?;
// 2.参数:需要id
// 3.结果:boolean类型
@Test
public void testDeleteById() throws Exception {
//模拟接收页面提交参数
int id = 8;
//1.获取connection对象
//加载配置文件,如果文件相对路径报错就用绝对路径!
Properties prop = new Properties();
prop.load(new FileInputStream("C:\\Users\\Lenovo\\Desktop\\jdbc-demo\\src\\druid.properties"));
//获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//获取数据库连接 Connection
Connection conn = dataSource.getConnection();
//2.定义SQL语句
String sql = "delete from tb_brand where id = ?;";
//3.获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//4.设置参数
pstmt.setInt(1, id);
//5.执行sql
int count = pstmt.executeUpdate();//影响行数
//6.处理结果
System.out.println(count > 0);
//7.释放资源
pstmt.close();
conn.close();
}