package com.example.springboot.service;
|
|
import java.sql.Connection;
|
import java.sql.DriverManager;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
import org.springframework.stereotype.Component;
|
|
import com.example.springboot.entity.Queue;
|
import com.example.springboot.entity.north_frame_buffer1_frames;
|
import com.example.springboot.entity.north_glass_buffer1;
|
import com.example.springboot.service.North_Glass_Buffer1Service.north_glass_buffer1Comparator;
|
|
@Component
|
public class JdbcConnections {
|
/**
|
* 数据库连接参数
|
* driver,url,username,password
|
*/
|
private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
|
private static final String URL = "jdbc:mysql://localhost:3306/canadames";
|
private static final String USERNAME = "root";
|
private static final String PASSWORD = "beibo.123/";
|
|
private static Connection conns = null;
|
private static PreparedStatement ps = null;
|
private static ResultSet rss = null;
|
|
public north_glass_buffer1 selectGlass(int glassid) throws SQLException {
|
try {
|
Connection conn = getConn();
|
north_glass_buffer1 glass = new north_glass_buffer1();
|
String sql = "select ordernumber from north_glass_buffer1 where glassid=?";
|
ps = conn.prepareStatement(sql);
|
ps.setInt(1, glassid);
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
glass.setordernumber(rs.getString("ordernumber"));
|
}
|
conn.close();
|
return glass;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
|
}
|
|
public north_glass_buffer1 SelectGlassByGlassIdOrderIdFrameId(String glassid, String orderid, String frameid)
|
throws SQLException {
|
|
try {
|
Connection conn = getConn();
|
north_glass_buffer1 north_glass_buffer1s = new north_glass_buffer1();
|
String sql = "select * from north_glass_buffer1 where barcode=? or ordernumber=? or FrameBarcode=?";
|
ps = conn.prepareStatement(sql);
|
ps.setString(1, glassid);
|
ps.setString(2, orderid);
|
ps.setString(3, frameid);
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
north_glass_buffer1s.setId(rs.getInt("Id"));
|
north_glass_buffer1s.setordernumber(rs.getString("ordernumber"));
|
north_glass_buffer1s.setlistnumber(rs.getString("listnumber"));
|
north_glass_buffer1s.setboxnumber(rs.getString("boxnumber"));
|
north_glass_buffer1s.setbarcode(rs.getString("barcode"));
|
north_glass_buffer1s.setglasslength(rs.getDouble("glasslength"));
|
north_glass_buffer1s.setglassheight(rs.getDouble("glassheight"));
|
north_glass_buffer1s.setglasslengthmm(rs.getDouble("glasslength_mm"));
|
north_glass_buffer1s.setglassheightmm(rs.getDouble("glassheight_mm"));
|
north_glass_buffer1s.setitemtype(rs.getString("itemtype"));
|
north_glass_buffer1s.setslotnumber(rs.getString("slotnumber"));
|
north_glass_buffer1s.setdatemodified(rs.getString("datemodified"));
|
north_glass_buffer1s.setdatecreated(rs.getString("datecreated"));
|
north_glass_buffer1s.setFrameBarcode(rs.getString("FrameBarcode"));
|
}
|
conn.close();
|
return north_glass_buffer1s;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
|
}
|
|
public north_glass_buffer1 SelectGlassByGlassIdOrderIdFrameIdout(String glassid, String orderid, String frameid)
|
throws SQLException {
|
try {
|
Connection conn = getConn();
|
north_glass_buffer1 north_glass_buffer1s = new north_glass_buffer1();
|
String sql = "select * from north_glass_buffer1 where barcode=? or ordernumber=? or FrameBarcode=?";
|
ps = conn.prepareStatement(sql);
|
ps.setString(1, glassid);
|
ps.setString(2, orderid);
|
ps.setString(3, frameid);
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
north_glass_buffer1s.setId(rs.getInt("Id"));
|
north_glass_buffer1s.setordernumber(rs.getString("ordernumber"));
|
north_glass_buffer1s.setlistnumber(rs.getString("listnumber"));
|
north_glass_buffer1s.setboxnumber(rs.getString("boxnumber"));
|
north_glass_buffer1s.setbarcode(rs.getString("barcode"));
|
north_glass_buffer1s.setglasslength(rs.getDouble("glasslength"));
|
north_glass_buffer1s.setglassheight(rs.getDouble("glassheight"));
|
north_glass_buffer1s.setglasslengthmm(rs.getDouble("glasslength_mm"));
|
north_glass_buffer1s.setglassheightmm(rs.getDouble("glassheight_mm"));
|
north_glass_buffer1s.setitemtype(rs.getString("itemtype"));
|
north_glass_buffer1s.setslotnumber(rs.getString("slotnumber"));
|
north_glass_buffer1s.setdatemodified(rs.getString("datemodified"));
|
north_glass_buffer1s.setdatecreated(rs.getString("datecreated"));
|
north_glass_buffer1s.setFrameBarcode(rs.getString("FrameBarcode"));
|
}
|
conn.close();
|
return north_glass_buffer1s;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
|
}
|
|
public north_glass_buffer1 SelectGlassByGlassIdOrderIdFrameIdouts(String glassid, String orderid, String frameid)
|
throws SQLException {
|
try {
|
Connection conn = getConn();
|
north_glass_buffer1 north_glass_buffer1s = new north_glass_buffer1();
|
String sql = "select * from north_glass_buffer1 where barcode=? or ordernumber=? or FrameBarcode=?";
|
ps = conn.prepareStatement(sql);
|
ps.setString(1, glassid);
|
ps.setString(2, orderid);
|
ps.setString(3, frameid);
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
north_glass_buffer1s.setId(rs.getInt("Id"));
|
north_glass_buffer1s.setordernumber(rs.getString("ordernumber"));
|
north_glass_buffer1s.setlistnumber(rs.getString("listnumber"));
|
north_glass_buffer1s.setboxnumber(rs.getString("boxnumber"));
|
north_glass_buffer1s.setbarcode(rs.getString("barcode"));
|
north_glass_buffer1s.setglasslength(rs.getDouble("glasslength"));
|
north_glass_buffer1s.setglassheight(rs.getDouble("glassheight"));
|
north_glass_buffer1s.setglasslengthmm(rs.getDouble("glasslength_mm"));
|
north_glass_buffer1s.setglassheightmm(rs.getDouble("glassheight_mm"));
|
north_glass_buffer1s.setitemtype(rs.getString("itemtype"));
|
north_glass_buffer1s.setslotnumber(rs.getString("slotnumber"));
|
north_glass_buffer1s.setdatemodified(rs.getString("datemodified"));
|
north_glass_buffer1s.setdatecreated(rs.getString("datecreated"));
|
north_glass_buffer1s.setFrameBarcode(rs.getString("FrameBarcode"));
|
}
|
conn.close();
|
return north_glass_buffer1s;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
|
}
|
|
public north_glass_buffer1 SelectGlassByGlassIdOrderIdFrameIdplchold(String glassid, String orderid, String frameid)
|
throws SQLException {
|
try {
|
Connection conn = getConn();
|
north_glass_buffer1 north_glass_buffer1s = new north_glass_buffer1();
|
String sql = "select * from north_glass_buffer1 where barcode=? or ordernumber=? or FrameBarcode=?";
|
ps = conn.prepareStatement(sql);
|
ps.setString(1, glassid);
|
ps.setString(2, orderid);
|
ps.setString(3, frameid);
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
north_glass_buffer1s.setId(rs.getInt("Id"));
|
north_glass_buffer1s.setordernumber(rs.getString("ordernumber"));
|
north_glass_buffer1s.setlistnumber(rs.getString("listnumber"));
|
north_glass_buffer1s.setboxnumber(rs.getString("boxnumber"));
|
north_glass_buffer1s.setbarcode(rs.getString("barcode"));
|
north_glass_buffer1s.setglasslength(rs.getDouble("glasslength"));
|
north_glass_buffer1s.setglassheight(rs.getDouble("glassheight"));
|
north_glass_buffer1s.setglasslengthmm(rs.getDouble("glasslength_mm"));
|
north_glass_buffer1s.setglassheightmm(rs.getDouble("glassheight_mm"));
|
north_glass_buffer1s.setitemtype(rs.getString("itemtype"));
|
north_glass_buffer1s.setslotnumber(rs.getString("slotnumber"));
|
north_glass_buffer1s.setdatemodified(rs.getString("datemodified"));
|
north_glass_buffer1s.setdatecreated(rs.getString("datecreated"));
|
north_glass_buffer1s.setFrameBarcode(rs.getString("FrameBarcode"));
|
}
|
conn.close();
|
return north_glass_buffer1s;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
|
}
|
|
// 根据玻璃id，订单id，铝框id查询客户玻璃信息
|
public Queue SelectGlassByGlassIdOrderIdFrameIdQueue(String glassid, String orderid, String frameid)
|
throws SQLException {
|
try {
|
Connection conn = getConn();
|
Queue queue = new Queue();
|
String sql = "select * from north_glass_buffer1 where barcode=? or ordernumber=? or FrameBarcode=?";
|
ps = conn.prepareStatement(sql);
|
ps.setString(1, glassid);
|
ps.setString(2, orderid);
|
ps.setString(3, frameid);
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
queue.setid(rs.getInt("Id"));
|
queue.setorderId(rs.getString("ordernumber"));
|
queue.setlistId(rs.getString("listnumber"));
|
queue.setboxId(rs.getString("boxnumber"));
|
queue.setglassId(rs.getString("barcode"));
|
queue.setglasswidthmm(rs.getDouble("glasslength"));
|
queue.setglassheightmm(rs.getDouble("glassheight"));
|
queue.setglasswidth(rs.getDouble("glasslength_mm"));
|
queue.setglassheight(rs.getDouble("glassheight_mm"));
|
// queue.setitemtype(rs.getString("itemtype"));
|
// queue.setslotnumber(rs.getString("slotnumber"));
|
// queue.setdatemodified(rs.getString("datemodified"));
|
// queue.setdatecreated(rs.getString("datecreated"));
|
// queue.setFrameBarcode(rs.getString("FrameBarcode"));
|
}
|
conn.close();
|
return queue;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
|
}
|
|
// 根据玻璃id，订单id，铝框id查询客户玻璃信息
|
public List<north_glass_buffer1> SelectGlassByGlassIdOrderIdFrameIds(String glassid, String orderid, String frameid)
|
throws SQLException {
|
try {
|
Connection conn = getConn();
|
// north_glass_buffer1 glass=new north_glass_buffer1();
|
List<north_glass_buffer1> glass = new ArrayList<north_glass_buffer1>();
|
String sql = "select * from north_glass_buffer1 where position(? in barcode) and position(? in ordernumber) and position(? in FrameBarcode)";
|
ps = conn.prepareStatement(sql);
|
ps.setString(1, glassid);
|
ps.setString(2, orderid);
|
ps.setString(3, frameid);
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
north_glass_buffer1 north_glass_buffer1s = new north_glass_buffer1();
|
|
north_glass_buffer1s.setId(rs.getInt("Id"));
|
north_glass_buffer1s.setordernumber(rs.getString("ordernumber"));
|
north_glass_buffer1s.setlistnumber(rs.getString("listnumber"));
|
north_glass_buffer1s.setboxnumber(rs.getString("boxnumber"));
|
north_glass_buffer1s.setbarcode(rs.getString("barcode"));
|
north_glass_buffer1s.setglasslength(rs.getDouble("glasslength"));
|
north_glass_buffer1s.setglassheight(rs.getDouble("glassheight"));
|
north_glass_buffer1s.setglasslengthmm(rs.getDouble("glasslength_mm"));
|
north_glass_buffer1s.setglassheightmm(rs.getDouble("glassheight_mm"));
|
north_glass_buffer1s.setitemtype(rs.getString("itemtype"));
|
north_glass_buffer1s.setslotnumber(rs.getString("slotnumber"));
|
north_glass_buffer1s.setdatemodified(rs.getString("datemodified"));
|
north_glass_buffer1s.setdatecreated(rs.getString("datecreated"));
|
north_glass_buffer1s.setFrameBarcode(rs.getString("FrameBarcode"));
|
|
glass.add(north_glass_buffer1s);
|
|
}
|
conn.close();
|
return glass;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
|
}
|
|
// 根据铝框id查询客户玻璃信息
|
public List<north_glass_buffer1> SelectGlassByGlassIdOrderIdFrameIdss(String frameid) throws SQLException {
|
try {
|
Connection conn = getConn();
|
// north_glass_buffer1 glass=new north_glass_buffer1();
|
List<north_glass_buffer1> glass = new ArrayList<north_glass_buffer1>();
|
String sql = "select * from north_glass_buffer1 where FrameBarcode=?";
|
ps = conn.prepareStatement(sql);
|
ps.setString(1, frameid);
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
north_glass_buffer1 north_glass_buffer1s = new north_glass_buffer1();
|
|
north_glass_buffer1s.setId(rs.getInt("Id"));
|
north_glass_buffer1s.setordernumber(rs.getString("ordernumber"));
|
north_glass_buffer1s.setlistnumber(rs.getString("listnumber"));
|
north_glass_buffer1s.setboxnumber(rs.getString("boxnumber"));
|
north_glass_buffer1s.setbarcode(rs.getString("barcode"));
|
north_glass_buffer1s.setglasslength(rs.getDouble("glasslength"));
|
north_glass_buffer1s.setglassheight(rs.getDouble("glassheight"));
|
north_glass_buffer1s.setglasslengthmm(rs.getDouble("glasslength_mm"));
|
north_glass_buffer1s.setglassheightmm(rs.getDouble("glassheight_mm"));
|
north_glass_buffer1s.setitemtype(rs.getString("itemtype"));
|
north_glass_buffer1s.setslotnumber(rs.getString("slotnumber"));
|
north_glass_buffer1s.setdatemodified(rs.getString("datemodified"));
|
north_glass_buffer1s.setdatecreated(rs.getString("datecreated"));
|
north_glass_buffer1s.setFrameBarcode(rs.getString("FrameBarcode"));
|
|
glass.add(north_glass_buffer1s);
|
|
}
|
conn.close();
|
return glass;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
|
}
|
|
// 根据铝框id查询l铝框是否翻转
|
public String SelectFlipByFrameBarcode(String frameBarcode) throws SQLException {
|
try {
|
Connection conn = getConn();
|
// north_glass_buffer1 glass=new north_glass_buffer1();
|
String flip = "";
|
String sql1 = "select * from north_glass_buffer1_frames where Barcode=? limit 1";
|
ps = conn.prepareStatement(sql1);
|
ps.setString(1, frameBarcode);
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
flip = rs.getString("Flip");
|
|
}
|
conn.close();
|
return flip;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
|
}
|
|
// 根据铝框id查询是否铝框摆放
|
public String SelectPositionByFrameBarcode(String frameBarcode) throws SQLException {
|
try {
|
Connection conn = getConn();
|
// north_glass_buffer1 glass=new north_glass_buffer1();
|
String position = "";
|
String sql1 = "select * from north_glass_buffer1_frames where Barcode=? limit 1";
|
ps = conn.prepareStatement(sql1);
|
ps.setString(1, frameBarcode);
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
position = rs.getString("flip");
|
}
|
conn.close();
|
return position;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
|
}
|
|
// 根据铝框id查询铝框宽
|
public String SelectFrameLengthByFrameBarcode(String frameBarcode) throws SQLException {
|
try {
|
Connection conn = getConn();
|
// north_glass_buffer1 glass=new north_glass_buffer1();
|
String position = "";
|
String sql1 = "select * from north_glass_buffer1_frames where Barcode=? limit 1";
|
ps = conn.prepareStatement(sql1);
|
ps.setString(1, frameBarcode);
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
position = rs.getString("framelength");
|
}
|
conn.close();
|
return position;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
|
}
|
|
// 根据铝框id查询铝框高
|
public String SelectFrameHeightByFrameBarcode(String frameBarcode) throws SQLException {
|
try {
|
Connection conn = getConn();
|
// north_glass_buffer1 glass=new north_glass_buffer1();
|
String position = "";
|
String sql1 = "select * from north_glass_buffer1_frames where Barcode=? limit 1";
|
ps = conn.prepareStatement(sql1);
|
ps.setString(1, frameBarcode);
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
position = rs.getString("frameheight");
|
}
|
conn.close();
|
return position;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
|
}
|
|
// 根据铝框id查询铝框信息
|
public north_frame_buffer1_frames SelectFrameBarcode(String frameBarcode) throws SQLException {
|
try {
|
Connection conn = getConn();
|
north_frame_buffer1_frames frame=new north_frame_buffer1_frames();
|
String sql1 = "select * from north_glass_buffer1_frames where Barcode=? limit 1";
|
ps = conn.prepareStatement(sql1);
|
ps.setString(1, frameBarcode);
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
frame.setId(rs.getInt("id"));
|
frame.setlistnumber(rs.getString("listnumber"));
|
frame.setordernumber(rs.getString("ordernumber"));
|
frame.setboxnumber(rs.getString("boxnumber"));
|
frame.setbarcode(rs.getString("barcode"));
|
frame.setframelength(rs.getDouble("framelength"));
|
frame.setframeheight(rs.getDouble("frameheight"));
|
frame.setframelengthmm(rs.getDouble("framelength_mm"));
|
frame.setframeheightmm(rs.getDouble("frameheight_mm"));
|
frame.setflip(rs.getInt("flip"));
|
}
|
conn.close();
|
return frame;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
}
|
|
/**
|
* 1. 加载驱动
|
* 2. 获取连接 conn
|
* 3. 创建语句 ps
|
* 4. 执行语句 rs
|
* 5. 处理结果
|
* 6. 回收资源
|
*
|
* 实现CRUD
|
* 更新：
|
* 1增加
|
* 2删除
|
* 3修改
|
* 查询：
|
* 1. 查一个，一个对象
|
* 2. 查一组，做成一个对象列表，查全部
|
*/
|
public static Connection getConn() throws SQLException {
|
try {
|
Connection conn = null;
|
// conn =
|
// DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&characterEncoding=utf-8",
|
// "root", "beibo.123/");
|
conn = DriverManager.getConnection(
|
"jdbc:mysql://192.168.102.9:3306/production?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false",
|
"northglass", "n0rthgla55");
|
// "jdbc:mysql://localhost:3306/canadames?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false",
|
// "root", "beibo.123/");
|
return conn;
|
} catch (Exception e) {
|
// TODO: handle exception
|
e.printStackTrace();
|
return null;
|
}
|
|
}
|
|
static {
|
try {
|
Class.forName(DRIVER);
|
} catch (ClassNotFoundException e) {
|
e.printStackTrace();
|
}
|
}
|
|
}
|
