写在前面的话：
- 参考资料：尚硅谷视频
- 本章内容：如何使用Java来操作数据库，实现增加、删除、修改和查询
- IDE：eclipse
- JDK：Java8
- MySQL：mysql Ver 8.0.26 for Win64 on x86_64
- 更新内容：对数据库进行查询
拓展知识：如果不了解JDBC如何连接数据库，可以看此篇文章。
JDBC连接数据库https://blog.csdn.net/qq_56402474/article/details/124390631?spm=1001.2014.3001.5501
拓展知识：如果不知道在eclipse中导入MySQL驱动，可以看此篇文章
下载MySQL驱动，并添加到eclipse工程中https://blog.csdn.net/qq_56402474/article/details/124268415?spm=1001.2014.3001.5501
目录
1.数据库连接
2.释放连接
3.对数据库进行增删改查
3.1 通过 Statement 执行更新操作
3.2 对水果表进行添加数据
3.3 对水果表进行修改数据
3.4 对水果表进行删除数据
3.5 对水果表进行查询操作 （更新）
4.示例
4.1 JDBC进行添加操作
4.2 对数据库进行查询操作
5.浮点型数据保留两位小数
6.随机数字
7.总结（完整代码）
1.数据库连接
private static Connection getConnection() throws Exception {
// 准备连接数据库的4个字符串
String driver = null;
String jdbcUrl = null;
String jdbcUser = null;
String jdbcPassword = null;
// 创建输入流的对象
InputStream inputStream = JdbcTools.class.getClassLoader().getResourceAsStream("exer01//jdbc.properties");
// 创建Properties的对象
Properties properties = new Properties();
// 加载properties配置文件到程序中
properties.load(inputStream);
// 获取文件中的数据
driver = properties.getProperty("driver");
jdbcUrl = properties.getProperty("jdbcURL");
jdbcUser = properties.getProperty("user");
jdbcPassword = properties.getProperty("password");
// 获取数据库驱动程序
Class.forName(driver);
// 进行连接
Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword);
// 返回连接
return conn;
}
2.释放连接
private static void release(Connection connection, Statement statement) {
// 判断连接是否为空
if (statement != null) {
// 为了保证连接必须关闭,使用异常处理，防止因为statement关闭出现异常,connection未能够关闭
try {
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
3.对数据库进行增删改查
在数据库中创建一个关于水果的一张表
CREATE TABLE fruits (
id int NOT NULL,
name varchar(20),
price float,
number int
)
在VScode中如下，表示创建成功！此时还没有数据
不了解VScode中操作数据库，可以了解此篇文章
在VScode下连接MySQL并使用https://blog.csdn.net/qq_56402474/article/details/123865933?spm=1001.2014.3001.5501
3.1 通过 Statement 执行更新操作
private static void update(String sql) {
// 获取数据库连接
Connection conn = null;
Statement statement = null;
try {
// 获取连接
conn = getConnection();
statement = conn.createStatement();
// 执行SQL语句
statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放连接
release(conn, statement);
}
}
3.2 对水果表进行添加数据
在上面private static void update(String sql)下，添加以下代码。
public static void insert(int id,String name,float price,int number){
String sql = null;
//INSERT INTO fruits VALUES(1,'苹果',7.8,50); [仿造格式]
//写出SQL语句
sql = "INSERT INTO fruits VALUES(" + id +",'" +
name + "'," + price + "," + number+");";
//执行操作
update(sql);
}
3.3 对水果表进行修改数据
public static void changeId(int id,String which_fruit) {
String sql = null;
//UPDATE fruits SET id = 101 WHERe name = '苹果';
sql = "UPDATE fruits SET id = " + id + " WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
public static void changePrice(float price,String which_fruit) {
String sql = null;
//UPDATE fruits SET id = 101 WHERe name = '苹果';
sql = "UPDATE fruits SET price = " + price + " WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
public static void changeNumber(int number,String which_fruit) {
String sql = null;
//UPDATE fruits SET id = 101 WHERe name = '苹果';
sql = "UPDATE fruits SET number = " + number + " WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
3.4 对水果表进行删除数据
public static void delete(String which_fruit) {
String sql = null;
//DELETE FROM fruits WHERe name = '苹果';
sql = "DELETe FROM fruits WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
3.5 对水果表进行查询操作 （更新）
查询需要6步操作
- 获取连接
- 获取Statement
- 获取SQL语句
- 执行SQL语句，并返回一个结果集
- 将结果集中的数据打印输出到控制台上
- 释放连接
public static void query() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;//结果集：用来接受查询到的数据
try {
//1.获取连接
connection = getConnection();
//2.获取Statement
statement = connection.createStatement();
//3.获取SQL语句
String sql = "SELECT * FROM fruits;";
//4.执行查询,并将结果(整张数据表)返回给resultSet进行保存
resultSet = statement.executeQuery(sql);
//5.在控制台上打印输出
while(resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
float price = resultSet.getFloat("price");
int number = resultSet.getInt("number");
System.out.println(id + "t" + name + "t" + String.format("%.2f",price) + "元t" + number + "个");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//6.释放连接
release(connection, statement, resultSet);
}
}
private static void release(Connection connection, Statement statement) {
// 判断连接是否为空
if (statement != null) {
// 为了保证连接必须关闭,使用异常处理，防止因为statement关闭出现异常,connection未能够关闭
try {
statement.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
3.对数据库进行增删改查
在数据库中创建一个关于水果的一张表
CREATE TABLE fruits (
id int NOT NULL,
name varchar(20),
price float,
number int
)
在VScode中如下，表示创建成功！此时还没有数据
不了解VScode中操作数据库，可以了解此篇文章
在VScode下连接MySQL并使用https://blog.csdn.net/qq_56402474/article/details/123865933?spm=1001.2014.3001.5501
3.1 通过 Statement 执行更新操作
private static void update(String sql) {
// 获取数据库连接
Connection conn = null;
Statement statement = null;
try {
// 获取连接
conn = getConnection();
statement = conn.createStatement();
// 执行SQL语句
statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放连接
release(conn, statement);
}
}
3.2 对水果表进行添加数据
在上面private static void update(String sql)下，添加以下代码。
public static void insert(int id,String name,float price,int number){
String sql = null;
//INSERT INTO fruits VALUES(1,'苹果',7.8,50); [仿造格式]
//写出SQL语句
sql = "INSERT INTO fruits VALUES(" + id +",'" +
name + "'," + price + "," + number+");";
//执行操作
update(sql);
}
3.3 对水果表进行修改数据
public static void changeId(int id,String which_fruit) {
String sql = null;
//UPDATE fruits SET id = 101 WHERe name = '苹果';
sql = "UPDATE fruits SET id = " + id + " WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
public static void changePrice(float price,String which_fruit) {
String sql = null;
//UPDATE fruits SET id = 101 WHERe name = '苹果';
sql = "UPDATE fruits SET price = " + price + " WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
public static void changeNumber(int number,String which_fruit) {
String sql = null;
//UPDATE fruits SET id = 101 WHERe name = '苹果';
sql = "UPDATE fruits SET number = " + number + " WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
3.4 对水果表进行删除数据
public static void delete(String which_fruit) {
String sql = null;
//DELETE FROM fruits WHERe name = '苹果';
sql = "DELETe FROM fruits WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
3.5 对水果表进行查询操作 （更新）
查询需要6步操作
- 获取连接
- 获取Statement
- 获取SQL语句
- 执行SQL语句，并返回一个结果集
- 将结果集中的数据打印输出到控制台上
- 释放连接
public static void query() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;//结果集：用来接受查询到的数据
try {
//1.获取连接
connection = getConnection();
//2.获取Statement
statement = connection.createStatement();
//3.获取SQL语句
String sql = "SELECT * FROM fruits;";
//4.执行查询,并将结果(整张数据表)返回给resultSet进行保存
resultSet = statement.executeQuery(sql);
//5.在控制台上打印输出
while(resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
float price = resultSet.getFloat("price");
int number = resultSet.getInt("number");
System.out.println(id + "t" + name + "t" + String.format("%.2f",price) + "元t" + number + "个");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//6.释放连接
release(connection, statement, resultSet);
}
}
在数据库中创建一个关于水果的一张表
CREATE TABLE fruits ( id int NOT NULL, name varchar(20), price float, number int )
在VScode中如下，表示创建成功！此时还没有数据
不了解VScode中操作数据库，可以了解此篇文章
在VScode下连接MySQL并使用https://blog.csdn.net/qq_56402474/article/details/123865933?spm=1001.2014.3001.5501
private static void update(String sql) {
// 获取数据库连接
Connection conn = null;
Statement statement = null;
try {
// 获取连接
conn = getConnection();
statement = conn.createStatement();
// 执行SQL语句
statement.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
} finally {
// 释放连接
release(conn, statement);
}
}
3.2 对水果表进行添加数据
在上面private static void update(String sql)下，添加以下代码。
public static void insert(int id,String name,float price,int number){
String sql = null;
//INSERT INTO fruits VALUES(1,'苹果',7.8,50); [仿造格式]
//写出SQL语句
sql = "INSERT INTO fruits VALUES(" + id +",'" +
name + "'," + price + "," + number+");";
//执行操作
update(sql);
}
3.3 对水果表进行修改数据
public static void changeId(int id,String which_fruit) {
String sql = null;
//UPDATE fruits SET id = 101 WHERe name = '苹果';
sql = "UPDATE fruits SET id = " + id + " WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
public static void changePrice(float price,String which_fruit) {
String sql = null;
//UPDATE fruits SET id = 101 WHERe name = '苹果';
sql = "UPDATE fruits SET price = " + price + " WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
public static void changeNumber(int number,String which_fruit) {
String sql = null;
//UPDATE fruits SET id = 101 WHERe name = '苹果';
sql = "UPDATE fruits SET number = " + number + " WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
3.4 对水果表进行删除数据
public static void delete(String which_fruit) {
String sql = null;
//DELETE FROM fruits WHERe name = '苹果';
sql = "DELETe FROM fruits WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
3.5 对水果表进行查询操作 （更新）
查询需要6步操作
- 获取连接
- 获取Statement
- 获取SQL语句
- 执行SQL语句，并返回一个结果集
- 将结果集中的数据打印输出到控制台上
- 释放连接
public static void query() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;//结果集：用来接受查询到的数据
try {
//1.获取连接
connection = getConnection();
//2.获取Statement
statement = connection.createStatement();
//3.获取SQL语句
String sql = "SELECT * FROM fruits;";
//4.执行查询,并将结果(整张数据表)返回给resultSet进行保存
resultSet = statement.executeQuery(sql);
//5.在控制台上打印输出
while(resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
float price = resultSet.getFloat("price");
int number = resultSet.getInt("number");
System.out.println(id + "t" + name + "t" + String.format("%.2f",price) + "元t" + number + "个");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//6.释放连接
release(connection, statement, resultSet);
}
}
在上面private static void update(String sql)下，添加以下代码。
public static void changeId(int id,String which_fruit) {
String sql = null;
//UPDATE fruits SET id = 101 WHERe name = '苹果';
sql = "UPDATE fruits SET id = " + id + " WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
public static void changePrice(float price,String which_fruit) {
String sql = null;
//UPDATE fruits SET id = 101 WHERe name = '苹果';
sql = "UPDATE fruits SET price = " + price + " WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
public static void changeNumber(int number,String which_fruit) {
String sql = null;
//UPDATE fruits SET id = 101 WHERe name = '苹果';
sql = "UPDATE fruits SET number = " + number + " WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
3.4 对水果表进行删除数据
public static void delete(String which_fruit) {
String sql = null;
//DELETE FROM fruits WHERe name = '苹果';
sql = "DELETe FROM fruits WHERe name = " + "'" + which_fruit +"';";
update(sql);
}
3.5 对水果表进行查询操作 （更新）
查询需要6步操作
- 获取连接
- 获取Statement
- 获取SQL语句
- 执行SQL语句，并返回一个结果集
- 将结果集中的数据打印输出到控制台上
- 释放连接
public static void query() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;//结果集：用来接受查询到的数据
try {
//1.获取连接
connection = getConnection();
//2.获取Statement
statement = connection.createStatement();
//3.获取SQL语句
String sql = "SELECT * FROM fruits;";
//4.执行查询,并将结果(整张数据表)返回给resultSet进行保存
resultSet = statement.executeQuery(sql);
//5.在控制台上打印输出
while(resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
float price = resultSet.getFloat("price");
int number = resultSet.getInt("number");
System.out.println(id + "t" + name + "t" + String.format("%.2f",price) + "元t" + number + "个");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//6.释放连接
release(connection, statement, resultSet);
}
}
查询需要6步操作
- 获取连接
- 获取Statement
- 获取SQL语句
- 执行SQL语句，并返回一个结果集
- 将结果集中的数据打印输出到控制台上
- 释放连接
public static void query() {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;//结果集：用来接受查询到的数据
try {
//1.获取连接
connection = getConnection();
//2.获取Statement
statement = connection.createStatement();
//3.获取SQL语句
String sql = "SELECT * FROM fruits;";
//4.执行查询,并将结果(整张数据表)返回给resultSet进行保存
resultSet = statement.executeQuery(sql);
//5.在控制台上打印输出
while(resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
float price = resultSet.getFloat("price");
int number = resultSet.getInt("number");
System.out.println(id + "t" + name + "t" + String.format("%.2f",price) + "元t" + number + "个");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//6.释放连接
release(connection, statement, resultSet);
}
}
4.示例
4.1 JDBC进行添加操作
在最后，做一个简单的小例子。在数据库中添加多个水果。
import java.text.DecimalFormat;
import java.util.Random;
import org.junit.Test;
public class JdbcTest {
public static void main(String[] args) {
String fruits[] = {
"沙果",
"海棠",
"野樱莓",
"枇杷",
"欧楂",
"山楂",
"杏",
"樱桃",
"桃",
"李子",
"梅子",
"橘子",
"砂糖桔",
"橙子",
"柠檬",
"青柠",
"柚子",
"金桔",
"葡萄柚",
"香橼",
"佛手",
"指橙",
"黄皮果",
"哈密瓜",
"香瓜",
"白兰瓜",
"刺角瓜",
"金铃子",
"草莓",
"菠萝莓",
"黑莓",
"覆盆子",
"云莓",
"罗甘莓",
"白里叶莓",
"葡萄",
"提子",
"醋栗",
"黑醋栗",
"红醋栗",
"蓝莓",
"蔓越莓",
"越橘",
"乌饭果",
"柿子",
"黑枣",
"香蕉",
"大蕉",
"南洋红香",
"无花果",
"菠萝蜜",
"构树果实",
"牛奶果",
"桑葚",
"火龙果",
"黄龙果",
"红心火龙果",
"仙人掌果",
"荔枝",
"龙眼",
"红毛丹",
"榴莲",
"猴面包果",
"阳桃",
"三敛果",
"椰子",
"槟榔",
"海枣",
"蛇皮果",
"莲雾",
"嘉宝果",
"番石榴",
"菲油果",
"苏里南苦樱桃",
"枸杞",
"香瓜茄",
"灯笼果",
"圣女果",
"芒果",
"山竹",
"柑橘",
"莲子"};
//调用JdbcTools里面的方法直接操控数据库
for(int i = 1;i < 21;i++) {
JdbcTools.insert(i,fruits[i-1],getPrice(),100);
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
System.out.println("=====数据库操作完成=====");
}
public static float getPrice() {
float price = 0f;
price = new Random().nextInt(10) + new Random().nextFloat() + 1.0f;
//浮点型数据保留两位小数
DecimalFormat decimalFormat = new DecimalFormat("#.00");
//以字符串的形式将保留2位小数的浮点型数据保留下来
String s = decimalFormat.format(price);
//将其转换回来，还是为float型数据
float f = Float.parseFloat(s);
return f;
}
}
在最后，做一个简单的小例子。在数据库中添加多个水果。
import java.text.DecimalFormat;
import java.util.Random;
import org.junit.Test;
public class JdbcTest {
public static void main(String[] args) {
String fruits[] = {
"沙果",
"海棠",
"野樱莓",
"枇杷",
"欧楂",
"山楂",
"杏",
"樱桃",
"桃",
"李子",
"梅子",
"橘子",
"砂糖桔",
"橙子",
"柠檬",
"青柠",
"柚子",
"金桔",
"葡萄柚",
"香橼",
"佛手",
"指橙",
"黄皮果",
"哈密瓜",
"香瓜",
"白兰瓜",
"刺角瓜",
"金铃子",
"草莓",
"菠萝莓",
"黑莓",
"覆盆子",
"云莓",
"罗甘莓",
"白里叶莓",
"葡萄",
"提子",
"醋栗",
"黑醋栗",
"红醋栗",
"蓝莓",
"蔓越莓",
"越橘",
"乌饭果",
"柿子",
"黑枣",
"香蕉",
"大蕉",
"南洋红香",
"无花果",
"菠萝蜜",
"构树果实",
"牛奶果",
"桑葚",
"火龙果",
"黄龙果",
"红心火龙果",
"仙人掌果",
"荔枝",
"龙眼",
"红毛丹",
"榴莲",
"猴面包果",
"阳桃",
"三敛果",
"椰子",
"槟榔",
"海枣",
"蛇皮果",
"莲雾",
"嘉宝果",
"番石榴",
"菲油果",
"苏里南苦樱桃",
"枸杞",
"香瓜茄",
"灯笼果",
"圣女果",
"芒果",
"山竹",
"柑橘",
"莲子"};
//调用JdbcTools里面的方法直接操控数据库
for(int i = 1;i < 21;i++) {
JdbcTools.insert(i,fruits[i-1],getPrice(),100);
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
System.out.println("=====数据库操作完成=====");
}
public static float getPrice() {
float price = 0f;
price = new Random().nextInt(10) + new Random().nextFloat() + 1.0f;
//浮点型数据保留两位小数
DecimalFormat decimalFormat = new DecimalFormat("#.00");
//以字符串的形式将保留2位小数的浮点型数据保留下来
String s = decimalFormat.format(price);
//将其转换回来，还是为float型数据
float f = Float.parseFloat(s);
return f;
}
}
测试截图（只取部分）
4.2 对数据库进行查询操作
package exer01;
import java.text.DecimalFormat;
import java.util.Random;
import org.junit.Test;
public class JdbcTest {
public static void main(String[] args) {
//对数据库进行查询
JdbcTools.query();
}
}
截图：
截图：
5.浮点型数据保留两位小数
浮点型数据保留2位有效数据https://blog.csdn.net/weixin_44985880/article/details/120512282
本文中的案例：
public class Price { public static void main(String[] args) { float price = 0f; // price = new Random().nextFloat(10) + 1.0f; price = new Random().nextInt(10) + new Random().nextFloat() + 1.0f; //浮点型数据保留两位小数 DecimalFormat decimalFormat = new DecimalFormat("#.00"); System.out.println(decimalFormat.format(price)); String s = decimalFormat.format(price); System.out.println(s); //将其转换回来，还是为float型数据 float f = Float.parseFloat(s); System.out.println("===========" + f); } }截图：
6.随机数字
根据情况，自行调整
price = new Random().nextInt(10) + new Random().nextFloat() + 1.0f;
7.总结（完整代码）
项目结构：
jdbc.properties文件
JdbcTest.java
package exer01;
import java.text.DecimalFormat;
import java.util.Random;
import org.junit.Test;
public class JdbcTest {
public static void main(String[] args) {
JdbcTools.query();
}
public void test1() {
String fruits[] = { "沙果", "海棠", "野樱莓", "枇杷", "欧楂", "山楂", "杏", "樱桃", "桃", "李子", "梅子", "橘子", "砂糖桔", "橙子", "柠檬",
"青柠", "柚子", "金桔", "葡萄柚", "香橼", "佛手", "指橙", "黄皮果", "哈密瓜", "香瓜", "白兰瓜", "刺角瓜", "金铃子", "草莓", "菠萝莓", "黑莓",
"覆盆子", "云莓", "罗甘莓", "白里叶莓", "葡萄", "提子", "醋栗", "黑醋栗", "红醋栗", "蓝莓", "蔓越莓", "越橘", "乌饭果", "柿子", "黑枣", "香蕉",
"大蕉", "南洋红香", "无花果", "菠萝蜜", "构树果实", "牛奶果", "桑葚", "火龙果", "黄龙果", "红心火龙果", "仙人掌果", "荔枝", "龙眼", "红毛丹", "榴莲",
"猴面包果", "阳桃", "三敛果", "椰子", "槟榔", "海枣", "蛇皮果", "莲雾", "嘉宝果", "番石榴", "菲油果", "苏里南苦樱桃", "枸杞", "香瓜茄", "灯笼果",
"圣女果", "芒果", "山竹", "柑橘", "莲子" };
// 调用JdbcTools里面的方法直接操控数据库
for (int i = 1; i < 21; i++) {
JdbcTools.insert(i, fruits[i - 1], getPrice(), 100);
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
System.out.println("=====数据库操作完成=====");
}
public static float getPrice() {
float price = 0f;
price = new Random().nextInt(10) + new Random().nextFloat() + 1.0f;
// 浮点型数据保留两位小数
DecimalFormat decimalFormat = new DecimalFormat("#.00");
// 以字符串的形式将保留2位小数的浮点型数据保留下来
String s = decimalFormat.format(price);
// 将其转换回来，还是为float型数据
float f = Float.parseFloat(s);
return f;
}
}
根据情况，自行调整
项目结构：
jdbc.properties文件
JdbcTest.java
package exer01; import java.text.DecimalFormat; import java.util.Random; import org.junit.Test; public class JdbcTest { public static void main(String[] args) { JdbcTools.query(); } public void test1() { String fruits[] = { "沙果", "海棠", "野樱莓", "枇杷", "欧楂", "山楂", "杏", "樱桃", "桃", "李子", "梅子", "橘子", "砂糖桔", "橙子", "柠檬", "青柠", "柚子", "金桔", "葡萄柚", "香橼", "佛手", "指橙", "黄皮果", "哈密瓜", "香瓜", "白兰瓜", "刺角瓜", "金铃子", "草莓", "菠萝莓", "黑莓", "覆盆子", "云莓", "罗甘莓", "白里叶莓", "葡萄", "提子", "醋栗", "黑醋栗", "红醋栗", "蓝莓", "蔓越莓", "越橘", "乌饭果", "柿子", "黑枣", "香蕉", "大蕉", "南洋红香", "无花果", "菠萝蜜", "构树果实", "牛奶果", "桑葚", "火龙果", "黄龙果", "红心火龙果", "仙人掌果", "荔枝", "龙眼", "红毛丹", "榴莲", "猴面包果", "阳桃", "三敛果", "椰子", "槟榔", "海枣", "蛇皮果", "莲雾", "嘉宝果", "番石榴", "菲油果", "苏里南苦樱桃", "枸杞", "香瓜茄", "灯笼果", "圣女果", "芒果", "山竹", "柑橘", "莲子" }; // 调用JdbcTools里面的方法直接操控数据库 for (int i = 1; i < 21; i++) { JdbcTools.insert(i, fruits[i - 1], getPrice(), 100); try { Thread.sleep(1000); } catch (InterruptedException e) { e.printStackTrace(); } } System.out.println("=====数据库操作完成====="); } public static float getPrice() { float price = 0f; price = new Random().nextInt(10) + new Random().nextFloat() + 1.0f; // 浮点型数据保留两位小数 DecimalFormat decimalFormat = new DecimalFormat("#.00"); // 以字符串的形式将保留2位小数的浮点型数据保留下来 String s = decimalFormat.format(price); // 将其转换回来，还是为float型数据 float f = Float.parseFloat(s); return f; } }
JdbcTools.java
package exer01; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.Properties; import org.junit.Test; public class JdbcTools { private static Connection getConnection() throws Exception { // 准备连接数据库的4个字符串 String driver = null; String jdbcUrl = null; String jdbcUser = null; String jdbcPassword = null; // 创建输入流的对象 InputStream inputStream = JdbcTools.class.getClassLoader().getResourceAsStream("exer01//jdbc.properties"); // 创建Properties的对象 Properties properties = new Properties(); // 加载properties配置文件到程序中 properties.load(inputStream); // 获取文件中的数据 driver = properties.getProperty("driver"); jdbcUrl = properties.getProperty("jdbcURL"); jdbcUser = properties.getProperty("user"); jdbcPassword = properties.getProperty("password"); // 获取数据库驱动程序 Class.forName(driver); // 进行连接 Connection conn = DriverManager.getConnection(jdbcUrl, jdbcUser, jdbcPassword); // 返回连接 return conn; } private static void release(Connection connection, Statement statement) { // 判断连接是否为空 if (statement != null) { // 为了保证连接必须关闭,使用异常处理，防止因为statement关闭出现异常,connection未能够关闭 try { statement.close(); } catch (Exception e) { e.printStackTrace(); } } if (connection != null) { try { connection.close(); } catch (Exception e) { e.printStackTrace(); } } } private static void release(Connection connection, Statement statement,ResultSet resultSet) { //判断结果集是否为null if(resultSet != null) { try { resultSet.close(); } catch (Exception e) { e.printStackTrace(); } } // 判断连接是否为空 if (statement != null) { // 为了保证连接必须关闭,使用异常处理，防止因为statement关闭出现异常,connection未能够关闭 try { statement.close(); } catch (Exception e) { e.printStackTrace(); } } if (connection != null) { try { connection.close(); } catch (Exception e) { e.printStackTrace(); } } } private static void update(String sql) { // 获取数据库连接 Connection conn = null; Statement statement = null; try { // 获取连接 conn = getConnection(); statement = conn.createStatement(); // 执行SQL语句 statement.executeUpdate(sql); } catch (Exception e) { e.printStackTrace(); } finally { // 释放连接 release(conn, statement); } } public static void insert(int id,String name,float price,int number){ String sql = null; //INSERT INTO fruits VALUES(1,'苹果',7.8,50); [仿造格式] //写出SQL语句 sql = "INSERT INTO fruits VALUES(" + id +",'" + name + "'," + price + "," + number+");"; //执行操作 update(sql); } public static void changeId(int id,String which_fruit) { String sql = null; //UPDATE fruits SET id = 101 WHERe name = '苹果'; sql = "UPDATE fruits SET id = " + id + " WHERe name = " + "'" + which_fruit +"';"; update(sql); } public static void changePrice(float price,String which_fruit) { String sql = null; //UPDATE fruits SET id = 101 WHERe name = '苹果'; sql = "UPDATE fruits SET price = " + price + " WHERe name = " + "'" + which_fruit +"';"; update(sql); } public static void changeNumber(int number,String which_fruit) { String sql = null; //UPDATE fruits SET id = 101 WHERe name = '苹果'; sql = "UPDATE fruits SET number = " + number + " WHERe name = " + "'" + which_fruit +"';"; update(sql); } public static void delete(String which_fruit) { String sql = null; //DELETE FROM fruits WHERe name = '苹果'; sql = "DELETe FROM fruits WHERe name = " + "'" + which_fruit +"';"; update(sql); } public static void query() { Connection connection = null; Statement statement = null; ResultSet resultSet = null;//结果集：用来接受查询到的数据 try { //1.获取连接 connection = getConnection(); //2.获取Statement statement = connection.createStatement(); //3.获取SQL语句 String sql = "SELECT * FROM fruits;"; //4.执行查询,并将结果(整张数据表)返回给resultSet进行保存 resultSet = statement.executeQuery(sql); //5.在控制台上打印输出 while(resultSet.next()) { int id = resultSet.getInt("id"); String name = resultSet.getString("name"); float price = resultSet.getFloat("price"); int number = resultSet.getInt("number"); System.out.println(id + "t" + name + "t" + String.format("%.2f",price) + "元t" + number + "个"); } } catch (Exception e) { e.printStackTrace(); }finally { //6.释放连接 release(connection, statement, resultSet); } } }
贴上gitee仓库地址
下载代码https://gitee.com/star-dream-f/jdbc完
