· 9 years ago · Sep 07, 2017, 03:18 PM
1package Main;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.PreparedStatement;
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.util.Vector;
9
10import org.json.JSONArray;
11import org.json.JSONObject;
12
13public class Tester{
14 private static Connection db;
15 private static PreparedStatement stmt;
16 final static String URL = "jdbc:mysql://127.0.0.1:3306/";
17 final static String DB_NAME = "hackeru";
18 final static String USER_NAME = "root";
19 final static String USER_PASS = "";
20 //private static String aBrandName[]= {"ZTE","Nokia","OPO","HTC"};
21 //private static int aPrice[] = {900,2500,2900,3250};
22 //private static int aQty[] = {5,3,2,7};
23 public static void main(String[] args) throws Exception {
24 // creating our connection string
25
26 // creating a connection to our database
27 db = DriverManager.getConnection(URL + DB_NAME, USER_NAME, USER_PASS);
28 //create table for the first time, and if the table exists do not create it....
29 //createTable();
30
31 //insert first data to our mySQL with JDBC
32 //insertFirstDemo();
33
34 //insert data in secured way.... (no SQL injection)
35 //saferInsert();
36
37 //insert array of data....
38 /*
39 for (int counter=0;counter<aBrandName.length;counter+=1)
40 {
41 saferInsert2(aBrandName[counter], aPrice[counter], aQty[counter]);
42 }
43 System.out.println("I was finished, yeah MAN!");
44 */
45 getData();
46 }
47
48 private static void getData() throws Exception
49 {
50 //create sql statment
51 String sql="SELECT * FROM phones";
52 //get result from mySQL to an object called ResultSet
53 ResultSet result=db.prepareStatement(sql).executeQuery();
54 //System.out.println(result.toString());
55 Vector <myPhones> myVec = new Vector<>();
56 for (result.first();!result.isAfterLast();result.next())
57 {
58 myVec.add(new myPhones(
59 result.getString("name"),
60 result.getInt("price"),
61 result.getInt("qty")
62 ));
63 }
64
65 for (myPhones item:myVec)
66 {
67 System.out.println(item);
68 }
69
70 /*
71 JSONArray jsonResult = new JSONArray();
72 for (result.first();!result.isAfterLast();result.next())
73 {
74 jsonResult.put(new JSONObject()
75 .put("name", result.getString("name"))
76 .put("price", result.getInt("price"))
77 .put("qty", result.getInt("qty"))
78 );
79 }
80
81 System.out.println(jsonResult.toString());
82 */
83 }
84
85 private static void saferInsert2(String brandName, int price, int qty) throws Exception
86 {
87 //assume there is a table phones with name,price,qty 1 2 3
88 String sql="INSERT INTO phones (name,price,qty) VALUES (?,?,?)";
89 PreparedStatement stmt = db.prepareStatement(sql); //prepare statement with 3 parms
90 stmt.setString(1, brandName); //bind string param as first param (first question mark)
91 stmt.setInt(2, price); //bind int param as second param (second question mark)
92 stmt.setInt(3, qty); //bin int param as third param (becuase there is no f*** way that i will buy again apple, me want android
93 stmt.execute();
94 }
95
96 private static void saferInsert() throws Exception
97 {
98 //assume there is a table phones with name,price,qty 1 2 3
99 String sql="INSERT INTO phones (name,price,qty) VALUES (?,?,?)";
100 PreparedStatement stmt = db.prepareStatement(sql); //prepare statement with 3 parms
101 stmt.setString(1, "Iphone 7 plus"); //bind string param as first param (first question mark)
102 stmt.setInt(2, 4300); //bind int param as second param (second question mark)
103 stmt.setInt(3, 1); //bin int param as third param (becuase there is no f*** way that i will buy again apple, me want android
104 stmt.execute();
105 }
106
107 private static void insertFirstDemo() {
108 // create a SQL command.
109 String sql = "INSERT INTO chocos (name) "
110 + "values ('Para'),('Milka'),('Oreo'),('Bueno'),"
111 + "('Ferror Roche');";
112
113 try {
114 // prepare statement for given SQL
115 stmt = db.prepareStatement(sql);
116 // execute the statement
117 stmt.execute();
118
119 } catch (SQLException e) {
120 // TODO Auto-generated catch block
121 e.printStackTrace();
122 }
123 System.out.println("Data was inserted");
124 }
125
126 private static void createTable() {
127 // create a SQL command.
128 String sql = "CREATE TABLE IF NOT EXISTS chocos" + " (id INT PRIMARY KEY AUTO_INCREMENT,"
129 + " name VARCHAR (16) NOT NULL);";
130
131 try {
132 // prepare statement for given SQL
133 stmt = db.prepareStatement(sql);
134 // execute the statement
135 stmt.execute();
136
137 } catch (SQLException e) {
138 // TODO Auto-generated catch block
139 e.printStackTrace();
140 }
141
142 System.out.println("Table was created");
143 }
144
145 private static String strValues(String[] strs)
146 {
147 StringBuilder values = new StringBuilder();
148 char del = ' ';
149 for (String str:strs)
150 {
151 values.append(del).append("('").append(str).append("')");
152 if (del == ' ') del=',';
153 }
154 return values.toString();
155 }
156}
157
158
159
160
161myPhones.class
162=================
163package Main;
164
165public class myPhones {
166 public String name;
167 public int price;
168 public int qty;
169
170 public myPhones(String name, int price, int qty) {
171 super();
172 this.name = name;
173 this.price = price;
174 this.qty = qty;
175 }
176
177 @Override
178 public String toString() {
179 return "myPhones name=" + name + " price=" + price + " qty=" + qty +" value:"+price*qty;
180 }
181
182
183
184}