JDBC编程练习4-4，烦请老师检查和指正～
主菜单类和DbUtils封装类
package goodsmanagement;
import goodsmanagement.command.*;
import java.util.Scanner;
public class GoodsManagement {
public static void main(String[]args){
System.out.println("1-Search goods by price");
System.out.println("2-Search goods by price range:");
System.out.println("3-Add new goods:");
System.out.println("4-Update goods'information by name:");
System.out.println("5-Delete goods by name:");
Scanner in = new Scanner(System.in);
Integer cmd = in.nextInt();
Command command = null;
switch(cmd){
case 1://Search goods by price
command = new QueryCommand();
command.execute();
break;
case 2://Search goods by price range
command = new PreparedStatementQueryCommand();
command.execute();
break;
case 3://Insert
command = new InsertCommand();
command.execute();
break;
case 4://Update
command = new UpdateCommand();
command.execute();
break;
case 5://Delete
command = new DeleteCommand();
command.execute();
break;
}
}
}
package common;
import java.sql.*;
public class DbUtils {//在实际调用的时候再对异常进行捕获
/**
* 创建数据库链接
* @return 新的Connection对象
* @throws ClassNotFoundException
* @throws SQLException
*/
public static Connection getConnection() throws ClassNotFoundException, SQLException {
//1.加载并注册JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.创建数据库链接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true","root","root");
return conn;
}
/**
* 关闭链接，释放资源
* @param rs 结果集对象
* @param stmt Statement对象
* @param conn Connection对象
*/
public static void closeConnection(ResultSet rs, Statement stmt, Connection conn){
//5.关闭链接，释放资源
try {
if(rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(stmt != null){
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn != null && !conn.isClosed()){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
接口类
package goodsmanagement.command;
public interface Command {
public void execute();
}接口实现类1
package goodsmanagement.command;
import goodsmanagement.command.Command;
import java.sql.*;
import java.util.Scanner;
public class QueryCommand implements Command {
@Override
public void execute() {
System.out.print("Please enter gprice:");
Scanner in = new Scanner(System.in);
Float gprice = in.nextFloat();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true","root","root");
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from t_goods where price<'"+gprice+"'");
while(rs.next()){
Integer gno = rs.getInt(1);
String name = rs.getString("name");
Float price = rs.getFloat("price");
String desp = rs.getString("desp");
System.out.println(name + "-" + gno + "-" + price + "-" + desp);
}
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}finally{
try{
if(rs!=null){
rs.close();
}
}catch(SQLException e){
e.printStackTrace();
}
try{
if(stmt!=null){
stmt.close();
}
}catch(SQLException e){
e.printStackTrace();
}
try{
if(conn!=null&&!conn.isClosed()){
conn.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
}}接口实现类2
package goodsmanagement.command;
import java.sql.*;
import java.util.Scanner;
public class PreparedStatementQueryCommand implements Command{
@Override
public void execute() {
System.out.print("Please enter gprice:");
Scanner in = new Scanner(System.in);
System.out.print("Please enter lowPrice:");
Float lowPrice = in.nextFloat();
System.out.print("Please enter hiPrice:");
Float hiPrice = in.nextFloat();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true","root","root");
String sql = "select * from t_goods where price>? and price<?";
pstmt = conn.prepareStatement(sql);
pstmt.setFloat(1,lowPrice);
pstmt.setFloat(2,hiPrice);
rs = pstmt.executeQuery();
while(rs.next()){
Integer gno = rs.getInt(1);
String name = rs.getString("name");
Float price = rs.getFloat("price");
String desp = rs.getString("desp");
System.out.println(name + "-" + gno + "-" + price + "-" + desp);
}
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}finally{
try{
if(rs!=null){
rs.close();
}
}catch(SQLException e){
e.printStackTrace();
}
try{
if(pstmt!=null){
pstmt.close();
}
}catch(SQLException e){
e.printStackTrace();
}
try{
if(conn!=null&&!conn.isClosed()){
conn.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
}
}接口实现类3
package goodsmanagement.command;
import common.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class InsertCommand implements Command{
@Override
public void execute() {
Scanner in = new Scanner(System.in);
System.out.print("Please enter good's name: ");
String name = in.next();
System.out.print("Please enter good's price: ");
float price = in.nextFloat();
System.out.print("Please enter good's description: ");
String desp = in.next();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DbUtils.getConnection();
String sql = "insert into t_goods(name, price, desp) values(?, ?, ?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setFloat(2, price);
pstmt.setString(3, desp);
int cnt = pstmt.executeUpdate();
if (cnt == 1) {
System.out.println("Added successfully!");
sql = "select * from t_goods";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
String gname = rs.getString("name");
Float gprice = rs.getFloat("price");
String gdesp = rs.getString("desp");
System.out.println(gname + "-" + gprice + "-" + gdesp);
}
}else{
System.out.println("Failt to add!");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DbUtils.closeConnection(rs,pstmt,conn);
}
}
}接口实现类4
package goodsmanagement.command;
import common.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class UpdateCommand implements Command{
@Override
public void execute() {
Scanner in = new Scanner(System.in);
System.out.print("Please enter good's name:");
String name = in.next();
System.out.print("Please enter good's new price:");
float price = in.nextFloat();
Connection conn = null;
PreparedStatement pstmt = null;
try{
conn = DbUtils.getConnection();
String sql = "update t_goods set price=? where name=?";
pstmt = conn.prepareStatement(sql);
pstmt.setFloat(1,price);
pstmt.setString(2,name);
int cnt = pstmt.executeUpdate();
if(cnt == 1){
System.out.println("Updated successfully!");
}else{
System.out.println("Fail to update!");
}
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}finally{
DbUtils.closeConnection(null,pstmt,conn);
}
}
}接口实现类5
package goodsmanagement.command;
import common.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class DeleteCommand implements Command{
@Override
public void execute() {
Scanner in = new Scanner(System.in);
System.out.print("Please enter good's name:");
String name = in.next();
Connection conn = null;
PreparedStatement pstmt = null;
try{
conn = DbUtils.getConnection();
String sql = "delete from t_goods where name=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,name);
int cnt = pstmt.executeUpdate();
if(cnt == 1){
System.out.println("Deleted successfully!");
}else{
System.out.println("Fail to delete!");
}
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}finally{
DbUtils.closeConnection(null,pstmt,conn);
}
}
}16
收起
正在回答 回答被采纳积分+1
1回答
相似问题
登录后可查看更多问答，登录/注册
恭喜解决一个难题，获得1积分~
来为老师/同学的回答评分吧
0 星