编写一个程序,根据用户输入的产品代码,在Product 表中搜索特定的产品,并显示产品的详细信息。该程序还应允许根据数量进行搜索并显示数量大于用户输入的值的产品详细信息。此外,还应该提供一个菜单,以便用户可以选择是根据产品代码还是数量进行搜索。Product表的结构如表所示
列名称 数据类型
ProductCode varchar
ProductName varchar
Quantity Numeric
向表中添加数据
ProductCode ProductName Quantity
A001 奥迪A6 550
A002 桑塔那2000 100
A003 奔驰SL500 80
提示:
使用PreparedStatement类搜索记录
使用非DSN连接建立与数据库的连接
以下是我写的代码,编译能过,执行的时候会报空指针异常,大家指点指点
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
class ProductDetails {
private Connection con;
private String url;
private String serverName;
private String portNumber;
private String databaseName;
private String userName;
private String password;
private String sql;
private BufferedReader br;
ProductDetails() {
url = "jdbc:microsoft:sqlserver://";
serverName = "MICROSOF-F690DY";
portNumber = "1433";
databaseName = "test";
userName = " ";
password = " ";
}
private String getConnectionUrl() {
return url + serverName + ":" + portNumber + ";databaseName=" + databaseName + ";";
}
private Connection getConnection() {
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = DriverManager.getConnection(getConnectionUrl(),userName,password);
if (con != null) {
System.out.println("连接成功!");
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("getConnection()内的错误跟踪:" + e.getMessage());
}
return con;
}
private void menudisplay() throws IOException {
char choice;
while (true) {
System.out.println();
System.out.println("1.根据产品代码进行搜索");
System.out.println("2.根据数量进行搜索");
System.out.println("3.退出");
System.out.println();
System.out.print("请输入您的选择:");
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
choice = (char) br.read();
switch(choice) {
case '1':
searchRecordByProductCode();
break;
case '2':
searchRecordByQuantity();
break;
default:
System.out.println("\n请输入一个有效数字");
break;
}
}
}
private void searchRecordByProductCode() throws IOException {
try {
System.out.println();
System.out.println("请输入产品代码:");
String code = br.readLine();
System.out.println("根据产品代码进行搜索。。。");
con = getConnection();
sql = "select * from product where ProductCode like '" +code+ "'";
PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println("");
System.out.println("产品代码" + rs.getString(1));
System.out.println("产品名称" + rs.getString(2));
System.out.println("数量" + rs.getString(3));
}
} catch (SQLException ce) {
System.out.println(ce);
}
}
private void searchRecordByQuantity() throws IOException {
try {
System.out.println();
System.out.println("请输入数量:");
int quantity = br.read();
System.out.println("根据数量进行搜索。。。");
con = getConnection();
sql = "select * form product where Quantity>" + quantity;
PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println("");
System.out.println("产品代码" + rs.getString(1));
System.out.println("产品名称" + rs.getString(2));
System.out.println("数量" + rs.getString(3));
}
} catch (SQLException ce) {
System.out.println(ce);
}
}
public static void main(String[] args) throws IOException {
ProductDetails productObj = new ProductDetails();
productObj.getConnectionUrl();
productObj.menudisplay();
}
}