tedsXvuA

1package MyDB;
2
3import java.sql.*;
4import java.util.Vector;
5
6public class Tester {
7    //to create a connection to our Data Base
8    private static Connection db;
9    //will convert our SQL request to a statement that the DB will recognize
10    private static PreparedStatement statement;
11    //address of our mysql
12    //final static String URL = "jdbc:mysql://127.0.0.1:3306/";
13    final static String URL = "jdbc:mysql://localhost/";
14    final static String OLDJDBC = "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC";
15    //name of the data base
16    final static String DB_NAME = "wtf";
17    //name of user (in our case administrator)
18    final static String DB_USER = "root";
19    //administrator password
20    final static String DB_PASS = "";
21
22    public static void main(String[] args) throws SQLException {
23        //create our connection string
24
25        //creating a connection to our data base, using our new library
26        db = DriverManager.getConnection(URL+DB_NAME+OLDJDBC,DB_USER,DB_PASS);
27
28        //create table for the first time if it's not exists
29        createTable();
30        /*
31        //insertSomeData();
32        saferInsert("CBD");
33        saferInsert("RIP");
34        saferInsert("SOB");
35        */
36
37        getData();
38    }
39
40    private static void getData() {
41        //create SQL statment
42        String sql = "SELECT * FROM drugs ";
43        //get result from mySQL to an object call ResultSet
44        try {
45            ResultSet result = db.prepareStatement(sql).executeQuery();
46            Vector<String> drugList = new Vector<>();
47            //move on result as itreator
48            for (result.first();!result.isAfterLast();result.next()){
49                //add to vector a column "name" that is string
50                drugList.add(result.getString("name"));
51            }
52            for (String item:drugList){
53                System.out.println(item);
54            }
55        } catch (SQLException e) {
56            e.printStackTrace();
57        }
58    }
59
60    private static void saferInsert(String drugName){//1
61        String sql = "INSERT INTO drugs (name) VALUES (?)";
62        try {
63            statement = db.prepareStatement(sql);
64            statement.setString(1,drugName);
65            statement.execute();
66        } catch (SQLException e) {
67            e.printStackTrace();
68        }
69    }
70
71    private static void insertSomeData() {
72        //create a SQL command
73        String sql = "INSERT INTO drugs (name) "+
74                "values ('CBD'),('RIP'),('SOB');";
75
76        try{
77            //prepare statement for given SQL
78            statement = db.prepareStatement(sql);
79            //execute the statment
80            statement.execute();
81        } catch (SQLException err){
82            System.out.println(err.getMessage());
83        }
84    }
85
86    private static void createTable() {
87        //create SQL statment
88        String sql = "CREATE TABLE IF NOT EXISTS drugs "+
89                "(id INT PRIMARY KEY AUTO_INCREMENT, "+
90                "name VARCHAR (16) NOT NULL);";
91        try{
92            //prepare a SQL command
93            statement = db.prepareStatement(sql);
94            //execute the SQL command
95            statement.execute();
96            System.out.println("Table was created");
97        } catch (SQLException err){
98            System.out.println(err.getMessage());
99        }
100
101    }
102}