Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.ResultSetMetaData;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.Date;
- public class MySQLAccess {
- private Connection connect = null;
- private Statement statement = null;
- private ResultSet resultSet = null;
- public void connectDB() {
- try {
- Class.forName("com.mysql.jdbc.Driver");
- connect = DriverManager
- .getConnection("jdbc:mysql://localhost/merx?"
- + "user=root&password=admin");
- statement = connect.createStatement();
- } catch (Exception e) {
- System.out.println("Failed to connect to MySQL DB");
- e.printStackTrace();
- }
- }
- public void readDataBase() {
- try {
- connectDB();
- resultSet = statement
- .executeQuery("select * from merx.inventory;");
- writeResultSet(resultSet);
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- close();
- }
- }
- public int newTransaction(Date date) {
- int id = -1;
- try {
- int refNum = getNewReferenceId() + 1;
- System.out.println("refNum = " + refNum);
- connectDB();
- String[] values = { "FALSE", "SMS", "SMS", "SMS" + refNum, "", "DELIVERY_OUT", "" };
- String query = String.format("INSERT INTO merx.warehouse_transaction " +
- "(approval_status, date_created, date_updated, delivered_by, received_by, reference_number, remarks, transaction_type, truck) " +
- "values (\"%s\", now(), now(), \"%s\",\"%s\",\"%s\",\"%s\",\"%s\",\"%s\");",
- values[0], values[1], values[2], values[3], values[4], values[5], values[6]);
- System.out.println(query);
- statement.executeUpdate(query);
- resultSet = statement.executeQuery("SELECT LAST_INSERT_ID();");
- if (resultSet.next())
- id = resultSet.getInt(1);
- System.out.println("transId = " + id);
- } catch (Exception e) {
- e.printStackTrace();
- }
- close();
- return id;
- }
- public void addItemToOrder(int transId, int invId, int qty) {
- try {
- connectDB();
- String query = String.format("INSERT INTO merx.warehouse_transaction_inventory " +
- "(quantity, inventory_id, warehouse_transaction_id) " +
- "values (%d, %d, %d);",
- qty, invId, transId);
- statement.executeUpdate(query);
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- close();
- }
- }
- public int getInventoryId(String code) {
- int id = -1;
- try {
- connectDB();
- String query = String.format("SELECT id FROM merx.inventory WHERE code = \"%s\";", code);
- resultSet = statement.executeQuery(query);
- if (resultSet.next())
- id = resultSet.getInt(1);
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- close();
- }
- return id;
- }
- private int getNewReferenceId(){
- int id = -1;
- try{
- connectDB();
- resultSet = statement.executeQuery("SELECT MAX(id) FROM warehouse_transaction;");
- if (resultSet.next())
- id = resultSet.getInt(1);
- }
- catch(Exception e){
- e.printStackTrace();
- } finally {
- close();
- }
- return id;
- }
- private void writeResultSet(ResultSet resultSet) throws SQLException {
- ResultSetMetaData resMeta = resultSet.getMetaData();
- int colCount = resMeta.getColumnCount();
- while (resultSet.next()) {
- for (int i = 1; i <= colCount - 1; i++) {
- System.out.print(resultSet.getObject(i).toString() + "|");
- }
- System.out.println(resultSet.getObject(colCount).toString());
- }
- }
- private void close() {
- try {
- if (resultSet != null) {
- resultSet.close();
- }
- if (statement != null) {
- statement.close();
- }
- if (connect != null) {
- connect.close();
- }
- } catch (Exception e) {
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement