API tools faq
paste
broken-arrow

Untitled

broken-arrow
Jun 24th, 2021
149
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 12.18 KB | None | 0 0
raw download clone embed print report
  1. public class SQLite {
  2.     @Getter
  3.     private static final SQLite Instance = new SQLite();
  4.     private final File chestStorage = ChestStorage.getData();// A reference to Main Class
  5.  
  6.     private Connection connection = connect();
  7.  
  8.     private boolean loadDataStartup = true;
  9.  
  10.     private final String TableNameChest = "mytest", TableOneFirstRow = "Location", TableOnesecondRow = "Player_UUID",
  11.             TableOneThirdRow = "FilterWhiteBlack", TableOneFourthRow = "Chest_Type", TableOneFifthRow = "Suction_Range",
  12.             TableOneSixthRow = "Link_Range", TableOneSeventhRow = "Link_Amount", TableOneEighthRow = "Next_Upgrade",
  13.             TableOneNineRow = "Chest_contents", TableOneTenRow = "SettingsName", TableOneElevenRow = "AmountOfPages",
  14.             TableOneTwelveRow = "FilterItems", TableOneThirteenRow = "Link_location";
  15.     private final String TableTwoUsers = "Users", TableTwoFirstRow = "PLAYER_NAME", TableTwosecondRow = "UUID";
  16.  
  17.     private boolean batchUpdateGoingOn = false;
  18.  
  19.  
  20.     public Connection connect() {
  21.  
  22.  
  23.         try {
  24.             File dbFile = new File(chestStorage, "database.db");
  25.             if (!dbFile.exists()) {
  26.                 try {
  27.                     dbFile.createNewFile();
  28.  
  29.                     //String url = "jdbc:sqlite:" + dbFile.getPath();
  30.                 } catch (IOException ex) {
  31.                     ChestStorage.getInstance().getLogger().log(Level.SEVERE, "File write error: " + dbFile + ".db", ex);
  32.                 }
  33.             }
  34.  
  35.             if (this.connection != null && !this.connection.isClosed()) {
  36.                 return this.connection;
  37.             }
  38.             Class.forName("org.sqlite.JDBC");
  39.             this.connection = DriverManager.getConnection("jdbc:sqlite:" + dbFile);
  40.             return this.connection;
  41.  
  42.  
  43.         } catch (SQLException ex) {
  44.             ChestStorage.getInstance().getLogger().log(Level.SEVERE, "SQLite exception on initialize", ex);
  45.         } catch (ClassNotFoundException ex) {
  46.             ChestStorage.getInstance().getLogger().log(Level.SEVERE, "You need the SQLite JBDC library. Google it. Put it in /lib folder.");
  47.         }
  48.         return null;
  49.     }
  50.  
  51.  
  52.     public void load(String sql) {
  53.         this.connection = connect();
  54.         try {
  55.             Statement s = this.connection.createStatement();
  56.             s.executeUpdate(sql);
  57.             s.close();
  58.         } catch (SQLException e) {
  59.             e.printStackTrace();
  60.         }
  61.         initialize();
  62.     }
  63. /*
  64.     public Statement Statements() {
  65.         try {
  66.             connect().createStatement();
  67.         } catch (SQLException throwables) {
  68.             throwables.printStackTrace();
  69.         }
  70.         return statement;
  71.     }*/
  72.  
  73.     public void disconnect() {
  74.         try {
  75.             if (connection != null) {
  76.                 connection.close();
  77.             }
  78.         } catch (SQLException exception) {
  79.             exception.printStackTrace();
  80.         }
  81.     }
  82.  
  83.  
  84.     /*private void update(String sql) {
  85.         try {
  86.             connect().executeupdate(sql);
  87.         } catch (SQLException exception) {
  88.             exception.printStackTrace();
  89.         }
  90.     }
  91.  
  92.     public ResultSet getResult(String sql) {
  93.         try {
  94.             return connect().executeQuery(sql);
  95.         } catch (SQLException exception) {
  96.             exception.printStackTrace();
  97.         }
  98.         return null;
  99.     }*/
  100.  
  101.     public void createTables() {
  102.         String test;
  103.         String tableOne = "CREATE TABLE IF NOT EXISTS " + TableNameChest + " (" + TableOneFirstRow + " VARCHAR(64) PRIMARY KEY, " +
  104.                 TableOnesecondRow + " VARCHAR(64) , " + TableOneThirdRow + " VARCHAR(64), " + TableOneFourthRow + " VARCHAR(64), " +
  105.                 TableOneFifthRow + " VARCHAR(64), " + TableOneSixthRow + " VARCHAR(64), " + TableOneSeventhRow + " VARCHAR(64), " +
  106.                 TableOneEighthRow + " VARCHAR(64), " + TableOneNineRow + " VARCHAR(64)," + TableOneTenRow + " VARCHAR(64)," +
  107.                 TableOneElevenRow + " VARCHAR(64)," + TableOneTwelveRow + " VARCHAR(64)," + TableOneThirteenRow + " VARCHAR(64));";
  108.         String tableTwo = "CREATE TABLE IF NOT EXISTS " + TableTwoUsers + " (" + TableTwoFirstRow + " VARCHAR(64) , " + TableTwosecondRow + " VARCHAR(64) PRIMARY KEY);";
  109.         //Common.log(tableOne);
  110.  
  111.  
  112.         String[] tabels = {tableOne, tableTwo};
  113.  
  114.         for (int i = 0; i < tabels.length; i++) {
  115.             test = tabels[i];
  116.             //System.out.println(test);
  117.             load(test);
  118.         }
  119.     }
  120.  
  121.     //TODO Fix more of load code and cleanup/fixes?
  122.     public void LoadFromSQlite() {
  123.         ArrayList<Location> locationArrayList = new ArrayList<>();
  124.         PreparedStatement ps = null;
  125.         this.connection = connect();
  126.         ResultSet rs;
  127.         SerializedMap map = new SerializedMap();
  128.  
  129.         try {
  130.             String sql = "SELECT * FROM " + TableNameChest;
  131.             ps = this.connection.prepareStatement(sql);
  132.             rs = ps.executeQuery();
  133.  
  134.             while (rs.next()) {
  135.  
  136.                 Location location = SerializeUtil.deserializeLocation(rs.getObject(TableOneFirstRow));
  137.                 UUID playerUUID = UUID.fromString(rs.getString(TableOnesecondRow));
  138.                 boolean filterWhiteBlack = rs.getBoolean(TableOneThirdRow);
  139.                 TypeOfContainer typeOfContainer = TypeOfContainer.valueOf(rs.getString(TableOneFourthRow));
  140.                 int suctionRange = rs.getInt(TableOneFifthRow);
  141.                 int linkRange = rs.getInt(TableOneSixthRow);
  142.                 int linkAmount = rs.getInt(TableOneSeventhRow);
  143.                 String nextUpgrade = rs.getString(TableOneEighthRow);
  144.                 ItemStack[] chestItems = SerializDeserialzItemStackFromToDatabase.itemStackArrayFromBase64(rs.getString(TableOneNineRow));
  145.                 String containerSettingsName = rs.getString(TableOneTenRow);
  146.                 int numberOfPages = rs.getInt(TableOneElevenRow);
  147.                 ItemStack[] filterItems = rs.getString(TableOneTwelveRow) != null ?
  148.                         (SerializDeserialzItemStackFromToDatabase.itemStackArrayFromBase64(rs.getString(TableOneTwelveRow))) : (new ItemStack[]{(new ItemStack(Material.AIR))});
  149.                 //Array LinkedLocations = rs.getArray(TableOneThirteenRow) != null ? rs.getArray(TableOneThirteenRow) : ((Array) new ArrayList<Location>());
  150.  
  151.                 //locationArrayList.addAll((Collection<? extends Location>) LinkedLocations.getArray());
  152.  
  153.                 ChestRegistry.getInstance().addChestToCacheFromSQL(location, new ChestData(playerUUID,
  154.                         nextUpgrade, typeOfContainer, containerSettingsName, numberOfPages,
  155.                         filterWhiteBlack, suctionRange, linkRange, linkAmount, filterItems, chestItems, null));
  156.  
  157.             }
  158.         } catch (SQLException throwables) {
  159.             throwables.printStackTrace();
  160.         } finally {
  161.             try {
  162.                 if (ps != null)
  163.                     ps.close();
  164.                 if (this.connection != null)
  165.                     this.connection.close();
  166.             } catch (SQLException ex) {
  167.                 ChestStorage.getInstance().getLogger().log(Level.SEVERE, Errors.sqlConnectionClose(), ex);
  168.             }
  169.         }
  170.     }
  171.  
  172.     public void removeChestfromSQlites(Location location) {
  173.         List<String> sqls = new ArrayList<>();
  174.         String sql = "DELETE FROM  " + TableNameChest + " WHERE " + TableOneFirstRow + " = '" + SerializeUtil.serializeLoc(location) + "'";
  175.  
  176.         sqls.add(sql);
  177.         this.batchUpdate(sqls);
  178.     }
  179.  
  180.     public void saveItemsToSQlites(Location location, ItemStack[] itemStack) {
  181.         List<String> sqls = new ArrayList<>();
  182.         String sql = "UPDATE " + TableNameChest + " SET " + TableOneNineRow + " = '" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(itemStack) + "' WHERE " + TableOneFirstRow + " = '" + SerializeUtil.serializeLoc(location) + "'";
  183.  
  184.         sqls.add(sql);
  185.         this.batchUpdate(sqls);
  186.     }
  187.  
  188.     public void saveFilterListToSQlites(Location location, ItemStack[] itemStack) {
  189.         List<String> sqls = new ArrayList<>();
  190.         String sql = "UPDATE " + TableNameChest + " SET " + TableOneTwelveRow + " = '" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(itemStack) + "' WHERE " + TableOneFirstRow + " = '" + SerializeUtil.serializeLoc(location) + "'";
  191.  
  192.         sqls.add(sql);
  193.         this.batchUpdate(sqls);
  194.     }
  195.  
  196.     @SneakyThrows
  197.     public void saveDataOnBlockPlace(ChestData chestData, Location location) {
  198.         List<String> sqls = new ArrayList<>();
  199.  
  200.         String sql = "REPLACE INTO " + TableNameChest + " (" + TableOneFirstRow
  201.                 + "," + TableOnesecondRow + "," + TableOneThirdRow
  202.                 + "," + TableOneFourthRow + "," + TableOneFifthRow + "," + TableOneSixthRow
  203.                 + "," + TableOneSeventhRow + "," + TableOneEighthRow + "," + TableOneNineRow
  204.                 + "," + TableOneTenRow + "," + TableOneElevenRow
  205.                 + ") VALUES('" + SerializeUtil.serializeLoc(location)
  206.                 + "','" + chestData.getPlayerId().toString() + "','" + chestData.isFilterWhiteBlack()
  207.                 + "','" + chestData.getTypeOfContainer() + "','" + chestData.getSuctionRange()
  208.                 + "','" + chestData.getLinkRange() + "','" + chestData.getLinkAmount()
  209.                 + "','" + chestData.getNextUpgrade()
  210.                 + "','" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(chestData.getChestContents())
  211.                 + "','" + chestData.getContainerSettingsName() + "','" + chestData.getNumberOfPages() + "')";
  212.         sqls.add(sql);
  213.  
  214.  
  215.         sqls.add(sql);
  216.  
  217.         //System.out.println(sqls);
  218.         this.batchUpdate(sqls);
  219.     }
  220.  
  221.     //TODO Make data values more cleaner.
  222.     @SneakyThrows
  223.     public void saveToSQlites(Map<Location, ChestData> chests) {
  224.         List<String> sqls = new ArrayList<>();
  225.         for (Map.Entry<Location, ChestData> entry : chests.entrySet()) {
  226.             Location location = entry.getKey();
  227.             ChestData chestData = entry.getValue();
  228.  
  229.             String sql = "REPLACE INTO " + TableNameChest + " (" + TableOneFirstRow
  230.                     + "," + TableOnesecondRow + "," + TableOneThirdRow
  231.                     + "," + TableOneFourthRow + "," + TableOneFifthRow + "," + TableOneSixthRow
  232.                     + "," + TableOneSeventhRow + "," + TableOneEighthRow + "," + TableOneNineRow
  233.                     + "," + TableOneTenRow + "," + TableOneElevenRow + "," + TableOneTwelveRow + "," + TableOneThirteenRow
  234.                     + ") VALUES('" + SerializeUtil.serializeLoc(location)
  235.                     + "','" + chestData.getPlayerId().toString() + "','" + chestData.isFilterWhiteBlack()
  236.                     + "','" + chestData.getTypeOfContainer() + "','" + chestData.getSuctionRange()
  237.                     + "','" + chestData.getLinkRange() + "','" + chestData.getLinkAmount()
  238.                     + "','" + chestData.getNextUpgrade()
  239.                     + "','" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(chestData.getChestContents())
  240.                     + "','" + chestData.getContainerSettingsName() + "','" + chestData.getNumberOfPages()
  241.                     + "','" + SerializDeserialzItemStackFromToDatabase.itemStackArrayToBase64(chestData.getFilterItems())
  242.                     + "','" + chestData.getLocations() + "')";
  243.  
  244.  
  245.             sqls.add(sql);
  246.         }
  247.         //System.out.println(sqls);
  248.         this.batchUpdate(sqls);
  249.     }
  250.  
  251.     protected final void batchUpdate(@NonNull List<String> sqls) {
  252.         this.connection = connect();
  253.  
  254.         if (sqls.size() == 0)
  255.             return;
  256.         try {
  257.             final Statement batchStatement = this.connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
  258.             final int processedCount = sqls.size();
  259.  
  260.             // Prevent automatically sending db instructions
  261.             this.connection.setAutoCommit(false);
  262.  
  263.  
  264.             for (final String sql : sqls)
  265.                 batchStatement.addBatch(sql);
  266.             if (processedCount > 10_000)
  267.                 Common.log("Updating your database (" + processedCount + " entries)... PLEASE BE PATIENT THIS WILL TAKE "
  268.                         + (processedCount > 50_000 ? "10-20 MINUTES" : "5-10 MINUTES") + " - If server will print a crash report, ignore it, update will proceed.");
  269.  
  270.             // Set the flag to start time notifications timer
  271.             batchUpdateGoingOn = true;
  272.  
  273.             // Notify console that progress still is being made
  274.             new Timer().scheduleAtFixedRate(new TimerTask() {
  275.  
  276.                 @Override
  277.                 public void run() {
  278.                     if (batchUpdateGoingOn)
  279.                         Common.log("Still executing, DO NOT SHUTDOWN YOUR SERVER.");
  280.                     else
  281.                         cancel();
  282.                 }
  283.             }, 1000 * 30, 1000 * 30);
  284.  
  285.             // Execute
  286.             batchStatement.executeBatch();
  287.  
  288.             // This will block the thread
  289.             this.connection.commit();
  290.  
  291.             //Common.log("Updated " + processedCount + " database entries.");
  292.  
  293.         } catch (final Throwable t) {
  294.             t.printStackTrace();
  295.  
  296.         } finally {
  297.             try {
  298.                 this.connection.setAutoCommit(true);
  299.  
  300.             } catch (final SQLException ex) {
  301.                 ex.printStackTrace();
  302.             }
  303.  
  304.             // Even in case of failure, cancel
  305.             batchUpdateGoingOn = false;
  306.         }
  307.     }
  308.  
  309.  
  310.     public void initialize() {
  311.         try {
  312.             PreparedStatement preparedStatement = this.connection.prepareStatement("SELECT * FROM " + TableNameChest + " WHERE " + TableOnesecondRow + " = ?");
  313.             ResultSet resultSet = preparedStatement.executeQuery();
  314.             close(preparedStatement, resultSet);
  315.  
  316.         } catch (SQLException ex) {
  317.             ChestStorage.getInstance().getLogger().log(Level.SEVERE, "Unable to retreive connection", ex);
  318.         }
  319.     }
  320.  
  321.     public void close(PreparedStatement preparedStatement, ResultSet resultSet) {
  322.         try {
  323.             if (preparedStatement != null)
  324.                 preparedStatement.close();
  325.             if (resultSet != null)
  326.                 resultSet.close();
  327.         } catch (SQLException ex) {
  328.             ex.printStackTrace();
  329.             /*Error.close(ChestStorage.getInstance(), ex);*/
  330.         }
  331.     }
  332.  
  333. }
  334.  
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!