商品检索系统

一：创建数据库（sqlsever），对数据库内容进行初步的设置

二：对整体实现功能有个整体框架的印象,加载数据库驱动

    可以建如下几个包：

1、JDBC下  DBConnection（数据库连接与关闭工具），代码：

package JDBC;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class DBConnection {public static Connection getConnection() throws SQLException {// 注册JDBC驱动程序try {Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");} catch (ClassNotFoundException e) {e.printStackTrace();}// 构造连接URLString dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=ProductManager;";String userId = "sa";String password = "123456";// 获取连接Connection conn = DriverManager.getConnection(dbURL, userId, password);return conn;}public static void closeConnection(Connection conn) {try {if (conn != null) {conn.close();}} catch (SQLException e) {e.printStackTrace();}}public static void closeStatement(Statement stmt) {try {if (stmt != null) {stmt.close();}} catch (SQLException e) {e.printStackTrace();}}public static void closeResultSet(ResultSet rs) {try {if (rs != null) {rs.close();}} catch (SQLException e) {e.printStackTrace();}}public static void commit(Connection conn) {try {if (conn != null) {conn.commit();}} catch (SQLException e) {e.printStackTrace();}}public static void rollback(Connection conn) {try {if (conn != null) {conn.rollback();}} catch (SQLException e) {e.printStackTrace();}}}

JDBC下   SqTest类（只是最初的数据库是否可以正常连接的检测，与此系统功能无关，可有可无）

package JDBC;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;//测试数据库是否可以连接成功public class SqTest {public static void main(String[] args) {Connection conn=null;try {Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=ProductManager;";String userid="sa";String password="123456"; conn=DriverManager.getConnection(dbURL, userid, password);System.out.println("连接数据库成功");} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}finally{try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}

2、pro.dao包下  ProductionDao接口（对此系统功能实现的接口设置），代码：

package pro.dao;import java.util.ArrayList;import pro.entity.*;public interface ProductionDao {// 根据编号查询商品public Production findByPid(int id);// 根据名称查询public ArrayList<Production> findByPname(String name);// 添加商品（新商品，添加一条新信息）public int addProduction(Production pro);// 查询所有public ArrayList<Production> findAllProduction();// 添加商品（添加已有商品，修改已有商品库存）public int updateProduction(Production pro);}

3、pro.dao.Imple包下 ProductionImpl类（对接口功能的具体实现方法的实现），代码：

package pro.dao.Impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import JDBC.DBConnection;import pro.dao.ProductionDao;import pro.entity.*;public class ProductionImpl implements ProductionDao {@Override// 根据编号查询商品public Production findByPid(int pId) {Production pros = null;Connection conn = null;PreparedStatement sttm = null;ResultSet rs = null;// 查询返回结果集String sql = "SELECT * FROM t_product where pId=? ";try {conn = DBConnection.getConnection();sttm = conn.prepareStatement(sql);sttm.setInt(1, pId);rs = sttm.executeQuery();while (rs.next()) {pros = new Production();pros.setpId(rs.getInt(1));pros.setpName(rs.getString(2));pros.setPrice(rs.getInt(3));pros.setStore(rs.getInt(4));}} catch (SQLException e) {e.printStackTrace();} finally {DBConnection.closeConnection(conn);DBConnection.closeStatement(sttm);}return pros;}@Override// 根据名称查询public ArrayList<Production> findByPname(String pName) {Production pros = null;Connection conn = null;PreparedStatement sttm = null;ResultSet rs = null;// 查询返回结果集String sql = "SELECT * FROM t_product ";ArrayList<Production> list = new ArrayList<Production>();try {conn = DBConnection.getConnection();sttm = conn.prepareStatement(sql);rs = sttm.executeQuery();while (rs.next()) {if (rs.getString("pName").contains(pName)) {pros = new Production();pros.setpId(rs.getInt(1));pros.setpName(rs.getString(2));pros.setPrice(rs.getInt(3));pros.setStore(rs.getInt(4));list.add(pros);}}} catch (SQLException e) {e.printStackTrace();} finally {DBConnection.closeConnection(conn);DBConnection.closeStatement(sttm);}return list;}@Override// 添加商品（新商品，添加一条新信息）public int addProduction(Production pro) {Connection conn = null;PreparedStatement sttm = null;int count = 0;// 添加sql语句String sql = "INSERT INTO t_product (pName,price,Store) VALUES(?,?,?)";// 创建连接try {conn = DBConnection.getConnection();// 赋值sttm = conn.prepareStatement(sql);sttm.setString(1, pro.getpName());sttm.setInt(2, pro.getPrice());sttm.setInt(3, pro.getStore());count = sttm.executeUpdate();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {DBConnection.closeConnection(conn);DBConnection.closeStatement(sttm);}return count;}@Override// 查询所有public ArrayList<Production> findAllProduction() {Production pros = null;Connection conn = null;PreparedStatement sttm = null;ResultSet rs = null;// 查询返回结果集String sql = "SELECT * FROM t_product ";ArrayList<Production> listAll = new ArrayList<Production>();try {conn = DBConnection.getConnection();sttm = conn.prepareStatement(sql);rs = sttm.executeQuery();while (rs.next()) {pros = new Production();pros.setpId(rs.getInt(1));pros.setpName(rs.getString(2));pros.setPrice(rs.getInt(3));pros.setStore(rs.getInt(4));listAll.add(pros);}} catch (SQLException e) {e.printStackTrace();} finally {DBConnection.closeConnection(conn);DBConnection.closeStatement(sttm);}return listAll;}@Override// 添加商品（添加已有商品，修改已有商品库存）public int updateProduction(Production pro) {Connection conn = null;PreparedStatement sttm = null;int count = 0;String sql = "UPDATE t_product set price=?,Store=? where pName=?";try {conn = DBConnection.getConnection();// 创建执行对象sttm = conn.prepareStatement(sql);sttm.setInt(1, pro.getPrice());sttm.setInt(2, pro.getStore());sttm.setString(3, pro.getpName());count = sttm.executeUpdate();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {DBConnection.closeConnection(conn);DBConnection.closeStatement(sttm);}return count;}}

4、pro.entity包下   Production类（实体类），代码:

package pro.entity;public class Production {private int pId; // 商品编号private String pName; // 商品名称private int price; // 商品价格private int Store; // 商品库存public int getpId() {return pId;}public void setpId(int pId) {this.pId = pId;}public String getpName() {return pName;}public void setpName(String pName) {this.pName = pName;}public int getPrice() {return price;}public void setPrice(int price) {this.price = price;}public int getStore() {return Store;}public void setStore(int store) {Store = store;}}

5、pro.view包下  ProductionView类（主界面的实现，里面的方法可以另外建类，会更加清晰），代码：

package pro.view;import java.util.ArrayList;import java.util.Scanner;import pro.dao.Impl.ProductionImpl;import pro.entity.Production;public class ProductionView {Scanner sc = null;static ProductionImpl prodao;static Production pro; // 实体类public static void main(String[] args) {for (;;) {System.out.println("=========操作提示=========\n"+ "======  1查询商品   ========\n"+ "======  2添加商品   ========\n" + "请选择：");Scanner sc = new Scanner(System.in);int i = sc.nextInt();if (i == 1) {System.out.println("====== 1按编号查询   2按名称查询  3返回主菜单   ======");int j = sc.nextInt();if (j == 1) {ProductionView.findByPid(sc); // 1. 根据编号查询商品} else if (j == 2) {ProductionView.findByPname(sc); // 2. 根据名称查询} else {continue;}} else if (i == 2) {ProductionView.addProduction(sc); // 3.添加商品（新商品，添加一条新信息）} else {System.out.println("请按提示操作，执行查询或添加功能");}}}// 1. 根据编号查询商品public static void findByPid(Scanner sc) {System.out.println("请输入商品编号：");int m = sc.nextInt();prodao = new ProductionImpl();Production proId = prodao.findByPid(m);if (proId != null) {System.out.println("----------------------------");System.out.println("商品编号为\t商品名称\t商品价格\t商品库存");System.out.println(m + "\t" + proId.getpName() + "\t"+ proId.getPrice() + "\t" + proId.getStore());System.out.println("----------------------------");} else {System.out.println("没有你要查询商品编号的信息，请确认后再查询");}}// 2. 根据名称查询(模糊查询)public static void findByPname(Scanner sc) {System.out.println("请输入商品关键字：");String pName = sc.next();prodao = new ProductionImpl();ArrayList<Production> proName = prodao.findByPname(pName);if (proName.size() != 0) {System.out.println("----------------------------");System.out.println("商品编号为\t商品名称\t商品价格\t商品库存");for (Production p : proName) {System.out.println(p.getpId() + "\t" + p.getpName() + "\t"+ p.getPrice() + "\t" + p.getStore());}System.out.println("----------------------------");} else {System.out.println("没有你要查找的商品信息，请确认信息后再重新查找。");}}// 3.添加商品（新商品，添加一条新信息）public static void addProduction(Scanner sc) {prodao = new ProductionImpl();pro = new Production();int i = 0;boolean j = true;System.out.println("请输入商品的名称：");String pName = sc.next();System.out.println("请输入商品价格：");int price = sc.nextInt();System.out.println("请输入商品数量：");int store = sc.nextInt();ArrayList<Production> findAll = prodao.findAllProduction(); // 查询数据库中的所有信息for (Production prod : findAll) { // 遍历if (pName.equals(prod.getpName())) { // 4.添加商品（添加已有商品，修改已有商品库存），判断数据库里是否已有商品prod.setPrice(price);prod.setStore(store);i = prodao.updateProduction(prod); // 若数据库已有，则进行修改操作,i为是否进行了此操作的判断System.out.println("111");j = false;}}if (j == true) {pro.setpName(pName);pro.setPrice(price);pro.setStore(store);i = prodao.addProduction(pro); // 若没有则进行增加的操作System.out.println("2222");}if (i != 0) { // 若以上进行了数据库的操作，则输出数据库的信息ArrayList<Production> proName = prodao.findByPname(pName); // 集合接收通过姓名查询的信息System.out.println("添加成功");System.out.println("----------------------------");System.out.println("商品编号为\t商品名称\t商品价格\t商品库存");for (Production p : proName) { // 遍历System.out.println(p.getpId() + "\t" + p.getpName() + "\t"+ p.getPrice() + "\t" + p.getStore());}System.out.println("----------------------------");j = true;} else {System.out.println("商品添加失败！");}}}

三：体会

         一些工具类可以直接用已有的，节省思考下面方法的时间。接口方法可以先写个大概，后期需要再加或者修改。通过功能的一步步实现，修改内部代码的不足，尽可能使bug得的减到最少。此程序仅限在数据库中没有同名商品的修改，若有同名商品，还需加id的判断，进行下一步的优化。（注：各自数据库的遍历顺序不同，相比于上一个修改，加true的识别，新商品与已有商品的区别。）