一:创建数据库(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();
}
// 构造连接URL
String 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 block
e.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 block
e.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的识别,新商品与已有商品的区别。)