SHARE
TWEET

Untitled

a guest Mar 20th, 2016 55 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. package cz.uhk.fim.ase.platform.database;
  2.  
  3. import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
  4. import java.sql.*;
  5. import java.util.logging.Level;
  6. import java.util.logging.Logger;
  7.  
  8. /**
  9.  *
  10.  * @author Jakub "AnDylek" Pluhar <jakub.pluhar@gmail.com at andylek.eu>
  11.  */
  12. public class DatabaseSQL {
  13.  
  14.     final static String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  15.     final static String DB_URL = "jdbc:mysql://imitgw.uhk.cz:5981";
  16.     final static String DB_NAME = "vp_agents";
  17.  
  18.    //Database login
  19.    //TODO: UNSAFE!!
  20.     final static String USER = "root";
  21.     final static String PASS = "Bcxa97am";
  22.  
  23.     public DatabaseSQL(){
  24.         final static String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  25.         final static String DB_URL = "jdbc:mysql://imitgw.uhk.cz:5981";
  26.         final static String DB_NAME = "vp_agents";
  27.  
  28.        //Database login
  29.        //TODO: UNSAFE!!
  30.         final static String USER = "root";
  31.         final static String PASS = "Bcxa97am";
  32.     }
  33.    
  34.     public void CreateDB() {
  35.  
  36.         try {
  37.  
  38.             MysqlDataSource dataSource = new MysqlDataSource();
  39.             dataSource.setUser(USER);
  40.             dataSource.setPassword(PASS);
  41.             dataSource.setServerName(DB_URL);
  42.             dataSource.setDatabaseName(DB_NAME);
  43.  
  44.             Connection conn;
  45.             Statement st;
  46.  
  47.             //Register JDBC driver
  48.             Class.forName(JDBC_DRIVER);
  49.  
  50.             //Open a connection
  51.             System.out.println("Connecting to database...");
  52.             conn = DriverManager.getConnection(DB_URL, USER, PASS);
  53.             System.out.println("Succesfully connected.");
  54.  
  55.             //Execute a query
  56.             System.out.println("Creating database...");
  57.             st = conn.createStatement();
  58.  
  59.             //TODO: Update database tables to real model.
  60.             //st.addBatch("CREATE TABLE agents (id INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,money INT(6),agentID VARCHAR(255), product VARCHAR(255), food INT(6), painkillers INT(6), tools INT(6))");
  61.             st.addBatch("CREATE TABLE agents (id INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY, cislo INT(4)");
  62.             st.executeBatch();
  63.             System.out.println("Database successfully created.");        
  64.  
  65.             //Ending commands
  66.             st.executeBatch();
  67.             st.close();
  68.             conn.close();
  69.            
  70.         } catch (SQLException | ClassNotFoundException ex) {
  71.             Logger.getLogger(DatabaseSQL.class.getName()).log(Level.SEVERE, null, ex);
  72.         }
  73.     }
  74.  
  75.     /**
  76.      * This method adds new row into database
  77.      *
  78.      * @param id
  79.      * @param agentID
  80.      * @param product
  81.      * @param inventory_Food
  82.      * @param inventory_PainKillers
  83.      * @param inventory_Tools
  84.      * @param money is money, that agent have
  85.      */
  86.     public static void AddRow(int id, String agentID, Float money, String product, int inventory_Food, int inventory_PainKillers, int inventory_Tools) {
  87.  
  88.         try {
  89.             MysqlDataSource dataSource = new MysqlDataSource();
  90.             dataSource.setUser(USER);
  91.             dataSource.setPassword(PASS);
  92.             dataSource.setServerName(DB_URL);
  93.             dataSource.setDatabaseName(DB_NAME);
  94.             Connection conn;
  95.             Statement st;
  96.             Class.forName(JDBC_DRIVER);
  97.            
  98.             System.out.println("Connecting to database...");
  99.             conn = DriverManager.getConnection(DB_URL, USER, PASS);
  100.             System.out.println("Succesfully connected.");
  101.            
  102.             System.out.println("Writing into database...");
  103.             st = conn.createStatement();            
  104.             st.addBatch("INSERT INTO agents (id, money, product, food, painkillers, tools) VALUES (" + id + "," + agentID + "," + money + "," + product + "," + inventory_Food + "," + inventory_PainKillers + "," + inventory_Tools +")");
  105.             st.executeBatch();
  106.             st.close();
  107.  
  108.         } catch (ClassNotFoundException | SQLException ex) {
  109.             Logger.getLogger(DatabaseSQL.class.getName()).log(Level.SEVERE, null, ex);
  110.         }
  111.     }
  112.  
  113.     public static void TestRow(int cislo){
  114.         try {
  115.             MysqlDataSource dataSource = new MysqlDataSource();
  116.             dataSource.setUser(USER);
  117.             dataSource.setPassword(PASS);
  118.             dataSource.setServerName(DB_URL);
  119.             dataSource.setDatabaseName(DB_NAME);
  120.             Connection conn;
  121.             Statement st;
  122.             Class.forName(JDBC_DRIVER);
  123.            
  124.             System.out.println("Connecting to database...");
  125.             conn = DriverManager.getConnection(DB_URL, USER, PASS);
  126.             System.out.println("Succesfully connected.");
  127.            
  128.             System.out.println("Writing into database...");
  129.             st = conn.createStatement();            
  130.             st.addBatch("INSERT INTO agents (cislo) VALUES (" + cislo + ")");
  131.             st.executeBatch();
  132.             st.close();
  133.  
  134.         } catch (ClassNotFoundException | SQLException ex) {
  135.             Logger.getLogger(DatabaseSQL.class.getName()).log(Level.SEVERE, null, ex);
  136.         }
  137.     }
  138.     public static void TestGet(){
  139.         try {
  140.             MysqlDataSource dataSource = new MysqlDataSource();
  141.             dataSource.setUser(USER);
  142.             dataSource.setPassword(PASS);
  143.             dataSource.setServerName(DB_URL);
  144.             dataSource.setDatabaseName(DB_NAME);
  145.             Connection conn;
  146.             Statement st;
  147.             Class.forName(JDBC_DRIVER);
  148.  
  149.             System.out.println("Connecting to database...");
  150.             conn = DriverManager.getConnection(DB_URL, USER, PASS);
  151.             System.out.println("Succesfully connected.");
  152.  
  153.             System.out.println("Writing into database...");
  154.             st = conn.createStatement();
  155.            
  156.             ResultSet rs = st.executeQuery("SELECT * FROM agents ORDER BY id");
  157.             System.out.println("ID | CISLO \n");
  158.             while (rs.next()) {                
  159.                 Integer id = rs.getInt("id");
  160.                 Integer cislo = rs.getInt("cislo");
  161.  
  162.                 System.out.println(id + " | " + cislo + "\n");
  163.             }
  164.            
  165.             st.executeBatch();
  166.             st.close();
  167.            
  168.         } catch (ClassNotFoundException | SQLException ex) {
  169.             Logger.getLogger(DatabaseSQL.class.getName()).log(Level.SEVERE, null, ex);
  170.         }
  171.     }
  172.     /**
  173.      * This method prints the whole database.
  174.      */
  175.     public static void GetDatabase() {
  176.         try {
  177.             MysqlDataSource dataSource = new MysqlDataSource();
  178.             dataSource.setUser(USER);
  179.             dataSource.setPassword(PASS);
  180.             dataSource.setServerName(DB_URL);
  181.             dataSource.setDatabaseName(DB_NAME);
  182.             Connection conn;
  183.             Statement st;
  184.             Class.forName(JDBC_DRIVER);
  185.  
  186.             System.out.println("Connecting to database...");
  187.             conn = DriverManager.getConnection(DB_URL, USER, PASS);
  188.             System.out.println("Succesfully connected.");
  189.  
  190.             System.out.println("Writing into database...");
  191.             st = conn.createStatement();
  192.            
  193.             ResultSet rs = st.executeQuery("SELECT * FROM agents ORDER BY id, agentID");
  194.             System.out.println("ID | AGENT ID | MONEY | PRODUCT | FOOD | PAINKILLERS | TOOLS " + "\n");
  195.             while (rs.next()) {                
  196.                 Integer id = rs.getInt("id");
  197.                 String agentID = rs.getString("agentID");
  198.                 Integer money = rs.getInt("money");
  199.                 String product = rs.getString("product");
  200.                 Integer food = rs.getInt("food");
  201.                 Integer painkillers = rs.getInt("painkillers");
  202.                 Integer tools = rs.getInt("tools");
  203.                 System.out.println(id + " | " + agentID + " | " + money + " | " + product + " | " + food + " | " + painkillers + " | " + tools + " | " + "\n");
  204.             }
  205.            
  206.             st.executeBatch();
  207.             st.close();
  208.            
  209.         } catch (ClassNotFoundException | SQLException ex) {
  210.             Logger.getLogger(DatabaseSQL.class.getName()).log(Level.SEVERE, null, ex);
  211.         }
  212.  
  213.     }
  214. }
RAW Paste Data
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
 
Top