Java and MySql

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210

package databaseconsole; import java.sql.*; import java.util.*; import java.util.logging.Level; import java.util.logging.Logger; import sun.security.util.Password; /** * * @author Mahinda Jayasundara */ public class DataBaseConsole { public static void main(String[] args) { String Driver = "com.mysql.jdbc.Driver"; String url="jdbc:mysql://localhost:3306/javadb"; String userName = "root"; String pasword=""; Connection con; Statement st; ResultSet rs1; try { Class.forName(Driver); try { con = DriverManager.getConnection(url, userName, pasword); Scanner integerInput = new Scanner(System.in); System.out.println("Please enter EPF number -: "); int myID = integerInput.nextInt(); //Select specific reord... String selectQuery1 = "SELECT * FROM student WHERE id = ?"; PreparedStatement prestatement1 = con.prepareStatement(selectQuery1); prestatement1.setInt(1, myID); rs1 = prestatement1.executeQuery(); if (!rs1.isBeforeFirst() ){ System.out.println("No records found... Try another record.."); } else{ while(rs1.next()){ String firstName = rs1.getString("fname"); String lastName = rs1.getString("lname"); String age = Integer.toString(rs1.getInt("age")); String marksOne = Integer.toString(rs1.getInt("marksOne")); String marksTwo = Integer.toString(rs1.getInt("marksTwo")); String marksThree = Integer.toString(rs1.getInt("marksThree")); System.out.println("First name: " + firstName); System.out.println("Last name: " + lastName); System.out.println("Age: " + age); System.out.println("Subject One marks: " + marksOne); System.out.println("Subject Two marks: " + marksTwo); System.out.println("Subject Three marks: " + marksThree); } } //Select all recods String selectQuery2 = "SELECT * FROM student"; PreparedStatement prepairState2 = con.prepareStatement(selectQuery2); ResultSet rs2 = prepairState2.executeQuery(); int recordCount = 1; if(rs2.isBeforeFirst()){ while(rs2.next()){ System.out.println("Display record set " + recordCount); String firstName = rs2.getString("fname"); String lastName = rs2.getString("lname"); String age = Integer.toString(rs2.getInt("age")); String marksOne = Integer.toString(rs2.getInt("marksOne")); String marksTwo = Integer.toString(rs2.getInt("marksTwo")); String marksThree = Integer.toString(rs2.getInt("marksThree")); System.out.println("First name: " + firstName); System.out.println("Last name: " + lastName); System.out.println("Age: " + age); System.out.println("Subject One marks: " + marksOne); System.out.println("Subject Two marks: " + marksTwo); System.out.println("Subject Three marks: " + marksThree); recordCount++; } } //select using 2 parameters System.out.println("Pease enter age -: "); int age = integerInput.nextInt(); System.out.println("Please enter id -: "); int id = integerInput.nextInt(); String selectQuery3 = "SELECT * FROM `student` WHERE id=? AND age=?"; PreparedStatement preparedStatement3 = con.prepareStatement(selectQuery3); preparedStatement3.setInt(2, age); preparedStatement3.setInt(1, id); ResultSet rs3 = preparedStatement3.executeQuery(); if(rs3.isBeforeFirst()){ while(rs3.next()){ System.out.println("Display record set " + recordCount); String firstName = rs3.getString("fname"); String lastName = rs3.getString("lname"); String age1 = Integer.toString(rs3.getInt("age")); String marksOne = Integer.toString(rs3.getInt("marksOne")); String marksTwo = Integer.toString(rs3.getInt("marksTwo")); String marksThree = Integer.toString(rs3.getInt("marksThree")); System.out.println("First name: " + firstName); System.out.println("Last name: " + lastName); System.out.println("Age: " + age1); System.out.println("Subject One marks: " + marksOne); System.out.println("Subject Two marks: " + marksTwo); System.out.println("Subject Three marks: " + marksThree); } } else{ System.out.println("No reocres found for query 3"); System.out.println(preparedStatement3.toString()); } //Using like statement Scanner stringScanner = new Scanner(System.in); System.out.println("Enter First name to search : "); String stringInput = stringScanner.next(); String selectQuery4 = "SELECT * FROM student WHERE fname LIKE ?"; PreparedStatement preparedStatement4 = con.prepareStatement(selectQuery4); preparedStatement4.setString(1, "%"+ stringInput + "%"); ResultSet rs4 = preparedStatement4.executeQuery(); if(rs4.isBeforeFirst()){ while(rs4.next()){ String firstName = rs4.getString("fname"); String lastName = rs4.getString("lname"); String age1 = Integer.toString(rs4.getInt("age")); String marksOne = Integer.toString(rs4.getInt("marksOne")); String marksTwo = Integer.toString(rs4.getInt("marksTwo")); String marksThree = Integer.toString(rs4.getInt("marksThree")); System.out.println("First name: " + firstName); System.out.println("Last name: " + lastName); System.out.println("Age: " + age1); System.out.println("Subject One marks: " + marksOne); System.out.println("Subject Two marks: " + marksTwo); System.out.println("Subject Three marks: " + marksThree); } } else{ System.out.println("Query contains no reords. Your query is"+ preparedStatement4.toString()); } //Create table statement String queryString5 = "CREATE TABLE temp(" + "id INT NOT NULL PRIMARY KEY," + "myName VARCHAR(100)," + "myAddress VARCHAR(500)" + ")"; PreparedStatement preparedStatement5 = con.prepareStatement(queryString5); preparedStatement5.executeUpdate(); //Update query test String fnameTemp ="XXXXXXXXX"; int idTemp = 3; String selectQuery7 = "UPDATE student SET fname=? WHERE id=?"; PreparedStatement pre7 = con.prepareStatement(selectQuery7); pre7.setInt(2, idTemp); pre7.setString(1, fnameTemp); pre7.executeUpdate(); String deleteQuery = "DELETE from student WHERE id=?"; int deleteID = 7; PreparedStatement pre8 = con.prepareStatement(deleteQuery); pre8.setInt(1, deleteID); pre8.executeUpdate(); System.out.println("Delete query" + pre8.toString()); } catch (SQLException ex) { Logger.getLogger(DataBaseConsole.class.getName()).log(Level.SEVERE, null, ex); } finally{ } } catch (ClassNotFoundException ex) { Logger.getLogger(DataBaseConsole.class.getName()).log(Level.SEVERE, null, ex); } } }