题目
1.在MySql数据库中创建数据库dbjava,数据表book,字段包括bno,bname,price,count(编号、书名、单价、数量)。
2.利用JDBC连接数据库dbjava,实现数据表的增删 改查,要求使用PreparedStatement 。
3.统计单价在50以下的书目信息并输出,这样的书籍有多少种?
4.统计单价最高和数量最多的书籍信息并输出。
运行截图
项目结构:
第一题:
第二题:
第三题:
第四题:
源代码
第一题:
略,创建一个数据库建表,均可在Navicat上可视化操作完成,十分便捷。(其实我这么说,主要原因是建库建表的代码我没有保存,嘿嘿[手动狗头])
第二题:
package com.serein.jdbc_demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class TestCreate {
public static void main(String[] args) throws Exception{
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取链接
String url = "jdbc:mysql://127.0.0.1:3306/dbjava?useSSL=false";
String username = "xxxxxxx";
String password = "xxxxxxx";
Connection conn = DriverManager.getConnection(url,username,password);
//定义SQL语句
String sql = "insert into book(bno,bname,price,count) values (?,?,?,?);";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//设置参数
pstmt.setInt(1,5);
pstmt.setString(2,"《冬寺点外卖指南》");
pstmt.setDouble(3,47.1);
pstmt.setInt(4,99);
//执行sql
int count = pstmt.executeUpdate();
System.out.println("受影响的行数:" + count);
//释放资源
pstmt.close();
conn.close();
}
}
package com.serein.jdbc_demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class TestDelete {
public static void main(String[] args) throws Exception{
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取链接
String url = "jdbc:mysql://127.0.0.1:3306/dbjava?useSSL=false";
String username = "xxxxxxx";
String password = "xxxxxxx";
Connection conn = DriverManager.getConnection(url,username,password);
//定义SQL语句
String sql = "delete from book where bno = ?";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//设置参数
pstmt.setInt(1,5);
//执行sql
int count = pstmt.executeUpdate();
System.out.println("受影响的行数:" + count);
//释放资源
pstmt.close();
conn.close();
}
}
package com.serein.jdbc_demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestUpdate {
public static void main(String[] args) throws Exception{
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取链接
String url = "jdbc:mysql://127.0.0.1:3306/dbjava?useSSL=false";
String username = "xxxxxxx";
String password = "xxxxxxx";
Connection conn = DriverManager.getConnection(url,username,password);
//定义SQL语句
String sql = "update book set count = ? where bno = 3;";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//设置参数
pstmt.setInt(1,49);
//执行sql
int count = pstmt.executeUpdate();
System.out.println("受影响的行数:" + count);
//释放资源
pstmt.close();
conn.close();
}
}
package com.serein.jdbc_demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestRead {
public static void main(String[] args) throws Exception{
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取链接
String url = "jdbc:mysql://127.0.0.1:3306/dbjava?useSSL=false";
String username = "xxxxxxx";
String password = "xxxxxxx";
Connection conn = DriverManager.getConnection(url,username,password);
//定义SQL语句
String sql = "select * from book";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
//执行sql
ResultSet rs = pstmt.executeQuery();
//光标移动到下一行,并判断是否有数据
while (rs.next()){
//用get方法获取数据
int bno = rs.getInt("bno");
String bname = rs.getString("bname");
double price = rs.getDouble("price");
int count = rs.getInt("count");
System.out.println("编号:" + bno);
System.out.println("书名:" + bname);
System.out.println("价格:" + price);
System.out.println("数量:" + count);
System.out.println("------------------");
}
//释放资源
rs.close();
pstmt.close();
conn.close();
}
}
第三题:
package com.serein.jdbc_demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Greater50 {
public static void main(String[] args) throws Exception{
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取链接
String url = "jdbc:mysql://127.0.0.1:3306/dbjava?useSSL=false";
String username = "xxxxxxx";
String password = "xxxxxxx";
Connection conn = DriverManager.getConnection(url,username,password);
//定义SQL语句
String sql = "select * from book where price < 50;";
String sql1 = "select count(*) from book where price <50;";
//获取pstmt对象
PreparedStatement pstmt = conn.prepareStatement(sql);
PreparedStatement pstmt1 = conn.prepareStatement(sql1);
//执行sql
ResultSet rs = pstmt.executeQuery();
ResultSet rs1 = pstmt1.executeQuery();
System.out.println("单价小于50的书:");
//光标移动到下一行,并判断是否有数据
while (rs.next()){
//用get方法获取数据
int bno = rs.getInt("bno");
String bname = rs.getString("bname");
double price = rs.getDouble("price");
int count = rs.getInt("count");
System.out.println("编号:" + bno);
System.out.println("书名:" + bname);
System.out.println("价格:" + price);
System.out.println("数量:" + count);
System.out.println("------------------");
}
while (rs1.next()) {
int counts = rs1.getInt(1);
System.out.println("这样的书一共有" + counts + "种");
}
//释放资源
rs.close();
pstmt.close();
pstmt1.close();
conn.close();
}
}
第四题:
package com.serein.jdbc_demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class PriceAndCountMAX {
public static void main(String[] args) throws Exception{
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取链接
String url = "jdbc:mysql://127.0.0.1:3306/dbjava?useSSL=false";
String username = "xxxxxxx";
String password = "xxxxxxx";
Connection conn = DriverManager.getConnection(url,username,password);
//定义SQL语句
String sql1 = "select * from book where price=(select max(price) from book);";
String sql2 = "select * from book where count=(select max(count) from book);";
//获取pstmt对象
PreparedStatement pstmt1 = conn.prepareStatement(sql1);
PreparedStatement pstmt2 = conn.prepareStatement(sql2);
//执行sql
ResultSet rs1 = pstmt1.executeQuery();
ResultSet rs2 = pstmt2.executeQuery();
//光标移动到下一行,并判断是否有数据
while (rs1.next()){
//用get方法获取数据
int bno = rs1.getInt("bno");
String bname = rs1.getString("bname");
double price = rs1.getDouble("price");
int count = rs1.getInt("count");
System.out.println("单价最高的图书信息:");
System.out.println("编号:" + bno);
System.out.println("书名:" + bname);
System.out.println("价格:" + price);
System.out.println("数量:" + count);
System.out.println("------------------");
}
while (rs2.next()){
//用get方法获取数据
int bno = rs2.getInt("bno");
String bname = rs2.getString("bname");
double price = rs2.getDouble("price");
int count = rs2.getInt("count");
System.out.println("数量最多的图书信息:");
System.out.println("编号:" + bno);
System.out.println("书名:" + bname);
System.out.println("价格:" + price);
System.out.println("数量:" + count);
}
//释放资源
rs1.close();
pstmt1.close();
pstmt2.close();
conn.close();
}
}
写在最后
其实以上代码冗余很多,要变的地方很少,相信大家仔细一看就会明白。记得把username和password改成自己的sql账户密码。
看在这次注释写的这么详细的份上,请伸出你的小手为我点一个赞!如果觉得有帮助,欢迎评论点赞转发哦!你们的支持是我更新的最大动力!
加更
有一点忘记说了,该项目需要导入一个名为mysql-connector-java-5.1.48.的jar包。具体操作为在项目根路径创建一个名为lib的目录,把jar包复制进去,然后右击该jar包添加库依赖,即可。